Oracle 10g:Create New Disaster Recovery Solution :Data Gaurd



Necessary Steps to Make Disaster Recovery Solution :Data Guard.

This is for Physical standby Database.

**Prerequisite

*Database should be in Archive Log Mode.

*Database Files Location Should be same for Both Databases.

*Standby Database with Database Name is primary and instance Name(or SID) is standby.


1.Copy All Backup files and Control file to Standby Database Host.

Use same path as Primary Database.It is better to make same Physical structure as Primary and standby Database.

2.Update Primary Database Init.ora File.Change Log Archive Parameter.

Pfile for Primary Database.

###########################################
# Archive
###########################################
DB_Name=primary
Service_name=primary
DB_UNIQUE_NAME=primary
log_archive_format=ARC%S_%R.%T
LOG_ARCHIVE_DEST_1= 'LOCATION=C:\Archive   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2= 'SERVICE=standby LGWR ASYNC   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby) '
FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY

SQL> alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=standby LGWR ASYNC   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=standby' ;

###########################################
Pfile for Standby Database.

###########################################
# Archive
###########################################
*.db_name='primary'
*.Service_names='standby'
*.db_unique_name='standby'
log_archive_dest_1='LOCATION=C:\Archive'
log_archive_format=ARC%S_%R.%T
standby_archive_dest='D:\Archive'
standby_file_management=auto

FAL_SERVER=PRIMARY
FAL_CLIENT=STANDBY
###########################################


###########################################
# File Configuration
###########################################
db_file_name_convert=(D:\primary\primary,'C:\standby')
log_file_name_convert=(D:\primary\primary,'C:\standby')

##########################################
##########################################
control_files=("C:\standby\STANDBY.ctl")


alter system set db_file_name_convert='(E:\oracle\product\10.2.0\oradata\primary,F:\oracle\product\10.2.0\oradata\primary)' scope=spfile
/



alter system set log_file_name_convert='(E:\oracle\product\10.2.0\oradata\primary,F:\oracle\product\10.2.0\oradata\primary)' scope=spfile
/



**All Redo Logs Group same as primary Database in Standby also Same is mandate.if it is not then make them as primary database.

3.Restart Standby Database Using update pfile.Start in Nomount Mode.

startup nomount pfile=F:\oracle\product\10.2.0\admin\standby\pfile\init.ora

SQL>Create spfile from pfile='F:\oracle\product\10.2.0\admin\standby\pfile\init.ora';



5.Restart Primary Database with updated Parameter file.
Start Force Logging.Create Standby redo logs same as standby redo logs with same size.

startup pfile='E:\oracle\product\10.2.0\admin\primary\pfile\init.ora'

SQL>Create spfile from pfile='E:\oracle\product\10.2.0\admin\primary\pfile\init.ora';

Start Logging,

SQL>ALTER DATABASE FORCE LOGGING;


SQL>ALTER DATABASE ADD STANDBY LOGFILE (‘E:\oracle\product\10.2.0\oradata\primary\s_redo01.log’) SIZE 52428800;

SQL>ALTER DATABASE ADD STANDBY LOGFILE (‘E:\oracle\product\10.2.0\oradata\primary\s_redo02.log’) SIZE 52428800;

SQL>ALTER DATABASE ADD STANDBY LOGFILE (‘E:\oracle\product\10.2.0\oradata\primary\s_redo03.log’) SIZE 52428800;

 Create New Standby Control file.
SQL>alter database create standby controlfile as 'C:\Backup\Primary\standby.ctl';




6.Take Backup of primary database.

Here the Script to take backup of primary Database.

Take RMAN Backup. Database should be in Archive Mode.

set oracle_sid=primary

rman target /

run
{
allocate channel c1 type disk format 'C:\Backup\rman_primary_%T_%U.rman';
backup database plus archivelog;
release channel c1;
allocate channel c2 type disk format 'C:\Backup\rman_primary_standby_CTL_%T_%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c2;
}



7.Create Standby Database as Primary by RMAN Duplicate Command.

All RMAN Backup File and Standby Database File Should be transferred to Standby Database server.File location should be same of RMAN Backup files.

 Both Services Entry Should be added in TNSNAMES.ora in primary and standby database server.

Backup Copy of REDO Log should be manually pasted on standby Location.

8.Connect through Standby Server.

set oracle_sid=standby

C:\Documents and Settings\Administrator>rman target 'sys/primary@primary' auxiliary /

Standby Database not mounted so through root login connected to RMAN.

RMAN Script

RUN
{
  SET NEWNAME FOR DATAFILE 1 TO 'F:\oracle\product\10.2.0\oradata\primary\SYSTEM01.DBF';
  SET NEWNAME FOR DATAFILE 2 TO 'F:\oracle\product\10.2.0\oradata\primary\UNDOTBS01.DBF';
  SET NEWNAME FOR DATAFILE 3 TO 'F:\oracle\product\10.2.0\oradata\primary\SYSAUX01.DBF';
  SET NEWNAME FOR DATAFILE 4 TO 'F:\oracle\product\10.2.0\oradata\primary\USERS01.DBF';
  SET NEWNAME FOR DATAFILE 5 TO 'F:\oracle\product\10.2.0\oradata\primary\TBS_01';
  DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;
 
}


Output..


9>   DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;
10>
11> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 04-JUN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=25 devtype=DISK
contents of Memory Script:
{
   set until scn  659308;
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 04-JUN-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=C:\BACKUP\STANDBY_SQL.CTL
output filename=C:\BACKUP\STANDBY_SQL.CTL
Finished restore at 04-JUN-12
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
   set until scn  659308;
   set newname for tempfile  1 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\SYSTEM01.DBF";
   set newname for datafile  2 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\SYSAUX01.DBF";
   set newname for datafile  4 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\USERS01.DBF";
   set newname for datafile  5 to
 "F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\TBS_01";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\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 04-JUN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=25 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\SYSTEM01.DB
F
restoring datafile 00002 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\UNDOTBS01.D
BF
restoring datafile 00003 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\SYSAUX01.DB
F
restoring datafile 00004 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\USERS01.DBF
restoring datafile 00005 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\TBS_01
channel ORA_AUX_DISK_1: reading from backup piece C:\BACKUP\RMAN_PRIMARY_2012060
4_0CNCMSKR_1_1.RMAN
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\RMAN_PRIMARY_20120604_0CNCMSKR_1_1.RMAN tag=TAG20120604T1
51123
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:22
Finished restore at 04-JUN-12
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=785085626 filename=F:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PRIMARY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=785085627 filename=F:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PRIMARY\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=785085627 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\PRIMARY\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=785085627 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\PRIMARY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=785085627 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\PRIMARY\TBS_01
contents of Memory Script:
{
   set until scn  659308;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-JUN-12
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_AUX_DISK_1: reading from backup piece C:\BACKUP\RMAN_PRIMARY_2012060
4_0ENCMSMC_1_1.RMAN
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\RMAN_PRIMARY_20120604_0ENCMSMC_1_1.RMAN tag=TAG20120604T1
51212
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=D:\STANDBY\ARCHIVES\ARC00009_0784723505.001 thread=1 sequen
ce=9
channel clone_default: deleting archive log(s)
archive log filename=D:\STANDBY\ARCHIVES\ARC00009_0784723505.001 recid=1 stamp=7
85085628
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-JUN-12
Finished Duplicate Db at 04-JUN-12



9.Check Services.

10.Start Redo Apply Services.

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

11.Verifications

On Standby:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

On Primary :
Do some redo log switches
SQL> ALTER SYSTEM SWITCH LOGFILE;

On Standby:
  Verify that the recent archived log file is shifted to standby side and applied with the following commands.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

12.(Optional/When Required)Cancel the Standby Recovery Mode:
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

13.Troubleshoot

select message from v$dataguard_status where dest_id = 2;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;

select thread#, sequence#, first_change#, next_change# from v$log_history;
 

3 comments:

  1. For Change Mode,

    -- Maximum Availability.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
    ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

    -- Maximum Performance.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
    ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

    -- Maximum Protection.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
    ALTER DATABASE OPEN;

    ReplyDelete

Dear User,

Thank you very much for your kind response