Implementing Oracle Data Guard in 11g release 2 Version:-
Oracle Data Guard is a feature provided by Oracle database, which makes production database available ,secure and accessible in the event of unplanned outage (Disaster caused production database un-available) or planned outage (Patch application, testing) etc.
The basic working of Data Guard is like, a process of Oracle Database sends the Archive log(s) to another location and another process at new location apply those Archive logs to new database which is a cloned copy of the database.
Sounds simple! J
In Data guard terminology the sender of Archive logs are called Primary database and Receiver database is Standby database.
There are types of Standby Databases in Data Guard configuration:
A:- Physical Standby
B:- Logical Standby
C:- SnapShot Standby
In this post we will talk about Physical Standby database. Oracle Documents defines Physical Standby database as block by block copy of Production database. Mean if we run a query and use ROWID column then result set will be identical. I checked this on Primary and Standby database by querying as follows:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select rowid,ename,sal from scott.emp;
ROWID ENAME SAL
------------------ ---------- ----------
AAAR3sAAEAAAACXAAA SMITH 800
AAAR3sAAEAAAACXAAB ALLEN 1600
AAAR3sAAEAAAACXAAC WARD 1250
SQL> conn sys/root123@STDBY as sysdba
Connected.
SQL> select rowid,ename,sal from scott.emp;
ROWID ENAME SAL
------------------ ---------- ----------
AAAR3sAAEAAAACXAAA SMITH 800
AAAR3sAAEAAAACXAAB ALLEN 1600
AAAR3sAAEAAAACXAAC WARD 1250
Methods to create Physical Standby database: We have two methods given by Oracle to configure Standby database.
1:- Manual 2:- RMAN
We will discuss RMAN method to create Standby database.
RMAN methodology to create Standby database have few steps that I am briefing here. Later we will discuss it in detail.
1:- Backup current control file for Standby Database
2:- Full backup of database with Archive logs
3:- Take database backup to destination server
4:- Make entries in Tnsnames.ora, Listener.ora file on both Servers
5:- Create Database , Add some Standby database specific parameter
5:- Duplicate target database as Standby to another Auxiliary database
Now! We discuss these steps in more detail.
Unlike Primary Database, Standby Database uses another type of Control file. Primary database has “CURRENT” Control file type and Standby database uses “Standby” Control file type.
To check another kind of Control file, Please use Oracle 11g Documentation.
Following is the procedure to take backup using RMAN, later to be used for Standby Database creation.
C:\Documents and Settings\Administrator>rman target sys/root123@TEST
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 20 17:00:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2084526936)
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'D:\SEED_CTL_FILE.CTL' TAG='CTL_FILE.BKP';
RMAN>backup check logical full as compressed backupset database plus archivelog;
Now! Take this backup to would be Standby server.
Get Pfile, orapw<SID Name>.ora file of Primary database to Standby Server. Make changes in pfile of Standby database as given below.
test.__db_cache_size=230686720
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__oracle_base='C:\app\huser'#ORACLE_BASE set from environment
test.__pga_aggregate_target=247463936
test.__sga_target=314572800
test.__shared_io_pool_size=0
test.__shared_pool_size=272629760
test.__streams_pool_size=4194304
*.archive_lag_target=300 ß To force log switch after 5 mins.
*.audit_file_dest='C:\app\huser\admin\test\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\APP\HUSER\ORADATA\TEST\CONTROL01.CTL','C:\APP\HUSER\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='C:\app\huser\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='C:\app\huser'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'ß Gap Resolution,The client location for Fetch Archive log
*.fal_server='STDBY' ßGap Resolution, Server location
*.log_archive_config='DG_CONFIG=(TEST,STDBY)' ß All the database involoved in Data Guard configuration
*.log_archive_dest_1='LOCATION=F:\oradata\TEST\ MANDATORY ' ß Local Archiving
*.log_archive_format='TEST%S_%R_%T.ARC' Location
*.log_archive_dest_2='SERVICE=STDBY LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY'ßRedo Log shipping on Standby
1:- Server=STDBY (STDBY is TNSNAMES entry of Standby Database)
2:- LGWR process responsible of REDO shipping to Standby Site
3:- ASYNC Primary database w’nt wait of changes to be committed on Standby Site.
4:- VAILD_FOR=(ALL_LOGFILES,PRIMARY_ROLE):- The type of REDO log file valid for this Standby Server And The role in which Database can be opened.
5:- DB_Unique_Name=STDBY The DB_UNIQUE_NAME of the database. We need to give DB_UNIQUE_NAME here because the DB_NAME of both Primary and Standby remains same. Means if my Primary database has DB_NAME=TEST then DB_NAME of Standby Database will be TEST.
*.open_cursors=300
*.pga_aggregate_target=247463936
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=524288000
*.sga_target=524288000
*.standby_file_management='AUTO' What Structrual changes occur at primary goes automatically at Standby. Like Tblspace add,Rename DB file,Drop Tablespace
*.undo_tablespace='UNDOTBS1'
Now Check the Pfile of Standby Databse
test.__db_cache_size=230686720
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__oracle_base='C:\app\huser'#ORACLE_BASE set from environment
test.__pga_aggregate_target=247463936
test.__sga_target=314572800
test.__shared_io_pool_size=0
test.__shared_pool_size=272629760
test.__streams_pool_size=4194304
*.archive_lag_target=3600 Automatic log switching
*.audit_file_dest='C:\app\huser\admin\test\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='test' db Name
*.db_unique_name=’STDBY’ DB unique name in DG configuration
*.db_recovery_file_dest='C:\app\huser\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='C:\app\huser'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST' Client name for Archive Log Gap Sequence
*.fal_server='STDBY' Server name for Archive Log Gap Sequence
*.log_archive_config='DG_CONFIG=(TEST,STDBY)' DB taking part in DG configuration.
*.log_archive_dest_1='LOCATION=C:\APP\HUSER\ORADATA\STDBY\ARC\ MANDATORY '
*.log_archive_dest_2='SERVICE=STDBY LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY'
*.log_archive_format='TEST%S_%R_%T.ARC'
*.open_cursors=300
*.pga_aggregate_target=247463936
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=524288000
*.sga_target=524288000
*.standby_file_management='AUTO' Structural change automatically ocurrses
*.undo_tablespace='UNDOTBS1'
Reload Listener after adding service names of Primary and Standby Database.
C:\Documents and Settings\Administrator>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2012 14:15:24
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PUESws128.corp.hexaware.com)(PORT=1521)))
Services Summary...
Service "DEMOHR91" has 1 instance(s).
Instance "DEMOHR91", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "STDBY" has 2 instance(s).
Instance "STDBY", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:765 refused:1
LOCAL SERVER
Instance "stdby", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:10 refused:0 state:ready
LOCAL SERVER
Service "TEST" has 2 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:258 refused:8
LOCAL SERVER
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "stdbyXDB" has 1 instance(s).
Instance "stdby", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: PUESWS128, pid: 5484>
(ADDRESS=(PROTOCOL=tcp)(HOST=PUESws128.corp.hexaware.com)(PORT=2902))
The command completed successfully
Now our basic structure of Data Guard is ready. We need to do following further:
1:- Take backup of Primary Database to Standby Server
rman target sys/root123@test
----------Controlfile for Standby creation
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'D:\SEED_CTL_FILE.CTL' TAG='CTL_FILE.BKP';
------------DBF(s) backup for Database
RMAN>backup check logical full as compressed backupset database plus archivelog;
2:- Put Standby Database (STDBY) in Nomount stage using SPFILE
C:\Documents and Settings\Administrator>oradim -new -sid stdby -startmode m
Instance created.
C:\Documents and Settings\Administrator>set oracle_sid=STDBY
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 16:22:38 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=%ORACLE_HOME%\database\initSTDBY.ora nomount
ORACLE instance started.
Total System Global Area 740724736 bytes
Fixed Size 1377164 bytes
Variable Size 255855732 bytes
Database Buffers 478150656 bytes
Redo Buffers 5341184 bytes
SQL> create spfile from pfile;
File created.
SQL> shutdown immedaite;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 740724736 bytes
Fixed Size 1377164 bytes
Variable Size 255855732 bytes
Database Buffers 478150656 bytes
Redo Buffers 5341184 bytes
2:- Connect to target Database using Catalog db or not and with Auxiliary Database and clone the target database for Standby
C:\Documents and Settings\Administrator>rman target sys/root123@TEST auxiliary sys/root123@STDBY
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 20 17:00:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2084526936)
connected to auxiliary database: TEST (not mounted)
RMAN> list backup of datafile 1;
using target database control file instead of recovery catalog
RMAN> run
2> {
3> Allocate Auxiliary Channel ch1 Device TYpe Disk;
4> Duplicate Target database for Standby NoFileNameCheck DoRecover;
5> }
allocated channel: ch1
channel ch1: SID=10 device type=DISK
Starting Duplicate Db at 20-JUN-12
contents of Memory Script:
{
set until scn 1170271;
sql clone "alter system set control_files =
''C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F58SC_.CTL'' comment=
''Set by RMAN'' scope=spfile";
restore clone standby controlfile;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set control_files = ''C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F58SC_.CTL'' comment= ''Se
e=spfile
Starting restore at 20-JUN-12
channel ch1: starting datafile backup set restore
channel ch1: restoring control file
channel ch1: reading from backup piece F:\ORADATA\TEST\RMAN_BKP\C-2084526936-20120620-01
channel ch1: piece handle=F:\ORADATA\TEST\RMAN_BKP\C-2084526936-20120620-01 tag=TAG20120620T161613
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:02
output file name=C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F5B1X_.CTL
Finished restore at 20-JUN-12
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 1170271;
set newname for tempfile 1 to
"C:\APP\HUSER\ORADATA\STDBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF";
set newname for datafile 3 to
"C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF";
set newname for datafile 4 to
"C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF";
set newname for datafile 5 to
"C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to C:\APP\HUSER\ORADATA\STDBY\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-JUN-12
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF
channel ch1: restoring datafile 00002 to C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF
channel ch1: restoring datafile 00003 to C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF
channel ch1: restoring datafile 00004 to C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF
channel ch1: restoring datafile 00005 to C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF
channel ch1: reading from backup piece F:\ORADATA\TEST\RMAN_BKP\TEST_0BNE16BD_1_1.BKP
channel ch1: reading from backup piece F:\ORADATA\TEST\RMAN_BKP\TEST_0BNE16BD_1_1.BKP
channel ch1: piece handle=F:\ORADATA\TEST\RMAN_BKP\TEST_0BNE16BD_1_1.BKP tag=BACKUP_FOR_STANDBY
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:02:15
Finished restore at 20-JUN-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=786474262 file name=C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=786474262 file name=C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF
contents of Memory Script:
{
set until scn 1170271;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-JUN-12
starting media recovery
archived log for thread 1 with sequence 68 is already on disk as file F:\ORADATA\TEST\TEST0000000068_0786391138_0001.ARC
archived log file name=F:\ORADATA\TEST\TEST0000000068_0786391138_0001.ARC thread=1 sequence=68
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-JUN-12
Finished Duplicate Db at 20-JUN-12
released channel: ch1
RMAN>
Now! RMAN work is done.
The first query that I run on Standby Database is:
Select Name,controlfile_type,database_Role,open_mode,protection_mode,protection_leve from v$database
NAME CONTROLFILE_TYPE DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ----------------- --------- ---------------- ---------------- -------------------- -------------------- -------------------- ---------------
TEST STANDBY PHYSICAL STANDBY MOUNT MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
1 rows selected
Now! We need to start MRP process by default its not started. So we need to write something like follow:
SQL> Alter Database Recover Managed Standby Database Using Current Logfile Disconnect From Session;
Database altered.
After this a Back ground process (MRP01) will be started that will apply REDO logs to Standby Database and keep Standby and Primary in Sync.
To see transfer of REDO logs from Primary to Standby Site you following same query on both Database(s).
On Primary: Select sequence# ,applied,name from v$archived_log order by 1 desc
Seq# App Name
197 YES STDBY
197 NO F:\ORADATA\TEST\TEST0000000197_0786391138_0001.ARC
196 YES STDBY
196 NO F:\ORADATA\TEST\TEST0000000196_0786391138_0001.ARC
195 NO F:\ORADATA\TEST\TEST0000000195_0786391138_0001.ARC
195 YES STDBY
194 NO F:\ORADATA\TEST\TEST0000000194_0786391138_0001.ARC
194 YES STDBY
193 NO F:\ORADATA\TEST\TEST0000000193_0786391138_0001.ARC
193 YES STDBY
192 NO F:\ORADATA\TEST\TEST0000000192_0786391138_0001.ARC
192 YES STDBY
On Standby: Select sequence#,applied,name from v$archived_log order by 1 desc
SEQUENCE# APPLIED NAME
-----------------------------------------------------------------------------------------------------------------------------------
197 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000197_0786391138.0001
196 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000196_0786391138.0001
195 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000195_0786391138.0001
194 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000194_0786391138.0001
193 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000193_0786391138.0001
192 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000192_0786391138.0001
Starting with Oracle 11g, You can open your Standby database in READ-ONLY mode and MRP process keeps applying the Archived log in Back end.
For trouble shooting you can check v$archive_dest,v$archive_dest_status
Select Dest_name,Status,Type,Database_mode,recovery_mode,destination from $archive_dest_status
LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE F:\oradata\TEST\
LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY STDBY
2:
Select Dest_Name,Status,Destination,target,error from v$archive_dest
LOG_ARCHIVE_DEST_1 VALID F:\oradata\TEST\ PRIMARY
LOG_ARCHIVE_DEST_2 VALID STDBY STANDBY
Views to trouble shoot the Data Guard
V$ARCHIVE_GAP,v$ARCHIVE_PROCESSES,V$DATABASE, V$ARCHIVE,V$PROXY_ARCHIVEDLOG, V$ARCHIVED_LOG,V$ARCHIVE_GAP, V$ARCHIVE_PROCESSES, V$ARCHIVE_DEST ,V$ARCHIVE_DEST_STATUS,V$PROXY_ARCHIVELOG_DETAILS,V$BACKUP_ARCHIVELOG_DETAILS,V$BACKUP_ARCHIVELOG_SUMMARY, V$PROXY_ARCHIVELOG_SUMMARY
In continuation, We will discuss new method of Oracle 11g to create Standby which saves from sending Backup to Standby Server.
For further knowledge refer following documents:
Any comment, suggestion, correction will be welcome.
