Details Of Cloning Procedure on a UNIX Server (updated 7/14/06)

   The following example shows the creation of a clone database instance called TEST from
   the current PPRD database on a UNIX 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:
      login oracle
         Setting the oracle SID to the name of the instance to copy (PPRD here).
*     cd $ORACLE_HOME/dbs
*     cp -p initPPRD.ora initTEST.ora
*     vi initTEST.ora
         Change all references to PPRD into TEST (vi command is ":1,$s/PPRD/TEST/g").
         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.
*  Add the clone's entry to the /etc/oratab file, like "TEST:/pgms/oracle/product/v9203:N",
   setting the last character in that entry to Y to have the clone start up automatically
   during dbstart, or to N for only manual startups.
*     vi /etc/oratab
         The vi commands to make most of these changes are usually:
         :/PPRD/co$
         :$s/PPRD/TEST/
         :wq
   Point to the current instance being cloned:
      cd /home/oracle
      . oraenv
         Set the oracle SID to the name of the instance to copy (PPRD here).
   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.shl 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. (But, see NOTE below if you are using my backup scripts and have
   unzipem.shl):
      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.shl
      select 'mkdir -p ' || replace(substr(name,1,instr(name,'/',-1)-1),'&SID','&NEWSID')
         from v$datafile
      union
      select 'mkdir -p ' || replace(substr(file_name,1,instr(file_name,'/',-1)-1),'&SID','&NEWSID')
         from dba_temp_files
      union
      select 'mkdir -p ' || replace(substr(member,1,instr(member,'/',-1)-1),'&SID','&NEWSID')
         from v$logfile
      union
      select 'mkdir -p ' || replace(substr(name,1,instr(name,'/',-1)-1),'&SID','&NEWSID')
         from v$controlfile
      union
      select 'mkdir -p ' || 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 'cp -p ' || name || ' ' || replace(name,'&SID','&NEWSID') from v$datafile
         order by substr(name,instr(name,'/',-1));
      select 'cp -p ' || file_name || ' ' || replace(file_name,'&SID','&NEWSID')
         from dba_temp_files order by substr(file_name,instr(file_name,'/',-1));
      select 'cp -p ' || member || ' ' || replace(member,'&SID','&NEWSID') from v$logfile
         order by substr(member,instr(member,'/',-1));
      select 'cp -p ' || name || ' ' || replace(name,'&SID','&NEWSID') from v$controlfile
         order by substr(name,instr(name,'/',-1));
      spool off
      !sed '/^[^cm]/d' cp_dbfiles.shl >cp_dbfilesx.shl; mv cp_dbfilesx.shl cp_dbfiles.shl
   then, login as sysdba to the current instance being cloned and run that script file:
      sqlplus "/ as sysdba"
      @cp_dbfiles.sql
   NOTE: If you use my backup scripts, which create an unzipem.shl script, you can copy
   that unzipem.shl script, such as: cp -p unzipem.shl unzipem_clone.shl, and edit that
   unzipem_clone.shl script to unzip the files from that backup to be used for the clone.
   The following vi commands can be used to make those edits, which (in the order shown
   below) remove the unzips of the trace files (*.trc), archivelog files (*.arc), and
   init.ora files, uncomment the unzips of the control files (ctrl*) and redo log files
   (log*), and change the SID-based target directories and target file names from using
   the current database (PPRD) to using the clone database (TEST).
      :g/\.trc/d
      :g/\.arc/d
      :g/initTEST\.ora/d
      :1,$s/^# \(gunzip.*ctrl\)/\1/
      :1,$s/^# \(gunzip.*log\)/\1/
      :1,$s/\/PPRD\//\/TEST\//g
      :1,$s/\([^g][^z][^i][^p][^1-2]\/[^.\/][^.\/]*\)PPRD/\1TEST/g
   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):
      ls -ltr /pgms/oradata/PPRD/udump | tail
   Edit that latest textual control file and remove all lines before the STARTUP NOMOUNT
   line 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 and NOARCHIVELOG, change the OPEN command to reset the logs, comment out the
   RECOVER DATABASE command, and change all comment lines to start with dashes.
      vi /pgms/oradata/PPRD/udump/pprd_ora_1142812.trc
         The vi commands to make these changes (for 9i+) are usually:
            :1,/STARTUP NOMOUNT/-1d
            :/ALTER TABLESPACE/+2,$d
            :1,$s/PPRD/TEST/g
            :/CREATE CONTROLFILE/s/DATABASE/SET DATABASE/
            :/CREATE CONTROLFILE/s/" .*$/" RESETLOGS NOARCHIVELOG/
            :/ALTER DATABASE OPEN/s/OPEN;/OPEN RESETLOGS;/
            :/RECOVER DATABASE/s/^/# /
            :1,$s/^#/--/
            :wq
   Shut down tasks that are running on the current instance, such as jobsub, posting,
   approvals, and my daemon process (if you are using that) as needed, running these
   from a separate unix (telnet) session, such as:
      login root
      su - jobsub
      kill -9 -1
      rm /home/jobsub/PPRD.fgractg  /home/jobsub/PPRD.forappl
        (remove the posting and approvals flag files, then, wait till the posting
        and approvals processes and sleep/wake have completely terminated)
      su - oracle
         PPRD
      daemon_stop.shl
      exit
   Shut down the current instance to copy:
      sqlplus "/ as sysdba"
      shutdown immediate
      exit
   Change the environment to point to the clone:
      . oraenv
         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"
      shutdown immediate
      exit
   Copy the current instance's database files to the clone (which also creates its
   directories; or, if you are copying from a backup directory created by my backup
   scripts, run the "mkdir" commands in cp_dbfiles.shl (but not the "cp" commands)
   and, then, run the unzipem_clone.shl script instead of running the cp_dbfiles.shl
   script below):
      sh cp_dbfiles.shl
   Copy the edited textual control file to the clone's datafile directory, for example:
      cp -p /pgms/oradata/PPRD/udump/pprd_ora_1142812.trc /data/oradata/TEST/ctrl.sql
   You may need to change /usr/lbin/oraenv to call the correct banenv file to set the BANNER
   environment for the clone.
