Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, March 14, 2013

ORA-08103 in in SAP With REVOKE

Print Friendly and PDF
UPDATE 10-06-2013, SAP have confirmed to me that fix for bug 15996357 is scheduled for SBP of August 2013.

UPDATE 22-04-2013, I can confirm this is Oracle bug 15996357, but the bug is still not fixed. 

Scenario: After an upgrade from to, then SAP SBP 092012 applied (taking it to, we were getting an error when running the following SQL:

SQL> revoke resource from SAPR3;

ERROR at line 1:
ORA-08103: object no longer exists

It is possible to grant the resource role (even though it’s deprecated in 11gR2), without any errors.

Following Oracle document 8103.1 “OERR: ORA-08103 object no longer exists / Troubleshooting, Diagnostic and Solution” (aptly named don’t you think), I performed the following actions as per the document:
  • We flushed the buffer cache: "alter session set events 'immediate trace name flush_cache level 1';"
  • Traced the SQL statement with EVENT 10236 & EVENT 10200 and a generic SQL session trace with BINDS.
  • Performed an "ANALYZE [table|index] VALIDATE STRUCTURE;" on all concerned tables & indexes found in the generic SQL session trace, with no corrupt objects reported.
  • We followed Oracle doc id 136697.1 (the DB is using DICTIONARY managed tablespaces), to check for overlapped extents, which returned no substantial findings.
  • The only warning from the hcheck.sql was for: "UPDATE STMT_AUDIT_OPTION_MAP set option#=234 WHERE NAME='ON COMMIT REFRESH';"
  • We ran RMAN "backup check logical validate database;" to check for corrupt blocks, with no rows reported in V$DATABASE_BLOCK_CORRUPTION at the end of run.
After reporting the issue to SAP, who subsequently forwarded the issue to Oracle, we were pointed to an issue captured in our SQL session trace.

There was one line that contained the text "ERROR":

WAIT #11529215045787303928: nam='db file sequential read' ela= 4337 file#=8 block#=2 blocks=1 obj#=-1 tim=8321110421922
EXEC #11529215045787303928:c=1000000,e=5300151,p=6873,cr=41312,cu=8,mis=0,r=0,dep=0,og=1,plh=0,tim=8321110422039
ERROR #11529215045787303928:err=8103 tim=8321110422064
Checking which data file was affected showed it was a SAP standard datafile:

SQL> select file_id,tablespace_name,file_name from dba_data_files where file_id='8';

Oracle support suggested we dump the block contents:

sqlplus /nolog
SQL> connect as sysdba
SQL> oradebug setmypid
SQL> set pages 100 lines 80
SQL> spool dump.log
SQL> alter system dump datafile '<data file path & name>' block 2;
SQL> oradebug tracefile_name
SQL> spool off
SQL> exit

The resultant trace file containing the dump of the block looked as follows:

Oradebug command 'setmypid' console output: <none>
Start dump data block from file <data file path & name> minblk 2 maxblk 2
Compatibility Vsn = 186646528=0xb200000
Db ID=3010416539=0xb36f4f9b, Db Name='DB1'
Activation ID=0=0x0
Control Seq=6514478=0x63672e, File size=3840=0xf00
File Number=8, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x20000002 (128/2)
scn: 0x0000.20a5a720 seq: 0x01 flg: 0x00 tail: 0xa7201001
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x9FFFFFFFEF506A00 to 0x9FFFFFFFEF508A00
9FFFFFFFEF506A00 10020000 20000002 20A5A720 00000100 [.... ... .. ....]
9FFFFFFFEF506A10 00000000 00000000 00000007 00000000 [................]
9FFFFFFFEF506A20 00000000 00000001 00000002 10200000 [............. ..]
9FFFFFFFEF506A30 00000000 00000001 00000002 20000004 [............ ...]
9FFFFFFFEF506A40 00000000 00000000 00000000 00000001 [................]
Repeat 1 times
9FFFFFFFEF506A60 00000000 00004DBC 00000000 20000003 [......M..... ...]
9FFFFFFFEF506A70 00000002 00000000 00000000 00000000 [................]
9FFFFFFFEF506A80 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
9FFFFFFFEF507A30 00000000 00000001 00010100 00010000 [................]
9FFFFFFFEF507A40 00000000 00000000 20000003 20000003 [........ ... ...]
9FFFFFFFEF507A50 00000000 00000000 00000000 00000000 [................]
Repeat 249 times
9FFFFFFFEF5089F0 00000000 00000000 00000000 A7201001 [............. ..]
Extent Control Header
Extent Header:: spare1: 0 spare2: 7 #extents: 1 #blocks: 2
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x20000004 ext#: 0 blk#: 1 ext size: 2
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Map Header:: next 0x00000000 #extents: 1 obj#: 19900 flag: 0x0
Extent Map
0x20000003 length: 2

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: USED lhd: 0x20000003 ltl: 0x20000003
End dump data block from file <data file path & name> minblk 2 maxblk 2

The type of block was specified as “DATA SEGMENT HEADER”.
In the Extent Header, we can see the object ID (obj#) is 19900, which matches an object:

SQL> set linesize 400
SQL> col object_name format A30
SQL> col owner format A10
SQL> select owner,object_name,object_type from dba_objects where object_id=19900;

----- ----------- -------------------
SAPR3 T063D       TABLE

This is confirmed by checking in DBA_SEGMENTS:

SQL> select owner, segment_name, tablespace_name from dba_segments where HEADER_FILE=8 and HEADER_BLOCK=2;

----- ------------ ------------------------------

The block looks good and data can be selected.

Oracle support finally confirmed that there is a bug in the Oracle 11gR2 core code.
If you perform the following SELECT statement:

SQL> select ts#, blocks,maxblocks,grantor#,priv1,priv2,priv3 from tsq$
where user#=(select user# from user$ where name = 'SAPR3')
and maxblocks=0;

If you have any results returned, then the inherent bug could cause the ORA-08103 error.
At the moment an Oracle bug has been filed. I’ll update further when I know the bug details and the SAP SBP that fixes it.

UPDATE 22-04-2013, I can confirm this is Oracle bug 15996357, but the bug is still not fixed. 

No comments: