DUPLICATE DATABASE WITHOUT CONNECTION TO TARGET DATABASE

In this post, I will demonstrate how to duplicate a database from its backups without any
connection to the source database. This method can be used if source database is not
available .
********************************
  source database  orcl
  Duplicate database  orclt
********************************
Overview:
on the source  host
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
-  Copy these backup files to the server where you want to create the duplicate copy.
- CREATE PFILE FROM SOURCE DATABASE
on the target host
- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the backup files from the source database
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Startup the target database in nomount mode using modified parameter file
- Using RMAN  connect to the  duplicate database (orclt) as auxiliary instance
- Specify the location of the backups and duplicate the database orcl to orclt
Implementation
————–
*******************
on the source  host
*******************
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
—-
—–
oracle@source$mkdir/home/oracle/stage
oracle@source$. oraenv orcl
              rman target /
RMAN>backup database format ‘/home/oracle/stage/%U.bak';
     backup archivelog all format ‘/home/oracle/stage/arch_%r%_s_%t.bak’
 ;
     backup current controlfile format ‘/home/oracle/stage/control.bak';
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE=’/home/oracle/stage/initsource.ora’
    FROM SPFILE;
*****************
 on the target host.
*****************
– Make a staging folder for backups and pfile
oracle@dest$mkdir -p /home/oracle/stage
– create other required  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 backup files from the source host
 # scp source:/home/oracle/stage/*.bak /home/oracle/stage/
– Copy pfile of source database (orcl)
 # scp source:/home/oracle/stage/initsource.ora /home/oracle/stage/inittarget.ora
– 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
 
– 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
– Edit the initialization parameter file from the main database.
$vi /home/oracle/stage/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 sorce 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”)
– Now set the Oracle SID as the duplicated database SID:
$ . oraenv
ORACLE_SID = [orclt] ?
– Startup the target 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  duplicate database (orclt) as auxiliary instance
$. oraenv
   orclt
$rman 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
– If duplicate database has the same directory structure as source (on a different host)
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ nofilenamecheck;
OR
– If duplicate database has different directory structure from source
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ ;
– 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;
     conn sys/oracle@orcl as sysdba
     select dbid from v$database;
——————————————————————————————–
Related links:
                                                                                                        ——————–

4 thoughts on “DUPLICATE DATABASE WITHOUT CONNECTION TO TARGET DATABASE

  1. I have multiple Oradata like Oradata01 , oradata02 , oradata03 . for these cases how do I use the rename command in pfile

    1. Hi Arnab

      You can use as many pairs of primary and standby replacement strings as required. You can use single or double quotation marks.

      DB_FILE_NAME_CONVERT = ‘string1′ , ‘string2′ , ‘string3′ , ‘string4′ , …
      Where:

      string1 is the pattern of the primary database filename
      string2 is the pattern of the standby database filename
      string3 is the pattern of the primary database filename
      string4 is the pattern of the standby database filename

      The following are example settings that are acceptable:

      DB_FILE_NAME_CONVERT = ‘/dbs/t1/’,’/dbs/t1/s_’,’dbs/t2/ ‘,’dbs/t2/s_’

      Pls refer to following link: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams054.htm

      regards
      Anju

Leave a Reply to Arnab Saha Cancel reply