Database – How to tell if an Oracle database is mounted and activated

databaseoraclesql

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.

Best Answer

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
Related Topic