Here is a counterexample with sober spaces. Let $X=\mathbb{N}\times\mathbb{N}$, equipped with a topology defined as follows. Fix an injection $i:\mathbb{N}\times\mathbb{N}\to\mathbb{N}$. Say that a set $U\subseteq X$ is open if for each $(a,b)\in U$, $U$ contains $(i(a,b),c)$ for all but finitely many $c\in\mathbb{N}$.
I claim that $X$ is Hausdorff, and thus sober. Let $p,q\in X$ be distinct points. Start with $U_0=\{p\}$ and $V_0=\{q\}$. Recursively define $U_{n+1}=U_n\cup ((i[U_n]\times\mathbb{N})\setminus\{q\})$ and $V_{n+1}=V_n\cup((i[V_n]\times\mathbb{N})\setminus\{p\})$. It is clear that $U=\bigcup U_n$ and $V=\bigcup V_n$ are open, since if $(a,b)\in U_n$ then $U_{n+1}$ contains $(i(a,b),c)$ for all but finitely many $c$, and similarly for $V$. I claim that $U_n$ and $V_n$ are disjoint for all $n$, and thus $U$ and $V$ are disjoint. Indeed, note that $U_n$ has the property that if $(a,b)\in U_n$ then either $(a,b)=p$ or $a=i(x)$ for some $x\in U_{n-1}$, and similarly for $V_n$. Thus $U_n$ and $V_n$ are disjoint by induction on $n$: if we already know $U_{n-1}$ and $V_{n-1}$ are disjoint, then all the points of $U_n$ (except $p$) have distinct first coordinate from all the points of $V_n$ (except $q$).
Now let $Y=\mathbb{N}\cup\{g\}$ where $\mathbb{N}$ has the cofinite topology and $g$ is the generic point. Define $f:X\to Y$ by $f(a,b)=b$. For any nonempty open $U\subseteq X$, $f[U]$ is cofinite in $\mathbb{N}$, and so there is a smallest open set of $Y$ that contains it, namely $f_!(U)=f[U]\cup\{g\}$. To verify the Frobenius condition, suppose $x\in f_!(U)\cap V$ for $U$ open in $X$ and $V$ open in $Y$. If $x\in\mathbb{N}$, then $(a,x)\in U$ for some $a\in U$ and then this $(a,x)$ is in $U\cap f^{-1}(V)$, witnessing that $x\in f_!(U\cap f^{-1}(V))$. If $x=g$, then note that $U$ and $V$ are both nonempty; let $(a,b)\in U$. Then $(i(a,b),c)\in U$ for all but finitely many $c$, and also $(i(a,b),c)\in f^{-1}(V)$ for all but finitely many $c$ since $V$ is cofinite. Thus $U\cap f^{-1}(V)$ is nonempty, and thus $g\in f_!(U\cap f^{-1}(V))$.
So, the map $f:X\to Y$ is open in the localic sense. However, it is not open in the topological sense, because $f[X]=\mathbb{N}$ is not open in $Y$.
On the other hand, you are correct that the two notions coincide if $Y$ is $T_1$. First, the existence of the left adjoint $f_!$ just means that for each $U\in\mathcal{O}(X)$, there is a smallest open set $f_!(U)\in\mathcal{O}(Y)$ that contains $f[U]$. If $Y$ is $T_1$, this immediately implies $f_!(U)=f[U]$ so $f$ is open in the topological sense: if $x\in f_!(U)\setminus f[U]$ then $f_!(U)\setminus\{x\}$ still contains $f[U]$ and is open, contradicting the definition of $f_!(U)$.
A bit more generally, they coincide if $Y$ is $T_D$, meaning that for each $y\in Y$, $\{y\}$ is open as a subset of $\overline{\{y\}}$. To prove this, note that a similar argument as in the paragraph above shows that every point of $f_!(U)$ must be a generization of a point of $f[U]$. So, to show that $f$ is open in the topological sense, it suffices to show that $f[U]$ is specialization-open for any $U\in\mathcal{O}(X)$. Suppose $y\in Y$ is a generization of a point of $f[U]$. Since $Y$ is $T_D$, there exists $V\in \mathcal{O}(Y)$ that contains $y$ but does not contain any other specialization of $y$. Now the Frobenius condition says $y\in f_!(U)\cap V\subseteq f_!(U\cap f^{-1}(V))$, so there is a point $x\in U\cap f^{-1}(V)$ such that $y$ is a generization of $f(x)$. Since $f(x)\in V$, this means $f(x)=y$, so $y\in f[U]$, as desired.