Custom Search . . .

Sunday, August 22, 2010

Step by Step to create physical standby database using RMAN


Dear blog readers and friends, In my busy schedule do not have time to write oracle tech..,  This week and next week planing to write something in oracle hints query's , data guard switch over etc.. 


Today I have done physical standby database using recovery manager and sharing my experience with you., Please let me know your feedback / comments

Step by Step to create physical standby database using RMAN

RMAN will take care the following procedure for creating standby database

a. Standby controlfile restore

b. Primary data files backup restore 

c. After restore standby controlfile, Rman recover the standby database by specify time or latest archive log generated. 

c. Finally, RMAN leave the standby database once it's mounted. If needed manually we can start MRP process. 

According to oracle documentation, RMAN cannot full automate for create standby database, there are some manual process require. 

Manual Process:

1. Starting database with out mount stage. 
2. Configuring INIT parameter in primary & standby database.  ( In windows env., manually need to create password file)
3. And we must have backup of database including current standby controlfile.

1. How to Configure NET Service (INIT) parameter in primary & standby database. 


2. Once completed net service configuration in primary database, copy same init parameter for standby database & needed some necessary changes like FAL_SERVER, FAL_CLIENT, LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST2

3. In standby database manually create password file & start the database with out mount stage

4. As i said, In production need to take complete backup with current standby controlfile. I used the below command for taking complete backup. 

Execute the below script in Target database:-

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 0 format 'e:\DBbackup\i001_%T_%u_s%s_p%p' database;
backup format 'e:\DBbackup\i001_arch_%T_%u_s%s_p%p' archivelog all;
backup current controlfile for standby format'e:\DBbackup\i001_CF_%T_%u_s%s_p%p' ; 
}


released channel: ORA_DISK_1
....
...
..


5. Once completed your full backup, EXIT your session, We need to connect AUXILIARY by using TARGET as well Catalog / NoCatalog

Ex:

C:\>rman target / nocatalog auxiliary sys/sys@dg_i001

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 23 02:56:06 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: I001 (DBID=2720927431)
using target database control file instead of recovery catalog
connected to auxiliary database: I001 (DBID=2720927431, not open)

Script for Standby Database creation by using Duplicate RMAN::-

run
{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby
nofilenamecheck;
#dorecover;
}

Duplicate Standby RMAN Logs::-


RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> allocate auxiliary channel c2 device type disk;
5> duplicate target database for standby
6> nofilenamecheck;
7> #dorecover;
8> }

allocated channel: c1
channel c1: sid=155 devtype=DISK

allocated channel: c2
channel c2: sid=158 devtype=DISK

Starting Duplicate Db at 23-AUG-10

contents of Memory Script:
{
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_
P1
channel c1: restored backup piece 1
piece handle=E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_P1 tag=TAG20100823T020325

channel c1: restore complete, elapsed time: 00:00:03
output filename=E:\ORADATA\I001\CONTROL01.CTL
output filename=E:\ORADATA\I001\CONTROL02.CTL
output filename=E:\ORADATA\I001\CONTROL03.CTL
Finished restore at 23-AUG-10

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "E:\ORADATA\I001\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "E:\ORADATA\I001\SYSTEM01.DBF";
   set newname for datafile  2 to
 "E:\ORADATA\I001\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "E:\ORADATA\I001\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\ORADATA\I001\USERS01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to E:\ORADATA\I001\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to E:\ORADATA\I001\UNDOTBS01.DBF
restoring datafile 00003 to E:\ORADATA\I001\SYSAUX01.DBF
channel c1: reading from backup piece E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORADATA\I001\SYSTEM01.DBF
restoring datafile 00004 to E:\ORADATA\I001\USERS01.DBF
channel c2: reading from backup piece E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1
channel c1: restored backup piece 1
piece handle=E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1 tag=TAG20100823T015352
channel c1: restore complete, elapsed time: 00:01:05
channel c2: restored backup piece 1
piece handle=E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1 tag=TAG20100823T015352
channel c2: restore complete, elapsed time: 00:01:40
Finished restore at 23-AUG-10

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=727710709 filename=E:\ORADATA\I001\SYSTEM01.DB
F
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=727710709 filename=E:\ORADATA\I001\UNDOTBS01.D
BF
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=727710709 filename=E:\ORADATA\I001\SYSAUX01.DB
F
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=727710710 filename=E:\ORADATA\I001\USERS01.DBF

Finished Duplicate Db at 23-AUG-10
released channel: c1
released channel: c2

RMAN>


Successfully completed standby database creating by using RMAN & both database are in Sync.,

Sync Checking:

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:21 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\ArchiveLog\i001
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence           23
SQL>
SQL> exit

C:\>sqlplus sys/sys@dg_i001 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\ArchiveLog\i001
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence           23
SQL>

Thanks.,