修改数据库名（db_name）及实例名（Instance_nameorService_name）

修改数据库名（db_name）及实例名
（Instance_nameorService_name）操作系统版本:rhel6.5 (Red Hat Enterprise Linux Server release 6.5 (Santiago)）
数据库版本：12.1.0.2.0
实验⽬的：
假设原来的数据库名为scp,要改成etdb,原实例名(service_name,instance_name)scp,要改成etdb.
当前数据库的db_name和instance_name:
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
3247610670 SCP
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string scp
db_unique_name string scp
global_names boolean FALSE
instance_name string scp
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string scp
使⽤oracle⾃带的nid⼯具修改数据库的db_name:
SQL> shutdown immediate; --先停⽌数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; --nid需要在mount状态下才能做
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1325402232 bytes
Database Buffers 805306368 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> ! nid target=sys/hzsunssytem dbname=etdb --nid是操作系统下执⾏的命令
DBNEWID: Release 12.1.0.2.0 - Production on Wed May 2519:25:522016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to database SCP (DBID=3247610670)
Connected to server version 12.1.0
Control Files in database:
/home/OracleData/scp/control01.ctl
/home/OracleArch/fast_recovery_area/scp/control02.ctl
Change database ID and database name SCP to ETDB? (Y/[N]) => Y --需要确认
Proceeding with operation
Changing database ID from3247610670 to 637227457
Changing database name from SCP to ETDB
Control File /home/OracleData/scp/control01.ctl - modified
Control File /home/OracleArch/fast_recovery_area/scp/control02.ctl - modified
Datafile /home/OracleData/scp/system01.db - dbid changed, wrote new name
Datafile /home/OracleData/scp/sysaux01.db - dbid changed, wrote new name
Datafile /home/OracleData/scp/undotbs01.db - dbid changed, wrote new name
Datafile /home/OracleData/scp/users01.db - dbid changed, wrote new name
Datafile /home/OracleData/scp/temp01.db - dbid changed, wrote new name
Control File /home/OracleData/scp/control01.ctl - dbid changed, wrote new name
Control File /home/OracleArch/fast_recovery_area/scp/control02.ctl - dbid changed, wrote new name
Instance shut down --此时数据库实例已经关闭
Database name changed to ETDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database ETDB changed to 637227457.
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 name and ID.
DBNEWID - Completed succesfully.
SQL> conn / as sysdba;
Connected to an idle instance. --连接到空闲实例
SQL>
SQL>
SQL> startup mount; --启动数据库到mount状态
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1325402232 bytes
Database Buffers 805306368 bytes
Redo Buffers 13848576 bytes
ORA-01103: database name 'ETDB'in control file is not 'SCP' --提⽰控制⽂件中的数据库名与实际的数据库名不匹配SQL> alter system set db_name=etdb scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1325402232 bytes
Database Buffers 805306368 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
637227457 ETDB
SQL> alter database open resetlogs;
Database altered.
修改数据库的 instance_name:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@dg1 ~]$ sed -i s/"ORACLE_SID=scp"/"ORACLE_SID=etdb"/g ~/.bash_profile
[oracle@dg1 ~]$ source ~/.bash_profile
[oracle@dg1 ~]$ env | grep ORACLE_SID
ORACLE_SID=etdb
[oracle@dg1 ~]$ mv $ORACLE_HOME/dbs/spfilescp.ora $ORACLE_HOME/dbs/spfileetdb.ora
[oracle@dg1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 2523:49:442016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1325402232 bytes
Database Buffers 805306368 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string ETDB
db_unique_name string ETDB
global_names boolean FALSE
instance_name string etdb
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string ETDB
SQL> alter system register; --向listener注册
System altered.
SQL> host lsnrctl reload;
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-MAY-2016 23:52:04
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
The command completed successfully
SQL> host lsnrctl status;
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-MAY-2016 23:52:10
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521))) STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 25-MAY-2016 23:14:06
Uptime 0 days 0 hr. 38 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ETDB" has 1 instance(s).
Instance "etdb", status READY, has 1 handler(s) for this service...
Service "scpXDB" has 1 instance(s).
Instance "etdb", status READY, has 1 handler(s) for this service...
The command completed successfully
最后修改⼀下密码⽂件：
[oracle@dg1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwetdb password='aba123!@#' entries=3
如果listener.ora、 tnsnames.ora⽂件中含有原来的SERVICE_NAME名称的话，⽤编辑器做相应的修改。

针对数据库⾃启动⽂件： /etc/oratab , /etc/rc.local ⽂件也需要做相应的修改。