Re: Q: How to copy an Oracle database ?

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1995/11/01
Message-ID: <Pine.SUN.3.91.951101154131.15880C-100000_at_seatimes>#1/1

On 26 Oct 1995, Peter N. Rasmussen wrote:
> What is the easiest and best way to make an exact copy (except, of
> course, the database name and filenames) of an Oracle 7 database ?
>
> We have to copy our production database (PROD) to a test database (TEST)
> on a regular basis. I know one way to do this :
> 1) full export of the PROD database.
> 2) create the TEST database the same way the PROD database was created.
> 3) full import into the TEST database.
>
> Is there a safe way to generate a database create script which is 100%
> correct, or is there a still better method ??

This is an extended set of steps beyond just changing the name of an existing database. This does work as I've done it in the exact case you are talking about (I presume you are using the OFA and TEST will go into it's own set of directories).

1. With PROD up do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
2. Shutdown PROD.
3. Copy each and every file of the database from the PROD directories to the TEST directoies.
4. Remove the control files from the TEST directories.
5. Locate and edit the trace file genereated in #1 above to get something like 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; Be sure the paths are correct for the TEST directories. [You could change data file names also -- but why?]

6. Copy over the pfile and do a symbolic link back to

    $ORACLE_HOME/dbs/xxxxx.ora

7. Run the script you editted in #5 above.

8. Shutdown and make a backup.

9. Startup both databases.

+----------------------------------------------------+
| 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 Nov 01 1995 - 00:00:00 CET