Cloning An Oracle Database on Windows NT/2000

[@more@]

Cloning An Oracle Database on Windows NT/2000

By Gerry Patterson

This Article presents two simple Perl scripts intended to work with Oracle on Windows NT/2000. The purpose of the coldarch script is to create a cold archive of a database.

The purpose of the coldclone script is to create a clone of the original database using the cold archive created with coldarch.

---

Cloning An Oracle Database.

First let's consider the steps that would be required to do this operation manually. The following is a list of the steps you might follow, if you were making a clone of an existing database on a Windows 2000 or Windows NT Server:

1. Create A Backup Control File Script.
   
   First you need to obtain a script that will create a copy of the existing control file. This is usually carried out with the SVRMGRL utility using the following commands:

   	CONNECT INTERNAL
   	ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

   This creates a file in the trace file directory. The file usually has the extension '.trc' and will be located either in the directory defined by the parameter 'user_dump_dest', or if this parameter is undefined it will be in $ORACLE_HOME/rdbms/log. Edit this file with your favourite editor and remove the crud. Then rename it as "ctrl.sql," where will be the ORACLE_SID of the copied database.
   
   
2. Modify The Script Created In The Previous Step.
   
   The CREATE CONTROLFILE command in the script ctrl.sql contains SQL, which might look something like this:

   	CREATE CONTROLFILE REUSE DATABASE "OLD_SID" RESETLOGS ARCHIVELOG
   	    MAXLOGFILES 32
   	    MAXLOGMEMBERS 2
   	    MAXDATAFILES 32
   	    MAXINSTANCES 16
   	    MAXLOGHISTORY 1815
   	LOGFILE
   	  GROUP 1 'E:ORACLEORADATAOLD_SIDREDO03.LOG'  SIZE 1M,
   	  GROUP 2 'E:ORACLEORADATAOLD_SIDREDO02.LOG'  SIZE 1M,
   	  GROUP 3 'E:ORACLEORADATAOLD_SIDREDO01.LOG'  SIZE 1M
   	DATAFILE
   	  'E:ORACLEORADATAOLD_SIDSYSTEM01.DBF',
   	  'E:ORACLEORADATAOLD_SIDRBS01.DBF',
   	  'E:ORACLEORADATAOLD_SIDTEMP01.DBF',
   	  'E:ORACLEORADATAOLD_SIDTOOLS01.DBF',
   	  'E:ORACLEORADATAOLD_SIDINDX01.DBF',
   	  'E:ORACLEORADATAOLD_SIDDR01.DBF',
   	  'E:ORACLEORADATAOLD_SIDWORK01.DBF',
   	  'E:ORACLEORADATAOLD_SIDTEMP02.DBF'
   	CHARACTER SET WE8ISO8859P1
   	;

   Where the string is the Oracle SID of the original database. This should be changed to . Normallly this will be contained somewhere in the full filespec (path + filename) of all redo logs, data logs and control files. If it isn't then it should have been. This entire document assumes that you have the SID somewhere in the full filespec of these crucial files and furthermore that there are no embedded spaces or other weird characters in these filespecs. If you failed to observe these universal conventions when you setup your database, you should not try to use any of the procedures outlined in this document.
   
   
3. Copy The Existing Database To The New Location.
   
   This will be a "cold" copy. So obviously you should make sure that the database is shutdown and all services are stopped before attempting to "cold" copy the database.
   
   If the copy is on the same host, you can use the DOS copy command (once the instance is shutdown). If you are lack the manual dexterity required for a keyboard you can copy the files with a mouse. If the target is a remote host then you will have to copy to a mass storage device or copy across the network.

   On the target host you need to copy all parameter files and all files mentioned above to their new location. Make sure you preserve ownership and permissions. The copied init.ora should be renamed to init.ora, and any parameter files pointed to by an ifile parameter (e.g. parameter files such as config.ora) should be renamed to contain (e.g. config.ora).
   
   The datafiles and redo log files from the pervious step also need to be renamed to contain the in the full filespec.
   
   

4. Set Up Parameter Files For The New Database
   
   There may be several parameters that need to be edited in init.ora. In particular you will need to edit the control_files parameter so that it points to the name and location that you want to use for the new control files. You will also have to change the DB_NAME parameter in init.ora. Change it to the newname for your database. Usually this corresponds the . Any 'ifile' parameters will need to be edited to point to the new name of the include file in the new location.
   
   
5. Create The Control File For The New Database.
   
   Now edit the file ctrl.sql and strip out everything up to and including the STARTUP NOMOUNT command. Remove the ALTER DATABASE OPEN command and everything after it. This leaves a command which just creates the controlfile.
   
   Now change all the appropriate instances of to . Unless you have a very good reason for doing so, you should make the database name the same as . Save this script in an area where you will find it again.
   
   Make sure that your ORACLE_SID is set to . Then use the SVRMGRL utility to run the following commands:

   	STARTUP NOMOUNT
   	@ctrl

