A wonderful feature in RMAN to clone or duplicate the database on the same server or remote server. This feature is useful when someone truncated or dropped the table and they wanted to recover the table. In that case, we can clone the database in different location and recover the database until point of time. This feature is very useful to recover the selected object from the RMAN backup rather than roll back a whole database. It is possible to create a duplicate database on a remote server with the same file structure or remote server with different file structure or the local server with a different file structure.
This article is tested in windows environment. Now I am going to clone the ORCL database and restore in different location on the same server. The new database name is called CLONEDB. Please remember, the directory, file names, Database names may varry between environment to environment. So you might need to change the scripts based on your database name, file structure when you follow the below steps.
Prerequisite: You should have target database and RMAN should be implemented to backup the target database. How do we implement RMAN backup? Please see this link.
Here is the Target DB and catalog DB info in my server.Target DB = ORCL
CatalogDB = CATDB
Data file, redo log, control file directory location for target and catalog DB:
C:\oracle\product\10.2.0\oradata\orcl
C:\oracle\product\10.2.0\oradata\catdb
Tnsentry file location
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
Password file location
C:\oracle\product\10.2.0\db_1\database
SPFILE location:
C:\oracle\product\10.2.0\db_1\dbs
Here are the nine easy steps to clone the database. I have followed this steps in Oracle10gR2 in windows environment. So please test the below steps in your dev/test environment before proceed to production. The steps are pretty much same for unix environment except directory names and step5
Step1. First create the directory structures for the CLONEDB.
Create the folder clonedb in the following locations.
c:\oracle\product\10.2.0\oradata
C:\oracle\product\10.2.0\admin
Create folders(adump,bdump,cdump,dpdump,udump) in the following location.
C:\oracle\product\10.2.0\admin\clonedb
Step2. Create the password file for clonedb database. Orapwd is oracle utility and we can run this utility in command prompt for windows server. For unix environment, we need to run this in $ prompt.
orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDclonedb.ora password=password entries=10
Step3. Add the appropriate entries into the tnsnames.ora and listener.ora files.
Add this entry in listener.ora file.
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = CLONEDB))
After adding the above entry, reload the listener(lsnrctl reload)
Add the below entry in tnsnames.ora file..
CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EW_Training)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)
Step 4. Build init.ora file for the duplicate database. Since we are duplicating the database on the same server as the original, we must convert the file names, so there is no conflict. These entries needs to be modified in pfile for cloning database.
db_name='clonedb'
dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
audit_file_dest='C:\oracle\product\10.2.0\admin\clonedb\adump'
background_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\bdump'
control_files='C:\oracle\product\10.2.0\oradata\clonedb\control01.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control02.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\cdump'
user_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\udump'
db_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
log_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
The modified PFILE is located in C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA
Step 5. Create the service for CLONEDB instance. Use ORDIM utility for windows environment. This command should run on OS Level.
ORADIM -NEW -SID clonedb
Creating service is only applicable for windows environment. We do not need to create any service in unix environment. In unix, starting the database spawns a services. We must ignore this step for unix environment.
Step 6. Create the spfile by using pfile which is build in step 4. Login as sys user in ORCL database and run this script.
create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\SPFILECLONEDB.ORA'
from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA'
Step 7. Create file name called initclonedb.ORA in C:\oracle\product\10.2.0\db_1\database location. The file should have the below content.
SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileclonedb.ora'
Step 8. Login as sys user in clonedb database. Start the clonedb instance without nomount.
C:\>set oracle_sid=clonedb
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 15:22:37 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
SQL>
Step 9. Connect as RMAN and clone the database. If you use any reserve word(like CLONE, TEST etc) for cloning database name, then you should use in double quotes. This case, i am using database name as clonedb. But in case, if we use name CLONE, then it should be quoted like this... (RMAN> duplicate target database to "CLONE";)
C:\>rman target=sys/password@orcl catalog=rman/rman@catdb AUXILIARY /
RMAN> duplicate target database to clonedb;
Now the ORCL database is successfully cloned and cloned database name is CLONEDB. In case if we want to clone the database to specific point of time, then we can use the below command to clone.
DUPLICATE TARGET DATABASE TO CLONEDB UNTIL TIME 'SYSDATE-4';
Questions and Answers....
Question1
I wanted to change the file names while cloning the database. How do we do that? Let us say, the file names in the target database are...
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
While cloning the database, i wanted to change the file name as below. How can i achieve this?
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSAUXCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\UNDOTBSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE02.DBF
The directory structures are taken care by db_file_name_convert and log_file_name_covert parameters. The file name changes are not taken care by this parameters. Here is the below sample script to take care of this....
RMAN>run
{
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF';
duplicate target database to clonedb;
}
Question2 One of my programmer truncated the table accidently. I wanted to clone that database and recover that table. But the database size is 2TB. I do not want to clone the entire 2 TB data. Since i wanted to clone only the tablespace which blongs to my table. How do we exclude unwanted tablespaces? Please remember, we can not skip SYSTEM, SYSAUX, UNDO, TEMP and default permanent tablespace. The below script ingore the specific tablespaces.
Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP TABLESPACE USERS, INVENTORY;
}
Question3 How do i skip the read only tablespace while cloning the database? The below script skip the read only tablespaces.
Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP READONLY ;
}
Question4 Let us say, my target database is running in Host1. My catalog database is running in Host2. I wanted to clone the target database in different server which is called Host3. Where should i run the RMAN?
RMAN is client program that connects to the database. So it does not matter where you run the RMAN. We could actually run in Host4 if we want to... We should be good as long as we connect right database. Where the RMAN client runs is irrelevant.
Note : As per oracle10gR2, during the cloning, if any tablespaces are OFFLINE in target database, will not be cloned into new database.
Temp tablespace never be backed up through RMAN. Hence, it will not be cloned. At the end, cloning process add temp files for temporary tablespace automatically. This is pretty much same for restoring the database from RMAN backup. RMAN creates the temporary tablespace when we restore the database.
I have done more Restore scenarios in Oracle12c. Please refer this post if you want to..
This article is tested in windows environment. Now I am going to clone the ORCL database and restore in different location on the same server. The new database name is called CLONEDB. Please remember, the directory, file names, Database names may varry between environment to environment. So you might need to change the scripts based on your database name, file structure when you follow the below steps.
Prerequisite: You should have target database and RMAN should be implemented to backup the target database. How do we implement RMAN backup? Please see this link.
Here is the Target DB and catalog DB info in my server.Target DB = ORCL
CatalogDB = CATDB
Data file, redo log, control file directory location for target and catalog DB:
C:\oracle\product\10.2.0\oradata\orcl
C:\oracle\product\10.2.0\oradata\catdb
Tnsentry file location
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
Password file location
C:\oracle\product\10.2.0\db_1\database
SPFILE location:
C:\oracle\product\10.2.0\db_1\dbs
Here are the nine easy steps to clone the database. I have followed this steps in Oracle10gR2 in windows environment. So please test the below steps in your dev/test environment before proceed to production. The steps are pretty much same for unix environment except directory names and step5
Step1. First create the directory structures for the CLONEDB.
Create the folder clonedb in the following locations.
c:\oracle\product\10.2.0\oradata
C:\oracle\product\10.2.0\admin
Create folders(adump,bdump,cdump,dpdump,udump) in the following location.
C:\oracle\product\10.2.0\admin\clonedb
Step2. Create the password file for clonedb database. Orapwd is oracle utility and we can run this utility in command prompt for windows server. For unix environment, we need to run this in $ prompt.
orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDclonedb.ora password=password entries=10
Step3. Add the appropriate entries into the tnsnames.ora and listener.ora files.
Add this entry in listener.ora file.
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = CLONEDB))
After adding the above entry, reload the listener(lsnrctl reload)
Add the below entry in tnsnames.ora file..
CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EW_Training)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)
Step 4. Build init.ora file for the duplicate database. Since we are duplicating the database on the same server as the original, we must convert the file names, so there is no conflict. These entries needs to be modified in pfile for cloning database.
db_name='clonedb'
dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
audit_file_dest='C:\oracle\product\10.2.0\admin\clonedb\adump'
background_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\bdump'
control_files='C:\oracle\product\10.2.0\oradata\clonedb\control01.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control02.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\cdump'
user_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\udump'
db_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
log_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
The modified PFILE is located in C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA
Step 5. Create the service for CLONEDB instance. Use ORDIM utility for windows environment. This command should run on OS Level.
ORADIM -NEW -SID clonedb
Creating service is only applicable for windows environment. We do not need to create any service in unix environment. In unix, starting the database spawns a services. We must ignore this step for unix environment.
Step 6. Create the spfile by using pfile which is build in step 4. Login as sys user in ORCL database and run this script.
create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\SPFILECLONEDB.ORA'
from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA'
Step 7. Create file name called initclonedb.ORA in C:\oracle\product\10.2.0\db_1\database location. The file should have the below content.
SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileclonedb.ora'
Step 8. Login as sys user in clonedb database. Start the clonedb instance without nomount.
C:\>set oracle_sid=clonedb
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 15:22:37 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
SQL>
Step 9. Connect as RMAN and clone the database. If you use any reserve word(like CLONE, TEST etc) for cloning database name, then you should use in double quotes. This case, i am using database name as clonedb. But in case, if we use name CLONE, then it should be quoted like this... (RMAN> duplicate target database to "CLONE";)
C:\>rman target=sys/password@orcl catalog=rman/rman@catdb AUXILIARY /
RMAN> duplicate target database to clonedb;
Now the ORCL database is successfully cloned and cloned database name is CLONEDB. In case if we want to clone the database to specific point of time, then we can use the below command to clone.
DUPLICATE TARGET DATABASE TO CLONEDB UNTIL TIME 'SYSDATE-4';
Questions and Answers....
Question1
I wanted to change the file names while cloning the database. How do we do that? Let us say, the file names in the target database are...
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
While cloning the database, i wanted to change the file name as below. How can i achieve this?
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSAUXCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\UNDOTBSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE02.DBF
The directory structures are taken care by db_file_name_convert and log_file_name_covert parameters. The file name changes are not taken care by this parameters. Here is the below sample script to take care of this....
RMAN>run
{
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF';
duplicate target database to clonedb;
}
Question2 One of my programmer truncated the table accidently. I wanted to clone that database and recover that table. But the database size is 2TB. I do not want to clone the entire 2 TB data. Since i wanted to clone only the tablespace which blongs to my table. How do we exclude unwanted tablespaces? Please remember, we can not skip SYSTEM, SYSAUX, UNDO, TEMP and default permanent tablespace. The below script ingore the specific tablespaces.
Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP TABLESPACE USERS, INVENTORY;
}
Question3 How do i skip the read only tablespace while cloning the database? The below script skip the read only tablespaces.
Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP READONLY ;
}
Question4 Let us say, my target database is running in Host1. My catalog database is running in Host2. I wanted to clone the target database in different server which is called Host3. Where should i run the RMAN?
RMAN is client program that connects to the database. So it does not matter where you run the RMAN. We could actually run in Host4 if we want to... We should be good as long as we connect right database. Where the RMAN client runs is irrelevant.
Note : As per oracle10gR2, during the cloning, if any tablespaces are OFFLINE in target database, will not be cloned into new database.
Temp tablespace never be backed up through RMAN. Hence, it will not be cloned. At the end, cloning process add temp files for temporary tablespace automatically. This is pretty much same for restoring the database from RMAN backup. RMAN creates the temporary tablespace when we restore the database.
I have done more Restore scenarios in Oracle12c. Please refer this post if you want to..
No comments:
Post a Comment