changing oracle database name manually and database id

Hi,
We can change our Database name manually by peforming below steps as follows:

changing oracle database name:
---------------------------------------------------

Using backup controlfile (Manual Process)

Step 1: Take the backup of your controlfile creation script in trace file.
------
As a first priority for DBA please take the backup of your controlfile
creation script in trace file which is located in udump directory.Switch the logfile
for recent changes to be recorded in logfiles.

SQL> show parameter %user%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string D:\ADMIN\BOSCHDB\UDUMP

SQL> select name from v$database;

NAME
---------
BOSCHDB

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to trace;

Database altered.

Step 2: Editing and creating controlfile creation script.
------
We can find the trace file in windows by seeing the most recent modified file which has the naming convention like 'boschdb_ora_2768.trc'.We have to edit this file
by deleting all the lines before 'startup nomount' command,once you do this the actual
control fie creation script will look like as given below:

controlfile_create.sql:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORADATA\BOSCHDB\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\ORADATA\BOSCHDB\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\ORADATA\BOSCHDB\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:\ORADATA\BOSCHDB\SYSTEM01.DBF',
'D:\ORADATA\BOSCHDB\UNDOTBS01.DBF',
'D:\ORADATA\BOSCHDB\SYSAUX01.DBF',
'D:\ORADATA\BOSCHDB\USERS01.DBF',
'D:\ORADATA\BOSCHDB\EXAMPLE01.DBF',
'D:\ORADATA\BOSCHDB\USER123.DBF'
CHARACTER SET WE8MSWIN1252
;

Step 3: Find the control files location and parameter file location.
------
SQL> show parameter %control%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string D:\ORADATA\BOSCHDB\CONTROL01.C
TL, D:\ORADATA\BOSCHDB\CONTROL
02.CTL, D:\ORADATA\BOSCHDB\CON
TROL03.CTL

My parameter file location is 'D:\admin\BoschDB\pfile' and my parameter file name
is 'init.ora.11172010122445'.

Step 4:Shutdown the instance.
------
We need to shutdown our instance in order to change parameter values i pfile.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 5: Change the db_name parameter in the parameter file.
------
Edit the parameter file 'init.ora.11172010122445'(In my experiment) by changing
the value of db_name parameter to TESTDB and save the changes.

###########################################
# Database Identification
###########################################
db_domain=""
db_name=TESTDB

###########################################

Step 6: Create the spfile from pfile.
------

SQL> conn /as sysdba
SQL> create spfile from pfile='D:\admin\BoschDB\pfile\init.ora.11172010122445';

File created.

Step 6:
--------
Create the controlfile by script created in step2 of this experiment as follows:


SQL> @D:\admin\BoschDB\udump\controlfile_create.sql
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 92275368 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes

Control file created.

Step 7: Verify the changes.
------
Once the controlfile gets created our Database as we all know will be in mount
state.We have to open our database with 'resetlogs' option Because The RESETLOGS option is always required after
incomplete media recovery or recovery using a backup control file.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TESTDB

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

To make this article complete on demand of Prafull,I'm adding the below steps for changing Database id.

changing DBID(Database id) of Database:
-------------------------------------------------------

Step 1: Set the sid of the Database
-------

As the first you have to set the sid of the Database in the Operating system.I'm doing
it on Windows but for unix also steps almost remain the same accept setting sid.

In Windows: set ORACLE_SID=TESTDB

In Unix environment:export ORACLE_SID=TESTDB

U:\>set ORACLE_SID=TESTDB


step 2: Start the Database
-------
Start the Database with sys user having SYSDBA privilege.

U:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 16 11:29:43 2011

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

step 3: Mount the Database
--------

You have to mount the Database for using DBNEWID utility.

If you don't mount you will get error NID-00121.The error is just for reference.

ERROR:
------
U:\>nid TARGET=/

DBNEWID: Release 10.2.0.1.0 - Production on Wed Feb 16 11:30:05 2011

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

Connected to database TESTDB (DBID=1762398852)

NID-00121: Database should not be open


Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Let us mount the Database for executing nid command successfully.

SQL> startup mount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 96469672 bytes
Database Buffers 188743680 bytes
Redo Buffers 2945024 bytes
Database mounted.

Step 4: Using nid command(DBNEWID utility) for changing DBID(Database id)
--------

Note 1:You have to set SID before using nid command(DBNEWID utility) whose DBID you want to change.
Note 2:Using nid command(DBNEWID utility) you can change database and database id but
in the below example I just changed DBID.

You have to use nid command(DBNEWID utility) for changing the DBID (Database
id ) as follows:

U:\>nid TARGET=/

DBNEWID: Release 10.2.0.1.0 - Production on Wed Feb 16 11:32:07 2011

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

Connected to database TESTDB (DBID=1762398852)

Connected to server version 10.2.0

Control Files in database:
D:\ORADATA\BOSCHDB\CONTROL01.CTL
D:\ORADATA\BOSCHDB\CONTROL02.CTL
D:\ORADATA\BOSCHDB\CONTROL03.CTL

Change database ID of database TESTDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1762398852 to 2526615742
Control File D:\ORADATA\BOSCHDB\CONTROL01.CTL - modified
Control File D:\ORADATA\BOSCHDB\CONTROL02.CTL - modified
Control File D:\ORADATA\BOSCHDB\CONTROL03.CTL - modified
Datafile D:\ORADATA\BOSCHDB\SYSTEM01.DBF - dbid changed
Datafile D:\ORADATA\BOSCHDB\UNDOTBS01.DBF - dbid changed
Datafile D:\ORADATA\BOSCHDB\SYSAUX01.DBF - dbid changed
Datafile D:\ORADATA\BOSCHDB\USERS01.DBF - dbid changed
Datafile D:\ORADATA\BOSCHDB\EXAMPLE01.DBF - dbid changed
Datafile D:\ORADATA\BOSCHDB\USER123.DBF - dbid changed
Control File D:\ORADATA\BOSCHDB\CONTROL01.CTL - dbid changed
Control File D:\ORADATA\BOSCHDB\CONTROL02.CTL - dbid changed
Control File D:\ORADATA\BOSCHDB\CONTROL03.CTL - dbid changed
Instance shut down

Database ID for database TESTDB changed to 2526615742.
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 ID.
DBNEWID - Completed succesfully.

As you see in the above output database ID is changed from 1762398852 to 2526615742

Note 3: command for changing only Database name is given below:

nid TARGET=SYSTEM/manager@TESTDB DBNAME=TESTDB2 SETNAME=YES LOGFILE=dbname.out


Step 5: Open the Database and Verify.
------
After running the nid command(DBNEWID) utility Database will be shutdown
so open the database and open it with RESETLOGS option as it is described in the output of step4 and verify the Changed Database id with the help of v$database view.

SQL> startup mount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 96469672 bytes
Database Buffers 188743680 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid||' '||name||' '||resetlogs_change#||' '||resetlogs_time
2 from v$database;

DBID||''||NAME||''||RESETLOGS_CHANGE#||''||RESETLOGS_TIME
-------------------------------------------------------------------------------

2526615742 TESTDB 2702513 16-FEB 2011.

Some times small things matters the most.Hope it helps.


Best regards,

Rafi.