Since this is a frequent issue this page is a copy and paste of Oracle Metalink note 15390.1
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.
SCOPE & APPLICATION
For DBAs requiring to either find or change the db_name or ORACLE_SID.
RELATED DOCUMENTS
Note 1018634.102 AFTER RENAMING THE DATABASE SELECT FROM DUAL RETURNS
OLD DATABASE NAME
Note 9560.1 ALTER TABLESPACE/DATABASE TO
RENAME FILES
Note 61590.1 Renaming an existing Windows NT
database(For Windows)
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:
(For Oracle9i/Oracle10g, must use SQL*Plus)
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 immediate
The database must be shutdown with SHUTDOWN NORMAL or
SHUTDOWN IMMEDIATE.
It must not be shutdown abnormally using SHUTDOWN ABORT.
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) redefine parameters instance_name and service_names
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_file parameter
does not point to old
file names, if they have been
renamed.
o "crdb<sid>.sql" & "crdb2<sid>.sql", This
is optional, these are
only used at database creation.
NOTE: for Oracle9i/Oracle10g if using an SPFILE: You must
rename it (i.e. spfile<sid>.ora)
along with changing parameter values in the SPFILE.
Please review the following
for more information on SPFILE.
Note 162491.1 Spfile and Init.ora Parameter File Startup of an Oracle9i
Instance
Note 137483.1 How to Modify the Content of an SPFILE Parameter File
5. To rename the database files and redo log files, follow the
instructions in
Note 9560.1.
6. Change the ORACLE_SID environment variable to the new value.
7. 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 8. 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>
8. Start up the database and verify that it works. Once this is
done,
shutdown the database and take a final backup of all control,
redo,
and data files.
The database must be shutdown with SHUTDOWN NORMAL or
SHUTDOWN IMMEDIATE.
It must not be shutdown abnormally using SHUTDOWN ABORT.
9. 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 (Oracle7 through Oracle8i)/ SQL*Plus for
Oracle9i/Oracle10g
% svrmgrl
SVRMGR> connect internal
NOTE: For Oracle9i/Oracle10g, must
use SQL*Plus (svrmgrl is obsolete after Oracle8i)
% sqlplus "/ as sysdba"
2. Type
SVRMGR> alter system switch logfile;
show parameter user_dump_dest;
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
immediate
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 the 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.
NOTE: for Oracle9i/Oracle10g if using an SPFILE: Please
review the following for
changing parameter values in the SPFILE.
Note 137483.1 How to Modify the Content of an SPFILE Parameter File
Change init parameter db_name to new name
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".
Backup controlfile
cp /ora01_nis/NIS_PRO/cntl/control01.ctl /tmp/control01.ctl
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<SID>.ora" file so that db_name="newdb_name" .
NOTE: for Oracle9i/Oracle10g if using an SPFILE: Please
review the following for
changing parameter values in the SPFILE.
Note 137483.1 How to Modify the Content of an SPFILE Parameter File
11. Login to Server Manager
% svrmgrl
SVRMGR> connect internal
NOTE: For Oracle9i/Oracle10g, must
use SQL*Plus (svrmgrl is obsolete after Oracle8i)
% sqlplus "/ as sysdba"
12. Run the "ccf.sql" script
SVRMGR> @ccf
This will issue a startup nomount, and then recreate the
controlfile.
If, at this point, an error stating that a file needs media
recovery
is reported, then the database was not shutdown normally as
specified
in step 4. Try recovering the database using the redo in the
current
logfile, by issuing:
SVRMGRL> recover database using backup controlfile
until cancel;
This will prompt for an archived redologfile. It may be
possible to
open the database after applying the current logfile. BUT
this is not
guaranteed.
To apply the necessary redo, 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. If not apply, the
logfiles
in turn until the logfile with status=CURRENT has been
applied. 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 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
After applying the current online log file the following
prompt should
be displayed:
Log Applied
Media Recovery Complete
At this point the database can be opened with:
SVRMGR> alter database open resetlogs;
13. The global database name may also need to be changed:
alter database rename global_name to <newdb_name>.<domain>
See
Note 1018634.102 for further detail.
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.
Add tempfiles
login to rman catalog and issue > reset database;