Go ahead with Oracle: August 2007

CREATING PHYSICAL STANDBY DATABASE USING RMAN

Table of Contents
1.0 Introduction

1.0.1 Purpose
1.0.2 Scope

2.0 working with RMAN for Standby database

2.0.1. What is the standby database
2.0.2. Overview
2.0.3. Initialization parameters and configuration
2.0.4 Pre-requisites
2.0.4.1 Target database must be mounted or open
2.0.4.2 A valid full database backup of the target database 2.0.5 Step required
2.0.5.1 Creating password file for standby server
2.0.5.2 Creating standby control file
2.0.5.3 Recording Last Sequence number
2.0.5.4 Backing up new archive logs
2.0.5.5 Making RMAN backup available on standby server
2.0.5.6 Creating same directory structure on standby
2.0.5.7 Starting standby instance
2.0.5.8 Ensure Oracle Net Connectivity to Standby (Auxiliary)
Database
2.0.5.8 Mount or Open the Target Database
2.0.5.9 Ensuring Necessary Backups and Archived Redo Log Files
2.0.6 Create Standby Database
2.0.7 put the Standby in Managed Recovery Mode
2.0.8 Verifying from alert log contents
2.0.9 Startup and Shutdown procedure of Primary and Standby DB

1.0 INTRODUCTION
1.0.1 Purpose
This document will aid understanding how to create a standby database using RMAN utility.
1.0.2 Scope
This article provides a quick introduction on how to create an Oracle standby database using the RMAN DUPLICATE TARGET DATABASE FOR STANDBY command on the Sun Solaris SunOS 5.9 operating environment. For this demonstration, we will create a standby database using RMAN's DUPLICATE command to a different host other than the primary database. Both hosts are running Sun Solaris SunOS 5.9.
Please refer following link for more details
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566.pdf

2.0 Creating physical standby database using RMAN

2.0.1 - What is the Oracle standby database (Physical and Logical)

The difference between physical and logical standby is in the way the changes from the primary are applied.
Both created as an exact image of the primary database. Both receive redo logs from the primary database. The difference is that a physical standby is mounted (but not open) and applies the received redo logs just as in the case of media failure recovery. A logical standby reconstructs SQL statements from the received redo logs and executes them. A logical standby is (must be) opened and can with some limitations be used for reporting or other purposes. A physical standby is mounted and generally cannot be used for any other purposes. You can however switch temporarily to read-only mode and query it but synchronization with the primary will be paused until you return to the recovery mode. In 10g Rel. 2 you can also open it read-write and then flashback to its original state

2.0.2 – Overview

RMAN cannot fully automate creation of the standby database because you must manually create an initialization parameter file for the standby database, start the standby instance without mounting the control file, and perform any Oracle Net setup (tnsnames.ora entries) required before performing the creation of the standby. Also, you must have RMAN backups of all datafiles available as well as a control file backup that is usable as a standby control file. All of these steps will be fully described in this article.
After the standby database is created, RMAN can back up the standby database and archived redo logs as part of your backup strategy. These standby backups are fully interchangeable with primary backups. In other words, you can restore a backup of a standby datafile to the primary database, and you can restore a backup of a primary datafile to the standby database

2.0.3- Configuration parameters (and Oracle initialisation parameters)

Primary database
Operating Environment= SunOS 5.9 Generic_118558-33 sun4u sparc SUNW,Sun-Fire-V240
Server Name= IGSMALPPS04
ORACLE_HOME= /oracle/product/9.2.0
Database SID=ORCL
Target TNS Names Entry=ORCL
Standby TNS Names Entry=SORCL
Archive Log Mode=TRUE
*._pga_max_size=41943040
*.aq_tm_processes=1
*.audit_trail='DB'
*.background_dump_dest='/oracle/admin/ORCL/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/oradata/ORCL/control01.ctl','/oracle/oradata/ORCL/control02.ctl','/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_cache_size=104857600
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='ORCL'
*.java_pool_size=31457280
*.job_queue_processes=10
*.large_pool_size=31457280
*.log_archive_format='Archive_%s_%t.ARC'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=20971520
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=104857600
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/ORCL/udump'
*.fal_client= 172.16.8.206
*.fal_server= 172.16.8.209
*.instance_name=ORCL
*.log_archive_dest_1= 'location=/oracle/oradata/ORCL/Archives'
*.log_archive_dest_2= 'service=SORCL optional reopen=15'
*.log_archive_dest_state_1= enable
*.log_archive_dest_state_2= enable
*.standby_archive_dest='/oracle/oradata/ORCL/Archives'
*.standby_file_management = auto

