PGTS PGTS Pty. Ltd.   ACN: 007 008 568               Mobile Version Coming Soon

point Site Navigation







Valid HTML 4.01!






   Download Kubuntu Today

   Ubuntu

   The Power Of KDE + Ubuntu





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<NEW_SID>.sql," where <NEW_SID> 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<NEW_SID>.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:\ORACLE\ORADATA\OLD_SID\REDO03.LOG'  SIZE 1M,
    	  GROUP 2 'E:\ORACLE\ORADATA\OLD_SID\REDO02.LOG'  SIZE 1M,
    	  GROUP 3 'E:\ORACLE\ORADATA\OLD_SID\REDO01.LOG'  SIZE 1M
    	DATAFILE
    	  'E:\ORACLE\ORADATA\OLD_SID\SYSTEM01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\RBS01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\TEMP01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\TOOLS01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\INDX01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\DR01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\WORK01.DBF',
    	  'E:\ORACLE\ORADATA\OLD_SID\TEMP02.DBF'
    	CHARACTER SET WE8ISO8859P1
    	;
    
    Where the string <OLD_SID> is the Oracle SID of the original database. This should be changed to <NEW_SID>. 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<OLD_SID>.ora should be renamed to init<NEW_SID>.ora, and any parameter files pointed to by an ifile parameter (e.g. parameter files such as config<OLD_SID>.ora) should be renamed to contain <NEW_SID> (e.g. config<NEW_SID>.ora).

    The datafiles and redo log files from the pervious step also need to be renamed to contain the <NEW_SID> 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<NEW_SID>.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<NEW_SID>.ora. Change it to the newname for your database. Usually this corresponds the <NEW_SID>. 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<NEW_SID>.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 <OLD_SID> to <NEW_SID>. Unless you have a very good reason for doing so, you should make the database name the same as <NEW_SID>. Save this script in an area where you will find it again.

    Make sure that your ORACLE_SID is set to <NEW_SID>. Then use the SVRMGRL utility to run the following commands:
    	STARTUP NOMOUNT
    	@ctrl<NEW_SID>
    
  6. Create The Services For NEW_SID

    Create the services "OracleService<Sid>" and the "OracleStart<Sid>" for "NEW_SID" with the following command:
    oradim -new -sid <NEW_SID> -intpwd <password> -startmode auto -pfile <path_name>
    
  7. Run 'CREATE CONTROLFILE' For <NEW_SID>

    Make sure that your current directory is the one that contains ctrl<NEW_SID>.sql

    Set your ORACLE_SID to <NEW_SID>

    Startup SVRMGRL and enter the following:
    	CONNECT INTERNAL
    	STARTUP NOMOUNT PFILE=<full path>\init<NEW_SID>.ora
    	@ctrl<NEW_SID>
    	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:

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 <NEW_SID> 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:

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