Oracle MAA: Oracle 19c RAC + ADG
Revision V1.0
| No. | Date | Author/Modifier | Comments |
|---|---|---|---|
| 1.0 | 2020-03-06 | 谈权 | 初稿：搭建 Oracle MAA: Oracle 19c RAC + Active Data Gurad |
Table of Contents
0. 「主库 RAC」 与 「备库」 环境
「主库 RAC」 与 「备库」 环境：
| Primary | Standby | |
|---|---|---|
| OS Version | CentOS Linux release 7.7.1908 (Core) | CentOS Linux release 7.7.1908 (Core) |
| DB Version | Version 19.6.0.0.0 | Version 19.6.0.0.0 |
| HOST IP | # Public (enp0s8) 192.168.6.21 tqdb21 192.168.6.22 tqdb22 # Private (enp0s9) # Virtual (enp0s8) | # ADG 192.168.6.10 tq1 |
| SCAN IP | # SCAN 192.168.6.20 tqdb-cluster tqdb-cluster-scan | |
| DB_NAME | tqdb | tqdb |
| DB_UNIQUE_NAME | tqdb | tqdb_adg |
| Instance_Name | tqdb1 tqdb2 | tqdb_adg |
| ArchiveFile | +DATA/archivelog | +DATA/archivelog |
| DB Storage | ASM | ASM |
| 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。
接下来，我们分别进行 switchover 和 failover 角色（来回）切换。
-- The End --