*  Create the clone's password file, if needed: 
*     rm $ORACLE_HOME/dbs/orapwTEST
*     orapwd file=$ORACLE_HOME/dbs/orapwTEST 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"
      @/data/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 = 'TEST0609',
         gubinst_name = 'TEST was cloned on 06/09/05';
   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
      . oraenv
         Set the Oracle SID to the name of the original instance (PPRD here).
      sqlplus "/ as sysdba"
      startup
      exit
*  Set up the listener to be able to connect to the clone:
*     lsnrctl status
         Shows the pathname of the Listener Parameter File (listener.ora).
*     vi $ORACLE_HOME/network/admin/listener.ora
         Edit the listener.ora file to copy the PPRD lines and change the copy to match TEST,
         such as:
            (SID_DESC=
             (SID_NAME=TEST)
             (ORACLE_HOME=/pgms/oracle/product/v9203)
            )
*     lsnrctl reload
         Note: Before doing this reload, you may want to switch to some other SID, such as PROD.
*  Edit the tnsnames.ora file on both the server (in $ORACLE_HOME/network/admin/tnsnames.ora)
   and in the client network's (such as Novell) orawin\net80\admin directory to copy the PREP
   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)
          )
        )
*  If you have a jobsub startup script, you might want to add the new clone instance (TEST)
   to it:
*     su - jobsub
*     vi start_jobsub.shl
         These lines log into jobsub (if you are allowed to use "su";
         otherwise, just login to jobsub, etc.) and edit the jobsub startup
         script to include the TEST instance (copy PPRD's lines and change
         to match TEST):
            ORACLE_SID=TEST; export ORACLE_SID; . oraenv
            echo "=== Starting jobsubmission for $ORACLE_SID.......       "
            nohup sh $BANNER_LINKS/gurjobs.shl > gurjobsTEST.out 2>&1 &
   You may need to also start up other tasks that need to run on the current instance, such
   as jobsub, sleep/wake (posting and approvals), and my daemon process (if you are using
   that) as needed, such as:
      login root
      su - jobsub
      start_jobsub.shl
      start_sleep_wake.shl
      exit
      su - oracle
          PPRD
      daemon_start.shl
      exit

* 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.

   . oraenv
      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