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:

  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