-
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
-
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.
-
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.
************************************************
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|