HOW TO CHANGE DATABASE NAME USING ORACLE NID UTILITY
====================================================

Here are the simple steps
how I changed the name of one our databases from CLNDB to CLNDBNEW
***********************************************************************
1.Set the database in MOUNT state
**********************************

CLNDB@SYS>STARTUP MOUNT

2.Go to OS level , glance on NID options
****************************************
d:\oracle\product\10.2.0\db_1\database>NID

DBNEWID: Release 10.2.0.3.0 - Production on Sun Jun 7 18:00:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO

d:\oracle\product\10.2.0\db_1\database>NID TARGET=sys/syssys dbname=clndbnew setname=y

DBNEWID: Release 10.2.0.3.0 - Production on Sun Jun 7 18:07:31 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to database CLNDB (DBID=770789742)

Connected to server version 10.2.0

Control Files in database:
D:\ORACLE\ORADATA\CLNDB\CONTROL1.CTL
D:\ORACLE\ORADATA\CLNDB\CONTROL2.CTL

Change database name of database CLNDB to CLNDBNEW? (Y/[N]) =>Y
Proceeding with operation
Changing database name from CLNDB to CLNDBNEW
Control File D:\ORACLE\ORADATA\CLNDB\CONTROL1.CTL - modified
Control File D:\ORACLE\ORADATA\CLNDB\CONTROL2.CTL - modified
Datafile D:\ORACLE\ORADATA\CLNDB\SYSTEM1.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\UNDOTBS1.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\SYSAUX1.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\LMTS.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\LMTSU.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\SSMA.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\SSMM.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\BIG.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\USERS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\ONE.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\TWO.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\CLNDB\TEMP2.DBF - wrote new name
Control File D:\ORACLE\ORADATA\CLNDB\CONTROL1.CTL - wrote new name
Control File D:\ORACLE\ORADATA\CLNDB\CONTROL2.CTL - wrote new name
Instance shut down

Database name changed to CLNDBNEW.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Modify parameter file name to new name
**************************************
d:\oracle\product\10.2.0\db_1\database>rename initclndb.ora initclndbnew.ora

d:\oracle\product\10.2.0\db_1\database>edit initclndbnew.ora

----- change the parameter db_name='clndb' to 'clndbnew'

generate a new password file before restarting
**********************************************

d:\oracle\product\10.2.0\db_1\database>orapwd file=pwdclndbnew.ora password=syssys

Set New Oracle SID
******************
d:\oracle\product\102~1.0\db_1\database>set oracle_sid=clndbnew

Set New Instance At OS level
****************************

d:\oracle\product\102~1.0\db_1\database>oradim -new -sid clndbnew
Instance created.

Connect to Oracle
*****************

d:\oracle\product\102~1.0\db_1\database>sqlplus / as sysdba

clndbnew@SYS>startup mount
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 268438216 bytes
Database Buffers 247463936 bytes
Redo Buffers 7094272 bytes
Database mounted.

clndbnew@SYS>alter system open resetlogs;

clndbnew@SYS>select name from v$database;

NAME
---------
CLNDBNEW

Please leave your comments, if this post helps you.

Regards,
Shafiulla Syed.

My First Post

Hi,
This is my first post in this blog.