Wednesday, June 20, 2012

Clone a database using RMAN DUPLICATE command

In this scenario, we want to create a database copy [AYXILIARY] using RMAN DUPLICATE command in a different server with the same OS.
In this post, I describe how to clone using RESTORE & RECOVER commands, but DUPLICATE is a lot easier.
There is no connection to the database we will copy [TARGET], using only tape backups.
Also, paths are different and memory available is fewer, so we need to create our new database with different parameters.

Our target database is named UC4DB and the auxiliary will be named UC4TEST.

First of all, install the same version of ORACLE binaries as in the target database and the backup software you are using, in our case Veritas NetBackup.
Set new environment variables in your profile:
export ORACLE_BASE=/oradata/database
export ORACLE_HOME=/oradata/database/product/11.2.0/dbhome_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=UC4TEST

Also, you must set variable NB_ORA_CLIENT.
I spent a few days, trying to resolve a weird behavior.
The first step of my RMAN DUPLICATE command was fetching SPFILE successfully.
The second step was to fetch control file, but I was getting the following errors:

RMAN-03002: failure of Duplicate Db command at 06/18/2012 13:13:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore


This was very strange, since SPFILE could be restored and control file could not, from the same backup piece.
Actually, NetBackup was not even trying to open a channel to get it.
I did not find anything helpful in Metalink.
The solution was offered by Veritas.
GENERAL ERROR: Oracle RMAN duplication restore attempt fails with RMAN error "ORA-27029 skgfrtrv: sbtrestore returned error" followed by "ORA-19511: Backup file not found in NetBackup catalog."
Although you pass NB_ORA_CLIENT through the SEND...PARMS() command, you must also set it as an environment variable in the terminal you are going to run RMAN.
So, before you run RMAN:
export NB_ORA_CLIENT=uc4db

Create $TNS_ADMIN/tnsnames.ora and insert in it the connection string to the database, where your RMAN repository resides.

The command we are going to execute is:
RUN
{
ALLOCATE AUXILIARY CHANNEL CH01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=bserver,NB_ORA_CLIENT=uc4db';
SET NEWNAME FOR DATABASE TO '/oradata/datafiles/%b';
DUPLICATE DATABASE UC4DB DBID 952593699 TO UC4TEST
UNTIL SCN 1010576
LOGFILE GROUP 1 ('/oradata/redo1/redo01.log',
                 '/oradata/redo2/redo01.log') SIZE 10M REUSE,
        GROUP 2 ('/oradata/redo1/redo02.log',
                 '/oradata/redo2/redo02.log') SIZE 10M REUSE
NOFILENAMECHECK
SPFILE
PARAMETER_VALUE_CONVERT 'UC4DB','UC4TEST'
SET CONTROL_FILES='/oradata/control/control01.ctl'
SET DB_CREATE_FILE_DEST='/oradata/datafiles'
SET AUDIT_FILE_DEST='/oradata/database/database/admin/uc4dbtest/adump'
SET DB_RECOVERY_FILE_DEST='/oradata/fast_recovery_area'
SET DIAGNOSTIC_DEST='/oradata/database'
SET DB_RECOVERY_FILE_DEST_SIZE='1G'
SET MEMORY_MAX_TARGET='1G'
SET MEMORY_TARGET='1G';
}

We will use one channel "ALLOCATE auxiliary CHANNEL CH01 TYPE 'SBT_TAPE';". Add more, if you need them.
The string "NB_ORA_SERV=bserver,NB_ORA_CLIENT=uc4db" passed through SEND command is ventor-specific. This is for NetBackup.
The "SET NEWNAME FOR DATABASE TO '/oradata/datafiles/%b';" will place all datafiles and tempfiles in /oradata/datafiles.
If you want to put them in more than one locations, you will have to use "SET NEWNAME FOR DATAFILE..." for each datafile and "SET NEWNAME FOR TEMPFILE..." for each tempfile.
If in your RMAN repository are more than one databases registered with the same name, you have to specify which one you want to copy.
This is done by specifying its DBID in the DUPLICATE command.
To find it, connect to you RMAN database, and execute:
SELECT DBID FROM RC_DATABASE
WHERE NAME='UC4DB';

