How to Configure Oracle Dataguard Broker
Make sure these parameters properly set on primary/standby
database.
standby_file_management=AUTO
fal_client=Host DB name
fal_server=Remote DB name
local_listener=Host Listener name
remote_listener=Remote Listener name
standby_archive_dest=archive destination on standby database
db_unique_name=should be different for primary and standby
log_file_name_convert=‘primary destination’,’standby destination’
RAC services should be created using db_unique_name
TNSNAMES.ORA entries:-
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT
= ))
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = ))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT
= ))
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = ))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
standby_file_management=AUTO
fal_client=Host DB name
fal_server=Remote DB name
local_listener=Host Listener name
remote_listener=Remote Listener name
standby_archive_dest=archive destination on standby database
db_unique_name=should be different for primary and standby
log_file_name_convert=‘primary destination’,’standby destination’
RAC services should be created using db_unique_name
TNSNAMES.ORA entries:-
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
LISTENER.ORA entries PRIMARY:-
SID_LIST_LISTENER_GNN01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oraidm_db/DB)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = +ASM1)
(ORACLE_HOME = /oraidm_db/ASM)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGMGRL)
(SID_NAME = PROD)
(ORACLE_HOME = /oraidm_db/DB)
(SERVICE_NAME =PROD )
)
)
LISTENER_GNN01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT
= 14242)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 14242)(IP = FIRST))
)
)
LISTENER.ORA entries STANDBY:-
LISTENER_SOLRAC01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = solrac01-vip)(PORT = 14242)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.17.91)(PORT = 14242)(IP = FIRST))
)
)
SID_LIST_LISTENER_SOLRAC01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /orasolpprd_db/DB)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = +ASMUMARKET1)
(ORACLE_HOME = /orasolpprd_db/ASM)
)
(SID_DESC =
(GLOBAL_DBNAME = UMARKET_DR_DGMGRL)
(SID_NAME = UMARKET01)
(ORACLE_HOME = /orasolpprd_db/DB)
(SERVICE_NAME =UMARKET )
)
)
Step 1. Stop broker in both of primary and standby databases (in all instances in case of RAC):
SQL>ALTER SYSTEM SET DG_BROKER_START=FALSE;
Make sure log_file_convert is properly set on standby database.
Step 2. Drop old broker configuration files from both primary and standby:
Check the location of files using : "show parameter dg_broker_config_file"
By default the broker configuration files will be in "$ORACLE_HOME/dbs" in Unix and "$ORACLE_HOME/database" in Windows.
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
---------------------- ------ ------------------------
dg_broker_config_file1 string \oracle\product\10.2.0\dbs\dr1PROD10G.dat
dg_broker_config_file2 string \oracle\product\10.2.0\dbs\dr2PROD10G.dat
SID_LIST_LISTENER_GNN01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oraidm_db/DB)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = +ASM1)
(ORACLE_HOME = /oraidm_db/ASM)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGMGRL)
(SID_NAME = PROD)
(ORACLE_HOME = /oraidm_db/DB)
(SERVICE_NAME =PROD )
)
)
LISTENER_GNN01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)
)
LISTENER.ORA entries STANDBY:-
LISTENER_SOLRAC01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = solrac01-vip)(PORT = 14242)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.17.91)(PORT = 14242)(IP = FIRST))
)
)
SID_LIST_LISTENER_SOLRAC01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /orasolpprd_db/DB)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = +ASMUMARKET1)
(ORACLE_HOME = /orasolpprd_db/ASM)
)
(SID_DESC =
(GLOBAL_DBNAME = UMARKET_DR_DGMGRL)
(SID_NAME = UMARKET01)
(ORACLE_HOME = /orasolpprd_db/DB)
(SERVICE_NAME =UMARKET )
)
)
Step 1. Stop broker in both of primary and standby databases (in all instances in case of RAC):
SQL>ALTER SYSTEM SET DG_BROKER_START=FALSE;
Make sure log_file_convert is properly set on standby database.
Step 2. Drop old broker configuration files from both primary and standby:
Check the location of files using : "show parameter dg_broker_config_file"
By default the broker configuration files will be in "$ORACLE_HOME/dbs" in Unix and "$ORACLE_HOME/database" in Windows.
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
---------------------- ------ ------------------------
dg_broker_config_file1 string \oracle\product\10.2.0\dbs\dr1PROD10G.dat
dg_broker_config_file2 string \oracle\product\10.2.0\dbs\dr2PROD10G.dat
Drop the files using ASMCMD if files are on ASM:
ASMCMD> cd DATA/PROD10G/
ASMCMD> rm dr1PROD10G.dat
ASMCMD> rm dr2PROD10G.dat
ASMCMD> cd DATA/PROD10G/
ASMCMD> rm dr1PROD10G.dat
ASMCMD> rm dr2PROD10G.dat
Drop the files
using :
Unix
$cd $ORACLE_HOME/dbs
$ rm dr1PROD10G.dat
$ rm dr2PROD10G.dat
$cd $ORACLE_HOME/dbs
$ rm dr1PROD10G.dat
$ rm dr2PROD10G.dat
Windows
c:\>cd $ORACLE_HOME/database
D:\oracle\product\10.2.0\db_2\database> del dr1PROD10G.dat
D:\oracle\product\10.2.0\db_2\database> del dr2PROD10G.dat
ALTER SYSTEM SET dg_broker_config_file1=’+ORAUMARKET_DATA3/dr1mgf.dat’ scope=both sid=’*’;
ALTER SYSTEM SET dg_broker_config_file2=’+ORAUMARKET_DATA3/dr2mgf.dat’ scope=both sid=’*’;
It should be on shared location in case of RAC.
Step 3. Start broker in both of primary and standby (in all instances in case of RAC):
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
c:\>cd $ORACLE_HOME/database
D:\oracle\product\10.2.0\db_2\database> del dr1PROD10G.dat
D:\oracle\product\10.2.0\db_2\database> del dr2PROD10G.dat
ALTER SYSTEM SET dg_broker_config_file1=’+ORAUMARKET_DATA3/dr1mgf.dat’ scope=both sid=’*’;
ALTER SYSTEM SET dg_broker_config_file2=’+ORAUMARKET_DATA3/dr2mgf.dat’ scope=both sid=’*’;
It should be on shared location in case of RAC.
Step 3. Start broker in both of primary and standby (in all instances in case of RAC):
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
Step 4. Connect to DGMGRL on primary: (from
instance one in case of RAC)
$dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> CONNECT sys/;
Connected.
$dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> CONNECT sys/
Connected.
Step 5. Create configuration on primary:
DGMGRL> CREATE CONFIGURATION 'DGCONFIG_PROD' AS PRIMARY DATABASE IS 'UMARKET' CONNECT IDENTIFIER IS UMARKET;
Configuration "PRODCONF" created with primary database "PROD10G"
DGMGRL> CREATE CONFIGURATION 'DGCONFIG_PROD' AS PRIMARY DATABASE IS 'UMARKET' CONNECT IDENTIFIER IS UMARKET;
Configuration "PRODCONF" created with primary database "PROD10G"
Step 6. Add standby in the configuration:
DGMGRL> ADD DATABASE 'UMARKET_DR' AS CONNECT IDENTIFIER IS UMARKET_DR MAINTAINED AS PHYSICAL;
Database "STDBY10G" added
Step 7.
DGMGRL> show configuration
Configuration
Name: DGCONFIG_PROD
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
UMARKET - Physical standby database
UMARKET_DR - Primary database
Current status for "DGCONFIG_PROD":
SUCCESS
Step 8. Enable Configuration:
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> ADD DATABASE 'UMARKET_DR' AS CONNECT IDENTIFIER IS UMARKET_DR MAINTAINED AS PHYSICAL;
Database "STDBY10G" added
Step 7.
DGMGRL> show configuration
Configuration
Name: DGCONFIG_PROD
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
UMARKET - Physical standby database
UMARKET_DR - Primary database
Current status for "DGCONFIG_PROD":
SUCCESS
Step 8. Enable Configuration:
DGMGRL> ENABLE CONFIGURATION;
Enabled.
Use the SHOW command to verify that the configuration and its
databases were successfully enabled and brought online:
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: PRODCONF
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PROD10G - Primary database
STDBY10G - Physical standby database
Current status for "PRODCONF"
SUCCESS
DGMGRL> show database verbose 'UMARKET'
Database
Name: UMARKET
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
UMARKET01 (apply instance)
UMARKET02
Properties:
InitialConnectIdentifier = 'umarket'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
SidName(*)
LocalListenerAddress(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
LatestLog(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Current status for "UMARKET":
SUCCESS
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: PRODCONF
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PROD10G - Primary database
STDBY10G - Physical standby database
Current status for "PRODCONF"
SUCCESS
DGMGRL> show database verbose 'UMARKET'
Database
Name: UMARKET
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
UMARKET01 (apply instance)
UMARKET02
Properties:
InitialConnectIdentifier = 'umarket'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
SidName(*)
LocalListenerAddress(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
LatestLog(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Current status for "UMARKET":
SUCCESS
DGMGRL> show database verbose 'UMARKET_DR'
Database
Name: UMARKET_DR
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
UMARKET01
UMARKET02
Properties:
InitialConnectIdentifier = 'umarket_dr'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
SidName(*)
LocalListenerAddress(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
LatestLog(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Current status for "UMARKET_DR":
SUCCESS
Show database 'UMARKET' 'InconsistentProperties';
Show database 'UMARKET_DR' 'InconsistentProperties';
Show database 'UMARKET' 'StatusReport';
Show database 'UMARKET_DR' 'StatusReport';
SHOW DATABASE 'UMARKET' 'LogXptStatus';
SHOW DATABASE 'UMARKET' 'InconsistentLogXptProps';
DGMGRL> switchover to 'UMARKET_DR'
Performing switchover NOW, please wait...
Operation requires shutdown of instance "UMARKET01" on database "UMARKET"
Shutting down instance "UMARKET01"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "UMARKET01" on database "UMARKET_DR"
Shutting down instance "UMARKET01"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "UMARKET01" on database "UMARKET"
Starting instance "UMARKET01"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "UMARKET01" on database "UMARKET_DR"
Starting instance "UMARKET01"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "UMARKET_DR"
Database
Name: UMARKET_DR
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
UMARKET01
UMARKET02
Properties:
InitialConnectIdentifier = 'umarket_dr'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
SidName(*)
LocalListenerAddress(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
LatestLog(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Current status for "UMARKET_DR":
SUCCESS
Show database 'UMARKET' 'InconsistentProperties';
Show database 'UMARKET_DR' 'InconsistentProperties';
Show database 'UMARKET' 'StatusReport';
Show database 'UMARKET_DR' 'StatusReport';
SHOW DATABASE 'UMARKET' 'LogXptStatus';
SHOW DATABASE 'UMARKET' 'InconsistentLogXptProps';
DGMGRL> switchover to 'UMARKET_DR'
Performing switchover NOW, please wait...
Operation requires shutdown of instance "UMARKET01" on database "UMARKET"
Shutting down instance "UMARKET01"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "UMARKET01" on database "UMARKET_DR"
Shutting down instance "UMARKET01"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "UMARKET01" on database "UMARKET"
Starting instance "UMARKET01"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "UMARKET01" on database "UMARKET_DR"
Starting instance "UMARKET01"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "UMARKET_DR"
Comments
Post a Comment