DATA GUARD BROKER CONFIGURATION

In this article we are going to learn how to set up Data Guard broker for managing data guard.

Primary and standby database server details:

Primary Server: ora1-1.mydomain Database: TESTDB
Standby Server: ora1-2.mydomain Database: STANDBY
Primary Database Details:

 

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 16:06:52 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

OPEN         TESTDB           PRIMARY          MAXIMUM PERFORMANCE

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

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

           803

 

Standby Database Details:

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED      STANDBY          PHYSICAL STANDBY MAXIMUM PERFORMANCE

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

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

           803

 

On both primary and standby databases, enable the dataguard broker process by setting the value of the parmater “DG_BROKER_START” to TRUE.

SQL> alter system set dg_broker_start=true;

 

System altered.

 

Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.

On Primary:

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TESTDB_DGMGRL)

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = TESTDB)

    )

On Standby:

   (SID_DESC =

      (GLOBAL_DBNAME = STANDBY_DGMGRL)

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = STANDBY)

    )

Listener.ora file contents on primary server:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.9.2)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TESTDB_DGMGRL )

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = TESTDB)

    )

  )

Listener.ora file contents on standby server:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.9.3)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

 

SID_LIST_LISTENER =

   (SID_DESC =

      (GLOBAL_DBNAME = STANDBY_DGMGRL )

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = STANDBY)

    )

 

  )

On the primary server, create the Dataguard Broker configuration.

 

[oracle@NVMBD1BZY150D00 ~]$ dgmgrl

 

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

Welcome to DGMGRL, type "help" for information.

 

DGMGRL> connect sys/sys@TESTDB

 

Connected.

 

DGMGRL> create configuration 'dgtest' as primary database is 'TESTDB' connect identifier is TESTDB;

 

Configuration "dgtest" created with primary database "TESTDB"

 

DGMGRL> show configuration;

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB - Primary database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

 

Add the standby database “SRPSTB” to the configuration created above.

DGMGRL> add database 'STANDBY' as connect identifier is STANDBY maintained as physical;

 

Database "STANDBY" added

 

DGMGRL> show configuration;

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB  - Primary database

    STANDBY - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

 

The configuration added, needs to be enabled.

 

DGMGRL> enable configuration;

 

Enabled.

 

DGMGRL> show configuration

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB  - Primary database

    STANDBY - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS

 

 

Setup is complete, you are ready to use DG Broker.

 

Few Monitorable properties to troubleshoot

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

 

 

Switchover of Databases (Primary to standby and standby to primary)

A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
Setup physical standby database

After completing physical standby database creation.

standby database creation

Follow below steps to switchover database from primary to standby and vice versa

SETUP:

PRIMARY (Primary Database with connection string as “TESTDB”)

[oracle@NVMBD1BZY150D00 301214]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 5 15:33:44 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select instance_name,status,database_role from v$database,v$instance;

 

INSTANCE_NAME    STATUS       DATABASE_ROLE

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

TESTDB           OPEN         PRIMARY

STANDBY (Secondary Database with connection string as “STANDBY”)

SQL> select instance_name,status,database_role from v$database,v$instance;

 INSTANCE_NAME    STATUS       DATABASE_ROLE

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

STANDBY          OPEN         PHYSICAL STANDBY

 

Archivelog Destination 1 of Primary Database is “/data1/archive/testdb” and Archivelog destination 2 of Primary database is pointing to the service-name of the standby database.

Archivelog Destination 1 of PRIMARY:

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string      LOCATION=/data1/archive/testdb VALID_

                                                 FOR= (ALL_LOGFILES, ALL_ROLES) D

                                                 B_UNIQUE_NAME=TESTDB

Archivelog Destination 2 of PRIMARY:

SQL> sho parameter log_archive_dest_2

 NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      SERVICE=STANDBY VALID_FOR=(ONL

                                                 INE_LOGFILES, PRIMARY_ROLE) DB_

                                                 UNIQUE_NAME=STANDBY

Archivelog Destination 1 of Standby database is “/data1/archive/STANDB” and archivelog destination 2 of standby database is pointing to the service-name of the primary database. (Note: destination 2 in standby database is not mandatory. It is required only if the standby database would be running in as primary database during switchover or failover. Hence it would be a good practice to set this parameter to avoid problems during the switchover or failover.)

Archive log destination 1 of STANDBY:

SQL> sho parameter log_archive_dest_1

 

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string      LOCATION=/data1/archive/STANDB

                                                 Y VALID_FOR=(ALL_LOGFILES,ALL_

                                                 ROLES) DB_UNIQUE_NAME=STANDBY

Archive log destination 2 of STANDBY:

SQL> sho parameter log_archive_dest_2

 

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      SERVICE=TESTDB VALID_FOR=(ONLI

                                                 NE_LOGFILES,PRIMARY_ROLE) DB_U

                                                 NIQUE_NAME=TESTDB

Pre-Switchover Checks:

These checks will have to be performed before the switchover activity is performed.

Verify whether Managed Recovery process is running on the standby database

Use the following query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CLOSING             157
ARCH      CLOSING             155
ARCH      CONNECTED             0
ARCH      CLOSING             156
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                158
MRP0      WAIT_FOR_LOG        158

The above fig. shows that the Managed Recovery Process (MRP0) is running on the standby database. If MRP is not running, then start the process with real time enabled using the below query in the standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the primary end are shipped and getting applied to the standby database.

At the primary side check the maximum archive log sequence that has got generated:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)

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

1                163

          

At the standby side, check the maximum archive log sequence that has been applied from the primary database:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)

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

         1            163

In the above case, sequence# 163 is the maximum sequence generated at the primary database and the same has been applied to the standby database.

Verify primary and standby tempfiles match

For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Tempfiles added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.

 SQL>select tmp.name filename,bytes,ts.name tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

 

Verify that all datafiles are online on both primary and standby databases

Check whether all the datafiles are online prior to the switchover on both primary and standby databases

SQL>select name from v$datafile where status=’OFFLINE’;

 On Primary side:

 SQL> select name from v$datafile where status='OFFLINE';

 no rows selected

On Standby side:

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

If there are any offline datafiles, then bring them online using the below query

 

SQL>alter database datafile <datafile name> online;

 Switchover Steps:

 These steps are performed during the switchover process at the primary database side.

 

Check if there are any jobs running on the primary database using the below query.

SQL> select * from dba_jobs_running;

no rows selected

If there are any jobs running on the primary database and if its execution is not very important, then terminate the job to continue further.

 

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> sho parameter job_que

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.

SQL>alter system set job_queue_processes=0 scope=spfile;

System altered

SQL>

Verify that the primary database can be switched over to the standby

Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

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

TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

Switchover the primary database to standby

Once when value of switchover_status returns “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

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

TO STANDBY

 SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

Now the primary database is switched over to the standby database. The execution of the above command may take some time and the archive logs generated during its execution would be automatically applied to the standby database. Once when the command is executed with the output as “Database altered”, it means that the primary database has been switched over to the standby.

Note: Always perform the switchover of the primary database to standby database first and then switchover the standby database to primary. If not, then you would end up landing with two primary databases

Switchover the standby database to primary

Query the switchover_status column from the v$database view at the standby side to determine whether the standby database can be switched over to the primary database.
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

Once when the value of switchover_status returns “TO PRIMARY” or “SESSIONS ACTIVE” on the standby database, then perform the switchover using the below query

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

Now the standby database has been switched over to the primary database.

Open the new primary database (STANDBY)

The new primary database will be in mount state. Open this new primary database using the below query.

SQL> alter database open;

Database altered.

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE

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

STANDBY          OPEN         PRIMARY

 

 
Restart the new standby database

Restart the new standby database (old primary database prim), bring it to the mount stage and start the managed recovery process.

Shutdown the new standby database (TESTDB)

SQL>shutdown immediate;

Startup the new standby database (TESTDB) in mount stage

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             335545928 bytes

Database Buffers          251658240 bytes

Redo Buffers               11837440 bytes

Database mounted

Start the managed recovery process on the the new standby database (TESTDB)

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE

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

TESTDB           MOUNTED      PHYSICAL STANDBY

 Post-Switchover tasks

Reset the job_queue_processes parameter to its previous value

Set the job queue processes to its original value on the new standby (TESTDB). 

SQL> sho parameter job_queue_processes

 NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

 

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.
SQL>alter system set job_queue_processes=1000 scope=spfile;

System altered

Now the roles of the databases have been changed. The primary database (TESTDB) has been changed to standby database and the standby database (STANDBY) has been changed to primary database.

The archive logs that get generated in the new primary database (STANDBY) get shipped automatically to the new standby database (TESTDB) and they are applied on it automatically.

Maximum archivelog generated at the new primary database (STANDBY)

SQL> select thread#,max(sequence#) from v$archived_log where applied=YES’ group by thread#;

  THREAD# MAX(SEQUENCE#)

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

         1            163

Maximum archivelog that has been shipped and applied to the new standby database (TESTDB)

SQL> select thread#,max(sequence#) from v$archived_log where applied=YES’ group by thread#;

    THREAD# MAX(SEQUENCE#)

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

         1            163

 

The roles can again be reversed by following the same above procedures.

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!