One can easily check via SELECT database_role FROM v$database; whether your Oracle database is a primary or standby database. Is it possible to make the same determination without database access, but only having (Unix) host access?
Oracle DBAs are very used to doing things like ps -ef | grep pmon or ps -ef | grep tnslsnr to quickly check if things are running, and these commands require no DB access. They can even be run from an unprivileged (non-root) Unix user who doesn't own Oracle. So thinking along those lines, I have 90% of it solved with things like ps -eo args | grep mrp0, but that falls short. It only works if the standby is mounted and doing managed recovery. It doesn't work if (i) the standby is simply mounted without starting MRP, or (ii) if the standby has been opened read-only.
So to recap, here are the parameters:
- Goal is to determine whether a running DB instance is a Primary or Standby.
- No access to the DB (e.g.: via SQL*Plus, JDBC, etc), but you have server access.
- Therefore can only use Unix tools (
ps,lsof, /proc,awk, …) - Nice if can be done from unprivileged (non-root) Unix user.
- Triple nice if can be done from any unprivileged Unix user (i.e. not the Oracle owner).
Note, I'm running Oracle 11gR2 & 12c on AIX, but I'll gladly take any *nix solution.
Note2, I don't care about the quasi-solution where as root you simply su to the Oracle owner and sqlplus / as sysdba. I want to do this without a database connection. No V$ views allowed, no X$ tables allowed.
Best Answer
You can try using:
ps -ef | grep -v grep | grep ora_prOr if data guard is in use:
ps -ef | grep -v grep | grep ora_rsm