部署oracle多租户19c rac to 19c rac adg

Leo 2024-01-28

789

文档课题：部署oracle多租户19c rac to 19c rac adg.
1、架构情况
资源配置：cpu 2c/mem 6g
主备硬件配置：
3个2g投票磁盘asm-diskb/c/d
3个15g数据文件磁盘asm-diske/f/g
1个20g的快速恢复区磁盘asm-diskg

项目	Primary db	physical standby db
db类型	rac	rac
db version	19.13	19.13
db存储	ASM	ASM
主机IP地址/hosts配置	192.168.133.161 hisdb1	192.168.133.181 hisdbdg1
	192.168.133.162 hisdb2	192.168.133.182 hisdbdg2
	192.168.78.161 hisdb1-priv	192.168.78.181 hisdbdg1-priv
	192.168.78.162 hisdb2-priv	192.168.78.182 hisdbdg2-priv
	192.168.133.173 hisdb1-vip	192.168.133.183 hisdbdg1-vip
	192.168.133.174 hisdb2-vip	192.168.133.184 hisdbdg2-vip
	192.168.133.175 hisdb-scan	192.168.133.185 hisdbdg-scan
OS版本	rhel 7.9 64位	rhel 7.9 64位
OS hostname	hisdb1 hisdb2	hisdbdg1 hisdbdg2
字符集	AMERICAN_AMERICA.AL32UTF8	AMERICAN_AMERICA.AL32UTF8
归档模式	Archive Mode	Archive Mode
oracle_sid	tmis1 tmis2	tmisdg1 tmisdg2
db_name/GLOBAL_DBNAME	tmis	tmis
db_unique_name	tmis	tmisdg
TNS_NAME	TMIS	TMISDG
数据文件所在磁盘组	+DATA	+DATA
归档路径	+FRA	+FRA
ORACLE_HOME	/u01/app/oracle/product/19.13/db_1	/u01/app/oracle/product/19.13/db_1
PDB情况	TMISPDB、ORCLPDB	TMISPDB、ORCLPDB

