How to Identify Connected Database Instance is Primary or Standby?

For primary and standby database the database name will be same and instance name will be different. These are the main differences to identify the primary and standby database instances.

Primary

1.  SQL> select name from v$database;

NAME

---------

KMPRD

2.  SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

KMPRD

3.  SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

4.  SQL> SELECT controlfile_type FROM V$database;

CONTROL

-------

CURRENT

5.  SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

Standby

1.  SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY    

2.  Database name will be common for both primary and standby SQL> select name from v$database;

NAME

---------

KMPRD

3.  SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

KMPRD_STANDBY

4.  SQL> SELECT controlfile_type FROM V$database;

CONTROL

-------

STANDBY

5.  SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED