Copying Databases

Submitted by dave on Thu, 01/21/2010 - 21:06

1) Starting with a Cold Backup, copy files to new destination.
Including datafiles, control files, temp files, online redo logs.

2) Set up admin directory
You can start with a copy of the original DB's admin directory if that makes things easier for you.

3) Set up $ORACLE_HOME/dbs
3a) init file
3b) password file

4) Startup Mount and Make Controlfile Changes
If at any time you need to see the contents of the controlfile and your database is in MOUNT mode, this command will write a text version of the controlfile to /udump:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

4a) Rename datafiles
You can get the old files and paths from the Controlfile Backup or if you have access to the source database you can use a statement liek this to generate the statements for you. 

select 'ALTER DATABASE RENAME FILE '''||FILE_NAME||''' to '''||
translate(FILE_NAME, 'ivr92prd','ivr92old')||''';' 
FROM dba_data_files;

4b) Rename Redo Log Files
You can get the old files and paths from the Controlfile Backup or if you have access to the source database you can use a statement liek this to generate the statements for you. 

select 'ALTER DATABASE RENAME FILE '''||member||''' to '''||
translate(member, 'ivr92prd','ivr92old')||''';' 
from v$logfile;

4c) Start up database, create new temp tablespace from scratch

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 
  '/u02/oradata/ivr92old/temp01.dbf' SIZE 2096896K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

Now you have all files ready to go and have a working database.

5) Rename the database using the 'nid' Oracle command line utility 

$ nid target=sys/password dbname=newname