Leo

ORA-01426 After Upgrade to 10g

Oracle seems to have a tendency to make small changes to database code which impacts application in a big way. Sad part is, these changes are not always documented properly. One of these cases is what I encountered. A simple PL/SQL code errors out as below:

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

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000;
5 END;
6 /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

Same block works fine in any previous versions like 9i and 8i. As per Oracle this is “Expected Behavior”. Oracle used to perform number arithmetic. In 10g integer arithmetic is being used. Any application using such code would require to undergo change as follows:

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_v2 number :=10000000;
4 v_code NUMBER(20);
5 BEGIN
6 v_code := v_v1 * v_v2 ;
7* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_code NUMBER(20);
4 BEGIN
5 v_code := v_v1 * 10000000 ;
6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000.0;
5 END;
6 /

PL/SQL procedure successfully completed.

Using any of the above methods, error is not observed. It is although not clear why oracle made this change. Another hurdle in moving to 10g!!. Hope Oracle provides Database capture feature on 9i and Replay on 10g.

2 July, 2008 by 10g 0

ORA-00600 [12333]

ORA-00600 with [12333] is an error that I have encountered on multiple occasions. This post is to shed some light on what this error is all about.

To start with let us try to understand when does oracle report ora-00600 error. An ora-00600 error is raised in the exception handler section of Oracle’s c-program code. i.e. Coders have forseen certain exceptional situation that are potential threats to data integrity or memory integrity and written appropriate exceptional handlers to report a ora-00600 error with appropriate information about the exception condition.

Details like [12333] indicate or provide hints as to what caused the exception. ora-00600 [12333] is reported when the server recieves data from a client and the server cannot recognize the data format. This error mostly is because of issues with network and does not indicate any data corruption.

Few Common Causes & Suggestions:
1. An incompatible client software can cause such an internal error. Metalink document 207303.1 explains the supported combinations of clients and Server release.

2. An incompatible NLS settings on Client. Check NLS settings especially ORA_NLS33 for 9i, ORA_NLS10 for 10g.

3. Apart from this, it could be because of network issues, client software, TIMEOUT setting, etc. Try to narrow down the error to a particular client machine/ client software.

4. This can be because of an Oracle bug also. Refer to metalink document 428629.1 for list of known bugs with ora-00600 [12333].

As a last resort you can check this with Oracle Support for any new issues.

20 May, 2008 by 10g 0

CPU (Critical Patch Update) and N-apply

OVERVIEW

Oracle has been regularly releasing security fixes for all it’s products to take care of any security vulnerability. Since 2005 Oracle formulated a policy that it will release CPU patch ( bundle of security fixes) every quarter (January, April, July, and October).

CPU includes fixes for various Oracle products including Database. Initially Oracle decided to include security fixes, fixes that conflict with security fix and fixes for bugs that affect wide range of users.

With each CPU Oracle provides a brief overview of fixes included, but does not elaborate on the vulnerabilities as this information could be misused. CPU patches are applied using Opatch utility.

CONFLICTS – SUPER SET – SUBSET

Before moving any further let us understand what a patch conflict is. As we all know, Oracle is bunch of c-programs. Any patch makes one or more changes to the c program files. Two patches can make changes to the same or different sets of c-code. When a patch shares one or more files with an existing patch then it is known as CONFLICT.

If a old patch makes all the changes to the c-code as compared to the new patch plus a set of additional changes, then the old patch is the SUPERSET of new patch. On the contrary if an existing patch contains only some of the changes compared to the new patch being installed, then it is called a SUBSET. While apply any new patch, Opatch checks for these 3 conditions.

In case of a superset situation, Opatch does not install the new patch and in case of subset old patch is rolled back and new patch is installed. When opatch detects a conflict scenario, user can decide which patch to keep and which patch to rollback.

N-APPLY

While applying a cpu patch, users can face a patch conflict with an all ready applied patch. You can request Oracle for a merge patch. CPU patches are cumulative i.e. fixes included in Jan fix will be part of April fix.

To take care of all the conflicts reported by various users, Oracle releases CPU super merge patches mid-way between 2 CPU patch releases. CPU super merge patches are merge of a CPU patch and all the reported patches conflicting with a given CPU patch.

