作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle MAA: Oracle 19c RAC + ADG

Revision V1.0

No.DateAuthor/ModifierComments
1.02020-03-06谈权初稿:搭建 Oracle MAA: Oracle 19c RAC + Active Data Gurad

[TOC]

0. 「主库 RAC」 与 「备库」 环境

Oracle 19c Data Guard Structure

「主库 RAC」 与 「备库」 环境:

PrimaryStandby
OS VersionCentOS Linux release 7.7.1908 (Core)CentOS Linux release 7.7.1908 (Core)
DB VersionVersion 19.6.0.0.0Version 19.6.0.0.0
HOST IP# Public (enp0s8)
192.168.6.21 tqdb21
192.168.6.22 tqdb22

# Private (enp0s9)
172.16.8.21 tqdb21-priv
172.16.8.22 tqdb22-priv

# Virtual (enp0s8)
192.168.6.23 tqdb21-vip
192.168.6.24 tqdb22-vip

# ADG
192.168.6.10 tq1
SCAN IP# SCAN
192.168.6.20 tqdb-cluster tqdb-cluster-scan
DB_NAMEtqdbtqdb
DB_UNIQUE_NAMEtqdbtqdb_adg
Instance_Nametqdb1
tqdb2
tqdb_adg
ArchiveFile+DATA/archivelog+DATA/archivelog
DB StorageASMASM
ASM for DB files+DATA/TQDB/DATAFILE+DATA/TQDB/DATAFILE
ASM for LOG files+DATA/TQDB/ONLINELOG+DATA/TQDB/ONLINELOG
ASM for TEMP files+DATA/TQDB/TEMPFILE+DATA/TQDB/TEMPFILE
ORACLE_HOME/u01/app/oracle/product/19c/dbhome/u01/app/oracle/product/19c/dbhome
grid 用户 ORACLE_BASE/u01/app/grid/u01/app/grid
grid 用户 ORACLE_HOME/u01/app/19c/grid/u01/app/19c/grid
oracle 用户 ORACLE_BASE/u01/app/oracle/u01/app/oracle
oracle 用户 ORACLE_HOME/u01/app/oracle/product/19c/dbhome/u01/app/oracle/product/19c/dbhome

0.1 停止tq1服务器上原来的 Oracle Restart 单实例

-- 1. 关闭原数据库实例 `tq1` 的自动启动
[root@tq1: ~]# srvctl disable database -db tq1
[root@tq1: ~]# 
-- `sqlplus > shutdown immediate;` 停止数据库实例
[oracle@tq1: ~]$ sqlplus / as sysdba
sys@TQ1(tq1)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


-- 2. 备份实例`tq1` 的环境变量
[oracle@tq1: ~]$ cp ~/.bash_profile ~/.bash_profile.bak_tq1

-- 3. 修改 oracle 用户的 `.bash_profile` 环境变量
-- DB_UNIQUE_NAME=tqdb_adg
-- ORACLE_SID=tqdb_adg
[oracle@tq1: ~]$ vim ~/.bash_profile
​```修改为: `tqdb_adg`
export ORACLE_SID=tqdb_adg
export DB_UNIQUE_NAME=tqdb_adg
​```

-- 4. 生效环境变量
[oracle@tq1: ~]$ . ~/.bash_profile
[oracle@tq1: ~]$ 
[oracle@tq1: ~]$ echo $ORACLE_SID
tqdb_adg
[oracle@tq1: ~]$ echo $DB_UNIQUE_NAME
tqdb_adg
[oracle@tq1: ~]$ 



操作记录:

-- 1. 关闭原数据库实例 `tq1` 的自动启动
[root@tq1: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       tq1                      STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       tq1                      STABLE
ora.asm
               ONLINE  ONLINE       tq1                      Started,STABLE
ora.ons
               OFFLINE OFFLINE      tq1                      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.tq1.db
      1        ONLINE  ONLINE       tq1                      Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             home,STABLE
--------------------------------------------------------------------------------
[root@tq1: ~]# 
[root@tq1: ~]# srvctl disable database -db tq1
[root@tq1: ~]# 
[root@tq1: ~]# srvctl status database -db tq1
Database is running.
[root@tq1: ~]# 
[root@tq1: ~]# 
[root@tq1: ~]# reboot

Last login: Sat Mar  7 00:19:24 2020 from 192.168.6.9
[root@tq1: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       tq1                      STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       tq1                      STABLE
ora.asm
               ONLINE  ONLINE       tq1                      Started,STABLE
ora.ons
               OFFLINE OFFLINE      tq1                      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.tq1.db
      1        ONLINE  OFFLINE                               STABLE
--------------------------------------------------------------------------------
[root@tq1: ~]# 

`srvctl disable database -db tq1` 后, 无法使用 `srvctl` 命令启停数据库。 
需要使用 `sqlplus > startup/shutdown` 数据库实例。
即使使用 `sqlplus > startup` 启动数据库实例,使用 `crsctl stat res -t` 查看 `ora.tq1.db` 的 `State` 依然是 `OFFLINE` 状态。
[root@tq1: ~]# srvctl start database -db tq1
PRCR-1079 : Failed to start resource ora.tq1.db
CRS-2501: Resource 'ora.tq1.db' is disabled
[root@tq1: ~]# srvctl stop database -db tq1   
PRCC-1016 : tq1 was already stopped
[root@tq1: ~]# 

-- 2. 备份实例`tq1` 的环境变量
[oracle@tq1: ~]$ cp ~/.bash_profile ~/.bash_profile.bak_tq1

-- 3. 修改 oracle 用户的 `.bash_profile` 环境变量
-- DB_UNIQUE_NAME=tqdb_adg
-- ORACLE_SID=tqdb_adg
[oracle@tq1: ~]$ vim ~/.bash_profile
​```修改为: `tqdb_adg`
export ORACLE_SID=tqdb_adg
export DB_UNIQUE_NAME=tqdb_adg


-- 4. 生效环境变量
[oracle@tq1: ~]$ . ~/.bash_profile
[oracle@tq1: ~]$ 
[oracle@tq1: ~]$ echo $ORACLE_SID
tqdb_adg
[oracle@tq1: ~]$ echo $DB_UNIQUE_NAME
tqdb_adg
[oracle@tq1: ~]$ 

1. 「主库 RAC」: 开启归档日志

-- 1. 停止两个节点的数据库实例,两个节点都到 mount 状态

-- 节点1:

oracle$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;

-- 节点2:

oracle$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;

-- 2. 设置归档目录(归档目录选为共享存储):
-- 在一个节点(节点1)执行,即可。

SQL> alter system set log_archive_dest_1='location=+DATA/ARCHIVELOG' scope=both;

-- 3. 确定都启动到 mount 状态后,开启归档:
-- 在一个节点(节点1)执行,即可。

SQL> alter database archivelog;

-- 4. 查看归档:

SQL> archive log list;

-- 5. 打开数据库(两个节点都要操作):

SQL> alter database open;

关于归档日志

  • 原则上Redo不设置镜像,因为存储已经设置镜像保护,且双份REDO对存储压力更大,另外重要系统都会部署data guard灾备。

  • 对于A+、A、B级别系统,为降低数据丢失,设置归档日志每小时备份一次。对于RPO接近于0的系统,可以通过灾备技术实现。

  • 归档目录初始设置为数据库大小的一定比例或者数值,100G以下50G, 100G-1TB 20%,1TB以上15%,递增单位为50G

  • 随着数据的增加,需要确保归档目录满足两天的归档量(以最近一月高峰为参考),初始配置不足的可以增加。

  • 归档非常重要,一旦长时间备份失败,造成数据库归档目录满,数据库就会直接停止工作。所以要加强归档日志的监控。

  • 在NBU中设置自动删除,对于data guard数据库,保留3小时归档日志,其他可以直接删除;

  • 每个库使用独立的归档目录。

当一组联机重做日志写满时,LGWR进程将开始写下一组日志文件。这被称为日志切换。此时,会产生检查(校验)点操作,还有一些信息被写到控制文件中。除了在重做日志自动切换和自动产生的检查点之外,Oracle数据库dba还可能根据管理和维护的需要,在任何时候强制性的进行重做日志切换,也可以强制性的产生校验点。

强制性产生重做日志文件切换的命令为:

alter system switch logfile 强制性产生校验点,不一定就归档当前的重做日志文件,(若自动归档打开,就归档前的重做日志,若自动归档没有打开,就不归档当前重做日志。)

alter system checkpoint

alter system archive log current 是归档当前的重做日志文件,不管自动归档有没有打都归档。
主要的区别在于:
ALTER SYSTEM SWITCH LOGFILE 对单实例数据库或RAC中的当前实例执行日志切换;
ALTER SYSTEM ARCHIVE LOG CURRENT 会对数据库中的所有实例执行日志切换。

为什么执行热备后要执行 alter system archive log current 这个语句,看到很多脚本都是这样写的。是不是必须的?

一般的RMAN脚本都是这样写的,因为RMAN是可以备份归档日志的。

alter system archive log current 这样后就可以将所有的归档都备份出来了。这样做是为了保证数据的完整和一致。

2. 「主库 RAC」: 开启 force logging

-- 1. 停止两个节点的数据库实例
# srvctl stop database -db tqdb
# crsctl stat res -t

-- 2. 两个节点都到 mount 状态,
-- 在一个节点执行数据库开启 force logging 即可
-- 节点1
SQL> startup mount;
-- 节点2
SQL> startup mount;
-- 节点1
SQL> alter database force logging;

-- 3. 查看 节点1 和 节点2 ,已经开启 `force logging`
SQL> select DBID, INST_ID, NAME, OPEN_MODE, DATABASE_ROLE, FORCE_LOGGING, FLASHBACK_ON from gv$database;

      DBID    INST_ID NAME       OPEN_MODE            DATABASE_ROLE    FORCE_LOGGING   FLASHBACK_ON
---------- ---------- ---------- -------------------- ---------------- --------------- ------------------
3966209240          1 TQDB       MOUNTED              PRIMARY          YES             NO
3966209240          2 TQDB       MOUNTED              PRIMARY          YES             NO

-- 4. 两个节点都, 开启数据库
-- 节点1
SQL> alter database open;
SQL> select force_logging from v$database;
-- 节点2
SQL> alter database open;
SQL> select force_logging from v$database;

操作记录:

-- 1. 停止两个节点的数据库实例
-- 节点1 
[root@tqdb21: ~]# srvctl stop database -db tqdb
[root@tqdb21: ~]# 
[root@tqdb21: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.chad
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.net1.network
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.ons
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.tqdb21.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb22.vip
      1        ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
[root@tqdb21: ~]# 

-- 2. 两个节点都到 mount 状态,
-- 在一个节点执行数据库开启 force logging 即可
-- 节点1 到 mount 状态
[oracle@tqdb21: ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 01:38:23 2020
Version 19.6.0.0.0

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

Connected to an idle instance.

01:38:26 idle> 
01:39:25 idle> startup mount;
ORACLE instance started.

Total System Global Area  822080768 bytes
Fixed Size                  8901888 bytes
Variable Size             390070272 bytes
Database Buffers          419430400 bytes
Redo Buffers                3678208 bytes
Database mounted.
01:39:48 idle> 
01:39:56 idle> conn / as sysdba
Connected.
01:40:00 idle(tqdb21)> 

-- 节点2 到 mount 状态
[oracle@tqdb22: ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 01:38:36 2020
Version 19.6.0.0.0

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



Connected to an idle instance.

01:38:37 idle> 01:38:37 idle> 
01:38:37 idle> 
01:38:37 idle> 
01:40:14 idle> startup mount;
ORACLE instance started.

Total System Global Area  822080768 bytes
Fixed Size                  8901888 bytes
Variable Size             356515840 bytes
Database Buffers          452984832 bytes
Redo Buffers                3678208 bytes
Database mounted.
01:40:39 idle> 
01:42:29 idle> 
01:42:29 idle> conn / as sysdba
Connected.
01:44:57 idle(tqdb22)> 


-- 节点1 数据库开启 force logging
-- 在一个节点执行数据库开启 force logging 即可
01:45:47 idle(tqdb21)> 
01:45:47 idle(tqdb21)> alter database force logging;

Database altered.

01:46:03 idle(tqdb21)> 

-- 3. 查看 节点1 和 节点2 ,已经开启 `force logging`
-- 节点1
01:57:40 idle(tqdb21)> select DBID, INST_ID, NAME, OPEN_MODE, DATABASE_ROLE, FORCE_LOGGING, FLASHBACK_ON from gv$database;

      DBID    INST_ID NAME       OPEN_MODE            DATABASE_ROLE    FORCE_LOGGING   FLASHBACK_ON
---------- ---------- ---------- -------------------- ---------------- --------------- ------------------
3966209240          1 TQDB       MOUNTED              PRIMARY          YES             NO
3966209240          2 TQDB       MOUNTED              PRIMARY          YES             NO

01:57:41 idle(tqdb21)> 

-- 节点2
01:59:19 idle(tqdb22)> select DBID, INST_ID, NAME, OPEN_MODE, DATABASE_ROLE, FORCE_LOGGING, FLASHBACK_ON from gv$database;

      DBID    INST_ID NAME       OPEN_MODE            DATABASE_ROLE    FORCE_LOGGING   FLASHBACK_ON
---------- ---------- ---------- -------------------- ---------------- --------------- ------------------
3966209240          1 TQDB       MOUNTED              PRIMARY          YES             NO
3966209240          2 TQDB       MOUNTED              PRIMARY          YES             NO

01:59:21 idle(tqdb22)> 


-- 4. 两个节点都, 开启数据库
-- 节点1
02:05:02 idle(tqdb21)> alter database open;

Database altered.

02:05:09 idle(tqdb21)> conn / as sysdba
Connected.
02:05:28 sys@TQDB(tqdb21)> 
02:05:36 sys@TQDB(tqdb21)> col name for a10;
02:05:49 sys@TQDB(tqdb21)> COL FORCE_LOGGING FOR A15;
02:05:55 sys@TQDB(tqdb21)> set lines 200
02:06:02 sys@TQDB(tqdb21)> select DBID, INST_ID, NAME, OPEN_MODE, DATABASE_ROLE, FORCE_LOGGING, FLASHBACK_ON from gv$database;

      DBID    INST_ID NAME       OPEN_MODE            DATABASE_ROLE    FORCE_LOGGING   FLASHBACK_ON
---------- ---------- ---------- -------------------- ---------------- --------------- ------------------
3966209240          2 TQDB       MOUNTED              PRIMARY          YES             NO
3966209240          1 TQDB       READ WRITE           PRIMARY          YES             NO

02:06:04 sys@TQDB(tqdb21)> 

-- 节点2
02:09:21 idle(tqdb22)> alter database open;

Database altered.

02:09:33 idle(tqdb22)> conn / as sysdba
Connected.
02:09:39 sys@TQDB(tqdb22)> col name for a10;
02:09:45 sys@TQDB(tqdb22)> COL FORCE_LOGGING FOR A15;
02:09:51 sys@TQDB(tqdb22)> set lines 200
02:09:58 sys@TQDB(tqdb22)> select DBID, INST_ID, NAME, OPEN_MODE, DATABASE_ROLE, FORCE_LOGGING, FLASHBACK_ON from gv$database;

      DBID    INST_ID NAME       OPEN_MODE            DATABASE_ROLE    FORCE_LOGGING   FLASHBACK_ON
---------- ---------- ---------- -------------------- ---------------- --------------- ------------------
3966209240          2 TQDB       READ WRITE           PRIMARY          YES             NO
3966209240          1 TQDB       READ WRITE           PRIMARY          YES             NO

02:10:05 sys@TQDB(tqdb22)> 

3. 「主库 RAC」: 修改主库适应dataguard环境参数

当 「主库 RAC」 已开启force logging之后再进行如下操作

-- 1. 「主库 RAC」执行, 修改主库适应dataguard环境参数
alter system set db_unique_name='tqdb' scope=spfile sid='*';

alter system set log_archive_config='DG_CONFIG=(tqdb,tqdb_adg)' scope=both sid='*';
alter system set log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=tqdb_adg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb_adg' scope=spfile sid='*';
alter system set log_archive_dest_state_1='enable' scope=both sid='*';
alter system set log_archive_dest_state_2='enable' scope=both sid='*';

alter system set fal_server='tqdb_adg' scope=spfile sid='*';
alter system set fal_client='tqdb' scope=spfile sid='*';

alter system set standby_file_management=AUTO scope=both sid='*';


​```
-- 当目标文件位置不同时(例如:主库使用 ASM,备库 使用本地文件系统),需要定义 `DB files` 、`TEMP files` 和 `LOG files` 的转换规则:
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/datafile','+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/orcl/tempfile' scope=spfile sid='*';  
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*';
​```

-- 2. 重启数据库
-- 停止两个节点的数据库实例
# srvctl stop database -db tqdb
# crsctl stat res -t
-- 启动两个节点的数据库实例
# srvctl start database -db tqdb

操作记录:

-- 1. 「主库 RAC」执行, 修改主库适应dataguard环境参数
-- 节点1
02:35:46 sys@TQDB(tqdb21)> 
02:35:47 sys@TQDB(tqdb21)> alter system set db_unique_name='tqdb' scope=spfile sid='*';

System altered.

02:35:51 sys@TQDB(tqdb21)> alter system set log_archive_config='DG_CONFIG=(tqdb,tqdb_adg)' scope=both sid='*';

System altered.

02:36:07 sys@TQDB(tqdb21)> alter system set log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb' scope=spfile sid='*';

System altered.

02:36:25 sys@TQDB(tqdb21)> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';

System altered.

02:36:40 sys@TQDB(tqdb21)> alter system set log_archive_dest_2='SERVICE=tqdb_adg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb_adg' scope=spfile sid='*';

System altered.

02:36:54 sys@TQDB(tqdb21)> alter system set log_archive_dest_state_1='enable' scope=both sid='*';

System altered.

02:37:03 sys@TQDB(tqdb21)> alter system set log_archive_dest_state_2='enable' scope=both sid='*';

System altered.

02:37:09 sys@TQDB(tqdb21)> alter system set fal_server='tqdb_adg' scope=spfile sid='*';

System altered.

02:37:20 sys@TQDB(tqdb21)> alter system set fal_client='tqdb' scope=spfile sid='*';

System altered.

02:37:28 sys@TQDB(tqdb21)> alter system set standby_file_management=AUTO scope=both sid='*';

System altered.

02:37:37 sys@TQDB(tqdb21)> 
02:37:39 sys@TQDB(tqdb21)> 


-- 2. 重启数据库
-- 停止两个节点的数据库实例
-- 节点1
[root@tqdb21: ~]# srvctl stop database -db tqdb
[root@tqdb21: ~]# 
[root@tqdb21: ~]# # crsctl stat res -t
[root@tqdb21: ~]# crsctl stat res -t  
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.chad
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.net1.network
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.ons
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.tqdb21.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb22.vip
      1        ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
[root@tqdb21: ~]# 

-- 启动两个节点的数据库实例
-- 节点1
[root@tqdb21: ~]# srvctl start database -db tqdb
[root@tqdb21: ~]# 
[root@tqdb21: ~]# crsctl stat res -t            
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.chad
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.net1.network
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.ons
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb.db
      1        ONLINE  ONLINE       tqdb21                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             home,STABLE
      2        ONLINE  ONLINE       tqdb22                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             home,STABLE
ora.tqdb21.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb22.vip
      1        ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
[root@tqdb21: ~]# 


-- 查看修改的参数,已经在两个节点生效
-- 节点1
02:48:41 sys@TQDB(tqdb21)> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(tqdb,tqdb_adg)
02:51:21 sys@TQDB(tqdb21)> 
02:51:34 sys@TQDB(tqdb21)> 
02:51:34 sys@TQDB(tqdb21)> show parameter fal_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      tqdb
fal_server                           string      tqdb_adg
02:51:35 sys@TQDB(tqdb21)> 
02:51:53 sys@TQDB(tqdb21)> 
02:51:53 sys@TQDB(tqdb21)> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
02:51:54 sys@TQDB(tqdb21)>  
02:52:12 sys@TQDB(tqdb21)> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+DATA/archivelog VALI
                                                 D_FOR=(ALL_LOGFILES,ALL_ROLES)
                                                  DB_UNIQUE_NAME=tqdb

02:52:57 sys@TQDB(tqdb21)> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=tqdb_adg ASYNC LGWR VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=tqdb_a
                                                 dg

02:53:03 sys@TQDB(tqdb21)> show parameter log_archive_dest_state_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1             string      enable

02:53:23 sys@TQDB(tqdb21)> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      enable

02:53:26 sys@TQDB(tqdb21)> 
02:54:27 sys@TQDB(tqdb21)> show parameter log_archive_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.arc
02:54:31 sys@TQDB(tqdb21)> 


-- 节点2
02:50:18 sys@TQDB(tqdb22)> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(tqdb,tqdb_adg)
02:50:32 sys@TQDB(tqdb22)> show parameter fal_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      tqdb
fal_server                           string      tqdb_adg
02:50:49 sys@TQDB(tqdb22)> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
02:51:07 sys@TQDB(tqdb22)> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+DATA/archivelog VALI
                                                 D_FOR=(ALL_LOGFILES,ALL_ROLES)
                                                  DB_UNIQUE_NAME=tqdb

02:53:46 sys@TQDB(tqdb22)> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=tqdb_adg ASYNC LGWR VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=tqdb_a
                                                 dg

02:53:49 sys@TQDB(tqdb22)> show parameter log_archive_dest_state_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1             string      enable

02:54:00 sys@TQDB(tqdb22)> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      enable

02:54:03 sys@TQDB(tqdb22)> show parameter log_archive_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.arc
02:54:23 sys@TQDB(tqdb22)> 

-- 查看归档目录 `grid$ asmcmd -p`
-- 节点1
​```
-- 切换 redo log, 查看日志编号 `SEQUENCE#`
02:58:02 sys@TQDB(tqdb21)> alter system archive log current;

System altered.

02:58:47 sys@TQDB(tqdb21)>
02:59:08 sys@TQDB(tqdb21)> set lines 200
02:59:11 sys@TQDB(tqdb21)> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME               CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ----------
         1          1         43  209715200        512          1 YES ACTIVE                3467655 2020-03-07 02:46:24      3475193 2020-03-07 02:58:46          0
         2          1         44  209715200        512          1 NO  CURRENT               3475193 2020-03-07 02:58:46   9.2954E+18                              0
         3          2         39  209715200        512          1 NO  CURRENT               3475198 2020-03-07 02:58:47   9.2954E+18                              0
         4          2         38  209715200        512          1 YES ACTIVE                3467666 2020-03-07 02:46:24      3475198 2020-03-07 02:58:47          0

02:59:12 sys@TQDB(tqdb21)> alter system checkpoint;

System altered.

02:59:25 sys@TQDB(tqdb21)> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME               CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ----------
         1          1         43  209715200        512          1 YES INACTIVE              3467655 2020-03-07 02:46:24      3475193 2020-03-07 02:58:46          0
         2          1         44  209715200        512          1 NO  CURRENT               3475193 2020-03-07 02:58:46   9.2954E+18                              0
         3          2         39  209715200        512          1 NO  CURRENT               3475198 2020-03-07 02:58:47   9.2954E+18                              0
         4          2         38  209715200        512          1 YES INACTIVE              3467666 2020-03-07 02:46:24      3475198 2020-03-07 02:58:47          0

02:59:28 sys@TQDB(tqdb21)> 

-- `grid$ asmcmd -p`
ASMCMD [+DATA/archivelog] > ls -l *.arc
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  UNPROT  COARSE   MAR 07 02:00:00  N    1_42_1032338008.arc => +DATA/TQDB/ARCHIVELOG/2020_03_07/thread_1_seq_42.338.1034390785
ARCHIVELOG  UNPROT  COARSE   MAR 07 02:00:00  N    1_43_1032338008.arc => +DATA/TQDB/ARCHIVELOG/2020_03_07/thread_1_seq_43.340.1034391527
ARCHIVELOG  UNPROT  COARSE   MAR 07 02:00:00  N    2_37_1032338008.arc => +DATA/TQDB/ARCHIVELOG/2020_03_07/thread_2_seq_37.339.1034390785
ARCHIVELOG  UNPROT  COARSE   MAR 07 02:00:00  N    2_38_1032338008.arc => +DATA/TQDB/ARCHIVELOG/2020_03_07/thread_2_seq_38.341.1034391527
ASMCMD [+DATA/archivelog] > 
​```

4. 「主库 RAC」: 主库增加standby log日志组

-- 1. 查看 redo log 信息(在一个节点执行即可)

SQL> select * from v$log;

-- 2. 主库增加standby logfile日志组:

主库是2组logfile,standby logfile需要至少多一组,每个thread多一组

alter database add standby logfile thread 1 group 5 '+data' size 200m;
alter database add standby logfile thread 1 group 6 '+data' size 200m;
alter database add standby logfile thread 1 group 7 '+data' size 200m;
alter database add standby logfile thread 2 group 8 '+data' size 200m;
alter database add standby logfile thread 2 group 9 '+data' size 200m;
alter database add standby logfile thread 2 group 10 '+data' size 200m;

执行记录:

-- 1. 查看 redo log 信息(在一个节点执行即可)
-- 节点1
03:11:18 sys@TQDB(tqdb21)> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME               CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ----------
         1          1         43  209715200        512          1 YES INACTIVE              3467655 2020-03-07 02:46:24      3475193 2020-03-07 02:58:46          0
         2          1         44  209715200        512          1 NO  CURRENT               3475193 2020-03-07 02:58:46   9.2954E+18                              0
         3          2         39  209715200        512          1 NO  CURRENT               3475198 2020-03-07 02:58:47   9.2954E+18                              0
         4          2         38  209715200        512          1 YES INACTIVE              3467666 2020-03-07 02:46:24      3475198 2020-03-07 02:58:47          0

03:11:25 sys@TQDB(tqdb21)> 

-- 2. 主库增加`standby logfile`日志组:
-- 节点1
03:13:05 sys@TQDB(tqdb21)> alter database add standby logfile thread 1 group 5 '+data' size 200m;

Database altered.

03:13:13 sys@TQDB(tqdb21)> alter database add standby logfile thread 1 group 6 '+data' size 200m;

Database altered.

03:13:20 sys@TQDB(tqdb21)> alter database add standby logfile thread 1 group 7 '+data' size 200m;

Database altered.

03:13:34 sys@TQDB(tqdb21)> alter database add standby logfile thread 2 group 8 '+data' size 200m;

Database altered.

03:13:43 sys@TQDB(tqdb21)> alter database add standby logfile thread 2 group 9 '+data' size 200m;

Database altered.

03:13:51 sys@TQDB(tqdb21)> alter database add standby logfile thread 2 group 10 '+data' size 200m;

Database altered.

03:13:58 sys@TQDB(tqdb21)> 

-- 
03:18:32 sys@TQDB(tqdb21)> select * from v$logfile;

    GROUP# STATUS          TYPE    MEMBER                                                       IS_     CON_ID
---------- --------------- ------- ------------------------------------------------------------ --- ----------
         1                 ONLINE  +DATA/TQDB/ONLINELOG/group_1.259.1032338013                  NO           0
         2                 ONLINE  +DATA/TQDB/ONLINELOG/group_2.260.1032338013                  NO           0
         3                 ONLINE  +DATA/TQDB/ONLINELOG/group_3.267.1032339499                  NO           0
         4                 ONLINE  +DATA/TQDB/ONLINELOG/group_4.268.1032339499                  NO           0
         5                 STANDBY +DATA/TQDB/ONLINELOG/group_5.342.1034392393                  NO           0
         6                 STANDBY +DATA/TQDB/ONLINELOG/group_6.343.1034392399                  NO           0
         7                 STANDBY +DATA/TQDB/ONLINELOG/group_7.344.1034392415                  NO           0
         8                 STANDBY +DATA/TQDB/ONLINELOG/group_8.345.1034392423                  NO           0
         9                 STANDBY +DATA/TQDB/ONLINELOG/group_9.346.1034392431                  NO           0
        10                 STANDBY +DATA/TQDB/ONLINELOG/group_10.347.1034392437                 NO           0

10 rows selected.

03:18:37 sys@TQDB(tqdb21)> 
03:21:18 sys@TQDB(tqdb21)> select * from v$standby_log;

    GROUP# DBID          THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME               CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------- ------------ ------------------- ------------ ------------------- ----------
         5 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                                                                     0
         6 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                                                                     0
         7 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                                                                     0
         8 UNASSIGNED          2          0  209715200        512          0 YES UNASSIGNED                                                                                                     0
         9 UNASSIGNED          2          0  209715200        512          0 YES UNASSIGNED                                                                                                     0
        10 UNASSIGNED          2          0  209715200        512          0 YES UNASSIGNED                                                                                                     0

6 rows selected.

03:21:19 sys@TQDB(tqdb21)>

-- `grid$ asmcmd -p`
ASMCMD [+DATA/archivelog] > ls -l +DATA/TQDB/ONLINELOG/
Type       Redund  Striped  Time             Sys  Name
ONLINELOG  UNPROT  COARSE   MAR 07 02:00:00  Y    group_1.259.1032338013
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_10.347.1034392437
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_2.260.1032338013
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_3.267.1032339499
ONLINELOG  UNPROT  COARSE   MAR 07 02:00:00  Y    group_4.268.1032339499
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_5.342.1034392393
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_6.343.1034392399
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_7.344.1034392415
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_8.345.1034392423
ONLINELOG  UNPROT  COARSE   MAR 07 03:00:00  Y    group_9.346.1034392431
ASMCMD [+DATA/archivelog] > 

5. 「主库 RAC」和「备库」: 主库备库增加tnsnames别名

-- 1. 「主库 RAC」 tnsnamrs.ora:

-- 两个节点都要执行

-- 两个节点已经自带了 `TQDB` 的别名
TQDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb)
    )
  )


-- 只需添加别名 `tqdb_adg`
tqdb_adg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb_adg) 
    )
  )

  

-- 2. 备库 tnsnames.ora:

-- 备库(tq1)添加两个别名 `tqdb` 和 `tqdb_adg`
tqdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )



tqdb_adg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb_adg) 
    )
  )

操作记录:

-- 1. 「主库 RAC」 tnsnamrs.ora:
-- 两个节点都要执行
-- 节点1
[oracle@tqdb21: ~]$ cd $ORACLE_HOME/network/admin
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ ll
total 8
drwxr-xr-x 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r-- 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
-rw-r----- 1 oracle oinstall  331 Feb 14 08:57 tnsnames.ora
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ vim tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TQDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb)
    )
  )


# 只需添加别名 `tqdb_adg`
tqdb_adg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb_adg)
    )
  )


[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:35:57

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb)))
OK (0 msec)
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb_adg

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:37:49

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg)))
OK (20 msec)
[oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ 


-- 节点2 
[oracle@tqdb22: ~]$ cd $ORACLE_HOME/network/admin
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ ll
total 8
drwxr-xr-x 2 oracle oinstall   64 Feb 13 18:52 samples
-rw-r--r-- 1 oracle oinstall 1536 Feb 13 18:52 shrept.lst
-rw-r----- 1 oracle oinstall  331 Feb 14 08:57 tnsnames.ora
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ vim tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TQDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb)
    )
  )

# 只需添加别名 `tqdb_adg`
tqdb_adg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb_adg)
    )
  )

[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:40:08

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb)))
OK (0 msec)
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb_adg

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:40:16

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg)))
OK (10 msec)
[oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ 


-- 2. 备库 tnsnames.ora:
[oracle@tq1: ~]$ cd $ORACLE_HOME/network/admin
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ ll
total 12
drwxr-xr-x 2 oracle oinstall 4096 Apr 17  2019 samples
-rw-r--r-- 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
-rw-r--r-- 1 oracle oinstall  166 Feb  7 01:06 tnsnames.ora
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ cat tnsnames.ora 
TQ1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tq1)
    )
  )

[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ vim tnsnames.ora 
TQ1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tq1)
    )
  )

# 备库(tq1)添加两个别名 `tqdb` 和 `tqdb_adg`
tqdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )



tqdb_adg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tqdb_adg)
    )
  )


[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ 
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:46:01

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
OK (10 msec)
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ tnsping tqdb_adg

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 03:46:05

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg)))
OK (0 msec)
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ 

6.「主库 RAC」-> 「备库」拷贝主库密码文件到备库

-- 1. 「主库 RAC」使用 asmcmd 从ASM中 copy 出密码文件到 OS文件系统目录 /tmp

-- 节点1 操作即可

ASMCMD [+DATA/TQDB/PASSWORD] > pwd
+DATA/TQDB/PASSWORD

ASMCMD [+DATA/TQDB/PASSWORD] > ls -l
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   FEB 14 08:00:00  Y    pwdtqdb.256.1032336929
PASSWORD  UNPROT  COARSE   FEB 14 08:00:00  Y    pwdtqdb.257.1032337993

ASMCMD [+DATA/TQDB/PASSWORD] > cp pwdtqdb.257.1032337993 /tmp
copying +DATA/TQDB/PASSWORD/pwdtqdb.257.1032337993 -> /tmp/pwdtqdb.257.1032337993

-- 2. 将/tmp目录的密码文件拷贝到备库(tqdb_adg)的$ORACLE_HOME/dbs目录

[grid@tqdb21: /tmp]$ ll pwdtqdb.257.1032337993 
-rw-r----- 1 grid oinstall 2048 Mar  7 04:37 pwdtqdb.257.1032337993
[grid@tqdb21: /tmp]$ scp pwdtqdb.257.1032337993 oracle@tq1:/u01/app/oracle/product/19c/dbhome/dbs/
The authenticity of host 'tq1 (192.168.6.10)' can't be established.
ECDSA key fingerprint is SHA256:zSacI7xtzLJVQgn+yoHHru1SMS2F9y5w1jpSPkNIuSI.
ECDSA key fingerprint is MD5:f1:89:3e:c0:bd:2b:ea:8f:7e:9d:b1:cc:bf:05:dd:94.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'tq1,192.168.6.10' (ECDSA) to the list of known hosts.
oracle@tq1's password: 
pwdtqdb.257.1032337993                                                                                                                                               100% 2048   342.6KB/s   00:00    
[grid@tqdb21: /tmp]$ 

-- 3. 对参数文件,密码文件重命名符合备库实例命名规范

[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ll -th
total 24K
-rw-r----- 1 oracle oinstall 2.0K Mar  7 04:48 pwdtqdb.257.1032337993
-rw-rw---- 1 oracle asmadmin 1.6K Mar  7 04:15 hc_tq1.dat
-rw-r--r-- 1 oracle asmadmin  941 Feb  6 17:38 inittq1.ora
-rw-r----- 1 oracle oinstall 2.0K Jan 17 21:43 orapwtq1
-rw-r----- 1 oracle asmadmin   24 Jan 17 21:27 lkTQ1
-rw-r--r-- 1 oracle oinstall 3.1K May 14  2015 init.ora
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ mv pwdtqdb.257.1032337993 orapwtqdb_adg
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ll -th
total 24K
-rw-r----- 1 oracle oinstall 2.0K Mar  7 04:48 orapwtqdb_adg
-rw-rw---- 1 oracle asmadmin 1.6K Mar  7 04:15 hc_tq1.dat
-rw-r--r-- 1 oracle asmadmin  941 Feb  6 17:38 inittq1.ora
-rw-r----- 1 oracle oinstall 2.0K Jan 17 21:43 orapwtq1
-rw-r----- 1 oracle asmadmin   24 Jan 17 21:27 lkTQ1
-rw-r--r-- 1 oracle oinstall 3.1K May 14  2015 init.ora
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ 

操作记录:

-- 1. 「主库 RAC」使用 `asmcmd` 从ASM中 copy 出密码文件到 OS文件系统目录 `/tmp`
-- 节点1 操作即可
[grid@tqdb21: ~]$ asmcmd -p
ASMCMD [+] > ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  NORMAL  N      OCR/
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls -l
Type  Redund  Striped  Time  Sys  Name
                             N    TQDB/
                             N    archivelog/
ASMCMD [+DATA] > cd TQDB
ASMCMD [+DATA/TQDB] > ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    ARCHIVELOG/
                             Y    CONTROLFILE/
                             Y    DATAFILE/
                             Y    ONLINELOG/
                             Y    PARAMETERFILE/
                             Y    PASSWORD/
                             Y    TEMPFILE/
ASMCMD [+DATA/TQDB] > cd PASSWORD
ASMCMD [+DATA/TQDB/PASSWORD] > ls -l
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   FEB 14 08:00:00  Y    pwdtqdb.256.1032336929
PASSWORD  UNPROT  COARSE   FEB 14 08:00:00  Y    pwdtqdb.257.1032337993
ASMCMD [+DATA/TQDB/PASSWORD] > pwd
+DATA/TQDB/PASSWORD
ASMCMD [+DATA/TQDB/PASSWORD] > cp pwdtqdb.257.1032337993 /tmp
copying +DATA/TQDB/PASSWORD/pwdtqdb.257.1032337993 -> /tmp/pwdtqdb.257.1032337993
ASMCMD [+DATA/TQDB/PASSWORD] > quit
[grid@tqdb21: ~]$ 

-- 2. 将`/tmp`目录的密码文件拷贝到备库(tqdb_adg)的`$ORACLE_HOME/dbs`目录
[grid@tqdb21: /tmp]$ ll pwdtqdb.257.1032337993 
-rw-r----- 1 grid oinstall 2048 Mar  7 04:37 pwdtqdb.257.1032337993
[grid@tqdb21: /tmp]$ scp pwdtqdb.257.1032337993 oracle@tq1:/u01/app/oracle/product/19c/dbhome/dbs/
The authenticity of host 'tq1 (192.168.6.10)' can't be established.
ECDSA key fingerprint is SHA256:zSacI7xtzLJVQgn+yoHHru1SMS2F9y5w1jpSPkNIuSI.
ECDSA key fingerprint is MD5:f1:89:3e:c0:bd:2b:ea:8f:7e:9d:b1:cc:bf:05:dd:94.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'tq1,192.168.6.10' (ECDSA) to the list of known hosts.
oracle@tq1's password: 
pwdtqdb.257.1032337993                                                                                                                                               100% 2048   342.6KB/s   00:00    
[grid@tqdb21: /tmp]$ 

-- 3. 对参数文件,密码文件重命名符合备库实例命名规范
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ll -th
total 24K
-rw-r----- 1 oracle oinstall 2.0K Mar  7 04:48 pwdtqdb.257.1032337993
-rw-rw---- 1 oracle asmadmin 1.6K Mar  7 04:15 hc_tq1.dat
-rw-r--r-- 1 oracle asmadmin  941 Feb  6 17:38 inittq1.ora
-rw-r----- 1 oracle oinstall 2.0K Jan 17 21:43 orapwtq1
-rw-r----- 1 oracle asmadmin   24 Jan 17 21:27 lkTQ1
-rw-r--r-- 1 oracle oinstall 3.1K May 14  2015 init.ora
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ mv pwdtqdb.257.1032337993 orapwtqdb_adg
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ll -th
total 24K
-rw-r----- 1 oracle oinstall 2.0K Mar  7 04:48 orapwtqdb_adg
-rw-rw---- 1 oracle asmadmin 1.6K Mar  7 04:15 hc_tq1.dat
-rw-r--r-- 1 oracle asmadmin  941 Feb  6 17:38 inittq1.ora
-rw-r----- 1 oracle oinstall 2.0K Jan 17 21:43 orapwtq1
-rw-r----- 1 oracle asmadmin   24 Jan 17 21:27 lkTQ1
-rw-r--r-- 1 oracle oinstall 3.1K May 14  2015 init.ora
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ 


7. 「备库」: 增加备库静态监听

-- 1. 「备库」: 增加备库静态监听

-- 1. 「备库」: 增加备库静态监听
[grid@tq1: /u01/app/19c/grid/network/admin]$ vim listener.ora 
​```
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 增加备库静态监听
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
      (SID_NAME = tqdb_adg)
    )
  )
​```
[grid@tq1: /u01/app/19c/grid/network/admin]$ 

-- 2. 备库重启监听

-- 2. 备库重启监听
# srvctl stop listener 
# srvctl start listener 
# crsctl stat res -t

grid$ lsnrctl status
grid$ lsnrctl service

操作记录:

-- 1. 「备库」: 增加备库静态监听
[grid@tq1: /u01/app/19c/grid/network/admin]$ vim listener.ora 
#Backup file is  /u01/app/grid/crsdata/tq1/output/listener.ora.bak.tq1.grid line added by Agent
# listener.ora Network Configuration File: /u01/app/19c/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON            # line added by Agent


# 增加备库静态监听
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
      (SID_NAME = tqdb_adg)
    )
  )

[grid@tq1: /u01/app/19c/grid/network/admin]$ 


-- 2. 备库重启监听
[grid@tq1: /u01/app/19c/grid/network/admin]$ crsctl stat res -t    
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       tq1                      STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       tq1                      STABLE
ora.asm
               ONLINE  ONLINE       tq1                      Started,STABLE
ora.ons
               OFFLINE OFFLINE      tq1                      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       tq1                      STABLE
ora.tq1.db
      1        ONLINE  OFFLINE                               STABLE
--------------------------------------------------------------------------------
[grid@tq1: /u01/app/19c/grid/network/admin]$ 
[grid@tq1: /u01/app/19c/grid/network/admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 07:09:03

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tq1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-MAR-2020 07:08:38
Uptime                    0 days 0 hr. 0 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/tq1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tq1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "tqdb_adg" has 1 instance(s).
  Instance "tqdb_adg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[grid@tq1: /u01/app/19c/grid/network/admin]$ lsnrctl service

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2020 07:09:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tq1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "tqdb_adg" has 1 instance(s).
  Instance "tqdb_adg", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
[grid@tq1: /u01/app/19c/grid/network/admin]$ 

8. 「备库」: 备库创建adump目录 和 归档目录 +DATA/archivelog

需要手工在备库 上创建 audit dump 目录。否则,duplicate 时会报错。

-- 1. 查看主库的 `adump` 目录
06:53:48 sys@TQDB(tqdb21)> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/tqdb/adump
06:53:49 sys@TQDB(tqdb21)> 

-- 备库执行,创建`adump`目录 
oracle$ mkdir -p /u01/app/oracle/admin/tqdb/adump/

-- 2. 创建归档目录 `+DATA/archivelog`
grid$ asmcmd -p
ASMCMD [+DATA] > mkdir archivelog
ASMCMD [+DATA/archivelog] > ls -l
ASMCMD [+DATA/archivelog] > 

备注: 由于备库也使用ASM磁盘管理,「数据文件目录 和 归档目录」都在 +DATA 磁盘组,所以不用在操作系统创建相应目录。

如果是操作系统文件管理,就需要再在OS文件系统里创建「数据文件目录 和 归档目录」。例如:

mkdir -p /u01/app/oracle/admin/std/adump/

mkdir -p /u01/arch

mkdir -p /u01/app/oracle/oradata/std
mkdir -p /u01/app/oracle/oradata/std/datafile/
mkdir -p /u01/app/oracle/oradata/std/tempfile/
mkdir -p /u01/app/oracle/oradata/std/onlinelog/

操作记录:

-- 1. 备库执行,创建`adump`目录 
[oracle@tq1: /u01/app/oracle/admin]$ ll
total 4
drwxr-x--- 6 oracle oinstall 4096 Jan 17 21:27 tq1
[oracle@tq1: /u01/app/oracle/admin]$ 
[oracle@tq1: /u01/app/oracle/admin]$ 
[oracle@tq1: /u01/app/oracle/admin]$ mkdir -p /u01/app/oracle/admin/tqdb/adump/
[oracle@tq1: /u01/app/oracle/admin]$ ll
total 8
drwxr-x--- 6 oracle oinstall 4096 Jan 17 21:27 tq1
drwxr-xr-x 3 oracle oinstall 4096 Mar  5 04:54 tqdb
[oracle@tq1: /u01/app/oracle/admin]$ cd tqdb/
[oracle@tq1: /u01/app/oracle/admin/tqdb]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 4096 Mar  5 04:54 adump
[oracle@tq1: /u01/app/oracle/admin/tqdb]$ 

-- 2. 创建归档目录 `+DATA/archivelog`
[grid@tq1: ~]$ asmcmd -p
ASMCMD [+DATA] > mkdir archivelog

9. 「备库」: 修改备库实例pfile文件

-- 「备库」: 修改备库实例pfile文件
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ vim inittqdb_adg.ora
*.audit_file_dest='/u01/app/oracle/admin/tqdb/adump'
*.db_unique_name='tqdb_adg'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(tqdb_adg,tqdb)'
*.log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb_adg'
*.log_archive_dest_2='SERVICE=tqdb ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.standby_file_management='AUTO'
*.fal_server='tqdb'
*.fal_client='tqdb_adg'
*.control_files='+DATA'
*.db_create_file_dest='+DATA'
*.db_name='tqdb'
*.pga_aggregate_target=379M
*.processes=300
*.sga_target=1136M
*.db_block_size=8192
*.compatible="19.0.0"
*.audit_trail="DB"
*.open_cursors=300
*._optimizer_use_auto_indexes="OFF"
[oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ 

10. 「备库」: 使用上面的pfile启动备库到 nomount 状态

-- 「备库」: 使用上面的`pfile`启动备库到 `nomount` 状态
[oracle@tq1: ~]$ echo $ORACLE_SID
tqdb_adg
[oracle@tq1: ~]$ echo $DB_UNIQUE_NAME
tqdb_adg
[oracle@tq1: ~]$ 
[oracle@tq1: ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:15:20 2020
Version 19.6.0.0.0

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

Connected to an idle instance.


08:15:23 idle> startup nomount pfile='/u01/app/oracle/product/19c/dbhome/dbs/inittqdb_adg.ora';
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size                  8895872 bytes
Variable Size             318767104 bytes
Database Buffers          855638016 bytes
Redo Buffers                7880704 bytes
08:16:00 idle> 
08:17:17 idle> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
[oracle@tq1: ~]$ 

11. 「主库 RAC」: 主库准备连接辅助实例

-- 1. 「主库 RAC」查看 RMAN 配置

oracle$ rman target /
RMAN> show all;

-- 2. 备库 oracle 验证登陆

oracle$ sqlplus sys/Oracle123@tqdb as sysdba
oracle$ sqlplus sys/Oracle123@tqdb21:1521/tqdb as sysdba
oracle$ sqlplus sys/Oracle123@tqdb22:1521/tqdb as sysdba 
oracle$ sqlplus sys/Oracle123@tqdb_adg as sysdba
oracle$ sqlplus sys/Oracle123@tq1:1521/tqdb_adg as sysdba 

-- 3.「主库 RAC」: 主库准备连接辅助实例

-- 节点1
[oracle@tqdb21: ~]$ rman target / auxiliary sys/Oracle123@tqdb_adg  
或者
[oracle@tqdb21: ~]$ rman target sys/Oracle123@tqdb auxiliary sys/Oracle123@tqdb_adg

操作记录:

-- 1. 「主库 RAC」查看 RMAN 配置 
[oracle@tqdb21: ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 7 07:59:29 2020
Version 19.6.0.0.0

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

connected to target database: TQDB (DBID=3966209240)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TQDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/dbhome/dbs/snapcf_tqdb1.f'; # default

RMAN> 

-- 2. 备库 oracle 验证登陆
[oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 07:57:16 2020
Version 19.6.0.0.0

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


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

07:57:16 sys@TQDB(tqdb22)> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
quit[oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 07:57:23 2020
Version 19.6.0.0.0

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


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

07:57:23 sys@TQDB(tqdb21)> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
[oracle@tq1: ~]$ 

-- 
[oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb21:1521/tqdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:04:38 2020
Version 19.6.0.0.0

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


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

08:04:38 sys@TQDB(tqdb21)> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
[oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb22:1521/tqdb as sysdba 

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:05:03 2020
Version 19.6.0.0.0

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


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

08:05:03 sys@TQDB(tqdb22)> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@tq1: ~]$ 

-- 
[oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb_adg as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:06:44 2020
Version 19.6.0.0.0

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

Connected to an idle instance.


08:06:47 idle> 
08:06:49 idle> quit
Disconnected
[oracle@tq1: ~]$ sqlplus sys/Oracle123@tq1:1521/tqdb_adg as sysdba       

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:07:14 2020
Version 19.6.0.0.0

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

Connected to an idle instance.


08:07:20 idle> quit
Disconnected
[oracle@tq1: ~]$ 

-- 3.「主库 RAC」: 主库准备连接辅助实例
[oracle@tqdb21: ~]$ rman target sys/Oracle123@tqdb auxiliary sys/Oracle123@tqdb_adg

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 7 08:21:14 2020
Version 19.6.0.0.0

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

connected to target database: TQDB (DBID=3966209240)
connected to auxiliary database: TQDB (not mounted)

RMAN> quit


Recovery Manager complete.
-- 或者
[oracle@tqdb21: ~]$ rman target / auxiliary sys/Oracle123@tqdb_adg                  

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 7 08:22:45 2020
Version 19.6.0.0.0

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

connected to target database: TQDB (DBID=3966209240)
connected to auxiliary database: TQDB (not mounted)

RMAN> 

12. 「主库 RAC」: 使用DUPLICATE开始备库创建

-- 执行 RMAN 脚本
run
{ 
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
allocate AUXILIARY channel c5 type disk;
allocate AUXILIARY channel c6 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

命令中各个项的简要说明如下 :

· FOR STANDBY:这告诉 DUPLICATE 命令将用于备用数据库,因此它不会强制更改 DBID。

· FROM ACTIVE DATABASE:DUPLICATE 将直接从源数据文件创建,无需额外的备份步骤。

· DORECOVER:DUPLICATE 将包括恢复步骤,使待机状态达到当前时间点。

· SPFILE:允许我们在从源服务器复制 spfile 时重置它。

· NOFILENAMECHECK:不检查目标文件位置。

操作记录:

-- 执行 RMAN 脚本
[oracle@tqdb21: ~]$ rman target / auxiliary sys/Oracle123@tqdb_adg

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 7 08:42:25 2020
Version 19.6.0.0.0

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

connected to target database: TQDB (DBID=3966209240)
connected to auxiliary database: TQDB (not mounted)

RMAN> 

RMAN> 

RMAN> run
2> { 
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> allocate AUXILIARY channel c4 type disk;
7> allocate AUXILIARY channel c5 type disk;
8> allocate AUXILIARY channel c6 type disk;
9> DUPLICATE TARGET DATABASE
10> FOR STANDBY
11> FROM ACTIVE DATABASE
12> DORECOVER
13> NOFILENAMECHECK;
14> release channel c1;
15> release channel c2;
16> release channel c3;
17> release channel c4;
18> release channel c5;
19> release channel c6;
20> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=32 instance=tqdb1 device type=DISK

allocated channel: c2
channel c2: SID=29 instance=tqdb1 device type=DISK

allocated channel: c3
channel c3: SID=34 instance=tqdb1 device type=DISK

allocated channel: c4
channel c4: SID=45 device type=DISK

allocated channel: c5
channel c5: SID=46 device type=DISK

allocated channel: c6
channel c6: SID=47 device type=DISK

Starting Duplicate Db at 2020-03-07 08:42:40
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb_adg'   ;
}
executing Memory Script

Starting backup at 2020-03-07 08:42:42
Finished backup at 2020-03-07 08:42:43
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/TQDB_ADG/CONTROLFILE/current.275.1034412163';
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files = 
  ''+DATA/TQDB_ADG/CONTROLFILE/current.275.1034412163'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Starting backup at 2020-03-07 08:42:44
channel c1: starting datafile copy
copying standby control file
output file name=+DATA/TQDB_ADG/CONTROLFILE/current.275.1034412163 tag=TAG20200307T084244
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2020-03-07 08:42:48

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1191181696 bytes

Fixed Size                     8895872 bytes
Variable Size                318767104 bytes
Database Buffers             855638016 bytes
Redo Buffers                   7880704 bytes
allocated channel: c4
channel c4: SID=40 device type=DISK
allocated channel: c5
channel c5: SID=41 device type=DISK
allocated channel: c6
channel c6: SID=46 device type=DISK

sql statement: alter system set  control_files =   ''+DATA/TQDB_ADG/CONTROLFILE/current.275.1034412163'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1191181696 bytes

Fixed Size                     8895872 bytes
Variable Size                318767104 bytes
Database Buffers             855638016 bytes
Redo Buffers                   7880704 bytes
allocated channel: c4
channel c4: SID=40 device type=DISK
allocated channel: c5
channel c5: SID=41 device type=DISK
allocated channel: c6
channel c6: SID=43 device type=DISK

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

sql statement: alter database mount standby database
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/system.270.1034411401 for datafile 1 with checkpoint SCN of 3556906
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401 for datafile 2 with checkpoint SCN of 3556891
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401 for datafile 3 with checkpoint SCN of 3556925
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437 for datafile 4 with checkpoint SCN of 3557114
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/users.274.1034411447 for datafile 5 with checkpoint SCN of 3557139
Using previous duplicated file +DATA/TQDB_ADG/DATAFILE/tq.273.1034411445 for datafile 6 with checkpoint SCN of 3557132

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+DATA/TQDB_ADG/DATAFILE/system.270.1034411401";
   set newname for datafile  2 to 
 "+DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401";
   set newname for datafile  3 to 
 "+DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401";
   set newname for datafile  4 to 
 "+DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437";
   set newname for datafile  5 to 
 "+DATA/TQDB_ADG/DATAFILE/users.274.1034411447";
   set newname for datafile  6 to 
 "+DATA/TQDB_ADG/DATAFILE/tq.273.1034411445";
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 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

sql statement: alter system archive log current
current log archived
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/1_45_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/2_40_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/1_46_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/2_41_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/1_47_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/2_42_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/2_43_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/1_48_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/1_49_1032338008.arc conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (archivelog) file name +DATA/archivelog/2_44_1032338008.arc conflicts with a file used by the target database

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+DATA/archivelog/1_45_1032338008.arc" auxiliary format 
 "+DATA/archivelog/1_45_1032338008.arc"   archivelog like 
 "+DATA/archivelog/2_40_1032338008.arc" auxiliary format 
 "+DATA/archivelog/2_40_1032338008.arc"   archivelog like 
 "+DATA/archivelog/1_46_1032338008.arc" auxiliary format 
 "+DATA/archivelog/1_46_1032338008.arc"   archivelog like 
 "+DATA/archivelog/2_41_1032338008.arc" auxiliary format 
 "+DATA/archivelog/2_41_1032338008.arc"   archivelog like 
 "+DATA/archivelog/1_47_1032338008.arc" auxiliary format 
 "+DATA/archivelog/1_47_1032338008.arc"   archivelog like 
 "+DATA/archivelog/2_42_1032338008.arc" auxiliary format 
 "+DATA/archivelog/2_42_1032338008.arc"   archivelog like 
 "+DATA/archivelog/2_43_1032338008.arc" auxiliary format 
 "+DATA/archivelog/2_43_1032338008.arc"   archivelog like 
 "+DATA/archivelog/1_48_1032338008.arc" auxiliary format 
 "+DATA/archivelog/1_48_1032338008.arc"   archivelog like 
 "+DATA/archivelog/1_49_1032338008.arc" auxiliary format 
 "+DATA/archivelog/1_49_1032338008.arc"   archivelog like 
 "+DATA/archivelog/2_44_1032338008.arc" auxiliary format 
 "+DATA/archivelog/2_44_1032338008.arc"   ;
   catalog clone archivelog  "+DATA/archivelog/1_45_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/2_40_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/1_46_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/2_41_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/1_47_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/2_42_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/2_43_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/1_48_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/1_49_1032338008.arc";
   catalog clone archivelog  "+DATA/archivelog/2_44_1032338008.arc";
   catalog clone datafilecopy  "+DATA/TQDB_ADG/DATAFILE/system.270.1034411401", 
 "+DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401", 
 "+DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401", 
 "+DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437", 
 "+DATA/TQDB_ADG/DATAFILE/users.274.1034411447", 
 "+DATA/TQDB_ADG/DATAFILE/tq.273.1034411445";
   switch clone datafile  1 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/system.270.1034411401";
   switch clone datafile  2 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401";
   switch clone datafile  3 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401";
   switch clone datafile  4 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437";
   switch clone datafile  5 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/users.274.1034411447";
   switch clone datafile  6 to datafilecopy 
 "+DATA/TQDB_ADG/DATAFILE/tq.273.1034411445";
}
executing Memory Script

Starting backup at 2020-03-07 08:44:03
channel c1: starting archived log copy
input archived log thread=1 sequence=47 RECID=78 STAMP=1034412160
channel c2: starting archived log copy
input archived log thread=2 sequence=42 RECID=79 STAMP=1034412161
channel c3: starting archived log copy
input archived log thread=1 sequence=45 RECID=74 STAMP=1034411473
output file name=+DATA/archivelog/1_47_1032338008.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
channel c1: starting archived log copy
input archived log thread=2 sequence=40 RECID=75 STAMP=1034411474
output file name=+DATA/archivelog/2_42_1032338008.arc RECID=0 STAMP=0
channel c2: archived log copy complete, elapsed time: 00:00:01
channel c2: starting archived log copy
input archived log thread=2 sequence=43 RECID=80 STAMP=1034412236
output file name=+DATA/archivelog/1_45_1032338008.arc RECID=0 STAMP=0
channel c3: archived log copy complete, elapsed time: 00:00:01
channel c3: starting archived log copy
input archived log thread=1 sequence=48 RECID=81 STAMP=1034412238
output file name=+DATA/archivelog/2_40_1032338008.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:02
channel c1: starting archived log copy
input archived log thread=1 sequence=46 RECID=76 STAMP=1034411479
output file name=+DATA/archivelog/2_43_1032338008.arc RECID=0 STAMP=0
channel c2: archived log copy complete, elapsed time: 00:00:02
channel c2: starting archived log copy
input archived log thread=2 sequence=41 RECID=77 STAMP=1034411480
output file name=+DATA/archivelog/1_48_1032338008.arc RECID=0 STAMP=0
channel c3: archived log copy complete, elapsed time: 00:00:02
channel c3: starting archived log copy
input archived log thread=2 sequence=44 RECID=83 STAMP=1034412242
output file name=+DATA/archivelog/1_46_1032338008.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
channel c1: starting archived log copy
input archived log thread=1 sequence=49 RECID=82 STAMP=1034412241
output file name=+DATA/archivelog/2_41_1032338008.arc RECID=0 STAMP=0
channel c2: archived log copy complete, elapsed time: 00:00:01
output file name=+DATA/archivelog/2_44_1032338008.arc RECID=0 STAMP=0
channel c3: archived log copy complete, elapsed time: 00:00:01
output file name=+DATA/archivelog/1_49_1032338008.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2020-03-07 08:44:08

cataloged archived log
archived log file name=+DATA/archivelog/1_45_1032338008.arc RECID=1 STAMP=1034412248

cataloged archived log
archived log file name=+DATA/archivelog/2_40_1032338008.arc RECID=2 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/1_46_1032338008.arc RECID=3 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/2_41_1032338008.arc RECID=4 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/1_47_1032338008.arc RECID=5 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/2_42_1032338008.arc RECID=6 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/2_43_1032338008.arc RECID=7 STAMP=1034412249

cataloged archived log
archived log file name=+DATA/archivelog/1_48_1032338008.arc RECID=8 STAMP=1034412250

cataloged archived log
archived log file name=+DATA/archivelog/1_49_1032338008.arc RECID=9 STAMP=1034412250

cataloged archived log
archived log file name=+DATA/archivelog/2_44_1032338008.arc RECID=10 STAMP=1034412250

cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/system.270.1034411401 RECID=1 STAMP=1034412250
cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401 RECID=3 STAMP=1034412250
cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401 RECID=2 STAMP=1034412250
cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437 RECID=4 STAMP=1034412250
cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/users.274.1034411447 RECID=5 STAMP=1034412250
cataloged datafile copy
datafile copy file name=+DATA/TQDB_ADG/DATAFILE/tq.273.1034411445 RECID=6 STAMP=1034412250

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/system.270.1034411401

datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/sysaux.269.1034411401

datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/undotbs1.271.1034411401

datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/undotbs2.272.1034411437

datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/users.274.1034411447

datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=1034412250 file name=+DATA/TQDB_ADG/DATAFILE/tq.273.1034411445

contents of Memory Script:
{
   set until scn  3559659;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2020-03-07 08:44:11

starting media recovery

archived log for thread 1 with sequence 45 is already on disk as file +DATA/archivelog/1_45_1032338008.arc
archived log for thread 1 with sequence 46 is already on disk as file +DATA/archivelog/1_46_1032338008.arc
archived log for thread 1 with sequence 47 is already on disk as file +DATA/archivelog/1_47_1032338008.arc
archived log for thread 1 with sequence 48 is already on disk as file +DATA/archivelog/1_48_1032338008.arc
archived log for thread 1 with sequence 49 is already on disk as file +DATA/archivelog/1_49_1032338008.arc
archived log for thread 2 with sequence 40 is already on disk as file +DATA/archivelog/2_40_1032338008.arc
archived log for thread 2 with sequence 41 is already on disk as file +DATA/archivelog/2_41_1032338008.arc
archived log for thread 2 with sequence 42 is already on disk as file +DATA/archivelog/2_42_1032338008.arc
archived log for thread 2 with sequence 43 is already on disk as file +DATA/archivelog/2_43_1032338008.arc
archived log for thread 2 with sequence 44 is already on disk as file +DATA/archivelog/2_44_1032338008.arc
archived log file name=+DATA/archivelog/1_45_1032338008.arc thread=1 sequence=45
archived log file name=+DATA/archivelog/2_40_1032338008.arc thread=2 sequence=40
archived log file name=+DATA/archivelog/1_46_1032338008.arc thread=1 sequence=46
archived log file name=+DATA/archivelog/2_41_1032338008.arc thread=2 sequence=41
archived log file name=+DATA/archivelog/1_47_1032338008.arc thread=1 sequence=47
archived log file name=+DATA/archivelog/2_42_1032338008.arc thread=2 sequence=42
archived log file name=+DATA/archivelog/1_48_1032338008.arc thread=1 sequence=48
archived log file name=+DATA/archivelog/2_43_1032338008.arc thread=2 sequence=43
archived log file name=+DATA/archivelog/2_44_1032338008.arc thread=2 sequence=44
archived log file name=+DATA/archivelog/1_49_1032338008.arc thread=1 sequence=49
media recovery complete, elapsed time: 00:00:01
Finished recover at 2020-03-07 08:44:13

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

deleted archived log
archived log file name=+DATA/archivelog/1_45_1032338008.arc RECID=1 STAMP=1034412248
deleted archived log
archived log file name=+DATA/archivelog/1_46_1032338008.arc RECID=3 STAMP=1034412249
deleted archived log
archived log file name=+DATA/archivelog/1_47_1032338008.arc RECID=5 STAMP=1034412249
deleted archived log
archived log file name=+DATA/archivelog/1_48_1032338008.arc RECID=8 STAMP=1034412250
deleted archived log
archived log file name=+DATA/archivelog/1_49_1032338008.arc RECID=9 STAMP=1034412250
deleted archived log
archived log file name=+DATA/archivelog/2_40_1032338008.arc RECID=2 STAMP=1034412249
deleted archived log
archived log file name=+DATA/archivelog/2_41_1032338008.arc RECID=4 STAMP=1034412249
deleted archived log
archived log file name=+DATA/archivelog/2_42_1032338008.arc RECID=6 STAMP=1034412249
Deleted 3 objects

deleted archived log
archived log file name=+DATA/archivelog/2_43_1032338008.arc RECID=7 STAMP=1034412249
Deleted 3 objects

deleted archived log
archived log file name=+DATA/archivelog/2_44_1032338008.arc RECID=10 STAMP=1034412250
Deleted 4 objects

Finished Duplicate Db at 2020-03-07 08:44:19

released channel: c1

released channel: c2

released channel: c3

released channel: c4

released channel: c5

released channel: c6

RMAN> 

RMAN> quit


Recovery Manager complete.
[oracle@tqdb21: ~]$ 


13. 「备库」: 检查备库开启MRP

操作记录:

-- 「备库」: 检查备库开启MRP
[oracle@tq1: ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 08:53:10 2020
Version 19.6.0.0.0

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


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

08:53:10 idle(tq1)> select open_mode from v$database;

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

08:53:24 idle(tq1)> 
08:53:32 idle(tq1)> alter database open;

Database altered.

08:53:38 idle(tq1)> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

08:53:51 idle(tq1)> conn / as sysdba
Connected.
08:53:55 sys@TQDB(tq1)> 
08:54:10 sys@TQDB(tq1)> alter database recover managed standby database disconnect;

Database altered.

08:54:48 sys@TQDB(tq1)> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

08:55:07 sys@TQDB(tq1)> 


14. 测试 ADG

-- 主库创建一张表 `tq.copy_dba_objects`
09:15:49 sys@TQDB(tqdb21)> create table tq.copy_dba_objects as select * from dba_objects;

Table created.

09:16:59 sys@TQDB(tqdb21)> 

09:17:16 sys@TQDB(tqdb21)> conn tq/tq
Connected.
09:17:23 tq@TQDB(tqdb21)> select count(*) from copy_dba_objects;

  COUNT(*)
----------
     23579

09:17:25 tq@TQDB(tqdb21)> 

-- 备库查询验证数据
[oracle@tq1: ~]$ sqlplus tq/tq

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 09:22:47 2020
Version 19.6.0.0.0

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

Last Successful login time: Sat Mar 07 2020 09:17:51 +08:00

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


09:22:50 tq@TQDB> select count(*) from copy_dba_objects;

  COUNT(*)
----------
     23579

09:22:54 tq@TQDB> 


15. ADG 常用命令

-- 停 MRP (managed recovery) 
alter database recover managed standby database cancel;

-- 在备库启动 recover 过程,应用主库传过来的日志(默认已经是real-time apply模式,因此省略using current logfile);
ALTER DATABASE RECOVER managed standby database disconnect from session;

--
alter database recover managed standby database using current logfile disconnect from session;

-- 修改保护模式为最大可用性
alter database set standby database to maximize availability;

-- 查询 Oracle ADG 保护模式
select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

-- 查询v$dataguard_process 视图,验证来自主库传输过来的日志过程和备库应用日志的情况(v$dataguard_process视图在12.2版本出现,取代了v$managed_standby);
select role,thread#,sequence#,action from v$dataguard_process;

-- 查询 v$archived_log 视图,验证来自主库传输过来的日志变化情况,下面输出可以看到主库传输过来的日志在增加
select NAME, DEST_ID, THREAD#, SEQUENCE#, ARCHIVED, APPLIED,DELETED,STATUS,COMPRESSED from v$archived_log;
-- 
select THREAD#, SEQUENCE#, NAME, ARCHIVED, APPLIED, DELETED, STATUS from v$archived_log order by 1, 2;

至此,我们已经完成搭建 Oracle MAA: Oracle 19c RAC + Active Data Gurad。

接下来,我们分别进行 switchoverfailover 角色(来回)切换。

-- The End --