Re: Rename ORACLE_SID?

From: Ms. D.H. Harvey <qq45_at_liverpool.ac.uk>
Date: 1996/07/23
Message-ID: <Dv078B.CE9_at_liverpool.ac.uk>

To Kim (sidepage_at_nntp.best.com) wrote:
: Hello All,
: Does anyone know of an easy method to rename an Oracle 's
: instance? I would think using exp/imp might do the trick, after
: creating a new database, but that takes too long...
 

: -mike

Hope this helps

RTSS Bulletin Board Document

   Doc #: 106373.655                                 Category: UNIX
    Date: 22-SEP-94                                    Author: MAROBERT

Abstract: CHANGING DBNAME OR ORACLE_SID

Document ID:        106373.655
Title:              Changing dbname or ORACLE_SID
Creation Date:      07 September 1994

Last Revision Date: 07 September 1994
Revision Number: 0
Product: RDBMS
Product Version: 7
Platform: UNIX
Information Type: ADVISORY

Impact:             MEDIUM
Abstract:           This bulletin describes how to change the dbname for a
                    database, or the ORACLE_SID for an instance, without
                    having to recreate the database.
Keywords:           SID;DBNAME;CHANGING;ORACLE_SID;INSTANCE;CONTROLFILE
-----------------------------------------------------------------------

Modifying a database to run under a new ORACLE_SID

---

1. Shutdown instance
2. Backup all control, redo and data files.
3. Go thru the .profile, .cshrc, .login, oratab, tnsnames.ora(for net v2), and redefine the environment variable ORACLE_SID to a new value. ie search thru disks and do a grep ORACLE_SID *
4. 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 don't rename any of the controlfiles, and the control_files parameter is used. control_files would be set in the initSID.ora file or in a file it references with the ifile parameter. Make sure control_files doesn't point to any old file names, if you renamed them. o crdb<sid>.sql & crdb2<sid>.sql This is optional. These are only used at database creation.
5. cd $ORACLE_HOME/rdbms/admin and rename the file: o startup<sid>.sql This is optional. (On some platforms, this file may be in $ORACLE_HOME/rdbms/install.) Make sure the contents of this file do not reference old initSID.ora files that have been renamed. This file simplifies the process to "startup exclusive" your database.
6. To rename the database files and redo log files, you would follow the instructions in the bulletin: 98863.723.
7. Change the ORACLE_SID environment variable to the new value.
8. start up database and verify it works. Once you have done this, shutdown the database and take a final backup of all control, redo and data files.
9. When the instance is started, the control file gets updated with the current ORACLE_SID.

   ---

Changing the dbname for a database

---

1. sqldba
2. connect internal
3. alter database backup controlfile to trace; This will write in a trace file, the CREATE CONTROLFILE command that would recreate the controlfile as it currently exists.
4. Exit and go to the directory where your trace files are located. They are usually in the $ORACLE_HOME/rdbms/log directory. If user_dump_dest is set in the initSID.ora, 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.
5. Get the CREATE CONTROLFILE command from the trace file and put it in a new file called something like ccf.sql.
6. Edit the ccf.sql file and modify the CREATE CONTROLFILE command. Just change the word "REUSE" to "SET",and "NORESETLOGS" to "RESETLOGS", and modify the dbname. Old line: CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ... New line: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ... Then save the ccf.sql file.
7. Rename the old control files for backup purposes and so they are not in the way of creating the new ones.
8. Edit initSID.ora so that db_name="newdbname".
9. sqldba
10. connect internal
11. startup nomount
12. _at_ccf
13. alter database open;
14. Make sure the database is working. Shutdown and backup the database.

References

---

ORACLE7 SERVER, SQL Language Reference Manual

---

                                         Oracle Worldwide Customer Support

Received on Tue Jul 23 1996 - 00:00:00 CEST