Your question is indeed asking for the finite sample distribution
of $r_{N}$. To address your question, let me rephrase it in terms
of linear regressions. So a linkage between $r_{N}$ and the ordinary
least square (OLS) estimator could be highlighted. You observe two
variables $\left\{ x_{i}\right\} _{i=1}^{N}$ and $\left\{ y_{i}\right\} _{i=1}^{N}$,
and they are uncorrelated. Suppose you run a linear regression between
$y_{i}$ and $x_{i}$,
$$
y_{i}=\beta x_{i}+u_{i},\quad i=1,\ldots,N.
$$
Since $y_{i}$ and $x_{i}$ are uncorrelated, the true value $\beta$
is $0$. Note that the OLS estimator of $\beta$ is
$$
\hat{\beta}_{N}=\frac{\sum_{i=1}^{N}x_{i}y_{i}}{\sum_{i=1}^{N}x_{i}x_{i}}=\frac{N^{-1}\sum_{i=1}^{N}x_{i}y_{i}}{N^{-1}\sum_{i=1}^{N}x_{i}x_{i}}.
$$
Here the numerator of $\hat{\beta}_{N}$ is your $r_{N}$. I did not
impose any distribution assumptions about $x_{i}$ and $y_{i}$, so
no loss of generality was incurred. Based on this linkage, we may
approach your question by using the distribution of $\hat{\beta}_{N}$.
However, it is well known that the finite sample distribution of $\hat{\beta}_{N}$
is not available in most cases. Consequently, my conclusion is pessimistic
in that the 'exact' answer to your question might not exist in most
cases. But an approximate $\tilde{N}$ can be easily found since $\hat{\beta}_{N}$
is asymptotically normal.
First, consider a finite sample case. If $\left\{ x_{i}\right\} _{i=1}^{N}$ is fixed in your experiment,
and $\left\{ y_{i}\right\} _{i=1}^{N}$ are i.i.d. normal with mean
$0$ and variance $\sigma^{2}$. Then it is well known that
$$
\hat{\beta}_{N}\mid x_{1},\ldots,x_{N}\sim N\left(\beta,\frac{\sigma^{2}}{N}\left(\frac{\sum_{i=1}^{N}x_{i}^{2}}{N}\right)^{-1}\right).
$$
Consequently,
$$
r_{N}\mid x_{1},\ldots,x_{N}\sim N\left(0,\frac{\sigma^{2}}{N}\left(\frac{\sum_{i=1}^{N}x_{i}^{2}}{N}\right)\right).
$$
Denote $\sigma_{r,N}^{2}=\left(\sigma^{2}/N\right)\left(\sum_{i=1}^{N}x_{i}^{2}/N\right)$,
and note that $\sigma_{r,N}$ is monotone decreasing with respect
to $N$. Then given $\varepsilon$, we have $\Pr\left(\left|r_{N}\right|<\varepsilon\right)=1-2\Phi\left(-\varepsilon/\sigma_{r,N}\right)$.
By letting $ $$1-2\Phi\left(-\varepsilon/\sigma_{r,N}\right)=\alpha$,
you can solve $\tilde{N}$. (Remark: I think you are asking for such
a $\tilde{N}$ that $\Pr\left(\left|r_{N}\right|<\varepsilon\right)\geq\alpha$
for all $N>\tilde{N}$.) But these arguments are based on the properties
of normal distributions.
Next, consider the large sample case ($N\rightarrow \infty$), where $x_i$ could be random. Suppose $\left\{ x_{i}\right\} _{i=1}^{N}$ and $\left\{ y_{i}\right\} _{i=1}^{N}$
satisfy the regularity conditions of the LLN and CLT. Asymptotically,
we have
$$
\hat{\beta}_{N}\rightarrow_{d}N\left(\beta,\frac{\sigma^{2}}{N}Q^{-1}\right),\quad Q=\mathrm{{plim}}_{N\rightarrow\infty}\frac{\sum_{i=1}^{N}x_{i}^{2}}{N},
$$
where $\sigma^{2}$ is the variance of $y_{i}$. By Slutsky lemma,
we have
$$
r_{N}\rightarrow_{d}N\left(0,\frac{\sigma^{2}}{N}Q\right).
$$
It is obvious that $\sigma_{r,N}^{2}$ is a consistent estimator of
$\left(\sigma^{2}/N\right)Q$. Then an approximate $\tilde{N}$ can
be found in the same way.
