Home > Troubleshooting > ORA-00752 on standby and DB_ULTRA_SAFE parameter

ORA-00752 on standby and DB_ULTRA_SAFE parameter

Alert file from standby database got following messages:

ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 8, block# 351475)
ORA-10564: tablespace TD_DATA
ORA-01110: data file 8: ‘+TD_DATA/test/datafile/test01.dbf’
ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 105684

and second part

ORA-10877: error signaled in parallel recovery slave PR00
ORA-00752: recovery detected a lost write of a data block
MRP0: Background Media Recovery process shutdown (test1)

The alert file  shows that an ORA-00752 error is raised on the standby database
and the managed recovery is cancelled:

The recommended procedure to recover from such errors is a failover to the physical standby!!!

If you read Oracle Documentation

http://download.oracle.com/docs/cd/B28359_01/server.111/b28294.pdf

Chapter 13.6

During media recovery in a Data Guard configuration, a physical standby database
can be used to detect lost-write data corruption errors on the primary database. This is
done by comparing SCNs of blocks stored in the redo log on the primary database to
SCNs of blocks on the physical standby database. If the SCN of the block on the
primary database is lower than the SCN on the standby database, then there was a
lost-write error on the primary database.

When comes to corruption first thing I do is to run  dbv to verify file in question:

-sh-3.2$ dbv userid=system/test file=’+TD_DATA/test/datafile/test01.dbf’

DBVERIFY: Release 11.1.0.6.0 – Production on Wed Jan 6 15:38:08 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY – Verification starting : FILE = +TD_DATA/test/datafile/test01.dbf

DBVERIFY – Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 615
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 12
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 5707
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 66
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

No corrupted blocks.

Note:  I had to use USERID with dbv because my files are on ASM.

Additional check on ORA-00752  revealed that this error is related to db_lost_write_protect parameter.When upgrading to 11g I remember setting
DB_ULTRA_SAFE parameter to DATA_AND_INDEX value ( just to get protected )

DATA_AND_INDEX (recommended)

DB_BLOCK_CHECKING is set to FULL.
DB_LOST_WRITE_PROTECT is set to TYPICAL.
DB_BLOCK_CHECKSUM is set to FULL.

DB_ULTRA_SAFE in my case is set to DB_LOST_WRITE_PROTECT to TYPICAL.Already I was thinking to set DB_LOST_WRITE_PROTECT to NONE and
to resume media recovery process but after doing some research on metalink.

It turns out that my standby database hit  bug:

Bug 7426336 Standby may report a false lost write (ORA-752)

and I was right.Just changing db_lost_write_protect=’NONE’ and restarting recover process fixed issue:

ALTER SYSTEM SET db_lost_write_protect=’NONE’ SCOPE=BOTH SID=’*’;

and resume MRP process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

set linesize 160
col CLIENT_PID format a10
select process, pid, client_process, client_pid, status,
thread#, sequence#, block#, blocks
from v$managed_standby;

SQL> @standby_processes.sql

PROCESS          PID CLIENT_P CLIENT_PID STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———- ——– ———- ———— ———- ———- ———- ———-
ARCH           12336 ARCH     12336      CLOSING               2       7775     182273       1700
ARCH           12338 ARCH     12338      CLOSING               3       7783     139265        364
ARCH           12340 ARCH     12340      CLOSING               3       7784      55297       1198
ARCH           12342 ARCH     12342      CLOSING               3       7786     196609       1293
RFS            13572 LGWR     14368      IDLE                  2       7776      66324          1
RFS            13677 LGWR     16523      IDLE                  3       7787      80841          1
MRP0           28537 N/A      N/A        APPLYING_LOG          2       7776      66323     204800  — here is my process running again.
RFS            14562 UNKNOWN  14606      IDLE                  0          0          0          0
RFS            13685 UNKNOWN  17132      IDLE                  0          0          0          0

9 rows selected.

About these ads
Categories: Troubleshooting

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

Follow

Get every new post delivered to your Inbox.

Join 42 other followers

%d bloggers like this: