Dataguard · Oracle

Active Dataguard in Action for Block Corruptions

Active Dataguard enables a dataguard to be open as read only and log apply at the same time. This way you can have near real time ( depending on the gap with your primary) read only copy of your primary system which has the all functionality of a normal dataguard. One of the cool features of Active Dataguard is Automatic Block Repair. In this blog I will demonstrate this feature.

The data block can be lost during write phase, zeroed out or corrupted. when the datablock is the first time used, it returns an error to the end user about the corruption.If Active Dataguard is enabled in your system then, on primary that block is restored from one of the active dataguards, no error is returned to the users. Everything occurs in the background and transparent to the users.

I assume the following points :
* block corruption has NOT been propagated to Dataguard
* you can access all the necessary redo logs and archive logs for recovering that block. (not much archivelog required if your lag is small)

Be careful you need extra licence to enable Active Dataguard.
http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC141”

Startup the dataguard as active dataguard.

SQL> startup mount ;     
Database mounted.

SQL> alter database open read only ;
Database altered.

SQL> select open_mode from v$database ;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.

SQL> select open_mode from v$database ;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Set up the Test Case on Primary Database

SQL> conn mehmeteser
Enter password: 
Connected.
SQL> create table junk_tbl tablespace JUNK_TBS as select * from dba_objects ;
Table created.

SQL> select *
  2    from (select distinct dbms_rowid.rowid_block_number(rowid) block_number
  3            from junk_tbl)
  4   where rownum  alter system flush buffer_cache;
System altered.

fun part Lets zero out the block on the test table block_number=1427

[oracle@host1 ~]$ dd of=/u01/datafile/VTYTEST_junk_tbs01  bs=8192 seek=1427 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.6328e-05 seconds, 226 MB/s

Let’s see What happens when we try to query the table:

WITH ACTIVE STANDBY

Block will be restored and recovered transparently in the background.

SQL> create table junk_tbl_bck tablespace junk_tbs as select * from junk_tbl;
Table created.

alert log :

Tue Jun 10 16:17:50 2014
Hex dump of (file 5, block 1427) in trace file /u01/app/oracle/diag/rdbms/vtytest/VTYTEST/trace/VTYTEST_ora_115127.trc
Corrupt block relative dba: 0x01400593 (file 5, block 1427)
Completely zero block found during multiblock buffer read
Reading datafile '/u01/datafile/VTYTEST_junk_tbs01' for corruption at rdba: 0x01400593 (file 5, block 1427)
Reread (file 5, block 1427) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 5, block# 1427)
Tue Jun 10 16:21:27 2014
Thread 1 advanced to log sequence 5211 (LGWR switch)
  Current log# 3 seq# 5211 mem# 0: +DATA/vtytest/onlinelog/group_3.299.839760905
  Current log# 3 seq# 5211 mem# 1: +DATA/vtytest/onlinelog/group_3.297.839760909
Tue Jun 10 16:21:27 2014
Archived Log entry 10399 added for thread 1 sequence 5210 ID 0x8cf991b8 dest 1:
Tue Jun 10 16:21:28 2014
Automatic block media recovery successful for (file# 5, block# 1427)
Tue Jun 10 16:21:28 2014

WITHOUT ACTIVE STANDBY

Manual restore is required. Rman block recovery can be used.

SQL> create table junk_tbl_bck tablespace junk_tbs as select * from junk_tbl;
create table junk_tbl_bck tablespace junk_tbs as select * from junk_tbl
                                                               *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1427)
ORA-01110: data file 5: '/u01/datafile/VTYTEST_junk_tbs01'

alert log :

Tue Jun 10 16:29:16 2014
Hex dump of (file 5, block 1427) in trace file /u01/app/oracle/diag/rdbms/vtytest/VTYTEST/trace/VTYTEST_ora_115127.trc
Corrupt block relative dba: 0x01400593 (file 5, block 1427)
Completely zero block found during multiblock buffer read
Reading datafile '/u01/datafile/VTYTEST_junk_tbs01' for corruption at rdba: 0x01400593 (file 5, block 1427)
Reread (file 5, block 1427) found same corrupt data (no logical check)
Errors in file /u01/app/oracle/diag/rdbms/vtytest/VTYTEST/trace/VTYTEST_ora_115127.trc  (incident=59009):
ORA-01578: ORACLE data block corrupted (file # 5, block # 1427)
ORA-01110: data file 5: '/u01/datafile/VTYTEST_junk_tbs01'
Incident details in: /u01/app/oracle/diag/rdbms/vtytest/VTYTEST/incident/incdir_59009/VTYTEST_ora_115127_i59009.trc
Tue Jun 10 16:29:16 2014
Corrupt Block Found
         TSN = 6, TSNAME = JUNK_TBS
         RFN = 5, BLK = 1427, RDBA = 20972947
         OBJN = 24975, OBJD = 24975, OBJECT = JUNK_TBL, SUBOBJECT = 
         SEGMENT OWNER = MEHMETESER, SEGMENT TYPE = Table Segment
Tue Jun 10 16:29:20 2014
Sweep [inc][59009]: completed
Errors in file /u01/app/oracle/diag/rdbms/vtytest/VTYTEST/incident/incdir_59009/VTYTEST_m000_130104_i59009_a.trc:
ORA-19583: conversation terminated due to error
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u01/datafile/VTYTEST_junk_tbs01'
ORA-01200: actual file size of 131072 is smaller than correct size of 131072 blocks
Tue Jun 10 16:29:20 2014
Dumping diagnostic data in directory=[cdmp_20140610162920], requested by (instance=1, osid=115127), summary=[incident=59009].
Tue Jun 10 16:29:43 2014
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s