Friday, February 5, 2016

Duplicate the database using Target database RMAN backup with ASM Migration

Oracle11gR2 comes with new features where we can duplicate the database from the Target Database to the Auxiliary Database  using RMAN backup without connecting to the Target database and rman catalog. Only thing what is required is,  full backup of the Target database.

High Level steps :

1. Have recent RMAN L0 backup of target database.
2. FTP the RMAN backup files from target server to auxiliary server
3. Create all the directories in auxiliary server
4. Start the instance(with nomount) in auxiliary instance
5. Duplicate the database in auxiliary instance.
6. create spfile in ASM disk
7. Database validation and verification

Target  DB info :

I am restoring the above database to the new host called ractest1. The RBDMS is already installed in ractest1 host and it is same as target database version.

Step1  Take RMAN L0 backup & Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.

usben_L0_backup.sh















Here is the log file for full backup.

RMAN>
connected to target database: USBEN (DBID=2379284599)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: T01
channel T01: SID=61 device type=DISK

executing command: SET COMMAND ID

Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current SPFILE in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16

Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current control file in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16

Starting backup at 16-FEB-16
channel T01: starting compressed incremental level 0 datafile backup set
channel T01: specifying datafile(s) in backup set
input datafile file number=00001 name=/data01/oradata/usben/system01.dbf
-- trimming the info here for brevity
input datafile file number=00003 name=/data01/oradata/usben/sysaux01.dbf
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:02:15
Finished backup at 16-FEB-16

Starting Control File and SPFILE Autobackup at 16-FEB-16
piece handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903950986_cd6dltf7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-16
released channel: T01
RMAN>
Recovery Manager complete.

usben_Arch_backup.sh














Here is the log file for archive log backup.
RMAN>
connected to target database: USBEN (DBID=2379284599)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
allocated channel: T01
channel T01: SID=59 device type=DISK

executing command: SET COMMAND ID

sql statement: alter system archive log current

Starting backup at 16-FEB-16
current log archived
channel T01: starting archived log backup set
channel T01: specifying archived log(s) in backup set
input archived log thread=1 sequence=267 RECID=261 STAMP=903951084
-- trimming the info here for brevity
input archived log thread=1 sequence=301 RECID=295 STAMP=903951315
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315 tag=TAG20160216T093515 comment=NONE
channel T01: backup set complete, elapsed time: 00:01:05
channel T01: deleting archived log(s)
archived log file name=/data02/oradata/arch/1_267_902782329.dbf RECID=261 STAMP=903951084
-- trimming the info here for brevity
archived log file name=/data02/oradata/arch/1_301_902782329.dbf RECID=295 STAMP=903951315
Finished backup at 16-FEB-16

Starting Control File and SPFILE Autobackup at 16-FEB-16
piece handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903951381_cd6dz606_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-16

released channel: T01

RMAN>

Recovery Manager complete.



Step2  FTP the RMAN backup files from target server to auxiliary server. 

The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server. Password file is placed under $ORACLE_HOME/dbs directory.

The below backup list has full backup, Archive log bakcup & Archive logs.





Step3  Create the necessary folders in auxiliary database.  Here i  am migrating the database from file system to ASM disk. This case, we don't need to create directory in the file system. I created the directory in ASM disk.

[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN

Add entry in /etc/oratab for usben database.

Edit the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.

initusben.ora

usben.__data_transfer_cache_size=0
usben.__db_cache_size=536870912
usben.__java_pool_size=16777216
usben.__large_pool_size=150994944
usben.__oracle_base='/ora/app/oracle'
usben.__pga_aggregate_target=671088640
usben.__sga_target=989855744
usben.__shared_io_pool_size=50331648
usben.__shared_pool_size=218103808
usben.__streams_pool_size=0
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='usben'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/ora/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)'
*.log_archive_dest_1='location=/backup/usben/arch'
*.log_archive_dest_state_1='enable'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Step4  Start the instance using the above parameter with nomount mode:
















Step5  Duplicate the database

restore.sh




















Start running the above script in background mode.

[oracle@RACTEST1 usben]$ nohup ./restore.sh &
[1] 14120
[oracle@RACTEST1 usben]$

Tailing the log file while restoring the database.
tail -f  restore.log

