Re: Making a Working Copy of a Database

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/03/06
Message-ID: <Pine.SUN.3.91.960306093255.4836A-100000_at_seatimes>

On Wed, 21 Feb 1996, David Ewing wrote:

> I would like to find the easiest way  to create a brand new
> database that is an exact copy of another, but with a different
> oracle sid.  We have the need to occassionaly copy our PREP

Standard reply for the following questions developed by Steve Butler of The Seattle Times Company (sbut-is_at_seatimes.com):

1. How can I change the location of my database?
2. How can I make a copy of my database and access it with a different SID?

ANSWER:
You will need some time to have the original database shut down. So, plan ahead.

These instructions make two presumptions: A. You are on a UNIX box of some type -- if not, improvise; and, B. You set up and are using the OFA. If so, then the /uxx used below

   are to be changed to your own mount point names, and the ORACLE portion    becomes the name of your designated database file area. Finally, tst    will be the new database name you are creating.

   This portion of the OFA results in path names of this form:

    /mount_point_name/ORACLE_FILE_AREA_NAME/database_name/data_file.ext

   Each directory area could be a complex set of directory names for those    who need to set up a more complex OFA.

1. While the original database is up and running connect as internal and issue the following command:

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 2. Now shutdown the original database and copy or move the files

   to their new location. I prefer to leave the originals in place    until the copy is up and working (especially if the original must    be restarted and the copy needs to work under a different SID).

   With the database shut down, copy ALL the database files (redo,    system, tabblespace datafiles, etc) EXCEPT THE CONTROL FILES to the new    location.

   Be sure the control files are NOT in the new location.

3. If the original is to remain as a database and the copies are to be

   a different SID, then you are free to startup the original database    at this point.

4. Locate the trace file in your user dump area. Edit it to change

   the path (location) on all the datafiles. Also change the database    name and use the SET option on the CREATE CONTROLFILE statement.

   Remember to:

1. Change the database name (unless you are moving the database).
2. Add the word SET (only when changing the database name).
3. Fixup the ARCHIVE or noARCHIVE as needed
4. Change the location paths for EVERY file!!!!
5. Add the RESETLOGS to the ALTER DATABASE OPEN statement.
6. Be sure the new initSID.ora file has the new control file names and paths (and that they don't exist yet).

   Your script should be similar to the following:

     STARTUP NOMOUNT
     CREATE CONTROLFILE set DATABASE TST RESETLOGS noARCHIVELOG
         MAXLOGFILES 6
         MAXLOGMEMBERS 4
         MAXDATAFILES 30
         MAXINSTANCES 1
         MAXLOGHISTORY 100
     LOGFILE
       GROUP 1 (
         '/u01/ORACLE/tst/redo01.log',
         '/u02/ORACLE/tst/redo01.log'
       ) SIZE 10M,
       GROUP 2 (
         '/u01/ORACLE/tst/redo02.log',
         '/u02/ORACLE/tst/redo02.log'
       ) SIZE 10M,
       GROUP 3 (
         '/u01/ORACLE/tst/redo03.log',
         '/u02/ORACLE/tst/redo03.log'
       ) SIZE 10M,
       GROUP 4 (
         '/u01/ORACLE/tst/redo04.log',
         '/u02/ORACLE/tst/redo04.log'
       ) SIZE 10M
     DATAFILE


       '/u01/ORACLE/tst/system01.dbf' SIZE 25M,

       '/u02/ORACLE/tst/rbs01.dbf' SIZE 50M,

       '/u01/ORACLE/tst/temp01.dbf' SIZE 100M,

       '/u04/ORACLE/tst/tools01.dbf' SIZE 15M,

       '/u01/ORACLE/tst/users01.dbf' SIZE 25M,

       '/u04/ORACLE/tst/addrs01.dbf' SIZE 100M,

       '/u02/ORACLE/tst/addrs_idx01.dbf' SIZE 100M,

       '/u01/ORACLE/tst/accts01.dbf' SIZE 450M,


       '/u03/ORACLE/tst/accts_idx01.dbf' SIZE 250M


     ;
     ALTER DATABASE OPEN RESETLOGS;

5. Change your $ORACLE_SID to the new value (be sure /etc/oratab is updated).

   Remember to use . oraenv so $ORACLE_HOME is appropriately set.

6. Copy the above script to the work area for your new database.

   The path is of this form:

       /$ORACLE_BASE/admin/data_base_name/create/control.sql

7. Be sure that the initSID.ora file is named correctly and accessable.

    I tend to forget that a soft link is required from $ORACLE_HOME/dbs     to $ORACLE_BASE/admin/database_name/pfile/init$ORACLE_SID.ora

8. Use SQLDBA, connect as internal, run this script.

9. Shutdown and get yourself a backup.

I've done this several times and usually forget a step. So, if this doesn't work exactly as outlined, improvise a little.

This procedure was given to me by the response center when I needed to copy our PRD to TST for a full volume test of a new release.

--Steve

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Wed Mar 06 1996 - 00:00:00 CET