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:
——————
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