Tuesday, November 15, 2011

RAC-ify an Oracle Database in Ten Steps - Convert a single-instance database into RAC

Assuming that you have successfully installed Grid Infrastructure 11gR2, Oracle Universal Installer (OUI) probably offers the best approach to create a new RAC-enabled database and instances on all available nodes. What if you already have an existing single-instance database that you want to RAC-ify? Should you use OUI? Yes, you can, but OUI option may not work for you as it didn't for us. With OUI, you need to create a template and then create a new RAC-enabled database based on the template. Oracle e-Biz Suite also suggests using a script to RAC-ify a single-instance database. We didn't use this script either. Instead, we used a manual approach as described below. It worked quite well for us. Downtime was quite minimal as well.

This example assumes that your data files, control files, redo log files, temp files and spfile are already on ASM. Of course, underlying grid infrastructure is successfully installed and configured.

This example shows how to convert a single-instance database with HRP instance into RAC-enabled database with HRP1 and HRP2 instances.

Step 1: Create a 2nd Redo Log Thread

SQL> select group#, thread#, member from v$logfile ;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         4          1 CURRENT
         5          1 INACTIVE
         6          1 INACTIVE

SQL> alter database add logfile thread 2 group 1 size 10m ;

Database altered.

SQL> alter database add logfile thread 2 group 2 size 10m ;

Database altered.

SQL> alter database add logfile thread 2 group 3 size 10m ;

Database altered.

SQL> select group#, thread#, status from v$log ;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          2 UNUSED
         2          2 UNUSED
         3          2 UNUSED
         4          1 CURRENT
         5          1 INACTIVE
         6          1 INACTIVE


Step 2: Create a 2nd undo tablespace

SQL> create undo tablespace undotbs2
  2  datafile '+DATA_HRP' size 50M ;

Tablespace created.

SQL> select file_name from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
+DATA_HRP/hrp/datafile/users.282.758901127
+DATA_HRP/hrp/datafile/undotbs1.279.758901123
+DATA_HRP/hrp/datafile/sysaux.278.758901117
+DATA_HRP/hrp/datafile/system.277.758901101
+DATA_HRP/hrp/datafile/test.280.758901125
+DATA_HRP/hrp/datafile/undotbs2.273.758911621

6 rows selected.

Step 3: Update RAC specific parameters

ALTER DATABASE ENABLE THREAD 2;

ALTER SYSTEM SET THREAD=1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET THREAD=2 SCOPE=SPFILE SID='HRP2';

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE SID='HRP2';

ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='HRP2';

Step 4: Rename Instance on Node 1 to HRP1

[oracle@racnode1 dbs]$ cat initHRP.ora
SPFILE='+DATA_HRP/hrp/spfilehrp.ora'

[oracle@racnode1 dbs]$ cp initHRP.ora initHRP1.ora

Step 5: Start HRP1 Instance on Node 1

[oracle@racnode1 dbs]$ export ORACLE_SID=HRP1
[oracle@racnode1 dbs]$ sqlplus / as sysdba

SQL> startup ;

ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226832 bytes
Variable Size             402654576 bytes
Database Buffers         1157627904 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.

SQL> select name, value from v$parameter where isdefault ='FALSE' order by 1;

NAME                           VALUE
------------------------------ --------------------------------------------------
audit_file_dest                /u05/app/oracle/admin/HRP/adump
audit_trail                    DB
compatible                     11.2.0.0.0
control_files                  +DATA_HRP/hrp/controlfile/control01.ctl
db_block_size                  8192
db_create_file_dest            +DATA_HRP
db_domain
db_name                        HRP
diagnostic_dest                /u05/app/oracle
dispatchers                    (PROTOCOL=TCP) (SERVICE=HRPXDB)
instance_number                1
open_cursors                   300
pga_aggregate_target           524288000
processes                      150
remote_login_passwordfile      EXCLUSIVE
sga_target                     1577058304
spfile                         +DATA_HRP/hrp/spfilehrp.ora
thread                         1
undo_tablespace                UNDOTBS1

