Changing the DBID and Database Name

Database : 10gr1
OS PLATFORM : Windows
Archive Log : Enable
Non Distributed Database System.


SQL> select DBID,NAME from v$database;

DBID NAME
---------- ---------
13875087 ORA10G


1.Ensure that you have a recoverable whole database backup.


2.shutdown database and startup in mount stage.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege.

C:\>nid TARGET=SYS/oracle DBNAME=ORA101
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORA10G (DBID=13875087)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 13875087 to 13925003
Changing database name from ORA10G to ORA101
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - modifie
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF - dbid changed
, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF - dbid changed
, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF - dbid changed,
wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\TEMP01.DBF - dbid changed,
wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - dbid ch
anged, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - dbid ch
anged, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - dbid ch
anged, wrote new name
Instance shut down

Database name changed to ORA101.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORA101 changed to 13925003.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

4.Open PFILE and edit DB_NAME parameter and create new SPFILE.
and Create New Password file.

C:\>orapwd file=c:\oracle\product\10.1.0\db_1\database\PWDora101.ora PASSWORD=or
acle entries=5 force=y
C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 18:54:20 2007

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

Enter password:
Connected to an idle instance.

SQL> -- Edit DB_NAME parameter in PFILE and CREATE NEW SPFILE.
SQL> create SPFILE from PFILE;

File created.

Note:password file name like "PWD.ORA" and sid is equal to INSTANCE_NAME not DATABASE_NAME.


5.Startup Database in MOUNT MODE and open with RESETLOGS.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select DBID,NAME from v$database;

DBID NAME
---------- ---------
13925003 ORA101



Note : Take complete database backup. previous backup no longer usable.