20200111-Oracle修改instance_name、db_name、db_unique_name、service_names

于 2024-12-15 13:01:21 发布
阅读量488 收藏 8
点赞数 3
文章标签： 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/144485702
版权

在这里插入图片描述

查看当前信息

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"

chenoracle
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值