RE: Duplicating a Database and Changig SID

From: Ron Yount <ronwy_at_swbell.net>
Date: Fri, 30 Nov 2001 09:16:48 -0800
Message-ID: <F001.003D20AC.20011130090545@fatcity.com>

This
is not real pretty as text, if you want a MS Word copy, send me your e-mail. <A href="mailto:ronwy_at_swbell.net">ronwy_at_swbell.net <FONT face=Arial color=#0000ff
size=2> 
 

The purpose of this document is to define the process and commands necessary to duplicate (clone) an existing database and alter its Database Identifier (DBID). There are two separate approaches, one with standard file systems and the other utilizing a BCV split.

Standard File System Procedures
1. Connect to the database as "internal" 2. Issue "alter database backup controlfile to trace resetlogs". (This file will be located in the user_dump_dest defined in the init<sid>.ora file and will be called
"ora_nnnn.trc".)

3. sql> select name, dbid from v$database;   (Use this for later reference).
4. Shutdown the database using "shutdown normal"
5. Copy the database (dbf) files to the file system(s) for the new database. (for a 

list of all the files, look at the trace file generated in step 2) 6. Copy the redo log files to the file system(s) for the new database. 7. Copy and rename the init<sid> file to init<newsid> to the new database file structure.
8. Do not copy the control files to the new location. 9. Restart the original database instance. 10. Edit the trace file created in Step 2. a. Delete all lines from the top of the file down to (and including) the line that says "startup nomount".
b. Alter the line that starts with "Create controlfile…" so that:  i. (REUSE) is changed to (SET)
 ii. (NORESETLOGS) is changed to (RESETLOGS)  iii. DATABASE = "<original_sid>" is changed to "<new_sid>". c. After the semicolon that ends the "create controlfile..." statement, delete everything except the line "alter database open resetlogs;" d. Alter all of the path statements for the data files so that they reference the
"new" path.

e. Save the file to a meaningful name such as
"$ORACLE_BASE/admin/<newsid>/create/cr_cfile.sql"
11. Edit the new init file.

a. Update SERVICE_NAMES, DB_NAME, and INSTANCE_NAME to the new SID.
b. Change the CONTROL_FILES to point to the correct path.
c. Change the USER_DUMP_DEST, LOG_ARCHIVE_DEST, CORE_DUMP_DEST, and 

BACKGROUND_DUMP_DEST, UTL_FILE_DIR to the new file system paths. 12. Create a password file for the new database with the "orapwd" command. 13. Create a link from $ORACLE_HOME/dbs/init<newsid>.ora to $ORACLE_BASE/admin/<newsid>/pfile/init<newsid>.ora using "ln -s <src> <link> syntax.

14. Change to the directory where the control file script from step (10-d) is located.
15. export ORACLE_SID=<new sid>
16. Use sqlplus to connect internal
a. sql> startup nomount

b. sql> @cr_cfile.sql
c. sql> select name, dbid from v$database. (There should be a new name, but same dbid from step 3)

d. sql> shutdown normal
e. sql> startup mount;
f. sql> exec dbms_backup_restore.zeroDbid(0);
g. sql> shutdown normal;

17. Rename the control files from the new instance, so they will appear to be missing.

a. sql> startup nomount;
b. sql> @cr_cfile.sql
c. sql> select name, dbid from v$database.  (There should be the new name, and new 

dbid)

18. Add an entry in tnsnames.ora for the new database.
19. Register the new database with RMAN and request a full backup (level 0).
20. Add an appropriate entry in the "oratab" file.

Received on Fri Nov 30 2001 - 11:16:48 CST