概述
DataGuard是甲骨文推出的一种高可用性数据库方案，在Oracle 8i之前被称为Standby Database。从Oracle 9i开始，正式更名为Data Guard。它是在主节点与备用节点间通过日志同步来保证数据的同步，可以实现数据库快速切换与灾难性恢复。Data Guard只是在软件上对数据库进行设置，并不需要额外购买任何组件。用户能够在对主数据库影响很小的情况下，实现主备数据库的同步。而主备机之间的数据差异只限于在线日志部分，因此被不少企业用作数据容灾解决方案。
分类
DataGuard分为"物理standby"和"逻辑standby"
物理standby
主备库之间物理结构，逻辑结构保持一致。通常情况下备用库通过主库传输过来的归档日志做恢复。
基于"块对块"方式。与主数据库相同。物理standby等同于生产库。物理standby能以read only方式打开(或者在flashback数据库下以"读写方式打开")。
物理standby一旦以"读写"方式打开，备用库将不会从主数据库接受重做数据。只有当备库flashback到过去的点，当备用库flashback到"读写"打开时间点前的状态时，DataGuard会自动同步备库与主库。才能恢复standby角色。
逻辑standby
只保证逻辑结构与主库一致。备用库要处于打开状态，通过从主库归档日志文件中提取出来的SQL语句做恢复。
创建时，与物理standby一样，使用主库copy.
逻辑备库通过执行SQL语句来更新。DataGuard转换日志文件中的数据为SQL语句，然后在逻辑standby上执行SQL语句.因为逻辑standby是通过SQL语句来实现数据同步，那么在同步期间其必须保持打开状态。
DataGuard环境规划
| 内容 | 主库 | 备库 |
|---|
| 操作系统 | CENTOS 7.4 | CENTOS 7.4 |
| IP | 192.168.117.129 | 192.168.117.128 |
| db_name | dg | dg |
| db_unique_name | dg1 | dg2 |
| 数据库安装 | 安装数据库软件+创建监听+安装数据库 | 安装数据库软件+创建监听 |
- IP地址
[oracle@dg1 dbs]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.117.129 dg1
192.168.117.128 dg2
- 监听
[oracle@dg1 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-AUG-2021 10:21:04
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 08-AUG-2021 02:41:32
Uptime 0 days 7 hr. 39 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/11.2.0.4/product/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
Services Summary...
Service "dg1" has 2 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Instance "dg1", status READY, has 1 handler(s) for this service...
Service "dg1XDB" has 1 instance(s).
Instance "dg1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@dg1 dbs]$
listener.ora
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.2.0.4/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg1)
(ORACLE_HOME =/u01/app/oracle/11.2.0.4/product)
(SID_NAME = dg1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0.4/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg2 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)
DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)
参数文件
[oracle@dg1 dbs]$ cat initdg1.ora
dg1.__db_cache_size=557842432
dg1.__java_pool_size=4194304
dg1.__large_pool_size=8388608
dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg1.__pga_aggregate_target=293601280
dg1.__sga_target=884998144
dg1.__shared_io_pool_size=0
dg1.__shared_pool_size=293601280
dg1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/dg1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dg1/control01.ctl','/u01/app/oracle/recovery_area/dg1/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='dg1'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='dg1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.FAL_CLIENT='dg1'
*.FAL_SERVER='dg2'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=293601280
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=881852416
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
*.DB_FILE_NAME_CONVERT='dg1','dg2'
节点2
[oracle@dg2 dbs]$ cat initdg2.ora
dg2.__db_cache_size=633339904
dg2.__java_pool_size=4194304
dg2.__large_pool_size=8388608
dg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg2.__pga_aggregate_target=293601280
dg2.__sga_target=880803840
dg2.__shared_io_pool_size=0
dg2.__shared_pool_size=222298112
dg2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dg2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dg2/control01.ctl','/u01/app/oracle/fast_recovery_area/dg2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dg1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='dg2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg2XDB)'
*.FAL_CLIENT='dg2'
*.FAL_SERVER='dg1'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='</u01/app/oracle/oradata/dg1/>','</u01/app/oracle/oradata/dg2>'
*.open_cursors=300
*.pga_aggregate_target=293601280
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=880803840
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
复制节点1的密码文件到节点2
dg1正常运行，dg2启动到nomount状态
rman target sys/oracle@dg1 auxiliary sys/oracle@dg2
复制数据库到dg2
duplicate target database for standby from active database;
duplicate target database for standby from active database nofilenamecheck;（节点1和节点2的目录完全一样时，使用nofilenamecheck参数）
备库开始接收应用日志
[oracle@oracle10g-dg2-213-101 ~]$ sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;
验证是否同步
备库:
archive log list;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
col dest_name format A40
SQL> select dest_name,status,error from v$archive_dest where rownum<3;
主库:
archive log list;
SQL> col dest_name format A40
col error format A20
set line 200;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
SQL> col dest_name format A40
select dest_name,status,error from v$archive_dest where rownum<3;
查看DG的日志信息
SQL> select * from v$dataguard_status;
Open Read Only standby数据库并且开启实时日志应用
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
Database opened.
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
7 rows selected.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
MRP0 N/A 27 APPLYING_LOG
8 rows selected.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
ARCH ARCH 19 CLOSING
ARCH ARCH 20 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 21 CLOSING
MRP0 N/A 22 WAIT_FOR_LOG
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 22 IDLE
9 rows selected.