Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

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
share|improve this answer
    
+1; It would be interesting to know what this value would be if you opened the standby for read only access. –  DCookie Jul 9 '09 at 20:06
add comment

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;
share|improve this answer
    
Good suggestion, but unfortunately it didn't tell me what I was hoping for :-/ –  Matt Simmons Jul 9 '09 at 18:24
add comment

This works for a physical standby, Oracle 10g:

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.

There might be other values as well, I'm not sure.

HTH

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.