Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 13 June 2013

Clone Database on the Same Host (with same DBID and different DBNAME)

Earlier I have posted how we can clone or duplicate database using RMAN duplicate command or using RMAN Restore and Recovery to a new host. Now in this article we will see how we can clone easily on windows system without using RMAN duplicate command.
Consider the source database name is ORCL and we need to clone on the same system of another (drive H:\) with the name ORCL3.
Step 01: In source database identify the data & Redolog files locations.
C:\>set ORACLE_SID=ORCL -- On Windows
$export ORACLE_SID=ORCL3 -- On Linux
C:\>sqlplus /nolog
C:\>connect sys/oracle@orcl3 as sysdba
SQL>SELECT DBID, NAME FROM V$DATABASE;
SQL>SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;
SQL>SELECT MEMBER FROM V$LOGFILE;
Step 02: Create Pfile and Backup Controlfile trace
SQL>Create Pfile='H:\Pfile.ora' from spfile;
SQL>Alter database backup controlfile to trace as 'H:\control.ctl';

Step 03: Shutdown the source DB and copy the datafile and redo log file to the new DB location.
SQL> shut immediate;
Note: Do not forget to create required directory structure on new database location drive. You can use OS copy method or any other method to copy the file from source to destination location.
Step 04: Edit the Pfile and perform required parameter changing
now Copy and edit the pfile and change parameter as per the new location specially control_files,db_name,dump_directory in order to reflect cloned database name and location.
Step 05: Edit the created Controlfile trace script and perform required changing
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE “database_name” in the controlfile creation script.
H:\controlfile.ctl
STARTUP NOMOUNT PFILE='H:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "ORCL3" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'H:\ORACLE\ORCL3\REDO01.LOG' SIZE 50M,
GROUP 2 'H:\ORACLE\ORCL3\REDO02.LOG' SIZE 50M,
GROUP 3 'H:\ORACLE\ORCL3\REDO03.LOG' SIZE 50M
DATAFILE 'H:\ORACLE\ORCL3\SYSTEM01.DBF','H:\ORACLE\ORCL3\UNDOTBS01.DBF',
'H:\ORACLE\ORCL3\SYSAUX01.DBF','H:\ORACLE\ORCL3\USERS01.DBF',
'H:\ORACLE\ORCL3\EXAMPLE01.DBF','H:\ORACLE\ORCL3\MUZ_IT01.DBF'
CHARACTER SET WE8MSWIN1252;
Step 06: Create new oracle service ORCL3 using oradim utility.

Use the below step only for windows system. However if you are on Linux system then simply ignore this step.
C:\>oradim -NEW -SID ORCL3 -PFILE='H:\PFILE.ORA’;
Step 07: Connect ORCL3 with as SYSDBA and run the controlfile script.
C:\>set ORACLE_SID=ORCL3
C:\>sqlplus / as sysdba
Connected to an idle instance.
SQL> @H:\controlfile.ctl
ORACLE instance started.
Control file created.
Step 08: Open the database with resetlogs option.
SQL> alter database open resetlogs;
Finally check the cloned database name and database id. The above procedure is useful in that case when we need to clone or duplicate the database in this way, it will not change the DBID as it was in source database. You can change the DBID later (if required) using the utility: DBNEWID

0 comments:

Post a Comment