Article-ID: <Note:61590.1>
Alias: OLS:11234946.61
Circulation: PUBLISHED (EXTERNAL)
Folder: server.DBA.Admin
Title: Renaming an existing Windows NT database
Document-Type: BULLETIN
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 07.02.0X to 08.01.0X
Updated-Date: 09-MAR-2001 09:40:06
References:
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: DATAFILEINFO; WINNT;
Products: 5;

PURPOSE
This bulletin outlines the steps required to rename an existing Oracle
Database on the Windows NT platform, including the SID, DB_NAME,
and all files associated with these.

SCOPE & APPLICATION
This note is intended for all DBAs.


===============================================================================

These instructions assume the following:

- your database is started
- you are on the Server
- the existing database SID is ORCL and DB_NAME is ORACLE
- the new database SID will be TEST and DB_NAME will be TEST

For database version 7.2 use sqldba72 and oradim72
7.3 use svrmgr23 and oradim73
8.0 use svrmgr30 and oradim80
8.1 use Svrmgrl and oradim

This document uses an 8.0 database, but these instructions can easily be
translated to the another version by changing the utility name.


---------------------------------------------------------------
WARNING:
Before proceeding, you should take a valid full backup.
You should also read the entire bulletin before you attempt to
use it.
---------------------------------------------------------------


1. Back up the controlfile to trace:

C:\> SET ORACLE_SID=ORCL
C:\> svrmgr30
SVRMGR> CONNECT INTERNAL/<internal_password>
Connected.

Now, we need to verify the user_dump_destination. This can be done
in two ways:

SVRMGR> SHOW PARAMETER USER_DUMP_DEST

NAME TYPE VALUE
---------------- ------- ------------------------------
user_dump_dest string D:\ORANT\rdbms80\utrace

** OR **

SVRMGR> SELECT value from v$parameter where name = 'user_dump_dest';

VALUE
----------------------------------
D:\ORANT\rdbms80\utrace


Create the dump file copy of the controlfile:

SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This will create a dump file, ORAxxxxx.TRC, in the user_dump_destination.


2. Verify which files your database contains:

SVRMGR> select name from v$datafile;

NAME
-----------------------------------------
D:\DATABASES\ORCL\SYS1ORCL.ORA
D:\DATABASES\ORCL\RBS1ORCL.ORA
D:\DATABASES\ORCL\USR1ORCL.ORA
D:\DATABASES\ORCL\TMP1ORCL.ORA
D:\DATABASES\ORCL\INDX1ORCL.ORA
D:\DATABASES\ORCL\OEM204.DBF
D:\DATABASES\ORCL\USR2ORCL.ORA
D:\DATABASES\ORCL\TEST.DBS
D:\DATABASES\ORCL\WEBDB.DBF


3. SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE the database and EXIT.


SVRMGR> shutdown immediate


4. Stop the services:

Using Control Panel/Services - scroll down to OracleServiceORCL and
click on the STOP button. You might be asked if you want to stop the
OracleStartORCL-service too. If so, select yes. Please note that under
Oracle 8i, only the OracleServiceORCL will exist.

Or type at the command prompt:

C:\>net stop OracleServiceORCL

The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services:

OracleStartORCL

Do you want to continue this operation? (Y/N): y

The OracleStartORCL service was stopped successfully.

The OracleServiceORCL service is stopping.
The OracleServiceORCL service was stopped successfully.
C:\>


5. Generally, the database files contain the SID name; if you want to change
this, use Explorer, File Manager or DOS, and rename the files listed in
step 2 to the appropriate filenames.

If you want to files to be placed elsewhere, copy the files to the new
Location.

Under Oracle8I, the default directory structure is such that :

D:\Oracle\oradata\ORCL - will contain control files, redo logs, datafiles

D:\Oracle\admin\ORCL\pfile - will contain the init file.

These files typically will not contain the <SID> name as part of the filename.

Example: The init file for the <SID> of 'LEO' will be:

D:\Oracle\admin\LEO\pfile\init.ora - with the directory structure defining the <SID>.

Reference the Getting Started Oracle8I for Windows NT manual for more information
Regarding the Oracle8I default directory structure.


6. Create and Edit the INITTEST.ORA:

a. Copy the %ORACLE_HOME%\database\INITORCL.ORA to
%ORACLE_HOME%\database\INITTEST.ORA
b. Replace all occurrences of the string ORCL with TEST and modify all
necessary directories to the correct path.
c. Modify DB_NAME = ORACLE to read DB_NAME = TEST
d. Save the file.

7. Edit the dump file created in Step 1:

a. Change all occurrences of the string ORCL to TEST.
b. Remove the header (delete all lines above the STARTUP NOMOUNT)
c. Modify the Startup line to read:

STARTUP NOMOUNT PFILE=%ORACLE_HOME%\DATABASE\INITTEST.ORA

In our example we would use:

STARTUP NOMOUNT PFILE=D:\ORANT\DATABASE\INITTEST.ORA

d. Change the NAME of the database. Modify the CREATE CONTROLFILE:

replace REUSE DATABASE "Oracle" with SET DATABASE "Test"
replace NORESETLOGS with RESETLOGS

e. Remove the lines

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;

NOTE: the ALTER SYSTEM ARCHIVE LOG ALL is only present when the original
database is in ARCHIVEMODE.

f. Save the file to CHNAME.SQL


=========================================================================

Example:
========

STARTUP NOMOUNT PFILE=D:\TEST\DATABASE\INITTEST.ORA
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 (
'D:\DATABASES\TEST\LOGTEST1.ORA',
'D:\DATABASES\TEST\CTLBIS\CTL1TESTBIS.ORA'
) SIZE 2M,
GROUP 2 (
'D:\DATABASES\TEST\LOGTEST2.ORA',
'D:\DATABASES\TEST\CTLBIS\CTL2TESTBIS.ORA'
) SIZE 2M
DATAFILE
'D:\DATABASES\TEST\SYS1TEST.ORA',
'D:\DATABASES\TEST\RBS1TEST.ORA',
'D:\DATABASES\TEST\USR1TEST.ORA',
'D:\DATABASES\TEST\TMP1TEST.ORA',
'D:\DATABASES\TEST\INDX1TEST.ORA',
'D:\DATABASES\TEST\OEM204.DBF',
'D:\DATABASES\TEST\USR2TEST.ORA',
'D:\DATABASES\TEST\TEST.DBS',
'D:\DATABASES\TEST\WEBDB.DBF'
;

===========================================================================

8. Use ORADIM80 to create the new instance with the new SID. From a DOS
Command Prompt type:

ORADIM80 -new -sid TEST -intpwd password -startmode auto -pfile %ORACLE_HOME%\DATABASE\INITTEST.ORA


9. Verify that the new service is started:

a. From in Control Panel/Services, scroll down to OracleServiceTEST.
b. If not started, click on the START button.


10. Executing the "chname.sql" script:

C:\> SET ORACLE_SID=TEST
C:\> svrmgr30

SVRMGR> CONNECT INTERNAL/internal_password
Connected.
SVRMGR> @<user_demp_destination_path>\chname.sql
#or the directory where you saved your chname-script.

This will recreate the controlfile with the new datafiles....


11. Open and Shutdown the database.

SVRMGR>ALTER DATABASE OPEN RESETLOGS;
statement processed

Verify at this point if the database is open:

SVRMGR>SELECT status FROM V$THREAD;

STATUS
------
OPEN

SVRMGR>SHUTDOWN IMMEDIATE;


12. Stop the TEST service and take a full backup

a. From in Control Panel/Services, scroll down to OracleServiceTEST.
b. Click on the STOP button.
c. Take a full backup of your database.


13. Removing the old-instance:

See <Note:62004.1> Removing an Oracle Instance and Database from
Windows NT

Please note:

a. If you reused the same files or you didn't rename them (i.e. you
skipped Step 2 partially or completely), don't remove these files
when following Step 6 of <Note:62004.1>

b. The instance removed in <Note:62004.1> is called TEST, which is
our new instance; you must replace TEST by ORCL in <Note:62004.1>
if you want to follow the instructions in <Note:62004.1> to the
letter.

Also, be sure to check your NET8 or SQL*Net setup for references to ORCL and
TEST (i.e. TNSNAMES.ORA, LISTENER.ORA, etc.).


----------------------------------------------------------------------------
Oracle Support Services