Duplicating a Database using RMAN – (UNIX / Linux)

PMMPRD to PMMQA1
Pre-requisites
A valid full database backup of the target database
$rman target sys/xxxx@pmmprd.world catalog rman/xxxxx@recprd
RMAN> list backup summary;

Target database must be mounted or open (Target means PMMPRD)

$ sqlplus “/ as sysdba”
SQL> startup open

Steps Required
Create Password File for Auxiliary Database
$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwPMMQA1 password=sys_password

Create an Initialization Parameter for the Auxiliary Database if 9i

$ export ORACLE_SID=PMMPRD
$ sqlplus “/ as sysdba”

SQL> create pfile=’/u01/app/oracle/product/9.2.0/dbs/initPMMQA1.ora’ from spfile;

NOTE: if 8i copy the initPMMPRD.ora file over to the other server and rename to initPMMQA1.ora

NOTE2: If the instance is 9i you can configure db_file_name_convert and log_file_name_convert parameters in the init file with more then one pair of strings. If the instance is 8i you can only have one pair of strings, if you need more then one pair of strings in 8i you will need to use SET NEWNAME in the RUN{} script.

db_file_name_convert = (‘/u06/app/oradata/PMMPRD’, ‘/u06/app/oradata/PMMQA1’)
log_file_name_convert = (‘/u03/app/oradata/PMMPRD ‘, ‘/u03/app/oradata/PMMQA1’,
‘/u04/app/oradata/PMMPRD ‘, ‘/u04/app/oradata/PMMQA1’,
‘/u05/app/oradata/PMMPRD ‘, ‘/u05/app/oradata/PMMQA1’)

control_files = ‘/u03/app/oradata/PMMQA1/control01.ctl’
, ‘/u04/app/oradata/PMMQA1/control02.ctl’
, ‘/u05/app/oradata/PMMQA1/control03.ctl’

db_name = ‘PMMQA1’
instance_name = ‘PMMQA1’
audit_file_dest = ‘/u01/app/oracle/admin/PMMQA1/adump’
background_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/bdump’
core_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/cdump’
user_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/udump’
service_names = ‘PMMQA1.WORLD’
dispatchers = ‘(PROTOCOL=TCP) (SERVICE=TESTDBXDB)’
log_archive_dest_1 = ‘location=/u06/app/oradata/PMMQA1/archive mandatory’

NOTE3: The mount points are example only, check all before the clone.
Create / Start the Auxiliary Instance

Create all needed directories for the duplicate database:

$ mkdir /u01/app/oracle/admin/PMMQA1
$ mkdir /u01/app/oracle/admin/PMMQA1/adump
$ mkdir /u01/app/oracle/admin/PMMQA1/bdump
$ mkdir /u01/app/oracle/admin/PMMQA1/cdump
$ mkdir /u01/app/oracle/admin/PMMQA1/create
$ mkdir /u01/app/oracle/admin/PMMQA1/pfile
$ mkdir /u01/app/oracle/admin/PMMQA1/scripts
$ mkdir /u01/app/oracle/admin/PMMQA1/udump
$ mkdir /u03/app/oradata/PMMQA1
$ mkdir /u04/app/oradata/PMMQA1
$ mkdir /u05/app/oradata/PMMQA1
$ mkdir /u06/app/oradata/PMMQA1
$ mkdir /u06/app/oradata/PMMQA1/archive

NOTE4: Mount point are example only, check all before clone.

$ export ORACLE_SID=PMMQA1
$ sqlplus “/ as sysdba”

SQL> startup nomount

Ensure Oracle Net Connectivity to Auxiliary Database from PMMPRD

$ sqlplus “sys/xxxxx@PMMQA1 as sysdba”

Login to Target and Auxiliary Database using RMAN
rman target sys/xxxxx@pmmprd.world catalog rman/xxxxx@recprd auxiliary sys/xxxxx@pmmqa1.world

Run the RMAN DUPLICATE DATABASE Command

RMAN> run
{
allocate auxiliary channel t1 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t2 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t3 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t4 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t5 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t6 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t7 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t8 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t9 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t10 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t11 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate auxiliary channel t12 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
duplicate target database to pmmqa1
logfile
group 1 (‘/u01/oradata/PMMQA1/redo01a.log’,
‘/u03/oradata/PMMQA1/redo01b.log’) size 500M,
group 2 (‘/u02/oradata/PMMQA1/redo02a.log’,
‘/u04/oradata/PMMQA1/redo02b.log’) size 500M,
group 3 (‘/u01/oradata/PMMQA1/redo03a.log’,
‘/u03/oradata/PMMQA1/redo03b.log’) size 500M,
group 4 (‘/u02/oradata/PMMQA1/redo04a.log’,
‘/u04/oradata/PMMQA1/redo04b.log’) size 500M;
}

RMAN> exit

If Recovery Fail

NOTE: There might be a possibility that the recover process fail because of missing arch logs that is trying to apply, if that happen you need to identify the logs missing and restore them to the PMMQA1 arch directory.
What I did was to log into PMMPRD restore the files there and copy over PMMQA1 and renamed the file.
After that I recover until cancel and open the database with resetlogs;

Export ORACLE_SID=PMMPRD
$ rman target sys/xxxxx@pmmprd.world catalog rman/xxxx@recprd

RMAN> run
{
allocate channel t1 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
allocate channel t2 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304’;
restore archivelog from logseq=29200 until logseq=29202 thread=1;
}

RMAN> exit

Trying manual recovery from sqlplus session.

Export ORACLE_SID=PMMQA1
$ sqlplus ‘/as sysdba’
SQL> recover database using backup controlfile until cancel;

Oracle will then prompt for a particular change for a particular thread. This information is obtained from the datafile headers. For the first log Oracle is able to suggest a filename to apply, again using information from the datafile header (the log sequence number) plus information from the init.ora parameter file (the log archive destination and format).

Example

ORA-00279: Change 4848 generated at 10/26/94 12:36:09 needed for thread 1
ORA-00289: Suggestion : $1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000030.ARC
ORA-00280: Change 4848 for thread 1 is in sequence #30
Specify log: {<RET>=suggested | filename | AUTO | FROM logsource | CANCEL}

SQL> <RETURN> — to accept the suggestion.

Let Oracle apply all the available archived logfiles.

Example

ORA-00279: Change 4977 generated at 10/26/94 13:34:13 needed for thread 1
ORA-00289: Suggestion : $1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000031.ARC
ORA-00280: Change 4977 for thread 1 is in sequence #31
ORA-00278: Logfile ‘$1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000030.ARC’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | FROM logsource | CANCEL}

SQL> AUTO
Open the database, using the RESETLOGS option.

SQL> alter database open resetlogs;
Create All tempfiles for Temporary Tablespace

$ export ORACLE_SID=PMMQA1
$ sqlplus “/ as sysdba”

SQL> alter tablespace temp2
add TEMPFILE ‘/u14/oradata/PMMQA1/TEMP2_01.dbf’ SIZE 10000M AUTOEXTEND OFF,
‘/u14/oradata/PMMQA1/TEMP2_02.dbf’ SIZE 6000M AUTOEXTEND OFF;

• Backup & Recovery
• General DBA