6. Create The Services For NEW_SID
   
   Create the services "OracleService" and the "OracleStart" for "NEW_SID" with the following command:

   oradim -new -sid  -intpwd  -startmode auto -pfile 

7. Run 'CREATE CONTROLFILE' For
   
   Make sure that your current directory is the one that contains ctrl.sql
   
   Set your ORACLE_SID to
   
   Startup SVRMGRL and enter the following:

   	CONNECT INTERNAL
   	STARTUP NOMOUNT PFILE=init.ora
   	@ctrl
   	ALTER DATABASE OPEN RESETLOGS;

---

Automating This Process.

Creating a clone of an Oracle Database is the type of thing that you might wish to carry out regularly. you might do this on a regular basis because:

• You wish to create a test instance of your production system.
• You wish to create another working copy of your production system for reporting purposes or for some form of off-line processing.

Obviously if you had to do this regularly you would write a script. If you are proficient at writing CMD scripts you could write a .cmd or .bat file to carry out these steps.

You would have to clobber the instance before you created the clone. But that could be easily accomplished in a CMD script. However some of the things that would be cumbersome in a CMD script would be:

• Changing the CREATE CONTROLFILE script when your database is altered (e.g. when you add a datafile to a tablespace).
• Creating a third copy (e.g. NEW_SID1).
• Creating a copy from an entirely different database.

Obviously these contingincies can be handled ... but usually it means re-writing or re-creating the CMD script.

Perl is so versatile that it does not have these problems.

---

coldarch.pl

The coldarch perl script creates a cold archive of an Oracle database on Windows NT/2000. The archive is a separate directory. It could be on the same machine as the host. However, in the interest of data integrity, it would make more sense to place it on a remote host (via the network).

The script relies on site specific variables that are set in the common.pl file.

The coldarch command is intended for incorporation into a script file. Ideally it should be incorporated into a routine backup schedule. However, if it is being invoked from a command line, the syntax would be as follows:

	coldarch intrnl_passwd db_name

Where:

intrnl_passwd is the Internal password of the database.
db_name is the database_name (should also be the SID).

The logic of the script is as follows:

1. Validate the command line parameters and the common.pl variables.
2. Create a lock file, to prevent a second copy of the program from running.
3. Initialise strings for a logfile and a Header File (for the archive directory)
4. Use SQL queries to gather information about control_files, datafiles, redo logs and locations of important files.
5. Create a script that will can CREATE CONTROL file
6. Shutdown the database.
7. Add the datafiles to the zip archive.
8. Copy the parameter files and included files (ifiles) to the archive directory.
9. Add the control file and redo logs to the zip archive.
10. Depending on the value of the variable $OPEN_TYPE, open the database. Some sites may choose not to open the database, because the coldarch procedure is integrated into the backup schedule. The variable $OPEN_TYPE is hard-coded in the coldarch script.

---

coldclone.pl

The coldclone perl script restores a cold archive of an Oracle database to a Windows NT/2000 host. The Target SID must differ from the source SID.

The script relies on the same site specific variables that the coldarch script relies on. These are set in the common.pl file.

The coldclone command is intended for incorporation into a script file. If it is being invoked from the command line, the syntax would be as follows:

	coldclone src_dir intrnl_passwd db_name

Where:

src_dir is the directory where the cold arhive resides.
intrnl_passwd is the Internal password of the database.
db_name is the database_name (should also be the SID).

The logic of the script is as follows:

1. Validate the command line parameters and the common.pl variables.
2. Verify that the database is shutdown.
3. Create a lock file, to prevent a second copy of the program from running.
4. Initialise strings for a logfile.
5. Parse the archive header, to determine the location of various files.
6. Verify that the database is a clone -- This script has been expressly written to create a clone. You would need a diffent script to do a restore.
7. Completely clobber the existing database (use the oradim utility to blow it away). You have passed the point of no return.
8. Unpack the datafiles from the zip archive.
9. Copy the parameter files and included files (ifiles) to the archive directory.
10. Construct an SQL query from the trace file (created by coldarch), which contains the CREATE CONTROLFILE.
11. Create the database anew with oradim. Start it up (NOMOUNT) and run the CREATE CONTROLFILE script. Open the database. (ALTER DATABASE OPEN).

NOTE: This utility can run if the target SID does not exist. However if you are not a DBA it is inadvisable to do so. You should create the services and listener entries by hand. If you are doing this MAKE SURE YOU BACKUP THE SOURCE DATABASE! (You have been warned).