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.
• In the source
database, issue the following command:
ALTER DATABASE BACKUP CONTROLFILE
TO TRACE;
• 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
• Define the new
$ORACLE_SID variable
• 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.
• Edit the init.ora
file. Make sure you edit:
• Start Server Manager
or SQL Plus for the clone.
• 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
• Shutdown the new clone
database
• 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.
• 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).
• Delete all clone
Control Files
• 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.
Another Method
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.
2) Edit the pfile as it
would be for secondary database server locations
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.