Monday, 10 March 2014

Duplicate or clone database from another database by directly connecting to source

How to Duplicate or clone  database from another database by directly connecting to source?

let source_1  be the source database and target_1 be the target database.


Here are the steps to do duplication/cloning of database by connecting to source database(source_1):

1.Check the source database  name
SQL>select name from v$database ;

----------------------------------------------------------------------------------------------------------------
2. Take backup from target database for safety with below script --(it is not mandatory if you are not going to drop your target database)
vi rman_fullback.rcv
run {
    allocate channel t1 type disk format '/u04/rmanbackup/source_1/full_source_1_%t_%s_%p_%U.bck' maxpiecesize 5120m;
    backup  AS COMPRESSED BACKUPSET database tag = full_open_backup_disk;
    sql  'alter system archive log current'; 
     Backup archivelog all;
    backup SPFILE format '/u04/rmanbackup/source_1/spfile_source_1.bck';
     Backup current controlfile format '/u04/rmanbackup/source_1/ctrl_source_1_%t_%s_%p_%U.bck';
     Release channel t1;
}
rman checksyntax@rman_fullback.rcv
Make sure it comes with no error.
nohup rman target / nocatalog cmdfile=rman_fullback.rcv log=rman_fullback.log &
------------------------------------------------------------------------------------------------------------------
3. Before dropping make sure above steps are completed and note the below from both databases
SQL> select name from v$datafile;
/u01/oradata/target_1/system_01.dbf
 /u03/oradata/source_1/system_01.dbf
SQL> select member from v$logfile;
/u01/oradata/target_1/redo06a.log
 /u03/oradata/source_1/redo06a.log
--------------------------------------------------------------------------------------------------------------------
4. Set the pameter in pfile of target 'source','target'
*.log_file_name_convert='/u03/oradata/source_1', '/u01/oradata/target_1'
*.db_file_name_convert='/u03/oradata/source_1', '/u01/oradata/target_1'
Or
 ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u03/oradata/source_1','/u01/oradata/target_1'   SCOPE=SPFILE;
 ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u03/oradata/source_1','/u01/oradata/target_1'   SCOPE=SPFILE;
--------------------------------------------------------------------------------------------------------------------
5. Create password file on both sides in $ORACLE_HOME/dbs location
orapwd file=orapwsource_1 password=admin123 force=y ignorecase=y
now send the pwd file from source to target
---------------------------------------------------------------------------------------------------------------------
6. Drop the target database
Shutdown immediate;
Startup mount restrict;
Drop database;
----------------------------------------------------------------------------------------------------------------------
7. on target database side
export $ORACLE_SID=target database name(target_1)
SQL> startup nomount;
SQL>exit
------------------------------------------------------------------------------------------------------------------------
8. Now duplicate the database by directly connecting to source and cloning
rman target sys/admin123@source auxiliary sys/admin123@target
Duplicate target database to target from active database nofilenamecheck;

If target and source are having different directories, please use below:
run
{
duplicate target database to 'TARGET_1' from active database
spfile
set db_file_name_convert '/u01/dba/db/data/SOURCE_1','/u02/TARGET_1/db/data/TARGET_1'
set LOG_FILE_NAME_CONVERT '/u01/dba/db/data/SOURCE_1','/u02/TARGET_1/db/data/TARGET_1'
set control_files '/u02/TARGET_1/db/data/TARGET_1/control01.ctl','/u02/TARGET_1/db/fast_recovery_area/TARGET_1/control02.ctl'
set diagnostic_dest '/u02/TARGET_1/db'
set db_recovery_file_dest '/u02/TARGET_1/db/fast_recovery_area'
set audit_file_dest '/u02/TARGET_1/db/admin/TARGET_1/adump'
SET log_archive_dest_1 'location=/u02/TARGET_1/db/arch'
;
}

---------------------------------------------------------------------------------------------------------------------------


No comments:

ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file.

ORA-00600 [kwqitnmphe:ltbagi] Cause: This issue arises in 12.1.0.2. The error occurs because there are still Historical Messages without...