How can I tell if an Oracle database is mounted and activated?
IANADBA, but I'm writing a script that will take action as long as the oracle standby database hasn't been activated. What I'm looking for is two queries (or sets of queries, if necessary).
1 - Is the database mounted (as in, has someone done "alter database mount standby database")
2 - How can I tell if the database is activated (as in, "alter database activate standby database")?
As I mentioned, I'm looking for queries, but if there's a way to tell in the system, I'm open to that, too. Thanks!
Update
I took the suggestion below (modified, slightly, because I'm dealing with Oracle 8i, and I get this:
SQL> SELECT INSTANCE_NAME, DATABASE_STATUS, INSTANCE_ROLE from v$instance;
INSTANCE_NAME DATABASE_STATUS INSTANCE_ROLE
---------------- ----------------- ------------------
RGR01 ACTIVE PRIMARY_INSTANCE
Right now, this instance is actively recovering archive logs, but is not "live". I'm still looking for a way to tell if it isn't mounted as a standby database.
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