960632998@qq.co
作者960632998@qq.co2021-11-24 11:07
系统运维工程师, 神州数码融信云技术服务有限公司

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

字数 14525阅读 1302评论 2赞 8

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

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

8

添加新评论2 条评论

zhuoxiangzhuoxiang技术支持, ZX
2022-04-21 22:44
调度
匿名用户
2021-11-30 08:22
1
Ctrl+Enter 发表

作者其他文章