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"，共同学习，共同成长！！！