DBA Tips Archive for Oracle


Copy and Rename an Oracle Database - (without export/import)

by Jeff Hunter, Sr. Database Administrator

You may find it necessary to duplicate (clone) an Oracle database. One method is to use import/export. This method can work fine, but what if your database is too big? Another method is to make a copy of the current database and rename it. This month I will present an article that explains the steps necessary to copy and rename a database. This article will assume that the original database is called PROD and you want to create a TEST duplicate database.

1.) Copy production database files and init.ora

The first step is to locate and copy all database files to their new location. You can use the view V$DATAFILE in the PROD database to locate these files. Before running the query from V$DATAFILE, ensure that you are connected to the PROD database by selecting from V$DATABASE:
  SQL> select name from v$database;


  SQL> select name from v$datafile;

After recording these files, shutdown the PROD database and perform an operating system copy of all database files to another location and/or machine. In my example, I will copy all datafiles to a new location as shown in the following table:

Old LocationNew Location
/u08/app/oradata/PROD/system01.dbf /u08/app/oradata/TEST/system01.dbf
/u06/app/oradata/PROD/rbs01.dbf /u06/app/oradata/TEST/rbs01.dbf
/u07/app/oradata/PROD/temp01.dbf /u07/app/oradata/TEST/temp01.dbf
/u10/app/oradata/PROD/userd01.dbf /u10/app/oradata/TEST/userd01.dbf
/u09/app/oradata/PROD/userx01.dbf /u09/app/oradata/TEST/userx01.dbf

After copying all files to their new location, startup the PROD database.

From the production database, get a copy of the initPROD.ora file and copy it to initTEST.ora. In the initTEST.ora file, change the value of "db_name" from PROD to TEST. Keep in mind that you may also need to change:

If the TEST database is going to be on a different machine, copy the initTEST.ora file to that machine in the proper directory.

2.) Create the script that will re-create the controlfile

Using SVRMGR on the PROD database, create a script that will be able to re-create the controlfile for the database.
  PROD on testdb: svrmgrl
  SVRMGR> connect internal
  SVRMGR> alter database backup controlfile to trace;
  Statement processed.
The above statement will put a text copy of the controlfile in the USER_DUMP_DEST directory. You will need to search for the newest trace file in this directory. In UNIX you can use the "ls -lt" command. Once you find the correct trace file, rename it to cr_control.sql and edit it as follows: Your script should now read:

Edited file cr_control.sql
  GROUP 1 (
  ) SIZE 200K,
  GROUP 2 (
  ) SIZE 200K,
  GROUP 3 (
  ) SIZE 200K
If the TEST database is on a different machine move this file to that machine.

3.) Create the new controlfile for TEST

Make sure that your Oracle environment variable "ORACLE_SID" is set to TEST. (i.e. export ORACLE_SID=TEST).

Now use SVRMGR and the CREATE CONTROLFILE script (cr_control.sql) to create your controlfile for TEST:

  TEST on testdb: svrmgrl
  SVRMGR> connect internal
  Connected to an idle instance.
  SVRMGR> @cr_control
  ORACLE instance started.
  Total System Global Area      32798752 bytes
  Fixed Size                       39816 bytes
  Variable Size                 22600856 bytes
  Database Buffers               9994240 bytes
  Redo Buffers                    163840 bytes
  Statement processed.
NOTE: Stay logged into SVRMGR and proceed to the next step.

4.) Open the TEST database

Before opening the TEST database, you will need to perform incomplete recovery. After recovery you can open the database using the RESETLOGS option as show below:
  SVRMGR> alter database recover database until cancel using backup controlfile;
  SVRMGR> alter database recover cancel;
  Statement processed.
  SVRMGR> alter database open resetlogs;
  Statement processed.
You can verify that the database was renamed to TEST by querying from V$DATABASE:
    SVRMGR> select name from v$database;
    1 row selected.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 23-Jan-2006 08:45:17 EST
Page Count: 140602