Here is the complete restore log file.


Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 17 13:23:26 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to auxiliary database: USBEN (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=34 device type=DISK

Starting Duplicate Db at 17-FEB-16

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/USBEN/CONTROLFILE/current.316.904051433'', ''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''USBEN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''USBEN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/backup/usben/rman/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.316.904051433'', ''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK

Starting restore at 17-FEB-16

channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:02
output file name=+DATA/USBEN/CONTROLFILE/current.316.904051433
output file name=+DATA/USBEN/CONTROLFILE/current.295.904051435
Finished restore at 17-FEB-16

database mounted

contents of Memory Script:
{
   set until scn  2534513;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-FEB-16

channel a1: starting datafile backup set restore
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to +DATA
channel a1: restoring datafile 00002 to +DATA
-- trimming the info here for brevity
channel a1: restoring datafile 00006 to +DATA
channel a1: restoring datafile 00007 to +DATA
channel a1: reading from backup piece /backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
channel a1: piece handle=/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 tag=USBEN_L0_20160216_0927
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:07:45
Finished restore at 17-FEB-16

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=904051938 file name=+DATA/USBEN/DATAFILE/system.309.904051473
datafile 2 switched to datafile copy
-- trimming the info here for brevity
input datafile copy RECID=14 STAMP=904051938 file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473

contents of Memory Script:
{
   set until scn  2534513;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-FEB-16

starting media recovery

archived log for thread 1 with sequence 302 is already on disk as file /backup/usben/rman/1_302_902782329.dbf
archived log for thread 1 with sequence 303 is already on disk as file /backup/usben/rman/1_303_902782329.dbf
-- trimming the info here for brevity
archived log for thread 1 with sequence 331 is already on disk as file /backup/usben/rman/1_331_902782329.dbf
archived log for thread 1 with sequence 332 is already on disk as file /backup/usben/rman/1_332_902782329.dbf
channel a1: starting archived log restore to default destination
channel a1: restoring archived log
archived log thread=1 sequence=267
channel a1: restoring archived log
archived log thread=1 sequence=268
channel a1: restoring archived log
archived log thread=1 sequence=269
-- trimming the info here for brevity
archived log thread=1 sequence=300
channel a1: restoring archived log
archived log thread=1 sequence=301
channel a1: reading from backup piece /backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
channel a1: piece handle=/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315 tag=TAG20160216T093515
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:55
archived log file name=/backup/usben/arch/1_267_902782329.dbf thread=1 sequence=267
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_267_902782329.dbf RECID=66 STAMP=904051990
archived log file name=/backup/usben/arch/1_268_902782329.dbf thread=1 sequence=268
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_268_902782329.dbf RECID=44 STAMP=904051963
-- trimming the info here for brevity
archived log file name=/backup/usben/arch/1_300_902782329.dbf thread=1 sequence=300
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_300_902782329.dbf RECID=65 STAMP=904051989
archived log file name=/backup/usben/arch/1_301_902782329.dbf thread=1 sequence=301
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_301_902782329.dbf RECID=64 STAMP=904051989
archived log file name=/backup/usben/rman/1_302_902782329.dbf thread=1 sequence=302
archived log file name=/backup/usben/rman/1_303_902782329.dbf thread=1 sequence=303
-- trimming the info here for brevity
archived log file name=/backup/usben/rman/1_331_902782329.dbf thread=1 sequence=331
archived log file name=/backup/usben/rman/1_332_902782329.dbf thread=1 sequence=332
media recovery complete, elapsed time: 00:05:50
Finished recover at 17-FEB-16
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''USBEN'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''USBEN'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Executing: create pfile='/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora' from spfile
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "USBEN" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA', '+DATA' ) SIZE 200 M ,
  GROUP   2 ( '+DATA', '+DATA' ) SIZE 200 M
 DATAFILE
  '+DATA/USBEN/DATAFILE/system.309.904051473'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for clone tempfile  2 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/USBEN/DATAFILE/usben_data.299.904051473",
 "+DATA/USBEN/DATAFILE/sysaux.314.904051473",
 "+DATA/USBEN/DATAFILE/undotbs1.302.904051473",
 "+DATA/USBEN/DATAFILE/example.306.904051473",
 "+DATA/USBEN/DATAFILE/users.296.904051473",
 "+DATA/USBEN/DATAFILE/usben_indx.310.904051473";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 2 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_data.299.904051473 RECID=1 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/sysaux.314.904051473 RECID=2 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473 RECID=3 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/example.306.904051473 RECID=4 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/users.296.904051473 RECID=5 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473 RECID=6 STAMP=904052369

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/usben_data.299.904051473
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/sysaux.314.904051473
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/example.306.904051473
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/users.296.904051473
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-FEB-16

RMAN>

Recovery Manager complete.

Step 6   Create the spfile in ASM disk.  We could do this step at the beginning too.  However, i do this step at the end for my convenience. 


sys@usben> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
-- Creating spfile in ASM disk
sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE;

File created.

sys@usben> !

-- Here i updated the parameter file to point to SPFILE in ASM disk.

[oracle@RACTEST1 dbs]$ cat initusben.ora
SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora'
[oracle@RACTEST1 dbs]$
-- Restart the database with spfile.
sys@usben> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@usben> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2923440 bytes
Variable Size             222299216 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
Database opened.
sys@usben> set echo on
sys@usben> set feedback on
sys@usben> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/USBEN/PARAMETERFILE/spfileusben.ora

sys@usben>


Step7  Verify all the files and make sure all files are moved to ASM disk. Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.

























Now the database is successfully restored in different host. The database is also migrated to ASM disk as part of restore.

Enjoy reading my blog!!


No comments: