See
Note <FONT color=#0000ff
size=2> Note:15390.1 Subject: How to Determine and Change
DB_NAME or ORACLE_SID on MetaLink
<FONT color=#0000ff
size=2>
Here it is
below
<FONT color=#0000ff
size=2>
<FONT color=#0000ff
size=2>Rick
<FONT color=#0000ff
size=2>
<FONT color=#0000ff
size=2>
<FONT color=#0000ff
size=2>
P<SPAN
class=961411117-18062001>urpose 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 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_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. 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
% 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 "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. 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
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, 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.
<FONT face=Tahoma
size=2>-----Original Message-----From: Andrey Bronfin
[mailto:Andreyb_at_maxbill.com]Sent: Monday, June 18, 2001 12:56
PMTo: Multiple recipients of list ORACLE-LSubject: how
to rename a database SID
HI ! How can i
rename a database on NT / UNIX ? Thanks a
lot in advance !
Received on Mon Jun 18 2001 - 12:08:15 CDT