How to change the database name in oracle

How to modify the Database Name (db_name) and Instance name (Instance_name or Service_name)

Nid is a tool provided by Oracle starting from 9iR2. It can be used to change the database name without the need to recreate the control file or other cumbersome methods.
Nid is a built-in tool in the oracle_home/bin directory. The following method assumes that you have logged on to the local database.
Purpose: In this example, assume that the original database name is orcl, change it to dborcl, and change the original Instance name (service_name, instance_name) orcl to dborcl.
Procedure Overview:

1. Check the current parameter information
2. Shut down the database and then mount the database
3. Run the nid command
4. Change the parameter file pfile. ora (db_name, instance_name)
5. Check changes
6. For windows, modify the service.
7. Modify the listening service

C: \ Documents ents and Settings \ Administrator> sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Thursday October 22 11:56:33 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
1. view the name before the change
SQL> show parameter name
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_name_convert string
Db_name string orcl
Db_unique_name string orcl
Global_names boolean FALSE
Instance_name string orcl
Lock_name_space string
Log_file_name_convert string
Service_names string orcl
2. -- shutdown the database first
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
3. -- nid must be in the mount state. Because you want to change the control file information
SQL> startup mount
The ORACLE routine has been started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes
The database has been loaded.

Nid is an operating system command, so you must use host
SQL> host nid-help
DBNEWID: Release 10.2.0.1.0-Production on Fri Oct 23 13:40:54 2009
Copyright (c) 1982,200 5, 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
The above is the nid command syntax
4. -- Run the nid command
SQL> host nid target = sys/aibo dbname = dborcl
DBNEWID: Release 10.2.0.1.0-Production on Thursday October 22 11:58:27 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected database ORCL (DBID = 1224293825)
Connected Server version 10.2.0
Number of control files in the database:
D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL01.CTL
D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL02.CTL
D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL03.CTL
Change the database ID and database name ORCL to DBORCL? (Y/[N]) => y
Operation continues
Change Database ID from 1224293825 to 3277448932
Change Database Name from ORCL to DBORCL
Control File D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL01.CTL-modified
Control File D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL02.CTL-modified
Control File D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL03.CTL-modified
The data file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSTEM01.DBF-dbid has been changed,
New name written
The data file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ UNDOTBS01.DBF-dbid has been changed,
New name written
The data file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSAUX01.DBF-dbid has been changed,
New name written
Data File D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ USERS01.DBF-dbid changed, already
Write new name
The data file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ EXAMPLE01.DBF-dbid has been changed,
New name written
Data File D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ TEMP01.DBF-dbid changed, already
Write new name
The control file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL01.CTL-dbid has been changed,
New name written
The control file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL02.CTL-dbid has been changed,
New name written
The control file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL03.CTL-dbid has been changed,
New name written
Close instance
The database name has been changed to DBORCL.
Modify the parameter file and generate a new password file before the restart.
The Database ID of database DBORCL has been changed to 3277448932.
All previous backup and archive redo logs of this database are unavailable.
The database cannot identify the previous backup and archive logs in the recovery area.
The database is closed. Use the RESETLOGS option to open the database.
The Database Name and ID have been changed.
DBNEWID-completed successfully.
5. --- shutdown Database
SQL> shutdown immediate
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

SQL> startup nomount
The ORACLE routine has been started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes

SQL> create pfile = 'd: \ oracle \ product \ 10.2.0 \ pfile20091022.ora 'from spfile;
The file has been created.
SQL> shutdown immediate;
ORA-01507 :??????

6. --- modify the initialization parameter file and spfile file (init. ora/spfile)

######################################## ###
Instance_name = eyglen
# Instance_name = eyglev
######################################## ###
Db_domain = ""
Db_name = eyglen
# Db_name = eyglev
######################################## ###
7. --- start the database with modified Parameters
SQL> startup pfile = 'd: \ oracle \ product \ 10.2.0 \ pfile20091022.ora'
The ORACLE routine has been started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes
The database has been loaded.
ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS Option

SQL> create spfile from pfile = 'd: \ oracle \ product \ 10.2.0 \ pfile20091022.ora'
2;
The file has been created.
SQL> shutdown immediate
ORA-01109: the database is not open

The database has been detached.
The ORACLE routine has been disabled.

SQL> startup
The ORACLE routine has been started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes
The database has been loaded.
ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS Option

SQL> alter database open noresetlogs
2;
Alter database open noresetlogs
*
Row 3 has an error:
ORA-01588: to open the database, you must use the RESETLOGS Option

SQL> alter database open resetlogs
2;
The database has been changed.

8. -- now the database has been started. Check whether the database has been modified.
SQL> select open_mode from v $ database;
OPEN_MODE
----------
READ WRITE
SQL> show parameter name
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_name_convert string
Db_name string dborcl
Db_unique_name string dborcl
Global_names boolean FALSE
Instance_name string dborcl
Lock_name_space string
Log_file_name_convert string
Service_names string dborcl
SQL>
SQL> select instance_name from v $ instance;
INSTANCE_NAME
----------------
Orcl

9. I found that v $ instance has not been modified. This is because on windows, continue with the following operations:

* ******* If the instance name in v $ instance is not changed on windows, continue with the following operations:
Passwd files are usually stored in the oracle_home/database directory. The file name is PWDsid. ora and the sid is the Instance name (Service_name ),
If the current database name and service_name are orcl, the passwd file is PWDorcl. ora.

C: \ Documents ents and Settings \ Administrator> orapwd file = D: \ oracle \ product \ 10.2.0 \ db _
1 \ database \ PWDdborcl. ora password = aibo entries = 5
Note that although the database name has been changed to dborcl, instance_name is still orcl. Therefore, the passwd file must be the same as before. Otherwise, an error occurs.

10. Delete the previous instance orcl
C: \ Documents ents and Settings \ Administrator> oradim-delete-sid orcl
The instance has been deleted.
11. Create a new instance named dborcl
C: \ Documents ents and Settings \ Administrator> oradim-new-sid dborcl-intpwd aibo-st
Artmode a-pfile D: \ oracle \ product \ 10.2.0 \ pfile20091022.ora
OPW-00005: files with the same name exist-please delete or rename
The instance has been created.
12.

C: \ Documents ents and Settings \ Administrator> set oracle_sid = dborcl
C: \ Documents ents and Settings \ Administrator> sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Thursday October 22 13:41:12 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.

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

SQL> quit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options disconnected

13. Check the service name
C: \ Documents ents and Settings \ Administrator> lsnrctl reload
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0-Production on-2009
3:13
Copyright (c) 1991,200 5, Oracle. All rights reserved.
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.56) (PORT = 1521 )))
Command executed successfully
C: \ Documents ents and Settings \ Administrator> sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Thursday October 22 13:43:21 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v $ database;
OPEN_MODE
----------
READ WRITE
SQL> select instance_name from v $ instance;
INSTANCE_NAME
----------------
Dborcl
SQL> show parameter name;
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_name_convert string
Db_name string dborcl
Db_unique_name string dborcl
Global_names boolean FALSE
Instance_name string dborcl
Lock_name_space string
Log_file_name_convert string
Service_names string dborcl

SQL> quit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options disconnected
Check whether the service is changed
C: \ Documents ents and Settings \ Administrator> tnsping dborcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0-Production on 22-10 months-
13:50:00 2009
Copyright (c) 1997,200 5, Oracle. All rights reserved.
Used parameter files:
D: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ sqlnet. ora
Alias resolved using EZCONNECT Adapter
Attempting to contact (DESCRIPTION = (CONNECT_DATA = (SERVICE_NAME = dborcl. gdgg. local
) (ADDRESS = (PROTOCOL = TCP) (HOST = 202.106.195.30) (PORT = 1521 )))
^ C

If the test fails, the dborcl configuration is incorrect.

Modify the tnsnames. ora file and add the following content:
××××××××××××××××××××××××××××××××××××××××××× *
DBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.56) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dborcl)
)
)
××××××××××××××××××××××××××××××××××××××××××× *

-- Restart the listener
C: \ Documents ents and Settings \ Administrator> lsnrctl reload
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0-Production on-2009
0: 46
Copyright (c) 1991,200 5, Oracle. All rights reserved.
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.56) (PORT = 1521 )))
Command executed successfully

--- Test
C: \ Documents ents and Settings \ Administrator> tnsping dborcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0-Production on 22-10 months-
13:52:18 2009
Copyright (c) 1997,200 5, Oracle. All rights reserved.
Used parameter files:
D: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ sqlnet. ora

Alias resolved using TNSNAMES Adapter
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.
2.56) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dborcl)
))
OK (20 ms)

All Database names (db_name) and instance names (Instance_name or Service_name) have been changed.