Author: Sanjay Kumar
After going through this post the reader will understand the purpose and importance of Oracle Data Guard. Performing the steps mentioned in the document will help in creating the Oracle Data guard without any issues. Note, this post assumes the reader has a good working knowledge as an Oracle DBA.
Oracle Data Guard – Introduction.
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
Assumptions:
Primary DB = DG1
Standby DB = DG2
Observer = DG3
DB Version = 11.2.0
O/S = Linux
1. Standby Database Creation
Install oracle binary 11.2.0. on Standby Site and insure the patch level on both the site must be same.
Configure listener.ora and tnsnames.ora on both the sites and then check the connectivity on both sites.
Start the standby database instance in NOMOUNT mode
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:
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.
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.
SQL> alter database recover managed standby database using current logfile disconnect;
Note that the current log sequence number on the standby is 10.
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.
SQL> alter system switch logfile;
Standby Server
We query the standby database. The logs were successfully transported and applied.
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
2 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
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.
[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
SQL> alter system set dg_broker_start=TRUE;
The Oracle Net listener on the primary database should have static service definitions:
[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.
[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
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:
SQL> select database_role from v$database;
DATABASE_ROLE ---------------- PRIMARY
Primary Server
The former primary database is now the new physical standby database:
SQL> select database_role from v$database;
DATABASE_ROLE ---------------- PHYSICAL STANDBY
Switch over to the former primary database:
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
3 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)
SQL> alter database flashback on;
Redo apply must be stopped to enable flashback database on the standby database:
DGMGRL> connect sys Password: ***** Connected.
DGMGRL> edit database 'dg2' set state='apply-off';
Succeeded.
Standby Server
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ YES
Primary Server
Restart the Redo apply
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:
[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)
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
[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
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)
#!/bin/ksh
dgmgrl -echo -logfile /home/oracle/dgmgrl.log << EOF
connect sys/*****@dg3
start observer
EOF
chmod +x observer.sh
./observer.sh &
Note:
All support notes referenced are provided by Oracle support. Please review for the latest updates on Oracle’s support site. http://support.oracle.com
