Home / server / Questions / 38488
Accepted
Matt Simmons
Asked: 2009-07-10 09:42:32 +0800 CST

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

  • 772

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.

sql database oracle

5 Answers

  1. 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
    • 9

  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

    • 3

  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;
    • 1

  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:

    enter image description here

    Standby data guard database:

    enter image description here

    • 1

  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.

    • 0