案例:DG主库未设置force logging导致备库坏块

DG搭建时,官方文档手册有明确提到要设置数据库为force_logging,防止有nologging操作日志记录不全导致备库应用时出现问题。

虽然是老生常谈的安装规范,但现实中总会遇到不遵守规范的场景,最近就在某客户现场遇到一则这样的案例,因为DG主库设置force_logging晚于DG搭建,导致备库出现坏块,使用dbv检查就表现为DBV-201错误。

下面我们来模拟下这个场景,同时演示下具体修复过程:

1.准备实验环境

主库确认没有开启force logging 模式,如果是,修改为不是,这是模拟故障场景的前提条件:

select force_logging from v$database;
ALTER DATABASE NO FORCE LOGGING;

搭建一套测试DG:主库修改系列DG配置参数后,创建pfile给备库修改使用,同时将密码文件、tnsnames.ora文件传输到备库端,启动实例到nomount状态:

create pfile='/tmp/pfile_for_standby.txt' from spfile;

scp /tmp/pfile_for_standby.txt ora11204@192.168.1.11:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp $ORACLE_HOME/dbs/orapwcrmdb1 ora11204@192.168.1.11:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsingle
scp $ORACLE_HOME/network/admin/tnsnames.ora ora11204@192.168.1.11:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora change pfile depend on standby env;
sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/pfile_for_standby.txt

使用duplicate搭建DG备库,注意备库需要静态监听:

vi dup_dg.sh

rman target sys/oracle@crmdb1 auxiliary sys/oracle@single <<EOF
duplicate target database for standby from active database dorecover nofilenamecheck;
EOF nohup sh dup_dg.sh > dup_dg.log & tail -200f dup_dg.log

注意:目标端所需目录要提前手工创建,因为duplicate过程发现没有对应目录会报错。

2.构造故障场景

主库用户表空间xxx,创建一张表插入数据,nologging创建索引;切换日志,备库检查坏块情况。
在jingyu用户下创建测试表,并使用nologging方式创建索引:

jingyu@CRMDB> create table TEST as select * from dba_objects;

Table created.

jingyu@CRMDB> create index idx_test on test(object_id) nologging;

Index created.

jingyu@CRMDB> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'JINGYU';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
JINGYU DBS_D_JINGYU TEMP

备库查看同步状态OK:

SQL>
set lines 1000
col value for a20
col name for a30
col unit for a30
col TIME_COMPUTED for a30
col DATUM_TIME for a30
select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 06/08/2020 09:14:26 06/08/2020 09:14:26
apply lag +00 00:00:00 day(2) to second(0) interval 06/08/2020 09:14:26 06/08/2020 09:14:26
apply finish time day(2) to second(3) interval 06/08/2020 09:14:26
estimated startup time 40 second 06/08/2020 09:14:26

但备库使用dbv检查数据文件,发现已经存在坏块,报错都是DBV-00201:

[ora11204@OEL-ASM arch]$ dbv file=/u01/oradata/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jun 8 08:52:12 2020

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

DBVERIFY - Verification starting : FILE = /u01/oradata/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBV-00201: Block, DBA 25168260, marked corrupt for invalid redo application

DBV-00201: Block, DBA 25168261, marked corrupt for invalid redo application

...这里省略大量DBV-00201的输出...

DBV-00201: Block, DBA 25168422, marked corrupt for invalid redo application

DBV-00201: Block, DBA 25168423, marked corrupt for invalid redo application

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1998
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 157
Total Pages Failing (Index): 0
Total Pages Processed (Other): 354
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 10291
Total Pages Marked Corrupt : 155
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2168379 (0.2168379)
[ora11204@OEL-ASM arch]$

此时再通过主库设置force logging挽救,为时过晚,只能对之后的操作起作用,但对已造成的坏块无法修复:

ALTER DATABASE FORCE LOGGING;

3.解决故障

在主库确认已设置force logging后,重新搭建DG环境。
当然如果造成坏块的数据文件不是很多,相比较全库而言,直接重新备份受损的数据文件也许是更效率的方案:
比如我这里测试环境,就只有1个数据文件收到了影响,只需要修复它就好:

3.1 确认下主库的这个文件是好的(无坏块):

[oracle@jystdrac1 trace]$ dbv userid=sys/oracle file=+DATA/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jun 8 09:26:21 2020

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

DBVERIFY - Verification starting : FILE = +DATA/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1998
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 312
Total Pages Failing (Index): 0
Total Pages Processed (Other): 199
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 10291
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

3.2 备份这个文件并传输到备库:

backup as compressed backupset datafile 6 format '/public/rman/primary_datafile_6.bak';

RMAN> backup as compressed backupset datafile 6 format '/public/rman/primary_datafile_6.bak';

Starting backup at 2020-06-08 09:29:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 instance=crmdb1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/crmdb/datafile/dbs_d_jingyu.365.1041070633
channel ORA_DISK_1: starting piece 1 at 2020-06-08 09:29:17
channel ORA_DISK_1: finished piece 1 at 2020-06-08 09:29:25
piece handle=/public/rman/primary_datafile_6.bak tag=TAG20200608T092917 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 2020-06-08 09:29:25

3.3 备库关闭,启动到mount,restore损坏的数据文件,然后open开启应用

RMAN> catalog start with '/public/rman/primary_';
RMAN> list backup of datafile 6;
RMAN> restore datafile 6;

再次使用dbv查看坏块情况,已经修复:

[ora11204@OEL-ASM rman]$ dbv file=/u01/oradata/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jun 8 09:37:28 2020

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

DBVERIFY - Verification starting : FILE = /u01/oradata/crmdb/datafile/dbs_d_jingyu.365.1041070633

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1998
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 312
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10311
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 179
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2168379 (0.2168379)
[ora11204@OEL-ASM rman]$

备库重新开启日志应用:

SQL> alter database open;
SQL> recover managed standby database using current logfile disconnect;
SQL> select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 06/08/2020 09:40:57 06/08/2020 09:40:56
apply lag +00 00:00:00 day(2) to second(0) interval 06/08/2020 09:40:57 06/08/2020 09:40:56
apply finish time day(2) to second(3) interval 06/08/2020 09:40:57
estimated startup time 32 second 06/08/2020 09:40:57

坏块消除后,再确认DG重新同步正常即可。

上一篇:nginx 作为静态资源web服务


下一篇:Cousera课程Learning How to Learn学习报告