Oracle Cloning - Conventional Duplicate Database method

-

Oracle Cloning - Conventional Duplicate Database method

For DBA Refreshing Table, Schema and Database are routine activity. You can refresh/Clone database using below method:

1.Conventional Restore method and Rename DB post DB Refresh
2.Conventional Duplicate Database method
3. Active database Cloning 
4.Backup Base Cloning

We will cover all these methods of cloning in individual blogs. In This blog , We will discuss about how to refresh/Clone Database using Conventional Duplicate Method.  

In Conventional Duplicate Method, Target database backup needed to be copied to auxiliary DB server same as that of the target database. In Conventional method if cloning , We need to connect to the target and auxiliary db and backup should be placed on auxiliary db server .

Below are the step followed to perform database cloning using Conventional Duplicate Database method.

1) Copy the RMAN backup files to Auxiliary database server

Note : Directory structure of RMAN backup need to be same on Auxiliary database server.Make symbolic link if required.

2)Prepare Auxiliary database parameter and crate password file for auxiliary database.

Backup the existing password file and parameter file and move the spfile to another name.

Verify the control_file parameter value, RMAN will create control file according to value of this parameter.

Verfify the REMOTE_LOGIN_PASSWORD_FILE , value should be exclusive

Set the Below parameter temporary to make the Auxiliary database 
db_file_name_convert & log_file_name_convert

Note : You can also create your own “set newname for datafile” for all files. In that case need not to set below parameter.

3)Create the password file for remote connection as sysdba

orapwd password=ops_dba file=orapw$ORACLE_SID entries=5

4)  Nomount the Auxiliary Database 

         Sqlplus “/ a sysdba”
    Startup nomount pfile= /u01/app/oracle/admin/auxdb/pfile/initauxdb.ora

     5)Login to Production database server and Create Auxiliary database

       Note : Crosscheck the space on mount point of Auxiliary Database server. Enough space should be available as we have on production server
oraclehost02:oraware->rman trace refresh.log
RMAN> connect target
RMAN> connect auxiliary sys/ops_dba@auxdb
RMAN> duplicate target database to auxdb;

     (Check DOC end section, If you want to use SET NEWNAME FOR DATAFILE option  . This Option clone database as per our standard environment)

      


     6)Remove the DB_FILE_NAME_CONVERT and LOG_FILE_NAME CONVERT parameter from pfile

     7)Create spfile from pfile and bouns auxiliary database.

     APPENDIX:

     Incase you want to use “SET NEWNAME FOR DATAFILE” option
Note : You can use the below commands to make the SET NEWNAME FOR DATAFILE command and place the same in run command 
 select 'set newname for datafile '''||name||''' to '''||replace(name,'Prod_SID','Auxiliary_SID ') ||''';' from v$datafile ;

 select 'set newname for tempfile '''||name||''' to '''||replace(name,'Prod_sid','Auxiliary_SID') ||''';' from v$tempfile ;
e.g
 select 'set newname for datafile '''||name||''' to '''||replace(name,'trgdb','auxdb') ||''';' from v$datafile ;
 select 'set newname for tempfile '''||name||''' to '''||replace(name,'trgdb','auxdb') ||''';' from v$tempfile ;

  

     Duplicate command With set new name :

     oraclehost01:oraware->rman trace refresh.log
RMAN> connect target
RMAN> connect auxiliary sys/ops_dba@auxdb
RMAN> run
{
set new name for datafile '/u02/oradata/trgdb/trgdb_system_01.dbf' to '/u02/oradata/auxdb/auxdb_system_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_undotbs_01.dbf' to '/u04/oradata/auxdb/auxdb_undotbs_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_sysaux_01.dbf' to '/u02/oradata/auxdb/auxdb_sysaux_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_users_01.dbf' to '/u02/oradata/auxdb/auxdb_users_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_tools_01.dbf' to '/u04/oradata/auxdb/auxdb_tools_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_ASTG_TAB_01.dbf' to '/u02/oradata/auxdb/auxdb_ASTG_TAB_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_CNVACT_IND01_01.dbf' to '/u03/oradata/auxdb/auxdb_CNVACT_IND01_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_CNVACT_IND02_01.dbf' to '/u04/oradata/auxdb/auxdb_CNVACT_IND02_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_CNVACT_TAB_01.dbf' to '/u05/oradata/auxdb/auxdb_CNVACT_TAB_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_COREARCH_01.dbf' to '/u02/oradata/auxdb/auxdb_COREARCH_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_EDI_IND_01.dbf' to '/u03/oradata/auxdb/auxdb_EDI_IND_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_EDI_TAB_01.dbf' to '/u04/oradata/auxdb/auxdb_EDI_TAB_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_EDI_TAB2_01.dbf' to '/u05/oradata/auxdb/auxdb_EDI_TAB2_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_INB_WMS_IND_01.dbf' to '/u02/oradata/auxdb/auxdb_INB_WMS_IND_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_INB_WMS_TAB_01.dbf' to '/u02/oradata/auxdb/auxdb_INB_WMS_TAB_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_INTF_IND_01.dbf' to '/u04/oradata/auxdb/auxdb_INTF_IND_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_INTF_TAB_01.dbf' to '/u05/oradata/auxdb/auxdb_INTF_TAB_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_INVTRAN_IDX_TAB_01.dbf' to '/u02/oradata/auxdb/auxdb_INVTRAN_IDX_TAB_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_ITS_IND_01.dbf' to '/u02/oradata/auxdb/auxdb_ITS_IND_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_ITS_TAB_01.dbf' to '/u04/oradata/auxdb/auxdb_ITS_TAB_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OEORDER_RELEASE_TAB_01.dbf' to '/u05/oradata/auxdb/auxdb_OEORDER_RELEASE_TAB_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSIND_01.dbf' to '/u02/oradata/auxdb/auxdb_OMSIND_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSIND2_01.dbf' to '/u03/oradata/auxdb/auxdb_OMSIND2_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_OMSIND3_01.dbf' to '/u04/oradata/auxdb/auxdb_OMSIND3_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSIND4_01.dbf' to '/u05/oradata/auxdb/auxdb_OMSIND4_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSIND5_01.dbf' to '/u02/oradata/auxdb/auxdb_OMSIND5_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSIND6_01.dbf' to '/u03/oradata/auxdb/auxdb_OMSIND6_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_OMSTAB_01.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB2_01.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB2_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSTAB3_01.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB3_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OUB_WMS_IND_01.dbf' to '/u03/oradata/auxdb/auxdb_OUB_WMS_IND_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_OUB_WMS_TAB_01.dbf' to '/u04/oradata/auxdb/auxdb_OUB_WMS_TAB_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_SNAPLOG_TAB_01.dbf' to '/u05/oradata/auxdb/auxdb_SNAPLOG_TAB_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSTAB4_01.dbf' to '/u03/oradata/auxdb/auxdb_OMSTAB4_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB5_01.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB5_01.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSTAB6_01.dbf' to '/u03/oradata/auxdb/auxdb_OMSTAB6_01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_OMSTAB7_01.dbf' to '/u04/oradata/auxdb/auxdb_OMSTAB7_01.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSTAB7_02.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB7_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB6_02.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB6_02.dbf';
set newname for datafile '/u04/oradata/trgdb/sox_user01.dbf' to '/u04/oradata/auxdb/sox_user01.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_ITS_TAB_02.dbf' to '/u04/oradata/auxdb/auxdb_ITS_TAB_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_EDI_TAB2_02.dbf' to '/u05/oradata/auxdb/auxdb_EDI_TAB2_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OUB_WMS_TAB_02.dbf' to '/u05/oradata/auxdb/auxdb_OUB_WMS_TAB_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB5_02.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB5_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB5_03.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB5_03.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSTAB6_03.dbf' to '/u03/oradata/auxdb/auxdb_OMSTAB6_03.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSTAB_02.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB_02.dbf';
set newname for datafile '/u02/oradata/trgdb/trgdb_OMSTAB7_03.dbf' to '/u02/oradata/auxdb/auxdb_OMSTAB7_03.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_EDI_IND_02.dbf' to '/u03/oradata/auxdb/auxdb_EDI_IND_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_undotbs_02.dbf' to '/u05/oradata/auxdb/auxdb_undotbs_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB3_02.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB3_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSIND3_02.dbf' to '/u05/oradata/auxdb/auxdb_OMSIND3_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_soxdata_01.dbf' to '/u05/oradata/auxdb/auxdb_soxdata_01.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_sysaux_02.dbf' to '/u05/oradata/auxdb/auxdb_sysaux_02.dbf';
set newname for datafile '/u05/oradata/trgdb/trgdb_OMSTAB4_02.dbf' to '/u05/oradata/auxdb/auxdb_OMSTAB4_02.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_ITS_IND_02.dbf' to '/u03/oradata/auxdb/auxdb_ITS_IND_02.dbf';
set newname for datafile '/u03/oradata/trgdb/trgdb_OMSTAB2_02.dbf' to '/u03/oradata/auxdb/auxdb_OMSTAB2_02.dbf';
set newname for datafile '/u04/oradata/trgdb/trgdb_users_02.dbf' to '/u04/oradata/auxdb/auxdb_users_02.dbf';
set newname for tempfile '/u04/oradata/trgdb/trgdb_temp_01.dbf' to '/u04/oradata/auxdb/auxdb_temp_01.dbf';
set newname for tempfile '/u04/oradata/trgdb/trgdb_temp2_01.dbf' to '/u04/oradata/auxdb/auxdb_temp2_01.dbf';
set newname for tempfile '/u02/oradata/trgdb/trgdb_temp_02.dbf' to '/u02/oradata/auxdb/auxdb_temp_02.dbf';
set newname for tempfile '/u02/oradata/trgdb/trgdb_temp2_02.dbf' to '/u02/oradata/auxdb/auxdb_temp2_02.dbf';
set until time "to_date('24-04-2008 09:14:00','dd-mm-yyyy hh24:mi:ss' )";
DUPLICATE TARGET DATABASE TO auxdb
 LOGFILE 
 GROUP 2 ('/u03/oradata/auxdb/auxdb_redo_g2_m1.rdo',        '/u04/oradata/auxdb/auxdb_redo_g2_m2.rdo' ) SIZE 50M REUSE,
 GROUP 4 ('/u02/oradata/auxdb/auxdb_redo_g4_m1.rdo',        '/u03/oradata/auxdb/auxdb_redo_g4_m2.rdo') SIZE 20M REUSE,
 GROUP 5 ('/u03/oradata/auxdb/auxdb_redo_g5_m1.rdo',        '/u04/oradata/auxdb/auxdb_redo_g5_m2.rdo' ) SIZE 20M REUSE,
GROUP 6 ('/u04/oradata/auxdb/auxdb_redo_g6_m1.rdo',
 '/u02/oradata/auxdb/auxdb_redo_g6_m2.rdo' ) SIZE 20M REUSE;
}


You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for Refresh


Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --d...
Read more

Change Oracle DB Name ,DBID and Both

-
Change Oracle DB Name ,DBID and Both Oracle DBA changing database name ,DBID or both are common activity which are needed to be performed regularly.  With the introduction on the NID utility this has become easy to make such changes.In this blog , We will show you how to change : 1. DB Name and ID  2. DB Name 3. DBID  using NID utility. Change DB Name and DBID: 1) Take Full backup of the database and mount database. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area  251658240 bytes Fixed Size                  2236368 bytes Variable Size             161210416 bytes Database Buffers           83886080 bytes Redo Buffers       ...
Read more