Oracle Data Guard Broker configuration

Oracle Data Guard Broker configuration example¶

The official Oracle Data Guard Broker docs docs are, of course, the best source for accurate and complete information about how to set up Oracle Data Guard with Oracle Data Guard Broker. They are, however, not always the most useful source, since they generally sacrifice readability for completeness.
This doc should be useful to people setting up a Data Guard Broker with DGMGRL. Managing Data Guard through the Broker is much simpler than managing it manually. DGMGRL is allegedly not as simple as managing Data Guard through Orcle Enterprise Manager Grid Control; however, Grid Control is an extra-cost option that not all of us have.
This procedure makes a number of assumptions that you have made life easy on yourself: you’re using Oracle 11g; you’ve configured a db_recovery_file_dest; your primary and standby databases will be on separate machines with identical directory structures; etc.
Special thanks go out to Chris Ruel of Perpetual Technologies in Indianapolis for his “Oracle 11g Data Guard” presentation, which provided the starting material for this. I have pared away some parts and fleshed out others, but I never would have figured the core of the material out without his help.
I am a beginner at this, and this procedure almost certainly contains steps that are not optimal, and perhaps completely unnecessary. All I can say for certain is that this is one path that got me going successfully.
To begin, assume that you have a primary database server, prodserv.myco.com, hosting a production database, PROD. You have a standby server, stbyserv.myco.com, and intend to put a standby Oracle database on it, STBY. Oracle 11g + is installed on both machines.

1. On stbyserv, create $ORACLE_HOME/dbs/initSTBY.ora with just one parameter:

   db_name = PROD
   

   (On Windows, all references to $ORACLE_HOME/dbs should be understood as %ORACLE_HOME%\database.)
   
2. On prodserv:

   SQL> CREATE PFILE FROM SPFILE;

   Add these parameters to $ORACLE_HOME/dbs/initPROD.ora:
   

   *.db_unique_name='PROD'
   *.dg_broker_start=TRUE
   *.fal_client='PROD'
   *.fal_server='STBY'
   *.log_archive_config='dg_config=(STBY,PROD)'
   *.log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)'
   *.log_archive_dest_2='service="STBY", db_unique_name="STBY", valid_for=(online_logfile,primary_role)'
   *.standby_file_management='AUTO'

3. These new parameters must be incorporated into PROD‘s SPFILE, and the instance must be restarted from the SPFILE:

   SQL> shutdown immediate;
   SQL> !rm $ORACLE_HOME/dbs/spfilePROD.ora
   SQL> startup;
   SQL> create spfile from pfile;
   SQL> shutdown immediate;
   SQL> startup;

4. Edit $ORACLE_HOME/network/admin files.
   On prodserv, add to listener.ora:
   

   (SID_DESC =
       (GLOBAL_DBNAME = stby_DGMGRL)
       (ORACLE_HOME = {oracle home directory here})
       (SID_NAME = STBY)
       (SERVICE_NAME = STBY)
   )

   On stbyserv, add to listener.ora:
   

   (SID_DESC =
       (GLOBAL_DBNAME = stby.myco.com)
       (ORACLE_HOME = {oracle home directory here})
       (SID_NAME = STBY)
   )
   (SID_DESC =
       (GLOBAL_DBNAME = prod_DGMGRL)
       (ORACLE_HOME = {oracle home directory here})
       (SID_NAME = PROD)
       (SERVICE_NAME = PROD)
   )

   (Note that each machine contains a _DGMGRL entry for the database hosted on the other machine)
   tnsnames.ora should have these entries on both machines:
   

   PROD =
       (DESCRIPTION =
           (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = prodserv.myco.com)(PORT = 1521))
           )
           (CONNECT_DATA =
               (SERVICE_NAME = prod.myco.com)
           )
       )
   STBY =
       (DESCRIPTION =
           (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = stbyserv.myco.com)(PORT = 1521))
           )
           (CONNECT_DATA =
               (SERVICE_NAME = stby.myco.com)
           )
       )

   Run lsnrctl reload on both machines. Verify that you can tnsping prod and tnsping stby from both machines.
   
5. Under *nix, copy $ORACLE_HOME/dbs/orapwPROD from PROD to $ORACLE_HOME/dbs/orapwSTBY on stbyserv.
   Under Windows, on stbyserv:
   

   oradim -new -sid stby -intpwd mypassword

6. SQL> connect sys@stby as sysdba
   SQL> startup nomount;

7. PROD should have standby logfiles - one more than its number of online redo logfiles, I believe.

   SQL> connect sys@prod as sysdba
   SQL> select type, count(*) from v$logfile group by type;
   SQL> alter database add standby logfile;
   SQL> alter database add standby logfile;
   SQL> alter database add standby logfile;
   SQL> alter database add standby logfile;

8. RMAN> connect target sys@prod
   RMAN> backup database plus archivelog;

9. Copy the {flash recovery area}/PROD/BACKUPSET/{today} directory from prodserv to stbyserv. Copy it to exactly the same place in the directory structure. Do not change prod to stby in the path.
   On stbyserv:
   

   mkdir {oradata directory}/PROD

   Again, do not rename PROD to STBY in the directory structure.
   

10. RMAN> connect target sys@prod auxiliary sys@stby
    RMAN> run {
        allocate channel prmy1 type disk;
        allocate auxiliary channel stby1 type disk;
        duplicate target database
        For standby
        from active database spfile
        set db_unique_name = 'STBY'
        set fal_client = 'STBY'
        set fal_server = 'PROD'
        set standby_file_management = 'AUTO'
        set log_archive_config = 'dg_config=(PROD,STBY)'
        set log_archive_dest_1 = 'service=PROD ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PROD'
        dorecover nofilenamecheck;
        }

11. SQL> connect sys@stby as sysdba;
    SQL> recover managed standby database disconnect;
    SQL> recover managed standby database cancel;
    SQL> alter database open read only;
    SQL> recover managed standby database using current logfile disconnect;

12. DGMGRL> connect sys@prod
    DGMGRL> create configuration 'standby_config' as
        > primary database is 'PROD'
        > connect identifier is 'PROD';
    
    DGMGRL> show configuration;
    DGMGRL> add database 'STBY' as
        > connect identifier is stby;
    DGMGRL> enable configuration;
    DGMGRL> enable database stby;

13. Finally ready!

    DGMGRL> connect sys@prod
    DGMGRL> switchover to 'STBY';

    I may be doing something wrong, but when I do this, PROD remains shut down after the failover. I can (and should) bring it up manually to have it work as a fully-functional failover (and, optionally, read-only reporting) instance.
    

    SQL> connect sys@prod as sysdba;
    SQL> startup;
    SQL> select count(*) from dba_objects;
    
      COUNT(*)
    ----------
         70705
    
    SQL> create table foo (bar NUMBER);
    create table foo (bar NUMBER)
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-16000: database open for read-only access