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 Export/Import

If you are cloning a database from one platform (such as AIX) to another platform (such as NT), or, possibly, from one version of Oracle to another version of Oracle (which I haven't tried - you're on your own on this!), or, if you don't want to use the datafile copy cloning technique (such as if you are on Oracle 8 or above and want to use RMAN at some later date on your current and cloned databases, or if you want to rebuild your current database), you can do the clone by creating a full export file of your source database, creating a stub target database with a similar datafile structure, and importing the file into your target database.  Before doing the import, you'll probably have to create a big rollback segment (taking the others offline) and a big temp tablespace to handle the large tables being imported, then, revert back to the original rollback segments and temp tablespace after the import to handle your normal operations (or, instead of creating a big rollback segment, you may just be able to use the commit=y and buffer=3072000 (or whatever size) import options to limit the rollback space needed by the import).   You'll also need to set up some additional grants for Banner which aren't handled by the import, and do some other cleanup to get the clone ready for use in Banner (most of the steps before this are generic to any Oracle database).

The stub.sql script has been written to generate the sql (stub_create.sql) to create a database using the current database as a model, including all of it's logfiles and logfile groups, system datafiles, all tablespaces and their datafiles and default storage settings, and all rollback segments, along with running the Oracle catalog procedures (catalog, catproc, catexp, catrep, catoctk, and caths).  If there are other Oracle catalog procedures you need to run for your setup, you'll either need to edit stub.sql to add them before running it, or modify the generated stub_create.sql file to add them.  Please check the generated stub_create.sql script before running it to see if it is sufficient for your database and your version of Oracle (this was created in Oracle version 8.0.5, and I've used it for 8.1.7 cloning).

You may have to edit the resulting stub_create.sql file to put in the new Oracle SID, the new init.ora parameter file name, and the new pathnames for all of the datafiles and redo log files (but, most of those changes can be made through prompts in stub.sql). You will also have to create the new init.ora parameter file for the new Oracle SID, copying the current database's init.ora parameter file to it and changing the database name and file pathnames in that new init.ora file, along with setting db_block_size, if not already included in it (otherwise, it defaults to 2048). Finally, remove the EXIT command in stub_create.sql when you are sure that you've made all of the appropriate renaming changes.

Before running the edited stub_create.sql file in the server manager to create the new database, first make absolutely sure that the ORACLE_SID environment variable is set to the new database SID. If you are pointing to an existing database's SID, or, if you didn't change the SID in the CREATE DATABASE command in stub_create.sql, YOU WILL DESTROY YOUR CURRENT DATABASE!!! Also, make sure that your current working directory is set to the equivalent ORACLE_HOME rdbms admin directory, so that the script can find all of the Oracle catalog procedures to run.

The steps in cloning from an existing database on AIX to a new database on NT using export/import are shown below.  If your new database is also on AIX, you would need to modify the steps accordingly (such as updating /etc/oratab and start_jobsub.shl described in the datafile cloning procedure above).  The details of this cloning procedure are in stub.txt.

The steps in cloning from an existing AIX database (PROD) to a new NT database (TEST) using export/import are as follows (where ORACLE_HOME is the directory containing Oracle on the NT, such as d:\oracle\v805):

1. Run stub.sql on the source database to generate the database creation commands.
2. Export the full source database from user ID system, making sure to use the "compress=n" option, and possibly using a unix pipe and gzip to zip the resulting dump file in parallel with the export (shown in stub.txt).
3. Bring up a DOS Prompt session on the NT and set ORACLE_SID to TEST.
4. Create the directories on the NT needed by the new database, including bdump, udump, and archivelog directories, as needed (NT doesn't use the cdump directory).  (Or, if you are doing subsequent export/imports, make sure the TEST database is shut down and delete all datafiles, control files, and redo log files for it.)
5. FTP or copy the resulting stub_create.sql and (zipped) export dump file, along with the source database's init.ora file (for initial creation), from the AIX to the NT.
6. Unzip the dump file (using WinZip or some other zip utility) on the NT, if needed.
7. Move and rename the init.ora file to the ORACLE_HOME\database directory on the NT as initTEST.ora, and edit the parameters in it (such as control files, dump and archive dest's, db name, and db_block_size), to use the NT pathnames and new SID (TEST), commenting out core_dump_dest for the NT.  (This step probably not needed for subsequent export/imports.)
8. Edit stub_create.sql to put in the new Oracle SID, the new init.ora parameter file name, and the new pathnames for all of the datafiles and redo log files, and modify the pathnames to the Oracle catalog procedures (such as ORACLE_HOME\rdbms80\admin\catalog.sql).
9. Create the Oracle services to run on the NT using the oradim command and initTEST.ora file.  (This step is not needed for subsequent export/imports.)
10. Change directory in the NT DOS session  to ORACLE_HOME\rdbms80\admin (or equivalent), bring up Server Manger (such as svrmgr30) and run stub_create.sql.
11. Change the sys and system passwords as needed.
12. Create a large rollback tablespace and large rollback segment, and drop and recreate the temp tablespace to be large  (you'll need to shut down the TEST database to delete it's datafiles).
13. Online the large rollback segment and offline the other rollback segments.
14. Exit Server Manger and do the full import.
15. Bring up Server Manager, online the original rollback segments, and offline the large rollback segment.
16. Drop the large rollback segment and its tablespace, and drop and recreate the temp tablespace (you'll need to shut down the TEST database to delete their datafiles).
17. Edit the listener.ora and tnsnames.ora files in ORACLE_HOME\net80\admin to add sections for the new SID.  (This step is not needed for subsequent export/imports.)
18. Stop and restart the listener service.  (This step is not needed for subsequent export/imports.)
19. Grant privileges on certain sys tables and routines to bansecr, public, and oas_public, as needed (shown in stub.txt; Banner specific).
20. Compile all routines using the guraltr script (Banner specific).
21. Make other updates for the new SID, such as changing the instance name in GUBINST (Banner specific)
22. Start up archiving, if needed (assuming "log_archive..." parameters have already been set up in the initTEST.ora file).

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