I am not aware of any white papers on this subject.

I don't see any pitfalls with 1 version / machine. I run many releases on the same machine, running 1 version / machine would be ideal.

Applying the cold backup is a great way to copy an instance (if you have the flexibility of doing that cold backup). A hot backup + restore would do it as well.

If you have problems creating links between test and production -- its most likely due to GLOBAL NAMES. You don't need to chang the sid to fix this, just "alter database rename global_name to new_name"


Here is how to change a database name and sid of an instance:

PURPOSE
This entry describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.

To find the current DB_NAME and ORACLE_SID:
===========================================

Query the views v$database and v$thread.

V$DATABASE gives DB_NAME
V$THREAD gives ORACLE_SID

If ORACLE_SID = DB_SID and db_name = DBNAME:

To find the current value of ORACLE_SID:

SVRMGR> select instance from v$thread;

INSTANCE
----------------
DB_SID

To find the current value of DB_NAME:

SVRMGR> select name from v$database;

NAME
---------
DBNAME


Modifying a database to run under a new ORACLE_SID:
===================================================

1. Shutdown the instance

2. Backup all control, redo, and data files.

3. Go through the .profile, .cshrc, .login, oratab, tnsnames.ora, (for SQL*Net version 2), and redefine the ORACLE_SID environment variable to a new value.

For example, search through disks and do a grep ORACLE_SID *

4. Change locations to the "dbs" directory

% cd $ORACLE_HOME/dbs

and rename the following files:

o init<sid>.ora (or use pfile to point to the init
file.)
o control file(s). This is optional if you do not rename
any of the controlfiles, and the control_files parameter
is used.

The "control_files" parameter is set in the
"init<SID>.ora" file or in a file it references with the
ifile parameter. Make sure that the control_files
parameter does not point to old
file names, if you have renamed them.
o "crdb<sid>.sql" & "crdb2<sid>.sql", This is optional.
These are only used at database creation.

5. Change locations to the "rdbms/admin" directory

% cd $ORACLE_HOME/rdbms/admin

and rename the file:

o startup<sid>.sql. This is optional. On some platforms, this file may be in the "$ORACLE_HOME/rdbms/install" directory. Make sure that the contents of this file do not reference old init<SID>.ora files that have been renamed. This file simplifies the "startup exclusive" process to start your database.

6. To rename the database files and redo log files if need be.

7. Change the ORACLE_SID environment variable to the new value.

8. Check in the "$ORACLE_HOME/dbs" directory to see if the password file has been enabled. If enabled, the file "orapw<OLD_SID>" will exist and a new password file for the new SID must be created (renaming the old file will not work). If "orapw<OLD_SID>" does not exist, skip to step 9. To create a new password file, issue the following command as oracle owner:

orapwd file=orapw<NEWSID> password=?? entries=<number of users to be granted permission to start the database instance>

9. Start up the database and verify that it works. Once you have done this, shutdown the database and take a final backup of all control, redo, and data files.

10. When the instance is started, the control file is updated with the current ORACLE_SID.


Changing the "db_name" for a Database:
======================================

1. Login to Server Manager

% svrmgrl
SVRMGR> connect internal

2. Type

SVRMGR> alter system switch logfile;

to force a checkpoint.

3. Type

SVRMGR> alter database backup controlfile to trace resetlogs;

This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.

4. Shutdown the database and exit SVRMGR

SVRMGR> shutdown

SVRMGR> exit

The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.

5. Change locations to the directory where your trace files are located. They are usually in the "$ORACLE_HOME/rdbms/log" directory. If "user_dump_dest" is set in the "init<SID>.ora" file, then go to the directory listed in the "user_dump_dest" variable. The trace file will have the form "ora_NNNN.trc with NNNN being a number.

6. Get the "CREATE CONTROLFILE" command from the trace file and put it in a new file called something like "ccf.sql".

7. Edit the "ccf.sql" file

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

8. Save and exit the "ccf.sql" file

9. Rename the old control files for backup purposes and so that they do
not exist when creating the new ones.

10. Edit the "init<SID>.ora" file so that db_name="newdb_name" .

11. Login to Server Manager

% svrmgrl
SVRMGR> connect internal

12. Run the "ccf.sql" script

SVRMGR> @ccf

This will issue a startup nomount, and then recreate the controlfile.

If, at this point, you are prompted for an archived redologfile, the database was not shutdown normally as specified in step 4 and so, during recovery it is asking for the redo in the current logfile. It may be possible to open the database after applying the current logfile. BUT this is not guaranteed. If, after applying the current logfile, the database will not open then it is highly likely that the operation must be restarted having shutdown the database normally.

To apply the necessary redo, you need to check the online logfiles and apply the one with the same sequence number as reported in the message. This usually is the logfile with status=CURRENT.

To find a list of the online logfiles:

SVRMGR> select group#, seq#, status from v$log;
GROUP# SEQUENCE# STATUS
---------- --------- ----------------
1 123 CURRENT <== this redo needs to be applied
2 124 INACTIVE
3 125 INACTIVE
4 126 INACTIVE
5 127 INACTIVE
6 128 INACTIVE
7 129 INACTIVE

7 rows selected.

SVRMGR> select member
from v$logfile
where GROUP# = 1;

Member
------------------------------------
/u02/oradata/V815/redoV81501.log

The last command in ccf.sql should be:

SVRMGR> alter database open resetlogs;

13. Make sure the database is working.

14. Shutdown and backup the database.