Oracle DataGuard灾备方案以及搭建步骤

Oracle DataGuard提供了一个高效、全面的灾难恢复和高可用性解决方案。它的存在是为了确保企业数据的高可用性，数据保护以及灾难恢复。DBA可以通过将一些操作（例如查询报表）转移到备库执行的方式来减小主库的压力。

Oracle DataGuard 提供三种数据保护模式：最大保护（ Maximum Protection ），最高可用（ Maximum Availability ）和最大性能（ Maximum Performance ）。如果按照对数据的保护程度或者说主从库数据的同步性 由低到高排序，三种保护模式的顺序应该是：最高性能、最高可用、最大保护。

最大保护（Maximum Protection）

最大保护模式为主数据库提供了最高水平的数据保护，从而确保了一个全面的零数据丢失灾难恢复解决方案。当在最大保护模式下运行时，重做记录由日志写入器 (LGWR) 进程从主数据库同步地传输到备用数据库，并且直到确认事务数据在至少一个备用服务器上的磁盘上可用时，才在主数据库上提交事务。如果出现standby数据库不可用时，主数据库会被关闭。由于重做传输的同步特性，这种最大保护模式可能潜在地影响主数据库响应时间。

最高可用（ Maximum Availability ）

最高可用性模式拥有仅次于最高水平的主数据库数据可用性。如同最大保护模式一样，重做数据由 LGWR 从主数据库同步地传输到备用数据库，直到确认事务数据在备用服务器的磁盘上可用时，事务才在主数据库上完成。不过，在这种模式下（与最大保护模式不同），如果 standby 数据库变为不可用 — 例如由于网络连接问题，处理将在主数据库上继续进行。备用数据库与主数据库相比，可能暂时落在后面，但当它再次变为可用时，备用数据库将使用主数据库上累积的归档日志自动同步，而不会丢失数据。由于同步重做传输，这种保护模式可潜在地影响 primary 主库响应时间和吞吐量。

最大性能（ Maximum Performance ）

最大性能模式是默认的保护模式。它与最高可用性模式相比，提供了稍微少一些的主数据库数据保护，但提供了更高的性能。在这种模式下，当主数据库处理事务时，重做数据由 LGWR 进程异步传输到备用数据库上。在任何情况下，均先完成主数据库上的写操作，主数据库的提交操作不等待备用数据库确认接收。如果任意备用目标数据库变为不可用，则处理将在主数据库上继续进行，这对性能只有很小的影响或没有影响。在主数据库出现故障的情况下，尚未被发送到备用数据库的重做数据会丢失。但是，如果网络有足够的吞吐量来跟上重做流量高峰，并且使用了 LGWR 进程来将重做流量传输到备用服务器，则丢失的事务将非常少或者为零。

逻辑 standby 是通过接收 primary 数据库的 redo log 并转换成 sql 语句，然后在 standby 数据库上执行 SQL 语句 (SQL Apply) 实现同步；物理 standby 是通过接收并应用 primary 数据库的 redo log 以介质恢复的方式 (Redo Apply) 实现同步。由于他们在实现方式上区别决定了物理 standby 无论从逻辑结构和物理结构都是和主库保持一致的，而逻辑 standby 则只需要保证数据库的逻辑结构一致即可。

3 .2 主库参数配置

3 .2.1 开启主库归档模式

SQL>startup mount;

SQL>select open_mode from v$database;

SQL>alter database archivelog;

SQL>alter database open;

SQL>archive log list;

3 .2.2 数据库设为 force logging

SQL>alter database force logging;

SQL>select name,log_mode,force_logging from v$database;

3 .2.3 主库添加 standby 日志

要求添加 standby 日志组数量至少为主库 onlinerelog 日志组数量 +1

SQL>select thread#,group#,members,bytes/1024/1024 from v$log;

SQL>alter database add standby logfile '+DATA/xyjc/onlinelog/standby_redo01.log’ size 50M;

SQL>alter database add standby logfile '+DATA/xyjc/onlinelog/standby_redo02.log’ size 50M;

SQL>alter database add standby logfile '+DATA/xyjc/onlinelog/standby_redo03.log’ size 50M;

