Kevin Kempf's Blog

July 21, 2009

LogMiner & Advanced Compression

Filed under: 11g, advanced compression, LogMiner, Oracle — Tags: , — kkempf @ 8:06 pm

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:

  1. Archive log sequence# 23676 causes Dataguard apply to crash
  2. 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.

Advertisements

Blog at WordPress.com.