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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- CUUG 筆記ORACLE db_name instance_name gobal_name service_name db_unique_name分析筆記OracleGo
- Oracle修改(db_name,db_unique_name, servie_names,db_domain）方法OracleAI
- DB_NAME、DB_UNIQUE_NAME、SERVICE_NAME和INSTANCE_NAME等的區別
- 利用nid工具修改db_name和instance_name
- ORACLE_SID,INSTANCE_NAME和DB_NAMEOracle
- instance_name、db_name、ORACLE_SID、db_name等的區別Oracle
- 驗證service_names、db_name、db_unique_name在註冊動態監聽服務名時的作用
- db_name和db_unique_name的區別
- DB_NAME DB_UNIQUE_NAME 和 SID 的理解
- oracle sid,instance_name,db_name,oracle_sid之間的關係Oracle
- [轉]ORACLE中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAMEOracle
- ORACLE中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAME介紹Oracle
- 關於db_name,instance_name等近似概念的理解????
- 關於db_name,oracle_sid,service_names,global_names等的總結說明Oracle
- 關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- （轉）關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- ORACLE資料庫修改資料庫名db_nameOracle資料庫
- ORACL中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAME介紹Oracle
- 轉貼：關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- SID、ORACLE_SID、INSTANCE_NAME、DB_NAME、SERVER_NAMES、GLOBAL_DBNAME等區別OracleServer
- Oracle（b_name,db_unique_name, servie_names,db_domain修改方法）OracleAI
- Rename db_name for OracleOracle
- 修改oracle例項名(sid)和資料庫名(db_name)Oracle資料庫
- 修改資料庫db_name的方法資料庫
- 在oracle 10g for solaris 5.10上修改db_name|db_idOracle 10g
- 利用nid工具修改db_name時hang住
- 【Oracle】service_name和service_names的關係Oracle
- oracle 10gR2 dataguard db_unique_name parameterOracle 10g
- 【故障-ORACLE】‘ALTER SYSTEM SET service_names='' SCOPE=MEMORY SID=’解釋Oracle
- 關於ORACLE_SID和DB_NAME的理解Oracle
- oracle 10g中的幾個概念(sid/db_name/....)Oracle 10g
- oracle 修改ORACLE例項Oracle
- DB_NAME Initialization Parameter (87)
- 【Oracle】修改oracle監聽埠Oracle
- Oracle修改時區Oracle
- oracle中修改processOracle
- oracle em 修改埠Oracle
- Oracle 字符集修改Oracle