RMAN: Database Clone, Refresh for Development

UAT Refresh with RMAN
Production site
oracledb1-> hostname
oracledb1.mycorpdomain.com
UAT
oracledb2-> hostname
oracledb2.mycorpdomain.com
1. Create the File system for Backup Dump on the UAT server
oracledb2-> cd dgprd
oracledb2-> mkdir adump bdump cdump udump pfile dpdump
oracledb2-> pwd
/u01/app/oracle/admin/dgprd
oracledb2-> ls -ltr
total 48
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 udump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 pfile
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 cdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 bdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 adump
2. Create the File system for the Oradata, RMAN, FLASH_RECOVERY_AREA on the UAT
oracledb2-> ls -ltr
total 40
drwx------ 2 root root 16384 Mar 28 13:57 lost+found
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:52 rman
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:52 dgprd
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:53 flash_recovery_area
3. List Incarnation - Test
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 CURRENT 1 02-JUN-07
4. Database Uptime, on Production - 2 Ways
SQL> !ps -efgrep pmon
oracle 3877 1 0 10:34 ? 00:00:00 ora_pmon_dgtest
oracle 5092 1 0 11:03 ? 00:00:00 ora_pmon_dgprd
SQL> select to_char(startup_time, 'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
2 FROM sys.v_$instance;
DB Startup Time
--------------------
02-JUN-2007 11:03:14
5. Create a pfile from spfile on the Production database(It will be done at the backup time)
SQL> create pfile='/oradata/rman/initdgprd.ora' from spfile;
File created.
SQL> !ls -ltr /oradata/rman/
total 6920
-rw-r----- 1 oracle oinstall 7061504 Jun 2 11:07 snapcontrolfile_dgprd.f
-rw-r--r-- 1 oracle oinstall 1025 Jun 2 11:23 initdgprd.ora
6. Configure the device type and the FTP location
RMAN> configure channel device type disk format '/oradata/rman/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/rman/%U';
new RMAN configuration parameters are successfully stored

******************** BACKUP DATABASE on PRODUCTION NOW *******************
7. RMAN> backup database plus archivelog;
Starting backup at 02-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-JUN-07
Starting backup at 02-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 02-JUN-07
Starting backup at 02-JUN-07
Finished backup at 02-JUN-07
Starting Control File and SPFILE Autobackup at 02-JUN-07
piece handle=/oradata/flash_recovery_area/DGPRD/autobackup/2007_06_02/o1_mf_s_656336693_4485cp8x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-JUN-07
RMAN>
***************************************************************************
8. NOW FTP THE FILEs !!!! Nothing but as Restoring from the TAPE backup.
*********************************************************************************************
oracledb1-> sftp 192.168.2.135
Connecting to 192.168.2.135...
The authenticity of host '192.168.2.135 (192.168.2.135)' can't be established.
RSA key fingerprint is 32:ff:dd:a1:36:1c:0c:71:8a:ef:ad:09:73:ec:e4:1c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.135' (RSA) to the list of known hosts.
oracle@192.168.2.135's password:
sftp> pwd
Remote working directory: /export/home/oracle
sftp> cd /oradata
sftp> cd rman
snapcontrolfile_dgprd.f 100% 6896KB 6.7MB/s 00:01
sftp> mput *
Uploading 01jhtpnp_1_1 to /oradata/rman/01jhtpnp_1_1
01jhtpnp_1_1 100% 8811KB 8.6MB/s 00:00
Uploading 02jhtpns_1_1 to /oradata/rman/02jhtpns_1_1
02jhtpns_1_1 100% 553MB 8.1MB/s 01:07
Uploading 03jhtppj_1_1 to /oradata/rman/03jhtppj_1_1
03jhtppj_1_1 100% 2560 2.5KB/s 00:00
Uploading initdgprd.ora to /oradata/rman/initdgprd.ora
initdgprd.ora 100% 1025 1.0KB/s 00:00
Uploading snapcontrolfile_dgprd.f to /oradata/rman/snapcontrolfile_dgprd.f
snapcontrolfile_dgprd.f 100% 6896KB 6.7MB/s 00:01
***********************************************************************************************************
9. Login to the UAT server
1. Copy the Parameter file to the dbs location
/oradata/rman
oracledb2-> cp -p initdgprd.ora /u01/app/oracle/product/10.2.0/db_1/dbs/
oracledb2-> pwd
/oradata/rman
2. Perform Controlfile Restore *************************************************
oracledb2-> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 2 11:57:50 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initdgprd.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes
Fixed Size 1260648 bytes
Variable Size 58721176 bytes
Database Buffers 96468992 bytes
Redo Buffers 2932736 bytes
RMAN> shutdown abort;
Oracle instance shut down
******************** COPY the parameter file ****************
oracledb2-> cp -p initdgprd.ora /u01/app/oracle/product/10.2.0/db_1/dbs/
oracledb2-> pwd
***************************************************************
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 67109736 bytes
Database Buffers 96468992 bytes
Redo Buffers 2932736 bytes
RMAN> restore controlfile from autobackup;
Starting restore at 02-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata/flash_recovery_area
database name (or database unique name) used for search: DGPRD
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/02/2007 12:02:16
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN> restore controlfile from '/oradata/rman/snapcontrolfile_dgprd.f';

Starting restore at 02-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output filename=/oradata/dgprd/control01.ctl
output filename=/oradata/dgprd/control02.ctl
output filename=/oradata/dgprd/control03.ctl
Finished restore at 02-JUN-07

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
9 ****** Recover the Database Until time ***************************************
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore archivelog all;
Starting restore at 02-JUN-07
Starting implicit crosscheck backup at 02-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 02-JUN-07

Starting implicit crosscheck copy at 02-JUN-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-JUN-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/flash_recovery_area/DGPRD/autobackup/2007_06_02/o1_mf_n_656339154_4487rn3f_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination
>>>Removed Few
archive log thread=1 sequence=71
channel ORA_DISK_1: reading from backup piece /oradata/rman/05jhtsib_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/05jhtsib_1_1 tag=TAG20070602T123211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=72
channel ORA_DISK_1: reading from backup piece /oradata/rman/07jhtsk5_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/07jhtsk5_1_1 tag=TAG20070602T123309
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JUN-07
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 PARENT 1 02-JUN-07
2 2 DGPRD 224754742 CURRENT 225172 02-JUN-07
RMAN> reset database to incarnation 1;
database reset to incarnation 1
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 CURRENT 1 02-JUN-07
2 2 DGPRD 224754742 ORPHAN 225172 02-JUN-07

RMAN> run {
2> set until time "to_date('02-JUNE-2007 12:20:00','DD-MON-YYYY HH24:MI:SS')";
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }

executing command: SET until clause

Starting restore at 02-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/dgprd/system01.dbf
restoring datafile 00002 to /oradata/dgprd/undotbs01.dbf
restoring datafile 00003 to /oradata/dgprd/sysaux01.dbf
restoring datafile 00004 to /oradata/dgprd/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/rman/02jhtpns_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/02jhtpns_1_1 tag=TAG20070602T114355
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 02-JUN-07

Starting recover at 02-JUN-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 58 is already on disk as file /oradata/dgprd/arc/1_58_656333366.arc
archive log thread 1 sequence 59 is already on disk as file /oradata/dgprd/arc/1_59_656333366.arc
archive log filename=/oradata/dgprd/arc/1_58_656333366.arc thread=1 sequence=58
archive log filename=/oradata/dgprd/arc/1_59_656333366.arc thread=1 sequence=59
media recovery complete, elapsed time: 00:00:04
Finished recover at 02-JUN-07
sql statement: alter database open resetlogs
RMAN>