DUPLICATE DATABASE USING BACKUP WITH CONNECTION TO TARGET DATABASE

In this post, I will demonstrate how to duplicate a database using its backups.
This method requires connection with the target database also to read its controlfile
to get information about the backups.
********************************
  source database  orcl
  Duplicate database  orclt
***********************************
Overview :
on the source  host
- Backup Database , Archivelogs and controlfile
- Copy these backup files to the server where you want to create the duplicate copy.
- Creat Pfile from source database

 

on the target host
- Add a line in the file /etc/oratab to reflect the database instance(orclt) you are going to copy
- create folders
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Startup the duplicate database (orclt) in nomount mode using modified parameter file
- Using RMAN  connect to the source database(orcl) as target database and duplicate database
  (orclt) as auxiliary instance
- duplicate the database orcl to orclt
Implementation :
———————-
on the source  host
———————-
– Make a folder to stage the backup
oracle$mkdir /home/oracle/stage
– Take the backup of the source database
oracle$. oraenv orcl
         rman target /
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
     backup database plus archivelog format ‘/home/oracle/stage/%U.rmb’;
The controlfile backup is also required. If you have configured the controlfile
autobackup, the backup would contain the controlfile as well. If you want to be sure,
 ors you have not configured controlfile autobackup, you can backup the controlfile
explicitly.
——-
- Creat Pfile from source database
——-
SQL>Create pfile=’/u01/app/oracle/oradata/orcl/initsource.ora’
    from spfile;
———————————————–
The rest of the steps occur on the target host.
———————————————–
———————————
 Copy the backup files to the server where you want to create the duplicate copy.
——————————-
$mkdir -p /home/oracle/stage
 scp sourcehost:/home/oracle/stage/*.rmb desthost::/home/oracle/stage/
– Add a line in the file /etc/oratab to reflect the database instance you are going to  copy:
orclt:/u01/app/oracle/product/11.2.0/db1:N
– Now set the Oracle SID as the duplicated database SID:
# . oraenv
ORACLE_SID = [orclt] ?
– create folders
 $mkdir -p /u01/app/oracle/oradata/orclt
  mkdir -p /u01/app/oracle/flash_recovery_area/orclt
  mkdir -p /u01/app/oracle/admin/orclt/adump
  mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy the initialization parameter file from the main database.
– If u are duplicating on the same host
$cp  /u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora
OR
– If u are duplicating on the different host
$scp  soucehost:/u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora
– Edit pfile
$vi /u01/app/oracle/oradata/orclt/inittarget.ora
   – Change db_name = orclt
   – Edit it to reflect the new locations that might be appropriate
     such as control file locations,audit dump destinations, datafile
     locations, etc.
   – add these lines –
     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
In case source and destination databases ae ASM, following lines can be added :
db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
————————————–
– Copy the password file as well
$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
OR
$ scp sourcehost:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
$ . oraenv
ORACLE_SID = [orclt] ?
– Startup the duplicate database in nomount mode using modified parameter file
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
- Using RMAN  connect to the source database(orcl) as target database and duplicate database
  (orclt) as auxiliary instance
$. oraenv
   orclt
$rman target sys/oracle@orcl auxiliary /
– duplicate the database orcl to orclt
– the command performs the following steps:
    * Creates an SPFILE
    * Shuts down the instance and restarts it with the new spfile
    * Restores the controlfile from the backup
    * Mounts the database
    * Performs restore of the datafiles. In this stage it creates the files in the
      converted names.
    * Recovers the datafiles up to the time specified and opens the database
RMAN>duplicate target database to orclt;
– check that duplicate database is up
$sqlplus / as sysdba
sql>conn hr/hr
    select * from tab;
– Note that  DBID is different from the main database so it can be backed up
   independently and using the same catalog as well.
SQL> select dbid from v$database;
      DBID
———-
3779358555
     conn sys/oracle@orcl as sysdba
    
     select dbid from v$database;
      DBID
———-
1326904854
———————————————————————————————–
Related links:
                                                ——————

One thought on “DUPLICATE DATABASE USING BACKUP WITH CONNECTION TO TARGET DATABASE

  1. HI Anju ,
    I will be thankful to you if you clear one of my doubt . We have rman backup with retention policy of redudancy 7.
    RMAN configuration parameters for database with db_unique_name PRAS10G are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
    CONFIGURE BACKUP OPTIMIZATION OFF;
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
    CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    Our backup shedule is daily we use to take L1 incremental backup and weekly L) backup .

    But i am quiet surprised when i fire report obsolete . It shows me backup pieces as obsolete where were taken just 4 days back. Can you please sime light on it why its so.
    Backup Piece 24876 05-NOV-13 /cummins/cummins/RMAN-L1/05_Nov_13/Databasefiles_cummins_skoo5tjh_27540_20131105
    Backup Set 24877 05-NOV-13
    Backup Piece 24877 05-NOV-13 /cummins/cummins/RMAN-L1/05_Nov_13/Databasefiles_cummins_sloo5tkp_27541_20131105
    Backup Set 24909 05-NOV-13
    Backup Piece 24909 05-NOV-13 /cummins/cummins/RMAN-L1/05_Nov_13/Controlfile_cummins_tdoo60d8_27565_20131105
    Backup Set 24910 05-NOV-13
    Backup Piece 24910 05-NOV-13 /cummins/cummins/RMAN-L1/05_Nov_13/cummins/backupset/2013_11_05/o1_mf_nnsnf_TAG20131105T052737_97k0g974_.bkp
    Backup Set 24912 05-NOV-13
    Backup Piece 24912 05-NOV-13 /cummins/cummins/RMAN-L0/05_Nov_13/Databasefiles_cummins_tpoo7jhj_27577_20131105
    Backup Set 24913 05-NOV-13
    Backup Piece 24913 05-NOV-13 /cummins/cummins/RMAN-L0/05_Nov_13/Databasefiles_cummins_tqoo7jik_27578_20131105
    Backup Set 24940 05-NOV-13
    Backup Piece 24940 05-NOV-13 /cummins/cummins/RMAN-L0/05_Nov_13/Controlfile_cummins_ucoo80cp_27596_20131105
    Backup Set 24941 05-NOV-13
    Backup Piece 24941 05-NOV-13 /cummins/cummins/RMAN-L0/05_Nov_13/cummins/backupset/2013_11_05/o1_mf_nnsnf_TAG20131105T233913_97m0f17s_.bkp
    Backup Set 24945 07-NOV-13
    Backup Piece 24945 07-NOV-13 /cummins/cummins/RMAN-L1/07_Nov_13/Databasefiles_cummins_uooob519_27608_20131107
    Backup Set 24944 07-NOV-13
    Backup Piece 24944 07-NOV-13 /cummins/cummins/RMAN-L1/07_Nov_13/Databasefiles_cummins_upoob52j_27609_20131107
    Backup Set 24987 07-NOV-13
    Backup Piece 24987 07-NOV-13 /cummins/cummins/RMAN-L1/07_Nov_13/Controlfile_cummins_vroobbdo_27643_20131107
    Backup Set 24988 07-NOV-13
    Backup Piece 24988 07-NOV-13 /cummins/cummins/RMAN-L1/07_Nov_13/cummins/backupset/2013_11_07/o1_mf_nnsnf_TAG20131107T060616_97pcgs1r_.bkp
    Backup Set 24990 08-NOV-13
    Backup Piece 24990 08-NOV-13 /cummins/cummins/RMAN-L1/08_Nov_13/Databasefiles_cummins_07oodpq1_27655_20131108
    Backup Set 24991 08-NOV-13
    Backup Piece 24991 08-NOV-13 /cummins/cummins/RMAN-L1/08_Nov_13/Databasefiles_cummins_08oodprj_27656_20131108
    Backup Set 25023 08-NOV-13
    Backup Piece 25023 08-NOV-13 /cummins/cummins/RMAN-L1/08_Nov_13/Controlfile_cummins_0vooe2fa_27679_20131108
    Backup Set 25024 08-NOV-13
    Backup Piece 25024 08-NOV-13 /cummins/cummins/RMAN-L1/08_Nov_13/cummins/backupset/2013_11_08/o1_mf_nnsnf_TAG20131108T065153_97s2jb06_.bkp
    Backup Set 25026 10-NOV-13
    Backup Piece 25026 10-NOV-13 /cummins/cummins/RMAN-L1/10_Nov_13/Databasefiles_cummins_1looj39u_27701_20131110
    Backup Set 25027 10-NOV-13
    Backup Piece 25027 10-NOV-13 /cummins/cummins/RMAN-L1/10_Nov_13/Databasefiles_cummins_1mooj3as_27702_20131110
    Backup Set 25085 10-NOV-13
    Backup Piece 25085 10-NOV-13 /cummins/cummins/RMAN-L1/10_Nov_13/Controlfile_cummins_37oojdvq_27751_20131110
    Backup Set 25086 10-NOV-13
    Backup Piece 25086 10-NOV-13 /cummins/cummins/RMAN-L1/10_Nov_13/cummins/backupset/2013_11_10/o1_mf_nnsnf_TAG20131110T073846_97yg068x_.bkp
    Backup Set 25088 11-NOV-13
    Backup Piece 25088 11-NOV-13 /cummins/cummins/RMAN-L1/11_Nov_13/Databasefiles_cummins_3joolo81_27763_20131111
    Backup Set 25089 11-NOV-13
    Backup Piece 25089 11-NOV-13 /cummins/cummins/RMAN-L1/11_Nov_13/Databasefiles_cummins_3koolo9t_27764_20131111

    Can you please share your expert comments

Your comments and suggestions are welcome!