**********
* File:		CloneDB.txt
* Date:		07/08/2002
* Owner:	GNU
* Created by:	Neil MacDannald
* Modified:	Neil MacDannald - 07/15/02 - Make file specific to S2000.
*
***
* This file is used to clone a database.  
* Normally we are cloning a new test database from the production.
* These are the steps necessary to do that.
*
**********
*
* Step 1) Get a good cold backup of the source database.  
* Do not allow any users or updates until the copy of production source files completes.
*
***
* Step 2) Startup the source (production) database.
*	  Startup the existing target (test) database.
*
***
* Step 3) Create a backup of the source database's 
*	  control file with the following command.
* In production environment (orap816) start sqlplus and enter this command.
 ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
*
* This will create a trace file under the current SID's udump directory.
* This file is edited and used to create the new test database.
*
***
*	Create a backup of the existing target database's 
*	  control file with the following command.
* In test environment (orat816) start sqlplus and enter this command.
*
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
*
* This will create a trace file under the current sid's udump directory.
* This file is used for reference so we know where the datafiles
* were on the test database.
*
***
*
* Step 4) Shut down the source and target databases. 
*	  Close the SSH window for the production database so you 
*	  won't remove the wrong files.
*
***
* Step 5) Remove the old test database files.
*
*##### MAKE DAMN SURE YOU DO NOT REMOVE ANY OS2P FILES !!!!!!!!! ######
*##### MAKE DAMN SURE YOU DO NOT REMOVE ANY OS2P FILES !!!!!!!!! ######
*##### MAKE DAMN SURE YOU DO NOT REMOVE ANY OS2P FILES !!!!!!!!! ######
*##### MAKE DAMN SURE YOU DO NOT REMOVE ANY OS2P FILES !!!!!!!!! ######
*##### MAKE DAMN SURE YOU DO NOT REMOVE ANY OS2P FILES !!!!!!!!! ######
*
* Remove the existing TEST system file:
	rm /u70/oradata/os2t/system/system01.dbf
* Remove the existing TEST rollback or undo file:
	rm /u70/oradata/os2t/rbs/rbsb01.dbf
	rm /u70/oradata/os2t/rbs/rbsb02.dbf
	rm /u70/oradata/os2t/rbs/undo011.dbf
* Remove the existing TEST archive files (if present)
	rm /u70/oradata/os2t/arch/*.ARC
* Remove the existing TEST log files:
	rm /u72/oradata/os2t/log/log1a
	rm /u72/oradata/os2t/log/log2a
	rm /u73/oradata/os2t/log/log1b
	rm /u73/oradata/os2t/log/log2b
* Remove the existing TEST control files.
	rm /u72/oradata/os2t/ctl/control01.ctl
	rm /u73/oradata/os2t/ctl/control02.ctl
* Remove the existing TEST datafiles:
	rm /u24/oradata/os2t/ts/*.dbf
	rm /u26/oradata/os2t/ts/*.dbf
	rm /u72/oradata/os2t/ts/*.dbf
	rm /u73/oradata/os2t/ts/*.dbf
*
***
*
* Step 6) Copy all files identified in the production's backup 
*	  controlfile to the location where the new databases 
*	  files are to reside.  Check test's backup control file
*	  to see where to copy source database files to.
*	  This step must be done as the root user.
*
* Note that you MUST 'SU' to root and change the owner and group of 
* the copied database files to be:  orat816:dba
*
***
********************* Do not copy redo log files ********
* Copy the log files:
*	cp /u45/oradata/os2p/log/log3a /u72/oradata/os2t/log/log3a
*	cp /u45/oradata/os2p/log/log4a /u72/oradata/os2t/log/log4a
*	cp /u47/oradata/os2p/log/log3b /u73/oradata/os2t/log/log3b
*	cp /u47/oradata/os2p/log/log4b /u73/oradata/os2t/log/log4b
* Change owner and group:
*	chown orat816:dba /u72/oradata/os2t/log/log*
*	chown orat816:dba /u73/oradata/os2t/log/log*
*
*******************************************************************
*
***
* Copy the system and rollback files:
	cp /u01/oradata/os2p/system/system01.dbf /u70/oradata/os2t/system/system01.dbf
	cp /u02/oradata/os2p/rbsbatch/rbsb01.dbf /u70/oradata/os2t/rbs/rbsb01.dbf
	cp /u02/oradata/os2p/rbsbatch/rbsb02.dbf /u70/oradata/os2t/rbs/rbsb02.dbf
	cp /u72/oradata/os2p/rbs/undo011.dbf /u70/oradata/os2t/rbs/undo011.dbf
* Change owner and group:
	chown orat816:dba /u70/oradata/os2t/system/system01.dbf
	chown orat816:dba /u70/oradata/os2t/rbs/rbsb0*.dbf
	chown orat816:dba /u72/oradata/os2t/rbs/undo011.dbf
*
***
* Copy all the other data (.dbf) files to the correct directories.
	cp /u20/oradata/os2p/ts/audit411.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/data011.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/data012.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/data211.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/data212.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/dataq011.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/dataq012.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx011.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx012.dbf	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx013.dbf	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx014.dbf	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndxr11.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx012.dbf 	/u72/oradata/os2t/ts/
	cp /u20/oradata/os2p/ts/ndx013.dbf 	/u72/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/data111.dbf	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/data112.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/data113.dbf	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar11.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar12.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar13.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar211.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar212.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/datar213.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx111.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx112.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx113.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx114.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx211.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx212.dbf 	/u73/oradata/os2t/ts/
	cp /u40/oradata/os2p/ts/ndx213.dbf 	/u73/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/data411.dbf 	/u24/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/data412.dbf 	/u24/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/data413.dbf 	/u24/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/data414.dbf 	/u24/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/data415.dbf 	/u24/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx411.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx412.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx413.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx414.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx411.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx412.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx413.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/ndx414.dbf 	/u26/oradata/os2t/ts/
	cp /u24/oradata/os2p/ts/temp011.dbf 	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6112.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6113.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6114.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6116.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6116.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6117.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_6118.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/NDX_b119.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/data611.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/data612.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/data613.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/data614.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx6111.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx612.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx613.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx614.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx615.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx616.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx617.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx618.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/ndx_6111.dbf	/u26/oradata/os2t/ts/
	cp /u26/oradata/os2p/ts/perfstat.dbf	/u26/oradata/os2t/ts/

Change owner and group:
	chown orat816:dba /u20/oradata/os2t/ts/*.dbf
	chown orat816:dba /u24/oradata/os2t/ts/*.dbf
	chown orat816:dba /u26/oradata/os2t/ts/*.dbf
	chown orat816:dba /u70/oradata/os2t/ts/*.dbf
	chown orat816:dba /u72/oradata/os2t/ts/*.dbf
	chown orat816:dba /u73/oradata/os2t/ts/*.dbf

***
* Step 7) Copy the production's backup control trace file 
*	  created in step 3 to orat816's directory:
*
cp /u01/app/oracle/product/8.1.6/trace/udump/os2p_ora_5516.trc /usr/orat816/rdbms/create_clone_db.sql
*
* Change owner and group:
chown orat816:dba /usr/orat816/rdbms/create_clone_db.sql
*
***
* Step 8) Modify the 'create_clone_db.sql' file:
*
* Remove all trace comments and information.
* Remove everything down to and including the STARTUP NOMOUNT line.
* Replace the REUSE DATABASE command with the SET DATABASE command.
* Replace the NORESETLOGS command with RESETLOGS command
* Change the name of the SID to "OS2T" (with double quotes included)
*
*#################
* Change all path names to the TEST locations as listed in Step 6.
*
* BE VERY SURE TO CHANGE ALL PRODUCTION DIRECTORIES IN THIS FILE 
* TO THE TEST DIRECTORIES!!!!
*#################
*
* Remove the RECOVER DATABASE command towards the end of the file.
* Remove the ALTER SYSTEM ARCHIVE LOG ALL command.
* Remove the ALTER DATABASE OPEN command
* Comment out (#) the ALTER TABLESPACE TEMP_01 .... line.
*
* Here's an example of what the edited file should look like:
*
CREATE CONTROLFILE SET DATABASE "OS2T" RESETLOGS 
NOARCHIVELOG
   MAXLOGFILES 255
   MAXLOGMEMBERS 5
   MAXDATAFILES 1022
   MAXINSTANCES 1
   MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
  '/u72/oradata/os2t/log/log1a.dbf',
  '/u73/oradata/os2t/log/log1b.dbf'
  ) SIZE 10M,
  GROUP 2 (
  '/u72/oradata/os2t/log/log2a.dbf',
  '/u73/oradata/os2t/log/log2b.dbf'
  ) SIZE 10M
DATAFILE
'/u72/oradata/os2t/ts/data011.dbf',
'/u73/oradata/os2t/ts/data111.dbf',
'/u72/oradata/os2t/ts/data211.dbf',
'/u73/oradata/os2t/ts/datar11.dbf',
'/u73/oradata/os2t/ts/datar211.dbf',
'/u72/oradata/os2t/ts/ndx011.dbf',
'/u73/oradata/os2t/ts/ndx111.dbf',
'/u73/oradata/os2t/ts/ndx211.dbf',
'/u72/oradata/os2t/ts/ndxr11.dbf',
'/u73/oradata/os2t/ts/ndxr211.dbf',
'/u26/oradata/os2t/ts/perfstat.dbf',
'/u72/oradata/os2t/ts/audit411.dbf',
'/u73/oradata/os2t/ts/ndx112.dbf',
'/u73/oradata/os2t/ts/ndx212.dbf',
'/u73/oradata/os2t/ts/ndxr212.dbf',
'/u72/oradata/os2t/ts/ndxr12.dbf',
'/u72/oradata/os2t/ts/ndx012.dbf',
'/u73/oradata/os2t/ts/data112.dbf',
'/u72/oradata/os2t/ts/data212.dbf',
'/u73/oradata/os2t/ts/datar212.dbf',
'/u72/oradata/os2t/ts/ndx013.dbf',
'/u73/oradata/os2t/ts/ndx213.dbf',
'/u73/oradata/os2t/ts/ndxr213.dbf',
'/u72/oradata/os2t/ts/dataq011.dbf',
'/u72/oradata/os2t/ts/data012.dbf',
'/u73/oradata/os2t/ts/ndx113.dbf',
'/u72/oradata/os2t/ts/dataq012.dbf',
'/u72/oradata/os2t/ts/ndxr13.dbf',
'/u73/oradata/os2t/ts/datar12.dbf',
'/u73/oradata/os2t/ts/datar213.dbf',
'/u72/oradata/os2t/ts/ndx014.dbf',
'/u73/oradata/os2t/ts/ndx114.dbf',
'/u72/oradata/os2t/ts/ndx411.dbf',
'/u72/oradata/os2t/ts/ndx412.dbf',
'/u72/oradata/os2t/ts/ndx413.dbf',
'/u72/oradata/os2t/ts/ndx414.dbf',
'/u72/oradata/os2t/ts/data411.dbf',
'/u72/oradata/os2t/ts/data412.dbf',
'/u72/oradata/os2t/ts/data413.dbf',
'/u72/oradata/os2t/ts/data414.dbf',
'/u73/oradata/os2t/ts/ndx611.dbf',
'/u73/oradata/os2t/ts/ndx612.dbf',
'/u73/oradata/os2t/ts/ndx613.dbf',
'/u73/oradata/os2t/ts/ndx614.dbf',
'/u73/oradata/os2t/ts/data611.dbf',
'/u73/oradata/os2t/ts/data612.dbf',
'/u73/oradata/os2t/ts/data613.dbf',
'/u73/oradata/os2t/ts/data614.dbf',
'/u73/oradata/os2t/ts/datar13.dbf',
'/u73/oradata/os2t/ts/ndx615.dbf',
'/u73/oradata/os2t/ts/ndx616.dbf',
'/u73/oradata/os2t/ts/ndx617.dbf',
'/u73/oradata/os2t/ts/ndx618.dbf',
CHARACTER SET WE8ISO8859P1
; 
*
***
* Step 9) Logon to test server manager (orat816) and 
*	  run the file modified in step 8.
svrmgrl
connect/as sysdba;
startup nomount;
@create_clone_db
*
*
* The statement should be processed with no errors.
* 
*
***
*
* Step 10) Shutdown the new database and exit server manager.
*
shutdown;
exit
*
***
*
* Step 11) Start up the new test database and reset logs.
*
* From orat816 O/S user start server manager. 
connect/as sysdba;
startup mount;
alter database open resetlogs;
alter database rename global_name to OS2T.CC.CA.US;
exit; 
*
***
*
* Step 12) Make additional changes to the os2t database as needed.
*   REQUIRED CHANGES:
* Turn off audits 
* Turn off archivelog mode
* Change passwords, roles, privileges, profiles.
* Drop and recreate all database links.
*
*   OPTIONAL CHANGES:
* Add additional control files.
* Modify initos2t.ora parameters
* Backup database.
* Reapply developer's 'work in process' schema modifications to test.
*
***
* Here is an example of the unedited production trace file 
* that was generated in step 3.
*
***
>cat os2p_ora_6300.trc

Dump file /u01/app/oracle/product/8.1.6/trace/udump/os2p_ora_6300.trc
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.1.6
System name:    SunOS
Node name:      solarsystem
Release:        5.7
Version:        Generic_106541-11
Machine:        sun4u
Instance name: os2p
Redo thread mounted by this instance: 1
Oracle process number: 85
UNIX process pid: 6300, image: oracle@solarsystem (TNS V1-V3)
*** SESSION ID:(11.63184) 2002-07-08 09:05:32.876
*** 2002-07-08 09:05:32.876
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OS2P" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 64
    MAXLOGMEMBERS 4
    MAXDATAFILES 1021
    MAXINSTANCES 8
    MAXLOGHISTORY 843
LOGFILE
  GROUP 3 (
    '/u45/oradata/os2p/log/log3a',
    '/u47/oradata/os2p/log/log3b'
  ) SIZE 100M,
  GROUP 4 (
    '/u45/oradata/os2p/log/log4a',
    '/u47/oradata/os2p/log/log4b'
  ) SIZE 100M
DATAFILE
  '/u01/oradata/os2p/system/system01.dbf',
  '/u20/oradata/os2p/ts/data011.dbf',
  '/u40/oradata/os2p/ts/data111.dbf',
  '/u20/oradata/os2p/ts/data211.dbf',
  '/u40/oradata/os2p/ts/datar11.dbf',
  '/u40/oradata/os2p/ts/datar211.dbf',
  '/u20/oradata/os2p/ts/ndx011.dbf',
  '/u40/oradata/os2p/ts/ndx111.dbf',
  '/u40/oradata/os2p/ts/ndx211.dbf',
  '/u20/oradata/os2p/ts/ndxr11.dbf',
  '/u40/oradata/os2p/ts/ndxr211.dbf',
  '/u26/oradata/os2p/ts/perfstat.dbf',
  '/u20/oradata/os2p/ts/audit411.dbf',
  '/u02/oradata/os2p/rbsbatch/rbsb01.dbf',
  '/u40/oradata/os2p/ts/ndx112.dbf',
  '/u40/oradata/os2p/ts/ndx212.dbf',
  '/u40/oradata/os2p/ts/ndxr212.dbf',
  '/u20/oradata/os2p/ts/ndxr12.dbf',
  '/u20/oradata/os2p/ts/ndx012.dbf',
  '/u40/oradata/os2p/ts/data112.dbf',
  '/u20/oradata/os2p/ts/data212.dbf',
  '/u40/oradata/os2p/ts/datar212.dbf',
  '/u20/oradata/os2p/ts/ndx013.dbf',
  '/u40/oradata/os2p/ts/ndx213.dbf',
  '/u40/oradata/os2p/ts/ndxr213.dbf',
  '/u20/oradata/os2p/ts/dataq011.dbf',
  '/u20/oradata/os2p/ts/data012.dbf',
  '/u40/oradata/os2p/ts/ndx113.dbf',
  '/u02/oradata/os2p/rbsbatch/rbsb02.dbf',
  '/u20/oradata/os2p/ts/dataq012.dbf',
  '/u20/oradata/os2p/ts/ndxr13.dbf',
  '/u40/oradata/os2p/ts/datar12.dbf',
  '/u40/oradata/os2p/ts/datar213.dbf',
  '/u20/oradata/os2p/ts/ndx014.dbf',
  '/u40/oradata/os2p/ts/ndx114.dbf',
  '/u24/oradata/os2p/ts/ndx411.dbf',
  '/u24/oradata/os2p/ts/ndx412.dbf',
  '/u24/oradata/os2p/ts/ndx413.dbf',
  '/u24/oradata/os2p/ts/ndx414.dbf',
  '/u24/oradata/os2p/ts/data411.dbf',
  '/u24/oradata/os2p/ts/data412.dbf',
  '/u24/oradata/os2p/ts/data413.dbf',
  '/u24/oradata/os2p/ts/data414.dbf',
  '/u26/oradata/os2p/ts/ndx611.dbf',
  '/u26/oradata/os2p/ts/ndx612.dbf',
  '/u26/oradata/os2p/ts/ndx613.dbf',
  '/u26/oradata/os2p/ts/ndx614.dbf',
  '/u26/oradata/os2p/ts/data611.dbf',
  '/u26/oradata/os2p/ts/data612.dbf',
  '/u26/oradata/os2p/ts/data613.dbf',
  '/u26/oradata/os2p/ts/data614.dbf',
  '/u72/oradata/os2p/rbs/undo011.dbf',
  '/u40/oradata/os2p/ts/datar13.dbf',
  '/u26/oradata/os2p/ts/ndx615.dbf',
  '/u26/oradata/os2p/ts/ndx616.dbf',
  '/u26/oradata/os2p/ts/ndx617.dbf',
  '/u26/oradata/os2p/ts/ndx618.dbf'
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP_01 ADD TEMPFILE '/u24/oradata/os2p/ts/temp011.dbf' REUSE;
# End of tempfile additions.
#