Detailed explanation of modifying database names in Oracle

For more information about how to modify the database name in Oracle, see step by step.

My friend asked me how to change the database name over the past two days, so I made a test and recorded it to illustrate the problem.
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 :??????

1 2 Next Page