Change Oracle Database Name

use "nid" command-line (Example: testdb2 -> testdb)

$ sqlplus / as sysdba

SQL*Plus: Release - Production on Tue Sep 9 12:39:21 2008

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

Connected to an idle instance.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 1803841536 bytes
Fixed Size 2145304 bytes
Variable Size 922747880 bytes
Database Buffers 872415232 bytes
Redo Buffers 6533120 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ nid target=system/password DBNAME=testdb

DBNEWID: Release - Production on Tue Sep 9 12:39:40 2008

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

Connected to database TESTDB2 (DBID=986660483)

Connected to server version

Control Files in database:
/oradata/testdb2/control01.ctl
/oradata/testdb2/control02.ctl
/oradata/testdb2/control03.ctl

Change database ID and database name TESTDB2 to TESTDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 986660483 to 2448266380
Changing database name from TESTDB2 to TESTDB
Control File /oradata/testdb2/control01.ctl - modified
Control File /oradata/testdb2/control02.ctl - modified
Control File /oradata/testdb2/control03.ctl - modified
Datafile /oradata/testdb2/system01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/sysaux01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/undotbs01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/users01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/example01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/test01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/temp01.dbf - dbid changed, wrote new name
Control File /oradata/testdb2/control01.ctl - dbid changed, wrote new name
Control File /oradata/testdb2/control02.ctl - dbid changed, wrote new name
Control File /oradata/testdb2/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB changed to 2448266380.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

-> modified parameter file [don't forget]
*.db_name='testdb'

-> gererated new password file.

If found
ORA-01103: database name 'TESTDB' in control file is not 'TESTDB2'

Perhaps should check parameter file and modify


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1803841536 bytes
Fixed Size 2145304 bytes
Variable Size 956302312 bytes
Database Buffers 838860800 bytes
Redo Buffers 6533120 bytes
Database mounted.

SQL> alter database open RESETLOGS;

Database altered.


SQL> select NAME from v$database;

NAME
---------
TESTDB