SQL>alter database add standby logfile '+DATA/xyjc/onlinelog/standby_redo04.log’ size 50M;

3 .2.4 确认数据库口令文件的使用模式

执行以下命令查看 remote_login_passwordfile 的值是否 EXCLUSIVE

SQL>show parameter remote_login_passwordfile

如果不是，执行以下命令进行设置，并且重启数据库，使其生效：

SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

SQL>shutdown immediate;

SQL>startup;

3 .2.5 相关 DG 参数设置

SQL>alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=( xyjc,xyjcdg )';

SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION= +DATA/xyjc/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= xyjc ';

SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE= xyjcdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= xyjcdg ';

说明：

主库到备库的日志传输链路通常采用 log_archive_dest_1 参数

备库到主库的日志传输链路 log_archive_dest_2 参数

SQL>alter system set FAL_SERVER=xyjcdg;

SQL>alter system set FAL_CLIENT=xyjc;

SQL>alter system set DB_FILE_NAME_CONVERT='+DATA/xyjc/datafile/','/oradata/xyjcdg/','+DATA/xyjc/TEMPFILE','/oradata/xyjcdg' scope=spfile;

SQL>alter system set log_file_name_convert='+DATA/xyjc/onlinelog/','/oradata/xyjcdg/' scope=spfile;

SQL>alter system set log_archive_dest_state_1=enable;

SQL>alter system set log_archive_dest_state_2=enable;

SQL> alter system set standby_file_management=auto;

3 .2.6 配 置 tnsnames.ora

配置主备库的 tnsnames.ora 文件，主库添加备库信息，备库添加主库信息，备库 netca 建立监听，并且在备库 netmgr 静态注册 standby 的监听。

主库 xyjc ： 分别在两个节点的 tnsnames.ora 文件中添加如下内容

su – oracle

cd $ORACLE_HOME/network/admin

vim tnsnames.ora

XYJC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xyjc)

)

)

XYJCDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xyjcdg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xyjc)

(INSTANCE_NAME= xyjc)

)

)

备库 xyjcdg ： 在节点的 tnsnames.ora 文件中添加如下内容

su – oracle

cd $ORACLE_HOME/network/admin

vim tnsnames.ora

XYJC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xyjc)

)

)

XYJCDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xyjcdg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xyjc)

(INSTANCE_NAME= xyjc)

)

)

备库 listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xyjcdg)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = qhzb)

(ORACLE_HOME = /soft/u01/app/oracle/product/11.2.0/)

(SID_NAME = qhzb)

)

)

ADR_BASE_LISTENER = /soft/u01/app/oracle

做完以上配置后，在主备库上执行以下命令，确保两个主机之间网络相通：

$tnsping xyjc

$tnsping xyjcdg

3 .2.7 密码文件配置

su - oracle

$cd $ORACLE_HOME/dbs

scp$ORACLE_HOME/dbs/orapwxyjc1 xyjcdg:$ORACLE_HOME /dbs/orapwxyjc

$orapwd file=orapwxyjc password=sys entries=10

3 .2.8 参数文件配置

su - oracle

sqlplus / as sysdba

create pfile='/tmp/initxyjc.ora' from spfile;

scp /tmp/ initxyjc.ora xyjcdg:/tmp/

3 .2.9 参数文件修改

备库节点本地编辑，并修改相关参数

xyjc.__db_cache_size=469762048

xyjc.__java_pool_size=16777216

xyjc.__large_pool_size=33554432

xyjc.__oracle_base='/soft/u01/app/oracle '#ORACLE_BASE set from environment

xyjc.__pga_aggregate_target=486539264

xyjc.__sga_target=721420288

xyjc.__shared_io_pool_size=0

xyjc.__shared_pool_size=184549376

xyjc.__streams_pool_size=0

*.audit_file_dest='/soft/u01/app/oracle/admin/xyjcdg/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/xyjcdg/control.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='+DATA/xyjc/datafile/','/oradata/xyjcdg/','+DATA/xyjc/TEMPFILE','/oradata/xyjcdg'

*.db_name='xyjc'

DB_UNIQUE_NAME=xyjcdg

*.diagnostic_dest='/soft/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=xyjcXDB)'

*.fal_client='xyjcdg'

*.fal_server='xyjc'

*.log_archive_config='DG_CONFIG=(xyjcdg,xyjc)'

*.log_archive_dest_1='LOCATION=/oradata/xyjcdg/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=xyjcdg'

*.log_archive_dest_2='SERVICE=xyjc LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xyjc'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='+DATA/xyjc/onlinelog/','/oradata/xyjcdg/'

*.memory_target=1203765248

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

上述加红部分是需要在主库参数文件基础上修改或添加的。并建立以下相应目录结构

mkdir –p /oradata/xyjcdg

mkdir –p /soft/u01/app/oracle/admin/xyjcdg/adump

mkdir –p /oradata/xyjcdg/archivelog

3 .2.10 RMAN 复制创建 standby 库

准备工作都完成，可以开始 standby 库的创建了。创建 standby 有两种方式 auto 和 manual ， auto 即为采用 Duplicate 方式在线创建备库 ， manual 即为在主库通过 rman 备份集手动拷贝至 standby 节点进行创建 standby 备库。

注：以下操作在备库完成

首先，我们使用之前修改的 pfile 把备库启动到 nomount 状态，生成 spfile ：

$echo $ORACLE_SID （确认 SID 是否我们设置的）

$sqlplus / as sysdba

SQL>startup nomount pfile='/tmp/initxyjc.ora';

SQL>create spfile from pfile='/tmp/initxyjc.ora';

SQL>shutdown immediate;

SQL>startup nomount;

复制数据文件，在 standby 备库上操作

[oracle@11R2 ~]$ rman target sys/sys@xyjc auxiliary sys/sys@xyjcdg

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 12 13:05:35 2020

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

connected to target database: XYJC (DBID=1945424536)

connected to auxiliary database: XYJC (not mounted)

确认我们已经连接上主库和备库后，执行以下命令：如果复制数据库时，备库的路径和原库一致，就需要加 nofilenamecheck-----dorecover nofilenamecheck

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 12-MAY-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/soft/u01/app/oracle/product/11.2.0/dbs/orapwxyjc' auxiliary format

'/u01/app/oracle/product/11.2.0/dbs/orapwxyjc' ;

}

executing Memory Script

Starting backup at 12-MAY-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=137 device type=DISK

Finished backup at 12-MAY-20

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/oradata/xyjcdg/control.ctl';

}

executing Memory Script

Starting backup at 12-MAY-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/soft/u01/app/oracle/product/11.2.0/dbs/snapcf_qhzb.f tag=TAG20200713T223810 RECID=1 STAMP=1045694293

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 12-MAY-20

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/oradata/qhzbdg/temp.274.1044372539";

switch clone tempfile all;

set newname for datafile 1 to

"/oradata/qhzbdg/system.266.1044372431";

set newname for datafile 2 to

"/oradata/qhzbdg/sysaux.267.1044372433";

set newname for datafile 3 to

"/oradata/qhzbdg/undotbs1.268.1044372433";

set newname for datafile 4 to

"/oradata/qhzbdg/users.269.1044372433";

backup as copy reuse

datafile 1 auxiliary format

"/oradata/qhzbdg/system.266.1044372431" datafile

2 auxiliary format

"/oradata/qhzbdg/sysaux.267.1044372433" datafile

3 auxiliary format

"/oradata/qhzbdg/undotbs1.268.1044372433" datafile

4 auxiliary format

"/oradata/qhzbdg/users.269.1044372433" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/xyjcdg/temp.274.1044372539 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-MAY-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/xyjc/datafile/system.266.1044372431

output file name=/oradata/xyjcdg/system.266.1044372431 tag=TAG20200713T223821

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/xyjc/datafile/sysaux.267.1044372433

output file name=/oradata/xyjcdg/sysaux.267.1044372433 tag=TAG20200713T223821

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/xyjc/datafile/undotbs1.268.1044372433

output file name=/oradata/xyjcdg/undotbs1.268.1044372433 tag=TAG20200713T223821

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/xyjc/datafile/users.269.1044372433

output file name=/oradata/xyjcdg/users.269.1044372433 tag=TAG20200713T223821

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 14-MAY-20

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1040389693 file name=/oradata/xyjcdg/system.266.1044372431

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=1040389693 file name=/oradata/xyjcdg/sysaux.267.1044372433

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=1040389693 file name=/oradata/xyjcdg/undotbs1.268.1044372433

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=1040389693 file name=/oradata/xyjcdg/users.269.1044372433

Finished Duplicate Db at 12-MAY-20

RMAN>

复制完成后，打开数据库开启实时同步：

SQL> alter database open;

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

查看主数据库状态

$sqlplus / as sysdba

SQL>select database_role from v$database;

DATABASE_ROLE

PRIMARY

登录到备库上查看：

$sqlplus / as sysdba

SQL>select database_role from v$database;

DATABASE_ROLE

PHYSICAL STANDBY

检查归档日志是否能正常传输（日志的序号必须是一样的）：

主库

SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

5 13-JUL-20 13-JUL-20 NO YES

6 13-JUL-20 13-JUL-20 NO YES

7 13-JUL-20 13-JUL-20 NO YES

8 13-JUL-20 13-JUL-20 NO YES

7 13-JUL-20 13-JUL-20 YES YES

8 13-JUL-20 13-JUL-20 YES YES

9 13-JUL-20 13-JUL-20 NO YES

9 13-JUL-20 13-JUL-20 YES YES

10 13-JUL-20 13-JUL-20 NO YES

10 13-JUL-20 13-JUL-20 NO YES

备库

SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

7 13-JUL-20 13-JUL-20 YES YES

8 13-JUL-20 13-JUL-20 YES YES

9 13-JUL-20 13-JUL-20 YES YES

10 13-JUL-20 13-JUL-20 IN-MEMORY YES

在备库节点，检查 , 当前应用进程是否有延时

select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

NAME VALUE UNIT TIME_COMPUTED

transport lag +00 00:00:00 day(2) to second(0) interval 05/14/2020 14:31:17

apply lag +00 00:00:00 day(2) to second(0) interval 05/14/2020 14:31:17

3 .2.11 查看 DG 模式

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

MAXIMUM PERFORMANCE MAXIMUM PERFORMANC

默认最大性能模式。

3 .2.12 验证数据同步

SQL> create user test identified by test;

User created.

SQL> select username from dba_users where account_status='OPEN';

USERNAME

SYSTEM

SYS

TEST

SQL> grant dba to test;

Grant succeeded.

SQL> create table test.test as select * from dba_objects where rownum<100;

Table created.

SQL> select count(*) from test.test;

COUNT(*)

99

SQL> alter system switch logfile;

System altered.

3 故障切换

我们配置 DG 的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。 DG 的故障切换分为 switchover 和 failover 两种，本文档暂只记录 switchover 切换。

登录主库：

$sqlplus / as sysdba

SQL>select switchover_status from v$database; （查看主库状态）

SESSIONS ACTIVE

查询结果是 TO STANDBY 或 SESSIONS ACTIVE 表明可以进行切换

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

主库切换为备库后实例会自动停掉，以下命令只在库未关闭情况下执行

SQL>shutdown immediate;

SQL> startup mount;

SQL> select database_role from v$database;

登录备库：

$sqlplus / as sysdba

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

SESSIONS ACTIVE

TO PRIMARY 或 SESSIONS ACTIVE 表明可以切换成主库；

现在可以把备库切换成主库了

SQL>alter database commit to switchover to primary with session shutdown;

SQL> ALTER DATABASE OPEN;

SQL>select switchover_status,database_role,open_mode from v$database;

SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE

TO STANDBY PRIMARY READ WRITE

记住：这时候要在现在的备库上开启同步：

SQL>startup mount;

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

SQL>alter database recover managed standby database cancel;

SQL>alter database open;

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

SQL> select switchover_status,database_role,open_mode from v$database;

SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE

NOT ALLOWED PHYSICAL STANDBY READ ONLY WITH APPLY