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
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
No comments:
Post a Comment