ORA-29516: Aurora assertion failure: Assertion failure

From Oracle 11g release 1 (11.1), Oracle introduces just-in-time(JIT) compiler for Oracle JVM environment, which results in faster execution because invalidation, recompilation, and storage of code is done dynamically.

JIT is controlled by parameter java_jit_enabled, and if it is set to TRUE then the Java methods are automatically compiled to native code by the JIT compiler and made available for use by all sessions.

But you can get the error Ora-29516 Aurora assertion failure on Linux x64bit platform, and the workaround to overcome that error is turning off the JIT compiler:

alter system set java_jit_enabled=false;

Profiled in Peer-to-Peer section of Oracle Magazine

Thanks to Oracle Magazine, I was profiled in the Peer-to-Peer section of Oracle Magazine.Vanessa Simmons, the Director of Marketing at Pythian has blogged about it here.

Pythian’s Oracle ACE, Fahd Mirza appears in this month’s Community: Peer-to-Peer review “In With the New“, as published in the September/October 2011 issue of Oracle Magazine.

Thanks Oracle and Pythian.

Script to start Oracle stats gathering in background from shell in Linux

Following is the script to gather stats for test.foo table. You can create a file at OS level and paste the following script:

touch gatherstats.sh
chmod a+x gatherstats.sh

then paste:

#!/bin/sh
sqlplus <<-EOF
/ as sysdba
set echo on timing on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'foo', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);
exit
EOF
echo -e "Stat completed for table FOO." | mail -s "Stat completed"


Now you can run the script in background:

nohup ./gatherstats.sh &

The script will email you upon completion.

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists Statistics Gathering

You might encounter the following error in your alert log file:

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

*** 2011-08-04 22:54:30.487
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"owner"','"tablename"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

On investigation, you tend to see that both the table and the dbms_stats objects exist and the statistics job failed just for this table. This might be a bug or more likely a transient hiccup with the statistics gathering job. You can verify that by just gathering the statistics manually for that table.

exec dbms_stats.gather_table_stats(ownname=>'',tabname=>'', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);

If it fails again, then you need to look into the accompanying trace file and log an SR for a likely bug.