Cloning a Database

Cloning a database means create an identical copy of a database, either on the same machine as the original, or on a different one. Additionally, it means changing the database name, and the Instance name. But those are both optional steps if the clone is being created on a different machine. Even when it's all happening on the one machine, the only requirement is to change the Instance name -changing the database name remains entirely optional.
What follows is therefore a set of instructions that takes things one-step at a time: first, the clone. Second the renaming of the Instance. Third (if you really want to) is the renaming of the Database. You can do it all in one go. But, if different machines are involved, there's strictly no requirement for steps 2 and 3 -so I've kept them separate. It goes without saying (I hope) that if you are cloning onto a new machine, the Oracle executables and environment variables (like ORACLE_BASE and ORACLE_HOME) need to bein place before you even start.

Step 1: The Actual Cloning

• Perform a clean shutdown of the source database or perform a hot backup of that DB

• Copy the source Data Files, Redo Logs, init.ora files and control file trace to the clone location

• Locate the Control File trace script made earlier (in your UDUMP directory):

o Clear out the junk at the top, so that the first line reads STARTUP NOMOUNT.

o Edit this statement to read:
CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS ARCHIVELOG

o Add to the STARTUP NOMOUNT line a reference to where the new init.ora is to be located (i.e., the line should read STARTUP NOMOUNT PFILE=/SOMEWHERE/INIT<SID>.ORA)

o Also edit all the file locations mentioned in the trace file so that they point to appropriate clone locations. For example, when it says LOGFILE GROUP 1 'D:\ODATA\BLAH\LOG1A.RDO’, change that to read
LOGFILE GROUP 1 ‘C:\SOMEWHERE_NEW\LOG1A.RDO'.

Do that for all file location references.

o CONTROL_FILES= (point to where you want the clone's Control Files created)
o LOG_ARCHIVE_DEST (and variants) (the source archives must not be overwritten by the clone's)
o USER_DUMP_DEST & BACKGROUND_DUMP_DEST (source trace files must not be overwritten by the clone)
o INSTANCE_NAME

• Run the Trace File script (ie, type @NAME_OF_SCRIPT). That will then connect you as per the connect line you added to the script, startup in the nomount stage, and issue the 'create controlfile' commands. At the end of the exercise, your database should be left in the fully open state, with all the required controlfiles in place.

At this point, you make sure everything is working perfectly. Once you know it is, it is time to consider re-naming the Instance and the Database

Step 2: Changing the Instance Name

• Exit completely out of SQL Plus or Server Manager (you can't set an environment variable if you simply shell out of those applications).

• Use the appropriate O/S command to set a new ORACLE_SID. On Unix, that means type EXPORT ORACLE_SID=XXX, and on NT, that means type SET ORACLE_SID=XXX

• Run SQL Plus or Server Manager once more, connect as a Privileged User, and issue the
STARTUP PFILE=/WHEREVER/<NAME OF INIT.ORA> command.

Note that on NT you'd have to use ORADIM (or, for versions before 8i, ORADIM80 or ORADIM73) to create a Service for the new Instance name before you can start it up (you still need to change the ORACLE_SID as well). On Unix, the change of ORACLE_SID is sufficient in itself.

Also note that I'm assuming here that you're using Operating System authentication for the Privileged User. If you've got a Password File instead, you'll need to copy the Password File from the primary database into the standard default location, and change its name to be ORAPW<SID>, otherwise you'll never get authenticated properly.

Step 3: Changing the Database Name

• While connected to the Clone database, issue the Alter Database Backup Controlfile to Trace command once more.

• Perform a clean Shutdown of the clone. (Shutdown Immediate will do, but don't try a Shutdown Abort).

• Locate the Control File trace script created earlier and make the following amendments to it:

o strip out all the rubbish at the top so that the first line reads STARTUP NOMOUNT.

o Add in a line right at the top which connects as a Privileged UserCONNECT / AS SYSDBA).

o Add to the "startup nomount" line a reference to where the new init.ora is to be located (i.e., the line should read STARTUP NOMOUNT PFILE=/SOMEWHERE/INIT<SID>.ORA).

o Change the CREATE CONTROLFILE REUSE 'X' NORESETLOGS..... line to read CREATE CONTROLFILE SET 'Y' RESETLOGS..... In other words, X is the old name, and Y is the new one. And yes, you're going to have to do a Resetlogs after this procedure.

o Finally, change the line ALTER DATABASE OPEN to ALTER DATABASE OPEN RESETLOGS.

• Edit the clone init.ora and change the db_name parameter to match the new database name

• Start SQL Plus or Server Manager and run the Control File trace script (by typing @NAME_OF_SCRIPT). As before, the database should eventually be left in the fully open state. Do a SELECT * FROM V$DATABASE to check that the database name really has changed.

• If you care about your clone, you should now perform a clean shutdown and backup, because the resetlogs that was issued will have rendered all prior backups and archives of the primary system completely useless as a way of recovering the clone.

Note that where these instructions refer to 'starting SQL Plus or Server Manager', they are assuming at least an Oracle 8i database. In all versions prior to that, Server Manager was the only tool able to perform startups and shutdowns.

1. If your database is not in ARCHIVE LOG mode, take an off-line database backup. If in ARCHIVELOG MODE, you can either take a hot or cold database backup.

2. On your production database, connect to "SYS AS SYSDBA" and:
    SQL> alter database backup controlfile to trace;

3. Edit the controlfile trace (in $ORACLE_BASE/admin/$ORACLE_SID>/udump) and remove the lines until you get to the 'CREATE CONTROLFILE ..' statement. Edit this stement to read:
    CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS ARCHIVELOG

4. Move this script to the new machine. Edit this script to update the directories for the datafiles and redo log files. Also comment out the lines for "RECOVER ..." and "ALTER DATABASE OPEN ..."

5. Restore/ or copy the backup and the archived logs (if any) to the new machine.

6. Copy your INIT.ORA file to the new machine. Ensure your controlfile and archive destinations in init.ora file are set properly on the new machine.

7. Set your Oracle Environment by running ". oraenv" on Unix.

8. Start sqlplus, connect SYS AS SYSDBA (internal) and run the create controlfile script.

9. Perform a database recovery using
   RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

You'll be prompted to supply an archived log file, Restore that file to the archive log destination (if not already there) and continue. Repeat for all archived files till you get to the last one. At that point specify 'CANCEL'.

10. Issue the command:
    ALTER DATABASE OPEN RESETLOGS;

11. Change the global name of the new database to avoid problems with networked application and replication:
   ALTER DATABASE RENAME GLOBAL_NAME = new_db_name;

12. Include the new database into your backup strategy.

You're all done.

Another Method
Environments
1. Primary or the Source database system
2. Secondary or the Target database system

Assumption
Primary database operated in ARCHIVELOG mode.
Secondary database to be created by cloning in a different server
Primary and Secondary environment Operating system are same.

Cloning Process
A. Steps to be followed in Primary database:
1)    Perform HOTBACKUP of all the data files
2)    LSN number
Login as sys as sysdba
Sql> Alter system archive log current;
Sql> Archive log list
      Note down the Current Log Sequence
3)    Control file trace
In Sql> prompt issue the statement
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
You can find the trace file in the udump directory.

B. Steps to be followed in the Secondary database server:
1)    If the secondary database is to be created in a different server
FTP the following to a temporary location in the secondary database server.

a)    hot backup of all the primary database data files
b)    all the archive log files generated in the primary database after you start hot backup
c)    control file trace of primary database
d)    pfile of the primary database

a)    Control_files- new locations
b)    instance_name – new name
c)    background_dump_dest – new location
d)    core_dump_dest- new location
e)    user_dump_dest – new location
f)    log_archive_dest- new location

Save the pfile as init_<SID>.ora in
$ORACLE_HOME/dbs directory if UNIX
$ORACLE_HOME\database if WINDOWS
Where SID is the instance name given above in the edited pfile

3)    In the control file trace Use the “SET #2. RESTLOGS case” CREATE CONTROLFILE section (Since we don’t use the primary database redolog files, we use this option). In that section Copy from “CREATE CONTROLFILE” to “CHARACTER SET <value>” to another file.
       Save the file as cr_control.sql
Edit the following section in cr_control.sql
a)    Use SET instead of REUSE
Old: CREATE CONTROLFILE REUSE DATABASE <"PRIMARY_DBNAME" >RESETLOGS
NEW: CREATE CONTROLFILE SET DATABASE <"SECONDARY_DBNAME"> RESETLOGS
b)    Edit the LOGFILE paths as per the secondary database environment
c)    Edit the DATAFILE paths as per the secondary database environment
d)    Save the cr_control.sql file

4)    Move the ftp ed Hot backup DATAFILE from temporary location of specific location as specified in the control file cr_control.sql

5)    Move the archived logs from temporary location to the location specified in log_archive_dest in pfile

6)    If Windows,
    Create an oracle service using ORADIM utility
ORADIM -NEW -SID sid | -SRVC service [-INTPWD password] [-MAXUSERS number] [-STARTMODE a|m] [-PFILE file] [-TIMEOUT secs]
In the CMD prompt
SET ORACLE_SID=<NEW_SID>
Sqlplus “/ as sysdba”
You will get a Connected to an idle instance message.

7)    If UNIX,
    In the $ prompt give
EXPORT ORACLE_SID=<new_sid>
Sqlplus “/ as sysdba”
You will get a Connected to an idle instance message.

8)    In the SQL> Prompt give
STARTUP NOMOUNT

9) Run the cr_control.sql file in the SQL> prompt
If Windows
@<path>/cr_control.sql

If Unix
@<path>\cr_control.sql if in Unix
You should get a message like Control File created

9)    Recover the database using backup control file. In the SQL> prompt give
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Note: this can also be done with UNTIL TIME or UNTIL SCN provided you know the time or the SCN until which you want to perform the recovery

10)    The recovery process will keep prompting for archive logs. Give return for each prompt of archive log.
Since the required archive logs from Primary database are restored in archive_log_dest of secondary, it will get applied for every return you give in the prompt.
Once the recovery process prompts for the archive log with the LSN number noted in the Primary database, in step 2) of Steps to be done in Primary database section
Give CANCEL in the recovery sql prompt

11)    Now you must get a message like
Media recovery cancelled.

12) Now in the SQL prompt give.
        ALTER DATABASE OPEN RESETLOGS;
         You should get a message like.
        Database opened.

12)    Bounce the database
SHUTDOWN IMMEDIATE;
STARTUP;

13)    Check for errors in bdump.

14)    Check log switch.

15)    Check redo log archival

16)    Compare the users, objects, size with the Primary database

17)    Take a complete cold back up and maintain that as generation 0 backup.

You have cloned the database.

Conclusion
Hot backup recovery is one of the best methods to clone database because there is no outage or down time required in the primary database and data is replicated in secondary database to the most recent time period.