Step 6: Start HRP1 Instance on Node 1 as RAC Instance

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET CLUSTER_DATABASE_INSTANCES=2 SCOPE=SPFILE;

System altered.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup ;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226832 bytes
Variable Size             402654576 bytes
Database Buffers         1157627904 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.

Step 7: Run catclust

SQL> start ?/rdbms/admin/catclust.sql
Step 8: Start HRP Instance on Node 2

[oracle@racnode2 dbs]$ cat initHRP2.ora
SPFILE='+DATA_HRP/hrp/spfileHRP.ora'

 
[oracle@racnode2 hdump]$ sqlplus / as sysdba

SQL> shutdown immediate ;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226832 bytes
Variable Size             469763440 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7499776 bytes

SQL> alter database mount ;

Database altered.

SQL> alter database open ;

Database altered.

Step 9: Manage both instances via srvctl

[oracle@racnode1 Scripts]$ srvctl remove database -d HRP
PRKO-3141 : Database HRP could not be removed because it was running

[oracle@racnode1 Scripts]$ srvctl add database -d HRP -o $ORACLE_HOME
PRCS-1007 : Server pool HRP already exists
PRCR-1086 : server pool ora.HRP is already registered

[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP1 -n racnode1
PRCD-1022 : Cannot add a second instance to a single instance database


[oracle@racnode1 Scripts]$ srvctl add database -d HRP -o $ORACLE_HOME
[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP1 -n racnode1
[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP2 -n racnode2

[oracle@racnode1 Scripts]$ srvctl start database -d HRP
 
Step 10: Verify

SQL> select * from gv$instance ;

   INST_ID INSTANCE_NUMBER INSTANCE_NAME
---------- --------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
         1               1 HRP1
racnode1
11.2.0.2.0        12-AUG-11 OPEN         YES          1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

         2               2 HRP2
racnode2
11.2.0.2.0        12-AUG-11 OPEN         YES          2 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


[oracle@racnode1 Scripts]$ srvctl config database -d HRP             
Database unique name: HRP
Database name:
Oracle home: /u05/app/oracle/db/11.2.0.2
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: HRP
Database instances: HRP1,HRP2
Disk Groups: DATA_HRP
Mount point paths:
Services:
Type: RAC
Database is administrator managed


Monday, November 14, 2011

ASM-ify an Oracle Database

RMAN's integration with ASM makes it a breeze to migrate a file-system based Oracle database to ASM with minimal downtime. RMAN's backup as copy command moves the datafiles to ASM. The procedure described here can be executed while database is online or offline. Database must be in ARCHIVELOG mode if you plan to move the database online. Otherwise, you need to shutdown the database to move it to ASM.

Please note that online move does require a small downtime to apply the archive logs. In addition, you also need to bring the database down to copy control files. If you plan to move the database to ASM in offline mode, then the downtime that would be needed to migrate the database is slightly more than the time it would take to copy the whole database to ASM.

This example shows how to move an Oracle database that currently resides on /u05 to +DATA_HRP ASM group.

Step 1: Move Temp Files to ASM

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA_HRP
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u05/app/oracle/oradata/HRP/temp01.dbf

SQL> alter tablespace temp add tempfile size 100m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u05/app/oracle/oradata/HRP/temp01.dbf
+DATA_HRP/hrp/tempfile/temp.284.758901505

SQL> alter tablespace temp drop tempfile '/u05/app/oracle/oradata/HRP/temp01.dbf' ;

Tablespace altered.

SQL> select file_name from dba_temp_files ;

FILE_NAME
--------------------------------------------------------------------------------
+DATA_HRP/hrp/tempfile/temp.284.758901505

Step 2: Move Redo Log Files to ASM

SQL> alter database add logfile group 4 size 10m ;

Database altered.

SQL> alter database add logfile group 5 size 10m ;

Database altered.

SQL> alter database add logfile group 6 size 10m ;

Database altered.

SQL> alter system switch logfile ;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3 ;

Database altered.

SQL> alter system switch logfile ;

System altered.

SQL> select member, group# from v$logfile ;

MEMBER                                                           GROUP#
------------------------------------------------------------ ----------
+DATA_HRP/hrp/onlinelog/group_4.285.758901721                      4
+DATA_HRP/hrp/onlinelog/group_5.286.758901739                      5
+DATA_HRP/hrp/onlinelog/group_6.287.758901751                      6

Note: If you get an error while dropping a redo logfile, then drop the logs after database restart.

Step 3: Move Data Files to ASM

RMAN> backup as copy database format '+DATA_HRP' ;

input datafile file number=00001 name=/u05/app/oracle/oradata/HRP/system01.dbf
output file name=+DATA_HRP/hrp/datafile/system.277.758901101 tag=TAG20110811T135140 RECID=18 STAMP=758901110

input datafile file number=00002 name=/u05/app/oracle/oradata/HRP/sysaux01.dbf
output file name=+DATA_HRP/hrp/datafile/sysaux.278.758901117 tag=TAG20110811T135140 RECID=19 STAMP=758901122

input datafile file number=00003 name=/u05/app/oracle/oradata/HRP/undotbs01.dbf
output file name=+DATA_HRP/hrp/datafile/undotbs1.279.758901123 tag=TAG20110811T135140 RECID=20 STAMP=758901123

input datafile file number=00005 name=+DATA_HRP/hrp/datafile/test.268.758899993
output file name=+DATA_HRP/hrp/datafile/test.280.758901125 tag=TAG20110811T135140 RECID=21 STAMP=758901124

copying current control file
output file name=+DATA_HRP/hrp/controlfile/backup.281.758901125 tag=TAG20110811T135140 RECID=22 STAMP=758901126

input datafile file number=00004 name=/u05/app/oracle/oradata/HRP/users01.dbf
output file name=+DATA_HRP/hrp/datafile/users.282.758901127 tag=TAG20110811T135140 RECID=23 STAMP=758901126

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 11-AUG-11
channel ORA_DISK_1: finished piece 1 at 11-AUG-11
piece handle=+DATA_HRP/hrp/backupset/2011_08_11/nnsnf0_tag20110811t135140_0.283.758901129 tag=TAG20110811T135140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-AUG-11

SQL> shutdown immediate ;

SQL> startup mount ;

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA_HRP/hrp/datafile/system.277.758901101"
datafile 2 switched to datafile copy "+DATA_HRP/hrp/datafile/sysaux.278.758901117"
datafile 3 switched to datafile copy "+DATA_HRP/hrp/datafile/undotbs1.279.758901123"
datafile 4 switched to datafile copy "+DATA_HRP/hrp/datafile/users.282.758901127"
datafile 5 switched to datafile copy "+DATA_HRP/hrp/datafile/test.280.758901125"

If backup as copy command was executed while database was online, then recover the database.

RMAN> recover database;

RMAN> alter database open;

database opened

SQL> select file_name from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
+DATA_HRP/hrp/datafile/users.282.758901127
+DATA_HRP/hrp/datafile/undotbs1.279.758901123
+DATA_HRP/hrp/datafile/sysaux.278.758901117
+DATA_HRP/hrp/datafile/system.277.758901101
+DATA_HRP/hrp/datafile/test.280.758901125

Step 4: Move Controlfile to ASM

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u05/app/oracle/oradata/HRP
                                                 /control01.ctl, /u05/app/oracl
                                                 e/oradata/HRP/control02.ctl

SQL> alter system set control_files='+DATA_HRP/hrp/controlfile/control01.ctl','+DATA_HRP/hrp/controlfile/control02.ctl'scope=spfile ;

System altered.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226832 bytes
Variable Size             419431792 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7499776 bytes

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA_HRP/hrp/controlfile/c
                                                 ontrol01.ctl,+DATA_HRP/hrp/
                                                 controlfile/control02.ctl

RMAN> restore controlfile from '/u05/app/oracle/oradata/HRP/control01.ctl' ;

Starting restore at 11-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=101 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA_HRP/hrp/controlfile/control01.ctl
Finished restore at 11-AUG-11
Step 5: Move SPFILE to ASM

SQL> create pfile from spfile ;

File created.

SQL> create spfile='+DATA_HRP/hrp/spfilehrp.ora' from pfile ;

File created.

[oracle@racnode1 Scripts]$ cd $ORACLE_HOME/dbs
[oracle@racnode1 dbs]$ rm initHRP.ora
[oracle@racnode1 dbs]$ rm spfileHRP.ora
[oracle@racnode1 dbs]$ echo "SPFILE='+DATA_HRP/hrp/spfilehrp.ora'" > initHRP.ora

SQL> shutdown immediate ;

SQL> startup ;

Step 6: Delete old Database on File System (Optional)

[oracle@racnode1 templates]$ rman nocatalog target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 11 15:14:21 2011

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

connected to target database: HRP (DBID=1884251670)
using target database control file instead of recovery catalog

RMAN> delete copy ;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
specification does not match any archived log in the repository
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
24      1    A 11-AUG-11       1034160    11-AUG-11
        Name: /u05/app/oracle/oradata/HRP/system01.dbf

13      1    A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/system.270.758900761
        Tag: TAG20110811T134559

25      2    A 11-AUG-11       1034160    11-AUG-11
        Name: /u05/app/oracle/oradata/HRP/sysaux01.dbf

14      2    A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/sysaux.271.758900775
        Tag: TAG20110811T134559

26      3    A 11-AUG-11       1034160    11-AUG-11
        Name: /u05/app/oracle/oradata/HRP/undotbs01.dbf

15      3    A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/undotbs1.272.758900791
        Tag: TAG20110811T134559

27      4    A 11-AUG-11       1034160    11-AUG-11
        Name: /u05/app/oracle/oradata/HRP/users01.dbf

16      4    A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/users.275.758900793
        Tag: TAG20110811T134559

28      5    A 11-AUG-11       1034160    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/test.268.758899993

17      5    A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/datafile/test.273.758900791
        Tag: TAG20110811T134559

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
29      A 11-AUG-11       1036591    11-AUG-11
        Name: +DATA_HRP/hrp/controlfile/control02.ctl
        Tag: TAG20110811T141829

22      A 11-AUG-11       1034160    11-AUG-11
        Name: +DATA_HRP/hrp/controlfile/backup.281.758901125
        Tag: TAG20110811T135140

6       A 11-AUG-11       1033690    11-AUG-11
        Name: +DATA_HRP/hrp/controlfile/backup.274.758900793
        Tag: TAG20110811T134559

1       A 10-AUG-11       985693     10-AUG-11
        Name: /u05/app/oracle/db/11.2.0.2/assistants/dbca/templates/HRP.ctl
        Tag: TAG20110810T144120


Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy
datafile copy file name=/u05/app/oracle/oradata/HRP/system01.dbf RECID=24 STAMP=758901280
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/system.270.758900761 RECID=13 STAMP=758900812
deleted datafile copy
datafile copy file name=/u05/app/oracle/oradata/HRP/sysaux01.dbf RECID=25 STAMP=758901280
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/sysaux.271.758900775 RECID=14 STAMP=758900812
deleted datafile copy
datafile copy file name=/u05/app/oracle/oradata/HRP/undotbs01.dbf RECID=26 STAMP=758901280
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/undotbs1.272.758900791 RECID=15 STAMP=758900812
deleted datafile copy
datafile copy file name=/u05/app/oracle/oradata/HRP/users01.dbf RECID=27 STAMP=758901280
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/users.275.758900793 RECID=16 STAMP=758900812
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/test.268.758899993 RECID=28 STAMP=758901280
deleted datafile copy
datafile copy file name=+DATA_HRP/hrp/datafile/test.273.758900791 RECID=17 STAMP=758900812
deleted control file copy
control file copy file name=+DATA_HRP/hrp/controlfile/control02.ctl RECID=29 STAMP=758902710
deleted control file copy
control file copy file name=+DATA_HRP/hrp/controlfile/backup.281.758901125 RECID=22 STAMP=758901126
deleted control file copy
control file copy file name=+DATA_HRP/hrp/controlfile/backup.274.758900793 RECID=6 STAMP=758900793
deleted control file copy
control file copy file name=/u05/app/oracle/db/11.2.0.2/assistants/dbca/templates/HRP.ctl RECID=1 STAMP=758817680
Deleted 14 objects

Online (Live) or Offline Cloning in Oracle11g


Oracle has added two more options in Oracle11g that further simplify the database cloning process. In Oracle11g, a database can be cloned directly from an active database or from backups specified via backup location clause. Database Cloning is accomplished via RMAN DUPLICATE command.
 
Cloning from an Active Database

This is a new feature in Oracle11g. The cloning process is LIVE. The cloning process is Online. Oracle, through RMAN, simultaneously opens up connections to both the source database and the clone database to create a clone database. The cloned database, or the duplicate database, is materialized directly from the source database. 

With Active Database Cloning, there is no need to take backups and then copy it to the clone database server. There is no need to copy archive logs to the clone database server. There is no need to catalog RMAN hot backups on the clone database server. RMAN Duplicate command is qualified by "FROM ACTIVE DATABASE" to create a clone database.

Clone from any backup location on the standby database server


With this technique, you simply copy database RMAN backups to the clone database server and specify its location as part of DUPLICATE command to create a clone db. Backups can be RMAN on-line or off-line, but should include control file and archive logs, if DB is in ARCHIVELOG mode and backups were taken online. Backup location clause is an Oracle11g enhancement to RMAN DUPLICATE command.

Let’s Clone the DB! 

Database version: 11.2.0.2. 
Operating system: Windows 2008 64-bit
Source DB_NAME: ORIGINAL
Clone DB_NAME: CLONE

Step 1 – Specify Clone DB Parameters 

Assign the following identifiers to the new database. These identifiers should not be the same as the source database. For the purpose of this illustration, let’s call the clone database, well, CLONE.

Database Name: CLONE
Database Unique Name: CLONE
Instance Name: CLONE
TNS Connect String: CLONE

Step 2 - Allocate Disk Resources on the CLONE DB Server

The next step is to allocate disk space to the CLONE database. For sake of simplicity, assume that all data files, redo log files and controls files will be stored in E:\Oradata\CLONE. It’s development environment!

Step 3 - Create folder structure on the CLONE DB Server

Follow your organizational naming standards and requirements to create the folder structure on the clone database server. We will create the folder structure as follows for this illustration:

Audit Files - E:\Oradmin\CLONE\audit
Diagnostic Dump Destination - E:\Oracle\diag
Archive Log Destination - E:\Orarch\CLONE
Data Files - E:\Oradata\CLONE

Step 4 – Create initCLONE.ora file

Copy ORIGINAL database’s init.ora file as initCLONE.ora, and make the following changes. The changes suggested below should be sufficient for most DBAs. It’s not a complete list. There may be additional parameters that would require updates.

db_name=CLONE
instance_name=CLONE
db_unique_name=CLONE

Update the following parameters to point to log/trace file locations as described above.

Diag_dest
audit_file_dest

Update the following parameter to the location where you want to store CLONE’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\CLONE\control1.ctl’,‘e:\oradata\CLONE\control2.ctl’ )

ORIGINAL’s data files reside on E:\OraData\ORIGINAL, but CLONE database’s data files will be stored in E:\OraData\CLONE.

Update the following parameter to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.

db_file_name_convert=('e:\oradata\ORIGINAL','e:\oradata\CLONE')

Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,

db_file_name_convert=('e:\oradata\ORIGINAL','e:\oradata\CLONE',
                      'd:\oradata\ORIGINAL','e:\oradata\CLONE' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.

log_file_name_convert=('e:\oradata\ORIGINAL','e:\oradata\CLONE')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

RMAN-03002: failure of Duplicate Db command
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: 
ORA-00201: control file version 11.2.0.1.0 incompatible with ORACLE version 11.2.0.0.0

Also make sure that remote_login_passwordfile is set to EXCLUSIVE.

If there are any password file issues, then you may get the following errors. You need to fix these errors before proceeding.

RMAN-03002: failure of Duplicate Db command at 09/24/2011 10:57:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/24/2011 10:57:05
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01031: insufficient privileges
ORA-17629: Cannot connect to the remote database server

Step 5 - Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

oradim -new -sid CLONE -syspwd verysecretdbpassword -startmode auto -pfile initCLONE.ora

This command creates a Windows service for CLONE database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to CLONE database as SYSDBA.

orapwd file=orapwCLONE password=verysecretpassword

Please note that password file name on Windows and Unix platforms are different.

In my test environment, I also had to set _compression_compatibility='11.2' as I was getting the following errors:

RMAN-03002: failure of Duplicate Db command at 09/24/2011 11:26:10
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower],
kdt.c], [9419], [11.2.0.1.0], [], [], [], [], [], [], [], []

Step 6 - CLONE spfile creation and Instance Startup

Start CLONE Oracle database instance with nomount option by:

C:\> set ORACLE_SID=CLONE

C:> sqlplus /nolog

SQL> connect / as sysdba

SQL> create spfile from pfile

SQL> Startup nomount

SQL> exit

Step 7 – TNSNAMES and Listener Configuration on both servers

Create a listener on the CLONE database server as follows:

LISTENER =
  (DESCRIPTION_LIST=
       (ADDRESS=(PROTOCOL=TCP)(Host=clonedbserver)(Port=1521))
   )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\ora11gR2)
      (SID_NAME = CLONE)
    )      

