事情是这样子的,用户目前有一套生产系统,然后后续会搭建一套新系统与其做ADG,新的系统会使用现有系统的DB_UNIQUE_NAME。由于ADG要求两套数据库的DB_UNIQUE_NAME不一样,因此希望能将现有系统的DB_UNIQUE_NAME修改,例如加一个tmp后缀。
以下为实验过程,RAC环境使用OCI建立了2节点的RAC环境,操作过程参考文档:How to change the DB_UNIQUE_NAME in a RAC database (Doc ID 1604421.1)。这个文档思路没问题,但写得太简单,直接上手会有问题的。因此给出了以下的详细实验步骤。
我们实验实现的目标为:
一些基本概念建议先看一下另一篇博客文章DBID,SID,DB name,DB global name的区别。
首先从数据库层面搜集信息:
SQL> select name from v$database;
NAME
---------
DB0903
SQL> select instance from v$thread;
INSTANCE
--------------------------------------------------------------------------------
DB09031
DB09032
SQL> show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string sub12092311540.training.oracle
vcn.com
SQL> SELECT * FROM global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DB0903.SUB12092311540.TRAINING.ORACLEVCN.COM
SQL> show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DB0903_yny1vf
OS层面搜集信息:
[oracle@dbim1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 01:47:07
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 03-SEP-2021 00:40:47
Uptime 0 days 1 hr. 6 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dbim1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.92)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.205)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DB0903XDB.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09031", status READY, has 1 handler(s) for this service...
Service "DB0903_yny1vf.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09031", status READY, has 1 handler(s) for this service...
Service "cb0df43e8eb11c01e0535c00000a5707.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09031", status READY, has 1 handler(s) for this service...
Service "orclpdb1.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09031", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbim1 ~]$ srvctl config database
DB0903_yny1vf
[oracle@dbim1 ~]$ srvctl config database -d DB0903_yny1vf -v
Database unique name: DB0903_yny1vf
Database name: DB0903
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DB0903_YNY1VF/PARAMETERFILE/spfile.269.1082250399
Password file: +DATA/DB0903_YNY1VF/PASSWORD/pwddb0903_yny1vf.259.1082249633
Domain: sub12092311540.training.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dbaoper
Database instances: DB09031,DB09032
Configured nodes: dbim1,dbim2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@dbim1 ~]$ srvctl status database -d DB0903_yny1vf -v
Instance DB09031 is running on node dbim1. Instance status: Open.
Instance DB09032 is running on node dbim2. Instance status: Open.
$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwASM
Backup of Password file: +DATA/orapwASM_backup
ASM listener: LISTENER
ASM instance count: ALL
Cluster ASM listener: ASMNET1LSNR_ASM
[oracle@dbim2 ~]$ srvctl status diskgroup -diskgroup DATA
Disk Group DATA is running on dbim1,dbim2
[oracle@dbim2 ~]$ srvctl status diskgroup -diskgroup RECO
Disk Group RECO is running on dbim1,dbim2
在以上输出中,srvctl config database -d DB0903_yny1vf -v
的输出尤为重要,需要妥善保存。后续需要再次运行此命令并比对其输出。
保存一份pfile以便后续用来启动数据库:
SQL> create pfile from spfile='+DATA/DB0903_YNY1VF/PARAMETERFILE/spfile.269.1082250399';
File created.
在集群中任一节点执行以下命令,停止和删除:
export DBNAME=DB0903_yny1vf
srvctl stop database -d $DBNAME
srvctl remove database -d $DBNAME
在另一节点监控到的状态:
[oracle@dbim2 ~]$ srvctl status database -d $DBNAME
Instance is being stopped on node dbim1
Instance is being stopped on node dbim2
[oracle@dbim2 ~]$ srvctl status database -d $DBNAME
Instance DB09031 is not running on node dbim1
Instance DB09032 is not running on node dbim2
[oracle@dbim2 ~]$ srvctl status database -d $DBNAME
PRCD-1120 : The resource for database DB0903_yny1vf could not be found.
PRCR-1001 : Resource ora.db0903_yny1vf.db does not exist
此时监听发生了变化,之前DB_UNIQUE_NAME关联的服务没有了:
[oracle@dbim1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 02:53:04
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 03-SEP-2021 00:40:47
Uptime 0 days 2 hr. 12 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dbim1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.92)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.205)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully
启动数据库,将DB_UNIQUE_NAME修改为DB0903_test:
SQL> startup
ORACLE instance started.
Total System Global Area 1.4496E+10 bytes
Fixed Size 9151688 bytes
Variable Size 2080374784 bytes
Database Buffers 1.2382E+10 bytes
Redo Buffers 24399872 bytes
Database mounted.
Database opened.
SQL> alter system set db_unique_name=DB0903_test scope=spfile sid='*';
System altered.
SQL> alter database rename global_name to DB0903_test.SUB12092311540.TRAINING.ORACLEVCN.COM;
SQL> SELECT * FROM global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DB0903.SUB12092311540.TRAINING.ORACLEVCN.COM
SQL> alter database rename global_name to DB0903_test.SUB12092311540.TRAINING.ORACLEVCN.COM;
Database altered.
SQL> SELECT * FROM global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DB0903_TEST.SUB12092311540.TRAINING.ORACLEVCN.COM
然后把数据库加回来,这里的很多参数实际上参考了之前srvctl config database命令的输出::
srvctl add database -db DB0903_test -dbname DB0903 -diskgroup 'RECO,DATA' -dbtype RAC -o /u01/app/oracle/product/19.0.0.0/dbhome_1 -domain sub12092311540.training.oraclevcn.com \
-spfile '+DATA/DB0903_YNY1VF/PARAMETERFILE/spfile.269.1082250399' -pwfile '+DATA/DB0903_YNY1VF/PASSWORD/pwddb0903_yny1vf.259.1082249633'
srvctl add instance -d DB0903_test -i DB09031 -n dbim1
srvctl add instance -d DB0903_test -i DB09032 -n dbim2
最终保证以下的命令输出出DB_UNIQUE_NAME外,应与之前的输出一致:
$ srvctl config database -d DB0903_test
Database unique name: DB0903_test
Database name: DB0903
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DB0903_YNY1VF/PARAMETERFILE/spfile.269.1082250399
Password file: +DATA/DB0903_YNY1VF/PASSWORD/pwddb0903_yny1vf.259.1082249633
Domain: sub12092311540.training.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dbaoper
Database instances: DB09031,DB09032
Configured nodes: dbim1,dbim2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
新的lsnrctl信息:
[oracle@dbim2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 04:00:47
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 03-SEP-2021 00:41:04
Uptime 0 days 3 hr. 19 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dbim2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.83)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.78)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX2", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "DB0903XDB.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09032", status READY, has 1 handler(s) for this service...
Service "DB0903_TEST.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09032", status READY, has 1 handler(s) for this service...
Service "cb0df43e8eb11c01e0535c00000a5707.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09032", status READY, has 1 handler(s) for this service...
Service "orclpdb1.sub12092311540.training.oraclevcn.com" has 1 instance(s).
Instance "DB09032", status READY, has 1 handler(s) for this service...
The command completed successfully
确认:
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DB0903_TEST
SQL> SELECT * FROM global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DB0903_TEST.SUB12092311540.TRAINING.ORACLEVCN.COM
[oracle@dbim1 ~]$ srvctl stop database -d $DBNAME
[oracle@dbim1 ~]$ srvctl start database -d $DBNAME
[oracle@dbim1 ~]$ srvctl status database -d $DBNAME
Instance DB09031 is running on node dbim1
Instance DB09032 is running on node dbim2
最后,如果之前为数据库创建了服务,也必须用srvctl命令将其加回来。