Monday, August 31, 2009

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description
In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned.

ORA-00939: too many arguments for function

But surprisingly on Oracle 9.2 database the same code does not return any error.

Cause of the Problem
As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255.

Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767.

Solution of the Problem
Option 01:
-If you are in oracle version 10.2.0.3 or earlier then enforce a restriction so that arguments of CASE statement does not exceed 255.

-Another thing to keep in mind that CASE statements can be nested. So if possible you can nest CASE statements and thus support your application to work.

Option 02:
Upgrade to oracle database 10.2.0.4 or higher where the arguments limit of CASE statement has increased to 32767.

How to determine the name of the trace file to be generated

In many cases we need to find out the name of the latest trace file generated in the USER_DUMP_DEST directory. What we usually do is go to physically to the USER_DUMP_DEST with the operating system browser and sort all the files by date and look for latest files. We can remove this hassle easily if we know what would be the trace file name in advance.

For example issuing,
D:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 10 21:58:15 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
will generated a trace file inside USER_DUMP_DEST. Let's see the location of USER_DUMP_DEST.

If you use Sql*plus then issue,
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP

Querying from v$parameter,
SQL> select value from v$parameter where name='user_dump_dest';

VALUE
-------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

On windows changes to directory E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP and issue,
E:\oracle\product\10.2.0\admin\orcl\udump>dir /OD

The latest files are for latest trace.

Now it would be quite easy task if we knew the name of the trace file to be generated by ALTER DATABASE command.

In advance we can get the trace file name by,


SQL> set linesize 130
SQL> COL trace_file FOR A60
SQL> SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');


SID SERIAL# TRACE_FILE
---------- ---------- ------------------------------------------------------------
146 11 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

So the trace file to be generated now will be named as orcl_ora_4968.trc

So now issuing, "alter database backup controlfile to trace" will generate the file named E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc.

On windows you can issue like this,
SQL> alter database backup controlfile to trace;

Database altered.

SQL> host notepad E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

ORA-27100: shared memory realm already exists

However if you merely see ORA-27100 error upon startup then proceed with this post.

Problem Description
When you try to startup your database instance even you issue startup nomount ORA-27100 is reported.

SQL> startup nomount
ORA-27100: shared memory realm already exists

Similarly, if you try to shutdown your oracle instance then ORA-27100 is reported.

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Restarting the service also does not help any. And even in some cases, rebooting the server does not help as well.

Current Changes in the Server
We had sga_max_size to 600M and then we change it to 1G. After that whenever we restart oracle database above message rises.

Cause of the Problem
Some changes are made in sga_max_size or sga parameters. After that database is abnormally shut down whenever you issue startup, ORA-27100 is received as a result of changes made to parameters affecting the SGA size.

Solution of the Problem
In most cases, ORA-27100 can be resolved by simply restarting Windows. However if avaialibility is too important and you can't afford the time of starting windows then,
- Move/ Rename database spfile so that next time starting database service can invoke old pfile to startup.

- Start the database service. Make sure pfile exist with old sga parameter settings.

- Now the service will start the database using pfile , with old SGA settings.
and you have started your database successfully.

- Create a new spfile from pfile.

Thursday, June 18, 2009

Oracle 10g New Features

The G stands for Grid Computing. A common missconception seems to be that grid is just the new name for RAC (having improved RAC) This is not the case. 10g comes with both RAC and grid. One will be able to install 10g with RAC only, with grid only, without either and with both. There is a profound difference between grid and RAC. 10g is said to have 149 new features. 10g provides a wealth of features that can be used to automate almost every aspect of its database administration. It is important to note that these automation features are optional, and they are not intended to replace standard DBA activities. Rather, the Oracle10g automation features are aimed at shops that do not have the manpower or expertise to manually perform the tasks.

Oracle Enhancements by Oracle Release
New Utilities in Oracle10g release 10.1.0:
• Oracle10g Grid – RAC enhanced for Oracle10g dynamic scalability with server blades (extra-cost option)
• Completely reworked 10g Enterprise Manager (OEM)
• AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options (extra-cost option)
• Automated Session History (ASH) materializes the Oracle Wait Interface over time (extra-cost option)
• Data Pump replaces imp utility with impdp
• Automatic Database Diagnostic Monitor (ADDM) (extra-cost option)
• Automatic Storage Management (ASM) introduced Stripe And Mirror Everywhere (SAME) standard
• Automatic Workload Repository (AWR) replaces STATSPACK (extra-cost option)
• SQLTuning Advisor
• SQLAccess Advisor
• Rolling database upgrades (using Oracle10g RAC)
• dbms_scheduler package replaces dbms_job for scheduling
• Set Database Default Tablespace syntax
• Rename Tablespace command
• Introduced RECYCLEBIN command for storing objects before they are dropped. Required new PURGE command for maintenance.
• sqlplus / as sysdba accessibility without quote marks
• SYSAUX tablespace
• Multiple Temporary Tablespaces supported to reduce stress on sorting in TEMP
• RMAN introduces compression for backups
• New drop database syntax
• New alter database begin backup syntax and alter database end backup. You don't need to specify the tablespaces one by one!!
• Oracle10g Data Guard Broker introduced
• Oracle10g RAC supports secure Redo Log transport
• Flashback enhancements for flashback database and flashback table syntax
• SQL Apply feature
• Cross Platform Transportable Tablespaces
• External Table unload utility
• SQL Regular Expression Support with the evaluate syntax
• New ROW TIMESTAMP column
• Automatic Database Tuning of Checkpoints, Undo Segments and shared memory
• Automated invoking of dbms_stats for CBO statistics collection
• Oracle Application Builder supports HTML DB
• Browser Based Data Workshop and SQL Workshop
• PL/SQL Compiler enhanced for compile-time Warnings in utl_mail and utl_compress

Click the Following Link for Full information Oracle 10g

RAC Design & Best Practices

RAC 10g R2 on Red Hat Linux Detail Design and Best Practices

Details followed in below link

RAC 10g


Copyright © 2008 Shaik Abdul Khaleel Technical blog. All rights reserved.