Changing the db_name for a Database

1. Login to SQL*Plus as sysdba
% sqlplus "/ as sysdba"

2. Type
alter system switch logfile;
to force a checkpoint.

3. Type

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 SQL *Plus

shutdown

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 the trace files are located.
They are usually in the "$ORACLE_HOME/rdbms/log" directory. If
"user_dump_dest" is set in the "init.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. Copy the contents of the trace file starting from the line with
STARTUP NOMOUNT down to the end of 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" RESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'.

It is possible to recreate the controlfile using the syntax:
CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ...

But this syntax will allow the existing controlfiles to be overwritten
without giving an error.

FROM:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
TO:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
# RECOVER DATABASE USING BACKUP CONTROLFILE

The last command in ccf.sql should be:
alter database open resetlogs

NOTE : In cases of Oracle 9i, the script must to be modified and
#(as comments) changed to -- as "#" is not recognized as a comment
in SQL*Plus

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.ora" file so that db_name="newdb_name" .

11. Login to SQL *Plus as sysdba
% sqlplus "/ as sysdba"

12. Run the "ccf.sql" script

@ccf

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

13. The global database name may also need to be changed:

alter database rename global_name to .

14. Make sure the database is working.

15. Shutdown and backup the database.

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