How can I tell if an Oracle database is mounted and activated?
I got it!
CONTROLFILE_TYPE from v$database is the key
SQL> select CONTROLFILE_TYPE from v$database;
CONTROL
-------
CURRENT
versus
SQL> select CONTROLFILE_TYPE from v$database;
CONTROL
-------
STANDBY
This works for a physical standby, Oracle 10g (and higher)
SELECT open_mode FROM v$database;
If the value is:
- 'MOUNTED', your database is mounted.
- 'READ WRITE', then you can assume it's been activated.
- 'READ ONLY' then it might be opened for query in read only mode, but not activated.
- 'READ ONLY WITH APPLY' when using active dataguard.
On primary:
select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
On slave:
OPEN_MODE
-------------------- --------------------
MOUNTED or READ ONLY WITH APPLY
There might be other values as well, I'm not sure.
HTH