How to tell/find if the instance is Primary/Standby?

By querying v$database one can tell if the host is primary or standby

For primary
testdb> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state
testd> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

OR
On Primary database the value of controlfile_type in V$database is “CURRENT” and standby is “STANDBY”

Value on Primary:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
CURRENT

Value on Standby:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
STANDBY

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s