Oracle DG Broker
Prerequisite : flashback enabled @orcl, orclstdby
Step-By-Step Configuration Of Data Guard Broker in Oracle
As we have already discuss about the Data Guard Broker and its benefits in earlier post . Here we will configure the data Guard Broker . Here are the steps :
Primary Databse = orcl
Standby Database = orclstdby
Step 1 : Check the Data Guard Broker process
SQL> show parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
Step 2 : Start the Data Guard Broker Process on Primary database
SQL>alter system set dg_broker_start=true scope=both;
System altered.
Step 3 : Check DG_BROKER on standby database and start it
SQL> show parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
SQL>alter system set dg_broker_start=true scope=both ;
System altered.
Step 4 : Edit the listener.ora file
Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domain value .
SQL> show parameter db_domain
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
Since the value of db_domain is null so the the value of GLOBAL_DBNAME = orcl_DGMGRL for primary database and for standby GLOBAL_DBNAME = orclstdby_DGMGRL. The primary listener.ora file is as
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = C:\oracle\product\
(SID_NAME = orcl)
)
)
Similarly, edit the listener.ora file on standby database .
Step 5 : Configure the Data Guard Configuration
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxxxx@PRIMARY_DB
Connected.
DGMGRL>
create configuration 'dgorcl'
as primary database is 'orcl'
connect identifier is PRIMARY_DB ;
add database orclstdby as connect identifier is STANDBY_DB maintained as physical;
Configuration "dgorcl" created with primary database "orcl" .
Once the configuration is created then check the status of configuration .
DGMGRL> show configuration
Configuration - dgorcl
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orclstdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
Step 6 : Enable the configuration@primary
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration;
Configuration - dgorcl
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orclstdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
Step 7 : View the Primary and Standby database properties
DDGMGRL> show database verbose orcl
Database - orcl
Role: PRIMARY
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'primary_db'
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 = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\ORAFS1\\ORADATA\ORCL, C:\ORAF
S1\\ORADATA\ORCL, C:\ORAFS2\\ORADATA\ORCL, C:\ORAFS2\
\ORADATA\ORCL'
LogFileNameConvert = 'C:\ORAFS3\\ORADATA\ORCL, C:\ORAF
S3\\ORADATA\ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'orcl'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST
=N2U1RNPMS-DB1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAM
E=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'C:/oraFS4//oradata/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%T%S_%R.ARC'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> Database - orcl
Unrecognized command "database", try "help"
DGMGRL>
DGMGRL> show database verbose orclstdby
Database - orclstdby
Role: PHYSICAL STANDBY
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'standby_db'
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 = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\ORAFS1\ORADATA\ORCL, C:\ORAF
S1\\ORADATA\ORCL, C:\ORAFS2\\ORADATA\ORCL, C:\ORAFS2\
\ORADATA\ORCL'
LogFileNameConvert = 'C:\ORAFS3\ORADATA\ORCL, C:\ORAF
S3\\ORADATA\ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'orcl'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST
=N2U1RNPMS-DB2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclstdby_DGMGRL)(INSTANC
E_NAME=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'C:/oraFS4/oradata/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%T%S_%R.ARC'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
Step 8 如果要設定ODG protection mode:
DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.
Step 9 Change the overall protection mode for the configuration.
Use the EDIT CONFIGURATION command to upgrade the broker configuration to the MAXAVAILABILITY protection mode:
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
Step 10 switch over to standby db
DGMGRL> switchover to 'orclstdby';
Performing switchover NOW, please wait...
New primary database "orclstdby" is opening...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "orclstdby"
DGMGRL>
Verify:
sqlplus sys/IV4chtm@PRIMARY_DB as sysdba
SQL>
select switchover_status from v$database;
Step 11 switch back
@old standby db
DGMGRL> switchover to 'orcl';
後記
請記住, 切換指令都要在standby db(未來的primary DB 執行)。
|
Oracle Broker Observer
(Ref: http://www.oracle.com/technetwork/articles/smiley-fsfo-084973.html )
By default, the observer will initiate failover to the target standby if and only if ALL of the following are true:
◆ observer is running
◆ observer and the standby both lose contact with the primary
Note: if the observer loses contact with the primary, but the standby does not, the observer can determine that the primary is still up via the standby.
◆ observer is still in contact with the standby
◆ durability constraints are met ==>
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: DR_Sales
Observer: (none)
Lag Limit: 30 seconds
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)
◆ failover threshold timeout has elapsed
|
設定步驟
1.
DGMGRL>
EDIT DATABASE orcl SET PROPERTY FastStartFailoverTarget = orclstdby;
EDIT DATABASE orclstdby SET PROPERTY FastStartFailoverTarget = orcl;
edit database PRIMARY_DB set property LogXptMode='SYNC';
edit database PRIMARY_DB set property NetTimeout=10;
edit database STANDBY_DB set property NetTimeout=10;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 10;
2.Enable Fast-Start Failover
DGMGRL> enable fast_start failover;
* ERROR ORA-16819: fast-start failover observer not started
3.Start the observer
dgmgrl sys/password@PRIMARY_DB
DGMGRL> start observer
Observer started
4.Verify the configuration
dgmgrl sys/password@PRIMATY_DB
DGMGRL> show configuration verbose
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
5.Testing the Configuration: switch over to standby db
dgmgrl sys/password@PRIMATY_DB
DGMGRL> switchover to 'orclstdby';
6.Reinstate the aborted primary as a standby
6.1Check Flashback Database retention
On the primary to be aborted:
SQL>select (sysdate - oldest_flashback_time)*24*60 as history from v$flashback_database_log;
HISTORY
----------
140.35 ==> 140mins, 先確認flashback 可回復到多久之前。
6.2 Initiate FSFO failover
Abort the primary.
SQL> shutdown abort
Once Flashback Database has succeeded, the observer will convert the database to a standby, bounce it, and begin apply services.
SQL>startup mount
observer log:
Initiating reinstatement for database "PRIMARY_DB"...
Reinstating database "PRIMARY_DB", please wait...
Operation requires shutdown of instance "orcl" on database "PRIMARY_DB"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "PRIMARY_DB"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "PRIMARY_DB" ...
Reinstatement of database "PRIMARY_DB" succeeded
dgmgrl status:
Configuration
Name: FSF
Enabled: YES
Protection Mode: MaxAvailability
Databases:
orcl_b - Primary database
PRIMARY_DB - Physical standby database
- Fast-Start Failover target
Fast-Start Failover: ENABLED
Current status for "FSF":
SUCCESS
|
後記
如何設定Health Conditions
Table 8-2 Examples of Health Conditions
Health Condition
|
Description
|
"Datafile Offline"
|
Data file offline due to a write error.
|
"Corrupted Controlfile"
|
Corrupted controlfile.
|
"Corrupted Dictionary"
|
Dictionary corruption of a critical database object.
|
"Inaccessible Logfile"
|
LGWR is unable to write to any member of a log group due to an I/O error.
|
"Stuck Archiver"
|
Archiver is unable to archive a redo log because device is full or unavailable.
|
Command Example
ENABLE FAST_START FAILOVER CONDITION "Corrupted Controlfile";
This specifies that a fast-start failover should be done if a corrupted controlfile is detected.
ENABLE FAST_START FAILOVER CONDITION 27102;
沒有留言:
張貼留言