Also add a TNSNAMES.ORA alias to tnsnames.ora file on the clone DB server:

ORIGINAL.WORLD =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = originaldbserver)(PORT = 1521))
        (CONNECT_DATA = (SID = ORIGINAL)(SERVER = DEDICATED))
  )

Also add a TNSNAMES.ORA alias to tnsnames.ora file on the original DB server:

CLONE.WORLD =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = clonedbserver)(PORT = 1521))
        (CONNECT_DATA = (SID = CLONE)(SERVER = DEDICATED))
  )

Use tnsping and  then SQL*plus to test connectivity on both the servers by

Sqlplus sys@ORIGINAL as sysdba

Sqlplus sys@CLONE as sysdba

Step 8 – RMAN Cloning

A CLONE database can be created either from the active database or backups, as shown below:


Step 8a - RMAN Cloning from Active Database

Start RMAN session on the clone database server as shown below. This example is for RMAN with NOCATALOG.

C:\> set ORACLE_SID=CLONE
C:\> rman nocatalog
RMAN> connect target sys@ORIGINAL
RMAN> Connect auxiliary sys@CLONE
RMAN> duplicate target database to CLONE from active database;

Please note that you cannot use the UNTIL clause when performing active database cloning. RMAN chooses a time based on when the online datafiles have been completely copied, so that the datafiles can be recovered to a consistent point in time.

Step 8b - RMAN Cloning from Backup Location

Copy Oracle Backups, including control file, data files and archive logs to E:\OraBack\Clone folder on the CLONE DB Server

C:\> set ORACLE_SID=CLONE
C:\> rman nocatalog
RMAN> connect target sys@ORIGINAL
RMAN> Connect auxiliary sys@CLONE
RMAN> duplicate target database to CLONE backup location 'e:\oraback\clone' ;

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to CLONE 
      from backup location 'e:\oraback\clone' 
      until time '08/03/2010:23:00:00'

Make sure that you specify the same database name above as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

Wow! CLONE Database is up and running.

DUPLICATE command has performed the following steps:
  • Restored and recovered ORIGINAL database
  • Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
  • New Control files created for CLONE database per control_files init.ora parameter
  • DB_NAME renamed to CLONE
  • Mounted CLONE database and performed complete media recovery. Applied necessary archive logs to accomplish this.
  • GLOBAL_NAME updated
  • TEMP files created 
  • Opened database with RESETLOGS option