Oracle DB 10g Clone using RMAN

http://www.dba-oracle.com/t_rman_clone_copy_database.htm https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2 https://www.youtube.com/watch?v=33f366qRlvE Use RMAN to copy/clone a database Oracle Tips by Burleson Consulting January 1, 2015 Question: I want to use RMAN to copy a database to a different server, in a different disk directory. Can I use RMAN to restore a database to another server? Answer: Cloning the database on the remote host requires two servers. Both servers should have Oracle Software installed on them. Make sure the Operating System is the same on both the servers. On the first server, we should have one database which will be duplicated to the second host. The first server will be named as "production server" and the database on it will be called "production database" (SID of the production database is db1). However, the second server will have a name as "auxiliary server" and the database which will be created on it will be called "auxiliary database" (SID of the auxiliary database is aux). The server name of the production database is PROD_SERVER and the server name of the auxiliary database is DUP_SERVER Also see: RMAN cloning on a local host Before cloning the production database, we should configure some initial actions as follows: Step 1 - Create a password file for the auxiliary instance It's possible to create a password file on the production server as well as on the auxiliary server. Because the passwords of target and auxiliary databases password files should not be similar as it's a security threat. However, it should be mentioned that when creating standby database from RMAN, the password file has to be same. But here, as we don't create standby database, the new password file should be created. Create a password file using ORAPWD utility as follows: $ orapwd file=$ORACLE_HOME/dbs/orapwaux password=test entries=3; Step 2 ? Configure auxiliary instance listener net configuration To clone the production database to auxiliary server, the connection should be configured between two servers i.e. must be able to access the production database from auxiliary server and vice versa. Thus, you need to make changes to tnsnames.ora and listener.ora files. If you haven't these files at $ORACLE_HOME/network/admin directory, create them using netca utility. First of all, change LISTENER.ORA file on auxiliary server and add the following lines: LISTENER.ORA #auxiliary instance (SID_DESC = (SID_NAME = aux) (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1) ) The reason why we made a change to listener.ora file is because we make auxiliary server to listen for the connections which comes from the production database. While duplicating the database, we'll connect to the auxiliary instance from the production database and as an auxiliary instance should understand and accept the connections which come from production database, we configured the listener.ora file. Without stopping and starting the listener, we apply new changes made to listener configuration by running the following command: $ lsnrctl reload Now, edit the TNSNAMES.ORA file at the production database and add the entry about auxiliary database to it as follows: AUX = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST= DUP_SERVER)(PORT=1521)) ) (CONNECT_DATA= (ORACLE_SID=aux) ) ) After changing the file, make sure you can connect to auxiliary instance by using tnsping utility $ tnsping aux Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST= DUP_SERVER)(PORT=1521))) (CONNECT_DATA= (ORACLE_SID=aux))) OK (40 msec) It means that we have access to the auxiliary server from production server and we successfully configured connection between two servers Step 3 ? Create a parameter file for an Auxiliary database We'll use this parameter file to start auxiliary instance in NOMOUNT mode. You can create pfile from spfile of production database and copy it to the auxiliary server. You can also create a parameter file which consists only of the following few parameters: DB_NAME: As we create duplicate database at the remote host, we can keep database name the same as the production database name. However, this parameter will be different when we duplicate database on the local host, because it?s impossible to have two databases with the same name in one host. CONTROL_FILES: This parameter defines the name of Control Files which will be restored to auxiliary instance DB_BLOCK_SIZE: This parameter must be the same as in the target instance COMPATIBLE: If production database uses specific compatible parameter, add this parameter to auxiliary parameter file with the same value SGA_TARGET: This parameter specifies the total size of all SGA components. To automatically size these components, make it the same as in the production database. However it is not mandatory that this be the same size as in the production database. The cloning may be done to create a Test / Development environment on a smaller server with lesser RAM. You need also to create the following directories on the auxiliary server and add them to the parameter file: adump bdump udump cdump Flash Recovery Area $ cd $ORACLE_HOME $ mkdir admin $ cd admin/ $ mkdir aux $ cd aux/ $ mkdir adump bdump cdump udump $ cd .. $ mkdir flash_recovery_area After changes are made to parameter file, it will look like as follows: DB_NAME=aux CONTROL_FILE=(/u02/oradata/aux/control01.ctl,/u02/oradata/aux/control02.ctl, /u02/oradata/aux/control03.ctl') DB_BLOCK_SIZE=8192 COMPATIBLE='10.2.0.1.0' SGA_TARGET=285212672 audit_file_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/adump' background_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/bdump' core_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/cdump' user_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/udump' db_recovery_file_dest='/u01/oracle/product/10.2.0/db_1/flash_recovery_area' db_recovery_file_dest_size=2147483648 Now, to make Oracle automatically uses this parameter file each time, create spfile as follows: $ export ORACLE_SID=aux $ export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1/ $ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 29 00:48:57 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> CREATE SPFILE FROM PFILE='/u01/oracle/product/10.2.0/db_1/dbs/pfile.ora'; File created Step 4 - Startup auxiliary instance in NOMOUNT mode SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes Step 5 - Backup the production database and copy all backup files to auxiliary server As the auxiliary instance is ready, you need to backup the production database and copy backup files to auxiliary server. As the backup will be recovered on the auxiliary server, the production database should be in ARCHIVELOG mode Connect to target instance and take the compressed backup of the database using nocatalog. The backup of the database can be taken by connecting to the RMAN catalog database as well $ export ORACLE_SID=db1 $ rman target / RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; Step 6 - Create datafile locations on an auxiliary server If you want to create the datafiles in the same directory as in the production database, create the folder of datafiles on the auxiliary server # mkdir u02 # cd u02/ # mkdir oradata # cd oradata/ # mkdir db1 # cd db1/ # chown -R oracle:oinstall /u02/ Step 7 - Copy the backup of the production database to the same directory residing on the auxiliary database server As we configured the database to use Flash Recovery Area, all RMAN backups will be stored under the flash recovery area folder. So the same folder should be created at the auxiliary server and all backup files should be copied to that folder. When you issue the DUPLICATE DATABASE command, RMAN searches the auxiliary server for the backup files which should reside at the same folder where they were at the production server. Identify the location of RMAN backups on the production server and create the same folders on the auxiliary server. Then copy all backup files from the production server to the auxiliary server Step 8 - Duplicate the Database After all above steps are done correctly, we're ready to create the clone database. For this, we connect to both production and auxiliary database from RMAN and issue the command "DUPLICATE TARGET DATABASE TO aux". $ rman target sys/my_pass auxiliary sys/test@aux connected to target database: DB1 (DBID=1298725119) connected to auxiliary database: AUX (not mounted) RMAN> DUPLICATE TARGET DATABASE TO aux NOFILENAMECHECK; Starting Duplicate Db at 06-DEC-09 ================= database opened Finished Duplicate Db at 06-DEC-09 After performing all steps, we have successfully duplicated the production database to the remote host (auxiliary server). RMAN performed the following steps automatically to duplicate the database : Allocates automatic auxiliary channel Creates a controlfile for the clone database Performs an incomplete recovery of the clone database using incremental backups and archived redo log files up to the last backed up archived redo log file. Shutdowns the database and opens it using RESETLOGS option. Generates a new unique DBID for the clone database But remember, all file locations were the same as in the production database. However, in case we need to change clone database?s directory structure, we can do it in different ways. Answer on RMAN cloning by Sorabh Harit: Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.0.0 The goal is restoring a database using RMAN on a different node with different backup directory structures and different database directory structures . You have a database backed up on NODE 1. You need to restore the database on NODE 2. The directory structure is different on NODE 2. You need to put the backups in a new directory structure in NODE 2, unlike as they were in NODE 1. You need to restore the database files into a new directory structure in NODE 2, unlike as they were in NODE 1. Solution Below is the procedure with an example of using RMAN to copy a database to another directory: Connect to the target database using rman and backup the database ---> NODE 1 $ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:29:33 2007Copyright ? 1982, 2005, Oracle. All rights reserved.connected to target database: ORA10G (DBID=3932056136)RMAN> backup database plus archivelog;Starting backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=143 recid=109 stamp=614392105channel ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp tag=TAG20070213T002825 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting backup at 13-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00003 name=/u01/oracle/product/oradata/ora10g/data/sysaux01.dbfinput datafile fno=00001 name=/u01/oracle/product/oradata/ora10g/data/system01.dbfinput datafile fno=00002 name=/u01/oracle/product/oradata/ora10g/data/undotbs01.dbfinput datafile fno=00004 name=/u01/oracle/product/oradata/ora10g/data/users01.dbfinput datafile fno=00005 name=/home/oracle/1.dbfinput datafile fno=00006 name=/u01/oracle/product/oradata/ora10g/data/sysaux02.dbfinput datafile fno=00007 name=/u01/oracle/product/oradata/ora10g/data/undotbs02.dbfchannel ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp tag=TAG20070213T002827 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55Finished backup at 13-FEB-07Starting backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=144 recid=110 stamp=614392165channel ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp tag=TAG20070213T002925 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting Control File and SPFILE Autobackup at 13-FEB-07piece handle=/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02 comment=NONEFinished Control File and SPFILE Autobackup at 13-FEB-07RMAN> exit Move the following files to the NODE 2 : + The database backup pieces + Controlfile backup piece + The parameter file i.e init.ora file Edit the PFILE on NODE 2 to change the environment specific parameters like . user_dump_dest = background_dump_dest = control_files = Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting the Oracle environment variables and start the database in nomount mode: [oracle@test-br test]$ export ORACLE_HOME=/u01/oracle/product/ora10g [oracle@test-br test]$ export ORACLE_SID=ora10g [oracle@test-br test]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@test-br test]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:36:55 2007 Copyright ? 1982, 2005, Oracle. All rights reserved. connected to target database (not started) RMAN> startup nomount Oracle instance started Total System Global Area 205520896 bytes Fixed Size 1218508 bytes Variable Size 75499572 bytes Database Buffers 121634816 bytes Redo Buffers 7168000 bytes Restore the controlfile from the backup piece. RMAN> restore controlfile from '/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02'; Starting restore at 13-FEB-07 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/oracle/product/oradata/ora10g/cntrl/control01.ctl Finished restore at 13-FEB-07 Mount the database. RMAN> alter database mount ; Now catalog the backup pieces that were shipped from NODE 1. RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp';Starting implicit crosscheck backup at 13-FEB-07allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 13-FEB-07Starting implicit crosscheck copy at 13-FEB-07using channel ORA_DISK_1Finished implicit crosscheck copy at 13-FEB-07searching for all files in the recovery areacataloging files...no files catalogedcataloged backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp recid=41 stamp=614393265RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp';cataloged backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp recid=42 stamp=614393292RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp';cataloged backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp recid=43 stamp=614393310 Get to know the last sequence available in the archivelog backup using the following command. RMAN > list backup of archivelog all; Rename the Redologfiles,so that they can be created in new locations when opened the database is opened in resetlogs. SQL> alter database rename file '/u01/oracle/product/oradata/ora10g/log/redo01.log' to '/home/oracle/test/log/redo01.log';.................. Now restore the datafiles to new locations and recover. RMAN> run { set until sequence set newname for datafile 1 to '/home/oracle/test/data/sys01.dbf'; set newname for datafile 2 to '/home/oracle/test/data/undotbs01.dbf'; set newname for datafile 3 to '/home/oracle/test/data/sysaux01.dbf'; set newname for datafile 4 to '/home/oracle/test/data/users01.dbf'; set newname for datafile 5 to '/home/oracle/test/data/1.dbf'; set newname for datafile 6 to '/home/oracle/test/data/sysaux02.dbf'; set newname for datafile 7 to '/home/oracle/test/data/undotbs02.dbf'; restore database; switch datafile all; recover database; alter database open resetlogs; } Here, Rampant TechPress author Kamran Agayev describes how to copy or clone a database using RMAN. Steps to clone a database using RMAN: 1: Create a password file on the destination server 2: Establish connectivity between the target and destination server (tnsnames.ora, sqlnet.ora) 3: Create the directories for the database files 4: Take the RMAN backup from the target server and copy it to the destination server. 5: Startup the destination database in nomount mode 6: Run the RMAN duplicate database command