主库信息
[grid@hisdb1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE ONLINE       hisdb1                   STABLE
               ONLINE ONLINE       hisdb2                   STABLE
ora.chad
               ONLINE ONLINE       hisdb1                   STABLE
               ONLINE ONLINE       hisdb2                   STABLE
ora.net1.network
               ONLINE ONLINE       hisdb1                   STABLE
               ONLINE ONLINE       hisdb2                   STABLE
ora.ons
               ONLINE ONLINE       hisdb1                   STABLE
               ONLINE ONLINE       hisdb2                   STABLE
ora.proxy_advm
               OFFLINE OFFLINE      hisdb1                   STABLE
               OFFLINE OFFLINE      hisdb2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE ONLINE       hisdb1                   STABLE
      2        ONLINE ONLINE       hisdb2                   STABLE
      3        ONLINE OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdb1                   STABLE
      2        ONLINE ONLINE       hisdb2                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdb1                  STABLE
      2        ONLINE ONLINE       hisdb2                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdb1                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdb1                   STABLE
      2        ONLINE ONLINE       hisdb2                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1       ONLINE ONLINE       hisdb1                   Started,STABLE
      2        ONLINE ONLINE       hisdb2                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE ONLINE       hisdb1                   STABLE
      2        ONLINE ONLINE       hisdb2                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE ONLINE       hisdb1                   STABLE
ora.hisdb1.vip
      1        ONLINE ONLINE       hisdb1                   STABLE
ora.hisdb2.vip
      1        ONLINE ONLINE       hisdb2                   STABLE
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.qosmserver
      1        ONLINE ONLINE       hisdb1                   STABLE
ora.scan1.vip
      1        ONLINE ONLINE       hisdb1                   STABLE
ora.tmis.db
      1        ONLINE ONLINE       hisdb1                   Open,HOME=/u01/app/o
                                                             racle/product/19.13/
                                                             db_1,STABLE
      2        ONLINE ONLINE       hisdb2                   Open,HOME=/u01/app/o
                                                             racle/product/19.13/
                                                             db_1,STABLE
--------------------------------------------------------------------------------

说明：当前需给TMIS搭建rac备库.

2、前期准备
2.1、主库准备
2.1.1、归档及强制记录日志
a、force logging + archivelog
b、创建新用户leo用作测试
[oracle@hisdb1 ~]$ export ORACLE_SID=tmis1
[oracle@hisdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 23 12:53:30 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter session set container=tmispdb;

Session altered.

SQL> create user leo identified by leo;

User created.

SQL> grant dba to leo;

Grant succeeded.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING             DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS      READ WRITE           NOARCHIVELOG NO                        PRIMARY          NOT ALLOWED
TMIS      READ WRITE           NOARCHIVELOG NO                        PRIMARY          NOT ALLOWED

SQL> conn / as sysdba
Connected.
SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING             DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS      READ WRITE           NOARCHIVELOG YES                       PRIMARY          NOT ALLOWED
TMIS      READ WRITE           NOARCHIVELOG YES                       PRIMARY          NOT ALLOWED

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           5

SQL> host srvctl stop database -d tmis -o immediate;

SQL> host srvctl status database -d tmis
Instance tmis1 is not running on node hisdb1
Instance tmis2 is not running on node hisdb2

SQL> host srvctl start database -d tmis -o mount

SQL> conn / as sysdba

Connected.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +FRA
Oldest online log sequence     26
Current log sequence           27

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database archivelog;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING             DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS      MOUNTED              ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED
TMIS      MOUNTED              ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     26
Next log sequence to archive   27
Current log sequence           27

SQL> alter database open;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING             DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS      READ WRITE           ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED
TMIS      MOUNTED              ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED
--主库open节点2的tmis实例.
[oracle@hisdb2 ~]$ export ORACLE_SID=tmis2
[oracle@hisdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 23 14:05:13 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter database open;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING             DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS      READ WRITE           ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED
TMIS      READ WRITE           ARCHIVELOG   YES                       PRIMARY          NOT ALLOWED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     3
Next log sequence to archive   4
Current log sequence           4

2.1.2、standby redo log
说明：备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile，主库创建standby logfile旨在主备切换后备用.
创建原则：
a、确保standby redo log大小与主库online redo log的大小一致;
b、若主库为单实例：standby redo log组数=主库日志组总数+1;
c、若主库为rac：standby redo log组数=(每线程的日志组数+1)*线程数;
d、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输.
示例语句，单实例:
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oralg/standby_redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oralg/standby_redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oralg/standby_redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oralg/standby_redo07.log') size 50m;
集群：
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M;

主库节点1实际操作：
SQL> select * from v$standby_log;

no rows selected

SQL> select group#,thread#,members,bytes/1024/1024 as "Size_M" from v$log;

    GROUP#    THREAD#    MEMBERS     Size_M
---------- ---------- ---------- ----------
         1          1          2        200
         2          1          2        200
         3          2          2        200
         4          2          2        200

SQL> col member for a45
SQL> select group#,status,type,member,con_id from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                            CON_ID
---------- ------- ------- --------------------------------------------- ----------
         2         ONLINE +DATA/TMIS/ONLINELOG/group_2.290.1158719585            0
         2         ONLINE +FRA/TMIS/ONLINELOG/group_2.301.1158719585             0
         1         ONLINE +DATA/TMIS/ONLINELOG/group_1.291.1158719585            0
         1         ONLINE +FRA/TMIS/ONLINELOG/group_1.300.1158719585             0
         3         ONLINE +DATA/TMIS/ONLINELOG/group_3.279.1158723299            0
         3         ONLINE +FRA/TMIS/ONLINELOG/group_3.302.1158723301             0
         4         ONLINE +DATA/TMIS/ONLINELOG/group_4.284.1158723301            0
         4         ONLINE +FRA/TMIS/ONLINELOG/group_4.303.1158723301             0

8 rows selected.

SQL> alter database add standby logfile thread 1 group 10 size 200m,group 11 size 200m,group 12 size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 20 size 200m,group 21 size 200m,group 22 size 200m;

Database altered.

SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes/1024/1024,blocksize,used,archived,status from v$standby_log;

    GROUP# DBID               THREAD# SEQUENCE# BYTES/1024/1024 BLOCKSIZE       USED ARC STATUS
---------- --------------- ---------- ---------- --------------- ---------- ---------- --- ----------
        10 UNASSIGNED               1          0             200        512          0 YES UNASSIGNED
        11 UNASSIGNED               1          0             200        512          0 YES UNASSIGNED
        12 UNASSIGNED               1          0             200        512          0 YES UNASSIGNED
        20 UNASSIGNED               2          0             200        512          0 YES UNASSIGNED
        21 UNASSIGNED               2          0             200        512          0 YES UNASSIGNED
        22 UNASSIGNED               2          0             200        512          0 YES UNASSIGNED

6 rows selected.

SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ---------------------------------------------
         2         ONLINE +DATA/TMIS/ONLINELOG/group_2.290.1158719585
         2         ONLINE +FRA/TMIS/ONLINELOG/group_2.301.1158719585
         1         ONLINE +DATA/TMIS/ONLINELOG/group_1.291.1158719585
         1         ONLINE +FRA/TMIS/ONLINELOG/group_1.300.1158719585
         3         ONLINE +DATA/TMIS/ONLINELOG/group_3.279.1158723299
         3         ONLINE +FRA/TMIS/ONLINELOG/group_3.302.1158723301
         4         ONLINE +DATA/TMIS/ONLINELOG/group_4.284.1158723301
         4         ONLINE +FRA/TMIS/ONLINELOG/group_4.303.1158723301
        10         STANDBY +DATA/TMIS/ONLINELOG/group_10.303.1159021149
        10         STANDBY +FRA/TMIS/ONLINELOG/group_10.304.1159021149
        11         STANDBY +DATA/TMIS/ONLINELOG/group_11.304.1159021149

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ---------------------------------------------
        11         STANDBY +FRA/TMIS/ONLINELOG/group_11.305.1159021151
      12         STANDBY +DATA/TMIS/ONLINELOG/group_12.305.1159021151
        12         STANDBY +FRA/TMIS/ONLINELOG/group_12.306.1159021151
        20         STANDBY +DATA/TMIS/ONLINELOG/group_20.306.1159021187
        20         STANDBY +FRA/TMIS/ONLINELOG/group_20.307.1159021187
        21         STANDBY +DATA/TMIS/ONLINELOG/group_21.307.1159021187
        21         STANDBY +FRA/TMIS/ONLINELOG/group_21.308.1159021187
        22         STANDBY +DATA/TMIS/ONLINELOG/group_22.308.1159021189
        22         STANDBY +FRA/TMIS/ONLINELOG/group_22.309.1159021191

20 rows selected.

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      tmis
db_unique_name                       string      tmis
global_names                         boolean     FALSE
instance_name                        string      tmis1
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      tmis

2.1.3、参数文件
使用如下命令修改主库参数(说明：当下主库使用的是spfile启动的实例)
注意：
a、log_archive_config='dg_config=( db_unique_name, db_unique_name)'，主库与备库端采用相同设置
b、log_archive_dest_n='service=……' ，service: 用于指定备库TNSNAMES描述符
c、db_file_name_convert、log_file_name_convert 参数值为转换路径，可直接写db_unique_name.
若使用ASM，可设置为*.db_file_name_convert=('+DATA','+RECOVERY')
d、fal_server、fal_client为TNSNAMES描述符

主库节点1实际操作：
SQL> alter system set log_archive_config='dg_config=(tmis,tmisdg)' sid='*';

System altered.

SQL> alter system set log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmis' sid='*';

System altered.

SQL> alter system set log_archive_dest_2='service=tmisdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmisdg' sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1=enable sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2=enable sid='*';

System altered.

SQL> alter system set standby_file_management='AUTO' sid='*';

System altered.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      *
standby_db_preserve_states           string      NONE
standby_file_management              string      AUTO
standby_pdb_source_file_dblink       string
standby_pdb_source_file_directory    string

说明：当主备切换时，需执行如下语句.
~~alter system set db_file_name_convert='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;~~
alter system set log_file_name_convert='+data/healdg/tempfile/','+data/heal/tempfile/','+data/healdg/onlinelog/','+data/heal/onlinelog/','+data/healdg/controlfile/','+data/heal/controlfile/' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='tmisdg' sid='*';
alter system set fal_client='tmis' sid='*';

--在主库用spfile文件创建pfile文件并传递至备库.
SQL> create pfile='/home/oracle/pfiletmis.ora' from spfile;

File created.
--使用scp命令.
[oracle@hisdb1 ~]$ scp pfiletmis.ora oracle@192.168.133.181:/home/oracle

2.1.4、监听文件
2.1.4.1、主库监听
配置主库和备库的监听文件，整个ADG的redo传输服务都依赖于Oracle Net，因此需要为主备库配置监听，且需要配置静态监听.配置方法可用netmgr、netca，或者直接编辑listener.ora文件.
主库节点1监听文件按如下修改：
[grid@hisdb1 admin]$ cat listener.ora
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

LISTENER =
(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.173)(PORT = 1521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = tmis)
      (ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
      (SID_NAME = tmis1)
)
)

主库节点2监听文件按如下修改：
[grid@hisdb2 admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set

LISTENER =
(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.174)(PORT = 1521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = tmis)
      (ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
      (SID_NAME = tmis2)
   )
)
说明：蓝色高亮为修改部分.

--重启主库监听.
[grid@hisdb1 admin]$ srvctl stop listener -l listener
[grid@hisdb1 admin]$ srvctl start listener -l listener
[grid@hisdb1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 19:16:12

Copyright (c) 1991, 2021, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-JAN-2024 19:12:20
Uptime                    0 days 0 hr. 3 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.13/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/hisdb1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.161)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.173)(PORT=1521)))
Services Summary...
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_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "0f52a0ce51589e1be063a185a8c0fc27" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "0f5def08812e1479e063a185a8c0f65d" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmis" has 2 instance(s).
Instance "tmis1", status UNKNOWN, has 1 handler(s) for this service...
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmisXDB" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmispdb" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
The command completed successfully

