ORA Errors – Persistent Storage Solutions

ORA-00600: internal error code, arguments: [kkdlGetCkyName1] – On ADG After Renaming Index

Published on July 11, 2012July 11, 2012 by AdvaitLeave a comment

I encountered wired error on my Active Dataguard (ADG) instance after renaming an index on primary

Here is a simple test case to reproduce the issue

On Primary:

SQL>create table T as select * from dba_objects;

Table created.

SQL>create index T_IDX on T(data_object_id);

Index created.

SQL>exec DBMS_STATS.GATHER_TABLE_STATS('ORACLE_DBA','T');

PL/SQL procedure successfully completed.

SQL>exec DBMS_STATS.GATHER_INDEX_STATS('ORACLE_DBA','T_IDX');

PL/SQL procedure successfully completed.

SQL>explain plan for
  2  select count(1) from T where data_object_id > 65000;

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    1 |    5 |    2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |    1 |    5 |           |      |
|*  2 |   INDEX RANGE SCAN| T_IDX | 29118 |   142K|    2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATA_OBJECT_ID">65000)

14 rows selected.

SQL>select count(1) from T where data_object_id > 65000;

  COUNT(1)
----------
     28337

On Standby:

SQL>select count(1) from T where data_object_id > 65000;

  COUNT(1)
----------
     28337

Now rename the index on primary

On Primary:

SQL>alter index T_IDX rename to T_IDX1;

Index altered.

SQL>

Now run the same query on standby

On Standby:

SQL>select count(1) from T where data_object_id > 65000;
select count(1) from T where data_object_id > 65000
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlGetCkyName1], [1622537], [],
[], [], [], [], [], [], [], [], []

DCYYZ1>

This is happening for following version

SQL>select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                  VERSION                  STATUS
---------------------------------------- ---------------------------------------- ----------------------------------------
NLSRTL                     11.2.0.2.0                  Production
Oracle Database 11g Enterprise Edition     11.2.0.2.0                  64bit Production
PL/SQL                     11.2.0.2.0                  Production
TNS for Linux:                 11.2.0.2.0                  Production

Solution: Oracle is working on Bug 13035388: ORA 600 [KKDLGETCKYNAME1] IN ADG for fixing this issue

Workaround: Rename back the index to original name. Or drop and recreate the index.

Hope this helps !!

Avoiding “no data found” : Tips

Published on April 29, 2011 by Advait4 Comments

Some time we face issue about no data found depending on selection criteria. And when this happens in PLSQL procedure we get annoying error

ORA-01403: No data found and execution stops.

One way to get around this error is to add exception block in our PLSQL procedure as shown below

EXCEPTION WHEN NO_DATA_FOUND THEN

But disadvantage of using this approach is that it will not process rest of the code and control will jump to exception block and from there on continue till the end. If we get this error in the middle of FOR loop or WHILE loop, rest of the records will not get processed.

One of the way to deal with this is joining your SQL with dual.

In that case you are sure to get a null value even if the row does not exists.

Example in my case I want to find the memory value and spfile value of one of the parameter in database by querying v$parameter and v$spparameter view.

If I do a simple join as below I am going to get “no data found” if these view does not have parameter

SQL> select a.name, a.value, b.value from v$parameter b, v$spparameter a
  2  where a.name = b.name
  3  and a.name = 'shared_pool_reserved_min_alloc';

no rows selected

SQL>

But if we join with dual we can get the name of the parameter and a null value in front of that

SQL> select c.col, a.value, b.value
  2  from v$parameter b, v$spparameter a, (select 'shared_pool_reserved_min_alloc' col from dual) c
  3  where c.col = a.name (+) and c.col = b.name (+);

COL                            VALUE                VALUE
------------------------------ -------------------- --------------------
shared_pool_reserved_min_alloc

SQL>

This is easier to process.

Hope this helps !!

WARNING: inbound connection timed out (ORA-3136)

Published on March 17, 2011 by AdvaitLeave a comment

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!

ORA-12705: Cannot access NLS data files or invalid environment specified

Published on June 2, 2008June 2, 2008 by Advait1 Comment

Some times we get this error in Oracle Applications R12 while running autoconfig for the first time on database side. This might be the case when we are cloning an environment or upgrading the database to new version. For example from 10g to 11g.

The cause for the error is that Oracle is not able to for NLS files in the environment. While running autoconfig, it checks for parameter s_db_oranls in context file on DB tier. This variable should point to correct NLS directory. Example of error is shown below.

=============================================================

afdbprf.sh started at Sun Jan 13 09:06:42 EST 2008
The environment settings are as follows …

