Title: how to rename a database SID
See
Note Note:15390.1Subject:How to Determine and Change
DB_NAME or ORACLE_SID on MetaLink
Here it is
below
Rick
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
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
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 initsid.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 "initSID.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
"crdbsid.sql" "crdb2sid.sql", This is
optional. These are
only used at database
creation. 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
"orapwOLD_SID" will exist and a new
password file for the new SID must be created
(renaming the old file will not work). If "orapwOLD_SID" does not
exist, skip to step 8. To create a new
password file, issue the following command as
oracle owner:
orapwd file=orapwNEWSID 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
% 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 the trace files are
located. They are usually in the
"$ORACLE_HOME/rdbms/log" directory. If
"user_dump_dest" is set in the "initSID.ora" file, then go to the
directory listed in the "user_dump_dest"
variable. The trace file will have the form
"ora_.trc with 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 synt
