Oracle修改instance_name、db_name、db_unique_name、service_names
Oracle修改instance_name、db_name、db_unique_name、service_names
檢視當前資訊
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dapuchai
db_unique_name string dapuchai
global_names boolean FALSE
instance_name string dapuchai
service_names string dapuchai
--- dapuchai是我的家鄉名
一 修改instance_name
將instance_name由dapuchai修改成cjcdb
1.1 停庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
1.2 修改/etc/oratab
[oracle@cjc ~]$ vim /etc/oratab
#dapuchai:/u01/app/oracle/product/11.2.0/db_1:N
cjcdb:/u01/app/oracle/product/11.2.0/db_1:N
1.3 修改/home/oracle/.bash_profile
[oracle@cjc ~]$ vim .bash_profile
#export ORACLE_SID=dapuchai
export ORACLE_SID=cjcdb
1.4 修改生效
[oracle@cjc ~]$ source .bash_profile
[oracle@cjc ~]$ echo $ORACLE_SID
cjcdb
[oracle@cjc ~]$ env |grep ORACLE
ORACLE_SID=cjcdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
1.5 修改dbs目錄下的檔名
[oracle@cjc ~]$ cd $ORACLE_HOME/dbs
[oracle@cjc dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@cjc dbs]$ ll -rth
total 20K
-rw-r--r-- 1 oracle oinstall 2.8K May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Jun 8 2019 lkDAPUCHAI
-rw-r----- 1 oracle oinstall 1.5K Jun 8 2019 orapwdapuchai
-rw-r----- 1 oracle oinstall 3.5K Jan 11 15:38 spfiledapuchai.ora
-rw-rw---- 1 oracle oinstall 1.6K Jan 11 15:47 hc_dapuchai.dat
[oracle@cjc dbs]$ mv lkDAPUCHAI lkCJCDB
[oracle@cjc dbs]$ mv orapwdapuchai orapwcjcdb
[oracle@cjc dbs]$ mv spfiledapuchai.ora spfilecjcdb.ora
[oracle@cjc dbs]$ mv hc_dapuchai.dat hc_cjcdb.dat
[oracle@cjc dbs]$ ll -rth
total 20K
-rw-r--r-- 1 oracle oinstall 2.8K May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Jun 8 2019 lkCJCDB
-rw-r----- 1 oracle oinstall 1.5K Jun 8 2019 orapwcjcdb
-rw-r----- 1 oracle oinstall 3.5K Jan 11 15:38 spfilecjcdb.ora
-rw-rw---- 1 oracle oinstall 1.6K Jan 11 15:47 hc_cjcdb.dat
1.6 檢視
[oracle@cjc dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 11 15:59:30 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 541068328 bytes
Database Buffers 289406976 bytes
Redo Buffers 6549504 bytes
Database mounted.
Database opened.
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dapuchai
db_unique_name string dapuchai
global_names boolean FALSE
instance_name string cjcdb
service_names string dapuchai
[oracle@cjc ~]$ ps -ef|grep ora_
oracle 3007 1 0 15:59 ? 00:00:00 ora_pmon_cjcdb
oracle 3009 1 0 15:59 ? 00:00:00 ora_psp0_cjcdb
oracle 3011 1 1 15:59 ? 00:00:00 ora_vktm_cjcdb
oracle 3015 1 0 15:59 ? 00:00:00 ora_gen0_cjcdb
oracle 3017 1 0 15:59 ? 00:00:00 ora_diag_cjcdb
oracle 3019 1 0 15:59 ? 00:00:00 ora_dbrm_cjcdb
oracle 3021 1 0 15:59 ? 00:00:00 ora_dia0_cjcdb
oracle 3023 1 0 15:59 ? 00:00:00 ora_mman_cjcdb
oracle 3025 1 0 15:59 ? 00:00:00 ora_dbw0_cjcdb
oracle 3027 1 0 15:59 ? 00:00:00 ora_lgwr_cjcdb
oracle 3029 1 0 15:59 ? 00:00:00 ora_ckpt_cjcdb
oracle 3031 1 0 15:59 ? 00:00:00 ora_smon_cjcdb
oracle 3033 1 0 15:59 ? 00:00:00 ora_reco_cjcdb
oracle 3035 1 0 15:59 ? 00:00:00 ora_mmon_cjcdb
oracle 3037 1 0 15:59 ? 00:00:00 ora_mmnl_cjcdb
oracle 3039 1 0 15:59 ? 00:00:00 ora_d000_cjcdb
oracle 3041 1 0 15:59 ? 00:00:00 ora_s000_cjcdb
oracle 3055 1 0 15:59 ? 00:00:00 ora_arc0_cjcdb
oracle 3057 1 0 15:59 ? 00:00:00 ora_arc1_cjcdb
oracle 3059 1 0 15:59 ? 00:00:00 ora_arc2_cjcdb
oracle 3061 1 0 15:59 ? 00:00:00 ora_arc3_cjcdb
oracle 3063 1 0 15:59 ? 00:00:00 ora_qmnc_cjcdb
oracle 3075 1 0 15:59 ? 00:00:00 ora_cjq0_cjcdb
oracle 3085 1 0 15:59 ? 00:00:00 ora_q000_cjcdb
oracle 3087 1 0 15:59 ? 00:00:00 ora_q001_cjcdb
oracle 3095 2864 0 16:00 pts/1 00:00:00 grep ora_
二 修改db_name
2.1 匯出建立控制檔案指令碼
SQL> alter database backup controlfile to trace as '/home/oracle/cc.ctl';
Database altered.
2.2 停庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.3 生成建立控制檔案指令碼cc.ctl
[oracle@cjc ~]$ cp cc.ctl cc.ctl.bak
[oracle@cjc ~]$ vim cc.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DAPUCHAI" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dapuchai/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/dapuchai/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/dapuchai/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/dapuchai/system01.dbf',
'/u01/app/oracle/oradata/dapuchai/sysaux01.dbf',
'/u01/app/oracle/oradata/dapuchai/undotbs01.dbf',
'/u01/app/oracle/oradata/dapuchai/users01.dbf',
'/u01/app/oracle/oradata/dapuchai/cjc_tbs01a.dbf',
'/u01/app/oracle/oradata/dapuchai/ogg_tbs01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dapuchai/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE OGG_TEMTBS ADD TEMPFILE '/u01/app/oracle/oradata/dapuchai/ogg_temtbs01.dbf'
SIZE 10485760 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M;
---dapuchai替換成cjcdb,DAPUCHAI替換成CJCDB,將REUSE改成SET
[oracle@cjc ~]$ vim cc.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CJCDB" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/cjcdb/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/cjcdb/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/cjcdb/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/cjcdb/system01.dbf',
'/u01/app/oracle/oradata/cjcdb/sysaux01.dbf',
'/u01/app/oracle/oradata/cjcdb/undotbs01.dbf',
'/u01/app/oracle/oradata/cjcdb/users01.dbf',
'/u01/app/oracle/oradata/cjcdb/cjc_tbs01a.dbf',
'/u01/app/oracle/oradata/cjcdb/ogg_tbs01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/cjcdb/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE OGG_TEMTBS ADD TEMPFILE '/u01/app/oracle/oradata/cjcdb/ogg_temtbs01.dbf'
SIZE 10485760 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M;
2.4 修改引數檔案
[oracle@cjc ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 11 16:21:40 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
[oracle@cjc ~]$ cd $ORACLE_HOME/dbs
[oracle@cjc dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@cjc dbs]$ ls
hc_cjcdb.dat initcjcdb.ora init.ora lkCJCDB lkDAPUCHAI orapwcjcdb spfilecjcdb.ora
[oracle@cjc dbs]$ cp initcjcdb.ora initcjcdb.ora.bak
[oracle@cjc dbs]$ vim initcjcdb.ora
dapuchai.__db_cache_size=352321536
cjcdb.__db_cache_size=289406976
dapuchai.__java_pool_size=4194304
cjcdb.__java_pool_size=4194304
dapuchai.__large_pool_size=8388608
cjcdb.__large_pool_size=71303168
dapuchai.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cjcdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dapuchai.__pga_aggregate_target=339738624
cjcdb.__pga_aggregate_target=339738624
dapuchai.__sga_target=503316480
cjcdb.__sga_target=503316480
dapuchai.__shared_io_pool_size=0
cjcdb.__shared_io_pool_size=0
dapuchai.__shared_pool_size=125829120
cjcdb.__shared_pool_size=125829120
dapuchai.__streams_pool_size=0
cjcdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dapuchai/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dapuchai/control01.ctl','/u01/app/oracle/oradata/dapuchai/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dapuchai'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dapuchaiXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/arch'
*.log_archive_format='dapuchai_%S_%R.%T_%D.ARC'
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
--去掉所有dapuchai.__* ，並將所有dapuchai改成cjcdb
[oracle@cjc dbs]$ vim initcjcdb.ora
cjcdb.__db_cache_size=289406976
cjcdb.__java_pool_size=4194304
cjcdb.__large_pool_size=71303168
cjcdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cjcdb.__pga_aggregate_target=339738624
cjcdb.__sga_target=503316480
cjcdb.__shared_io_pool_size=0
cjcdb.__shared_pool_size=125829120
cjcdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cjcdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/cjcdb/control01.ctl','/u01/app/oracle/oradata/cjcdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cjcdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cjcXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/arch'
*.log_archive_format='cjcdb_%S_%R.%T_%D.ARC'
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@cjc dbs]$ mv spfilecjcdb.ora spfilecjcdb.ora.bak
[oracle@cjc dbs]$ rm -rf lkDAPUCHAI
2.5 修改其他目錄名
---將dapuchai替換成cjcdb，DAPUCHAI替換成CJCDB
/u01/app/oracle/oradata/dapuchai
/u01/app/oracle/diag/rdbms/dapuchai
/u01/app/oracle/admin/dapuchai
[oracle@cjc oradata]$ pwd
/u01/app/oracle/oradata
[oracle@cjc oradata]$ ls
dapuchai
[oracle@cjc oradata]$ mv dapuchai cjcdb
[oracle@cjc rdbms]$ pwd
/u01/app/oracle/diag/rdbms
[oracle@cjc rdbms]$ ls
dapuchai
[oracle@cjc rdbms]$ mv dapuchai cjcdb
[oracle@cjc admin]$ pwd
/u01/app/oracle/admin
[oracle@cjc admin]$ ls
dapuchai
[oracle@cjc admin]$ mv dapuchai cjcdb
[oracle@cjc pfile]$ pwd
/u01/app/oracle/admin/cjcdb/pfile
[oracle@cjc pfile]$ ll -rth
total 4.0K
-rw-r----- 1 oracle oinstall 1.7K Jun 8 2019 init.ora.582019222313
[oracle@cjc pfile]$ cp init.ora.582019222313 init.ora.582019222313.bak
[oracle@cjc pfile]$ vim init.ora.582019222313
...dapuchai改成cjcdb
2.6 重新命名現有控制檔案
[oracle@cjc cjcdb]$ pwd
/u01/app/oracle/oradata/cjcdb
[oracle@cjc cjcdb]$ ls
cjc_tbs01a.dbf control02.ctl ogg_temtbs01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control01.ctl ogg_tbs01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@cjc cjcdb]$ mv control01.ctl control01.ctl.bak
[oracle@cjc cjcdb]$ mv control02.ctl control02.ctl.bak
2.7 修改tnsnames.ora
[oracle@cjc admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@cjc admin]$ vim tnsnames.ora
DAPUCHAI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dapuchai)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dapuchai)
)
)
---修改後
CJCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cjc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cjcdb)
)
)
2.8 重建控制檔案
[oracle@cjc ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 11 17:01:23 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @/home/oracle/cc.ctl
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 541068328 bytes
Database Buffers 289406976 bytes
Redo Buffers 6549504 bytes
Control file created.
Database altered.
Tablespace altered.
Tablespace altered.
2.9 驗證
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string cjcdb
db_unique_name string cjcdb
global_names boolean FALSE
instance_name string cjcdb
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string cjcdb
2.10 重建spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 541068328 bytes
Database Buffers 289406976 bytes
Redo Buffers 6549504 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilecjcdb.ora
3 遇到的問題
---建立控制檔案時報錯:
CREATE CONTROLFILE REUSE DATABASE "CJCDB" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name DAPUCHAI in file header does not match given name of
CJCDB
ORA-01110: data file 1: '/u01/app/oracle/oradata/cjcdb/system01.dbf'
解決方案：
將CREATE CONTROLFILE REUSE DATABASE ...改成CREATE CONTROLFILE SET DATABASE ...
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2672985/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 叢集修改OCR中記錄的db_unique_name以及db_name
- CREATE CONTROLFILE修改DB_NAME一定不能使用REUSE
- Oracle修改時區Oracle
- oracle nid修改dbnameOracle
- Oracle 字符集修改Oracle
- oracle ora-00333 修改案例Oracle
- Oracle 增加 修改 刪除 列Oracle
- 2.6.2.1 初始化引數DB_NAME
- Oracle RAC修改public, VIP, SCAN IPOracle
- ordebug 手動修改Oracle sga scnOracle
- 修改oracle 的統計資訊Oracle
- ERROR: failed to establish dependency between database db_name and diskgroupErrorAIDatabase
- oracle 修改表欄位的長度Oracle
- Oracle RAC修改引數檔案位置Oracle
- 【RAC】Oracle RAC如何修改心跳網路Oracle
- Oracle 11G 修改scan_ipOracle
- 使用NID修改Oracle資料庫名Oracle資料庫
- 修改Oracle資料庫字符集(zt)Oracle資料庫
- Oracle RAC修改Scan IP，Public IP的方法Oracle
- 4.2.9 修改元件的Oracle重啟配置元件Oracle
- Oracle 修改欄位型別和長度Oracle型別
- Oracle-批量修改欄位裡面的值Oracle
- oracle之 單例項監聽修改埠Oracle單例
- 【RAC】Oracle rac 如何修改公網及vipOracle
- Oracle 修改預設監聽埠故障分析Oracle
- 【故障處理】ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
- Oracle Rac 修改SGA_TARGET值無變化Oracle
- oracle11g修改控制檔案路徑Oracle
- 修改Oracle字符集為ZHS16GBKOracle
- 修改系統時間對oracle的影響Oracle
- 「Oracle」資料庫字符集編碼修改Oracle資料庫
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- Oracle 增加修改刪除欄位與新增註釋Oracle
- oracle11g修改資料檔案路徑Oracle
- 修改主機時區對Oracle的影響分析Oracle
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- Oracle RAC修改網路配置知識點彙總Oracle
- Oracle 限制業務使用者自身修改密碼Oracle密碼