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:
——————–
Thanks a lot for sharing. Very useful POC.
I have multiple Oradata like Oradata01 , oradata02 , oradata03 . for these cases how do I use the rename command in pfile
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
Hi, nice and useful info. here. hope this may also useful:
How to Duplicate a database using RMAN backup
Thanks,