Monthly Archives: October 2009

Logrotating of files like default-web-access.log with help of Linux

What to do about the ever growing files on Oracle Application Server as:
default-web-access.log
server.log

and more..

These files are not logrotated (at least in the ‘older versions’ of OAS) and cannot always be deleted or renamed manually on a ‘normal’ way (I know, there is a way..), and it must be scripted to avoid extraordinary large log-files.
Quite annoying, until Frits Hoogland pointed me at a standard Linux-functionality, ‘logrotate’.

For example, to logrotate the files default-web-acces.log and server.log in the directory  $ORACLE_HOME/j2ee/home/log/home_default_island_1, create a script like this:

———-//—————-

/software/oracle/product/10.2/middle/j2ee/home/log/home_default_island_1/*.log {
daily
compress
delaycompress
dateext
maxage 30
rotate 7
size=+1024k
notifempty
missingok
copytruncate
}

———-//———–

(an explenation of these parameters follows at the bottom of this post,  pay special attention to ‘copytruncate’, which is very suitable for several files of the application server.)

This script should be (on Linux) in /etc/logrotate.d/  (as root).   Call it ‘ora_logrotate’ for example.
The Linux-program ‘logrotate’  is daily executed (from /etc/cron.daily/) and will run the scripts in /etc/logratate.d .

You can test this manually by running it like ‘/etc/cron.daily/logrotate ora_logrotate’. The file ora_logrotate will be executed when it is place in /etc/logrotate.d .
When there is a standardised environment you can also make a script like this:

By |October 27th, 2009|Categories: App. Server|Tags: , |0 Comments

Apex, HTTP-404 while clicking 'application builder'

Apex, version 3.2.1.00.10, on a 10.2.0.3-database is working fine for some time.

Then, while clicking the ‘application builder’ home-page, we got the ‘page not found’-error in the browser.

Logging of Apache shows:

mod_plsql: /pls/apex_schd/f HTTP-404 ORA-03113: end-of-file on communication channel\n
mod_plsql: Unable to reset state for mode 0: Err 3114 url=>/pls/apex_schd/f

Restarted the http-server and webcache: no result.
Configured the same apex on another application server, pointing to the same database: no result.

Allright, database alert-file shows:
ORA-07445: exception encountered: core dump [opidsa()+386] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

There were no changes (really, there weren’t…) in the past few days.

Points out to be a bug in 10.2.0.3 (with any version of Apex): bugnr 5745817 :

“Implementing regular flushes of the shared pool can prevent the problem from occurring.
The problem is fixed in 11g via non-published bug:5648872 and the fix is included in 10.2.0.4.” (note 418531.1)
The patch is also available: 5648872

For the time being, ‘alter system flush shared_pool’ is fine for me, until we upgrade to 10.2.0.4 (or 11gr2 of course… :-)  ).

By |October 26th, 2009|Categories: App. Server, Database|Tags: |1 Comment

Java-errors in 10.2.0.2 – ORA-29516 and ORA-03113

Why is it that java is crossing my path so much…

After a restore of a database on another node, including renaming him/her, java classes were invalid.
Example of the source code is at the bottom of this post by the way. RDBMS 10.2.0.2 on Suse Linux 9.2.
Trying to create / replace them, errors popped up:

ORA-29516: Aurora assertion failure: Assertion failure at eox.c:317
Uncaught exception Root of all Java exceptions:
java.lang.NullPointerException

After some thoughts about the causes like the renaming, environment settings etc., decided to go for the ‘inelegant’ way, such as removing java from the database, and installing it again:

@?/javavm/install/rmjvm.sql
shutdown immediate
startup
@?/javavm/install/initjvm.sql

After that, creating the java source was no problem(!), but while compiling it got the generic error:
–> ORA-03113: end-of-file on communication channel

But then..
It appeares I was connected through sql*net.  Didn’t expect  it matters so much…
A direct connect on the server (sqlplus “/as sysdba”) and ‘alter java source BlobHandler compile; ‘ did the job.

Sample source:

create or replace java source named BlobHandler as
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;

public class BlobHandler {

public static void ExportBlob(String myFile, BLOB myBlob) throws Exception {
// Bind the image object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

// Transfer the data
while ((length = inStream.read(buffer)) != -1) {
outStream.write(buffer, 0, length);
outStream.flush();
}

// Close everything down
inStream.close();
outStream.close();
}
};
/

alter java source BlobHandler compile;
show errors java source BlobHandler

create or replace procedure ExportBlob (p_file in varchar2, p_blob in blob)
as language java
name ‘BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
/

By |October 19th, 2009|Categories: Database|Tags: , |0 Comments

RMAN: restore database on another node including rename

m4s0n501

In my urge to write down some basic RMAN-checklists I could use in the future,  consider the following:

You’ve got no DataGuard at your disposal. Single node, relying on your RMAN-backup on disk, which is fortunately copied to tape. Now your complete node is burning down. What’s is left is your RMAN-backup (made by this script).
In a bad scenario you’ll have to restore this to another node.

No rocket science, but at that very moment you’re not very relaxed, and will be happy that it’s documented in the past by or for you how to do it. Also to be used to make a copy of the production-database, however I find the technique of ‘duplicating with RMAN‘  easier and more adequate for this purpose. Here are the basics of a restore on another node and give the database another name. Yes, it can be scripted and make it faster at certain points , but that’s not the point in this story….

Example: restore the database DEVDB (node LARRY) on the node ELLISON and give it the name DEVDBNEW.
The daily backups on LARRY are on  /local/data/oracle/rman/DEVDB

On ELLISON, create the same backup-directory:
As root:
>> mkdir –p /local/data/oracle/rman/DEVDB
>> chown –R oracle:oinstall /local
As an alternative: if there is already a /backup/data/oracle or something like that, use a symbolic link:
>> ln –s /backup local

The intention here is to copy the bunch from LARRY to ELLISON, but if it’s possible, you can always mount the directory ‘/local/data/oracle/rman/DEVDB’ on ELLISON to the same directory on LARRY, so you don’t have to copy.  Saves a lot of time, only be aware of the ‘..cfc..’-file. If the origin database is in ‘autobackup’ mode, this file will be overwritten by the origin database when there are changes.  Also the new database is using this file, while restoring and may also be in autobackup-mode.  So make a copy of the original file (which has  a date which is the end of the backup you are using).

Find out where the files should be placed:

select name from v$tempfile
union
select member from v$logfile
union
select name from v$datafile
union
select name from v$controlfile;

or: use ‘report schema’ in RMAN.

As Oracle:
Edit /etc/oratab on ELLISON, with the origin name, temporarily:
DEVDB:/software/oracle/product/10202:N
Run .oraenv (DEVDB), for the right environment.

Create directories for the database files (these ones already with the new db name):
>>mkdir –p /data10/oradata/DEVDBNEW/arch

Create new backup directories:
>> mkdir –p /backup/ELLISON/DEVDBNEW/export
>> mkdir –p /backup/ELLISON/DEVDBNEW/rman

Create directories for the admin files (with the new db name):
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/adump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/bdump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/cdump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/udump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/scripts
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/pfile
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/dpdump

By |October 18th, 2009|Categories: Database, RMAN|Tags: , |0 Comments

Cloning / duplicating with RMAN, the basics

It is considered  a skill every DBA should have, cloning a database with RMAN.  But still meeting DBA’s which never used this… Decided to write down some basic RMAN-stuff , just as a notepad, and maybe it’s helpfull for others..

My own basic checklist through an example:

Cloning of the  DEVDB on the node  LARRIE to node ELLISON, new database name DEVDBNEW.  The versions of the Oracle database on LARRY and ELLISON should be equal by the way!

On ELLISON:
– In order to get the backup on the same place where it stood on LARRIE ( RMAN gets this from the controlfile): create the same directory. E.g. create directory /backup/LARRIE/DEVDB/rman (on ELLISON!)

As root on ELLISON:
>> mkdir -p /backup/LARRIE/DEVDB/rman
>> chown -R oracle:oinstall /backup

– put the new database name  and home in /etc/oratab :
>> DEVDBNEW:/software/oracle/product/10.2:N

As oracle on ELLISON:
– run oraenv, for getting your right environment:
>> . oraenv (DEVDBNEW).

– Create a password file:
>> cd $ORACLE_HOME/dbs
>> orapwd file=orapwDEVDBNEW password=<password> entries=5

– Create directories for your datafiles:
>> mkdir -p /data/oradata/DEVDBNEW/arch
>> mkdir -p /data/oradata/DEVDBNEW/directories

– Create directories for your backup:
>> mkdir -p /data/oradata/backup/DEVDBNEW/export
>> mkdir -p /data/oradata/backup/DEVDBNEW/rman

– Create directories for the admin-files:
>> mkdir -p /software/oracle/admin/DEVDBNEW/adump
>> mkdir -p /software/oracle/admin/DEVDBNEW/bdump
>> mkdir -p /software/oracle/admin/DEVDBNEW/cdump
>> mkdir -p /software/oracle/admin/DEVDBNEW/udump
>> mkdir -p /software/oracle/admin/DEVDBNEW/scripts
>> mkdir -p /software/oracle/admin/DEVDBNEW/pfile
>> mkdir -p /software/oracle/admin/DEVDBNEW/dpdump

By |October 18th, 2009|Categories: Database, RMAN|Tags: , |0 Comments