Oracle Archives » Page 4 of 14 » Musings of an IT Implementor

ORA-08103 in 11.2.0.3.3 in SAP With REVOKE

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 10.2.0.5.0 to 11.2.0.3, then SAP SBP 092012 applied (taking it to 11.2.0.3.3), 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=8321110422064Checking 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 V10 STYLE FILE HEADER: 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 Unlocked 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;

OWNER OBJECT_NAME OBJECT_TYPE ----- ----------- ------------------- 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;

OWNER SEGMENT_NAME TABLESPACE_NAME ----- ------------ ------------------------------ SAPR3 T063D PSAPLOADD

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.

HowTo: Use AWR snapshot data to provide a history of sequential read values

Scenario: You have changed your back-end storage hardware (SAN array or SATA disk storage) and you want to see a historical overview of sequential read times of your database.

It’s possible to query the Oracle AWR data (provided you have paid for the license), to provide a historical list of sequential read times acccording to the snapshots taken by AWR.
You are obviously limited to the amount of data retained by AWR and the frequency of the AWR snapshots.

set linesize 400 pagesize 400 SELECT event_start.snap_id, to_char(snap.begin_interval_time,'DD-MM-YY HH24:MI') as begin_time, to_char(snap.end_interval_time,'HH24:MI') as end_time, round(decode( (event_end.total_waits - nvl(event_start.total_waits, 0)),0, to_number(NULL), ((event_end.time_waited_micro - nvl(event_start.time_waited_micro,0))/1000) / (event_end.total_waits - nvl(event_start.total_waits,0)) ),0) avgwait, event_end.event_name event_name, (event_end.time_waited_micro - nvl(event_start.time_waited_micro,0)/1000000) total_ms, event_end.total_waits FROM dba_hist_system_event event_start, dba_hist_system_event event_end, dba_hist_snapshot snap WHERE event_end.snap_id = event_start.snap_id + 1 AND event_end.event_name = 'db file sequential read' AND event_start.event_name = event_end.event_name AND event_start.snap_id = snap.snap_id AND event_start.dbid = snap.dbid AND event_start.instance_number = snap.instance_number AND snap.begin_interval_time > SYSDATE - 14 -- max 14 days history. -- AND to_char(snap.begin_interval_time,'HH24') IN ('09','10','11','12','13','14','15','16','17') -- AND to_char(snap.begin_interval_time,'MI') = '50' ORDER BY event_start.snap_id;

NOTE: You can restrict the snapshot intervals used to provide “hourly” values by uncommenting the additional two lines.

impdp to Import Packages but No Overwrite / Create or Replace

Oracle say that you should be using impdp for all 11g import operations.

If you’re using impdp to import a dump file which contains some packages, you need to be aware that when you run an import with impdp, the package source is not automatically recreated i.e. your export will have exported the package create header with “CREATE PACKAGE …” and not “CREATE OR REPLACE PACKAGE …”.

Consequently, when you run the import of the package(s), if they already exist in the database, they will not be overwritten or replaced.
There is no command line option for impdp to change this feature (like “table_exists_action=replace” for TABLES).
You have to manually extract the package source from the exported dump file using the impdp “SQLFILE” command line option, which will create a separate SQL file containing the package sources.
Then modify the generated script file to change the “CREATE PACKAGE …” to “CREATE OR REPLACE PACKAGE …”.

If you’re running on UNIX, you can use the power of SED to accomplish this task:

> impdp <user>/<password> DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=scriptfile.sql

> cat scriptfile.sql | sed ‘s/^CREATE PACKAGE/CREATE OR REPLACE PACKAGE/’ > scriptfile2.sql

NOTE: You need the “^” in the command so that it only replaces the first occurrence of “CREATE PACKAGE” on a line, otherwise you might replace something you need.

Estimating Oracle Export Space Requirements

To estimate a full Oracle export space requirements, you can use DataPump.
The below command estimates for a full export of the database.
It makes use of the ESTIMATE_ONLY option on the expdp command line.

First you need to create your directory in the database:

> sqlplus / as sysdba


SQL> create directory dp_dump as '/your_path';
SQL> quit;
> expdp "/ as sysdba" full=y directory=DP_EXPORT logfile=full_exp_estimate.log  estimate_only=yes
...
Total estimation using BLOCKS method: 2.418 GB

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 18:50:48

The method above runs through each object to be exported and calculates the number of blocks relevant to the block size of each object, that will be required on disk.

If you have a large database and your statistics are up-to-date, then you could use the additional “ESTIMATE=STATISTICS” option, which uses the data gathered from the Oracle statistics collections to estimate the space required. This is a lot quicker but needs accurate stats.

The example above took 1 min.

With the “ESTIMATE=STATISTICS” option, it took 46 seconds, but estimated only 991.3 MB would be required (half as much as the BLOCKS method). There’s obviously some missing stats on objects in my DB.

How to Patch an Oracle Database Under SAP

Are you thinking of patching an Oracle database which sits under an SAP system?
If you have a specific bug and you’ve identified the Oracle patch number that fixes the bug, you’d be tempted to just download the patch from Oracle.

According to SAP, you should not download any patches from Oracle directly. As you know, the Oracle binaries themselves are slightly different for an SAP system.
Instead, if you have the Oracle patch number, search through the README files that come as part of the SAP Bundle Patch (SBP) for Oracle downloads located on the marketplace: https://service.sap.com/oracle-download to see if the Oracle patch is included in the bundle patch.

If you can’t see it in there, then it may be worth asking SAP to clarify if/when they may include it in the next bundle patch.
Each bundle patch is released monthly, but it may not mean that relevant Oracle patches older than a month are included in the bundle.

The bundle patches themselves are cumulative, so you only need to apply the latest one. It includes specific Oracle patches, plus a CPU patch (dependent on the date/time of the released SBP).

Remember to re-check the SAP notes about Oracle database parameters after applying SBPs, since SAP usually update the notes at each SBP release, to include any relevant _fix_control or event parameter settings.