This entire process was time consuming. Conflicts also prevent application of CPU patches. To overcome this, Oracle has come up with a new concept of packaging cpu patches for 10.2.0.3 (not all platforms) and upwards since July 2007. With this new process Oracle has stopped including non-security fixes in CPUs. Oracle has come up with N-apply concept.

With the new N-apply concept, a cpu is divided into one or more molecules. Molecule is set of one or more bug fixes. This feature is specially use full when users encounter conflicts. Conflicts can be resolved at the molecular level. This provides us a technique to apply a part of cpu fixes while patch conflicts are resolved.

To make use of the n-apply feature ensure that you have latest version of opatch, run the command:

opatch napply -skip_subset -skip_duplicate

OPatch parameter -skip_subset, which is specified at CPU patch install, enables OPatch to determine that a merge patch that includes a molecule is already installed in the Oracle home, and to then skip application of the molecule patch.

OPatch parameter, -skip_duplicate, provides the additional benefit of detecting when a molecule patch has already been applied, as in the case of a previous CPU, and to skip application of it. This reduces the length of time required to do the n-apply CPU installation and minimizes the overall change to the Oracle home.

4 May, 2008 by 10g 1

Understanding of Oracle 11g Automatic Diagnostic Repository – Part 1

INTRODUCTION

In an endeavour to move towards self managed database and provide better support solutions, Oracle 11g introduces another new feature in 11g called the Automatic Diagnostic Repository or ADR. As the name implies ADR is a automatically managed file based repository of diagnostic information such as trace files, core dumps, alert log, etc… ADR is like a mini database managing traces of multiple instances and multiple prouducts (database, ASM, CRS, etc…) with metadata of these traces being maintained under a unified directory structure. ADR promises to use consistent diagnostic data structures across products. Set of tools bundled with ADR facilitate us in analyzing diagnostic data.

As an outcome of this feature:
– Critical errors captured in the diagnostic repository show up as alerts in EM. In cases where the issue is a known issue, Oracle recommends corrective actions to be implemented by DBA.
– It is intended to make it easy for DBAs to consolidate and transfer diagnostic data to Oracle support, thus facilitating faster resolution of the issue.

ARCHITECTURE

Starting with Oracle 11g R1, traditional *_DUMP_DEST parameters are ignored and the trace location is decided based on DIAGNOSTIC_DEST parameter. If this parameter is not set explicitly, it defaults to ORACLE_BASE environment variable if this is set, if this is not set, then it defaults to ORACLE_HOME/log.

DIAGNOSTIC_DEST is the ADR root directory also known as ADR base.

Each instance of each product stores diagnostic data in its own ADR home directory. For example, in a Real Application Clusters (RAC) environment with shared storage and ASM, each database instance and each ASM instance have a home directory within ADR.

Under ADR base are multiple ADR homes. ADR home is the root directory for all traces within an instance of a product. i.e. Each instance of a product has an ADR home. Directroy structure of ADR is pictorial represented as below:

Under the ADR base there will be a folder “diag”. Within this folder there will be seperate folders for each product like rdbsm, asm, crs, clients, lsnrctl, netcman, etc… Each of these folders will have seperate folders for every instance of the product.

For an instance of Oracle database, the structure is as depicted. All traces that used to be within USER_DUMP_DEST and BACKGROUND_DUMP_DEST are with trace folder. There is a seperate folder for alert log. A copy of the alert log in XML format is mainted in the alert folder.

Problem and Incident are two terms used frequently in ADR. All errors like the ora-600, ora-7445 or any other error like ora-470 that crashes database instance are termed as critical error. A critical error is termed as a Problem and is given a problem id by ADR based on it’s symptoms. Each occurrence of a critical error is termed as an Incident.

Incident directory contains multiple subdirectories, where each subdirectory is named for a particular incident, and where each contains dumps pertaining only to that incident.

Metadata directory contains ADR’s metadata.

HM directory contains health monitor reports which are part of ADR.

ADRCI – ADR command interpreter and EM support work bench are the two interfaces to ADR.

We will discuss these tools in our next post.

4 May, 2008 by 11g 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers