Oracle DBA Tips Corner

     Return to the Oracle DBA Tips Corner.

click me  

Duplicate a Database in Flash Recovery Area using ASM to non-ASM Host using RMAN

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. Backup the Source Database using RMAN
  3. Create Copy of Backupsets to Local File System
  4. Create a Current Controlfile and SPFILE from the Source Database
  5. Copy RMAN Backupsets to Destination Host
  6. Restore and Recover Destination Database


Overview

Your current backup and recovery strategy makes use of RMAN and creates its backupsets (backup pieces) in the Flash Recovery Area using ASM. Everything is working fine until one day you need to have access to these backup pieces from a regular non-ASM file system. The question then becomes, how do I restore backupsets (backup pieces) that are currently in ASM to my non-ASM file system? This is a common question when the DBA wants to use the RMAN backupsets to duplicate or copy a database to another host on a non-ASM file system. It also provides a means to test the integrity of your backups on another host!

The quick answer is to use the BACKUP BACKUPSET (and BACKUP ARCHIVELOG if necessary) RMAN statement to copy backupsets (backup pieces) out of ASM to the local non-ASM file system. Using this method, you are essentially utilizing RMAN to create another copy of the backupset(s) to the local file system that you want to use for creating a duplicate database on a host that does not have ASM in use. This second copy of the backupset(s) will be on the local file system where you can then FTP them to another host and use to duplicate the database. When using the BACKUP BACKUPSET and BACKUP ARCHIVELOG statements, RMAN makes a record in the current control file that another copy of these backupset(s) exist and where they are located. You would then take a backup of the current control file and transfer it (along with the backupset(s) that are on the local file system) to the destination host. After copying all files to the destination host, it is just a matter of restoring and recovering to create a duplication of the source database. The use of these two RMAN statements as well as the restore / recovery methods are fully explained in this article.

In this article, I will fully explain how to extract a database named orcl (actually, making a second copy of the RMAN backup pieces) in the Flash Recovery Area using ASM to the local file system using RMAN. This will be considered the source database. We will then transfer the RMAN backup pieces (along with a backup controlfile and SPFILE) to a destination host and duplicate the database on a non-ASM file system - again using RMAN. This will be considered the target database.

In an effort to make this article more interesting, the source database will be a two-node RAC configuration while the target database will only be a single instance configuration. The source databases uses ASM for all physical files and has an ASM diskgroup defined for the user data (+ORCL_DATA1) and another for recovery files (+FLASH_RECOVERY_AREA). The following illustration and table fully explains the source and destination configurations.

Source Oracle Database Configuration - (Oracle RAC / ASM)
Machine Names: linux1.idevelopment.info
linux2.idevelopment.info
Oracle SIDs: orcl1
orcl2
Database Name: orcl
ASM Disk Groups: +ORCL_DATA1
+FLASH_RECOVERY_AREA
Operating System: Red Hat Linux 4 - (CentOS 4.3)
Oracle Release: Oracle10g Release 2 - (10.2.0.1.0)
Target Oracle Database Configuration - (Single Instance non-RAC / non-ASM)
Machine Name: linux3.idevelopment.info
Oracle SID: orcl
Database Name: orcl
File System for Oracle Database Files: db_recovery_file_dest = /u02/flash_recovery_area
db_create_file_dest = /u02/oradata/orcl
db_create_online_log_dest_1 = /u02/oradata/orcl
Operating System: Red Hat Linux 3 - (CentOS 3.4)
Oracle Release: Oracle10g Release 2 - (10.2.0.1.0)

  Please note that the tasks I provided in this article to duplicate a database using ASM to a non-ASM file system have been fully tested and work as documented. It may not, however, be the best and most efficient method for doing so. I just know it works for me!

Please contact me at jhunter@idevelopment.info if you know of a better method and would like to share it.


Backup the Source Database using RMAN

The first step is to take (or verify) a complete backup of the source database. As mentioned in the overview, my source database is a two-node RAC configuration with a Flash Recovery Area defined using ASM. The source database is already in archivelog mode and the archive destination set to use "USE_DB_RECOVERY_FILE_DEST": 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     83
Next log sequence to archive   84
Current log sequence           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
With regards to RMAN, all persistent configuration parameters are set to their default and I am not using an RMAN Recovery Catalog.

