oracle nid修改dbname

標籤：linux   AC   監聽   load   etl   sum   change   creat   監聽配置   

修改name的目的只是為了日後管理清晰點，不重複。我比較懶、記性又差，所以就整理記下
1、檢查當前name
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string test141
db_unique_name string test141
global_names boolean FALSE
instance_name string test141
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string test141

SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
4003894062 TEST141

2、檢查監聽
[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-9月 -2017 19:23:58

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.233)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-9月 -2017 23:46:38
Uptime 2 days 19 hr. 37 min. 20 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/db/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.233)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "test141" has 1 instance(s).
Instance "test141", status READY, has 1 handler(s) for this service...
The command completed successfully
--上面的檢查其實都可以省略

--開始nid修改name
大致步驟：關閉DB、mount DB、nid dbname、修改init*.ora、修改環境變數並open DB、重建(s)pfile、修改tnsname.ora和listener.ora中對應名字並啟動
關閉DB之前必須要知道sys密碼，不知道的話就改下
SQL> alter user sys identified by qwer1234;
User altered.

1、關閉資料庫，啟動至mount模式
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size 2229944 bytes
Variable Size 687868232 bytes
Database Buffers 1392508928 bytes
Redo Buffers 5173248 bytes
Database mounted.

2、NI修改DB_NAME
[[email protected] admin]$ nid target=sys/qwer1234 dbname=test233

DBNEWID: Release 11.2.0.3.0 - Production on 星期一 9月 4 19:26:25 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database TEST141 (DBID=4003894062)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/dbdata/control01.ctl
/u01/app/oracle/fast_recovery_area/dbdata/control02.ctl

Change database ID and database name TEST141 to TEST233? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 4003894062 to 3120964513
Changing database name from TEST141 to TEST233
Control File /u01/app/oracle/oradata/dbdata/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/dbdata/control02.ctl - modified
Datafile /u01/app/oracle/oradata/dbdata/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_member.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_member.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_account.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_account.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_sysdb.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_sysdb.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_risk.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_risk.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_tppaml.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_tppaml.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_test.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/DAT_MS_ORDERS.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/pftp01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_cashreserve_01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_orders.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_orders.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/DAT_SSCARD.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/IDX_SSCARD.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/DAT_GDXT.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/IDX_GDXT.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/DAT_DINPAYPOS.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/IDX_DINPAYPOS.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/DAT_ERCON.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/IDX_ERCON.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/dat_bill01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/idx_bill01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/temp01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dbdata/TEMP_PFPT.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/dbdata/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/dbdata/control02.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to TEST233.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST233 changed to 3120964513.
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.

3、修改參數檔案
cd $ORACLE_HOME/dbs/init***.ora

vi /u01/app/oracle/product/11.2.0/db/dbs/inittest141.ora
#*.db_name=‘test141‘ 注釋了
*.db_name=‘test233‘ 新增為想要修改的名字

vi .bash_profile

#export ORACLE_HOSTNAME=test141
export ORACLE_HOSTNAME=test233
#export ORACLE_SID=test141
export ORACLE_SID=test233

#export ORACLE_UNQNAME=test141
export ORACLE_UNQNAME=test233

4、重新生效環境變數，並啟動資料庫
[[email protected] ~]$ source .bash_profile
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 9月 4 19:33:40 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile =‘/u01/app/oracle/product/11.2.0/db/dbs/inittest141.ora‘;
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size 2229944 bytes
Variable Size 486541640 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5173248 bytes
Database mounted.
ORA-01589: 要開啟資料庫則必須使用 RESETLOGS 或 NORESETLOGS 選項

SQL> alter database open resetlogs;

Database altered.

5、重建pfile檔案
SQL> create spfile from pfile=‘/u01/app/oracle/product/11.2.0/db/dbs/inittest141.ora‘;

File created.

Elapsed: 00:00:00.06
SQL> create pfile from spfile;

File created.

6、修改監聽配置
cd $ORACLE_HOME/network/admin
vi tnsname.ora

test233 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.233)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
#(SERVICE_NAME = test141)
(SERVICE_NAME = test233)
)
)

vi listener.ora
有SERVICE_NAME的話也修改為test233

重新載入或啟動
[[email protected] admin]$ lsnrctl reload/start

oracle nid修改dbname