DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Oracle Dataguard: data block corruption on SYSAUX

Hybrid View

  1. #1
    Join Date
    Sep 2009
    Posts
    11

    Oracle Dataguard: data block corruption on SYSAUX

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please check trace file ... can you see ORA-26040 reported after ORA-01110?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2009
    Posts
    11
    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.



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

  4. #4
    Join Date
    Sep 2009
    Posts
    11
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width