How can I tell if an Oracle database is mounted and activated?

Question

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.

Answer 1

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

Answer 2

This works for a physical standby, Oracle 10g (and higher)

SELECT open_mode FROM v$database;

If the value is:

1. 'MOUNTED', your database is mounted.
2. 'READ WRITE', then you can assume it's been activated.
3. 'READ ONLY' then it might be opened for query in read only mode, but not activated.
4. '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

Answer 3

This might be overly simplistic for what you after as i'm not too familiar with standby databases

but as long as the instance is up you should be able to query the v$instance view and get the status of the database for example

SELECT INSTANCE_NAME, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE FROM v$instance;

Answer 4

I really do not know the answer for this question - "How can I tell if an Oracle database is mounted and activated?", but I have to post answer to let people know that please do not get misled from the answers of this post because most are grossly mistaken.

• Those who are saying that use "v$instance", are absolutely wrong because that will tell whether "instance" is mounted or starting or active or not. Please note that database and database instance are different things, so if you do not know difference then better read, and also read about Oracle RAC and Oracle Data Gurard.

• Those you are saying use select CONTROLFILE_TYPE from v$database; are also not right because as per Oracle this column tell about control file information and tells whether database is in standby mode or active mode, which is different then whether database has mounted or whether database is active.

Like I said, I think it is very important to understand the different between database and database instance and also know the what is Oracle RAC and Oracle Data Gurard.

Suppose, I have a primary database and another standby databguard database, and both database/instance are active for use, but see the difference from below screen shots:

Primary database:

Standby data guard database:

Answer 5

Could you monitor the connections to the database via netstat/lsof? Presumably you'd notice clients starting to use it.

You may also be able to watch the archive log for those commands.