注意：rac架构是在grid用户下修改监听文件.

2.1.4.2、备库监听
先搭建rac adg环境，可参照如下博客，此处不再赘述.
https://blog.51cto.com/u_12991611/5376800
注意：
a、备库只安装gi及数据库软件，不安装实例.
b、备库两个节点监听文件配置参考主库.

备库节点1监听文件按如下内容修改：
[grid@hisdbdg1 admin]$ cat listener.ora
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

LISTENER =
(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.183)(PORT = 1521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = tmisdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
      (SID_NAME = tmisdg1)
   )
)

注意：
a、此处global_dbname需为tmisdg.
b、蓝色高亮为修改部分.

备库节点2监听文件按如下内容修改：
[grid@hisdbdg2 admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set

LISTENER =
(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.184)(PORT = 1521))
   )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = tmisdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
      (SID_NAME = tmisdg2)
   )
)
说明：
a、此处global_dbname需为tmisdg.
b、备库节点2参照备库节点1添加，同时注意LISTENER_SCAN1的信息，有时会没有scan listener信息.
c、蓝色高亮为修改部分.

--备库重启监听.
[grid@hisdbdg1 admin]$ srvctl stop listener -l listener
[grid@hisdbdg1 admin]$ srvctl start listener -l listener
[grid@hisdbdg1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 19:30:04

Copyright (c) 1991, 2021, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-JAN-2024 19:27:05
Uptime                    0 days 0 hr. 2 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.13/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/hisdbdg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.181)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.183)(PORT=1521)))
Services Summary...
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_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "tmisdg" has 1 instance(s).
Instance "tmisdg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

注意：ASM信息需1分钟后才会正常显示.

2.1.5、tnsnames.ora
配置主库tnsnames.ora文件.注意：tnsnames.ora文件在oracle用户下，主库两节点均加入如下内容：
TMIS =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tmis)
    )
)

TMISDG =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tmisdg)
    )
)

--将tnsnames.ora传输到备库两节点对应目录.
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.181:/u01/app/oracle/product/19.13/db_1/network/admin
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.182:/u01/app/oracle/product/19.13/db_1/network/admin

2.1.6、密码文件
配置主备库密码文件，要求主备库sys密码相同，此处直接复制主库节点1的密码文件到其它3个节点并修改名称.注意：密码文件名为orapw+ORACLE_SID.
[grid@hisdb1 ~]$ srvctl config database -d tmis
Database unique name: tmis
Database name: tmis
Oracle home: /u01/app/oracle/product/19.13/db_1
Oracle user: oracle
Spfile: +data/spfiletmis.ora
Password file: +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: tmis1,tmis2
Configured nodes: hisdb1,hisdb2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

[grid@hisdb1 ~]$ asmcmd pwcopy +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419 /home/grid/orapwtmis1
copying +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419 -> /home/grid/orapwtmis1
[grid@hisdb1 ~]$ scp orapwtmis1 grid@192.168.133.181:/home/grid

--将密码文件放至备库节点1的dbs目录下.
[root@hisdbdg1 ~]# cp /home/grid/orapwtmis1 /u01/app/oracle/product/19.13/db_1/dbs/
[root@hisdbdg1 ~]# chown oracle:oinstall /u01/app/oracle/product/19.13/db_1/dbs/orapwtmis1
[root@hisdbdg1 ~]# su - oracle
[oracle@hisdbdg1 ~]$ cd /u01/app/oracle/product/19.13/db_1/dbs/
[oracle@hisdbdg1 dbs]$ mv orapwtmis1 orapwtmisdg1
处理备库节点2密码文件.
[oracle@hisdbdg1 dbs]$ scp orapwtmisdg1 oracle@192.168.133.182:/u01/app/oracle/product/19.13/db_1/dbs/
[oracle@hisdbdg2 dbs]$ pwd
/u01/app/oracle/product/19.13/db_1/dbs
[oracle@hisdbdg2 dbs]$ mv orapwtmisdg1 orapwtmisdg2