If you omit "UNTIL SCN", RMAN will try to restore until value of NEXT_CHANGE# in the most recent row of RC_LOG_HISTORY view.
SELECT * FROM RC_LOG_HISTORY
WHERE DB_NAME='UC4DB'
ORDER BY FIRST_TIME DESC;

With "LOGFILE", we specify new locations and sizes for redo logs.
With "SPFILE", we instruct RMAN to fetch the target database's SPFILE. Everything under SPFILE are changes we need to make for our auxiliary database.
"PARAMETER_VALUE_CONVERT" will replace every "UC4DB" string in the initialization parameters with "UC4TEST".
The remaining "SET" commands change some initialization parameters about locations and the amount of memory Oracle will use.
You have to create these locations, before running the command.

We connect to the auxiliary database and recovery catalog, we "startup clone nomount" and we execute the script:
[oracle@oravm ~]$ rman auxiliary / catalog rman/******@rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 20 12:56:04 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
connected to auxiliary database (not started)

RMAN> startup clone nomount;

startup failed: ORA-01078: failure in processing system parameters Not an actual error, instance will start without a pfile.
LRM-00109: could not open parameter file '/oradata/database/product/11.2.0/dbhome_1/dbs/initUC4TEST.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes
Fixed Size                     2226456 bytes
Variable Size                130025192 bytes
Database Buffers              20971520 bytes
Redo Buffers                   5439488 bytes

RMAN> RUN
{
ALLOCATE auxiliary CHANNEL CH01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=bserver,NB_ORA_CLIENT=uc4db';
SET NEWNAME FOR DATABASE TO '/oradata/datafiles/%b';
DUPLICATE DATABASE UC4DB DBID 952593699 TO UC4TEST
UNTIL SCN 1010576
LOGFILE GROUP 1 ('/oradata/redo1/redo01.log',
                 '/oradata/redo2/redo01.log') SIZE 10M REUSE,
        GROUP 2 ('/oradata/redo1/redo02.log',
                 '/oradata/redo2/redo02.log') SIZE 10M REUSE
NOFILENAMECHECK
SPFILE
PARAMETER_VALUE_CONVERT 'UC4DB','UC4TEST'
SET CONTROL_FILES='/oradata/control/control01.ctl'
SET DB_CREATE_FILE_DEST='/oradata/datafiles'
SET AUDIT_FILE_DEST='/oradata/database/database/admin/uc4dbtest/adump'
SET DB_RECOVERY_FILE_DEST='/oradata/fast_recovery_area'
set DIAGNOSTIC_DEST='/oradata/database'
SET DB_RECOVERY_FILE_DEST_SIZE='1G'
SET MEMORY_MAX_TARGET='1G'
SET MEMORY_TARGET='1G';
}

allocated channel: CH01
channel CH01: SID=429 device type=SBT_TAPE
channel CH01: Veritas NetBackup for Oracle - Release 6.5 (2010042404)

sent command to channel: CH01

executing command: SET NEWNAME

Starting Duplicate Db at 20-JUN-12

contents of Memory Script:
{
   set until scn  1010576;
   restore clone spfile to  '/oradata/database/product/11.2.0/dbhome_1/dbs/spfileUC4TEST.ora';
   sql clone "alter system set spfile= ''/oradata/database/product/11.2.0/dbhome_1/dbs/spfileUC4TEST.ora''";
}

executing Memory Script

executing command: SET until clause

Starting restore at 20-JUN-12

WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel CH01: starting datafile backup set restore
channel CH01: restoring SPFILE  Restore SPFILE
output file name=/oradata/database/product/11.2.0/dbhome_1/dbs/spfileUC4TEST.ora
channel CH01: reading from backup piece 14ndr7i5_1_1
channel CH01: piece handle=14ndr7i5_1_1 tag=TAG20120618T095714
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:05:45
Finished restore at 20-JUN-12

sql statement: alter system set spfile= ''/oradata/database/product/11.2.0/dbhome_1/dbs/spfileUC4TEST.ora''

contents of Memory Script: Change initialization parameters' values
{
   sql clone "alter system set  db_name =
 ''UC4TEST'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
 ''/oradata/control/control01.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DB_CREATE_FILE_DEST =
 ''/oradata/datafiles'' comment=
 '''' scope=spfile";
   sql clone "alter system set  AUDIT_FILE_DEST =
 ''/oradata/database/database/admin/uc4dbtest/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''/oradata/fast_recovery_area'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DIAGNOSTIC_DEST =
 ''/oradata/database'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DB_RECOVERY_FILE_DEST_SIZE =
 1G comment=
 '''' scope=spfile";
   sql clone "alter system set  MEMORY_MAX_TARGET =
 1G comment=
 '''' scope=spfile";
   sql clone "alter system set  MEMORY_TARGET =
 1G comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''UC4TEST'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''/oradata/control/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  DB_CREATE_FILE_DEST =  ''/oradata/datafiles'' comment= '''' scope=spfile

sql statement: alter system set  AUDIT_FILE_DEST =  ''/oradata/database/database/admin/uc4dbtest/adump'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''/oradata/fast_recovery_area'' comment= '''' scope=spfile

sql statement: alter system set  DIAGNOSTIC_DEST =  ''/oradata/database'' comment= '''' scope=spfile

sql statement: alter system set  DB_RECOVERY_FILE_DEST_SIZE =  1G comment= '''' scope=spfile

sql statement: alter system set  MEMORY_MAX_TARGET =  1G comment= '''' scope=spfile

sql statement: alter system set  MEMORY_TARGET =  1G comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2235208 bytes
Variable Size                616563896 bytes
Database Buffers             444596224 bytes
Redo Buffers                   5541888 bytes

allocated channel: CH01
channel CH01: SID=122 device type=SBT_TAPE
channel CH01: Veritas NetBackup for Oracle - Release 6.5 (2010042404)

contents of Memory Script:
{
   set until scn  1010576;
   sql clone "alter system set  db_name =
 ''UC4DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''UC4TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2235208 bytes
Variable Size                616563896 bytes
Database Buffers             444596224 bytes
Redo Buffers                   5541888 bytes

allocated channel: CH01
channel CH01: SID=5 device type=SBT_TAPE
channel CH01: Veritas NetBackup for Oracle - Release 6.5 (2010042404)

Starting restore at 20-JUN-12

channel CH01: starting datafile backup set restore
channel CH01: restoring control file Restore control file
channel CH01: reading from backup piece 13ndr7g3_1_1
channel CH01: piece handle=13ndr7g3_1_1 tag=TAG20120618T095714
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:05:45
output file name=/oradata/control/control01.ctl
Finished restore at 20-JUN-12

database mounted

contents of Memory Script:
{
   set until scn  1010576;
   set newname for datafile  1 to
 "/oradata/datafiles/system01.dbf";
   set newname for datafile  2 to
 "/oradata/datafiles/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata/datafiles/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata/datafiles/uc4_data01.dbf";
   set newname for datafile  5 to
 "/oradata/datafiles/uc4_index01.dbf";
   set newname for datafile  6 to
 "/oradata/datafiles/users01.dbf";
   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

Starting restore at 20-JUN-12

channel CH01: starting datafile backup set restore
channel CH01: specifying datafile(s) to restore from backup set Restore datafiles to their new location
channel CH01: restoring datafile 00001 to /oradata/datafiles/system01.dbf
channel CH01: restoring datafile 00002 to /oradata/datafiles/sysaux01.dbf
channel CH01: restoring datafile 00003 to /oradata/datafiles/undotbs01.dbf
channel CH01: restoring datafile 00004 to /oradata/datafiles/uc4_data01.dbf
channel CH01: restoring datafile 00005 to /oradata/datafiles/uc4_index01.dbf
channel CH01: restoring datafile 00006 to /oradata/datafiles/users01.dbf
channel CH01: reading from backup piece ORACLE_ONLINE_UC4DB_1dne083c_1_1:786440300.dbf
channel CH01: piece handle=ORACLE_ONLINE_UC4DB_1dne083c_1_1:786440300.dbf tag=TAG20120620T073813
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:06:35
Finished restore at 20-JUN-12

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=786448844 file name=/oradata/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=786448844 file name=/oradata/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=786448844 file name=/oradata/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=786448844 file name=/oradata/datafiles/uc4_data01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=786448844 file name=/oradata/datafiles/uc4_index01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=786448844 file name=/oradata/datafiles/users01.dbf
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2235208 bytes
Variable Size                616563896 bytes
Database Buffers             444596224 bytes
Redo Buffers                   5541888 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2235208 bytes
Variable Size                616563896 bytes
Database Buffers             444596224 bytes
Redo Buffers                   5541888 bytes

allocated channel: CH01
channel CH01: SID=122 device type=SBT_TAPE
channel CH01: Veritas NetBackup for Oracle - Release 6.5 (2010042404)

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UC4TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/oradata/redo1/redo01.log', '/oradata/redo2/redo01.log' ) SIZE 10 M  REUSE,
  GROUP   2 ( '/oradata/redo1/redo02.log', '/oradata/redo2/redo02.log' ) SIZE 10 M  REUSE
 DATAFILE
  '/oradata/datafiles/system01.dbf'
 CHARACTER SET AL32UTF8

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata/datafiles/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata/datafiles/sysaux01.dbf",
 "/oradata/datafiles/undotbs01.dbf",
 "/oradata/datafiles/uc4_data01.dbf",
 "/oradata/datafiles/uc4_index01.dbf",
 "/oradata/datafiles/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/datafiles/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/oradata/datafiles/sysaux01.dbf RECID=1 STAMP=786448857
cataloged datafile copy
datafile copy file name=/oradata/datafiles/undotbs01.dbf RECID=2 STAMP=786448857
cataloged datafile copy
datafile copy file name=/oradata/datafiles/uc4_data01.dbf RECID=3 STAMP=786448857
cataloged datafile copy
datafile copy file name=/oradata/datafiles/uc4_index01.dbf RECID=4 STAMP=786448857
cataloged datafile copy
datafile copy file name=/oradata/datafiles/users01.dbf RECID=5 STAMP=786448857

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=786448857 file name=/oradata/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=786448857 file name=/oradata/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=786448857 file name=/oradata/datafiles/uc4_data01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=786448857 file name=/oradata/datafiles/uc4_index01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=786448857 file name=/oradata/datafiles/users01.dbf

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

database opened Duplication completed, database is open
Finished Duplicate Db at 20-JUN-12
released channel: CH01

RMAN>
Recovery Manager complete.

Create a password file:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10 force=y

The final step is to create a listener and the whole operation is completed.
Posted by
Labels:

8 comments:

  1. HaranathJuly 13, 2012 at 8:43 PM

    Is it possible to do simultaneous rman duplication from production backup to different destinations at a time ? Thx

    ReplyDelete
    Replies
    1. Lefteris TsekourasJuly 13, 2012 at 9:28 PM

      I don't see why not.
      I haven't try it, but backup tapes should be available to every destination.
      I will ask out NetBackup administrator on Monday for more details.

      Delete
    2. Lefteris TsekourasJuly 16, 2012 at 8:25 AM

      As it turns out, it's complicated.
      It depends on how many channels were writing in each tape, when the backup was being taken.
      But this is something you don't know for sure, so you may get a "tape is in use" message and, after a while, perhaps a timeout, depending on your settings.
      So, to be safe, you need to do one restore each time.

      Delete
  2. AnonymousSeptember 8, 2014 at 3:06 PM

    This not work in 12.1.0.1.4 database. Send commnad not send params.

    ReplyDelete
    Replies
    1. AnonymousSeptember 17, 2018 at 9:26 PM

      It will work with 12.1.0.2. Upgrade your Database to 12.1.0.2 then perform the Duplicate steps.

      Delete
    2. AnonymousSeptember 17, 2018 at 9:28 PM

      It will work with 12.1.0.2. Upgrade your DB to 12.1.0.2 then perform the Duplicate

      Delete
  3. AnonymousDecember 11, 2014 at 4:30 AM

    Thank you somuch, It worked for me too after setting the NB_ORA_CLIENT=

    ReplyDelete
  4. UnknownMay 2, 2016 at 9:11 AM

    Hi,

    I spent almost 4 weeks trying to clone from rman tape backup and i am doing exactly same steps as mentioned above but still failing.. your post helped me to restore it successfully.

    the restore went fine after setting the environment variable NB_ORA_CLIENT.

    ReplyDelete

Subscribe to: Post Comments (Atom)