-- Dupe_db_using_exp_imp.txt by Scot Martin
-- 10.2.0.1 (7-SEP-2006) Windows 32bit

Referenced scripts and files:
	create_create_database.sql
	create_create_tablespaces.sql
	full_export_params.txt
	full_import_params.txt

Useful Related Oracle 10gR2 Documentation References:
	Database Upgrade Guide Chapters 2, 8, and 4
	Database Utilities Chapter 19
	Database Aministrators Guide Chapter 2

This file describes how to duplicate a database using the (older) export and import
utilities.  This would be useful to a) upgrade a smaller sized database to 10g or b)
make a duplicate copy of an existing database for testing or other purposes.
Regardless of the goal, Export and Import can be used in cross platform situations.

Chapter 8 of the 10gR2 Upgrade Guide walks through the steps fairly clearly, and my
goal in writing this document is not to retype those steps.  Read that chapter first,
(it is small) and install the oracle database software on the target computer.  As
with all operations, make sure to have a complete backup of the source database.  If
you are using these instructions to upgrade a database, also read and follow chapter 4;
these instructions are intended for an audience that is merely duplicating a database.

Note that a full database export does not include, at the very least, SYS triggers that
are used by various oracle database options.  They must be manually recreated after the
import by reinstalling any such options that are used in your environment.  Also note
that a way to move segments into a different tablespace during an export import operation
is to rely on the fact that if a tablespace does not exist, import will place segments
into the default tablespace of the user doing the import.  Further control over users
and object assignments can be obtained by using the fromuser and touser options of the
import command (which are not shown in the example below).

These instructions assume a Windows environment using operating system authentication.
Also assumed is that default OS specific locations will be used for the new spfile.

1. Create a directory file structure to house the new database, likely based on OFA.

2. Create a parameter file for the new instance based on the existing parameter file,
   and then edit it to work with the new database.  If currently using a pfile, just
   copy it and edit it.  If using an spfile:

   create pfile='new_path_initdupe.ora' from spfile;

   Edit at least the db_name, background_dump_dest, core_dump_dest, user_dump_dest,
   and control_files parameters to point to new locations.  Change all occurrences
   of the source database name with the new name.  Change other params if desired.

3. Count all of the objects owned by each user in the source database for use later
   as a doule check to ensure that they all were imported successfully into dupe.

   select username, count(object_id) from dba_objects, dba_users where username = owner(+)
   group by rollup(username);

   Also run "examine_db.sql" against the source database as a further double check.

4. Generate create tablespace statements for the tablespaces in the source database
   by running "create_create_tablespaces.sql".  Edit the resulting output file as
   needed so that datafile names and paths point to the desired new database location.

5. Generate a create database statement by running "create_create_database.sql" in the
   source database.  Edit the resulting output file as needed for the new database.

6. Create a full database export from the source database.  Ensure that no changes
   will be performed on the source database during the export (to be absolutely
   sure of this, use "startup open read only" when starting up the database).

   When exporting, consider using the file or file_size parameters if desired.

   exp userid='/ as sysdba' parfile=full_export_params.txt

7. Create an instance for the duplicate database.  The following assumes that both the
   new database and new instance will be called "dupe".  If you wanted to create a
   password file for sysdba authentication, this would be the step to run orapwd.

   oradim -new -sid DUPE -startmode manual -srvcstart system

8. Connect to and startup nomount the new instance:

   OSPROMPT> set oracle_sid=dupe
   OSPROMPT> sqlplus / as sysdba
   SQL> create spfile from pfile
   SQL> startup nomount

9. Create the new database by running the create database statement generated earlier.
   Then build the data dictionary by running the catalog.sql and catproc.sql scripts:

   start ?/rdbms/admin/catalog.sql
   start ?/rdbms/admin/catproc.sql

10. Create tablespaces in the new database by running the create statements that were
    generated and edited earlier.

11. Alter the database to assign a default permanent tablespace to all users.

    alter database default tablespace 'USERS';

12. Exit sqlplus and take a cold backup of the new database, as it exists at this point, to
    preserve progress in case there is a problem with the import.

13. From within the same OS session (with oracle_sid still set to dupe), import the old database
    information into the new:

    imp userid='/ as sysdba' parfile=full_import_params.txt

14. Install any optional (nonstandard) database components, such as CDC or OLAP, that
    are used by your environment by referring to the specific component documentaton
    and by running appropriate supplied oracle installation scripts.

15. Configure the database to run in archivelog mode, by adjusting the various
    log_archive parameters as needed for the new database, and by running:

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;

16. Shutdown and take a final cold backup of the new and complete dupe database.