Oracle Dataguard: data block corruption on SYSAUX

[badkuk]

Hello All,

i recently found this under the alert logs on our logical standby db running 10.2.0.4.0 release 2:

**************************************************************************************************** ***
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 3341, D:\LSTDBY1\STREDO05.LOG

Thu Jan 21 09:53:45 2010

LOGMINER: Begin mining logfile for session 1 thread 2 sequence 3229, D:\LSTDBY1\STREDO09.LOG

LOGSTDBY Apply process P008 started with pid=67 OS id=3840

LOGSTDBY Apply process P006 started with pid=65 OS id=3620

LOGSTDBY Apply process P005 started with pid=64 OS id=3624

LOGSTDBY Analyzer process P003 started with pid=58 OS id=3632

LOGSTDBY Apply process P007 started with pid=66 OS id=3612

LOGSTDBY Apply process P004 started with pid=63 OS id=3628

Thu Jan 21 09:57:17 2010

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[1]: Assigned to RFS process 3108

RFS[1]: Identified database type as 'logical standby'

Thu Jan 21 09:57:17 2010

RFS LogMiner: Client enabled and ready for notification

RFS[1]: Successfully opened standby log 7: 'D:\LSTDBY1\STREDO07.LOG'

Thu Jan 21 09:57:19 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 09:57:29 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3354_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:06:35 2010

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[2]: Assigned to RFS process 2340

RFS[2]: Identified database type as 'logical standby'

Thu Jan 21 10:06:35 2010

RFS LogMiner: Client enabled and ready for notification

RFS[2]: Successfully opened standby log 10: 'D:\LSTDBY1\STREDO10.LOG'

Thu Jan 21 10:06:37 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:06:45 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\2_3242_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:07:19 2010

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[3]: Assigned to RFS process 3964

RFS[3]: Identified database type as 'logical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Thu Jan 21 10:07:19 2010

RFS LogMiner: Client enabled and ready for notification

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 7: 'D:\LSTDBY1\STREDO07.LOG'

Thu Jan 21 10:07:42 2010

Hex dump of (file 3, block 78089) in trace file d:\LSTDBY1\bdump\LSTDBY12_p001_3832.trc

Corrupt block relative dba: 0x00c13109 (file 3, block 78089)

Fractured block found during buffer read

Data in bad block:

type: 32 format: 2 rdba: 0x00c13109

last change scn: 0x0000.028f4ec4 seq: 0x3 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x02612001

check value in block header: 0x56d2

computed block checksum: 0x4ca7

Reread of rdba: 0x00c13109 (file 3, block 78089) found same corrupted data

Thu Jan 21 10:07:42 2010

krvxerpt: Errors detected in process 61, role builder.

Thu Jan 21 10:07:42 2010

krvxmrs: Leaving by exception: 1578

Thu Jan 21 10:07:42 2010

Errors in file d:\LSTDBY1\bdump\LSTDBY12_p001_3832.trc:

ORA-01578: ORACLE data block corrupted (file # 3, block # 78089)

ORA-01110: data file 3: 'D:\LSTDBY1\SYSAUX01.DBF'


LOGSTDBY status: ORA-01578: ORACLE data block corrupted (file # 3, block # 78089)

ORA-01110: data file 3: 'D:\LSTDBY1\SYSAUX01.DBF'

Thu Jan 21 10:07:43 2010

TLCR process death detected. Shutting down TLCR

logminer process death detected, exiting logical standby

LOGSTDBY Analyzer process P003 pid=58 OS id=3632 stopped

Thu Jan 21 10:07:45 2010

Errors in file d:\LSTDBY1\bdump\LSTDBY12_lsp0_3688.trc:

ORA-12801: error signaled in parallel query server P001, instance RAC6:reports2 (2)

ORA-01578: ORACLE data block corrupted (file # 3, block # 78089)

ORA-01110: data file 3: 'D:\LSTDBY1\SYSAUX01.DBF'



LOGSTDBY Apply process P004 pid=63 OS id=3628 stopped

LOGSTDBY Apply process P005 pid=64 OS id=3624 stopped

LOGSTDBY Apply process P006 pid=65 OS id=3620 stopped

LOGSTDBY Apply process P007 pid=66 OS id=3612 stopped

LOGSTDBY Apply process P008 pid=67 OS id=3840 stopped

Thu Jan 21 10:08:13 2010

Corrupt Block Found

TSN = 2, TSNAME = SYSAUX

RFN = 3, BLK = 78089, RDBA = 12661001

OBJN = 5812, OBJD = 5862, OBJECT = SYS_LOB0000005811C00008$$, SUBOBJECT =

SEGMENT OWNER = SYSTEM, SEGMENT TYPE = Lob Segment

Thu Jan 21 10:08:15 2010

LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action

LOGSTDBY status: ORA-16111: log mining and apply setting up

Thu Jan 21 10:08:15 2010

LOGMINER: Parameters summary for session# = 1

LOGMINER: Number of processes = 3, Transaction Chunk Size = 201

LOGMINER: Memory Size = 30M, Checkpoint interval = 150M

Thu Jan 21 10:17:24 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:17:25 2010

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 8: 'D:\LSTDBY1\STREDO08.LOG'

Thu Jan 21 10:17:33 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3356_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:27:16 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:27:16 2010

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 7: 'D:\LSTDBY1\STREDO07.LOG'

Thu Jan 21 10:27:31 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3357_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:37:23 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:37:24 2010

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 8: 'D:\LSTDBY1\STREDO08.LOG'

Thu Jan 21 10:37:31 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3358_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:40:58 2010

alter database stop logical standby apply

Thu Jan 21 10:40:58 2010

ALTER DATABASE STOP LOGICAL STANDBY APPLY

Thu Jan 21 10:40:59 2010

LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed

Thu Jan 21 10:40:59 2010

Completed: alter database stop logical standby apply

Thu Jan 21 10:41:32 2010

alter database stop logical standby apply

Thu Jan 21 10:41:32 2010

ALTER DATABASE STOP LOGICAL STANDBY APPLY

Completed: alter database stop logical standby apply

Thu Jan 21 10:47:16 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:47:16 2010

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 7: 'D:\LSTDBY1\STREDO07.LOG'

Thu Jan 21 10:47:34 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3359_698506430.ARC] to LogMiner session id [1]

Thu Jan 21 10:48:23 2010

alter database start logical standby apply immediate

Thu Jan 21 10:48:23 2010

with optional part

IMMEDIATE

Attempt to start background Logical Standby process

LOGSTDBY Parameter: DISABLE_APPLY_DELAY =

LOGSTDBY Parameter: LOG_AUTO_DELETE = FALSE

LOGSTDBY Parameter: REAL_TIME =

LSP0 started with pid=60, OS id=3668

Thu Jan 21 10:48:24 2010

Completed: alter database start logical standby apply immediate

Thu Jan 21 10:48:24 2010

LOGSTDBY status: ORA-16111: log mining and apply setting up

Thu Jan 21 10:48:24 2010

LOGMINER: Parameters summary for session# = 1

LOGMINER: Number of processes = 3, Transaction Chunk Size = 201

LOGMINER: Memory Size = 30M, Checkpoint interval = 150M

Thu Jan 21 10:49:29 2010

alter database start logical standby apply immediate

Thu Jan 21 10:49:29 2010

ORA-16103 signalled during: alter database start logical standby apply immediate...

Thu Jan 21 10:50:13 2010

alter database start logical standby apply immediate

Thu Jan 21 10:50:13 2010

ORA-16103 signalled during: alter database start logical standby apply immediate...

Thu Jan 21 10:57:16 2010

RFS LogMiner: Client enabled and ready for notification

Thu Jan 21 10:57:16 2010

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Successfully opened standby log 8: 'D:\LSTDBY1\STREDO08.LOG'

Thu Jan 21 10:57:28 2010

RFS LogMiner: Registered logfile [D:\LSTDBY1\STD\1_3360_698506430.ARC] to LogMiner session id [1]

**************************************************************************************************** ***


Unfortunately the Logminer dictionary resides on SYSAUX, as verified by querying V$SYSAUX_OCCUPANTS; the end result is that logical standby apply won't stick.

When i try to perform recovery on the SYSAUX tablespace, i get an ORA-283 error, stating that no recovery is necessary.


Any tips on how to go about this? Would it be possible to just drop SYSAUX, recreate it, then rerun DBMS_LOGSTDBY.BUILD to rebuild the objects/dictionary needed by LogMiner?

tia

[PAVB]

Please check trace file ... can you see ORA-26040 reported after ORA-01110?

[badkuk]

nope, didn't find any ORA-26040's

************************************************
Dump file d:\LSTDBY1\bdump\LSTDBY12_lsp0_1352.trc

Thu Jan 21 22:59:15 2010

ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Windows NT Version V5.2 Service Pack 2

CPU : 2 - type 8664, 1 Physical Cores

Process Affinity : 0x0000000000000000

Memory (Avail/Total): Ph:5970M/8089M, Ph+PgF:4473M/9694M

Instance name: LSTDBY12



Redo thread mounted by this instance: 2



Oracle process number: 63



Windows thread id: 1352, image: ORACLE.EXE (LSP0)





*** 2010-01-21 22:59:17.062

*** SERVICE NAME:(SYS$BACKGROUND) 2010-01-21 22:59:14.265

*** SESSION ID:(262.2) 2010-01-21 22:59:14.265

knahcapplymain: encountered error=12801

*** 2010-01-21 22:59:17.562

ksedmp: internal or fatal error

ORA-12801: error signaled in parallel query server P001, instance RAC6:LSTDBY12 (2)

ORA-01578: ORACLE data block corrupted (file # 3, block # 78089)

ORA-01110: data file 3: 'D:\LSTDBY1\SYSAUX01.DBF'

KNACDMP: *******************************************************

KNACDMP: Dumping apply coordinator's context at 3198d720

KNACDMP: Apply Engine # 0

KNACDMP: Apply Engine name

KNACDMP: Coordinator's Watermarks ------------------------------

KNACDMP: Apply High Watermark = 0x0000.01bc5d25

KNACDMP: Apply Low Watermark = 0x0000.01bc5d25

KNACDMP: Recovery Low Watermark = 0x0000.01bc5d25

KNACDMP: Fetch Low Watermark = 0x0000.01bc5df5

KNACDMP: Fetch Low Watermark Time = 708851746

KNACDMP: Oldest SCN = 0x0000.00000000

KNACDMP: Oldest XID = 0x0000.000.00000000

KNACDMP: Last replicant syncpoint SCN = 0x0000.028ff6d6

KNACDMP: Last syncpoint at primary SCN = 0x0000.01bc5d25

KNACDMP: First partition max SCN = 0x0000.01bc5dde

KNACDMP: Last partition max SCN = 0x0000.01bde47e

KNACDMP: Last processed SCN = 0x0000.01bc5df5

KNACDMP: Conservative SCN = 0x0000.01bc5df5

KNACDMP: Recovery start SCN = 0x0000.00000000

KNACDMP: Recovery high watermark = 0x0000.01bc5d25

KNACDMP: Coordinator's constants -------------------------------

KNACDMP: number of apply slaves = 5

KNACDMP: safety level (K) = 1

KNACDMP: max txns in memory = 400

KNACDMP: max constraints per table = 246

KNACDMP: hash table size (in entries) = 1000000

KNACDMP: Coordinator's intervals -------------------------------

KNACDMP: syncpoint interval (ms) = 0

KNACDMP: write low watermark interval(ms)= 1

KNACDMP: Coordinator's timers/counters -------------------------

KNACDMP: current time = 1264085960

KNACDMP: low watermark timer = 0

KNACDMP: syncpoint timer = 1264085953

KNACDMP: Coordinator's txn counts -------------------------

KNACDMP: total txns applied = 0

KNACDMP: total applied at last plwm write= 0

KNACDMP: apply prog. entries below plwm = 0

KNACDMP: Coordinator's State/Flags -----------------------------

KNACDMP: Coordinator's State = KNACST_APPLY_UNTIL_END

KNACDMP: Coordinator's Flags = 0x4

KNACDMP: Slave counts ------------------------------------------

KNACDMP: number of reserved slaves = 1

KNACDMP: number of admin slaves = 0

KNACDMP: number of slaves in wait cmt = 0

KNACDMP: number of slaves suspended = 0

KNACDMP: number of safe slaves = 0

KNACDMP: Slave Lists -------------------------------------------

KNACDMP: Dumping All Slaves :-

Slave id = 0, State = 8, Flags = 0, Not Assigned

Slave id = 1, State = 0, Flags = 0, Not Assigned

Slave id = 2, State = 0, Flags = 0, Not Assigned

Slave id = 3, State = 0, Flags = 0, Not Assigned

Slave id = 4, State = 0, Flags = 0, Not Assigned

Slave id = 5, State = 0, Flags = 0, Not Assigned

KNACDMP: End dumping all slaves

KNACDMP: syncdep slaves = { }

KNACDMP: cont chunk slaves = { }

KNACDMP: cont slaves = { }

KNACDMP: exec txn slaves = { }

KNACDMP:Idle slaves (5) ={ 1 2 3 4 5 }

*** 2010-01-21 22:59:27.593

KNACDMP: Txn Lists ---------------------------------------------

KNACDMP: Dumping all txns :-

KNACDMP: End dumping all txns.

KNACDMP: Complete txns = { ** NO UNASS ** }

KNACDMP: Unassigned txns = { }

KNACDMP: *******************************************************

Warning: Apply error received: ORA-26714: User error encountered while applying. Clearing.

*** 2010-01-21 23:00:10.859

knahcapplymain: encountered error=12801

*** 2010-01-21 23:00:11.031

ksedmp: internal or fatal error

ORA-12801: error signaled in parallel query server P001, instance RAC6:LSTDBY12 (2)

ORA-01578: ORACLE data block corrupted (file # 3, block # 78089)

ORA-01110: data file 3: 'D:\LSTDBY1\SYSAUX01.DBF'

KNACDMP: *******************************************************

KNACDMP: Dumping apply coordinator's context at 3198d720

KNACDMP: Apply Engine # 0

KNACDMP: Apply Engine name

KNACDMP: Coordinator's Watermarks ------------------------------

KNACDMP: Apply High Watermark = 0x0000.01bc5d25

KNACDMP: Apply Low Watermark = 0x0000.01bc5d25

KNACDMP: Recovery Low Watermark = 0x0000.01bc5d25

KNACDMP: Fetch Low Watermark = 0x0000.01bc5d25

KNACDMP: Fetch Low Watermark Time = 708851746

KNACDMP: Oldest SCN = 0x0000.00000000

KNACDMP: Oldest XID = 0x0000.000.00000000

KNACDMP: Last replicant syncpoint SCN = 0x0000.028ff6d6

KNACDMP: Last syncpoint at primary SCN = 0x0000.01bc5d25

KNACDMP: First partition max SCN = 0x0000.01bc5dde

KNACDMP: Last partition max SCN = 0x0000.01bde47e

KNACDMP: Last processed SCN = 0x0000.01bc5df5

KNACDMP: Conservative SCN = 0x0000.01bc5df5

KNACDMP: Recovery start SCN = 0x0000.00000000

KNACDMP: Recovery high watermark = 0x0000.01bc5d25

KNACDMP: Coordinator's constants -------------------------------

KNACDMP: number of apply slaves = 5

KNACDMP: safety level (K) = 1

KNACDMP: max txns in memory = 400

KNACDMP: max constraints per table = 246

KNACDMP: hash table size (in entries) = 1000000

KNACDMP: Coordinator's intervals -------------------------------

KNACDMP: syncpoint interval (ms) = 0

KNACDMP: write low watermark interval(ms)= 1

KNACDMP: Coordinator's timers/counters -------------------------

KNACDMP: current time = 1264086010

KNACDMP: low watermark timer = 0

KNACDMP: syncpoint timer = 1264086010

KNACDMP: Coordinator's txn counts -------------------------

KNACDMP: total txns applied = 0

KNACDMP: total applied at last plwm write= 0

KNACDMP: apply prog. entries below plwm = 0

KNACDMP: Coordinator's State/Flags -----------------------------

KNACDMP: Coordinator's State = KNACST_APPLY_UNTIL_END

KNACDMP: Coordinator's Flags = 0x204

KNACDMP: Slave counts ------------------------------------------

KNACDMP: number of reserved slaves = 1

KNACDMP: number of admin slaves = 0

KNACDMP: number of slaves in wait cmt = 0

KNACDMP: number of slaves suspended = 0

KNACDMP: number of safe slaves = 0

KNACDMP: Slave Lists -------------------------------------------

KNACDMP: Dumping All Slaves :-

Slave id = 0, State = 8, Flags = 0, Not Assigned

Slave id = 1, State = 0, Flags = 0, Not Assigned

Slave id = 2, State = 0, Flags = 0, Not Assigned

Slave id = 3, State = 0, Flags = 0, Not Assigned

Slave id = 4, State = 0, Flags = 0, Not Assigned

Slave id = 5, State = 0, Flags = 0, Not Assigned

KNACDMP: End dumping all slaves

KNACDMP: syncdep slaves = { }

KNACDMP: cont chunk slaves = { }

KNACDMP: cont slaves = { }

KNACDMP: exec txn slaves = { }

KNACDMP:Idle slaves (5) ={ 1 2 3 4 5 }

KNACDMP: Txn Lists ---------------------------------------------

KNACDMP: Dumping all txns :-

KNACDMP: End dumping all txns.

KNACDMP: Complete txns = { ** NO UNASS ** }

KNACDMP: Unassigned txns = { }

KNACDMP: *******************************************************

Warning: Apply error received: ORA-26714: User error encountered while applying. Clearing.



************************************************

[badkuk]

i recently came across articles on how to move the LOGMNR and LOGSTDBY objects from SYSAUX to another tablespace; is this a possible solution? Despite the reported corrupted data blocks, SYSAUX is still online.

In case this doesn't work...is it possible to

-- recreate the LOGMNR and LOGSTDBY objects on another tablespace
-- set LogMiner and Logical standby process to use this new tablespace, and
-- either manually resend all archive logs since day 1(the environment i set up is pretty young), or wait for Logminer/LOGSTBY processes to kick in


tia