2.1.7、主机名解析
2.1.7.1、主库解析
--在主库/etc/hosts中添加IP与主机名的对应关系，以便解析.
[root@hisdb1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
#Public IP
192.168.133.161 hisdb1
192.168.133.162 hisdb2

#Private IP
192.168.78.161 hisdb1-priv
192.168.78.162 hisdb2-priv

#Virtual IP
192.168.133.173 hisdb1-vip
192.168.133.174 hisdb2-vip

#Scan IP
192.168.133.175 hisdb-scan

######### ADG IP ############
#Public IP
192.168.133.181 hisdbdg1
192.168.133.182 hisdbdg2

#Private IP
192.168.78.181 hisdbdg1-priv
192.168.78.182 hisdbdg2-priv

#Virtual IP
192.168.133.183 hisdbdg1-vip
192.168.133.184 hisdbdg2-vip

#Scan IP
192.168.133.185 hisdbdg-scan

说明：主库两个节点均在/etc/hosts文件中添加蓝色高亮内容.

2.1.7.2、备库解析
--在/etc/hosts中添加IP与主机名的对应关系，以便解析.
[root@hisdbdg1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
#Public IP
192.168.133.181 hisdbdg1
192.168.133.182 hisdbdg2

#Private IP
192.168.78.181 hisdbdg1-priv
192.168.78.182 hisdbdg2-priv

#Virtual IP
192.168.133.183 hisdbdg1-vip
192.168.133.184 hisdbdg2-vip

#Scan IP
192.168.133.185 hisdbdg-scan

################# MASTER IP ###############
#Public IP
192.168.133.161 hisdb1
192.168.133.162 hisdb2

#Private IP
192.168.78.161 hisdb1-priv
192.168.78.162 hisdb2-priv

#Virtual IP
192.168.133.173 hisdb1-vip
192.168.133.174 hisdb2-vip

#Scan IP
192.168.133.175 hisdb-scan
说明：分别在备库两个节点的/etc/hosts文件中添加蓝色高亮内容.

2.2、备库操作
2.2.1、修改oratab
备库两个节点均在/etc/oratab添加条目.
备库节点1
tmisdg1:/u01/app/oracle/product/19.13/db_1:N
备库节点2
tmisdg2:/u01/app/oracle/product/19.13/db_1:N

2.2.2、修改参数文件
--备库修改主库传递过来的参数文件.
[oracle@hisdbdg1 ~]$ vi pfiletmis.ora
*.audit_file_dest='/u01/app/oracle/admin/tmisdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/TMISDG/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='tmis'
*.db_unique_name='tmisdg'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10000m
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
tmisdg2.instance_number=2
tmisdg1.instance_number=1
*.log_archive_config='dg_config=(tmis,tmisdg)'
*.log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmisdg'
*.log_archive_dest_2='service=tmis lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmis'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1129m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3385m
*.standby_file_management='AUTO'
*.fal_client='TMISDG'
*.fal_server='TMIS'
tmisdg2.thread=2
tmisdg1.thread=1
*.log_file_name_convert='+DATA','+DATA','+FRA','+FRA'
*.db_file_name_convert='+DATA','+DATA'

2.2.3、创建相应目录
[oracle@hisdbdg1 ~]$ mkdir -p /u01/app/oracle/admin/tmisdg/adump
[oracle@hisdbdg2 ~]$ mkdir -p /u01/app/oracle/admin/tmisdg/adump
[oracle@hisdbdg1 ~]$ su - grid
[grid@hisdbdg1 ~]$ asmcmd
ASMCMD> cd data
ASMCMD> ls
ASMCMD> mkdir tmisdg
ASMCMD> cd tmisdg
ASMCMD> mkdir controlfile
ASMCMD> ls
controlfile/

2.2.4、启动到nomount
配置备库pfile文件，并启动到nomount状态，此处选择备库节点1作为实施节点.
[oracle@hisdbdg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:01:52 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/pfiletmis.ora';
ORACLE instance started.

Total System Global Area 3556768424 bytes
Fixed Size                  9140904 bytes
Variable Size             754974720 bytes
Database Buffers         2785017856 bytes
Redo Buffers                7634944 bytes

2.2.5、互通测试
主备库4个节点分别测试.
[oracle@hisdb1 ~]$ tnsping tmis

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JAN-2024 16:21:24

Copyright (c) 1997, 2021, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tmis)))
OK (0 msec)
[oracle@hisdb1 ~]$ tnsping tmisdg

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JAN-2024 16:21:28

Copyright (c) 1997, 2021, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tmisdg)))
OK (0 msec)

说明：此处仅用主库节点1示例.

2.2.6、连接验证
主库验证.
[oracle@hisdb1 ~]$ sqlplus sys/oracle_4U@tmisdg as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:57:03 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      tmisdg
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@hisdb1 ~]$ sqlplus sys/oracle_4U@tmis as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:57:25 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      tmis
备库验证
[oracle@hisdbdg1 trace]$ sqlplus sys/oracle_4U@tmisdg as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:59:26 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> set line 200
SQL> show parameter db_unique_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 tmisdg
[oracle@hisdbdg1 trace]$ sqlplus sys/oracle_4U@tmis as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 17:03:43 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      tmis

2.2.7、活动复制
[oracle@hisdbdg1 trace]$ rman target sys/oracle_4U@192.168.133.161:1521/tmis auxiliary sys/oracle_4U@192.168.133.181:1521/tmisdg

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 24 17:19:42 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: TMIS (DBID=346738717)
connected to auxiliary database: TMIS (not mounted)

RMAN> run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate auxiliary channel a3 device type disk;
allocate auxiliary channel a4 device type disk;
allocate auxiliary channel a5 device type disk;
allocate auxiliary channel a6 device type disk;
allocate auxiliary channel a7 device type disk;
allocate auxiliary channel a8 device type disk;
duplicate target database for standby from active database;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=289 instance=tmis1 device type=DISK

allocated channel: c2
channel c2: SID=53 instance=tmis1 device type=DISK

allocated channel: c3
channel c3: SID=32 instance=tmis1 device type=DISK

allocated channel: c4
channel c4: SID=269 instance=tmis1 device type=DISK

allocated channel: c5
channel c5: SID=68 instance=tmis1 device type=DISK

allocated channel: c6
channel c6: SID=292 instance=tmis1 device type=DISK

allocated channel: c7
channel c7: SID=58 instance=tmis1 device type=DISK

allocated channel: c8
channel c8: SID=310 instance=tmis1 device type=DISK

allocated channel: a1
channel a1: SID=267 instance=tmisdg1 device type=DISK

allocated channel: a2
channel a2: SID=32 instance=tmisdg1 device type=DISK

allocated channel: a3
channel a3: SID=268 instance=tmisdg1 device type=DISK

allocated channel: a4
channel a4: SID=33 instance=tmisdg1 device type=DISK

allocated channel: a5
channel a5: SID=269 instance=tmisdg1 device type=DISK

allocated channel: a6
channel a6: SID=34 instance=tmisdg1 device type=DISK

allocated channel: a7
channel a7: SID=270 instance=tmisdg1 device type=DISK

allocated channel: a8
channel a8: SID=35 instance=tmisdg1 device type=DISK

Starting Duplicate Db at 24-JAN-24

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format '/u01/app/oracle/product/19.13/db_1/dbs/orapwtmisdg1'   ;
}
executing Memory Script

Starting backup at 24-JAN-24
Finished backup at 24-JAN-24

contents of Memory Script:
{
   restore clone from service '192.168.133.161:1521/tmis' standby controlfile;
}
executing Memory Script

Starting restore at 24-JAN-24

channel a1: starting datafile backup set restore
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:04
output file name=+DATA/TMISDG/CONTROLFILE/control01.ctl
Finished restore at 24-JAN-24

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
Using previous duplicated file +DATA/TMISDG/DATAFILE/undotbs1.259.1159118809 for datafile 4 with checkpoint SCN of 4468923
Using previous duplicated file +DATA/TMISDG/DATAFILE/users.262.1159118819 for datafile 7 with checkpoint SCN of 4468986
Using previous duplicated file +DATA/TMISDG/DATAFILE/undotbs2.264.1159118857 for datafile 9 with checkpoint SCN of 4469406
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983 for datafile 12 with checkpoint SCN of 4469737
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165 for datafile 13 with checkpoint SCN of 4470474
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341 for datafile 14 with checkpoint SCN of 4471102

contents of Memory Script:
{
   set newname for tempfile 1 to
"+DATA";
   set newname for tempfile 2 to
"+DATA";
   set newname for tempfile 3 to
"+DATA";
   set newname for tempfile 4 to
"+DATA";
   switch clone tempfile all;
   set newname for datafile 1 to
"+DATA";
   set newname for datafile 3 to
"+DATA";
   set newname for datafile 4 to
"+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809";
   set newname for datafile 5 to
"+DATA";
   set newname for datafile 6 to
"+DATA";
   set newname for datafile 7 to
"+DATA/TMISDG/DATAFILE/users.262.1159118819";
   set newname for datafile 8 to
"+DATA";
   set newname for datafile 9 to
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857";
   set newname for datafile 10 to
"+DATA";
   set newname for datafile 11 to
"+DATA";
   set newname for datafile 12 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983";
   set newname for datafile 13 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165";
   set newname for datafile 14 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
   set newname for datafile 15 to
"+DATA";
   set newname for datafile 16 to
"+DATA";
   set newname for datafile 17 to
"+DATA";
   set newname for datafile 18 to
"+DATA";
   restore
   from nonsparse   from service
'192.168.133.161:1521/tmis'   clone datafile
    1, 3, 5, 6, 8, 10, 11, 15, 16, 17, 18   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
renamed tempfile 4 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-JAN-24

channel a1: starting datafile backup set restore
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to +DATA
channel a2: starting datafile backup set restore
channel a2: using network backup set from service 192.168.133.161:1521/tmis
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00003 to +DATA
channel a3: starting datafile backup set restore
channel a3: using network backup set from service 192.168.133.161:1521/tmis
channel a3: specifying datafile(s) to restore from backup set
channel a3: restoring datafile 00005 to +DATA
channel a4: starting datafile backup set restore
channel a4: using network backup set from service 192.168.133.161:1521/tmis
channel a4: specifying datafile(s) to restore from backup set
channel a4: restoring datafile 00006 to +DATA
channel a5: starting datafile backup set restore
channel a5: using network backup set from service 192.168.133.161:1521/tmis
channel a5: specifying datafile(s) to restore from backup set
channel a5: restoring datafile 00008 to +DATA
channel a6: starting datafile backup set restore
channel a6: using network backup set from service 192.168.133.161:1521/tmis
channel a6: specifying datafile(s) to restore from backup set
channel a6: restoring datafile 00010 to +DATA
channel a7: starting datafile backup set restore
channel a7: using network backup set from service 192.168.133.161:1521/tmis
channel a7: specifying datafile(s) to restore from backup set
channel a7: restoring datafile 00011 to +DATA
channel a8: starting datafile backup set restore
channel a8: using network backup set from service 192.168.133.161:1521/tmis
channel a8: specifying datafile(s) to restore from backup set
channel a8: restoring datafile 00015 to +DATA
channel a3: restore complete, elapsed time: 00:01:02
channel a3: starting datafile backup set restore
channel a3: using network backup set from service 192.168.133.161:1521/tmis
channel a3: specifying datafile(s) to restore from backup set
channel a3: restoring datafile 00016 to +DATA
channel a5: restore complete, elapsed time: 00:01:36
channel a5: starting datafile backup set restore
channel a5: using network backup set from service 192.168.133.161:1521/tmis
channel a5: specifying datafile(s) to restore from backup set
channel a5: restoring datafile 00017 to +DATA
channel a8: restore complete, elapsed time: 00:01:37
channel a8: starting datafile backup set restore
channel a8: using network backup set from service 192.168.133.161:1521/tmis
channel a8: specifying datafile(s) to restore from backup set
channel a8: restoring datafile 00018 to +DATA
channel a1: restore complete, elapsed time: 00:01:41
channel a2: restore complete, elapsed time: 00:01:41
channel a4: restore complete, elapsed time: 00:01:40
channel a6: restore complete, elapsed time: 00:01:39
channel a7: restore complete, elapsed time: 00:01:39
channel a5: restore complete, elapsed time: 00:00:10
channel a8: restore complete, elapsed time: 00:00:09
channel a3: restore complete, elapsed time: 00:00:34
Finished restore at 24-JAN-24

sql statement: alter system archive log current

contents of Memory Script:
{
   restore clone force from service '192.168.133.161:1521/tmis'
           archivelog from scn 4468923;
   catalog clone datafilecopy "+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809",
"+DATA/TMISDG/DATAFILE/users.262.1159118819",
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
   switch clone datafile 4 to datafilecopy
"+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809";
   switch clone datafile 7 to datafilecopy
"+DATA/TMISDG/DATAFILE/users.262.1159118819";
   switch clone datafile 9 to datafilecopy
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857";
   switch clone datafile 12 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983";
   switch clone datafile 13 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165";
   switch clone datafile 14 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
   switch clone datafile all;
}
executing Memory Script

Starting restore at 24-JAN-24

channel a1: starting archived log restore to default destination
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: restoring archived log
archived log thread=1 sequence=33
channel a2: starting archived log restore to default destination
channel a2: using network backup set from service 192.168.133.161:1521/tmis
channel a2: restoring archived log
archived log thread=2 sequence=17
channel a1: restore complete, elapsed time: 00:00:16
channel a2: restore complete, elapsed time: 00:00:15
Finished restore at 24-JAN-24

cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809 RECID=15 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857 RECID=16 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983 RECID=17 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165 RECID=18 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/users.262.1159118819 RECID=19 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341 RECID=20 STAMP=1159125388

datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809

datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/users.262.1159118819

datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857

datafile 12 switched to datafile copy
input datafile copy RECID=17 STAMP=1159125387 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983

datafile 13 switched to datafile copy
input datafile copy RECID=18 STAMP=1159125387 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165

datafile 14 switched to datafile copy
input datafile copy RECID=20 STAMP=1159125388 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341

datafile 1 switched to datafile copy
input datafile copy RECID=21 STAMP=1159125390 file name=+DATA/TMISDG/DATAFILE/system.270.1159125247
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=1159125390 file name=+DATA/TMISDG/DATAFILE/sysaux.258.1159125249
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=1159125390 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/system.265.1159125249
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=1159125390 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/sysaux.271.1159125249
datafile 8 switched to datafile copy
input datafile copy RECID=25 STAMP=1159125391 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/undotbs1.266.1159125249
datafile 10 switched to datafile copy
input datafile copy RECID=26 STAMP=1159125391 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/system.261.1159125249
datafile 11 switched to datafile copy
input datafile copy RECID=27 STAMP=1159125391 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/sysaux.257.1159125251
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/system.272.1159125253
datafile 16 switched to datafile copy
input datafile copy RECID=29 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/sysaux.274.1159125349
datafile 17 switched to datafile copy
input datafile copy RECID=30 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/undotbs1.273.1159125347
datafile 18 switched to datafile copy
input datafile copy RECID=31 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/undo_2.275.1159125349
Finished Duplicate Db at 24-JAN-24
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
released channel: a1
released channel: a2
released channel: a3
released channel: a4
released channel: a5
released channel: a6
released channel: a7
released channel: a8

3、单机转rac
由于备库是rac，需修改spfile到磁盘组，然后才能启动rac dg的第2个节点.
3.1、生成pfile
[oracle@hisdbdg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 23:03:39 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

3.2、修改pfile
--按如下内容修改pfile文件.
[oracle@hisdbdg1 ~]$ cat pfiletmis.ora
*.audit_file_dest='/u01/app/oracle/admin/tmisdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/TMISDG/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='tmis'
*.db_unique_name='tmisdg'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10000m
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
tmisdg2.instance_number=2
tmisdg1.instance_number=1
*.log_archive_config='dg_config=(tmis,tmisdg)'
*.log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmisdg'
*.log_archive_dest_2='service=tmis lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmis'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1129m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3385m
*.standby_file_management='AUTO'
*.fal_client='TMISDG'
*.fal_server='TMIS'
tmisdg2.thread=2
tmisdg1.thread=1
*.log_file_name_convert='+DATA','+DATA','+FRA','+FRA'
*.db_file_name_convert='+DATA','+DATA'
tmisdg1.undo_tablespace='UNDOTBS1'
tmisdg2.undo_tablespace='UNDOTBS2'

注意：蓝色标注内容.

3.3、创建spfile
备库节点1：
--创建spfile文件到磁盘组，并在pfile文件中添加spfile路径.
SQL> create spfile='+data/tmisdg/parameterfile/spfiletmisdg.ora' from pfile='/home/oracle/pfiletmis.ora';

File created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@hisdbdg1 ~]$ echo "SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'" > $ORACLE_HOME/dbs/inittmisdg1.ora
[oracle@hisdbdg1 ~]$ cat $ORACLE_HOME/dbs/inittmisdg1.ora
SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'

在备库节点2的pfile文件中添加spfile路径.
[oracle@hisdbdg1 ~]$ echo "SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'" > $ORACLE_HOME/dbs/inittmisdg2.ora
[oracle@hisdbdg1 ~]$ cat $ORACLE_HOME/dbs/inittmisdg2.ora
SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'

注意：删除原spfile.

3.4、备库重启
启动备库两个节点后验证.
SQL> startup force
ORACLE instance started.

Total System Global Area 3556768424 bytes
Fixed Size                  9140904 bytes
Variable Size             754974720 bytes
Database Buffers         2785017856 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        MOUNTED
         4 ORCLPDB                        MOUNTED
SQL> alter pluggable database TMISPDB open;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB open;

Pluggable database altered.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/tmisdg/parameterfile/spf
                                                 iletmisdg.ora
SQL> set line 9999
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING                          DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --------------------------------------- ---------------- --------------------
TMIS      READ ONLY            ARCHIVELOG   YES                                     PHYSICAL STANDBY RECOVERY NEEDED
--启动节点2后，查询结果如下.
SQL> set line 200
SQL> col force_logging for a15
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FORCE_LOGGING   DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --------------- ---------------- --------------------
TMIS      READ ONLY            ARCHIVELOG   YES             PHYSICAL STANDBY RECOVERY NEEDED
TMIS      READ ONLY            ARCHIVELOG   YES            PHYSICAL STANDBY RECOVERY NEEDED

4、加入crsctl
将备库加入crsctl中，说明：dbca创建的数据库会自动加入crsctl，但通过rman创建的库需手动添加，加入crsctl中后可通过srvctl管理.
4.1、集群状态
--加入crsctl前集群状态.
[grid@hisdbdg1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.chad
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.net1.network
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.ons
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.proxy_advm
               OFFLINE OFFLINE      hisdbdg1                 STABLE
               OFFLINE OFFLINE      hisdbdg2                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        ONLINE OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 Started,STABLE
      2        ONLINE ONLINE       hisdbdg2                 Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.hisdbdg1.vip
      1        ONLINE ONLINE       hisdbdg1                 STABLE
ora.hisdbdg2.vip
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.qosmserver
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.scan1.vip
      1        ONLINE ONLINE       hisdbdg2                 STABLE
--------------------------------------------------------------------------------

4.2、指令说明
[grid@hisdb3 ~]$ srvctl add database -h

Adds a database configuration to the Oracle Clusterware.

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
    -d <db_unique_name>      Unique name for the database
    -o <oracle_home>         ORACLE_HOME path
    -c <type>                Type of database: RAC One Node, RAC, or Single Instance
    -e <server_list>         Candidate server list for RAC One Node database
    -i <inst_name>           Instance name prefix for administrator-managed RAC One Node database (default first 12 characters of <db_unique_name>)
    -w <timeout>             Online relocation timeout in minutes
    -x <node_name>           Node name. -x option is specified for single-instance databases
    -m <domain>              Domain for database. Must be set if database has DB_DOMAIN set.
    -p <spfile>              Server parameter file path
    -r <role>                Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
    -s <start_options>       Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
    -t <stop_options>        Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
    -n <db_name>             Database name (DB_NAME), if different from the unique name given by the -d option
    -y <dbpolicy>            Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
    -g "<serverpool_list>"   Comma separated list of database server pool names
    -a "<diskgroup_list>"    Comma separated list of disk groups
    -j "<acfs_path_list>"    Comma separated list of ACFS paths where database's dependency will be set
-h                       Print usage

4.3、操作过程
4.3.1、主库操作
主库将数据库置于mount状态.
[grid@hisdb1 ~]$ srvctl stop database -d tmis -o immediate
[grid@hisdb1 ~]$ srvctl start database -d tmis -o mount
[grid@hisdb1 ~]$ srvctl status database -d tmis
Instance tmis1 is running on node hisdb1
Instance tmis2 is running on node hisdb2

4.3.2、备库操作
备库节点1操作.
[oracle@hisdbdg1 ~]$ srvctl add database -d tmisdg -c RAC -o /u01/app/oracle/product/19.13/db_1 -p '+data/tmisdg/parameterfile/spfiletmisdg.ora' -r physical_standby -n tmis

注意：以上为一行命令，且需用oracle用户执行，否则报错PRCD-1025、PRKH-1014.

[oracle@hisdbdg1 ~]$ srvctl add instance -d tmisdg -i tmisdg1 -n hisdbdg1
[oracle@hisdbdg1 ~]$ srvctl add instance -d tmisdg -i tmisdg2 -n hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl status database -d tmisdg
Instance tmisdg1 is not running on node hisdbdg1
Instance tmisdg2 is not running on node hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl start database -d tmisdg
[oracle@hisdbdg1 ~]$ srvctl status database -d tmisdg
Instance tmisdg1 is running on node hisdbdg1
Instance tmisdg2 is running on node hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl config database -d tmisdg -a
Database unique name: tmisdg
Database name: tmis
Oracle home: /u01/app/oracle/product/19.13/db_1
Oracle user: oracle
Spfile: +data/tmisdg/parameterfile/spfiletmisdg.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database is enabled
Database is individually enabled on nodes:
Database is individually disabled on nodes:
OSDBA group: dba
OSOPER group: oper
Database instances: tmisdg1,tmisdg2
Configured nodes: hisdbdg1,hisdbdg2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

4.3.3、备库查询
[grid@hisdbdg1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE ONLINE      hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.chad
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.net1.network
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.ons
               ONLINE ONLINE       hisdbdg1                 STABLE
               ONLINE ONLINE       hisdbdg2                 STABLE
ora.proxy_advm
               OFFLINE OFFLINE      hisdbdg1                 STABLE
               OFFLINE OFFLINE      hisdbdg2                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        ONLINE OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE      hisdbdg2                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 Started,STABLE
      2        ONLINE ONLINE       hisdbdg2                 Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE ONLINE       hisdbdg1                 STABLE
      2        ONLINE ONLINE       hisdbdg2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.hisdbdg1.vip
      1        ONLINE ONLINE       hisdbdg1                 STABLE
ora.hisdbdg2.vip
    1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.qosmserver
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.scan1.vip
      1        ONLINE ONLINE       hisdbdg2                 STABLE
ora.tmisdg.db
      1        ONLINE ONLINE       hisdbdg1                 Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19.13/db_1,STABLE
      2        ONLINE ONLINE       hisdbdg2                 Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19.13/db_1,STABLE
--------------------------------------------------------------------------------

SQL> set line 200
SQL> select group#,thread#,sequence#,bytes/1024/1024,blocksize,members,status from v$log

    GROUP#    THREAD# SEQUENCE# BYTES/1024/1024 BLOCKSIZE    MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ---------- ----------------
         1          1          0             200        512          2 CURRENT
         2          1          0             200        512          2 UNUSED
         3          2         0             200        512          2 CURRENT
         4          2          0             200        512          2 UNUSED

SQL> col force_logging for a15
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FORCE_LOGGING   OPEN_MODE            SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --------------- -------------------- --------------------
346738717 TMIS          4499450 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES             READ ONLY            RECOVERY NEEDED
346738717 TMIS          4499450 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES             READ ONLY            RECOVERY NEEDED
SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes,blocksize,used,archived,status from v$standby_log
2 ;

    GROUP# DBID               THREAD# SEQUENCE#      BYTES BLOCKSIZE       USED ARC STATUS
---------- --------------- ---------- ---------- ---------- ---------- ---------- --- ----------
        10 UNASSIGNED               1          0 209715200        512          0 NO UNASSIGNED
        11 346738717                1         37 209715200        512   47114752 YES ACTIVE
        12 UNASSIGNED               1          0 209715200        512          0 YES UNASSIGNED
        20 UNASSIGNED               2          0 209715200        512          0 NO UNASSIGNED
        21 346738717                2         19 209715200        512   88186880 YES ACTIVE
        22 UNASSIGNED               2          0 209715200        512          0 YES UNASSIGNED

6 rows selected.

说明：如上所示，数据库tmisdg已成功加入集群管理.

4.3.4、实时同步
--备库开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> col db_unique_name for a20
SQL> col DB_UNIQUE_NAME for a15
SQL> select inst_id,dbid,name,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

   INST_ID       DBID NAME      DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FORCE_LOGGING   OPEN_MODE            SWITCHOVER_STATUS
---------- ---------- --------- --------------- ----------- -------------------- ---------------- --------------- -------------------- --------------------
         1 346738717 TMIS      tmisdg              4660918 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES             READ ONLY WITH APPLY NOT ALLOWED
         2 346738717 TMIS      tmisdg             4660918 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES             READ ONLY WITH APPLY NOT ALLOWED

SQL> col name for a70
SQL> r
1 SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2    FROM v$archived_log a
3   WHERE a.sequence# >= 12
4     AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5* ORDER BY a.THREAD#, a.sequence#, a.dest_id;

   DEST_ID    THREAD# NAME                                                                    SEQUENCE# ARC APPLIED   NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_33.257.1159125383               33 YES NO             4499502
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_34.269.1159138997               34 YES YES            4601346
         2          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_35.268.1159138987               35 YES YES            4601353
         2          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_36.270.1159138997               36 YES YES            4607887
         1          2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_17.256.1159125383               17 YES YES            4499507
         1          2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_18.271.1159138997               18 YES YES            4601347

6 rows selected.

5、同步验证
5.1、主库启库
将主库两节点从mount开启到open状态，主库节点1切换日志验证.
[oracle@hisdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 25 00:03:17 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter database open;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        READ WRITE NO
         4 ORCLPDB                        READ WRITE NO

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

备库查询：
SQL> select thread#,sequence#,first_time,next_time,completion_time,applied from v$archived_log order by 1,2;

   THREAD# SEQUENCE# FIRST_TIME          NEXT_TIME           COMPLETION_TIME     APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
         1         33 2024-01-24 08:29:15 2024-01-24 19:16:06 2024-01-24 19:16:26 YES
         1         34 2024-01-24 19:16:06 2024-01-24 22:09:27 2024-01-24 23:03:16 YES
         1         35 2024-01-24 22:09:27 2024-01-24 22:09:30 2024-01-24 23:03:16 YES
       1         36 2024-01-24 22:13:02 2024-01-24 22:13:02 2024-01-24 23:03:16 YES
         1         37 2024-01-24 22:13:02 2024-01-25 00:03:32 2024-01-25 00:03:37 YES
         1         38 2024-01-25 00:03:32 2024-01-25 00:07:21 2024-01-25 00:07:22 YES
         1         39 2024-01-25 00:07:21 2024-01-25 00:07:31 2024-01-25 00:07:32 IN-MEMORY
         2         17 2024-01-24 08:31:59 2024-01-24 19:16:06 2024-01-24 19:16:25 YES
         2         18 2024-01-24 19:16:06 2024-01-24 22:09:28 2024-01-24 23:03:16 YES
         2         19 2024-01-24 22:09:28 2024-01-25 00:04:11 2024-01-25 00:04:25 YES

10 rows selected.

5.2、建表测试
--主库建表.
SQL> alter session set container=orclpdb;

Session altered.

SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

--备库端查询日志应用情况.
SQL> alter session set container=tmispdb;

SQL> col username for a15
SQL> select username from all_users where username='LEO';

USERNAME
---------------
LEO

SQL> alter session set container=orclpdb;

Session altered.

SQL> select * from test;

        ID
----------
         1

SQL> SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2    FROM v$archived_log a
3   WHERE a.sequence# >= 12
4     AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5   ORDER BY a.THREAD#, a.sequence#, a.dest_id;

   DEST_ID    THREAD# NAME                                                                    SEQUENCE# ARC APPLIED   NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_33.257.1159125383               33 YES YES            4499502
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_34.269.1159138997               34 YES YES            4601346
         2          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_35.268.1159138987               35 YES YES            4601353
         2          1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_36.270.1159138997               36 YES YES            4607887
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_37.272.1159142617               37 YES YES            4660950
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_38.274.1159142841               38 YES YES            4674183
         1          1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_39.275.1159142853               39 YES YES            4674213
         1          2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_17.256.1159125383               17 YES YES            4499507
         1          2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_18.271.1159138997               18 YES YES            4601347
         1          2 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_2_seq_19.273.1159142663               19 YES YES            4662833

10 rows selected.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
MRP0      APPLYING_LOG
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE

PROCESS   STATUS
--------- ------------
RFS       IDLE
RFS       IDLE

13 rows selected.

备库节点2：
SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED

6 rows selected.

5.3、测试创建pdb
--主库创建pdb.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        READ WRITE NO
        4 ORCLPDB                        READ WRITE NO
SQL> create pluggable database healpdb admin user leo identified by leo role=(connect);

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        READ WRITE NO
         4 ORCLPDB                        READ WRITE NO
         5 HEALPDB                        MOUNTED
SQL> alter pluggable database healpdb open instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        READ WRITE NO
         4 ORCLPDB                        READ WRITE NO
         5 HEALPDB                        READ WRITE NO

--备库查询
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 TMISPDB                        READ ONLY NO
         4 ORCLPDB                        READ ONLY NO
         5 HEALPDB                        MOUNTED
SQL> alter pluggable database healpdb open instances=all;

Pluggable database altered.

结论：备库数据实时同步正常，至此19c 多租户rac到rac的adg搭建完成.

参考文档：https://blog.csdn.net/jycjyc/article/details/104710732

adg 多租户

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

部署oracle多租户19c rac to 19c rac adg

1、架构情况

2、前期准备

2.1、主库准备

2.1.1、归档及强制记录日志

2.1.2、standby redo log

2.1.3、参数文件

2.1.4、监听文件

2.1.4.1、主库监听

2.1.4.2、备库监听

2.1.5、tnsnames.ora

2.1.6、密码文件

2.1.7、主机名解析

2.1.7.1、主库解析

2.1.7.2、备库解析

2.2、备库操作

2.2.1、修改oratab

2.2.2、修改参数文件

2.2.3、创建相应目录

2.2.4、启动到nomount

2.2.5、互通测试

2.2.6、连接验证

2.2.7、活动复制

3、单机转rac

3.1、生成pfile

3.2、修改pfile

3.3、创建spfile

3.4、备库重启

4、加入crsctl

4.1、集群状态

4.2、指令说明

4.3、操作过程

4.3.1、主库操作

4.3.2、备库操作

4.3.3、备库查询

4.3.4、实时同步

5、同步验证

5.1、主库启库

5.2、建表测试

5.3、测试创建pdb

评论