Details Of Cloning Procedure using Export/Import

The following example shows the creation of a clone database instance called
TEST on an NT from the current PROD database on an AIX using export/import.
(Note: "$" means you're at the unix prompt, "SQL>" means you're in sqlplus,
"SVRMGR>" means you're in server manager, "D:\>" means you're at the NT MS/DOS
command prompt, and "ftp>" means you're in ftp.  Also, the PATH environment
variable on the NT should already contain the Oracle executables directory,
such as d:\oracle\v805\bin.)

From the AIX unix system (fill in your own passwords for the sys syspasswd
and the system systempasswd):
$ . oraenv
  PROD
$ cd /home/dba_scripts
  (cd to whatever directory that stub.sql is in)
$ sqlplus system/systempasswd
SQL> @stub.sql
  d:\oradata\test   (parameter to change all datafile pathnames to, if needed)
  d:\oradata\test   (parameter to change all log file pathnames to, if needed)
  test              (parameter to change the Oracle SID to, if needed)
SQL> exit

Export the full PROD database, doing file compression using gzip in parallel
using a unix pipe (your unix security may require you to be in user ID root
to create and remove a pipe):
$ mknod /tmp/exp_pipe p
$ gzip -cNf </tmp/exp_pipe >prod.dmp.gz &
$ echo systempasswd | exp system file=/tmp/exp_pipe full=y compress=n
$ rm -f /tmp/exp_pipe

From the NT system, bring up an MS/DOS Command Prompt session (which is used
for the rest of the commands in this example; make sure to set ORACLE_SID),
and create directories for the new TEST database, such as:
D:\> set ORACLE_SID=TEST
D:\> mkdir d:\oradata\test
D:\> mkdir d:\oradata\test\bdump
D:\> mkdir d:\oradata\test\udump
D:\> mkdir d:\oradata\test\archivelogs  (if needed)
(Or, if you are doing subsequent export/imports, make sure the TEST database
is shut down, then, delete all of TEST's datafiles, control files, and redo
log files here.)

FTP the files (stub_create.sql, prod.dmp.gz, and initPROD.ora, if needed) to
the NT from the AIX (such as myhost.mysite.edu), such as:
D:\> cd d:\oradata\test   (or, "lcd d:\oradata\test" in ftp after open)
D:\> ftp
ftp> open myhost.mysite.edu
  (open whatever URL that points to your AIX box; then, probably enter the
  user ID and password for the AIX oracle user here)
ftp> cd /home/dba_scripts
  (cd to whatever directory that stub_create.sql was generated in)
ftp> type ascii
ftp> get stub_create.sql
ftp> cd /u05/oradata
  (cd to whatever directory that prod.dmp.gz was generated in)
ftp> type binary
ftp> get prod.dmp.gz
ftp> cd /u00/oracle/product/v805/dbs
  (cd to whatever directory that initPROD.ora is in)
ftp> type ascii
ftp> get initPROD.ora
ftp> bye

Unzip prod.dmp.gz using WinZip or some other zip utility, creating prod.dmp.

Create (copy/rename/edit) the init.ora file (initTEST.ora), such as:
D:\> copy initPROD.ora d:\oracle\v805\database\initTEST.ora
Edit initTEST.ora to contain the new pathnames and SID, along with setting
the db_block_size that was listed when stub.sql was run (and comment out the
core_dump_dest line for NT).  (Another initTEST.ora is probably not needed
for subsequent export/imports, unless it was changed in PROD.)

Edit stub_create.sql to contain the new pathnames and SID, including the pfile
parameter file pathname (probably also giving a full file pathname for the spool
command).

If you already had a TEST oracle instance defined, you will probably have to
delete the NT services for TEST and recreate them in order to keep from getting
an "invalid password file" error during the "create database" command.  To delete
the NT services for TEST (which deletes the password file):
D:\> oradim80 -delete -sid test

Create the NT services for the TEST Oracle instance using oradim, such as:
D:\> oradim80 -new -sid test -intpwd syspasswd -startmode auto -pfile d:\oracle\v805\database\initTEST.ora
D:\> oradim80 -startup -sid test -starttype srvc,inst -usrpwd syspasswd -pfile d:\oracle\v805\database\initTEST.ora
(Note: If you change the SYS password in this NT database, and you have the
NT services set up to automatically start the database (-startmode auto) when
the server is rebooted, you must also change the password in the strtTEST.cmd
file, using a text editor, in the d:\oracle\v805\database directory.)

Run the edited stub_create.sql from the Oracle admin directory, such as:
D:\> cd d:\oracle\v805\rdbms80\admin
D:\> svrmgr30
SVRMGR> @d:\oradata\test\stub_create.sql
If there are problems creating the database (such as invalid password file),
shutdown immediate, remove the database's files, delete the NT services
(oradim80 -delete -sid test), recreate the NT services as shown above, and
rerun stub_create.sql.

Change the sys and system passwords, then, create large rollback and temp
segments to handle the full import, such as:
SVRMGR> alter user sys identified by syspasswd;
SVRMGR> alter user system identified by systempasswd;
SVRMGR> create tablespace rbs_big
  datafile 'd:\oradata\test\rbsbTEST_1.dbf' size 400008K
  default storage (initial 6144000 next 6144000 pctincrease 0
  minextents 2 maxextents 249);
