ORA-08103: object no longer exists

From the docs:

ORA-08103: object no longer exists
Cause: The object has been deleted by another user since the operation began, or a prior incomplete recovery restored the database to a point in time during the deletion of the object.
Action: Action: Delete the object if this is the result of an incomplete recovery.

It started innocently enough.



Weird, I can see it in the schema browser.



I can select it from the schema browser and view the table properties.



I can issue a SELECT *



Strangely, the Count Rows just fails silently.

Enough of this GUI crap.

From the server itself, but first, the specs:

Host: Ubuntu 10.10 Desktop
RAM: 24 GB
CPU: Dual Xeon QuadCore something or another
VirtualBox: 4.0.4 r70112

Guest: Oracle Enterprise Linux 5.5 (Carthage)
RAM: 12 GB
Processors: 4
Shared Folders: 3
Guest Additions installed.
Database: 11gR2

Describe the table:

sql>desc eligibility
 Name                    Null?    Type
 ----------------------- -------- ----------------
 FILE_ID                          NUMBER
 RECORD_ID                        NUMBER(38)
 RECIPIENT_ID                     VARCHAR2(12)
 MONTH_OF                         NUMBER(4)
 DISTRICT_CODE                    VARCHAR2(2)
 COUNTY                           VARCHAR2(2)
 ELIGIBILITY_FLAG                 VARCHAR2(1)
 HMO_FLAG                         VARCHAR2(1)
 NURSING_HOME_FLAG                VARCHAR2(1)
 MEDIPASS_FLAG                    VARCHAR2(1)
 AGE                              NUMBER(38)
 PROGRAM_CODE                     VARCHAR2(4)
 GENDER                           VARCHAR2(1)

Good.

SELECT COUNT(*)

sql>SELECT COUNT(*) FROM eligibility;
SELECT COUNT(*) FROM eligibility
                     *
ERROR at line 1:
ORA-08103: object no longer exists

OK.

sql>SELECT * FROM eligibility WHERE rownum < 11;

   FILE_ID  RECORD_ID RECIPIENT_ID   MONTH_OF DI CO E H N M        AGE PROG G
---------- ---------- ------------ ---------- -- -- - - - - ---------- ---- -
        52 1892424743 ACF9ASfEJrVw          1 07 48 6 0 0 0         27 MM P F
        52 1892424744 ACF9ASfEJrVw          2 07 48 6 0 0 0         27 MM P F
        52 1892424745 ACF9ASfEJrVw          3 07 48 6 0 0 0         28 MM P F
        52 1892424746 ACF9ASfEJrVw          4 07 48 6 0 0 0         28 MM P F
        52 1892424747 ACF9ASfEJrVw          5 07 48 1 0 0 0         28 MA R F
        52 1892424748 ACF9ASfEJrVw          6 07 48 1 0 0 0         28 MA R F
        52 1892424749 ACF9ASfEJrVw          7 07 48 1 0 0 0         28 MA R F
        52 1892424750 ACF9ASfEJrVw          8 07 48 1 0 0 0         28 MA R F
        52 1892424751 ACF9ASfEJrVw          9 07 48 1 0 0 0         28 MA R F
        52 1892424752 ACF9ASfEJrVw         10 07 48 1 0 0 0         28 MA R F

10 rows selected.

Hah. You are there, sort of. Try to fool it.

sql>SELECT COUNT(*) FROM ( SELECT * FROM eligibility );
SELECT COUNT(*) FROM ( SELECT * FROM eligibility )
                                     *
ERROR at line 1:
ORA-08103: object no longer exists

Barnacles.

If I remember correctly, this table took about 10 hours to load. Sometime later, I had another corrupted block, fortunately it was on a small table so I dropped it, the tablespace and the associated data file. Next time I restarted the server, I'm told to run fsck. Not good.

I run it with the -c switch. I can get back into it. Now this.

This is a DW type environment. ARCHIVELOG is not turned on. There is no backup.

So, should I drop and reload the dang thing? Or is it somehow recoverable?

Help. Winning answer will get a t-shirt.

Update 03/18/2011 10:46 PM EST
I tried Gary's method from below first, as it seemed the easiest to test out. There was a single bitmap index on the table, so I booted it:

DROP INDEX bmp_fileid_eligibility;

Now the test:

SELECT COUNT(*) FROM eligibility;

Waiting...

