Monday 25 June 2012

Oracle 11g Data Guard (Physical)

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.