HTTP access_log crashes Oracle Enterprise Manager Grid Control 
Wednesday, May 12, 2010, 02:48 PM - Oracle, Oracle
Issue:

Once in a while, depending on the installation size, the apache httpd access_log grows to 2GB on the Oracle Enterprise Manager Grid Control installation.

Solution:

There are two possible solutions:
A. Correct the log rotate setup;
B. Stop the logging.
This solution is the solution to stop the logging (since it is pretty much useless anyway).
Edit the sysman/config/httpd_em.conf file and hash out the following lines:

ErrorLog /oracle/product/oms10g/Apache/Apache/logs/error_log
TransferLog "/oracle/product/oms10g/Apache/Apache/logs/access_log"


to

# ErrorLog /oracle/product/oms10g/Apache/Apache/logs/error_log
# TransferLog "/oracle/product/oms10g/Apache/Apache/logs/access_log"


Delete the old log files from Apache/Apache/logs and restart grid control. The logging should be stopped.

  |  permalink  |  
Fast recover of a dataguard 
Wednesday, May 13, 2009, 11:27 AM - Oracle
Issue:

If Oracle dataguard stops receiving data due to full flash recovery area and init.ora shows standby_file_management=AUTO a fast way of recovery is required.

Solution:

Reset the dg_broker_start parameter on the primary database to false (alter system set dg_broker_start=false). DG broker stops. Then reset back to true.
Eventual gap can be resolved by copying the archivelogs to the dataguard and adding those archivelogs:
alter database register logfile 'fullpathtoarchivelog/logname';

  |  permalink  |  
RMAN backup does not respond for long period 
Thursday, March 26, 2009, 02:43 PM - Oracle
Issue:

A backup with RMAN is after the actual backup is finished not responsive for a prolonged period of time.

Solution:

Probably the controlfile contains many unused delete object records:
Run a query "select count(*) from v$deleted_object". If this is as slow as the end of the RMAN session, you found the issue.
To solve this situation run:
SQL> execute dbms_backup_restore.resetcfilesection(19);
This effectively truncates v$deleted_object, resulting in fast backups again.
The issue should not occur anymore from Oracle 11.1.0.7 and higher.
  |  permalink  |  
Oracle Universal installer not starting 
Monday, March 2, 2009, 09:14 PM - Oracle, Oracle
Issue:

The Oracle Universal Installer (OUI) is not starting in Microsoft Windows. There is no error screen or anything relevant in the log files from the installer.

Solution:

The issue might be that the unzip action of the installer encounters a too long path, resulting in an error which is not logged. This is visible when trying to start the unzip step manually.
The solution is to place the installation software on a shorter path.
  |  permalink  |  
Archiver hung error in log while Oracle is not in archive log mode 
Wednesday, December 3, 2008, 10:34 PM - Oracle, Oracle
Issue:

You find an archiver hung error in log while Oracle is not in archive log mode (So archiver can not hang since it is not running....).

Solution:

Oracle is most likely connected with a database link to another database which is running in archive log mode. At that other database this exception occurred. The exception model of Oracle is such that the exception is communicate down to the requester of the action, which apparently requested some data coinciding with the occurance of the archive log error. This way the requesting database, which is not running in archive log mode is logging an archiver error from another database.
Solve the archiver error at the other database.
  |  permalink  |  
Oracle chooses full table scan over index while cost is equal 
Friday, October 31, 2008, 08:36 AM - Oracle
Issue:

Oracle 10/11 chooses in the cost based optimizer for a full table scan instead of using the index. This is done when the cost of both options are equal according to Oracle.

Solution:

There are 2 possible solutions:
1. Use a hint: It garantees that the index will be used all the time;
2. Oracle chooses alphabetically when the cost is equal: Rename your index to a place earlier in the alphabet than your table: This lets Oracle keep its behaviour but still get the index when the cost is equal.
  |  permalink  |  
Dataguard fails with divide by zero exception 
Friday, October 31, 2008, 08:20 AM - Oracle, Oracle
Issue:

Oracle 10.2.0.3 dataguard fails with a divide by zero exception crashing the memory recovery proces (MRP0).

Solution:

The solution is twofold:
1. Upgrade oracle to 10.2.0.4 (see metalink for details)
2. Just restart the process:
Login with sqlplus.
The database is still running, so just type:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Recovery should continu (archive logs will be applied).
  |  permalink  |  related link  |  
Using oracle plsql exceptions 
Tuesday, April 8, 2008, 11:18 AM - Oracle, Oracle
Issue:

You have an custom exception you want to catch in plsql.

Solution:

Create a custom exception:
declare
MISSING_DATA exception;
PRAGMA EXCEPTION_INIT(MISSING_DATA, exception_number);
begin
begin
Your SQL;
exception when MISSING_DATA then dbms_output.put_line(' exception caught');
end;
end;
/

The important part is to set the correct exception_number:
Run your sql and see what exception oracle throws as first exception (ORA-12345). Replace exception_number with -12345.


  |  permalink  |  
Oracle 11g installer fails 
Sunday, January 27, 2008, 02:42 PM - SuSE, Oracle
Issue:

The Oracle installer fails with the following error on SuSE 10.3:
java: xcb_xlib.c:52: xcb_xlib_unlock: Assertion `c->xlib.lock' failed

Solution:
The java based installer is not compiled on the local machine and is expecting certain libraries with a certain version. The comparison of these libraries can be relaxed by setting the following environment parameter:
export LIBXCB_ALLOW_SLOPPY_LOCK=0

Try to run the installer again. It should start.
  |  permalink  |  
Passing sql scripts to sqlplus with extra variables 
Monday, March 19, 2007, 03:32 PM - Oracle
Issue:
Using unix variables in an Oracle sql script with sqlplus.

Solution:
Use the following parameters to pass the sql to sqlplus:
$SQLDIR/sqlplus -silent $USER/$PASSWORD $SCRIPTNAME.sql PARAM

Important is the @ before the $SCRIPTNAME. The last parameter PARAM can be used in sqlplus with &INT where INT is the number of the parameter (in this case 1=> &1).
Also important is the SQL which you parse into sqlplus in this case: Put a quit on the last line to prevent sqlplus from not exiting in batchmode, for example if it is run from the cron deamon.


  |  permalink  |  
Oracle SQL Developer fails to update or delete a record 
Sunday, March 18, 2007, 07:44 PM - Oracle
Issue:
Oracle SQL Developer and other graphic userinterfaces use database properties to determine how to update or delete a row in a database. This can lead to mystery record locks which can not be traces.
No lock can be found in V$LOCK, no abnormal sessions can be found in V$SESSION.
Typical oracle where statement looks like:
WHERE ROWID = 'ABCDE+ABCDEFGHIJKL' AND ORA_ROWSCN = '123456'

Solution:
Type your own update statement instead of the SQL Developer generated one, and see if that works. If so, it is a problem with SQL Developer, else search on.

  |  permalink  |  
SET ECHO OFF but still SQL code visible in spool file 
Wednesday, March 7, 2007, 01:49 PM - Oracle
Issue

The SET ECHO OFF command in sqlplus does not produce the required result. In other words: You are still seeing SQL code in your logfiles or spoolfiles.

Solution

There is a sqlplus parameter which is also of influence. This parameter is: silent. If you call sqlplus with -silent, the SQL code will not show anymore.
  |  permalink  |