Workaround and fix for ORA-error stack (00600[ktt_check_thershold-1])

I have got the below issue from the grid control as shown below:

Message:

Message=ORA-error stack (00600[ktt_check_thershold-1]) logged in D:\ORACLE\PRODUCT\10.2.0\ADMIN\ISPNTDDB\BDUMP\alert_ISPNTDDB.log.
Metric=Generic Alert Log Error
Metric value=Errors in file d:\oracle\product\10.2.0\admin\ispntddb\bdump\ispntddb_mmon_9296.trc:~ORA-00600: internal error code, arguments: [ktt_check_thershold-1], [524288], [524288], [1048576], [], [], [], []~ 

The issue was occurring on database  10.2.0.4.0 on windows 64bit 2003.

after investigating the below trace:

Name
--------
d:\oracle\product\10.2.0\admin\ispntddb\bdump\ispntddb_mmon_9676.trc

Sun Jan 05 12:37:20 2014
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:5577M/16381M, Ph+PgF:7222M/17821M
Instance name: ispntddb

Redo thread mounted by this instance: 1

Oracle process number: 27

Windows thread id: 9676, image: ORACLE.EXE (MMON)


*** SERVICE NAME:(SYS$BACKGROUND) 2014-01-05 12:37:20.062
*** SESSION ID:(462.42118) 2014-01-05 12:37:20.062
*** 2014-01-05 12:37:20.062
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktt_check_thershold-1], [524288], [524288], [1048576], [], [], [], []
check trace file d:\oracle\product\10.2.0\db_1\rdbms\trace\ispntddb_ora_0.trc for preloading .sym file messages
----- Call Stack Trace -----
ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr <- ktte_check_threshol
<- ktte_check_undo_tbs <- ktte_monitor_tsth <- 833 <- ktte_monitor_ts <- ksbcti
<- ksbabs <- kebm_mmon_main <- ksbrdp <- opirip <- opidrv
<- sou2o <- opimai_real <- opimai <- BackgroundThreadSta <- 0000000077D6B71A
---------
SO: 000000027255EC50, type: 4, owner: 000000026F3B9508, flag: INIT/-/-/0x00
(session) sid: 462 trans: 0000000000000000, creator: 000000026F3B9508, flag: (51) USR/- BSY/-/-/-/-/-
DID: 0001-001B-00003047, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS
service name: SYS$BACKGROUND
last wait for 'control file sequential read' blocking sess=0x0000000000000000 seq=19 wait_time=1453 seconds since wait started=1
file#=0, block#=13, blocks=1
Dumping Session Wait History
for 'control file sequential read' count=1 wait_time=1453
file#=0, block#=13, blocks=1
for 'control file sequential read' count=1 wait_time=2076
file#=0, block#=11, blocks=1
----
SO: 000000026F3B9508, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=27, calls cur/top: 00000002725E6480/00000002725E77F0, flag: (2) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: none
last post sent: 0 0 48
last post sent-location: ksoreq_reply
last process posted by me: 6f3b3328 1 14
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 000000026F4331C0
O/S info: user: SYSTEM, term: CAINCNTD01, ospid: 9676
OSD pid info: Windows thread id: 9676, image: ORACLE.EXE (MMON)
Dump of memory from 0x000000026F394998 to 0x000000026F394BA0
26F394990 0000000D 00000000 [........]
26F3949A0 6AA82350 00000002 00000010 000313A7 [P#.j............]
26F3949B0 725E77F0 00000002 00000003 000313A7 [.w^r............]
26F3949C0 726F31C0 00000002 0000000B 000313A7 [.1or............]
26F3949D0 7255EC50 00000002 00000004 0003129B [P.Ur............]
26F3949E0 726F7008 00000002 0000000B 000313A7 [.por............]
26F3949F0 726F7130 00000002 0000000B 000313A7 [0qor............]
26F394A00 6F7C4418 00000002 0000000D 000313A7 [.D|o............]
26F394A10 6C50A1A0 00000002 00000007 000313A7 [..Pl............]
26F394A20 726F7240 00000002 0000000B 000313A7 [@ror............]
26F394A30 726F7460 00000002 0000000B 000313A7 [`tor............]
26F394A40 6C50AF48 00000002 00000007 000313A7 [H.Pl............]
26F394A50 6C50CF70 00000002 00000007 000313A7 [p.Pl............]
26F394A60 6C50D730 00000002 00000007 000313A7 [0.Pl............]
26F394A70 00000000 00000000 00000000 00000000 [................]
Repeat 18 times
(FOB) flags=2 fib=000000026C9D4D20 incno=0 pending i/o cnt=0
fname=O:\ISPNTDDB\CONTROL03.CTL
fno=2 lblksz=16384 fsiz=1030
(FOB) flags=2 fib=000000026C9D4980 incno=0 pending i/o cnt=0
fname=O:\ISPNTDDB\CONTROL02.CTL
fno=1 lblksz=16384 fsiz=1030
(FOB) flags=2 fib=000000026C9D45E0 incno=1 pending i/o cnt=0
fname=O:\ISPNTDDB\CONTROL01.CTL
fno=0 lblksz=16384 fsiz=1030

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

Alert Log Content:

Wed Jan 01 10:56:46 2014
Restarting dead background process MMON
MMON started with pid=27, OS id=9668
Wed Jan 01 10:56:49 2014
Errors in file d:\oracle\product\10.2.0\admin\ispntddb\bdump\ispntddb_mmon_9668.trc:
ORA-00600: internal error code, arguments: [ktt_check_thershold-1], [1179648], [1179648], [1572864], [], [], [], []

Wed Jan 01 10:57:50 2014
Restarting dead background process MMON
MMON started with pid=30, OS id=8956
Wed Jan 01 10:57:53 2014
Errors in file d:\oracle\product\10.2.0\admin\ispntddb\bdump\ispntddb_mmon_8956.trc:
ORA-00600: internal error code, arguments: [ktt_check_thershold-1], [1179648], [1179648], [1572864], [], [], [], []

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

Reason for the issue:

When initially create the datafile with MAXSIZE specified and then resize the file > MAXSIZE and the datafile in autoextend mode then you may hit the bug.

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

Permanent fix:

Apply the patch number is 8392341.

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

Workaround:

Try to resize the datafile for both undo and temp and check if this will fix the issue.

If the issue still exist disable the autoextend and this should fix the issue.

Kind Regards

Mohamed ELAzab

ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt

Statistics Errors:

stats on table FND_CP_GSM_OPP_AQTBL is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP******

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Investigating the issue I find that the historgram contains duplicate records which it should has only one record. This is a known issue after upgrade and should be handled as per below:

The query result below show that we are impacted by this issue:

SQL> select column_name, nvl(hsize,254) hsize

from FND_HISTOGRAM_COLS

where table_name = 'JE_BE_LINE_TYPE_MAP'

order by column_name;  2    3    4

COLUMN_NAME                         HSIZE

------------------------------ ----------

SOURCE                                254

SOURCE                                254

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

TABLE_NAME                     COLUMN_NAME                      COUNT(*)

------------------------------ ------------------------------ ----------

JE_BE_LOGS                     DECLARATION_TYPE_CODE                   2

JE_FR_DAS_010                  TYPE_ENREG                              2

JE_FR_DAS_010_NEW              TYPE_ENREG                              2

JE_BE_LINE_TYPE_MAP            SOURCE                                  2

JE_BE_VAT_REP_RULES            SOURCE                                  2

JE_BE_VAT_REP_RULES            LINE_TYPE                               2

JE_BE_VAT_REP_RULES            VAT_REPORT_BOX                          2

JG_ZZ_SYS_FORMATS_ALL_B        JGZZ_EFT_TYPE                           2

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

I used the below to delete the obsoleted records:

SQL> delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and  column_name = '&COLUMN_NAME'

and rownum=1;  2    3    4

Enter value for table_name: JE_BE_LOGS

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_BE_LOGS'

Enter value for column_name: DECLARATION_TYPE_CODE

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'DECLARATION_TYPE_CODE'

1 row deleted.

SQL> /

Enter value for table_name: JE_FR_DAS_010

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_FR_DAS_010'

Enter value for column_name: TYPE_ENREG

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /

Enter value for table_name: JE_FR_DAS_010_NEW

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_FR_DAS_010_NEW'

Enter value for column_name: TYPE_ENREG

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /

Enter value for table_name: JE_BE_LINE_TYPE_MAP

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_BE_LINE_TYPE_MAP'

Enter value for column_name: SOURCE

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'SOURCE'

1 row deleted.

SQL> /

Enter value for table_name: JE_BE_VAT_REP_RULES

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_BE_VAT_REP_RULES'

Enter value for column_name: SOURCE

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'SOURCE'

1 row deleted.

SQL> /

Enter value for table_name: JE_BE_VAT_REP_RULES

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_BE_VAT_REP_RULES'

Enter value for column_name: LINE_TYPE

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'LINE_TYPE'

1 row deleted.

SQL> /

Enter value for table_name: JE_BE_VAT_REP_RULES

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JE_BE_VAT_REP_RULES'

Enter value for column_name: VAT_REPORT_BOX

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'VAT_REPORT_BOX'

1 row deleted.

SQL> /

Enter value for table_name: JG_ZZ_SYS_FORMATS_ALL_B

old   2: where table_name = '&TABLE_NAME'

new   2: where table_name = 'JG_ZZ_SYS_FORMATS_ALL_B'

Enter value for column_name: JGZZ_EFT_TYPE

old   3: and  column_name = '&COLUMN_NAME'

new   3: and  column_name = 'JGZZ_EFT_TYPE'

1 row deleted.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;  2    3    4

no rows selected

SQL> commit;

Commit complete.

I fixed the issue and rerun the above query and it returned 0 rows which means that the issue was killed.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;  2    3    4

no rows selected

Regards

Mohamed ELAzab

Oracle APPS Cloning || R12 Perl lib version doesn't match executable version

When i was cloning the development using the  adcfgclone.pl i got strange error.
The Error :
adcfgclone.pl dbTechStack

script returned:
****************************************************

.end std out.
Perl lib version (v5.8.4) doesn't match executable version (v5.10.0) at /usr/perl5/5.8.4/lib/sun4-solaris-64int/Config.pm line 32.
Compilation failed in require at /dbtop/proderpdb/11.2/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /dbtop/proderpdb/11.2/appsutil/clone/ouicli.pl line 35.

.end err out.
****************************************************

I found out it is Oracle bug in R12: and the Bug # 9724411.

Solution :

export PERL5LIB=/perl/lib/5.10.0:/perl/site_perl/5.10.0:/appsutil/perl

Regards

Mohamed ElAzab

What is the scan listener and how to deal with it?.

I will explain today the scan concept that was introduced with real application clustering in 11g release 2 and the benefit behind it.

The Single Client Access Name (SCAN) is a feature used in Oracle Real Application Clusters
environments that provides a single name for clients to access any Oracle Database running
in a cluster. You can think of SCAN as a cluster alias for databases in the cluster. The benefit
is that the client’s connect information does not need to change if you add or remove nodes or
databases in the cluster.

When you have  a single name to access the cluster to connect to a database in this cluster you  allow clients to use EZConnect and the  simple JDBC thin URL to access any database running in the cluster.You don't care about the  number of databases or servers running in the cluster and regardless on which server(s) in  the cluster the requested database is actually active.

Requirements to setup scan:
Oracle Real application clustering introduced grid infra structure strating from release 2.You must install the grid and it may have a different home that contains both Oracle Clusterware and Oracle Automatic Storage Management.
When installing the grid infrastructure you will be prompted to provide a SCAN name. There are 2 options for defining the SCAN:
1. Define a SCAN using the corporate DNS (Domain Name Service)
2. Define a SCAN using the Oracle Grid Naming Service (GNS)

1– Using the Corporate DNS:
If you will choose this path then you must ask your network administrator to create at least one single name  that resolves to three IP addresses. Three IP addresses are  recommended considering load balancing and high availability requirements regardless of the number of servers in the cluster.

• The IP addresses must be on the same subnet as your default public network in the cluster.
• The three IP addresses  must be using a round-robin algorithm.
• The name must be 15 characters or less in length, not including the domain.
•  The scan name must be resolvable without the domain suffix.
• The IPs must not be assigned to a network interface.

Example:

testing-scan.mydomain.com  IN  A 110.45.98.180

                                          IN  A 110.45.98.190

                                          IN  A 110.45.98.100

In order to check the SCAN configuration in DNS use “nslookup”. The DNS should provide 

round-robin access to the IPs resolved by the SCAN entry,You have to run the “nslookup” command at least  twice to see the round-robin algorithm work.The result should be that each time, the “nslookup”  would return a set of three IPs in a different order.

Round-robin on DNS level allows for a connection request load balancing across SCAN listeners floating in the cluster. It is not required for SCAN to function as a whole and the absence of such a setup will not prevent the fail over of a connection request to another SCAN listener, in case the first SCAN listener in the list is down. 

The Oracle Client typically handles failover of connections requests across SCAN listeners in the 

cluster. Oracle Clients of version Oracle Database 11g Release 2 or higher will not require any 

special configuration to provide this type of failover. Older clients require considering additional 

configuration.

It is therefore recommended that the minimum version of the client used to connect to a database using SCAN is of version Oracle Database 11g Release 2 or higher.

Using client-side DNS caching may generate a false impression that DNS round-robin is not occurring from the DNS server. (DNS not return a set of three IPs ). Client-side DNS 

caches are typically used to minimize DNS requests to an external DNS server as well as to minimize DNS resolution time. This is a simple recursive DNS server with local items.

If the client-side DNS cannot be set up to provide round-robin locally or cannot be disabled, Oracle Clients using a JDBC:thin connect will typically attempt a connection to the SCAN-IP and SCAN listener which is returned first in the list .In this case one of the three Ips was only used and basically disables the connection request load balancing  across SCAN listeners in the cluster from those clients, but does not affect SCAN functionality as a whole. Oracle Call Interface (OCI) based database access drivers will apply an internal round-robin algorithm and do not need to be considered in this case.

2– Using the Oracle Grid Naming Service (GNS):

You will only enter the SCAN name during the installation. At some stage in the cluster configuration, three IP addresses will be acquired from either a DHCP service or using 

“Stateless Address Auto Configuration” (SLAAC) when using IPv6 based IP addresses with Oracle  RAC 12c (using GNS, however, assumes that you use some form of dynamic IP assignment on your  public network) to create the SCAN. SCAN name resolution will then be provided by the GNS.

Workaround in case you cannot setup SCAN:

Oracle Universal Installer (OUI) enforces providing a default SCAN resolution during the Oracle Grid  Infrastructure installation, since the SCAN is mendatory during the creation of Oracle RAC 11g Release 2 or higher databases in the cluster. All Oracle Database 11g Release 2 or higher 

tools used to create a database .The Database Configuration Assistant (DBCA), or the Network 

Configuration Assistant (NetCA)) would assume its presence. Hence, OUI will not allow you to continue with the installation until you have provided a suitable SCAN resolution

If you want to overcome the installation requirement without setting up a DNS-based SCAN 

resolution, you can use a hosts-file based workaround. In this case, you would use a typical hosts-file entry to resolve the SCAN to only 1 IP address and one IP address only. It is not possible to simulate the round-robin resolution that the DNS server does using a local host file. The host file look-up the OS performs will only return the first IP address that matches the name Thus, you will create only 1 SCAN for the cluster. you will have to change the hosts-file on all nodes in the cluster for this purpose.