The following configuration was tested on RHEL4U2 64-bit with Oracle 10.1.0.5 database.
Pri db_unique_name = 'TESTPRI'DG db_unique_name = 'TESTDG'Configuration(any name) = 'DG_TEST'sys password = 'sys'Pri conn stg = 'TESTPRI'
1. Set up init parameters on primary to enable broker
Note: For RAC, ensure dg_broker_config_files are on shared storage and accessible to all the instances.
Note: Broker config files are named as dr1<
SQL> alter system set dg_broker_start=false sid='*';System altered.SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';System altered.SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';System altered.SQL> alter system set dg_broker_start=true sid='*';System altered.
2. Verify if DMON process has started on all the instances of primary. Example:
$ ps -efgrep dmongrep -v greporacle 16190 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR1$ ps -efgrep dmongrep -v greporacle 29723 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR2
3. Set up init parameters on standby
SQL> alter system set dg_broker_start=false sid='*';System altered.SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';System altered.SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';System altered.SQL> alter system set dg_broker_start=true sid='*';System altered.
4. GLOBAL_DBNAME should be set to <
This is important otherwise you'll have TNS-12154 error during switchover operation.
Example:
SID_LIST_LISTENER_TESTPRI = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /apps/oracle/product/10g/db) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = TESTPRIR1) (GLOBAL_DBNAME = TESTPRI_DGMGRL) (ORACLE_HOME = /apps/oracle/product/10g/db) ) )
5. DGMGRL Configuration5.1ConnectDGMGRL> CONNECT sys/sysConnected.5.2Create ConfigurationDGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;Configuration "DG_TEST" created with primary database "TESTPRI".5.3Verify configurationDGMGRL> SHOW CONFIGURATION;Configuration Name: DG_TEST Enabled: NO Protection Mode: MaxPerformance Databases: TESTPRI - Primary databaseCurrent status for "DG_TEST":DISABLED5.4Verify database; if RAC verify if all instances are validatedDGMGRL> show database 'TESTPRI';Database Name: TESTPRI Role: PRIMARY Enabled: NO Intended State: ONLINE Instance(s): TESTPRIR1 TESTPRIR2Current status for "TESTPRI":DISABLED5.5Add standby database to the configurationDGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;Database "TESTDG" added.5.6Enable the brokerDGMGRL> ENABLE CONFIGURATION;Enabled.5.7Verfying againDGMGRL> SHOW CONFIGURATION;Configuration Name: DG_TEST Enabled: YES Protection Mode: MaxPerformance Databases: TESTPRI - Primary database TESTDG - Physical standby databaseCurrent status for "DG_TEST":SUCCESS6. TroubleshootingLet us see some sample issues and their fixIssueDGMGRL> CONNECT sys/sysORA-16525: the Data Guard broker is not yet availableFixSet dg_broker_start=trueIssueAfter enabling the configuration, on issuing SHOW CONFIGURATION, this error comes Warning: ORA-16608: one or more sites have warningsFixTo know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.Few Monitorable properties to troubleshootDGMGRL> 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';IssueDGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT TESTPRIR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting. TESTPRIR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.IssueDGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE TESTPRIR2 ArchiveLagTarget 0 0 TESTPRIR2 LogArchiveMaxProcesses 4 2 4 ExampleDGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses'; LogArchiveMaxProcesses = '4'FixDGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;orSQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';System altered.DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses'; LogArchiveMaxProcesses = '4'More commandsDGMGRL> SHOW DATABASE VERBOSE 'dbname';This will show all property values in detailDGMGRL> HELP;List of all broker commands with usage help
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
That's it my friends. You are all set to use broker now for managing your dataguard configuration. In my next blog, I'll talk about switchover using DG broker
