Data block corruption cleared with alter system flush buffer_cache

So I had:
ORA-08103: object no longer exists
being reported on SQL affecting one application table. But that table existed ok in dba_tables, could be described ok, and selects restricted to its indexed columns returned data ok.

Suspicion was some sort of data block corruption. Rman backup logs had not reported any corrpution, but dbverify did find some corrupt blocks. Those blocks were not associated with any record in dba_extents though.

All that is curious enough, but it is the fix which was the real surprise. Running:
SQL> alter system flush buffer_cache ;
Fixed the problem. Implication of that is that there is intermittent data block corruption, and that table was corrupt at the time it was first read into cache, and clean again (on disk) by the time I came along to look at it.

Dbverify later on also switched over to giving the datafiles a clean bill of health.

What’s the root cause? Don’t know (yet), but intermittent disk or memory hardware failure seems the most likely. Nothing reported in server logs though.

This on Oracle 10.1.0.4.0 Enterprise Edition on Microsoft Windows Server 2003 Standard Edition Service Pack 1 on Intel Xeon 3.2GHz.

The dbverify syntax I used was like:
dbv file=D:\ORACLE\ORADATA\SYSTEM01.DBF logfile=4.log
I didn’t have to specify block size because database was at the default block size for that version of dbv (seen with dbv help=y).

Free X windows emulator for running Oracle Installer

It’s often a pain getting X windows GUIs like the Oracle Installer and DBCA to run on Micrsoft Windows PC clients. Emulators like Reflection-X, KEA-Term, and Exceed work ok, but they cost money to licence and require source media and local admin rights to install. Those things can take an age to organise in large bureaucratic organisations.

An easy way round this is to download and run “DSL (Damn Small Linux) embedded”. It’s like having a linux desktop, but runs as a program inside Microsoft Windows. It’s free, and works as well or better than the commercial emulators for me - except on one old laptop without much memory where mouse control back inside Windows became erratic.

“DSL embedded” can be downloaded from here (look for dsl-[VERSION]-embedded.zip). Unzip that and run “dsl-base.bat” to start it up under Microsoft Windows. While using it, “Ctrl+Alt” switches back to Microsoft Windows applications. To close it, right click its desktop and select “power down”.

More details at damnsmalllinux.org

Data Guard today

Today had noticed that redo logs were not being applied to standby. Checking live database alert log showed errors like:

Bug? with wrong results from all_objects in stored plsql procedures

Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used.

Run this code to see the anomaly for yourself:

set serverout on
declare
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
create or replace procedure af_temp as
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
exec af_temp ;
drop procedure af_temp ;

It’s the exact same plsql each time, but very different results reported. For example:

all_objects: 13501

PL/SQL procedure successfully completed.

Procedure created.

all_objects: 4929

PL/SQL procedure successfully completed.

Procedure dropped.

This behaviour appears to occur on all versions - I tested from 7.3 through 10gR2. The biggest discrepancies in results seem to be with DBA users other than SYS, but all users show some discrepancy.

This issue doesn’t occur with the dba_ views, so best to use them instead in stored plsql objects. That does mean explicitly granting select privileges on the dba_ views being used in stored procedures, which isn’t necessary for the all_ views.

So, is this a bug? I can’t see any mention of it all in metalink.

It’s not just an academic issue either: it caused this code of mine to fail to find and drop the objects it was meant to drop.

Of course this would never happen in the real world

10046 tracing in another session

To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large):

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_bool_param_in_session(&1,&serial,'timed_statistics',true)
exec sys.dbms_system.set_int_param_in_session(&1,&serial,'max_dump_file_size',999999999)
exec sys.dbms_system.set_ev(&1,&serial,10046,8,'')

And to switch it off:

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_ev(&1,&serial,10046,0,'')

Note that you can run new (9i+) tkprof against old (8i and below) 10046 trace files in order to report the wait information. It is also possible to cat multiple trace files together before tkprof’ing.

Based on Alexander Bubernak’s post at http://www.dbasupport.com/oracle/ora10g/10046event.shtml

Clone a database

The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file and directory names, and change cp to rcp or scp if cloning to another server.

Database clones can also be done with rman, which has the advantage of avoiding the performance draining hot backup mode.

set pages 9999 lines 132 serverout on size 99999
BEGIN
  FOR t IN ( select distinct tablespace_name from dba_data_files )
  LOOP
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' begin backup ;') ;
    FOR f IN ( select file_name from dba_data_files where tablespace_name = t.tablespace_name )
    LOOP
      dbms_output.put_line('host cp '||f.file_name||' '||f.file_name ) ;
    END LOOP ;
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' end backup ;') ;
  END LOOP ;
END ;
/
set lines 80
select 'alter tablespace '||tablespace_name||' add tempfile '||file_name||' size '||bytes/1024/1024||' m ;'
from dba_temp_files
/
prompt alter system switch logfile ;;
prompt alter system backup controlfile to trace ;;

Slow SQL report

Lists slow SQL in library cache for tuning investigations:

set pages 9999
spool c.lst
select elapsed_time/1000000 secs, executions,
  elapsed_time/1000000/greatest(executions,1) secs_per_exec,
sql_text from v$sql
where executions > 50
and elapsed_time/1000000/greatest(executions,1) > 1
order by 3 desc
/
spool off
ed c.lst

For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.

Silent installer on unix still needs X windows until 10g

It’s not always easy to get X windows properly, or to get hold of X windows emulator software for Windows PCs. So it was a bit of a step backwards when the installer moved away from the ugly but effective clunky text display that it had in version 7 and below.

Running the installer in silent mode should have been a way around the need for X displays, but in fact it still fails (v8 through 9iR2) with an error like:

Exception java.lang.NullPointerException occurred..

So, even though you’re not going to use the installer GUI, oracle (for those versions) makes you have X windows all set up anyway. Quite annoying.

But - it’s fixed in 10g!

So oracle versions up to 7, and then from 10gR1 and above, don’t need X windows to install. Versions 8 up to 9iR2 do.

10gR2 Installation Guide says:

A.1.1 Reasons for Using Silent Mode or Noninteractive Mode
Use silent mode if you want to:

• Complete an unattended installation, which you might schedule using operating system utilities such as at
• Complete several similar installations on multiple systems without user interaction
• Install the software on a system that does not have X Window System software installed on it

And 10gR1 Installation Guide for Unix Systems:

Automated Installation Methods Using Response Files
By creating a response file and specifying this file when you start the Installer, you can automate some or all of the Oracle Database installation. These automated installation methods are useful if you need to perform multiple installations on similarly configured systems or if the system where you want to install the software does not have X Window system software installed.

To run an install in silent mode, copy a template response file from Disk1/response/*.rsp on the installation media, and edit it to include the install choices you want. Then run the installer with options:

./runInstaller -silent -responseFile filename.rsp

Actually there’s a little more to it than that, but see the installation guide for the full details.

Using 10g datapump and scheduler to copy schemas

Update 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below.

Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in this post.

For a nightly process to copy small schemas from one database to another, the normal, or old style, way to do that is with export/import in a cron controlled shell script, which would include a sql-from-sql script to drop all the target schema objects before doing the import.

But that presents problems in RAC, which is what I was working on - which node does the cron script reside on? What happens if that node is down?

An option would be to have the script run on another machine, one unrelated to the RAC cluster, but that introduces more points of failure, and means sending data back and forward over sql*net.

So instead I used the oracle’s 10g scheduler and datapump, as below. This data pumps straight over a database link, without having to write to a dump file in between, which is nice. And because it is all in the database, it ought to be unaffected by particular RAC nodes being down.