RMAN Configuration on Primary Database
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/oradata/RMAN_BKP/ORCL_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/oradata/RMAN_BKP/RMAN_BKP_%s_%t_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/9.2.0/dbs/snapcf_ORCL.f'; # default

Note: Full RMAN backup require of primary database on primary site.

Standby database server
Operating Environment= SunOS 5.9 Generic_118558-33 sun4u sparc SUNW,Sun-Fire-V240
Server Name= igsmalpps01
ORACLE_HOME= /oracle/oracle9i
Database SID=RORCL
Target TNS Names Entry=ORCL
Standby TNS Names Entry=SORCL
*._pga_max_size=41943040
*.aq_tm_processes=1
*.audit_trail='DB'
*.background_dump_dest='/oracle/admin/ORCL/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/oradata/ORCL/control01.ctl','/oracle/oradata/ORCL/control02.ctl','/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_cache_size=104857600
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='ORCL'
*.java_pool_size=31457280
*.job_queue_processes=10
*.large_pool_size=31457280
*.log_archive_format='Archive_%s_%t.ARC'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=20971520
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=104857600
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/ORCL/udump'
*.fal_client= 172.16.8.206
*.fal_server= 172.16.8.209
*.instance_name=ORCL
*.log_archive_dest_1= 'location=/oracle/oradata/ORCL/Archives'
*.log_archive_dest_2= 'service=ORCL optional reopen=15'
*.log_archive_dest_state_1= enable
*.log_archive_dest_state_2= enable
*.standby_archive_dest='/oracle/oradata/ORCL/Archives'
*.standby_file_management = auto

Standby (Auxiliary) Database Server

2.0.4 - Pre-requisites

2.0.4.1 Target database must be mounted or open

bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:02:48 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

SQL> startup open
ORACLE instance started.

Total System Global Area 337087024 bytes
Fixed Size 731696 bytes
Variable Size 218103808 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL>

2.0.4.2 A valid full database backup of the target database

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1049 B F A DISK 09-AUG-07 1 1 TAG20070809T125511
1087 B A A DISK 09-AUG-07 1 1 TAG20070809T130045
1155 B A A DISK 09-AUG-07 1 1 TAG20070809T163121
1159 B F A DISK 09-AUG-07 1 1 TAG20070809T163126
1175 B A A DISK 09-AUG-07 1 1 TAG20070809T163202
1179 B F A DISK 09-AUG-07 1 1
1188 B A A DISK 10-AUG-07 1 1 TAG20070810T140824
1192 B F A DISK 10-AUG-07 1 1 TAG20070810T140833
1208 B A A DISK 10-AUG-07 1 1 TAG20070810T140919
1212 B F A DISK 10-AUG-07 1 1

RMAN>

2.0.5 - Step required
2.0.5.1-Creating password file for standby server
bash-2.05$ cd $ORACLE_HOME
bash-2.05$ cd dbs
bash-2.05$ orapwd file=orapwORCL password=deep123 entries=5
bash-2.05$ pwd
/oracle/oracle9i/dbs
bash-2.05$

