- Posted by Gavin Soorma
- On March 19, 2010
- 2 Comments
This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control GUI as well and the assumption is that there is a Physical Standby database in place and redo transport and redo apply have already been configured and that both the Primary and Standby database are in sync.
Primary Database: genoa1_js
Standby Database: genoa1_fc
On both Primary as well as Standby database start the Data Guard Broker process
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; System altered.
Edit the listener.ora on both nodes to add a static entry for DGMGRL
This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.
Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = genoa1_js_dgmgrl) (ORACLE_HOME = /u01/oracle/product/11.1.0/db_1) (SID_NAME = genoa1) ) )
Create the configuration
genoa1:/u01/oracle> dgmgrl DGMGRL for IBM/AIX RISC System/6000: Version 220.127.116.11.0 - 64bit Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/xxxx Connected. DGMGRL> CREATE CONFIGURATION 'GenesisDR' > AS > PRIMARY DATABASE IS 'genoa1_js' > CONNECT IDENTIFIER IS 'genoa1_js' > ; Configuration "GenesisDR" created with primary database "genoa1_js"
Add the Standby database to the configuration
DGMGRL> ADD DATABASE > 'genoa1_fc' > AS > CONNECT IDENTIFIER IS 'genoa1_fc' > ; Database "genoa1_fc" added DGMGRL> SHOW CONFIGURATION Configuration Name: GenesisDR Enabled: NO Protection Mode: MaxAvailability Databases: genoa1_js - Primary database genoa1_fc - Physical standby database Fast-Start Failover: DISABLED Current status for "GenesisDR": DISABLED
Enable the configuration
DGMGRL> ENABLE CONFIGURATION Enabled. DGMGRL> SHOW CONFIGURATION Configuration Name: GenesisDR Enabled: YES Protection Mode: MaxAvailability Databases: genoa1_js - Primary database genoa1_fc - Physical standby database Fast-Start Failover: DISABLED Current status for "GenesisDR": SUCCESS
View the Standby and Primary database properties
DGMGRL> show database genoa1_js Database Name: genoa1_js Role: PRIMARY Enabled: YES Intended State: TRANSPORT-ON Instance(s): genoa1 Current status for "genoa1_js": SUCCESS DGMGRL> show database genoa1_fc Database Name: genoa1_fc Role: PHYSICAL STANDBY Enabled: YES Intended State: APPLY-ON Instance(s): genoa1 Current status for "genoa1_fc": SUCCESS DGMGRL> show database verbose genoa1_fc Database Name: genoa1_fc Role: PHYSICAL STANDBY Enabled: YES Intended State: APPLY-ON Instance(s): genoa1 Properties: DGConnectIdentifier = 'genoa1_fc' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' StatusReport = '(monitor)' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' HostName = 'drou037' SidName = 'genoa1' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=drou037)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=genoa1_fc_DGMGRL)(INSTANCE_NAME=genoa1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' LatestLog = '(monitor)' TopWaitEvents = '(monitor)' Current status for "genoa1_fc": SUCCESS
Change the properties of a configured databaase
DGMGRL> EDIT DATABASE 'genoa1_js' SET PROPERTY 'LogXptMode'='SYNC'; Property "LogXptMode" updated
Performing a switchover
Note: In this case, currently the Primary Database is genoa1_fc and the Standby database is genoa1_js.
DGMGRL> switchover to 'genoa1_js' Performing switchover NOW, please wait... New primary database "genoa1_js" is opening... Operation requires shutdown of instance "genoa1" on database "genoa1_fc" Shutting down instance "genoa1"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "genoa1" on database "genoa1_fc" Starting instance "genoa1"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "genoa1_js" DGMGRL> SHOW CONFIGURATION; Configuration Name: GenesisDR Enabled: YES Protection Mode: MaxAvailability Databases: genoa1_js - Primary database genoa1_fc - Physical standby database Fast-Start Failover: DISABLED Current status for "GenesisDR": SUCCESS
Monitoring the Data Guard Broker Configuration
If we receive any error or warnings we cab obtain more information about the same ny running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.
DGMGRL> show database genoa1_js statusreport STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> show database genoa1_js logxptstatus LOG TRANSPORT STATUS PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS genoa1 genoa1_fc DGMGRL> show database genoa1_js InconsistentProperties INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE