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.,