ORA-01578: ORACLE data block corrupted (file # 9, block # 2440127)
ORA-01110: data file 9: '/u01/app/oracle/oradata/TESTING/medicaid_01_04.dbf'
01578. 00000 -  "ORACLE data block corrupted (file # %s, block # %s)"
*Cause:    The data block indicated was corrupted, mostly due to software
           errors.
*Action:   Try to restore the segment containing the block indicated. This
           may involve dropping the segment and recreating it. If there
           is a trace file, report the errors in it to your ORACLE
           representative.

Great. Same situation as last time. I ended up rebuilding the entire VM. I've never had these kinds of problems before...perhaps there is something wrong with my system setup or possibly how I setup Linux? I don't know. Sadly, I currently lack the skills necessary to figure it out. More importantly, I lack the time.

Following Martin's suggestion from last time, I have determined that the corrupt block belongs to the table and not the index (duh, I just dropped it and got that error). Next up, Joel's suggestion, CTAS.

Update 03/19/2011 1:46 AM EST
No go with CTAS

Error starting at line 1 in command:
CREATE TABLE elig
  COMPRESS BASIC
  NOLOGGING
AS
SELECT *
FROM eligibility
Error at Command Line:6 Column:5
Error report:
SQL Error: ORA-01578: ORACLE data block corrupted (file # 9, block # 2440127)
ORA-01110: data file 9: '/u01/app/oracle/oradata/TESTING/medicaid_01_04.dbf'
01578. 00000 - "ORACLE data block corrupted (file # %s, block # %s)"
*Cause:     The data block indicated was corrupted, mostly due to software
            errors.
*Action:    Try to restore the segment containing the block indicated. This
            may involve dropping the segment and recreating it. If there
            is a trace file, report the errors in it to your ORACLE
            representative.

Update 03/19/2011 3:46 AM EST
After reviewing my notes, I decided to drop and reload. This wasn't the large table that I have, it was only 10 GB or so of compressed data. I was able to drop the load time by 67% by moving the parallel hint from the INSERT section to the SELECT section. Originally it looked like this:

INSERT /*+ append parallel( e, 8 ) */ INTO claims
SELECT
  col1,
  ...,
  col10
FROM ext_claims;

COMMIT;

Changing it to this sped things up dramatically

INSERT /*+ append */ INTO claims
SELECT /*+ parallel( e, 8 ) */
  col1,
  ...,
  col10
FROM ext_claims;

After it was complete this time...

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>host

[oracle@medicaid ~]$ rman target=sys/testing@testing

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 19 04:30:46 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTING (DBID=89312249)


RMAN> backup database;

Starting backup at 19-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=91 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
...
...
...
input datafile file number=00001 name=/u01/app/oracle/oradata/TESTING/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/TESTING/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TESTING/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TESTING/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TESTING/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAR-11
channel ORA_DISK_1: finished piece 1 at 19-MAR-11
piece handle=/u01/app/oracle/product/11.2.0/dbhome/dbs/07m7j6h6_1_1 tag=TAG20110319T043341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 01:05:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-MAR-11
channel ORA_DISK_1: finished piece 1 at 19-MAR-11
piece handle=/u01/app/oracle/product/11.2.0/dbhome/dbs/08m7jabs_1_1 tag=TAG20110319T043341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-MAR-11

Now I have a good backup. Will continue to do this after anything major changes.

Still need to figure out what is happening, Gary's suggestion below to check out the BLOCK related init parameters will be a start.

CyanogenMod 6.0.2 - Email Force Close

This week I managed to brick my phone, then with help unbrick it, and like an ID10T, I messing with it again.

After getting it fixed yesterday, I reinstalled CyanogenMod 6.0.2. Lo and behold, those Email Force Close errors showed up again. Great.

Fortunately I use GMail, so it doesn't really affect me, but it is very annoying.

With a trick I learned yesterday, I decided to see what was going on.

The trick is the Android Debug Bridge, or ADB. Among other things, it allows you to run shell commands on your phone. Since I have had Ubuntu for close to 2 years now, I'm naturally a Linux expert (quit laughing).

The specific command I am talking about is logcat. You plug your phone in, run adb logcat and you can see what's going on...it's a log file.

With it turned on, I went to applications and selected Email.



Sorry! The application Email (process com.android.email) has stopped unexpectedly. Please try again.

I then checked the log output...and what could it be? I'm thinking it's some sort of java error, but after reading more closely...

I/Database(19754): sqlite returned: error code = 1, msg = no such column: accountColor
D/AndroidRuntime(19754): Shutting down VM
W/dalvikvm(19754): threadid=1: thread exiting with uncaught exception (group=0x4001d7e0)
E/AndroidRuntime(19754): FATAL EXCEPTION: main

What's that? Database? Really?

A little further down:

E/AndroidRuntime(19754): java.lang.RuntimeException: Unable to create service 
com.android.exchange.SyncManager: android.database.sqlite.SQLiteException: no such column: 
accountColor: , while compiling: SELECT
  _id,
  displayName,
  emailAddress,
  syncKey,
  syncLookback,
  syncInterval,
  hostAuthKeyRecv,
  hostAuthKeySend,
  flags,
  isDefault,
  compatibilityUuid,
  senderName,
  ringtoneUri,
  protocolVersion,
  newMessageCount,
  securityFlags,
  securitySyncKey,
  signature,
  accountColor
FROM Account

(formatted for better readability)

So someone forgot to add a column? Nice.

Now I know I can put a database on there, I'll be more likely to write something for it. :)

Never Use RAISE_APPLICATION_ERROR Again

By Michael O’Neill
http://twitter.com/cleverideanet (professional)
http://twitter.com/oraclenude (personal)
oraclenerd articles

If you write Oracle PL/SQL, you know what RAISE_APPLICATION_ERROR is. It is an abomination of hard-coding and poor practice. If you didn't know that, I'm sorry I was the one who told you. I've written and used extensively an ultra-simple framework to eliminate RAISE_APPLICATION_ERROR from my code forever.
Here's an example (assume 11gR2) of something we all know we can do:

begin  dbms_output.put_line(1/0); end;

This will throw an unhandled ORA-01476 exception. We could write some meaningful handling of that with this:

begin 
   dbms_output.put_line(1/0);
exception
   when zero_divide
   then
      dbms_output.put_line('zero divide exception caught');
end;

This coding is elegant because Oracle has conveniently predefined an exception named ZERO_DIVIDE and a corresponding pragma for us. Unfortunately, Oracle has only 22 predefined exceptions. What happens when I do this:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
end;

This will throw an unhandled ORA-01861 exception. My option to handle this is less than meaningful because this is not a predefined exception:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when others
   then
      case sqlcode
         when -1861
         then
            dbms_output.put_line('literal does not match exception caught');
         else
            raise;
      end case;
end;

This leads me to the inevitable desire to create my own named exception and pragma, so I could have code that looks like this instead:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when error.ora_literal_string_mismatch
   then
      dbms_output.put_line('literal does not match exception caught');
end;

Understanding this, creating my own ERROR package with a friendly named exception and pragma for ORA-01861 leads me to the pattern of how to handle my own application exceptions, namely defining an exception and pragma.

But how does this get RAISE_APPLICATION_ERROR out of my life? Consider the ERROR abbreviated package source I use (full source: error.pks and error.pkb):

create or replace package error is
 
   package_name constant varchar2(32) := 'error'; -- in case you want to change the package name
 
   -- application exceptions and pragmas
 
   (snip)
 
   not_one_based constant string(64) := package_name || '.app_not_one_based';
   app_not_one_based exception;
   pragma exception_init(app_not_one_based, -20004);
 
   sparsity_not_allowed constant string(64) := package_name || '.app_sparsity_not_allowed';
   app_sparsity_not_allowed exception;
   pragma exception_init(app_sparsity_not_allowed, -20003);
 
   parameter_cannot_be_null constant string(64) := package_name || '.app_parameter_cannot_be_null';
   app_parameter_cannot_be_null exception;
   pragma exception_init(app_parameter_cannot_be_null, -20002);
 
   string_too_large constant string(64) := package_name || '.app_string_too_large';
   app_string_too_large exception;
   pragma exception_init(app_string_too_large, -20001);
 
   application_exception constant string(64) := package_name || '.app_application_exception';
   app_application_exception exception;
   pragma exception_init(app_application_exception, -20000);
 
   -- rdbms exceptions and pragmas
 
   (snip)
 
   literal_string_mismatch constant string(64) := package_name || '.ora_literal_string_mismatch';
   ora_literal_string_mismatch exception;
   pragma exception_init(ora_literal_string_mismatch, -1861);
 
   (snip)
 
   procedure throw(p_exception in varchar2);
 
   procedure throw
   (
      p_exception in varchar2
     ,p_message   in varchar2
   );
 
end;

You can see several user-defined exceptions and pragmas as well as the ORA_LITERAL_STRING_MISMATCH used in the previous example. The full source has more defined, but is not relevant to understanding the concept I am presenting.

Notice there is just one (overloaded) method, THROW. THROW is what I use instead of RAISE_APPLICATION_ERROR.

So, instead of this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      raise_application_error(-20000, 'I hate the letter b');
   end if;
end;

I use this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      error.throw(error.application_exception, 'I hate the letter b');
   end if;
end;

On its surface this doesn’t seem terribly interesting or useful. Below the surface, several powerful advantages are gained:

• A single ERROR package encapsulates a schema’s application exceptions and pragmas, giving me a consistent SQLCODEs returned to my C# code.
• No more, remembering what number to use in RAISE_APPLICATION_ERROR.
• Easier to understand code
• I can effectively organize my exceptions without sprawling them throughout a schema’s packages
• I can extend the ERROR package (and I have) to do many more things like logging or default messages for exceptions without writing that into my schema’s application packages.

How does it work? Taking a look at the body for the THROW method reveals all:

procedure throw
(
   p_exception in varchar2
  ,p_message   in varchar2
) is
begin
   begin
      begin
         execute immediate ('begin raise ' || p_exception || '; end;');
         -- exception is raised and immediately trapped
      exception
         when ora_plsql_compilation_error then
            throw(error.exception_does_not_exist, p_exception);
      end;
     
   exception
      when others then
         if sqlcode between - 20999 and - 20000
         then
            raise_application_error(sqlcode, p_message);
            -- this is the best/only use of raise_application_error
            -- and eliminates the need in application code
         else
            raise;
            -- nothing extra to do for extra for exceptions outside raise_application_error range
         end if;
   end;
exception
   when others then
      raise; -- finally, bubbles up the original throw() call
end throw;

There are few caveats I have using this development pattern. I don’t consolidate every exception I write into my ERROR package, only those exceptions that I want to bubble up unhandled to my C# code. I don’t feel it is necessary to have the same ERROR package in every application schema. In other words I don’t evolve every incarnation of my ERROR package when I’m adding exceptions and pragmas to one schema’s ERROR package. Finally, my ERROR package has a multitude more bells and whistles I’m not sharing in this post for clarity’s sake. If you are interested in a more extended version of my ERROR, let me know via Twitter (@cleverideanet)

Copyright © 2010 Michael O'Neill
Published by Permission on oraclenerd

TNS-12518: TNS:listener could not hand off client connection

Trying to start up XE on a windows machine I kept getting this:

TNS-12518: TNS:listener could not hand off client connection
Cause: The process of handing off a client connection to another process failed.
Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support.

I didn't install the database, if that helps.

Note to self, you should start the service prior to trying to connect.

ORA-08177 - II

So my p.i.t.a. DBA won't link back to me, I told him it would be helpful to have both of our perspective's linked up since we're working on the same problem, I'm gonna link to him.

Anyway, last week he was able to pull all the SQL statements from memory that were operating under the SERIALIZABLE isolation level. Pretty cool stuff. You can find his post here.

Also, on Thursday night I ran 4 simultaneous tests of approximately 500 transactions each. While running I found a whole bunch of table locks. Of those tables, I found any ENABLED triggers and DISABLED them. After disabling the 6 triggers, the 2000 or so transactions were successfully completed in about 60 seconds, and the locks on the tables were very brief.

Right before I went to bed I realized though that I had run them in the default mode of READ COMMITTED. I almost got up and re-ran but decided to wait until morning.

Disabling the 6 triggers again and this time doing

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

before each script fired, they all broke with ORA-08177 within seconds.

On my previous post pertaining to this error, Gary Myers left a well thought out comment that I wanted to highlight here:

The basic nugget is that every statement in the transaction happens as if it all happened at the same instant (ie the SCN that the tansaction started). In the default mode, each SQL runs as of the SCN the statement starts executing.

Wouldn't expect it on a straight SELECT (maybe a SELECT ...FOR UPDATE). The error crops up when you try to get a record in current mode (ie you want to update/delete it) and it has changed since the start of the transaction. In default mode, the statement would lock until that other transaction completed, then restart as of a new SCN. Since in SERIALIZABLE mode it can't use a new SCN, it just falls over.

The 'solutions' are (1) make the transactions complete faster so they are less likely to overlap and (2) grab all the locks you need as early as possible. Shouldn't be trigger related unless they have autonomous transactions.

"usage of serializable isolation level is a design error that leads to non-scalable applications"
Don't agree with the word 'error'. It is a design choice. When you have a transaction of multiple statements, in default mode, the statements do not see a consistent picture of the data - even though each statement is individually consistent. There's a balance between consistency and concurrency. SERIALIZABLE moves that balance a bit more towards consistency.

I've also mapped out the process that produces the error and it ain't pretty. Most points are spawned off by the various triggers. I believe there is an underlying design flaw here as this particular process should be short and sweet. Some of the triggers firing are maintenance of post-processing data which would probably be served better by an asynchronous process taking care of that.

ORA-08177 can't serialize access for this transaction

We've been getting this error recently and we are in the process of researching the problem.

I figured I might as well share some of my reference material in case someone else may find it useful.

First, what is it? From the docs:

ORA-08177: can't serialize access for this transaction
Cause: Encountered data changed by an operation that occurred after the start of this serializable transaction.

Action: In read/write transactions, retry the intended operation or transaction.

There are 4 isolation levels when it comes to transaction processing, all are based on the ANSI/ISO SQL standard SQL92:

• READ UNCOMMITTED
• READ COMMITTED
• SERIALIZABLE
• REPEATABLE READ

Oracle handles these the following ways:

READ UNCOMMITTED:
Oracle Database never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle Database.

READ COMMITTED
Oracle Database meets the READ COMMITTED isolation standard. This is the default mode for all Oracle Database applications. Because an Oracle Database query only sees data that was committed at the beginning of the query (the snapshot time), Oracle Database actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.

SERIALIZABLE
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

REPEATABLE READ
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

I've learned more about transaction isolation levels in the last few days than I ever cared to know.

READ COMMITTED is the default for Oracle. There's a possibility that our calling applications are using SERIALIZABLE via the driver. I've never worked with SERIALIZABLE.

According to the trace files it is happening on both INSERTs and SELECTs and we've seen multiple occurrences on the same 2 statements.

I suspect, but can't prove, it's (evil) trigger related. Any pointers as to how to prove that would be most helpful. Or any pointers in general in dealing with this issue.

Update
I did find this post where the author says:

Once more - in the vaste majority of cases usage of serializable isolation level is a design error that leads to non-scalable applications. Most databases enforce it with very restictive locks that kill concurrency and in case of Oracle and other databases that rely on multiversioning (PosgreSQL, Interbase) you have to be ready to failed ("Can not serialize") transactions. Latter is, usually, tolerable as soon as application is prepared for them (normally, it is enough just to restart transaction). For Oracle quite good discussion of this topic (as almost any other Oracle-related topic) may be found on asktom.oracle.com (http://asktom.oracle.com).

Unfortunately it was just a statement without the proof to back it up. I'm trying to get away from just making blanket statements without providing the evidence.

ORA-32031: illegal reference of a query name in WITH clause

I was trying to use the subquery factoring clause, WITH, in a query as it was a fairly small subset but called a number of times...with the same predicates.

I ran into the above mentioned error.

Here's my query:

WITH periods
AS
(
  SELECT datefrom, datethru
  FROM vw_periods
  WHERE periodtypeid = 'WEEK'
    AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
)
SELECT *
FROM periods;

(Thanks Tom!) Pretty simple right? That's what I thought.

  FROM vw_periods
       *
ERROR at line 6:
ORA-32031: illegal reference of a query name in WITH clause

Hmmm...what's that? Since I can't get to the docs right now, I'll point you here.

Cause:
forward or recursive reference of a query name in WITH clause is not allowed.
Action:
Correct query statement, then retry.

That's not very helpful.

What's the definition of the view?

CREATE OR REPLACE
VIEW vw_periods
AS
SELECT
  periodid,
  periodtypeid,
  datefrom,
  datethru
FROM periods;

Nothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it?

SQL>WITH per
  2  AS
  3  (
  4    SELECT datefrom, datethru
  5    FROM vw_periods
  6    WHERE periodtypeid = 'WEEK'
  7      AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
  8  )
  9  SELECT *
 10  FROM per;

DATEFROM  DATETHRU
--------- ---------
08-DEC-08 14-DEC-08
15-DEC-08 21-DEC-08
22-DEC-08 28-DEC-08
29-DEC-08 04-JAN-09
05-JAN-09 11-JAN-09
12-JAN-09 18-JAN-09
19-JAN-09 25-JAN-09
26-JAN-09 01-FEB-09

8 rows selected.

Of course it can!

Lesson? Don't try to name your subquery with the same name as the base table.