Creating an ASM duplicate database from a non-ASM database

Here are some simple steps we can take to clone or create a duplicate database from a non-ASM file system to an ASM file system.

1) create a static listener.ora entry on target machine

 (SID_DESC =
     (GLOBAL_DBNAME = dupdb)
     (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
     (SID_NAME = dupdb)
    )

2) add a tns alias on the source machine – this will be used for the auxiliary connection

DUPDBAUX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dupdb)
    )
  )

3) Copy the init,ora and password file from source database to $ORACLE_HOME/dbs on target machine

4) Edit the following entries in the init.ora file

In our case we are not changing the database name on the target so we are leaving the db_name value unchanged. Otherwise we will assign the new target database name to the parameter db_name.

*.control_files=’+DATA’,’+DATA’ ( note – this will change and spfile will include the full ASM file name)
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+DATA’
*.db_create_online_log_dest_2=’+DATA’ (this will multiplex the redo log files – remove if not required)

Note – ensure the parameters db_file_name_convert and log_file_name_convert are NOT set.

5) In our case the directory structure on source and target machines is different, so we need to create the required directories for the bdump, cdump, adump and udump locations and make changes in the init.ora file accordingly.

*.audit_file_dest=’/u01/oracle/admin/dupdb/adump’
*.user_dump_dest=’/u01/oracle/admin/dupdb/udump’
*.background_dump_dest=’/u01/oracle/admin/dupdb/bdump’
*.core_dump_dest=’/u01/oracle/admin/dupdb/cdump’

6) Create the spfile from pfile on the target

7) Take a backup of the database and archivelogs on the source database. If the backups are going to tape, the target machine should be configured to access the same tape library. If we are taking backups to disk, we need to ensure that the same directory structure of the source machine where the disk backup is created exists on the target machine.

8) Copy the backup files from the source to the target machine. Ensure they are located in same directory name as source machine.

9) STARTUP NOMOUNT the target database

10) On source issue the RMAN command :

rman target / auxiliary sys/xxx@dupdbaux

11) Run the following RMAN command to create the duplicate database on the ASM file system

run {
allocate channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database to
dupdb;
}

12) Check the location of the datafiles and online redo log files – they are now on the ASM disk group +DATA

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/dupdb/datafile/system.487.717585195
+DATA/dupdb/datafile/undotbs1.493.717585195


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/dupdb/onlinelog/group_3.500.717585859
+DATA/dupdb/onlinelog/group_3.501.717585859
+DATA/dupdb/onlinelog/group_2.498.717585859
+DATA/dupdb/onlinelog/group_2.499.717585859
+DATA/dupdb/onlinelog/group_1.496.717585857
+DATA/dupdb/onlinelog/group_1.497.717585857

13) Note the location of the control files – the spfile has been updated to reflect the new ASM format control file names

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/dupdb/controlfile/current.494.717585221, +DATA/dupdb/controlfile/current.495.717585221