Duplicate A Database In Flash Recovery Area Using Asm To Non_asm Using Rman

by Jeff Hunter, Sr. Database Administrator

Contents Overview Backup the Source Database using RMAN Create Copy of Backupsets to Local File System Create a Current Controlfile and SPFILE from the Source Database Copy RMAN Backupsets to Destination Host Restore and Recover Destination Database

Overview BACKUP BACKUPSETBACKUP ARCHIVELOGsecond ARCHIVELOG orclsource

copyBACKUP BACKUPSETBACKUP

databasetarget database

Source Oracle Database Configuration - (Oracle RAC / ASM) Machine Names: Oracle SIDs: Database Name: ASM Disk Groups:

Operating System: Oracle Release:

g

Target Oracle Database Configuration - (Single Instance non-RAC / nonASM) Machine Name: Oracle SID: Database Name: File System for Oracle Database Files: Operating System: Oracle Release:

g

Backup the Source Database using RMAN archivelog modeUSE_DB_RECOVERY_FILE_DEST SQL> archive log list Database log mode Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence

Archive Mode Enabled USE_DB_RECOVERY_FILE_DEST 83 84 84

SQL> SELECT name || ' = ' || value "Instance Parameters" 2 FROM v$spparameter 3 WHERE name in ('db_name', 'db_create_file_dest', 'db_recovery_file_dest'); Instance Parameters -------------------------------------------db_create_file_dest = +ORCL_DATA1 db_recovery_file_dest = +FLASH_RECOVERY_AREA db_name = orcl

$ rman target / RMAN> backup database plus archivelog delete input; Starting backup at 07-AUG-06 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=120 instance=orcl1 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=84 recid=43 stamp=597872381 input archive log thread=2 sequence=118 recid=42 stamp=597870048 input archive log thread=2 sequence=119 recid=44 stamp=597872508 channel ORA_DISK_1: starting piece 1 at 07-AUG-06

channel ORA_DISK_1: finished piece 1 at 07-AUG-06 piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060 807t193947_0.277.597872395 tag=TAG20060807T193947 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:29 channel ORA_DISK_1: deleting archive log(s) archive log filename=+FLASH_RECOVERY_AREA/orcl/archivelog/2006_08_07/thread_1_seq _84.263.597872377 recid=43 stamp=597872381 archive log filename=+FLASH_RECOVERY_AREA/orcl/archivelog/2006_08_07/thread_2_seq _118.278.597870037 recid=42 stamp=597870048 archive log filename=+FLASH_RECOVERY_AREA/orcl/archivelog/2006_08_07/thread_2_seq _119.279.597872501 recid=44 stamp=597872508 Finished backup at 07-AUG-06 Starting backup at 07-AUG-06 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00003 name=+ORCL_DATA1/orcl/datafile/sysaux.261.596123007 input datafile fno=00001 name=+ORCL_DATA1/orcl/datafile/system.259.596122973 input datafile fno=00002 name=+ORCL_DATA1/orcl/datafile/undotbs1.260.596122999 input datafile fno=00005 name=+ORCL_DATA1/orcl/datafile/undotbs2.264.596123035 input datafile fno=00004 name=+ORCL_DATA1/orcl/datafile/example.263.596123027 input datafile fno=00006 name=+ORCL_DATA1/orcl/datafile/users.265.596123043 channel ORA_DISK_1: starting piece 1 at 07-AUG-06 channel ORA_DISK_1: finished piece 1 at 07-AUG-06 piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060 807t194020_0.279.597872503 tag=TAG20060807T194020 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 07-AUG-06 channel ORA_DISK_1: finished piece 1 at 07-AUG-06 piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/ncsnf0_tag20060 807t194020_0.278.597872521 tag=TAG20060807T194020 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06 Finished backup at 07-AUG-06 Starting backup at 07-AUG-06 current log archived using channel ORA_DISK_1 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=85 recid=45 stamp=597872526 input archive log thread=2 sequence=120 recid=46 stamp=597872651 channel ORA_DISK_1: starting piece 1 at 07-AUG-06 channel ORA_DISK_1: finished piece 1 at 07-AUG-06

piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060 807t194210_0.264.597872533 tag=TAG20060807T194210 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 channel ORA_DISK_1: deleting archive log(s) archive log filename=+FLASH_RECOVERY_AREA/orcl/archivelog/2006_08_07/thread_1_seq _85.263.597872525 recid=45 stamp=597872526 archive log filename=+FLASH_RECOVERY_AREA/orcl/archivelog/2006_08_07/thread_2_seq _120.271.597872651 recid=46 stamp=597872651 Finished backup at 07-AUG-06

RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------32 78.17M DISK 00:00:22 07-AUG-06 BP Key: 52 Status: AVAILABLE Compressed: NO Tag: TAG20060807T193947 Piece Name: +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t193 947_0.277.597872395 List Thrd ---1 2 2

of Archived Logs in backup set 32 Seq Low SCN Low Time Next SCN ------- ---------- --------- ---------84 6294884 07-AUG-06 6338437 118 6204595 07-AUG-06 6326110 119 6326110 07-AUG-06 6338439

Next Time --------07-AUG-06 07-AUG-06 07-AUG-06

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------33 Full 1.12G DISK 00:01:26 07-AUG-06 BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20060807T194020 Piece Name: +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060807t194 020_0.279.597872503 List of Datafiles in backup set 33 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---1 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/system.259.596122973 2 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/undotbs1.260.596122999 3 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/sysaux.261.596123007 4 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/example.263.596123027 5 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/undotbs2.264.596123035 6 Full 6338527 07-AUG-06 +ORCL_DATA1/orcl/datafile/users.265.596123043 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ---------------

34

Full 14.80M DISK 00:00:04 07-AUG-06 BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20060807T194020 Piece Name: +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/ncsnf0_tag20060807t194 020_0.278.597872521 Control File Included: Ckp SCN: 6338895 Ckp time: 07-AUG-06 SPFILE Included: Modification time: 07-AUG-06 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------35 446.50K DISK 00:00:01 07-AUG-06 BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20060807T194210 Piece Name: +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t194 210_0.264.597872533 List Thrd ---1 2

of Archived Logs in backup set 35 Seq Low SCN Low Time Next SCN ------- ---------- --------- ---------85 6338437 07-AUG-06 6338959 120 6338439 07-AUG-06 6338961

Next Time --------07-AUG-06 07-AUG-06

IMPORTANT! +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/...

cop y linux3.idevelopment.inf o +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/.. ./u02/orarestore/...

• •

Create Copy of Backupsets to Local File System /u02/orarestoreBACKUP BACKUPSET BACKUP BACKUPSET RMAN> backup backupset format '/%U'; RMAN> backup backupset completed after 'date_string' format '/%U'; $ rman target / RMAN> backup backupset 33 format '/u02/orarestore/%U';

Starting backup at 07-AUG-06 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=144 instance=orcl1 devtype=DISK input backupset count=33 stamp=597872421 creation_time=07-AUG-06 channel ORA_DISK_1: starting piece 1 at 07-AUG-06 channel ORA_DISK_1: backup piece +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060807t194 020_0.279.597872503 piece handle=/u02/orarestore/11hq5jp5_1_2 comment=NONE channel ORA_DISK_1: finished piece 1 at 07-AUG-06 channel ORA_DISK_1: backup set complete, elapsed time: 00:01:14 Finished backup at 07-AUG-06 RMAN> backup backupset completed after "TO_DATE('07-AUG-2006 18:00:00', 'DD-MON-YYYY HH24:MI:SS')" format '/u02/orarestore/%U';

Create a Current Controlfile and SPFILE from the Source Database

BACKUP BACKUPSE T +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/.../u02/orarestore/...

$ rman target / RMAN> backup spfile include current controlfile format '/u02/orarestore/source_controlfile'; Starting backup at 07-AUG-06 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=123 instance=orcl1 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 07-AUG-06 channel ORA_DISK_1: finished piece 1 at 07-AUG-06 piece handle=/u02/orarestore/source_controlfile tag=TAG20060807T213351 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06 Finished backup at 07-AUG-06

Copy RMAN Backupsets to Destination Host /u02/orarestore $ cd /u02/orarestore $ scp 11hq5jp5_1_2 source_controlfile linux3:/u02/orarestore oracle@linux3's password: xxxxx

11hq5jp5_1_2 source_controlfile

100% 1148MB 100% 15MB

6.7MB/s 14.8MB/s

02:52 00:01

BACKUP BACKUPSET/u02/orarestore/11hq5jp5_1_2

Restore and Recover Destination Database linux3.idevelopment.info $ su - oracle $ $ $ $ $ $ $

mkdir mkdir mkdir mkdir mkdir mkdir mkdir

-p -p -p -p -p -p -p

/u01/app/oracle/admin/orcl/adump /u01/app/oracle/admin/orcl/bdump /u01/app/oracle/admin/orcl/cdump /u01/app/oracle/admin/orcl/dpdump /u01/app/oracle/admin/orcl/pfile /u01/app/oracle/admin/orcl/scripts /u01/app/oracle/admin/orcl/udump

$ mkdir -p /u02/oradata/orcl $ echo "orcl:/u01/app/oracle/product/10.2.0/db_2:N" >> /etc/oratab $ . oraenv ORACLE_SID = [TESTDB] ? orcl $ echo $ORACLE_SID orcl

Create Oracle Password File $ orapwd file=$ORACLE_HOME/dbs/orapworcl password=change_on_install $ ls -l $ORACLE_HOME/dbs/orapworcl -rw-r----- 1 oracle dba 1536 Aug 7 21:05 /u01/app/oracle/product/10.2.0/db_2/dbs/orapworcl

Restore SPFILE

/u02/orarestore/source_controlfile orcldb_name$ORACLE_HOME/dbs/initorcl.ora

Temporary $ORACLE_HOME/dbs/initorcl.ora File *.db_name='orcl' $ORACLE_SID $ echo $ORACLE_SID orcl $ sqlplus "/ as sysdba" SQL> startup nomount ORACLE instance started. Total System Global Area

113246208 bytes

Fixed Size Variable Size Database Buffers Redo Buffers

1218004 58722860 50331648 2973696

bytes bytes bytes bytes

$ rman target / RMAN> restore spfile from '/u02/orarestore/source_controlfile'; Starting restore at 07-AUG-06 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=39 devtype=DISK channel ORA_DISK_1: autobackup found: /u02/orarestore/source_controlfile channel ORA_DISK_1: SPFILE restore from autobackup complete Finished restore at 07-AUG-06 orcl RMAN> exit $ sqlplus "/ as sysdba" SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down.

$ ls -l $ORACLE_HOME/dbs/spfileorcl.ora -rw-r----- 1 oracle dba 3584 Aug 7 21:22 /u01/app/oracle/product/10.2.0/db_2/dbs/spfileorcl.ora $ rm $ORACLE_HOME/dbs/initorcl.ora

$ sqlplus "/ as sysdba" SQL> create pfile from spfile; File created. initorcl.ora

Original Instance Parameters - (initorcl.ora) orcl1.__db_cache_size=155189248 orcl2.__db_cache_size=167772160 orcl2.__java_pool_size=4194304 orcl1.__java_pool_size=8388608 orcl2.__large_pool_size=4194304 orcl1.__large_pool_size=4194304 orcl1.__shared_pool_size=113246208 orcl2.__shared_pool_size=104857600

orcl2.__streams_pool_size=0 orcl1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.cluster_database_instances=2 *.cluster_database=true *.compatible='10.2.0.1.0' *.control_files='+ORCL_DATA1/orcl/controlfile/current.256.596122957' ,'+FLASH_RECOVERY_AREA/orcl/controlfile/current.256.59 6122959' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='+ORCL_DATA1' *.db_domain='idevelopment.info' *.db_file_multiblock_read_count=16 *.db_name='orcl' *.db_recovery_file_dest='+FLASH_RECOVERY_AREA' *.db_recovery_file_dest_size=50465865728 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' orcl2.instance_number=2 orcl1.instance_number=1 *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=94371840 *.processes=150 *.remote_listener='LISTENERS_ORCL' *.remote_login_passwordfile='exclusive' *.sga_target=285212672 orcl2.thread=2 orcl1.thread=1 *.undo_management='AUTO' orcl2.undo_tablespace='UNDOTBS2' orcl1.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