SVRMGR> create rollback segment rbs_big tablespace rbs_big
  storage (initial 6144000 next 6144000 minextents 3
  maxextents 66);
SVRMGR> select file_name from dba_data_files where tablespace_name = 'TEMP';
SVRMGR> drop tablespace temp;
SVRMGR> shutdown immediate
SVRMGR> host
D:\> del d:\oradata\test\tempTEST_1.dbf
D:\> del d:\oradata\test\tempTEST_2.dbf
D:\> del d:\oradata\test\tempTEST_3.dbf
D:\> exit
SVRMGR> startup pfile=d:\oracle\v805\database\initTEST.ora
SVRMGR> create tablespace temp
  datafile 'd:\oradata\test\tempTEST_1.dbf' size 420008K temporary
  default storage (initial 6144000 next 6144000 pctincrease 0
  maxextents 249);
SVRMGR> select segment_name,status from dba_rollback_segs;
SVRMGR> alter rollback segment rbs_big online;
SVRMGR> alter rollback segment rbs1 offline;
SVRMGR> alter rollback segment rbs2 offline;
SVRMGR> alter rollback segment rbs3 offline;
SVRMGR> exit

Do the full import:
D:\> cd d:\oradata\test
D:\> imp80 system/systempasswd file=prod.dmp full=y ignore=y log=prod.log

Revert back to the original rollback and temp segments (you can see the
original definition in stub_create.sql), such as:
D:\> svrmgr30
SVRMGR> connect internal
SVRMGR> select segment_name,status from dba_rollback_segs;
SVRMGR> alter rollback segment rbs1 online;
SVRMGR> alter rollback segment rbs2 online;
SVRMGR> alter rollback segment rbs3 online;
SVRMGR> alter rollback segment rbs_big offline;
SVRMGR> select file_name from dba_data_files where tablespace_name = 'RBS_BIG';
SVRMGR> select file_name from dba_data_files where tablespace_name = 'TEMP';
SVRMGR> drop rollback segment rbs_big;
SVRMGR> drop tablespace rbs_big;
SVRMGR> drop tablespace temp including contents;
SVRMGR> shutdown immediate
SVRMGR> host
D:\> del d:\oradata\test\rbsbTEST_1.dbf
D:\> del d:\oradata\test\tempTEST_1.dbf
D:\> exit
SVRMGR> startup pfile=d:\oracle\v805\database\initTEST.ora
SVRMGR> create tablespace temp datafile
  'd:\oradata\test\tempTEST_1.dbf' size 81920K,
  'd:\oradata\test\tempTEST_2.dbf' size 40008K,
  'd:\oradata\test\tempTEST_3.dbf' size 80008K temporary
  default storage (initial 256K next 1024K pctincrease 0
  minextents 1 maxextents 249);
SVRMGR> exit

Add sections for the new TEST Oracle SID to the listener.ora (in SID_LIST)
and tnsnames.ora (new host_TEST.WORLD) files in the Oracle Net80 Admin
directory (such as d:\oracle\v805\net80\admin).  Then, stop and restart
the listener service (from the NT Start button, go to Settings, Control
Panel, Services, click on OracleTNSListener80, click the Stop button,
then, click the Start button).  (This step is not needed for subsequent
export/imports.)

Set up additional grants and make other changes for Banner:
D:\> sqlplus sys/syspasswd
SQL> grant select on dba_role_privs to bansecr;
SQL> grant select on dba_sys_privs to bansecr;
SQL> -- grant select on exu7dfr to bansecr;  (if running Oracle 7?)
SQL> -- grant select on exu8dfr to bansecr;  (if running Oracle 8?)
SQL> grant select on user$ to bansecr;
SQL> grant select on defrole$ to bansecr;
SQL> grant select on dba_roles to bansecr;
SQL> grant execute on dbms_pipe to public;

If you are using the Web products and OAS (substitute your own values for
the CGI-BIN Web Rule for the "Web For" products):
SQL> grant execute on dbms_sys_sql to oas_public;
SQL> update wtailor.twgbwrul
  set twgbwrul_cgibin_dir = '/server_test/plsql'
  where twgbwrul_cgibin_dir = '/server_prod/plsql';

If you are using my password aging routines:
SQL> grant select on dba_users to bansecr;
SQL> grant select on dba_jobs to bansecr;

Other grants and changes:
SQL> connect baninst1
SQL> grant execute on baninst1.G$_foreign_sql_pkg to bansecr;
SQL> connect system
SQL> @guraltr
  (run guraltr several times for dependencies)
SQL> update gubinst set gubinst_instance_name = 'TEST';

To keep your test and production job submission numbers from running
over each other:
SQL> select * from dba_sequences where sequence_name = 'GJBPSEQ';
SQL> drop sequence general.gjbpseq;
SQL> create sequence general.gjbpseq minvalue 90000 cycle cache 30 maxvalue 99999999;

If you are using my password aging routines on PROD, but, don't want
them to run on TEST:
SQL> connect bansecr
SQL> execute guapswd_stop;
SQL> exit

Start up archiving ("create database" defaults to noarchivelog mode),
if needed (assuming the "log_archive..." parameters have been set up
in the initTEST.ora file):
D:\> svrmgr30
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup mount pfile=d:\oracle\v805\database\initTEST.ora
SVRMGR> alter database archivelog;
SVRMGR> alter database open;
SVRMGR> exit

The end.