Environment: 11g, initial primary db ‘upup’, standby db ‘upup1′
Here are the major steps:
1.Setup primary db and standby db
2.using spfile instead of pfile for both standby db and primary db.
3.set DG_BROKER_START = true
4.Set parameters of data guard broker configuration files
On the server you want to setup the broker, in my case, it is the primary db server
dg_broker_config_file1 = /u01/app/oracle/product/11.1.0/db_2/dbs/dr1upup1.dat
dg_broker_config_file2 = /u01/app/oracle/product/11.1.0/db_2/dbs/dr2upup1.dat
5.static register services in listener
Configure service upup1_DGB in upup1 listener
Configure service upup_DGB in upup listener
6.Setup the broker configuration
dgmgrl
DGMGRL> connect /
create configuration my_dg as
primary database is upup1
connect identifier is upup1;
add database upup as
connect identifier is upup
maintained as physical;
enable configuration
show configuration
Configuration
Name: my_dg
Enabled: YES
Protection Mode: MaxPerformance
Databases:
upup1 – Primary database
upup – Physical standby database
Fast-Start Failover: DISABLED
Current status for “my_dg”:
SUCCESS
DGMGRL> show database verbose upup1
Database
Name: upup1
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
upup
Properties:
DGConnectIdentifier = ‘upup1′
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
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 = ‘5′
LogArchiveMinSucceedDest = ‘1′
DbFileNameConvert = ”
LogFileNameConvert = ”
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘rac1.baby.com’
SidName = ‘upup’
StandbyArchiveLocation = ‘/u01/app/oracle/oradata/upup/arch/’
AlternateLocation = ”
LogArchiveTrace = ‘0′
LogArchiveFormat = ‘upup_%t_%s_%r.arc’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’
Current status for “upup1″:
SUCCESS
DGMGRL> show database verbose upup
Database
Name: upup
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
upup
Properties:
DGConnectIdentifier = ‘upup’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
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 = ‘5′
LogArchiveMinSucceedDest = ‘1′
DbFileNameConvert = ”
LogFileNameConvert = ”
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘rac2.baby.com’
SidName = ‘upup’
StandbyArchiveLocation = ‘/u01/app/oracle/oradata/upup/arch/’
AlternateLocation = ”
LogArchiveTrace = ‘0′
LogArchiveFormat = ‘upup_%t_%s_%r.arc’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’
Current status for “upup”:
SUCCESS
6.Switchover
DGMGRL> switchover to upup
Performing switchover NOW, please wait…
New primary database “upup” is opening…
Operation requires shutdown of instance “upup” on database “upup1″
Shutting down instance “upup”…
ORA-01031: insufficient privileges
You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance “upup”
You must shut down instance “upup” manually
Operation requires startup of instance “upup” on database “upup1″
You must start instance “upup” manually
Switchover succeeded, new primary is “upup”
Now upup is primary and in open status, I need to shutdown upup1 and startup mount
conn sys/oracle@upup1 as sysdba
SQL> shutdown immediate
7.Check DGMGRL status again
DGMGRL> show configuration
Configuration
Name: my_dg
Enabled: YES
Protection Mode: MaxPerformance
Databases:
upup – Primary database
upup1 – Physical standby database
Fast-Start Failover: DISABLED
Current status for “my_dg”:
SUCCESS
8.Setup Fast failover
–Enable flashback on both db
SQL> startup mount
SQL> alter system set db_recovery_file_dest_size=1G;
SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/oradata’;
–set fast failover target
DGMGRL> EDIT DATABASE upup SET PROPERTY FastStartFailoverTarget = upup1;
DGMGRL> EDIT DATABASE upup1 SET PROPERTY FastStartFailoverTarget = upup;
–set log mode and protection mode
DGMGRL> EDIT DATABASE ‘upup’ SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE ‘upup1′ SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 10;
–enable fash failover
DGMGRL> enable fast_start failover
DGMGRL> connect sys/oracle@upup1
Connected.
DGMGRL> start observer
Observer started
…<to be continue>…
–connect another session
dgmgrl
DGMGRL> show fast_start failover
Fast-Start Failover: ENABLED
Threshold: 10 seconds
Target: upup1
Observer: rac1.baby.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
Make sure standby db is ready to fast failover
SQL> select FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;
FS_FAILOVER_STATUS FS_FAIL
———————- ——-
SYNCHRONIZED YES
conn primary db, simulate primary failure
SQL> shutdown abort
ORACLE instance shut down.
At the observer session:
DGMGRL> start observer
Observer started
21:50:30.64 Tuesday, June 23, 2009
Initiating Fast-Start Failover to database “upup1″…
Performing failover NOW, please wait…
Failover succeeded, new primary is “upup1″
21:50:51.64 Tuesday, June 23, 2009
21:56:10.28 Tuesday, June 23, 2009
Initiating reinstatement for database “upup”…
Reinstating database “upup”, please wait…
Operation requires shutdown of instance “upup” on database “upup”
Shutting down instance “upup”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “upup” on database “upup”
Starting instance “upup”…
Unable to connect to database
ORA-12541: TNS:no listener
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance “upup”
You must start instance “upup” manually
Reinstatement of database “upup” failed
21:56:33.07 Tuesday, June 23, 2009
–as indicated, start upup
SQL> startup mount
DGMGRL> reinstate database upup;
Reinstating database “upup”, please wait…
Reinstatement of database “upup” succeeded
DGMGRL> show database verbose upup
Database
Name: upup
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
upup
Properties:
DGConnectIdentifier = ‘upup’
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 = ‘5′
LogArchiveMinSucceedDest = ‘1′
DbFileNameConvert = ”
LogFileNameConvert = ”
FastStartFailoverTarget = ‘upup1′
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘rac2.baby.com’
SidName = ‘upup’
StandbyArchiveLocation = ‘/u01/app/oracle/oradata/upup/arch/’
AlternateLocation = ”
LogArchiveTrace = ‘0′
LogArchiveFormat = ‘upup_%t_%s_%r.arc’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’
Current status for “upup”:
SUCCESS
Note: monitor your alter logs all the time and correct error if any.
Filed under: Data Guard | Tagged: 11g, broker, Data Guard
To check the oracle standby realted processes, use this sql: select * from v$managed_standby;
To check the oracle resetlogs id, use this sql:
select * from v$database_incarnation;