Oracle NID工具修改数据库DBID、数据库名称、数据库实例名
DBID是数据库的唯一标识符，在一些特殊场合会涉及到DBID的相关内容，本篇文章的目的是演示将DB_NAME的值从ORCL修改为ORCL1。
一.查看当前的环境
[oracle@oracledb admin]$ echo $ORACLE_SID orcl [oracle@oracledb admin]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 17 09:34:51 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> show parameter service_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string orcl SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string orcl SQL> select dbid,name from v$database; DBID NAME ---------- --------- 1424428733 ORCL SQL> host lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-DEC-2015 09:36:52 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 17-DEC-2015 09:32:02 Uptime 0 days 0 hr. 4 min. 49 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledb)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
二.启动数据库到MOUNT模式。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted.
三.使用NID工具将DB_NAME从orcl修改为orcl1。
[oracle@oracledb admin]$ nid target=sys/wangshengzhuang dbname=orcl1 DBNEWID: Release 11.2.0.1.0 - Production on Thu Dec 17 09:40:47 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to database ORCL (DBID=1424428733) Connected to server version 11.2.0 Control Files in database: /usr/oracle/app/oradata/orcl/control01.ctl /usr/oracle/app/flash_recovery_area/orcl/control02.ctl Change database ID and database name ORCL to ORCL1? (Y/[N]) => Y Proceeding with operation Changing database ID from 1424428733 to 1283125855 Changing database name from ORCL to ORCL1 Control File /usr/oracle/app/oradata/orcl/control01.ctl - modified Control File /usr/oracle/app/flash_recovery_area/orcl/control02.ctl - modified Datafile /usr/oracle/app/oradata/orcl/system01.db - dbid changed, wrote new name Datafile /usr/oracle/app/oradata/orcl/sysaux01.db - dbid changed, wrote new name Datafile /usr/oracle/app/oradata/orcl/undotbs01.db - dbid changed, wrote new name Datafile /usr/oracle/app/oradata/orcl/users01.db - dbid changed, wrote new name Datafile /usr/oracle/app/oradata/orcl/example01.db - dbid changed, wrote new name Datafile /usr/oracle/app/oradata/orcl/temp01.db - dbid changed, wrote new name Control File /usr/oracle/app/oradata/orcl/control01.ctl - dbid changed, wrote new name Control File /usr/oracle/app/flash_recovery_area/orcl/control02.ctl - dbid changed, wrote new name Instance shut down Database name changed to ORCL1. Modify parameter file and generate a new password file before restarting. Database ID for database ORCL1 changed to 1283125855. 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. [oracle@oracledb admin]$
四.新建密码文件。
[oracle@oracledb admin]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=wangshengzhuang entries=5
五.修改参数文件中的DB_NAME参数值。
由于nid修改了控制文件和数据文件中的DB_NAME值，所以将参数文件中的DB_NAME修改来和控制文件、数据文件相同，修改为ORCL1。
[oracle@oracledb admin]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 17 09:48:14 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> alter system set db_name=orcl1 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 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string ORCL1 SQL>
六.查看修改后的环境
修改后的数据库DB_NAME和Service_name都会自动发生变化，instance_name不会发生变化。
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string ORCL1 SQL> show parameter service NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string ORCL1 SQL> show parameter instance_nam NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string orcl SQL> select dbid,name from v$database; DBID NAME ---------- --------- 1283125855 ORCL1 SQL> host lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-DEC-2015 09:52:23 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 17-DEC-2015 09:32:02 Uptime 0 days 0 hr. 20 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledb)(PORT=1521))) Services Summary... Service "ORCL1" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully SQL>
七、修改实例名
修改.bash_profile 中的ORACLE_SID
vi .bash_profile
使修改生效
source .bash_profile
修改参数文件路径
[oracle@oracledb ~]$ cd $ORACLE_HOME/dbs [oracle@oracledb dbs]$ pwd /usr/oracle/app/product/11.2.0/dbhome_1/dbs [oracle@oracledb dbs]$ mv spfileorcl.ora spfileorcl1.ora [oracle@oracledb dbs]$ mv initorcl.ora initorcl1.ora
重启数据库，查看实例名
[oracle@oracledb dbs]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 17 10:03:21 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: *************** Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. Database opened. SQL> show parameter instance_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string orcl1
