Oracle changes the database name method _oracle

How to modify the database name (db_name) and instance name (instance_name or service_name)

Nid is a tool that Oracle has provided from 9IR2 and can be used to change the database name without having to go through the cumbersome ways of rebuilding control files before.
The NID is a self-contained tool in the Oracle_home/bin directory. The following methods assume landing into the database native to do.
Objective: In this case, assume that the original database name is ORCL, to be changed to DBORCL, the original instance name (Service_name,instance_name) ORCL, to be changed to DBORCL.
Steps Overview:

1. Check the current parameter condition
2.shutdown database, then mount the database
3. Run Nid command
4. Change parameter file Pfile.ora (db_name,instance_name)
5. Detection of changes
6. If the Windows platform, to modify the service
7. Modify the Listening service

C:\Documents 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, +, Oracle. All rights reserved.

Connect to:
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 corresponding 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.--First shutdown database
sql> shutdown Immediate
The database has been closed.
The database has been unloaded.
The ORACLE routine has been closed.
3.--nid need to be in the Mount state to do. Because you want to change the information for the control file
Sql> Startup Mount
The ORACLE routine has started.
Total System Global area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database buffers 423624704 bytes
Redo buffers 7135232 bytes
Database loading complete.

Nid is an operating system command, so use the host
Sql> Host Nid-help
Dbnewid:release 10.2.0.1.0-production on Fri Oct 23 13:40:54 2009
Copyright (c) 1982, +, 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
Above is the syntax of the NID command
4. --Run 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, +, Oracle. All rights reserved.
Connected Database ORCL (dbid=1224293825)
Server version is connected 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
Do you want to change the database ID and database name ORCL to DBORCL? (Y/[n]) => Y
The operation continues
Change the database ID from 1224293825 to 3277448932
Change the 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
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf-dbid has changed,
New name has been written
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf-dbid has changed,
New name has been written
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf-dbid has changed,
New name has been written
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf-dbid has changed and has been
Write new name
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. Dbf-dbid has changed,
New name has been written
Data file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01. Dbf-dbid has changed and has been
Write new name
Control file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01. Ctl-dbid has changed,
New name has been written
Control file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02. Ctl-dbid has changed,
New name has been written
Control file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03. Ctl-dbid has changed,
New name has been written
Instance shutdown
The database name has been changed to DBORCL.
Modify the parameter file and generate a new password file before restarting.
The database ID for database DBORCL has been changed to 3277448932.
All previous backup and archive redo logs for this database are not available.
The database does not recognize the previous backup and archive logs in the recovery area.
The database is turned off and the database is opened with the Resetlogs option.
The database name and ID have been successfully changed.
Dbnewid-Completed successfully.
5.---shutdown database
sql> shutdown Immediate
Ora-01034:oracle Not available
Ora-27101:shared Memory realm does not exist

Sql> Startup Nomount
The ORACLE routine has 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 initialization parameter file, 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 started.
Total System Global area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database buffers 423624704 bytes
Redo buffers 7135232 bytes
Database loading complete.
ORA-01589: You must use the Resetlogs or Noresetlogs option to open the database

Sql> create SPFile from pfile= ' D:\oracle\product\10.2.0\pfile20091022.ora '
2;
The file has been created.
sql> shutdown Immediate
ORA-01109: Database not open

The database has been unloaded.
The ORACLE routine has been closed.

Sql> Startup
The ORACLE routine has started.
Total System Global area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database buffers 423624704 bytes
Redo buffers 7135232 bytes
Database loading complete.
ORA-01589: You must use the Resetlogs or Noresetlogs option to open the database

sql> ALTER DATABASE open Noresetlogs
2;
ALTER DATABASE open Noresetlogs
*
Line 1th Error:
ORA-01588: You must use the Resetlogs option to open the database

sql> ALTER DATABASE open Resetlogs
2;
The database has changed.

8.-Now that the database has been started, check it out and see if it 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. Found that the v$instance did not change over, this is because on the Windows platform, continue with the following operations

If it is a Windows platform, the Instanc_name does not change in v$instance, continue with the following operation
The passwd file is usually placed in the Oracle_home/database directory, and the file name is pwdsid.ora,sid as the instance name (service_name).
If the current database name and service_name are ORCL, the passwd file is Pwdorcl.ora

C:\Documents and Settings\administrator>orapwd File=d:\oracle\product\10.2.0\db_
1\database\pwddborcl.ora Password=aibo entries=5
To be aware of this, although the database name has been changed to DBORCL, but instance_name or ORCL, so passwd file must be the same as before. Otherwise there will be an error.

10. Delete the previous instance ORCL
C:\Documents and Settings\administrator>oradim-delete-sid ORCL
Instance has been deleted.
11. Create a new instance name DBORCL
C:\Documents 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-please delete or rename
Instance has been created.
12.

C:\Documents and Settings\administrator>set ORACLE_SID=DBORCL
C:\Documents 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, +, Oracle. All rights reserved.

Connect to:
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 Service Name
C:\Documents and Settings\administrator>lsnrctl Reload
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 2 February-October-2009 13:4
3:13
Copyright (c) 1991, +, Oracle. All rights reserved.
Connecting to (Description= (address= (protocol=tcp) (host=192.168.2.56) (port=1521))
Command execution succeeded
C:\Documents 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, +, Oracle. All rights reserved.

Connect to:
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 has been changed
C:\Documents and Settings\administrator>tnsping DBORCL
TNS Ping Utility for 32-bit windows:version 10.2.0.1.0-production on 2 February-October-
2009 13:50:00
Copyright (c) 1997, +, Oracle. All rights reserved.
Parameter file used:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used the Ezconnect adapter to resolve the alias
Attempting to Contacts (description= connect_data= (service_name=dborcl.gdgg.local
) (Address= (PROTOCOL=TCP) (host=202.106.195.30) (port=1521)))
^c

Test does not pass, indicating a problem with DBORCL configuration

Modify the Tnsnames.ora file and add the following:
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
DBORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.2.56) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = DBORCL)
)
)
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--Restart Listening
C:\Documents and Settings\administrator>lsnrctl Reload
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 2 February-October-2009 13:5
0:46
Copyright (c) 1991, +, Oracle. All rights reserved.
Connecting to (Description= (address= (protocol=tcp) (host=192.168.2.56) (port=1521))
Command execution succeeded

---in the test
C:\Documents and Settings\administrator>tnsping DBORCL
TNS Ping Utility for 32-bit windows:version 10.2.0.1.0-production on 2 February-October-
2009 13:52:18
Copyright (c) 1997, +, Oracle. All rights reserved.
Parameter file used:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used the TNSNAMES adapter to resolve the alias
Attempting to contacts (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 to this