2.0.5.2 Creating standby control file
bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ rman target / catalog rman/rman@rman
Recovery Manager: Release 9.2.0.6.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1155865002)
connected to recovery catalog database
RMAN> backup current controlfile for standby format='/oracle/oradata/RMAN_BKP/standbyORCL.ctl';
Starting backup at 10-AUG-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 10-AUG-07
channel ORA_DISK_1: finished piece 1 at 10-AUG-07
piece handle=/oracle/oradata/ RMAN_BKP /standbyORCL.ctl comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-AUG-07
Starting Control File and SPFILE Autobackup at 10-AUG-07
piece handle=/oracle/oradata/RMAN_BKP/ORCL_c-1155865002-20070810-01 comment=NONE
Finished Control File and SPFILE Autobackup at 10-AUG-07
RMAN>

2.0.5.3 Recording Last Sequence number

bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:38:48 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
41

2.0.5.3 - Backing up new archive logs

bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ rman target / catalog rman/rman@rman

Recovery Manager: Release 9.2.0.6.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1155865002)
connected to recovery catalog database

RMAN> backup archivelog all delete input;
Starting backup at 10-AUG-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=31 recid=73 stamp=630254339
input archive log thread=1 sequence=32 recid=74 stamp=630254340
input archive log thread=1 sequence=33 recid=75 stamp=630254342
input archive log thread=1 sequence=34 recid=76 stamp=630254343
input archive log thread=1 sequence=35 recid=77 stamp=630254358
input archive log thread=1 sequence=36 recid=78 stamp=630254531
channel ORA_DISK_1: starting piece 1 at 10-AUG-07
channel ORA_DISK_1: finished piece 1 at 10-AUG-07
piece handle=/oracle/oradata/RMAN_BKP/RMAN_BKP_89_630254532_2pip1qu4_1_1
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/oracle/oradata/ORCL/Archives/Archive_31_1.ARC recid=73
stamp=630254339
archive log filename=/oracle/oradata/ORCL/Archives/Archive_32_1.ARC recid=74
stamp=630254340
archive log filename=/oracle/oradata/ORCL/Archives/Archive_33_1.ARC recid=75
stamp=630254342
archive log filename=/oracle/oradata/ORCL/Archives/Archive_34_1.ARC recid=76
stamp=630254343
archive log filename=/oracle/oradata/ORCL/Archives/Archive_35_1.ARC recid=77
stamp=630254358
archive log filename=/oracle/oradata/ORCL/Archives/Archive_36_1.ARC recid=78
stamp=630254531
Finished backup at 10-AUG-07

Starting Control File and SPFILE Autobackup at 10-AUG-07
piece handle=/oracle/oradata/RMAN_BKP/ORCL_c-1155865002-20070810-02
comment=NONE
Finished Control File and SPFILE Autobackup at 10-AUG-07

2.0.5.5 Making RMAN backup available on standby server

bash-2.05$ mkdir /oracle/oradata/RMAN_BKP
bash-2.05$
bash-2.05$ rcp /oracle/oradata/RMAN_BKP/*.* 172.16.8.209:/oracle/oradata/RMAN_BKP
bash-2.05$ rcp /oracle/oradata/RMAN_BKP/* 172.16.8.209:/oracle/oradata/RMAN_BKP
bash-2.05$

2.0.5.6 Creating same directory structure on standby
bash-2.05$ mkdir /oracle/oradata/admin
bash-2.05$ mkdir /oracle/oradata/admin/udump
bash-2.05$ mkdir /oracle/oradata/admin/bdump
bash-2.05$ mkdir /oracle/oradata/admin/cdump
bash-2.05$ mkdir /oracle/oradata/ORCL

2.0.5.7 Starting the standby instance

bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 16:30:09 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 337087024 bytes
Fixed Size 731696 bytes
Variable Size 218103808 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes

SQL>

2.0.5.8 Ensure Oracle Net Connectivity to Standby (Auxiliary) Database
On Primary Database Server

bash-2.05$ lsnrctl services
LSNRCTL for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:23:39
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IGSMALPPS04)(PORT=2030)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
bash-2.05$

bash-2.05$ tnsping ORCL

TNS Ping Utility for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:24:41
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 172.16.8.206)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
ORCL)))
OK (10 msec)
bash-2.05$ tnsping SORCL
TNS Ping Utility for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:24:49
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/oracle/product/9.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 172.16.8.209)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
SORCL)))
OK (0 msec)
bash-2.05$

On Standby Database Server

bash-2.05$ lsnrctl services
LSNRCTL for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:38:52
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

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 "finsp1" has 1 instance(s).
Instance "finsp1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 2 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
bash-2.05$

bash-2.05$ tnsping ORCL
TNS Ping Utility for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:39:58
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/oracle/oracle9i/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = igsmalpps01)(PORT = 1521))) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)

bash-2.05$ tnsping SORCL
TNS Ping Utility for Solaris: Version 9.2.0.6.0 - Production on 10-AUG-2007 16:40:01
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/oracle/oracle9i/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST =172.16.8.209)(PORT = 1521))) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = SORCL)))
OK (0 msec)
bash-2.05$

RMAN>

2.0.5.8 Mount or Open the Target Database
bash-2.05$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 16:33:47 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup open
ORACLE instance started.
Total System Global Area 337087024 bytes
Fixed Size 731696 bytes
Variable Size 218103808 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.

2.0.5.9 Ensuring Necessary Backups and Archived Redo Log Files
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1049 B F A DISK 09-AUG-07 1 1 TAG20070809T125511
1087 B A A DISK 09-AUG-07 1 1 TAG20070809T130045
1155 B A A DISK 09-AUG-07 1 1 TAG20070809T163121
1159 B F A DISK 09-AUG-07 1 1 TAG20070809T163126
1175 B A A DISK 09-AUG-07 1 1 TAG20070809T163202
1188 B A A DISK 10-AUG-07 1 1 TAG20070810T140824
1192 B F A DISK 10-AUG-07 1 1 TAG20070810T140833
1208 B A A DISK 10-AUG-07 1 1 TAG20070810T140919
1222 B F A DISK 10-AUG-07 1 1
1240 B A A DISK 10-AUG-07 1 1 TAG20070810T144212
1249 B F A DISK 10-AUG-07 1 1

2.0.6 Create Standby Database

bash-2.05$ export ORACLE_SID=ORCL
bash-2.05$ rman target / auxiliary sys/deep123@SORCL

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1155865002)
connected to auxiliary database: ORCL (not mounted)

RMAN> run{
2> set until sequence = 41 thread = 1;
3> allocate auxiliary channel ch1 type disk;
4> duplicate target database for standby dorecover nofilenamecheck ;
5> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

allocated channel: ch1
channel ch1: sid=14 devtype=DISK

Starting Duplicate Db at 13-AUG-07

printing stored script: Memory Script
{
restore clone standby controlfile to clone_cf;
replicate clone controlfile from clone_cf;
sql clone 'alter database mount standby database';
}
executing script: Memory Script

Starting restore at 13-AUG-07

channel ch1: starting datafile backupset restore
channel ch1: restoring controlfile
output filename=/oracle/oradata/ORCL/control01.ctl
channel ch1: restored backup piece 1
piece handle=/oracle/oradata/RMAN_BKP/stby_cfile.33ip245j_1_1 tag=TAG20070810T171947
params=NULL
channel ch1: restore complete
Finished restore at 13-AUG-07

replicating controlfile
input filename=/oracle/oradata/ORCL/control01.ctl
output filename=/oracle/oradata/ORCL/control02.ctl
output filename=/oracle/oradata/ORCL/control03.ctl

sql statement: alter database mount standby database

printing stored script: Memory Script
{
set until scn 4108109;
set newname for datafile 1 to
"/oracle/oradata/ORCL/system01.dbf";
set newname for datafile 2 to
"/oracle/oradata/ORCL/undotbs01.dbf";
set newname for datafile 3 to
"/oracle/oradata/ORCL/cwmlite01.dbf";
set newname for datafile 4 to
"/oracle/oradata/ORCL/drsys01.dbf";
set newname for datafile 5 to
"/oracle/oradata/ORCL/example01.dbf";
set newname for datafile 6 to
"/oracle/oradata/ORCL/indx01.dbf";
set newname for datafile 7 to
"/oracle/oradata/ORCL/odm01.dbf";
set newname for datafile 8 to
"/oracle/oradata/ORCL/tools01.dbf";
set newname for datafile 9 to
"/oracle/oradata/ORCL/users01.dbf";
set newname for datafile 10 to
"/oracle/oradata/ORCL/xdb01.dbf";
restore
check readonly
clone database
;
}
executing script: Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 13-AUG-07

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/oradata/ORCL/system01.dbf
restoring datafile 00002 to /oracle/oradata/ORCL/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/ORCL/cwmlite01.dbf
restoring datafile 00004 to /oracle/oradata/ORCL/drsys01.dbf
restoring datafile 00005 to /oracle/oradata/ORCL/example01.dbf
restoring datafile 00006 to /oracle/oradata/ORCL/indx01.dbf
restoring datafile 00007 to /oracle/oradata/ORCL/odm01.dbf
restoring datafile 00008 to /oracle/oradata/ORCL/tools01.dbf
restoring datafile 00009 to /oracle/oradata/ORCL/users01.dbf
restoring datafile 00010 to /oracle/oradata/ORCL/xdb01.dbf
channel ch1: restored backup piece 1
piece handle=/oracle/oradata/RMAN_BKP/RMAN_BKP_97_630263937_31ip2441_1_1
tag=TAG20070810T171857 params=NULL
channel ch1: restore complete
Finished restore at 13-AUG-07

printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=14 stamp=630499130 filename=/oracle/oradata/ORCL/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=15 stamp=630499130 filename=/oracle/oradata/ORCL/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=16 stamp=630499130 filename=/oracle/oradata/ORCL/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=17 stamp=630499130 filename=/oracle/oradata/ORCL/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=18 stamp=630499130 filename=/oracle/oradata/ORCL/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=19 stamp=630499130 filename=/oracle/oradata/ORCL/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=20 stamp=630499130 filename=/oracle/oradata/ORCL/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=21 stamp=630499130 filename=/oracle/oradata/ORCL/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=22 stamp=630499130 filename=/oracle/oradata/ORCL/xdb01.dbf

printing stored script: Memory Script
{
set until scn 4108109;
recover
standby
clone database
delete archivelog
;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 13-AUG-07

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=37
channel ch1: restoring archive log
archive log thread=1 sequence=38
channel ch1: restoring archive log
archive log thread=1 sequence=39
channel ch1: restoring archive log
archive log thread=1 sequence=40
channel ch1: restored backup piece 1
piece handle=/oracle/oradata/RMAN_BKP/RMAN_BKP_101_630497688_35ip98co_1_1
tag=TAG20070813T101448 params=NULL
channel ch1: restore complete
archive log filename=/oracle/oradata/ORCL/Archives/Archive_37_1.ARC thread=1
sequence=37
channel clone_default: deleting archive log(s)
archive log filename=/oracle/oradata/ORCL/Archives/Archive_37_1.ARC recid=4
stamp=630499134
archive log filename=/oracle/oradata/ORCL/Archives/Archive_38_1.ARC thread=1
sequence=38
channel clone_default: deleting archive log(s)
archive log filename=/oracle/oradata/ORCL/Archives/Archive_38_1.ARC recid=2
stamp=630499133
archive log filename=/oracle/oradata/ORCL/Archives/Archive_39_1.ARC thread=1
sequence=39
channel clone_default: deleting archive log(s)
archive log filename=/oracle/oradata/ORCL/Archives/Archive_39_1.ARC recid=1
stamp=630499133
archive log filename=/oracle/oradata/ORCL/Archives/Archive_40_1.ARC thread=1
sequence=40
channel clone_default: deleting archive log(s)
archive log filename=/oracle/oradata/ORCL/Archives/Archive_40_1.ARC recid=3
stamp=630499133
media recovery complete
Finished recover at 13-AUG-07
Finished Duplicate Db at 13-AUG-07
released channel: ch1

2.0.7 put the Standby in Managed Recovery Mode

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
ORCL MOUNTED

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

RMAN>

2.0.8 Verifying from alert log contents

ALTER DATABASE OPEN
Mon Aug 13 10:48:56 2007
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 45
Current log# 2 seq# 45 mem# 0: /oracle/oradata/ORCL/redo02.log
Successful open of redo thread 1
Mon Aug 13 10:48:57 2007
SMON: enabling cache recovery
Mon Aug 13 10:48:57 2007
Successfully onlined Undo Tablespace 1.
Mon Aug 13 10:48:57 2007
SMON: enabling tx recovery
Mon Aug 13 10:48:57 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Mon Aug 13 10:49:52 2007
ARC0: Begin FAL archive (thread 1 sequence 42 destination SORCL)
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
ARC0: Complete FAL archive (thread 1 sequence 42 destination SORCL)
Mon Aug 13 10:49:56 2007
ARC1: Begin FAL archive (thread 1 sequence 43 destination SORCL)
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
ARC1: Complete FAL archive (thread 1 sequence 43 destination SORCL)
Mon Aug 13 10:49:56 2007
ARC0: Begin FAL archive (thread 1 sequence 44 destination SORCL)
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
ARC0: Complete FAL archive (thread 1 sequence 44 destination SORCL)
Mon Aug 13 10:51:26 2007
ARC0: Evaluating archive log 2 thread 1 sequence 45
ARC0: Beginning to archive log 2 thread 1 sequence 45
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
Mon Aug 13 10:51:26 2007
Thread 1 advanced to log sequence 46
Current log# 3 seq# 46 mem# 0: /oracle/oradata/ORCL/redo03.log
Mon Aug 13 10:51:26 2007
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/oradata/ORCL/Archives/Archive_45_1.ARC'
ARC0: Completed archiving log 2 thread 1 sequence 45
Mon Aug 13 10:51:28 2007
Thread 1 advanced to log sequence 47
Current log# 1 seq# 47 mem# 0: /oracle/oradata/ORCL/redo01.log
Mon Aug 13 10:51:28 2007
ARC1: Evaluating archive log 3 thread 1 sequence 46
ARC1: Beginning to archive log 3 thread 1 sequence 46
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/oradata/ORCL/Archives/Archive_46_1.ARC'
ARC1: Completed archiving log 3 thread 1 sequence 46
Mon Aug 13 10:51:31 2007
Thread 1 advanced to log sequence 48
Current log# 2 seq# 48 mem# 0: /oracle/oradata/ORCL/redo02.log
Mon Aug 13 10:51:31 2007
ARC0: Evaluating archive log 1 thread 1 sequence 47
ARC0: Beginning to archive log 1 thread 1 sequence 47
Creating archive destination LOG_ARCHIVE_DEST_2: 'SORCL'
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/oradata/ORCL/Archives/Archive_47_1.ARC'
ARC0: Completed archiving log 1 thread 1 sequence 47

2.0.9 Startup and Shutdown procedure of Primary and Standby DB

Start up Sequence of Primary and standby database

1. Startup the standby database first
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 337087024 bytes
Fixed Size 731696 bytes
Variable Size 218103808 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.
SQL> alter database recover managed standby database disconnect;

Database altered.

2. Start-up the Primary Database second

Shut down Sequence of Primary and standby database

1. Shutdown the primary database first
2. Shutdown the standby database second

Go ahead with Oracle

My Blog List

Friday, August 31, 2007

CREATING PHYSICAL STANDBY DATABASE USING RMAN

Thursday, August 2, 2007

Using TAF with RAC

Blog Archive

About Me

BBC

New DBA

Hello there!!