Re: How to create a copy of an Instance?

From: Steve Butler <sbut-is_at_seatimes.com>
Date: Wed, 15 Feb 1995 08:12:44 -0800
Message-ID: <Pine.SUN.3.91.950215075755.29932A-100000_at_seatimes>

On 14 Feb 1995, Michael Nolan wrote:

> What's the easiest way to create a duplicate copy of an instance
> on the same machine but with files in different directories? (with a
> different SID, obviously)

I had a similar requirment. The users wanted the PRODUCTION database copied to a TEST environment. A call to support revealed the following:

By using the ALTER DATABSE BACKUP CONTROLFILE TO TRACE; command in the source (production) database you will get an ORA_nnnn.TRC file (in UDUMP -- if you set up using the ORACLE_BASE structure). That file needs to be editted to become the SQL script to create the new control file.

Then SHUTDOWN the SOURCE and issue CP commands to move the data files to the new locations.

You will need to edit that new SQL script to reference the new data file locations. You can also change the name of the database by changing it in the CREATE CONTROLFILE line of this script.

Once you have the entire database copied, rm the control files. Then run the script from SQLDBA. The script does the following (short form);

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE newname NORESETLOGS NOARCHIVELOG

   etc. for several lines (about 30 in my case) RECOVER DATABASE
ALTER DATABASE OPEN; Now, I changed the script from the TRC in the following manner:

1. Put the new database name in where NEWNAME is on the CREATE CONTROLFILE line.
2. Changed NOARCHIVELOG to ARCHIVELOG.
3. Added an ALTER SYSTEM ARCHIVE LOG ALL; just after the RECOVER DATABASE
4. Changed all the file paths in about 30 lines to reference the new locations. This was somewhat easy as I followed the ORACLE_BASE structure and could do a wildcard replacement in the editor.

This beats doing an export and import every time!

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-464-2905         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |

| Seattle, WA 98111 Packet: Not currently active |

+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Wed Feb 15 1995 - 17:12:44 CET