Flashback Data Archive

In a wonderful post from Laurent , he has demonstrated how shutdown abort may prove harmful. To summarize:  he creates flashback data archive and assigns it to a table.  Inserts few values in the table , followed by commit and shutdown abort.  After bringing up the database, he creates a a new undo tablespace (undo2) and tells database to use new tablespace, followed by clean shutdown and startup. After this, old undo tablespace can’t be dropped as it has active transactions in it . I tried the same test case and received similar results:

 
 
SQL> drop tablespace undotbs1 ;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU9_1312457103$' found, terminate
dropping tablespace

 

I thought of doing some more experiments.
Lets dump the undo header of the problematic undo segment and surprisingly: No Active Transactions found. Here is the transaction table:
For readability, only relevant portions are shown
 
TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x05f6  0x0001  0x0000.00451063  0x00c0010a  0x0000.000.00000000  0x00000001   0x00000000  1312478443
   0x01    9    0x00  0x05f6  0x0002  0x0000.0045106d  0x00c0010a  0x0000.000.00000000  0x00000001   0x00000000  1312478443
   ....
   0x1c    9    0x00  0x05f6  0x001d  0x0000.00451628  0x00c0010c  0x0000.000.00000000  0x00000001   0x00000000  1312480283
   0x1d    9    0x00  0x05f6  0x001e  0x0000.00451693  0x00c0010c  0x0000.000.00000000  0x00000001   0x00000000  1312480585
   0x1e    9    0x00  0x05f6  0x0020  0x0000.004516a3  0x00c0010c  0x0000.000.00000000  0x00000001   0x00000000  1312480585
   0x1f    9    0x00  0x05f6  0x0001  0x0000.004516d6  0x00c0010c  0x0000.000.00000000  0x00000001   0x00000000  1312480616
   0x20    9    0x00  0x05f6  0x0021  0x0000.00456600  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1312480634
   0x21    9    0x00  0x05f6  0xffff  0x0000.0045660b  0x00c0010d  0x0000.000.00000000  0x00000001   0x00000000  1312480636


 

Further look at the EXT (ended) Transaction table:
 
 
  EXT TRN TBL::
  index  extflag    extHash    extSpare1   extSpare2
  ---------------------------------------------------
   0x00  0x00000000 0x00000000 0x00000000  0x00000000
   0x01  0x00000000 0x00000000 0x00000000  0x00000000
   ....
   0x1c  0x00000000 0x00000000 0x00000000  0x00000000
   0x1d  0x00000000 0x00000000 0x00000000  0x00000000
   0x1e  0x00000000 0x00000000 0x00000000  0x00000000
   0x1f  0x00008c00 0x00000000 0x00000000  0x00000000
   0x20  0x00000000 0x00000000 0x00000000  0x00000000
   0x21  0x00000000 0x00000000 0x00000000  0x00000000

Every slot except 0x1f showed something interesting.Though not sure what is extflag pointing to. Going back to the Transaction table and to the same slot shows undo block address.  

 
 
SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(12583180) as FILE#,
  2  dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(12583180) as BLOCK from dual;
     FILE#      BLOCK
---------- ----------
         3        268
SQL> alter system dump datafile 3 block 268;
System altered.

 

This is how the undo block looks like. 
 
 
UNDO BLK: 
xid: 0x0009.01f.000005f6  seq: 0x2da cnt: 0x14  irb: 0x14  icl: 0x0   flg: 0x0002

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ec4     0x03 0x1e5c     0x04 0x1df4     0x05 0x1d48    
0x06 0x1ce0     0x07 0x1c34     0x08 0x1b88     0x09 0x1adc     0x0a 0x1a74    
0x0b 0x1a0c     0x0c 0x1964     0x0d 0x18bc     0x0e 0x1814     0x0f 0x176c    
0x10 0x16c8     0x11 0x163c     0x12 0x15ec     0x13 0x1544     0x14 0x14d8    
 
“In case”,  this was an undo block that contained an active transaction, to go through the undo chain, we consult irb value which is 0x14. This gives the first block from where the undo chain starts.   So, lets search through the block for Rec# 0x14.
 
 
*-----------------------------
* Rec #0x14  slt: 0x1f  objn: 74023(0x00012127)  objd: 74023  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 4
*-----------------------------
uba: 0x00c0010c.02da.13 ctl max scn: 0x0000.004492a3 prv tx scn: 0x0000.00450ffc
txn start scn: scn: 0x0000.004516d6 logon user: 0
 prev brb: 12583178 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00416362  hdba: 0x00416361
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) 
.
SQL> select object_name from dba_objects where object_id=74023;
OBJECT_NAME
-------------------------------
TEST_TAB
 
Lets look at the flashback archive segments that got created when Flashback Archive (FLA_ARCH) was enabled for this object.
 
 
SQL>  select segment_name from dba_segments where tablespace_name='TEST_TS' and
  2  segment_name like '%74023';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_FBA_TCRV_IDX_74023
SYS_FBA_TCRV_74023
SYS_FBA_HIST_74023
SYS_FBA_DDL_COLMAP_74023

 

SYS_FBA_TCRV_% table holds start SCN for every transaction done on the table.
 
 
SQL> select startscn from SYS_FBA_TCRV_74023 order by 1;

  STARTSCN
----------
   4527830
   4552384
   4552433
   4552433
   4552433
   4552433
6 rows selected.


  

Compare the Start SCN reported with TXN start SCN mentioned in the Rec# 0x14 of undo block dump. txn start scn: scn: 0x0000.004516d6. Converting this to Decimal gives same SCN 4527830. So, its likely that this transaction actually caused issues.  Now, lets bring the old undo tablespace online and then restart the database.
 
 
SQL> alter system set undo_tablespace=undotbs1;
System altered

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             289410148 bytes
Database Buffers          222298112 bytes
Redo Buffers               22605824 bytes
Database mounted.
Database opened.
SQL> select segment_name from dba_undo_extents where tablespace_name='UNDOTBS1' and status='ACTIVE';
no rows selected

  

Dumping the same undo header clears the extflags in the transaction table. So, it can be concluded that there was no undo/block corruption but some pending cleanup actions in the old undo tablespace (due to Shutdown abort). Thats why the transaction table earlier didn’t report any Active Transactions in the undo header dump.