With that said, let's take a complete backup of the source database: 

$ 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_tag20060807t193947_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_tag20060807t194020_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_tag20060807t194020_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_tag20060807t194210_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


With the backup complete, let's take a look at the backupsets created in the Flash Recovery Area: 

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_tag20060807t193947_0.277.597872395

  List of Archived Logs in backup set 32
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    84      6294884    07-AUG-06 6338437    07-AUG-06
  2    118     6204595    07-AUG-06 6326110    07-AUG-06
  2    119     6326110    07-AUG-06 6338439    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_tag20060807t194020_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_tag20060807t194020_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_tag20060807t194210_0.264.597872533

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

  IMPORTANT!

Note that since this was a full (complete) backup, we get not only the database files (BS_KEY=33), but a separate backupset (BS_KEY=34) that contains a copy of the current controlfile and an SPFILE. This controlfile; however, will NOT be the one that we use for the duplicate database. The controlfile recorded in this latest backup reflects a single copy (Copy #1) of the backupsets as they exist in the Flash Recovery Area (+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/...) using ASM.

In the next section, we will use RMAN to make a copy of the required backupset(s) (which current exist in ASM) to the local file system so that they can be transferred over to the destination host (linux3.idevelopment.info) and used to create the destination database. When the RMAN copy completes, it will record in the controlfile the location of a second copy (Copy #2) of the backup pieces. Basically, the control will indicate that the backup pieces for backupsets we copied have two locations - one in ASM (+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/...) and another on the local file system (/u02/orarestore/...). When we use RMAN to restore the database from the destination host, it will use the location of the second copy to find the required backup pieces.

With regards to the backup we just performed, note the following items that will be important in the sections that follow:


Create Copy of Backupsets to Local File System

At this point, we have a complete (full) backup of the source database, however, it resides in ASM. In this section, we will use RMAN to create a copy of the required backupsets (backup pieces) that are part of the above RMAN database backup. We will copy the backupsets to the local disk / file system (/u02/orarestore). To do this, we use the BACKUP BACKUPSET statement. This commands will need to be executed from RMAN on the host of the source database.

The general syntax for the BACKUP BACKUPSET can take either a backupset key (bs_key) or a date string (default is DD-MON-YY) that represents the backupsets to copy: 

RMAN> backup backupset <bs_key [, bs_key]> format '<filesystem path>/%U';
or 
RMAN> backup backupset completed after 'date_string' format '<filesystem path>/%U';
For the example in this article, I only want to make a copy of one of the backupsets since it contains a full backup of all database files: 
$ 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_tag20060807t194020_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

  Note that I could have also elected to make a copy of all RMAN backupsets completed after a certain date and time. This, however, would include more backupsets than required to duplicate the source database: 
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

At this point, we have a full (complete) backup of the database that resides in the Flash Recovery Area using ASM. We also have a copy of one of the backupsets (BS_KEY=33) that exists on both ASM and the local file system. This backupset contains all of the database files required to duplicate the source database to the destination host.

We now need to create a small RMAN backupset that includes a copy of the current control (and SPFILE) from the source database. We will instruct RMAN to create this backupset to the local file system.

Remember, after we executed the BACKUP BACKUPSET statement in the previous section, RMAN recorded the fact that a second copy of that backupset exists - one in ASM (+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/...) and another on the local file system (/u02/orarestore/...).

Also note that we want a current copy of the SPFILE. We can create a single backupset that includes both by running the following from the source database: 

$ 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

Now that the RMAN backup pieces are available on the local file system, we can transfer them to the destination host. Verify that the directory /u02/orarestore exists on the destination host. From the source machine, copy all required backup pieces to the destination: 
$ cd /u02/orarestore
$ scp 11hq5jp5_1_2 source_controlfile linux3:/u02/orarestore
oracle@linux3's password: xxxxx
11hq5jp5_1_2                          100% 1148MB   6.7MB/s   02:52
source_controlfile                    100%   15MB  14.8MB/s   00:01

  The backup pieces being copied to the destination host must be copied to a directory of the same exact name (and file name) that was used when you performed the copy of the backupsets (BACKUP BACKUPSET). The current controlfile from the source database stored the name of this backup piece (/u02/orarestore/11hq5jp5_1_2) as the physical name and location of where RMAN should expect to find it.


Restore and Recover Destination Database

At this point, all of the required backup pieces to duplicate the source database have been copied to the destination machine. In this section, we will perform the tasks needed to restore and recover the source database on the destination host.

Note that the following tasks should all be completed on the destination host (i.e. linux3.idevelopment.info)! Before performing any of the tasks below, it is assumed that all Oracle environment variables have been set and that all directories have been created to host the new (destination) database. For example: 

$ su - oracle

$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/admin/orcl/bdump
$ mkdir -p /u01/app/oracle/admin/orcl/cdump
$ mkdir -p /u01/app/oracle/admin/orcl/dpdump
$ mkdir -p /u01/app/oracle/admin/orcl/pfile
$ mkdir -p /u01/app/oracle/admin/orcl/scripts
$ mkdir -p /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
The first step is to create an Oracle password file for the destination database: 
$ 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
In the next step, we need to restore the SPFILE backup to be used for the destination database. This makes use of the backupset that contains the SPFILE and current controlfile of the source database, which in this example the backup piece is /u02/orarestore/source_controlfile.

Before we can restore the SPFILE using RMAN we need to have a temporary instance up and running for the destination database. RMAN requires an instance up and running in order to restore the SPFILE. We can create a temporary initialization parameter file for orcl by defining only one parameter, that being the db_name. Create the temporary initialization file $ORACLE_HOME/dbs/initorcl.ora as follows:

Temporary $ORACLE_HOME/dbs/initorcl.ora File 
*.db_name='orcl'

After creating the temporary initialization parameter file, verify the $ORACLE_SID and start the temporary instance: 

$ echo $ORACLE_SID
orcl

$ sqlplus "/ as sysdba"

SQL> startup nomount
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1218004 bytes
Variable Size              58722860 bytes
Database Buffers           50331648 bytes
Redo Buffers                2973696 bytes


We can now restore the SPFILE backup that was taken from the source database: 

$ 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


Exit from RMAN and shutdown the temporary orcl instance: 

RMAN> exit

$ sqlplus "/ as sysdba"

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.


You can verify the existence of the restored SPFILE as well as removing the temporary initialization parameter file we created earlier: 

$ 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


The restored SPFILE cannot be used directly by the new destination database. One of the most significant modifications I need to make is to remove all cluster related parameters as the new destination database will only be a single instance configuration. Let's first convert the SPFILE to a text file so we can make the necessary modifications: 

$ sqlplus "/ as sysdba"

SQL> create pfile from spfile;

File created.


The following table shows a before and after image of my initorcl.ora file:

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.596122959'
*.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'

With the new modified initorcl.ora in place, we should convert it to an SPFILE and test that you can start the instance: 

$ sqlplus "/ as sysdba"

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Restore Controlfile
We can now restore the current controlfile from the same RMAN backup piece we used to extract the SPFILE from. The following assumes the orcl instance is already running: 
$ 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
Next, we need to restore the destination database. This is done through RMAN and makes use of the backup piece(s) that we copied over from the source database. After restoring the database, exit from RMAN: 
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
The final step is to recover and open the destination database. This is done through SQL*Plus: 
$ 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_.arc
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


Verify that any missing tempfiles that were part of any temporary tablespaces exist.

Clean out any extraneous ASM RMAN Files from Controlfile
Before you attempt to run an RMAN backup from the new duplicated database, clear out all extraneous files that exist in the current controlfile. This includes old backup pieces that are still recorded as being in the Flash Recovery Area of the ASM instance: 
$ 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_tag20060807t193947_0.277.597872395
53      33      1   1   EXPIRED     DISK        +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060807t194020_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_tag20060807t194020_0.278.597872521
55      35      1   1   EXPIRED     DISK        +FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t194210_0.264.597872533
deleted backup piece
backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t193947_0.277.597872395 recid=52 stamp=597872393
deleted backup piece
backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/nnndf0_tag20060807t194020_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_tag20060807t194020_0.278.597872521 recid=54 stamp=597872520
deleted backup piece
backup piece handle=+FLASH_RECOVERY_AREA/orcl/backupset/2006_08_07/annnf0_tag20060807t194210_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


Copyright (c) 1998-2010 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 08-Aug-2006 10:08:12 EDT
Page Count: 16831