Oracle LiveLabs实验：Oracle Database Hybrid Active Data Guard
概述此实验申请地址在这里，时间为4小时。实验手册在这里。在整个实验中，会创建主备数据库系统，都在OCI上创建。主数据库系统即On-Premises端，备数据库系统即云端，共同组成混合云架构。随后我们会构建主备数据库系统间的ADG。此实验对于手工搭建ADG很有帮助。实验 1: 创建主数据库系统任务1-4：通过Resource Manager创建实例打包的 terraform 主数据库实例创建脚本 d
·
概述
此实验申请地址在这里，时间为4小时。
实验手册在这里。
在整个实验中，会创建主备数据库系统，都在OCI上创建。主数据库系统即On-Premises端，备数据库系统即云端，共同组成混合云架构。随后我们会构建主备数据库系统间的ADG。
此实验对于手工搭建ADG很有帮助。
实验 1: 创建主数据库系统
任务1-4：通过Resource Manager创建实例
打包的 terraform 主数据库实例创建脚本 db19c-primary-num.zip
此脚本会创建VCN和一个Shape为VM.Standard2.1的计算实例，名为primary。VCN的Security List中已添加规则，允许ingress到1521的访问。
创建完成后记录其公网IP地址：XXX.XXX.XXX.XXX
主数据库系统也可以直接用OCI中的数据库模板创建，使用的VCN是公网就行。因为为了简便，最终主备数据库系统是通过公网地址通讯的。
任务5：确认数据库已启动
登录计算实例，查看数据库创建日志：
$ tail -f /u01/ocidb/buildsingle.log
INFO (node:primary): Current Single Instance state (11:16:52)...
oracle 13570 1 0 10:46 ? 00:00:00 /u01/app/oracle/product/19c/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 18631 1 0 11:16 ? 00:00:00 ora_dbw0_ORCL
INFO (node:primary): Single Instance running (see output above)
2022-01-21 11:16:52:[singlestate:Time :primary] Completed successfully in 0 seconds (0h:00m:00s)
2022-01-21 11:16:52:[buildsingle:Done :primary] Building 19c Single Instance
2022-01-21 11:16:52:[buildsingle:Time :primary] Completed successfully in 1856 seconds (0h:30m:56s)
确认数据库和监听均已启动：
ps -ef | grep ORCL
ps -ef | grep tns
确认数据库可以登录：
sudo su - oracle
sqlplus system/Ora_DB4U@localhost:1521/orclpdb
记录数据库名和版本：
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> select NAME from v$database;
NAME
---------
ORCL
实验2: 准备主数据库系统
以下均在主数据库系统操作。
任务1: Open the 1521 Port for the on-premise Database
在VCN设置中，1521端口已开放。此处是指在操作系统一级开放：
sudo firewall-cmd --zone=public --add-port=1521/tcp --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --list-all | grep 1521
任务2: Enable ssh Connect for the Oracle User
其实这一步可以不做，因为用户默认就是可以ssh的，但这一步的目的主要是为了安全，也就是只允许oracle和opc用户ssh。
sudo vi /etc/ssh/sshd_config
# 加入以下
AllowUsers oracle opc
sudo systemctl restart sshd.service
任务3: Enable TDE
Oracle MAA 最佳实践建议使用 Oracle 透明数据加密 (TDE) 来加密主数据库和备用数据库，以确保所有数据都是静态加密的。 数据可以在迁移过程中转换，但强烈建议在迁移之前转换为 TDE，以提供最安全的 Data Guard 环境。
sudo su - oracle
mkdir -p /u01/app/oracle/admin/ORCL/wallet
在文件$ORACLE_HOME/network/admin/sqlnet.ora中添加以下：
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
)
)
在数据库中创建，打开keystore，创建master key：
connect / as sysdba
-- create keystore
administer key management create keystore '/u01/app/oracle/admin/ORCL/wallet' identified by "Ora_DB4U";
-- open keystore
administer key management set keystore open identified by "Ora_DB4U" container=all;
-- create master key
administer key management set key identified by "Ora_DB4U" with backup using 'backup' container=all;
-- Make keystore autologin
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/wallet' identified by "Ora_DB4U";
-- Reset wallet from PASSWORD to AUTOLOGIN mode.
administer key management set keystore close identified by "Ora_DB4U" container=all;
查看keystore状态：
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- ---------- ---------- --------- -------- --------- ----------
FILE /u01/app/oracle/admin/ORCL/wallet/ OPEN AUTOLOGIN SINGLE NONE NO 1
FILE OPEN AUTOLOGIN SINGLE UNITED NO 2
FILE OPEN AUTOLOGIN SINGLE UNITED NO 3
任务4: Encrypt the Data Files
本例仅加密PDB中的USERS表空间。
先确认其未加密：
SQL> alter session set container=orclpdb;
Session altered.
SQL> select tablespace_name, encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
加密USERS表空间：
SQL> alter tablespace users encryption online encrypt;
Tablespace altered.
SQL> select tablespace_name, encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS YES
任务5: Enable the Network Encryption
对于未由 TDE 加密的任何其他数据库有效负载（例如数据文件或重做标头），也需要通过VPN 连接或 Oracle Net 加密。 在本实验中，您使用公共 Internet 连接本地和云端，因此您需要启用网络加密。
检查网络服务：
connect / as sysdba
set linesize 120
col network_service_banner for a85
select i.network_service_banner from v$session_connect_info i, v$session s where s.sid=i.sid and s.serial# = i.serial# and s.username = 'SYS';
NETWORK_SERVICE_BANNER
-------------------------------------------------------------------------------------
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Authentication service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
在$ORACLE_HOME/network/admin/sqlnet.ora文件中添加以下：
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)
再次检查网络服务，增加了AES256和SHA1两行，表明网络加密已生效：
NETWORK_SERVICE_BANNER
-------------------------------------------------------------------------------------
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Authentication service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
6 rows selected.
任务6: Enable Achivelog and Flashback
默认未开启归档：
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence 15
Current log sequence 17
开启归档和Flashback：
shutdown immediate
startup mount
alter database archivelog;
!mkdir -p /u01/app/oracle/fra/ORCL
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fra/ORCL' SCOPE=BOTH SID='*';
alter database flashback on;
alter database open;
确认均已开启：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
任务7: Change Redo Log Size and Create Standby Log
查看redo log group，大小为200M。而最佳建议为1G：
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 209715200 INACTIVE
2 209715200 CURRENT
3 209715200 INACTIVE
添加3个1G大小的redo log group：
alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/redo04.log' size 1024M;
alter database add logfile group 5 '/u01/app/oracle/oradata/ORCL/redo05.log' size 1024M;
alter database add logfile group 6 '/u01/app/oracle/oradata/ORCL/redo06.log' size 1024M;
执行多次log switch，直到3个老的200MB的log group变为INACTIVE，然后删除它们：
alter system switch logfile;
alter system checkpoint;
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 209715200 INACTIVE
2 209715200 INACTIVE
3 209715200 INACTIVE
4 1073741824 INACTIVE
5 1073741824 CURRENT
6 1073741824 UNUSED
6 rows selected.
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
现在我们有了3个1G的redo log group。
按照最佳建议，我们需要创建4个4 standby log group，srl表示standby redo log：
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/ORCL/srl_redo01.log' size 1024M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/ORCL/srl_redo02.log' size 1024M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/ORCL/srl_redo03.log' size 1024M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/ORCL/srl_redo04.log' size 1024M;
查看状态：
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 1073741824
2 1 1073741824
3 1 1073741824
7 1 1073741824
任务8: Modify the Init Parameters for Best Practice
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=both;
alter system set FAST_START_MTTR_TARGET=300 scope=both;
实验3: 创建备数据库系统
在OCI中通过VCN Wizrad创建一个VCN，启用公网连接。名字无所谓，使用默认参数。
在Default Security List中。添加规则，允许ingress到1521的TCP/IP访问。
创建Shape为VM.Stanard2.1的单实例VM DBCS数据库系统，参数如下：
- 数据库系统名：dbstandby
- 软件版本：EE-EP
- storage management software：Logical Volume Manager。
- Hostname prefix: dbstby 最多16字节，为避免重复，建议加个日期或时间
- 数据库名：ORCL，必须与源数据库一致。
- 数据库版本：19.11.0.0，必须与源数据库一致。
- PDB name: orclpdb
- Database unique name suffix：stby 强烈建议填写，这样名字可以固定下来
- 网络：具有公网的子网
数据库系统建立完毕后，记录数据库信息：
- Database Unique Name：ORCL_lin1dq
- 公网IP：129.152.2.194
实验4：设置主备数据库系统间的连接
在 Data Guard 配置中，信息在主数据库和备用数据库之间双向传输。 这需要在主数据库和备用数据库上进行基本配置、网络调整和开放端口。
任务1: 配置主机名解析
在主数据库系统的/etc/hosts中，添加备数据库系统的主机名解析：
129.152.2.194 dbstby
在备数据库系统的/etc/hosts中，添加主数据库系统的主机名解析：
129.152.12.48 primary primary.subnet1.primaryvcn.oraclevcn.com
任务2: Prompt-less SSH configure
配置主数据库系统到备数据库系统的oracle用户无口令SSH访问。
在主数据库系统，oracle用户下，生成秘钥对，拷贝公钥：
$ ssh-keygen -t rsa
$ ls -l ~/.ssh
total 8
-rw-------. 1 oracle oinstall 1679 Jan 21 15:18 id_rsa
-rw-r--r--. 1 oracle oinstall 396 Jan 21 15:18 id_rsa.pub
$ cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDF6icBzKzUj8PmdmWLVSyF5QoNEt+wqYV7OXoyEPi4eKlZtD3+PcdyT+rtUd+WFGXpn78YO7zpskhzWuXRUBJNHJ3PgQdicT/0l2IR0/SE/LRLIxSTV3X9O42JvKes+XebXBQdBayqeeAClqh0FzDQQJtGu0/g8w56NahadCw8hEphFsuDzbwq2V/i/4hLNQEhupcbEJHgsNN+tC9C8xH68e8SfnudmCpZ8lhk3GUgBA5MGd3LwGzjCQHRnVh2s2UmmyVEbp3cIz774q6DyvlR6KJ2/AAUiBjo5IDJK/XleIyS+q724X4/eLEeLxYsU7XPmTde8+4mdEl9LdnOTAxN oracle@primary
将公钥追加到备数据库系统，oracle用户下的~/.ssh/authorized_keys文件中。
[oracle@dbstby ~]$ cat ~/.ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDF6icBzKzUj8PmdmWLVSyF5QoNEt+wqYV7OXoyEPi4eKlZtD3+PcdyT+rtUd+WFGXpn78YO7zpskhzWuXRUBJNHJ3PgQdicT/0l2IR0/SE/LRLIxSTV3X9O42JvKes+XebXBQdBayqeeAClqh0FzDQQJtGu0/g8w56NahadCw8hEphFsuDzbwq2V/i/4hLNQEhupcbEJHgsNN+tC9C8xH68e8SfnudmCpZ8lhk3GUgBA5MGd3LwGzjCQHRnVh2s2UmmyVEbp3cIz774q6DyvlR6KJ2/AAUiBjo5IDJK/XleIyS+q724X4/eLEeLxYsU7XPmTde8+4mdEl9LdnOTAxN oracle@primary
确认主数据库系统的oracle用户可以无口令ssh登录备数据库系统。
$ ssh oracle@dbstby echo Test success
模仿以上过程，配置备数据库系统到主数据库系统的oracle用户无口令SSH访问，此略。测试语句如下：
ssh oracle@primary echo Test success
实验5A：Deploy Active Data Guard with LVM
任务 1: Manually Delete the Standby Database Created by Tooling
登录备数据库：
SQL> select DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME
------------------------------
ORCL_lin1dq
根据上一步输出的DB_UNIQUE_NAME，形成以下脚本并运行：
connect / as sysdba
set heading off linesize 999 pagesize 0 feedback off trimspool on
spool /tmp/files.lst
select 'rm '||name from v$datafile union all select 'rm '||name from v$tempfile union all select 'rm '||member from v$logfile;
spool off
create pfile='/tmp/ORCL_lin1dq.pfile' from spfile;
关闭数据库：
connect / as sysdba
shutdown immediate
运行通过spool生成的脚本文件，删除数据和日志文件：
chmod a+x /tmp/files.lst
vi /tmp/files.lst，删除第1行和最后1行
. /tmp/files.lst
任务 2: Copy the Password File to the Cloud Host
在备数据库系统，拷贝主数据库系统的口令文件：
# 以oracle用户运行
scp oracle@primary:/u01/app/oracle/product/19c/dbhome_1/dbs/orapwORCL $ORACLE_HOME/dbs
任务 3: Copying the Wallet File to the Cloud Host
在备数据库系统：
export DB_UNIQUE_NAME=ORCL_lin1dq
scp oracle@primary:/u01/app/oracle/admin/ORCL/wallet/ewallet.p12 /opt/oracle/dcs/commonstore/wallets/tde/$DB_UNIQUE_NAME
scp oracle@primary:/u01/app/oracle/admin/ORCL/wallet/cwallet.sso /opt/oracle/dcs/commonstore/wallets/tde/$DB_UNIQUE_NAME
chmod 600 /opt/oracle/dcs/commonstore/wallets/tde/$DB_UNIQUE_NAME/*wallet*
任务 4: Configure Static Listeners
A static listener is needed for initial instantiation of a standby database. The static listener enables remote connection to an instance while the database is down in order to start a given instance. See MOS 1387859.1 for additional details. A static listener for Data Guard Broker is optional.
在主数据库系统的$ORACLE_HOME/network/admin/listener.ora文件中添加以下：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL)
(ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1)
(SID_NAME=ORCL)
)
(SID_DESC=
(GLOBAL_DBNAME=ORCL_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1)
(SID_NAME=ORCL)
)
)
然后reload 监听：
lsnrctl reload
在备数据库系统的$ORACLE_HOME/network/admin/listener.ora文件中添加以下，注意GLOBAL_DBNAME需与DB_UNIQUE_NAME一致：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL_lin1dq)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME=ORCL)
)
(SID_DESC=
(GLOBAL_DBNAME=ORCL_lin1dq_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME=ORCL)
)
)
然后reload 监听：
lsnrctl reload
mount备数据库，虽然之前删除了所有数据文件，但是mount还是没问题的：
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 22 01:53:28 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6442449464 bytes
Fixed Size 9148984 bytes
Variable Size 1090519040 bytes
Database Buffers 5318377472 bytes
Redo Buffers 24403968 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
任务 5: TNS Entries for Redo Transport
在主数据库端的tnsnames.ora文件中，添加以下net service name，用于传输redo：
# $ORACLE_HOME/network/admin/tnsnames.ora
ORCL_lin1dq =
(DESCRIPTION =
(SDU=65536)
(RECV_BUF_SIZE=134217728)
(SEND_BUF_SIZE=134217728)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_lin1dq)
(UR=A)
)
)
当前，备数据库端的tnsnames.ora文件如下：
# $ORACLE_HOME/network/admin/tnsnames.ora
ORCL_LIN1DQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby.sub01211258140.myvcn.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_lin1dq.sub01211258140.myvcn.oraclevcn.com)
)
)
改为如下：
## 以下为修改的，改动为去除HOST和SERVICE_NAME中的域名
## 不同的数据库域名在做DML重定向时会报错。
ORCL_LIN1DQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_lin1dq)
)
)
## 以下为新增的
ORCL =
(DESCRIPTION =
(SDU=65536)
(RECV_BUF_SIZE=134217728)
(SEND_BUF_SIZE=134217728)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
(UR=A)
)
)
接下来需要设置TCP socket size为128M（134217728）。备数据库系统已经设置好了：
# /sbin/sysctl -p | grep net.core.*max
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
我们只需设置主数据库系统，在/etc/sysctl.conf中修改以下：
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
使其生效并确认：
# sudo /sbin/sysctl -p
# sudo /sbin/sysctl -a | egrep net.core.[w,r]mem_max
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
任务 6: Instantiate the Standby Database
在备数据库系统，以oracle用户，首先建立目录：
export DB_UNIQUE_NAME=ORCL_lin1dq
mkdir -p /u02/app/oracle/oradata/$DB_UNIQUE_NAME/pdbseed
mkdir -p /u02/app/oracle/oradata/$DB_UNIQUE_NAME/orclpdb
mkdir -p /u03/app/oracle/redo/$DB_UNIQUE_NAME/onlinelog
以sysdba运行以下SQL（此时数据库处于MOUNTED状态）：
alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u02/app/oracle/oradata/ORCL_lin1dq' scope=spfile;
alter system set db_create_online_log_dest_1='/u03/app/oracle/redo/ORCL_lin1dq/onlinelog' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u03/app/oracle/redo/ORCL_lin1dq/onlinelog' scope=spfile;
alter system set db_domain='' scope=spfile;
关闭数据库：
shutdown immediate
用RMAN将数据库启动到nomount状态：
[oracle@dbstby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 22 03:48:57 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 6442449464 bytes
Fixed Size 9148984 bytes
Variable Size 1090519040 bytes
Database Buffers 5318377472 bytes
Redo Buffers 24403968 bytes
执行恢复：
-- 从主数据库系统恢复controlfile
RMAN> restore standby controlfile from service 'ORCL';
Starting restore at 22-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u02/app/oracle/oradata/ORCL_lin1dq/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/ORCL_LIN1DQ/control02.ctl
Finished restore at 22-JAN-22
-- mount数据库
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
-- 恢复数据库，需要一些时间
RMAN> restore database from service 'ORCL' section size 5G;
-- 关闭数据库
RMAN> shutdown immediate
使用SQL Plus再次mount数据库：
$ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 6442449464 bytes
Fixed Size 9148984 bytes
Variable Size 1090519040 bytes
Database Buffers 5318377472 bytes
Redo Buffers 24403968 bytes
Database mounted.
任务 7: Clear All Online and Standby Redo Logs
在备数据库端，运行以下SQL生成并运行脚本：
set pagesize 0 feedback off linesize 120 trimspool on
spool /tmp/clearlogs.sql
select distinct 'alter database clear logfile group '||group#||';' from v$logfile;
spool off
@/tmp/clearlogs.sql
clear logfile group相当于重新初始化logfile。
任务 8: Configure Data Guard Broker
在备数据库系统运行以下命令：
show parameter dg_broker_config_file;
show parameter dg_broker_start;
alter system set dg_broker_start=true;
select pname from v$process where pname like 'DMON%';
输出如下：
SQL> show parameter dg_broker_config_file;
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr1ORCL_lin1dq.d
at
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr2ORCL_lin1dq.d
at
SQL> show parameter dg_broker_start;
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
SQL> select pname from v$process where pname like 'DMON%';
DMON
DMON是Data Guard Broker Monitor Process，作用如下，详情参考这里：
Manages and monitors a database that is part of a Data Guard broker configuration
在主数据库系统，运行同样的命令：
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 22 04:37:38 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> show parameter dg_broker_config_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/19c/db
home_1/dbs/dr1ORCL.dat
dg_broker_config_file2 string /u01/app/oracle/product/19c/db
home_1/dbs/dr2ORCL.dat
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
System altered.
SQL> select pname from v$process where pname like 'DMON%';
PNAME
-----
DMON
在DGMGRL中注册数据库：
$ dgmgrl sys/Ora_DB4U@ORCL
CREATE CONFIGURATION adgconfig AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
ADD DATABASE ORCL_lin1dq AS CONNECT IDENTIFIER IS ORCL_lin1dq MAINTAINED AS PHYSICAL;
enable configuration;
查看配置状态：
DGMGRL> SHOW CONFIGURATION;
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_lin1dq - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 24 seconds ago)
有一个警告，等一会就好了：
DGMGRL> SHOW CONFIGURATION;
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_lin1dq - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 47 seconds ago)
if there is a warning message, Warning: ORA-16809: multiple warnings detected for the member, or Warning: ORA-16854: apply lag could not be determined. You can wait several minutes and show configuration again.
实验 5B: Deploy Active Data Guard with ASM
以下步骤是针对在 Lab5 中使用 ASM 进行存储管理的云数据库。 如果您选择 LVM 作为存储，请使用实验5B。
任务1: Manually Delete the standby Database Created by Tooling
获取数据库的DB_UNIQUE_NAME：
$ sudo su - oracle
$ srvctl config database
ORCL_stby
运行以下SQL以生成脚本：
set heading off linesize 999 pagesize 0 feedback off trimspool on
spool /tmp/files.lst
select 'asmcmd rm '||name from v$datafile union all select 'asmcmd rm '||name from v$tempfile union all select 'asmcmd rm '||member from v$logfile;
spool off
create pfile='/tmp/ORCL_stby.pfile' from spfile;
exit;
赋予脚本执行权限，并删除文件的第1和最后1行：
chmod a+x /tmp/files.lst
保持数据库配置，然后关闭数据库：
srvctl config database -d ORCL_stby > /tmp/ORCL_nrt1d4.config
srvctl stop database -d ORCL_stby -o immediate
输出如下：
$ cat /tmp/ORCL_nrt1d4.config
Database unique name: ORCL_stby
Database name: ORCL
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL_STBY/PARAMETERFILE/spfile.269.1094644865
Password file:
Domain: sub01211258140.myvcn.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: ORCL
Configured nodes: dbstby0707
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
删除数据库文件，需以grid用户运行：
sudo su - grid
. /tmp/files.lst
任务2: Copy the Password File to the Cloud Host
本小节均在备数据库系统操作。
以oracle用户运行：
scp oracle@primary:/u01/app/oracle/product/19c/dbhome_1/dbs/orapwORCL /tmp
chmod 777 /tmp/orapwORCL
切换到grid用户：
$ sudo su - grid
$ asmcmd pwcopy --dbuniquename ORCL_stby -f /tmp/orapwORCL +DATA/ORCL_STBY/orapwORCL_stby
copying /tmp/orapwORCL -> +DATA/ORCL_STBY/orapwORCL_stby
ASMCMD-9453: failed to register password file as a CRS resource
报错了，需以oracle用户运行以下：
srvctl modify database -db ORCL_stby -pwfile '+DATA/ORCL_STBY/orapwORCL_stby'
确认口令文件已成功注册，注意Password file一行：
$ srvctl config database -d ORCL_stby
Database unique name: ORCL_stby
Database name: ORCL
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL_STBY/PARAMETERFILE/spfile.269.1094644865
Password file: +DATA/ORCL_STBY/orapwORCL_stby
Domain: sub01211258140.myvcn.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: ORCL
Configured nodes: dbstby0707
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
任务3: Copying the Wallet File to the Cloud Host
查看$ORACLE_HOME/network/admin/sqlnet.ora文件。
主数据库系统应包含以下内容：
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
)
)
备数据库系统应包含以下内容：
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
在备数据库系统，运行以下：
scp oracle@primary:/u01/app/oracle/admin/ORCL/wallet/ewallet.p12 /opt/oracle/dcs/commonstore/wallets/tde/ORCL_stby
scp oracle@primary:/u01/app/oracle/admin/ORCL/wallet/cwallet.sso /opt/oracle/dcs/commonstore/wallets/tde/ORCL_stby
chmod 600 /opt/oracle/dcs/commonstore/wallets/tde/ORCL_stby/*wallet*
任务4: Configure Static Listeners
在主数据库系统的$ORACLE_HOME/network/admin/listener.ora文件中添加以下：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL)
(ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1)
(SID_NAME=ORCL)
)
(SID_DESC=
(GLOBAL_DBNAME=ORCL_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1)
(SID_NAME=ORCL)
)
)
然后reload 监听：
lsnrctl reload
在备数据库系统的$ORACLE_HOME/network/admin/listener.ora文件中添加以下(需以grid用户查看)，注意GLOBAL_DBNAME需与DB_UNIQUE_NAME一致：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL_stby)
(ORACLE_HOME=/u01/app/19.0.0.0/grid)
(SID_NAME=ORCL)
)
(SID_DESC=
(GLOBAL_DBNAME=ORCL_stby_DGMGRL)
(ORACLE_HOME=/u01/app/19.0.0.0/grid)
(SID_NAME=ORCL)
)
)
然后reload 监听：
lsnrctl reload
重启数据库到mount状态：
srvctl start database -db ORCL_stby -startoption mount
任务5: TNS Entries for Redo Transport
在主数据库端的tnsnames.ora文件中，添加以下net service name，用于传输redo：
# $ORACLE_HOME/network/admin/tnsnames.ora
ORCL_stby =
(DESCRIPTION =
(SDU=65536)
(RECV_BUF_SIZE=134217728)
(SEND_BUF_SIZE=134217728)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_stby)
(UR=A)
)
)
在从数据库的tnsnames.ora文件中，添加以下内容：
ORCL_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_stby)
)
)
ORCL =
(DESCRIPTION =
(SDU=65536)
(RECV_BUF_SIZE=134217728)
(SEND_BUF_SIZE=134217728)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
(UR=A)
)
)
接下来设置TCP/IP socket size部分，参考实验5A任务5。最后用以下命令确认：
# sudo /sbin/sysctl -a | egrep net.core.[w,r]mem_max
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
任务6: Instantiate the Standby Database
本小节均在备数据库系统上操作。
以grid用户运行：
asmcmd mkdir DATA/ORCL_STBY/pdbseed
asmcmd mkdir DATA/ORCL_STBY/orclpdb
asmcmd mkdir RECO/ORCL_STBY/ONLINELOG
在备数据库系统，以oracle用户运行：
ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/ORCL','+DATA/ORCL_STBY' scope=spfile;
ALTER SYSTEM SET db_create_online_log_dest_1='+RECO' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/ORCL','+RECO/ORCL_STBY/ONLINELOG' scope=spfile;
ALTER SYSTEM SET db_domain='' scope=spfile;
exit;
关闭数据库：
srvctl stop database -d ORCL_stby -o immediate
然后运行RMAN将数据库启动到nomount状态：
$ rman target /
RMAN> startup nomount
Oracle instance started
Total System Global Area 6442449464 bytes
Fixed Size 9148984 bytes
Variable Size 1140850688 bytes
Database Buffers 5268045824 bytes
Redo Buffers 24403968 bytes
RMAN> restore standby controlfile from service 'ORCL';
Starting restore at 23-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+RECO/ORCL_STBY/CONTROLFILE/current.256.1094644361
Finished restore at 23-JAN-22
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> restore database from service 'ORCL' section size 5G;
...
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 23-JAN-22
RMAN> shutdown immediate
database dismounted
Oracle instance shut down
RMAN> exit
启动数据库到mount状态：
srvctl start database -d ORCL_stby -o mount
任务7: Clear All Online and Standby Redo Logs
在备数据库系统，运行以下SQL生成脚本。
set pagesize 0 feedback off linesize 120 trimspool on
spool /tmp/clearlogs.sql
select distinct 'alter database clear logfile group '||group#||';' from v$logfile;
spool off
@/tmp/clearlogs.sql
任务8: Configure Data Guard Broker
在主备数据库系统均运行以下命令：
show parameter dg_broker_config_file;
show parameter dg_broker_start;
alter system set dg_broker_start=true;
select pname from v$process where pname like 'DMON%';
主数据库系统输出略，可参考实验5A任务8。
备数据库系统输出为：
SQL> show parameter dg_broker_config_file;
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
.0/dbhome_1/dbs/dr1ORCL_stby.d
at
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
.0/dbhome_1/dbs/dr2ORCL_stby.d
at
SQL> show parameter dg_broker_start;
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
SQL> select pname from v$process where pname like 'DMON%';
DMON
在DGMGRL中注册数据库：
$ dgmgrl sys/Ora_DB4U@ORCL
CREATE CONFIGURATION adgconfig AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
ADD DATABASE ORCL_stby AS CONNECT IDENTIFIER IS ORCL_stby MAINTAINED AS PHYSICAL;
enable configuration;
查看配置状态：
DGMGRL> SHOW CONFIGURATION;
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_lin1dq - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 24 seconds ago)
有一个警告，等一会就好了：
DGMGRL> SHOW CONFIGURATION;
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_lin1dq - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 47 seconds ago)
if there is a warning message, Warning: ORA-16809: multiple warnings detected for the member, or Warning: ORA-16854: apply lag could not be determined. You can wait several minutes and show configuration again.
实验6：测试Active Data Guard
测试DML重定向和switchover。
任务1: Test Transaction Replication
在主数据库系统，创建测试用户并授权：
connect / as sysdba
alter pluggable database all open;
alter session set container=orclpdb;
create user testuser identified by testuser;
grant connect,resource to testuser;
alter user testuser quota unlimited on users;
以测试用户登录，创建测试表和测试数据：
connect testuser/testuser@primary:1521/orclpdb
create table test(a number,b varchar2(20));
insert into test values(1,'line1');
commit;
在备数据库系统，打开数据库：
connect / as sysdba
select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
alter database open;
alter pluggable database orclpdb open;
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ ONLY NO
select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
若OPEN_MODE为READ ONLY而非READ ONLY WITH APPLY，则运行以下SQL：
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;
select open_mode,database_role from v$database;
在备数据库系统，以测试用户验证：
connect testuser/testuser@dbstby:1521/orclpdb
select * from test;
A B
---------- --------------------
1 line1
任务2: Check Lag between the Primary and Standby
在主数据库系统，准备测试脚本：
wget https://objectstorage.us-ashburn-1.oraclecloud.com/p/Bq05Vhib-p_vraOu-wFpTEmyydA4d8qekXWXcb6W6M3pL43LVSAS2eFwKpYvAVxQ/n/c4u04/b/data-management-library-files/o/workload.sh
wget https://objectstorage.us-ashburn-1.oraclecloud.com/p/KNpGKB2VdoBWC5VWOHvD5vHg9P1OO5mqBJUxNonwY9LzaMaZ8Kcu7w3wBq9xgebW/n/c4u04/b/data-management-library-files/o/scn.sql
chmod a+x workload.sh
这两个脚本内容如下：
$ cat scn.sql
conn system/Ora_DB4U@orclpdb
select current_scn, to_char(sysdate, 'YYYYMMDD-HH12MISS') time from v$database;
$ cat workload.sh
echo ""
echo " NOTE:"
echo " To break out of this batch"
echo " job, please issue CTL-C "
echo ""
echo "...sleeping 5 seconds"
echo ""
sleep 5
sqlplus -S /nolog << EOF
connect testuser/testuser@orclpdb;
drop table sale_orders;
create table sale_orders(ORDER_ID number, ORDER_DATE varchar2(9), CUSTOMER_ID number);
EOF
c=1
while [ $c -le 1000 ]
do
sqlplus -S /nolog << EOF
connect testuser/testuser@orclpdb;
insert all
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3035,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
into sale_orders (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (3041,'10-MAY-20', 13287)
select 1 from dual;
commit;
select count(*) from sale_orders;
@scn.sql
EOF
sleep 1
(( c++))
done
运行脚本，保持终端打开：
. ./workload.sh
在备数据库系统，以测试用户登录：
connect testuser/testuser@dbstby:1521/orclpdb
select count(*) from sale_orders;
COUNT(*)
----------
330
select count(*) from sale_orders;
COUNT(*)
----------
340
在备数据库系统，以SYS用户登录：
connect / as sysdba
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
-----------
2923866
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
-----------
2923911
set linesize 120;
column name format a25;
column value format a20;
column time_computed format a20;
column datum_time format a20;
select name, value, time_computed, datum_time from v$dataguard_stats;
NAME VALUE TIME_COMPUTED DATUM_TIME
------------------------- -------------------- -------------------- --------------------
transport lag +00 00:00:00 01/23/2022 04:33:13 01/23/2022 04:33:12
apply lag +00 00:00:00 01/23/2022 04:33:13 01/23/2022 04:33:12
apply finish time +00 00:00:00.000 01/23/2022 04:33:13
estimated startup time 9 01/23/2022 04:33:13
用DGMGRL检查延迟：
$ dgmgrl sys/Ora_DB4U@orcl
DGMGRL> show database ORCL_stby
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: ON
Instance(s):
ORCL
Database Status:
SUCCESS
任务3: Test DML Redirection
19c开始，支持DML重定向。
以下操作均在备数据库系统执行。
在DML重定向打开之前，插入会报错。
connect testuser/testuser@dbstby:1521/orclpdb
insert into test values(2,'line2');
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
开启就运行正常了：
ALTER SESSION ENABLE ADG_REDIRECT_DML;
insert into test values(2,'line2');
1 row created.
commit;
select * from test;
A B
---------- --------------------
2 line2
1 line1
DML重定向性能上会慢些：
You may encounter the performance issue when using the DML redirection. This is because each of the DML is issued on a standby database will be passed to the primary database where it is executed. The default Data Guard protection mode is Maximum Performance and the redo transport mode is ASYNC. The session waits until the corresponding changes are shipped to and applied to the standby. In order to improve performance of the DML redirection, you need to switch the redo logfile more frequently on the primary side, or you can change the protection mode to Maximum Availability and the redo transport mode to SYNC - This protection mode provides the highest level of data protection, but it’s need the high throughput and low latency network, you can use FastConnect or deploy the Data Guard in different ADs within the same region.
用DGMGRL查看protection mode和redo transport mode：
$ dgmgrl sys/Ora_DB4U@orcl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jan 23 04:41:32 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 19 seconds ago)
DGMGRL> show database orcl LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> show database orcl_stby LogXptMode
LogXptMode = 'ASYNC'
修改protection mode为最大可用，redo transport mode为同步：
DGMGRL> EDIT DATABASE orcl SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT DATABASE orcl_stby SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration
Configuration - adgconfig
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 27 seconds ago)
再次插入数据，性能应有所提升(似乎不明显)：
SQL> insert into test values(3,'line3');
1 row created.
Elapsed: 00:00:00.49
SQL> commit;
Commit complete.
Elapsed: 00:00:00.15
备数据库改为默认保护模式：
$ dgmgrl sys/Ora_DB4U@orcl
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
EDIT DATABASE orcl_stby SET PROPERTY LogXptMode='ASYNC';
EDIT DATABASE orcl SET PROPERTY LogXptMode='ASYNC';
show configuration
任务4: Switchover to the Cloud
$ dgmgrl sys/Ora_DB4U@orcl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jan 23 04:53:39 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> validate database ORCL_stby
Database Role: Physical standby database
Primary Database: orcl
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
orcl : On
orcl_stby: Off
Managed by Clusterware:
orcl : NO
orcl_stby: YES
Validating static connect identifier for the primary database orcl...
The static connect identifier allows for a connection to database "orcl".
DGMGRL> switchover to orcl_stby
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_stby"
Connecting ...
Connected to "ORCL_stby"
Connected as SYSDBA.
New primary database "orcl_stby" is opening...
Operation requires start up of instance "ORCL" on database "orcl"
Starting instance "ORCL"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORCL"
Database mounted.
Database opened.
Connected to "ORCL"
Switchover succeeded, new primary is "orcl_stby"
切换倒是正常，但之前在主数据库系统运行的工作负载脚本失败了：
SP2-0640: Not connected
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
主备数据库系统的角色互换了：
-- 备数据库
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
-- 主数据库
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ ONLY NO
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
实验7：Failover and Reinstate
故障转移是假定主数据库完全失效的计划外事件。 备用数据库立即转换为主数据库。 当您使用最高性能保护模式时，故障转移可能会导致数据丢失。 故障转移后，必须将旧的主数据库恢复为物理备用数据库，通过启用闪回数据库和 Data Guard 代理可使过程变得简单。
任务1: Setup the Current Primary Database Flashback
在上一个实验中，执行了failover，因此本地数据库变为备库，云端数据库变为主库。
在云端数据库，执行以下，可以看到flashback没有打开：
SQL> select open_mode,database_role,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE FLASHBACK_ON
-------------------- ---------------- ------------------
READ WRITE PRIMARY NO
开启flashback：
shutdown immediate
startup mount
alter database flashback on;
alter database open;
alter pluggable database all open;
select open_mode,database_role,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE FLASHBACK_ON
-------------------- ---------------- ------------------
READ WRITE PRIMARY YES
任务2: Failover
$ dgmgrl sys/Ora_DB4U@orcl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jan 23 05:08:38 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)
DGMGRL> validate database orcl_stby
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
orcl_stby: YES
DGMGRL> validate database orcl
Database Role: Physical standby database
Primary Database: orcl_stby
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
orcl_stby: YES
orcl : NO
DGMGRL> failover to orcl
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl"
现在，主数据库是本地数据库，备用数据库被禁用，需要恢复。
任务3: Reinstate the Previous Primary Database
# Reinstate 前
DGMGRL> show configuration
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 35 seconds ago)
# Reinstate (时间有点长)
connect sys/Ora_DB4U@orcl_stby
shutdown immediate
startup mount
connect sys/Ora_DB4U@orcl
reinstate database orcl_stby
Reinstating database "orcl_stby", please wait...
Reinstatement of database "orcl_stby" succeeded
# Reinstate 后
DGMGRL> show configuration
Configuration - adgconfig
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 53 seconds ago)
检查备库状态：
SQL> select open_mode,database_role,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE FLASHBACK_ON
-------------------- ---------------- ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY YES
实验8：拆除ADG关系
本节参考How to Remove One Standby Database from a Data Guard Configuration (Doc ID 2196935.1)。
DGMGRL配置中删除备库：
$ dgmgrl sys/Ora_DB4U@ORCL
disable database orcl_lin1dq
remove database orcl_lin1dq
remove configuration
在主数据库系统，清空以下参数：
alter system set log_archive_dest_n='' scope=both;
确保log_archive_config中不包括备库：
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(ORCL)
补充一点，我感觉配置DG Broker时会自动设置log_archive_config：
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(ORCL_stby,orcl)
删除DG Broker配置文件：
-- 主库
SQL> show parameter dg_broker_config
NAME TYPE VALUE
---------------------- ------ ----------------------------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/19c/dbhome_1/dbs/dr1ORCL.dat
dg_broker_config_file2 string /u01/app/oracle/product/19c/dbhome_1/dbs/dr2ORCL.dat
-- 备库
SQL> show parameter dg_broker_config
NAME TYPE VALUE
---------------------- ------ --------------------------------------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr1ORCL_lin1dq.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr2ORCL_lin1dq.dat
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献22条内容
所有评论(0)