Read Data Guard Physical Standby Database Best Practices – Part I
In this second part we will setup the standby database and enable Fast Start Fail Over.
Standby Database Creation
Start the standby database instance in NOMOUNT start up mode:
|
1 |
SQL> startup nomount pfile=initdg2.ora; |
Now that the configuration of the standby server is complete, let’s perform the duplication from the primary site.
Primary Server
Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
RMAN> connect target sys target database Password: connected to target database: DG1 (DBID=1753913301) RMAN> connect auxiliary sys@dg2 auxiliary database Password: connected to auxiliary database: DG2 (not mounted) RMAN> run{ allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby from active database spfile parameter_value_convert 'dg1','dg2' set db_unique_name='dg2' set db_file_name_convert='/dg1/','/dg2/' set log_file_name_convert='/dg1/','/dg2/' set control_files='/opt/oradata/dg2/dg2.ctl' set log_archive_max_processes='5' set fal_client='dg2' set fal_server='dg1' set standby_file_management='AUTO' set log_archive_config='dg_config=(dg1,dg2)' set log_archive_dest_2='service=dg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg1' ; } |
The RMAN duplication process is finished without errors. After the database duplication, we will force the log switch on the primary database to archive the current redo log group.
|
1 |
SQL> alter system switch logfile; |
Standby Server
On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.The DISCONNECT option means the redo apply will run in background session.
|
1 2 |
SQL> alter database recover managed standby database using current logfile disconnect; |
Note that the current log sequence number on the standby is 10.
|
1 2 3 4 5 6 7 8 |
SQL> select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM APPLIED ---------- --------- --------- 9 16-JAN-12 YES 10 16-JAN-12 IN-MEMORY |
Primary Server
Let’s perform 3 additional log switches on the primary database. Then we will query the standby database to verify that the log files are applied to the standby.
|
1 |
SQL> alter system switch logfile; |
Standby Server
We query the standby database. The logs were successfully transported and applied.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM APPLIED ---------- --------- --------- 9 16-JAN-12 YES 10 16-JAN-12 YES 11 16-JAN-12 YES 12 16-JAN-12 YES 13 16-JAN-12 IN-MEMORY |
Broker Configuration
Configuring the broker is recommended because it simplifies data guard operations.
The DG_BROKER_START parameter must be set to TRUE.
Standby Server
|
1 |
SQL> alter system set dg_broker_start=TRUE; |
The Oracle Net listener must be also configured with an additional static service identifier. The value of the GLOBAL_DBNAME attribute must be set to a concatenation of _DGMGRL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[oracle@dg2 dbs]$ cat $ORACLE_HOME/network/admin/listener.ora LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=dg2) (SID_NAME=dg2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) ) (SID_DESC= (GLOBAL_DBNAME=dg2_DGMGRL) (SID_NAME=dg2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) ) ) [oracle@dg2 dbs]$ lsnrctl status (Entry truncated) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "dg2" has 1 instance(s). Instance "dg2", status UNKNOWN, has 1 handler(s) for this service... Service "dg2_DGMGRL" has 1 instance(s). Instance "dg2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Primary Server
|
1 |
SQL> alter system set dg_broker_start=TRUE; |
The Oracle Net listener on the primary database should have static service definitions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=dg1) (SID_NAME=dg1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) ) (SID_DESC= (GLOBAL_DBNAME=dg1_DGMGRL) (SID_NAME=dg1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@dg1 ~]$ lsnrctl start (Entry truncated) Services Summary... Service "dg1" has 1 instance(s). Instance "dg1", status UNKNOWN, has 1 handler(s) for this service... Service "dg1_DGMGRL" has 1 instance(s). Instance "dg1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Let’s create a broker configuration and identify the primary database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@dg1 ~]$ dgmgrl DGMGRL> connect sys Password: Connected. DGMGRL> create configuration 'DGConfig1' as primary database is 'dg1' > connect identifier is dg1; Configuration "DGConfig1" created with primary database "dg1" DGMGRL> add database 'dg2' as connect identifier is dg2; Database "dg2" added DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: dg1 - Primary database dg2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Perform a switchover test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DGMGRL> switchover to dg2; Performing switchover NOW, please wait... New primary database "dg2" is opening... Operation requires shutdown of instance "dg1" on database "dg1" Shutting down instance "dg1"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "dg1" on database "dg1" Starting instance "dg1"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "dg2" DGMGRL> show configuration; Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: dg2 - Primary database dg1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Standby Server
Confirm of the role switch:
|
1 2 3 4 5 |
SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY |
Primary Server
The former primary database is now the new physical standby database:
|
1 2 3 4 5 |
SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY |
Switch over to the former primary database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
DGMGRL> switchover to dg1; Performing switchover NOW, please wait... New primary database "dg1" is opening... Operation requires shutdown of instance "dg2" on database "dg2" Shutting down instance "dg2"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "dg2" on database "dg2" Starting instance "dg2"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "dg1" DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: dg1 - Primary database dg2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database dg2 Database - dg2 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 5 minutes 2 seconds Apply Lag: 5 minutes 51 seconds Real Time Query: OFF Instance(s): dg2 Database Status: SUCCESS |
Enable Fast Start Fail Over
The actual configuration is running in Max Performance mode and Fast Start Fail Over is currently disabled.
Primary Server
To configure FSFO, you must first enable flashback database on both the primary and standby databases. (Further reading: Introduction to Oracle Flashback Technology)
|
1 |
SQL> alter database flashback on; |
Redo apply must be stopped to enable flashback database on the standby database:
|
1 2 3 4 5 |
DGMGRL> connect sys Password: Connected. DGMGRL> edit database 'dg2' set state='apply-off'; Succeeded. |
Standby Server
|
1 2 3 4 5 6 7 8 9 |
SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
Primary Server
Restart the Redo apply:
|
1 2 |
DGMGRL> edit database 'dg2' set state='apply-on'; Succeeded. |
The Observer
The server dg3 will act as the observer in the Fast-Start Fail Over configuration. The Oracle client binaries have been installed with administrator option. Confirm the connectivity with both the primary and the standby databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@dg3 ~]$ tnsping dg1 (Entry truncated) Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))) OK (140 msec) [oracle@dg3 ~]$ tnsping dg2 (Entry truncated) Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521))) OK (50 msec) [oracle@dg3 ~]$ |
The prerequisites for FSFO have been met. So FSFO can be configured, enabled and started. The FSFO observer process will be started using the DGMGRL session and will be logged to a file named observer.log:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
[oracle@dg3 admin]$ dgmgrl -logfile ./observer.log DGMGRL for Linux: Version 11.2.0.1.0 - Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@dg1 Password: Connected. DGMGRL> show fast_start failover Fast-Start Failover: DISABLED Threshold: 30 seconds Target: (none) 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) DGMGRL> edit configuration set property FastStartFailoverLagLimit=60; Property "faststartfailoverlaglimit" updated DGMGRL> enable fast_start failover; Enabled. DGMGRL> start observer; |
Primary Server
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DGMGRL> show configuration verbose Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: dg1 - Primary database dg2 - (*) Physical standby database (*) Fast-Start Failover target Fast-Start Failover: ENABLED Threshold: 30 seconds Target: dg2 Observer: dg3.localdomain Lag Limit: 60 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Configuration Status: SUCCESS |
Create a shell script and run the shell script in the background; create observer.sh (MOS ID: 1084681.1)
|
1 2 3 4 5 6 7 |
#!/bin/ksh dgmgrl -echo -logfile /home/oracle/dgmgrl.log << EOF connect sys/wissem@dg3 start observer EOF chmod +x observer.sh ./observer.sh & |
Where do you place the Observer?
That all depends on your requirements, the observer can be placed in a third site separated from primary and standby sites with a HA observer; this means 2 hosts; one observer process running on the first host and the other on standby (not running) on the second host. Only one observer can run at any one time.
References
https://docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm
Load comments