Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

Cloning a Database using Datafile Copy

A copy of a database can be made with a different Oracle SID name in order to create a new test database to try out transactions on, or to clone the PPRD database into the PROD database, or to make a copy from a backup to restore tables from without impacting the current database, or for other purposes. As long as you get all the pieces done, cloning is fairly straightforward, and should take less than an hour to do for a database around 8 gig in size.   The steps in cloning from an existing database to a new database are shown below. These steps would need to be modified slightly if you are restoring a copy from tape to the new database's directories instead of from an existing online database.  The details of this cloning procedure are in cloning.txt for cloning a database on a UNIX server, and in cloning_win.txt for cloning a database on a Windows server.  (This information is also included in my backup and recovery presentation.)

The steps in cloning from an existing database to a new database using datafile copy are as follows:

1. Copy the existing database's init.ora file to the new database's init.ora file, and edit the parameters in the copy to match.
2. Edit the /etc/oratab file to add a line for the new SID (UNIX only).
3. Get the list of pathnames for the existing database's datafiles, control files, and redo log files.
4. Create a textual control file of the existing database.
5. Edit the textual control file from step 4 to match the new SID, directory locations, and file names.
6. Shut down tasks that are running on the existing database, as needed, such as jobsub, posting, and approvals.
7. Shutdown the existing database.
8. Create other directories needed by the new database, such as the bdump, cdump, udump, and archivelog directories.
9. Copy the files from step 3 to the new database's directories, renaming the files as needed to match the new SID.
10. Run the textual control file from step 5, creating the new control files and starting up the new database instance.  (Note that the existing database must be shut down, in most cases, before Oracle will create the control file of the copy.)
11. Update the global name to the new SID (if needed for remote access with create database link).
12. Make other updates for Banner for the new SID, such as changing the jobsub sequence number to not collide with the original, and changing the instance name for GUAINST.
13. Startup the the existing database instance.
14. Edit the listener.ora file to add a section for the new SID (UNIX only).
15. Reload the listener (UNIX only).
16. Edit the tnsnames.ora files on the server and on the client network to add the new SID.
17. Edit the start_jobsub.shl file to add a section for the new SID, if needed.
18. Stop and restart the jobsub processes.
19. Restart other tasks as needed.

Now, except for editing tnsnames.ora on the client network, the new instance is up and running, listener and jobsub recognize it, and the original instance is up and running.  (Most steps should be generic to any Oracle site for the most part; steps 6, 12, 17, and 18 are specific to Banner products.)

WARNING: This cloning procedure may not work with some of the Oracle 8 (and later) tools (such as the RMAN Recovery Manager's recovery catalog, because it uses an internal database ID instead of just the Oracle SID to identify the database - but see below). So, if you want to use those tools, you will need to find some other way to clone your Oracle 8 database.  I heard at the Summit 2000 Technical Forum that you can set up separate RMAN's for databases in Oracle 8, which may allow you to use this cloning procedure (with separate RMAN's for production and for the clone).  Also, in Oracle 8i, you can copy tablespaces between databases, which would allow you to create an empty clone database, then, copy your production data tablespaces to that clone database (dropping and re-copying the tablespaces as needed to refresh the clone data).  I'll try these out sometime when I get a chance.

Oracle has come out with a new ID utility ("nid") in Oracle 9i to change the internal database ID.  So, you can do datafile copy cloning while also using RMAN and other utilities on the cloned copy that depend on unique ID's for the databases.  To change the internal database ID on the cloned copy, do the following (see cloning.txt for the details; thanks to David Melton of the University of Richmond for this information):

1. Shutdown the cloned copy database.
2. Startup the database in MOUNT mode.
3. Run the nid utility as user SYS, answering the prompt with "Y".
4. Shutdown the database again.
5. Startup the database in MOUNT mode.
6. Open the database with the RESETLOGS option.

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI