Because my code fix from Oracle doesn’t seem imminent, I decided to take a look at the offending archivelog from LogMiner’s perspective today, and see if I could glean any useful knowledge about what it was trying to do when it killed the Dataguard apply service. If nothing else, this serves as a good demo of how to see the contents of one archivelog in any environment; it’s not something I’ve used since RDBMS 8i days, and the Oracle documentation is pretty disorganized (see here).
Here are the “knowns” going into this experiment, which were easily obtained by looking at the alert log on my standby:
- Archive log sequence# 23676 causes Dataguard apply to crash
- The segment in question is BOM.CST_ITEM_COST_DETAILS
I started by restoring the “offending” archivelog, then invoking LogMiner and adding it to the current LogMiner worklist:
exec dbms_logmnr.add_logfile(logfilename => ‘/usr/tmp/PROD00010681000855__23676.arc’, options => dbms_logmnr.new);
Next I started LogMiner, using the existing dictionary from the online catalog (I tried a few other ways, such as a flat file export, but this was easiest):
exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
Now I wanted to see what operation was going on at the time of the block corruption:
select seg_owner ,seg_name ,operation ,sql_redo ,sql_undo from v$logmnr_contents where seg_owner = 'BOM' and seg_name = 'CST_ITEM_COST_DETAILS' ; SEG_OWNER SEG_NAME OPERATION SQL_REDO SQL_UNDO ---------- ------------------------- --------------- -------------------- -------------------- BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported BOM CST_ITEM_COST_DETAILS UNSUPPORTED Unsupported Unsupported ... 827 rows selected.
Yep, totally useless, 100% “Unsupported” value. It had been awhile since I’d used LogMiner, so I wanted to be sure I was doing it right; I took a (short) but otherwise random SCN and received expected results:
select
seg_owner
,scn
,seg_name
,operation
,sql_redo
,sql_undo
from
v$logmnr_contents
where
sql_redo != 'Unsupported'
and
scn = 5975016494077
;
SEG_OWNER SCN SEG_NAME OPERATION SQL_REDO SQL_UNDO
---------- -------------- ----------- --------------- -------------------- --------------------
APPLSYS 5975016494077 FND_LOGINS INSERT insert into "APPLSYS delete from "APPLSYS
"."FND_LOGINS"("LOGI "."FND_LOGINS" where
N_ID","USER_ID","STA "LOGIN_ID" IS NULL
RT_TIME","END_TIME", and "USER_ID" IS NUL
"PID","SPID","TERMIN L and "START_TIME" I
AL_ID","LOGIN_NAME", S NULL and "END_TIME
"SESSION_NUMBER","SU " IS NULL and "PID"
BMITTED_LOGIN_ID","S IS NULL and "SPID" I
ERIAL#","PROCESS_SPI S NULL and "TERMINAL
D","LOGIN_TYPE","SEC _ID" IS NULL and "LO
URITY_GROUP_ID") val GIN_NAME" IS NULL an
ues (NULL,NULL,NULL, d "SESSION_NUMBER" I
NULL,NULL,NULL,NULL, S NULL and "SUBMITTE
NULL,NULL,NULL,NULL, D_LOGIN_ID" IS NULL
NULL,NULL,NULL); and "SERIAL#" IS NUL
L and "PROCESS_SPID"
IS NULL and "LOGIN_
TYPE" IS NULL and "S
ECURITY_GROUP_ID" IS
NULL and ROWID = 'A
AElDrAAPAAAJaQACw';
At this point, I wasn’t sure what the relationship was between Unsupported and object types; a little digging around on Metalink and Doc 282994.1 makes it clear that certain actions and certain object types threw this message, but none of them were specific to 11g RDBMS. This in addition to the fact that the document was ancient made me keep looking.
I had a hunch that compressed tables were the cuplrit, and that they simply couldn’t display DDL information because of compression. However when I did a little more querying I found that a compressed table usually shows Unsupported under Redo and Undo DDL, but not necessarily always. By contrast, uncompressed objects would usually contain DDL but sometimes also show Unsupported in the Redo and Undo DDL columns of the view. I’m going to see if there’s a tie to the action type tomorrow, and I’ll update the post.
*edit* “Official” word from support is as so:
LogMiner does not support these datatypes and table storage attributes:
- BFILE datatype
- Simple and nested abstract datatypes (ADTs)
- Collections (nested tables and VARRAYs)
- Object refs
- Tables using table compression
- SecureFiles
This fails to explain why I had some advanced compression objects which did have visible redo/undo, but I grow tired of dealing with support.
