Details Of Cloning Procedure on a Windows server (updated 7/10/06)

   The following example shows the creation of a clone database instance called TEST from
   the current PPRD database on a Windows server.  The commands marked with an asterisk (*)
   are only needed if this is the first time you have done this clone, and are not needed
   for subsequent re-cloning.

   WARNING: This cloning procedure may not work with some of the Oracle 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).  So, if you want to use those
   tools, you will need to find some other way to clone your Oracle database or change the
   cloned database ID before using those tools (see at end).  (Thanks to John Morgan of
   SCT for this tip.)

*  Create the init.ora file for TEST:
      Bring up an MS/DOS Command Prompt shell
      D:
*     cd d:\oracle\v9206\database
*     copy initPPRD.ora initTEST.ora
*     edit initTEST.ora
         Change all references to PPRD into TEST.  You might also want to turn off
         the archiving process (log_archive_start=false).  Also, change the file names
         if you are putting the clone's database files on a different disk volume or in
         a different directory structure.
   Point to the current instance being cloned:
      set ORACLE_HOME=d:\oracle\v9206
      set ORACLE_SID=PPRD
         Set the oracle SID to the name of the instance to copy (PPRD here).
      cd d:\oradata\pprd
   Generate the "make directory" (if needed) and "copy file" commands for the new clone,
   including copying datafiles, temp files, redo logs, and control files (also change the
   file names in the resulting cp_dbfiles.bat if you are putting them on a different disk
   volume or in a different directory structure).  Put the following in a script file
   named cp_dbfiles.sql:
      set pagesize 0 recsep off linesize 160 trimspool on feedback off verify off
      column sid new_value sid
      select name sid from v$database;
      define newsid = 'TEST'
      spool cp_dbfiles.bat
      select 'mkdir ' || replace(substr(name,1,instr(name,'\',-1)-1),'&SID','&NEWSID')
         from v$datafile
      union
      select 'mkdir ' || replace(substr(file_name,1,instr(file_name,'\',-1)-1),'&SID','&NEWSID')
         from dba_temp_files
      union
      select 'mkdir ' || replace(substr(member,1,instr(member,'\',-1)-1),'&SID','&NEWSID')
         from v$logfile
      union
      select 'mkdir ' || replace(substr(name,1,instr(name,'\',-1)-1),'&SID','&NEWSID')
         from v$controlfile
      union
      select 'mkdir ' || replace(value,'\&SID\','\&NEWSID\') from v$parameter
         where (name in ('background_dump_dest','user_dump_dest','core_dump_dest',
         'audit_file_dest') or name like 'log_archive_dest%')
         and name not like 'log_archive_dest_state%' and value is not null;
      select 'copy ' || name || ' ' || replace(name,'&SID','&NEWSID') from v$datafile
         order by substr(name,instr(name,'\',-1));
      select 'copy ' || file_name || ' ' || replace(file_name,'&SID','&NEWSID')
         from dba_temp_files order by substr(file_name,instr(file_name,'\',-1));
      select 'copy ' || member || ' ' || replace(member,'&SID','&NEWSID') from v$logfile
         order by substr(member,instr(member,'\',-1));
      select 'copy ' || name || ' ' || replace(name,'&SID','&NEWSID') from v$controlfile
         order by substr(name,instr(name,'\',-1));
      spool off
   then, login as sysdba to the current instance being cloned and run that script file:
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      @cp_dbfiles.sql
   Generate a textual control file to edit later for the new clone instance, and get the
   directory that it is in:
      alter database backup controlfile to trace;
      select value from v$parameter where name like '%user_dump%';
      exit
   Find the latest textual control file generated (should be the last .trc file listed):
      dir -od d:\oradata\pprd\udump
   Edit that latest textual control file and remove all lines before the STARTUP NOMOUNT
   line (of the "Set #2. RESETLOGS case", if there are two sets of creates) and after the
   ALTER TABLESPACE TEMP ADD TEMPFILE command (or the ALTER DATABASE OPEN command, if no
   TEMPFILE command exists), change the SID-based values to match the clone's SID (changing
   from PPRD to TEST here) (plus making any other pathname changes, if needed), change the
   CREATE CONTROLFILE command to SET the database SID and to specify RESETLOGS, change the
   OPEN command to reset the logs, comment out the RECOVER DATABASE command, and change all
   comment lines to start with dashes.
      edit d:\oradata\pprd\udump\pprd_ora_5852.trc
      Some resulting changes:
         CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS  ARCHIVELOG
         -- RECOVER DATABASE USING BACKUP CONTROLFILE
         ALTER DATABASE OPEN RESETLOGS;
   Shut down the current instance to copy:
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      shutdown immediate
      exit
   Change the environment to point to the clone:
      set ORACLE_SID=TEST
         Set the oracle SID to the name of the new instance (TEST here).
   Shut down the clone if it currently exists and is running:
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      shutdown immediate
      exit
   Copy the current instance's database files to the clone (which also creates its
   directories):
      cp_dbfiles.bat
   Copy the edited textual control file to the clone's datafile directory, for example:
      cd d:\oradata\test
      copy d:\oradata\pprd\udump\pprd_ora_5852.trc ctrl.sql
   FYI (PROBABLY NOT NEEDED HERE): If the clone's NT services need to be recreated for
   some reason (which also creates the password file %ORACLE_HOME%\database\PWDtest.ORA),
   shut it down and delete it first by doing:
      set ORACLE_SID=TEST
         Set the oracle SID to the name of the new instance (TEST here).
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      shutdown immediate
      exit
      oradim -delete -sid test
*  Create the NT services for the TEST Oracle instance using oradim (which also creates
   the password file %ORACLE_HOME%\database\PWDtest.ORA, and tells the listener about
   this SID), such as:
      oradim -new -sid test -intpwd <sys password> -startmode auto -pfile d:\oracle\v9206\database\initTEST.ora
   FYI (PROBABLY NOT NEEDED HERE): If the clone's password file needs to be recreated for
   some reason, such as if the sys password is changed, do the following: 
      set ORACLE_SID=TEST
         Set the oracle SID to the name of the new instance (TEST here).
      del %ORACLE_HOME%\database\PWDtest.ORA
      orapwd file=%ORACLE_HOME%\database\PWDtest.ORA password=<sys password> entries=5
   Create the clone's control files (pointing to it's new file locations) and open the clone
   database, using the edited textual control file:
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      shutdown immediate
      @d:\oradata\TEST\ctrl.sql
   Change the global name so that doing a "create database link" to access a remote database
   doesn't give you a "loopback" error:
      select * from global_name;
      update global_name set global_name = 'TEST';
   For Banner, change the jobsub one-up sequence number, starting at minvalue, so that it
   doesn't overlap the original instance's numbers (minvalue should be much greater than
   the original's last_number):
      select * from dba_sequences where sequence_name = 'GJBPSEQ';
      drop sequence general.gjbpseq;
      create sequence general.gjbpseq minvalue 90000 cycle cache 30 maxvalue 99999999;
   For Banner, change the instance name that appears on the top line (title) of the Banner
   forms:
      select gubinst_instance_name,gubinst_name from gubinst;
      update gubinst set gubinst_instance_name = 'TEST0710',
         gubinst_name = 'TEST was cloned on 07/10/06';
   If you are using my password aging script and don't want the passwords to expire in the
   new instance, stop the password aging check (guapswd_change):
      connect bansecr
      execute guapswd_stop;
   Restart the original instance:
      exit
      set ORACLE_SID=PPRD
         Set the Oracle SID to the name of the original instance (PPRD here).
      sqlplus "/ as sysdba" (or sqlplus "sys/<syspassword> as sysdba")
      startup
      exit
*  Edit the tnsnames.ora file on the server (in %ORACLE_HOME%\network\admin\tnsnames.ora)
   and in the client network's (such as Novell) directory to copy the PPRD instance's lines
   and change the copy to match TEST, such as:
      myserver_test = 
        (DESCRIPTION = 
          (ADDRESS_LIST = 
              (ADDRESS = 
                (PROTOCOL = TCP)
                (Host = 111.222.33.4)
                (Port = 1521)
              )
          )
          (CONNECT_DATA = (SID = TEST)
          )
        )

* Not required for subsequent re-clones of this instance.

   If you want to change the internal database ID of the cloned copy so that you can use
   utilities such as RMAN on that copy which requires unique database ID's, you can use
   the Oracle 9i "nid" (new ID) utility to generate a new database ID, as shown below
   (thanks to David Melton of the University of Richmond; added 1/12/04).  Note that I
   haven't tried this, yet, but, you can give it a try if you need to use RMAN on a
   copy-datafile clone.

   set ORACLE_SID=TEST
      Set the oracle SID to the name of the new instance (TEST here).
   sqlplus "/ as sysdba"
   shutdown immediate
   startup mount
   host
   nid SYS/<syspassword>
      Answer the prompt with Y
   exit
   shutdown immediate
   startup mount
   alter database open RESETLOGS;
   shutdown immediate
   startup
   exit