Re: Making a Working Copy of a Database

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/02/23
Message-ID: <Pine.SUN.3.91.960223095736.13403A-100000_at_seatimes>#1/1

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

Whoever is maintaining the FAQ should add this one to the list!!! However, it's been over a month since the last one so I'll try to remember all the gruesome steps <<grin>>.

> I was wondering if there was a way to just copy the physical
> files that make up the database to another file system, and
> then bring up the new database pointed at these new files.
> This would be so much quicker. Keep in mind, I do not want
> to disturb the original PREP database, but create TEST as
> an exact copy. Thanks in advance.

Yup, you can do just that, but first...

1. While PREP is up and running do

        ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 2. Now shutdown PREP. Yes, it must be shutdown for step 3.

3. Do those cp commands to copy the files to their new home.

    (You are using OFA?)

4. You are now free to start PREP up again.

5. Remove the CONTROL files from the TEST area. We will create

    them shortly.

6. Locate the trace file (probably in $ORACLE_BASE/admin/prep/udump/) and edit away the garbage until you get something that looks like this:

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;    Remember to:

1. Change the database name.
2. Add the word SET.
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 initTEST.ora file has the new control file names and paths (and that they don't exist yet).
7. Save this as a .sql file (probably in $ORACLE_BASE/admin/test/create).
8. Use . oraenv to change to the TEST sid.
9. Be sure the $ORACLE_HOME/dbs has a soft link to $ORACLE_BASE/admin/test/pfile/initTEST.ora.
10. Fire up SQLDBA and execute the script from 6.g.

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 Fri Feb 23 1996 - 00:00:00 CET