Check DataGaurd Role Primary/Standby

Which Database is the primary Database , Which One Is the Standby

Simple Query Will Answer this , The Scenario Like the following I have Data Guard I want to check which one Of these database are Primary Or Standby How Can I do that :

SQL > select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

The above Output Indicate that you are Now On Primary Database, Different Output for Standby

SQL > select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

There’s More than One Way
SQL> SELECT controlfile_type FROM V$database;

Output On Primary Database :

CONTROL
——–
CURRENT

On Standby :

CONTROL
——–
STANDBY

Thank you
Osama Mustafa

Dealing With Database Gaurd

Database Switchover 

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:

 

On Primary Server:
SQL> alter database commit to switchover to standby;
This may cause the following error to be generated:
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
If this does occur then restart the database, as below, before retrying the above command:
SQL> shutdown immediate
SQL> startup

 

SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;

The primary server is now configured as a DR standby database.
On DR Server:
SQL> alter database recover managed standby database cancel;
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
SQL> startup
The DR server is now configured as the primary database.
To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows:


SQL> alter database recover managed standby database cancel;
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup
The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

Opening the Standby Database in Read Only Mode

 

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary.
On standby server:
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
The standby database is now open and available for querying in read only mode.
To put the standby database back into standby mode:
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;

How to check whether the Standby Database is in Sync

On the primary server:

SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;

 
On the standby server:

SQL> SELECT max(sequence#) AS “STANDBY”, applied
          FROM v$archived_log GROUP BY applied;

The standby database is in sync with the primary database if the above PRIMARY value matches the above STANDBY value where applied = ‘YES’.
 

 


How To Check Oracle Physical Standby is in Sync with the Primary or Not?

On Primary

set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest
where DESTINATION is NOT NULL

At Physical Standby


SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1



SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP



select process,status,sequence# from v$managed_standby

Compare the output of the query its should be equal .