New Instance Parameters - (initorcl.ora) *.__db_cache_size=167772160 *.__java_pool_size=8388608 *.__large_pool_size=4194304 *.__shared_pool_size=113246208 *.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='/u02/oradata/orcl/control01.ctl','/u02/oradata/orcl /control02.ctl' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='/u02/oradata/orcl' *.db_domain='idevelopment.info' *.db_file_multiblock_read_count=16 *.db_file_name_convert='+ORCL_DATA1','/u02/oradata' *.db_name='orcl' *.db_recovery_file_dest='/u02/flash_recovery_area' *.db_recovery_file_dest_size=50465865728 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.log_file_name_convert='+ORCL_DATA1','/u02/oradata' *.open_cursors=300 *.pga_aggregate_target=94371840

*.processes=150 *.remote_login_passwordfile='exclusive' *.sga_target=285212672 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump' initorcl.ora $ sqlplus "/ as sysdba" SQL> create spfile from pfile; File created. SQL> startup nomount ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers

285212672 1218992 92276304 188743680 2973696

bytes bytes bytes bytes bytes

Restore Controlfile orcl $ rman target /

RMAN> restore controlfile from '/u02/orarestore/source_controlfile'; Starting restore at 07-AUG-06 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output filename=/u02/oradata/orcl/control01.ctl output filename=/u02/oradata/orcl/control02.ctl Finished restore at 07-AUG-06

Restore the Database RMAN> alter database mount; using target database control file instead of recovery catalog database mounted RMAN> restore database; Starting restore at 07-AUG-06 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u02/oradata/orcl/ORCL/datafile/o1_mf_system_2fhvy3kr_.dbf restoring datafile 00002 to /u02/oradata/orcl/ORCL/datafile/o1_mf_undotbs1_2fhvy3ld_.dbf restoring datafile 00003 to /u02/oradata/orcl/ORCL/datafile/o1_mf_sysaux_2fhvy3kj_.dbf

restoring datafile 00004 to /u02/oradata/orcl/ORCL/datafile/o1_mf_example_2fhvy3or_.dbf restoring datafile 00005 to /u02/oradata/orcl/ORCL/datafile/o1_mf_undotbs2_2fhvy3o0_.dbf restoring datafile 00006 to /u02/oradata/orcl/ORCL/datafile/o1_mf_users_2fhvy3qy_.dbf channel ORA_DISK_1: reading from backup piece /u02/orarestore/11hq5jp5_1_2 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/orarestore/11hq5jp5_1_2 tag=TAG20060807T194020 channel ORA_DISK_1: restore complete, elapsed time: 00:02:26 Finished restore at 07-AUG-06 RMAN> exit

Recover and Open the Database $ sqlplus "/ as sysdba" SQL> recover database until cancel using backup controlfile; ORA-00279: change 6338527 generated at 08/07/2006 19:40:22 needed for thread 1 ORA-00289: suggestion : /u02/flash_recovery_area/ORCL/archivelog/2006_08_07/o1_mf_1_85_%u_.ar c ORA-00280: change 6338527 for thread 1 is in sequence #85 Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL> alter database open resetlogs; Database altered. SQL> exit

Clean out any extraneous ASM RMAN Files from Controlfile $ rman target /

RMAN> delete noprompt force backup; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------52 32 1 1 EXPIRED DISK +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t193 947_0.277.597872395 53 33 1 1 EXPIRED DISK +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060807t194 020_0.279.597872503 56 33 1 2 AVAILABLE DISK /u02/orarestore/11hq5jp5_1_2

54 34 1 1 EXPIRED DISK +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/ncsnf0_tag20060807t194 020_0.278.597872521 55 35 1 1 EXPIRED DISK +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t194 210_0.264.597872533 deleted backup piece backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060 807t193947_0.277.597872395 recid=52 stamp=597872393 deleted backup piece backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060 807t194020_0.279.597872503 recid=53 stamp=597872423 deleted backup piece backup piece handle=/u02/orarestore/11hq5jp5_1_2 recid=56 stamp=597875483 deleted backup piece backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/ncsnf0_tag20060 807t194020_0.278.597872521 recid=54 stamp=597872520 deleted backup piece backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060 807t194210_0.264.597872533 recid=55 stamp=597872532 Deleted 5 objects RMAN> delete noprompt force copy; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK specification does not match any archive log in the recovery catalog