Tuesday, June 26, 2012

Clone a database using RMAN RESTORE & RECOVER commands

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

Our original database is named UC4DB and the one we will create 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=UC4DB

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

We connect to the database and recovery catalog, and firstly we fetch a control file and create a pfile from UC4DB's backup spfile:
[oracle@oravm ~]$ rman target / catalog rman/******@rman
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jun 26 10:14:26 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
connected to recovery catalog database

RMAN> STARTUP FORCE 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/initUC4DB.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 CHANNEL CH01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=bserver,NB_ORA_CLIENT=uc4db';
SET UNTIL SCN 1010635; Omit this line, if you want to restore to the latest SCN.
RESTORE
SPFILE TO PFILE '/oradata/database/product/11.2.0/dbhome_1/dbs/initUC4DB.ora' Create a pfile.
CONTROLFILE TO '/oradata/control/control01.ctl'; Fetch control file to a new location.
}

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 until clause
Starting restore at 26-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 to PFILE
output file name=/oradata/database/product/11.2.0/dbhome_1/dbs/initUC4DB.ora
channel CH01: restoring control file
output file name=/oradata/control/control01.ctl
channel CH01: reading from backup piece ORACLE_ONLINE_UC4DB_1ene0888_1_1:786440456.dbf
channel CH01: piece handle=ORACLE_ONLINE_UC4DB_1ene0888_1_1:786440456.dbf tag=TAG20120620T073813
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:06:35
Finished restore at 26-JUN-12
released channel: CH01

RMAN> SHUTDOWN IMMEDIATE;

Oracle instance shut down

If you get an ORA-04031 during startup [Note 567020.1], then set the following to your environment and retry:
export ORA_RMAN_SGA_TARGET=350

Now, edit your new pfile and change all filesystem destinations and memory parameters, according to your new specs.
*.audit_file_dest='/oradata/database/database/admin/uc4dbtest/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/control/control01.ctl'
*.db_block_size=8192
*.db_domain='ote.gr'
*.db_name='uc4db'
*.db_recovery_file_dest='/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/oradata/database'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1G
*.nls_length_semantics='CHAR'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=335
*.undo_tablespace='UNDOTBS1'

We continue in RMAN:
RMAN> STARTUP MOUNT;

connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area    1068937216 bytes
Fixed Size                     2235208 bytes
Variable Size                616563896 bytes
Database Buffers             444596224 bytes
Redo Buffers                   5541888 bytes

RMAN> RUN
{
ALLOCATE CHANNEL CH01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=bserver,NB_ORA_CLIENT=uc4db';
SET NEWNAME FOR DATABASE TO '/oradata/datafiles/%b'; All datafiles and tempfiles will be put in this path. Alternatively, use "SET NEWNAME FOR DATAFILE..." for each one.
SET UNTIL SCN 1010635;
RESTORE DATABASE; Will read your latest full online backup and fetch all datafiles.
SWITCH DATAFILE ALL;
RECOVER DATABASE; Will read all archive logs and recover to your SCN.
}

allocated channel: CH01
channel CH01: SID=360 device type=SBT_TAPE
channel CH01: Veritas NetBackup for Oracle - Release 6.5 (2010042404)
sent command to channel: CH01
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 26-JUN-12
Starting implicit crosscheck backup at 26-JUN-12
Crosschecked 2 objects
Finished implicit crosscheck backup at 26-JUN-12
Starting implicit crosscheck copy at 26-JUN-12
Finished implicit crosscheck copy at 26-JUN-12
searching for all files in the recovery area
cataloging files...
no files cataloged
channel CH01: starting datafile backup set restore
channel CH01: specifying datafile(s) to restore from backup set
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 26-JUN-12
Starting recover at 26-JUN-12
starting media recovery
channel CH01: starting archived log restore to default destination
channel CH01: restoring archived log
archived log thread=1 sequence=62
channel CH01: reading from backup piece ORACLE_ARCHIVELOGS_UC4DB_1fne0jje_1_1:786452078.arc
channel CH01: piece handle=ORACLE_ARCHIVELOGS_UC4DB_1fne0jje_1_1:786452078.arc tag=TAG20120620T105437
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:05:55
archived log file name=/oradata/fast_recovery_area/UC4DB/archivelog/2012_06_26/o1_mf_1_62_7ylv81dg_.arc thread=1 sequence=62
channel default: deleting archived log(s)
archived log file name=/oradata/fast_recovery_area/UC4DB/archivelog/2012_06_26/o1_mf_1_62_7ylv81dg_.arc RECID=35 STAMP=786971057
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JUN-12
released channel: CH01

RMAN> exit
Recovery Manager complete.

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

Connect with sqlplus find your redo logs' locations, change them and open your database with RESETLOGS:
[oracle@oravm dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 26 11:10:10 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
----------------------
/redo1/redo/redo01.log
/redo2/redo/redo01.log
/redo1/redo/redo02.log
/redo2/redo/redo02.log
/redo1/redo/redo03.log
/redo2/redo/redo03.log
6 rows selected.

SQL> ALTER DATABASE RENAME FILE '/redo1/redo/redo01.log' TO '/oradata/redo1/redo01.log';
Database altered.

SQL> ALTER DATABASE RENAME FILE '/redo2/redo/redo01.log' TO '/oradata/redo2/redo01.log';
Database altered.

SQL> ALTER DATABASE RENAME FILE '/redo1/redo/redo02.log' TO '/oradata/redo1/redo02.log';
Database altered.

SQL> ALTER DATABASE RENAME FILE '/redo2/redo/redo02.log' TO '/oradata/redo2/redo02.log';
Database altered.

SQL> ALTER DATABASE RENAME FILE '/redo1/redo/redo03.log' TO '/oradata/redo1/redo03.log';
Database altered.

SQL> ALTER DATABASE RENAME FILE '/redo2/redo/redo03.log' TO '/oradata/redo2/redo03.log';
Database altered.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME STATUS
------------- ------
UC4DB         OPEN

Let's take a look in alert_UC4DB.log and see what RESETLOGS is doing:

Tue Jun 26 11:13:00 2012
ALTER DATABASE OPEN RESETLOGS
RESETLOGS after incomplete recovery UNTIL CHANGE 1010635
Errors in file /oradata/database/diag/rdbms/uc4db/UC4DB/trace/UC4DB_ora_10850.trc:
ORA-00313: open failed for members of log group 1 of thread 1 Starts to clear old redo logs, but cannot find them.
ORA-00312: online log 1 thread 1: '/oradata/redo2/redo01.log'
ORA-27037: unable to obtain file status
...
...
...
Errors in file /oradata/database/diag/rdbms/uc4db/UC4DB/trace/UC4DB_ora_10850.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/redo2/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/oradata/redo1/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete Ends with clearing old redo logs.
Resetting resetlogs activation ID 952537123 (0x38c69023)
Online log /oradata/redo1/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/redo2/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/redo1/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/redo2/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/redo1/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata/redo2/redo03.log: Thread 1 Group 3 was previously cleared
Tue Jun 26 11:13:02 2012
Setting recovery target incarnation to 2
Tue Jun 26 11:13:02 2012
Assigning activation ID 953861744 (0x38dac670)
LGWR: STARTING ARCH PROCESSES
Tue Jun 26 11:13:02 2012
ARC0 started with pid=21, OS id=10887
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Tue Jun 26 11:13:03 2012
ARC1 started with pid=22, OS id=10889
Tue Jun 26 11:13:03 2012
ARC2 started with pid=23, OS id=10891
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/redo1/redo01.log New redo logs are used.
  Current log# 1 seq# 1 mem# 1: /oradata/redo2/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jun 26 11:13:03 2012
SMON: enabling cache recovery
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
Tue Jun 26 11:13:03 2012
ARC3 started with pid=24, OS id=10893
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[10850] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:949975734 end:949976404 diff:670 (6 seconds)
Dictionary check beginning
Tue Jun 26 11:13:04 2012
Errors in file /oradata/database/diag/rdbms/uc4db/UC4DB/trace/UC4DB_dbw0_10311.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/oradata/datafiles/temp01.dbf' Cannot find tempfile.
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oradata/database/diag/rdbms/uc4db/UC4DB/trace/UC4DB_dbw0_10311.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/oradata/datafiles/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /oradata/datafiles/temp01.dbf Creates tempfile.
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Jun 26 11:13:07 2012
QMNC started with pid=25, OS id=10896
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN RESETLOGS

We shutdown the database and we "startup mount" it, so we can use NID utility to change database's DBID and DBNAME.
SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;

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
Database mounted.


We run NID and set a new name [UC4TEST] for our database:
[oracle@oravm dbs]$ nid TARGET=sys/oracle DBNAME=UC4TEST
DBNEWID: Release 11.2.0.3.0 - Production on Tue Jun 26 11:38:51 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database UC4DB (DBID=952593699)
Connected to server version 11.2.0
Control Files in database:
    /oradata/control/control01.ctl
Change database ID and database name UC4DB to UC4TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 952593699 to 1822026827
Changing database name from UC4DB to UC4TEST
    Control File /oradata/control/control01.ctl - modified
    Datafile /oradata/datafiles/system01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/sysaux01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/undotbs01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/uc4_data01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/uc4_index01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/users01.db - dbid changed, wrote new name
    Datafile /oradata/datafiles/temp01.db - dbid changed, wrote new name
    Control File /oradata/control/control01.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to UC4TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database UC4TEST changed to 1822026827.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Now you need to change your ORACLE_SID environment variable to UC4TEST. Change it in your profile and resource it:
export ORACLE_SID=UC4TEST

Rename initUC4DB.ora to initUC4TEST.ora and make the following change in it:
*.db_name='uc4test'

Delete old password file and create a new one:
[oracle@oravm dbs]$ rm /oradata/database/product/11.2.0/dbhome_1/dbs/orapwuc4db
[oracle@oravm dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10 force=y

Again, connect with sqlplus and open database with RESETLOGS:
[oracle@oravm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 26 11:51:58 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP MOUNT;

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
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> SELECT DBID, INSTANCE_NAME, STATUS FROM V$INSTANCE, V$DATABASE;

DBID       INSTANCE_NAME STATUS
---------- ------------- ------
1822026827 UC4TEST       OPEN

So, at the end, we have a clone of our source database with a different DBID and name.
The final step is to create a listener, perhaps a SPFILE and the whole operation is completed.

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.