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 ...