部署目标
搭建两节点 Oracle 19c Dataguard 环境
主库不关闭
使用物理备库
不使用Broker
部署环境介绍
操作系统版本：OEL 7.6
数据库版本：Oracle Database 19c
两台主机名为：sdedu， sdrep
主库名称：SS19P
备库名称：SS19S
操作步骤
(1). 查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志；
[oracle@sdedu ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 20:04:29 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
---------------------- ---------------------------------------
ARCHIVELOG NO
(2). 查看主库归档目的地，从下面结果中可以看到，归档目的地使用了快速恢复区；
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
(3). 开启主库附加日志，并验证开启的结果；
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
---------------------- ---------------------------------------
ARCHIVELOG YES
从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，
同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。
STANDBY NOLOGGING FOR DATA AVAILABILITY模式：使用批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。
提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。
SQL> alter database set standby nologging for data availability;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
--------------------------- ---------------------------------------------------------------------
NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITY
STANDBY NOLOGGING FOR LOAD PERFORMANCE模式：与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。
在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。
SQL> alter database set standby nologging for load performance;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
-------------------------- --------------------------------------------------------------------------
NOARCHIVELOG STANDBY NOLOGGING FOR LOAD PERFORMANCE
(4). 在主库中添加附加日志；
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SS19P/stredo04.log') size 100m;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SS19P/stredo05.log') size 100m;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SS19P/stredo06.log') size 100m;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SS19P/stredo07.log') size 100m;
Database altered.
(5). 修改主库参数；
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SS19P,SS19S)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19P';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SS19S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19S';
System altered.
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;
System altered.
SQL> alter system set FAL_SERVER=SS19S;
System altered.
SQL> alter system set DB_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;
System altered.
SQL> alter system set LOG_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
(6). 编辑主库 listener.ora 网络配置文件，添加连接主库点静态注册；
[oracle@sdedu ~]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/
[oracle@sdedu admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SS19P.sandata.com.cn)
(SID_NAME = SS19P)
(ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
)
)
(7). 编辑主库 tnsnames.ora 网络配置文件，添加连接备库的本地服务名；
[oracle@sdedu admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_SS19P =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))
SS19P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SS19P.sandata.com.cn)
)
)
SS19S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SS19S.sandata.com.cn)
)
)
(8). 将主库中的网络文件和口令文件传输到备库；
[oracle@sdedu admin]$ scp listener.ora tnsnames.ora sdrep:`pwd`
oracle@sdrep's password:
listener.ora 100% 558 451.3KB/s 00:00
tnsnames.ora 100% 652 580.3KB/s 00:00
[oracle@sdedu admin]$ cd ../../dbs
[oracle@sdedu dbs]$ scp orapwSS19P sdrep:`pwd`/orapwSS19S
oracle@sdrep's password:
orapwSS19P
(9). 备库中创建对应路径：
[oracle@sdrep ~]$ cd /u01/app/oracle/
[oracle@sdrep oracle]$ mkdir -p admin/SS19S/adump
[oracle@sdrep oracle]$ mkdir -p oradata/SS19S
[oracle@sdrep oracle]$ mkdir -p fast_recovery_area/SS19S
(10). 创建备库的参数文件：
[oracle@sdrep oracle]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/dbs/
[oracle@sdrep dbs]$ vi initSS19S.ora
DB_NAME=SS19S
(11). 修改备库的 listener.ora 配置文件：
[oracle@sdrep dbs]$ vi ../network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SS19S.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SS19S.sandata.com.cn)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = SS19S)
)
)
(12). 修改备库的 tnsnames.ora 配置文件：
[oracle@sdedu admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_SS19P =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))
SS19P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SS19P.sandata.com.cn)
)
)
SS19S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SS19S.sandata.com.cn)
)
)
(13). 启动辅助实例；
[oracle@sdrep dbs]$ export ORACLE_SID=SS19S
[oracle@sdrep dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 16:41:03 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1543500824 bytes
Fixed Size 9135128 bytes
Variable Size 1006632960 bytes
Database Buffers 520093696 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
(14). 使用复制数据库技术搭建备库，由于备库的文件路径包含SS19S，因此需要写明audit_file_dest,control_files参数（使用单引号也可以）；
[oracle@sdrep dbs]$ rman target sys/oracle@SS19P auxiliary sys/oracle@SS19S
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 20:51:58 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SS19P (DBID=592912636)
connected to auxiliary database: SS19P (not mounted)
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER
5> SPFILE
6> SET DB_UNIQUE_NAME="SS19S" COMMENT "Is a dbstyle duplicate"
7> SET AUDIT_FILE_DEST="/u01/app/oracle/admin/SS19S/adump"
8> SET CONTROL_FILES='/u01/app/oracle/oradata/SS19S/control01.ctl','/u01/app/oracle/fast_recovery_area/SS19S/control02.ctl'
9> SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(SS19S,SS19P)"
10> SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19S"
11> SET LOG_ARCHIVE_DEST_2="SERVICE=SS19P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19P"
12> SET DB_FILE_NAME_CONVERT="SS19P","SS19S"
13> SET LOG_FILE_NAME_CONVERT="SS19P","SS19S"
14> SET FAL_SERVER="SS19P" COMMENT "Is primary"
15> SET STANDBY_FILE_MANAGEMENT="AUTO"
16> NOFILENAMECHECK;
Starting Duplicate Db at 06-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
current log archived
省略部分………
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_10_g7zjp7gs_.arc RECID=1 STAMP=1002228807
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_11_g7zjp8po_.arc RECID=2 STAMP=1002228808
Deleted 2 objects
Finished Duplicate Db at 06-MAR-19
RMAN> quit
Recovery Manager complete.
(15). 进入备库验证角色信息；
[oracle@sdrep dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 20:54:39 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------
PHYSICAL STANDBY
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------------------
SS19S
(16). 在备库启动recover 过程，应用主库传过来的日志（默认已经是real-time apply模式，因此省略using current logfile）；
SQL> recover managed standby database disconnect from session;
Media recovery complete.
(17). 查询v$dataguard_process 视图，验证来自主库传输过来的日志过程和备库应用日志的情况（v$dataguard_process视图在12.2版本出现，取代了v$managed_standby)；
SQL> select role,thread#,sequence#,action from v$dataguard_process;
ROLE THREAD# SEQUENCE# ACTION
------------------------------- ----------------- -------------------- ------------------------
recovery apply slave 0 0 IDLE
archive local 0 0 IDLE
redo transport timer 0 0 IDLE
gap manager 0 0 IDLE
recovery logmerger 1 12 WAIT_FOR_LOG
recovery apply slave 0 0 IDLE
managed recovery 0 0 IDLE
archive redo 0 0 IDLE
archive redo 0 0 IDLE
archive redo 0 0 IDLE
redo transport monitor 0 0 IDLE
log writer 0 0 IDLE
12 rows selected.
(18). 查询 v$archived_log 视图，验证来自主库传输过来的日志变化情况，下面输出可以看到主库传输过来的日志在增加；
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
------------------- ---------
10 YES
11 YES
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
------------------- ---------
10 YES
11 YES
12 YES
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
------------------- ---------
10 YES
11 YES
12 YES
13 YES