ORACLE_HOME : /oracle/PROD/db/tech_st/10.2.0
ORACLE_SID : PROD
PATH : /oracle/PROD/db/tech_st/10.2.0/perl/bin:/oracle/PROD/db/tech_st/10.2.0/bin:/usr/bin:/usr/sbin:/oracle/PROD/db/tech_st/10.2.0/appsutil/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/oracle/PROD/db/tech_st/10.2.0/perl/bin:/oracle/PROD/db/tech_st/10.2.0/perl/bin:/oracle/PROD/db/tech_st/10.2.0/bin:/usr/bin:/usr/sbin:/oracle/PROD/db/tech_st/10.2.0/appsutil/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/oracle/PROD/db/tech_st/10.2.0/perl/bin:/oracle/PROD/db/tech_st/10.2.0/bin:/usr/bin:/usr/sbin:/oracle/PROD/db/tech_st/10.2.0/appsutil/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/oracle/PROD/apps/tech_st/10.1.3/perl/bin:/oracle/PROD/apps/tech_st/10.1.2/bin:/oracle/PROD/apps/apps_st/appl/fnd/12.0.0/bin:/oracle/PROD/apps/apps_st/appl/ad/12.0.0/bin:/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/jre/bin:/oracle/PROD/apps/apps_st/comn/util/unzip/unzip/unzip-5.50::/oracle/PROD/apps/tech_st/10.1.2/bin:/usr/bin:/usr/ccs/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/java/jre1.3.1_13/bin:/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/bin
Library Path : /oracle/PROD/db/tech_st/10.2.0/lib:/usr/X11R6/lib:/usr/openwin/lib:/oracle/PROD/db/tech_st/10.2.0/lib:/usr/dt/lib:/oracle/PROD/db/tech_st/10.2.0/ctx/lib

Executable : /oracle/PROD/db/tech_st/10.2.0/bin/sqlplus

SQL*Plus: Release 10.2.0.2.0 – Production on Sun Jan 13 09:06:42 2008

Enter value for 1: Enter value for 2: Enter value for 3: ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

ERRORCODE = 1 ERRORCODE_END

ERROR
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
<filename> <phase> <return code where appropriate>

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle/PROD/db/tech_st/10.2.0/appsutil/install/PROD_orcl-lma-01
afdbprf.sh INSTE8_PRF 1

[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle/PROD/db/tech_st/10.2.0/appsutil/install/PROD_orcl-lma-01
adcrobj.sh INSTE8_APPLY 1

AutoConfig is exiting with status 2

The log file for this session is located at:
/oracle/PROD/db/tech_st/10.2.0/appsutil/log/PROD_orcl-lma-01/01130906/adconfig.log

=============================================================

To fix this error change the context file to point the variable s_db_oranls to correct NLS directory. This variable should point to ORACLE_HOME/nls/data/9idata

Edit the database context file and change the following:
FROM
<ORA_NLS oa_var=”s_db_oranls”
osd=”unix”>/oracle/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data</ORA_NLS>
TO
<ORA_NLS oa_var=”s_db_oranls”
osd=”unix”>/oracle/PROD/db/tech_st/10.2.0/nls/data/9idata</ORA_NLS>

Run Autoconfig.

Hope this helps !!

ORA-12705: Cannot access NLS data files or invalid environment specified

Published on March 31, 2008 by Advait4 Comments

While running autoconfig in oracle apps on database side, you might encounter this error. Also you can see this when you do sqlplus from command prompt.

Cause:

Either your NLS_LANG parameter is not set correctly or NLS_ORA10 parameter is not set correctly.

Solution :

check $ORA_NLS10 path. This should point to ORACLE_HOME/nls/data/9idata

if this is not set correct, then you need to set the same manually using export command. Also if this is an oracle application database ( either 11i or R12), then make sure to change the variable s_db_oranls in CONTEXT_FILE and then run autoconfig.

If ORACLE_HOME/nls/data/9idata path is not present then you can create the same using $ORACLE_HOME/nls/data/old/cr9idata.pl script. Run this script and it will create the desired directories and after that set ORA_NLS10 parameter.

If this doesn’t resolve the issue then you need to check NLS_LANG parameter setting. This should be (Language)_(Territory).(character set). Example American_America.UTF8.

Hope this helps !!

ORA-00600: internal error code, arguments: [kcratr1_lastbwr]

Published on March 31, 2008March 31, 2008 by Advait17 Comments

Some time we encounter this error while starting the database. Below is the solution for recovering through this error.

Error:

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264916 bytes
Variable Size 264241900 bytes
Database Buffers 801112064 bytes
Redo Buffers 7122944 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []

Encountered in : 10.2.0.2.0 Database

Cause:

Oracle is unable to perform instance recover but it works when is invoked manually.

Solution:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264916 bytes
Variable Size 264241900 bytes
Database Buffers 801112064 bytes
Redo Buffers 7122944 bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

References

Metalink note ID: 393984.1