How to Rename Database/Change DB_NAME
or ORACLE_SID/Instance Name
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
Modify 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 environment settings and redefine the
ORACLE_SID environment variable to a new value.
For Unix, you can check for instance .profile, .cshrc,
.login, oratab.
On Unix, you can also search through disks and do a grep
ORACLE_SID *
For Windows use the 'set' command in a command window and
make corrections via the System Control Panel applet (Advanced tab >
Environment Variables).
For all Operating systems, check the tnsnames.ora.
4. Change locations to the "dbs" directory (or
"database" directory for Windows)
$ cd $ORACLE_HOME/dbs
and rename the following files:
init<sid>.ora (or use pfile to point to the init
file)
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.
"crdb<sid>.sql" &
"crdb2<sid>.sql", This is optional, these are only used at
database creation.
NOTE: for Oracle9i and higher, 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.
Change 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;
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.
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
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<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
4 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. After the 'alter database open (resetlogs)' command in
the controlfile create script, there are commands to add the necessary temp
files.
Next example is from 11.2:
...
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'+DATA/v1120/tempfile/temp.271.786464435'
SIZE
288358400 REUSE AUTOEXTEND ON NEXT
655360 MAXSIZE 32767M;
-- End of tempfile additions.
Please make sure, the required temp files are indeed
added.
14. 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.
15. Make sure the database is working.
16. 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.
for reference : http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
++++++++++++++++++15390.1+++++++++++++++++
No comments:
Post a Comment