Large TCP Socket (KGAS) event wait

One of our dev database has a large number of TCP Socket (KGAS) event waits when a piece of PL/SQL code runs.

SYS@dev>
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between
sysdate - 120/2880 and sysdate
group by active_session_history.event
order by 2 desc;

EVENT TTL_WAIT_TIME
------------------- -------------
TCP Socket (KGAS) 843316255
log file sync 1912981
.....

I check the Oracle reference of TCP Socket wait events, it says,

KGAS is a component in the server which handles TCP/IP sockets which is typically used in dedicated connections in 10.2+ by some
PLSQL built in packages such as UTL_HTTP and UTL_TCP.

However, in this particular piece of code, there's no such package called, Momen blogged about the same event when he's using SMTP package. But it looks like this doesn't apply to us.

http://momendba.blogspot.com/2007/03/tcp-socket-kgas-wait-event.html

I then looked into metalink, I found this Doc,

''TCP Socket (Kgas)'' Waits Present in 10.2
Doc ID:
Note:416451.1

It basically says this event is merely reporting some network related event, it's not threatening performance. The conclusion is this event can be safely ignored :D

Well, I hope Oracle could have fixed the bug in 10.2.0.4 and 11g, so that reporting of event in more DBA comforting method.

Oracle RMAN bug

I just hit an Oracle RMAN Bug while revising one of my RMAN backup scripts. I had a typo in my ORACLE_SID setting. So RMAN started without a target database connection, the script subsequently issued,

sql "alter system switch logfile";

Which crashed RMAN with ORA-600 numbers

corpdb 15 oracle %setenv ORACLE_SID ctest
corpdb 16 oracle %rman catalog rmancat/rman@rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 18:10:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)connected to recovery catalog database
RMAN> sql "alter system switch logfile";
sql statement: alter system switch logfile
DBGANY: CMD type=sql id=1 status=NOT STARTED
DBGANY: 1 STEP id=1 status=NOT STARTED chid=default
DBGANY: 1 TEXTNOD = -- sql
DBGANY: 2 TEXTNOD = begin
DBGANY: 3 TEXTNOD = krmicd.execSql(
DBGANY: 4 PRMVAL = stmt=>'alter system switch logfile'
DBGANY: 5 TEXTNOD = );
DBGANY: 6 TEXTNOD = end;
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-00600: internal error, arguments [6000] [] [] [] []
corpdb 17 oracle %

While runing other RMAN Command should result following errors,

RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 06/30/2008 18:04:48
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HPUX-ia64 Error: 2: No such file or directory

ORA-01882: timezone region %s not found

ORA-01882: timezone region %s not found

I got this error while running

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

A little research on metalink help solved the problem. Metalink has a Doc specifically explain how to fix this error. In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.
The Doc provided a convenience script to fix the problem. After running the script problem gone. For more information check,

Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade
Doc ID: Note:414590.1

Oracle licensing on multicore processor

With the increasing popularity of multi-core processor nowadays, it's unclear for a lot of people including DBAs how Oracle calculate the required license for certain server configuration.
A quick guideline is, for one server one core has processor licensing factor of .50, a quad core CPU require 2 processor licenses.
However there's a catch here, this equation is not apply to more than one multicore servers, for example RAC setup. Two multicore servers installed and/or running the program on 8 cores will need 8 multiplied by a core processor licensing factor of .75 equals 6. Therefore instead of 4 processor licenses this require 6 processor licenses.
Also note, When licensing Oracle programs with Standard Edition One or Standard Edition in the product name, a processor is counted equivalent to an occupied socket; however, in the case of multi-chip modules, each chip in the multi-chip module is counted as one occupied socket.

Please refer to following licensing document from Oracle for more detail.

Oracle Licensing

ORA-01466 and flashback query

Someone brought this up in one OTN forum post.

I did a quick search on google and found this,
http://www.adp-gmbh.ch/ora/err/ora_01466.html

It seems a viable explanation. However I really need some sort reference to verify the 5 minutes time frame. Why 5 minutes? Not 1 minutes for example.

Then I found metalink note,

Error ORA-01466 while executing a flashback query.
Doc ID:
Note:281510.1

It explains everything.
The reason is because smon_scn_time is updated every 5 minutes in 9i, please note, in 10g smon_scn_time is updated every 6 seconds which is much more reasonable and making it very unlikely to hit the error.

ORA-03115 unsupported network datatype

ORA-03115: unsupported network datatype or representation

This is an annoying error that comes and goes during one of your implementation project. The application is using an utility that connecting to database from Excel. The client is version 9.2.0.1 and database is 10.2.0.3

After a quick search on internet,
Metalink note
OCI Application Errors with ORA-3115
Doc ID: Note:460498.1
seems a hit. After I applied the workaround, the problem seems go away for now. The thing I don't like about this is

1. It applies not only between 9i and 10g, according to the notes, the problem exists between 10.2.0.3 and 11g as well.
2. The workaround is set CURSOR_SHARING to EXACT, which is not preferred setting if you having a lot of customized SQL running on your database.

Looks like Oracle didn't have a patch specifically for this bug yet. If anyone knows otherwise please drop a line.

ORA-39082 error while import using data pump

When you use data pump import (impdp) doing database migration, sometime you will get ORA-39082 error, for example,

ORA-39082: Object type ALTER_PROCEDURE:"DX"."UPDATE_MKT"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."UPDATE_SALES"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."CHECK_CURRENT"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."DELETE_PAST"
created with compilation warnings

After import, when issue

alter procedure UPDATE_MKT compile;
alter procedure DELETE_PAST compile;
etc.

They compiled successfully without any errors or warning.

The reason behind this is because data pump import create procedures before views, if your procedure have dependency on views then you will have the ORA-39082 compilation errors at import.

Even the problem can be easily fixed by recompile all procedures after import. This is a little inconvenience for DBA that need to do DB refresh regularly.

Or perhaps this is the way Oracle suggest us don't reference views in procedures :)

Also there are Bugs return similar error

Impdp Returns ORA-39082 When Importing Wrapped Procedures

Doc ID:
Note:460267.1