ORA-600 [2015] during transaction rollback in undo block for COMPRESS table

Problem Description
Supplemental Logging for OLTP-compressed table may corrupt UNDO block.

The Corrupt UNDO block results in the inability of tables from being read for transaction abort/recovery or consistent-read.

SMON may crash the database as it is unable to rollback the transaction associated to the corrupt UNDO segment.

This problem only occurs when Supplemental Logging is enabled for OLTP-compressed table.

Supplemental Logging is normally enabled when using Logical Standby Database, Golden Gate or Oracle Streams.

In RDBMS version 11.1 Supplemental Logging on COMPRESS tables is not supported so one of the workarounds described in the section below has to be used.

This restriction is lifted in 11.2g. However, the same problem is caused in 11.2g by bug 8331063.

Symptoms of the Problem

ORA-600 [2015] in Oracle foreground process or SMON background process in the presence of supplemental logging for COMPRESS tables.

This error is raised on undo application code paths and would show functions like ktundo on the stack.

If db_block_checking is enabled, the next errors can be produced with check code 2007 preventing the corruption going to disk:

ORA-600 [kdBlkCheckError] [file#] [block#] [2007]
ORA-600 [kddummy_blkchk] [file#] [block#] [2007]

and the trace file contains:

Block Checking: DBA = rdba, Block Type = Undo data block
ERROR: Undo Block Corrupted. Error Code = 2007
ktu4ubck: undo record #n with n change vectors exceeds allowed 293

Supplemental Logging can be enabled at database or table level.

Supplemental Logging at database level (any column value different than "NO" means that it is enabled):

select supplemental_log_data_min, supplemental_log_data_pk,supplemental_log_data_ui 
from v$database;

Supplemental Logging at table level for a COMPRESS table:

select t.owner, t.table_name
from dba_log_groups g, dba_tables t
where g.owner = t.owner
and g.table_name = t.table_name
and t.compression = 'ENABLED';

Solution of the Problem
If you are using 11.2g then install patch 8331063.

However if you are in 11.1 and in 11.2g you can follow following workarounds.
1) Set _lmn_compression=FALSE to disable supplemental logging on compress tables.

or,
2) Disable Supplemental Logging.

or,
3) Disable COMPRESSION by using,
'alter table ... MOVE NOCOMPRESS'. Don't use 'alter table ... NOCOMPRESS' statement.

Note that this statement does not repair existent corruptions but it will prevent them from future happenings.

ORA-00600 [kdtigetrow-2] or [kddummy_blkchk] During Insert

Problem Description
While performing an array of insert operation into a partitioned table it fails with either of the following two errors:

1. Error ORA-00600 [kdtigetrow-2]

Or,

2. If block checking is turned on, it may fail with: ORA-00600 [kddummy_blkchk].

Call stack functions from the trace file contains: kdtGetRow insidx insflush

For example I have created a partitioned table and during dataload I repeatedly encounter corruption ORA-600 [kdtigetrow-2]. Table is recreate to resolve corruption but fail again afterwards.
Table does not contain any LOB segments.
From the alert log,

Errors in file /u01/apps/oracle/udump/prod7_ora_1828.trc:
ORA-600: internal error code, arguments: [kdtigetrow-2], [10], [168], [858], [], [], [], []
.
Errors in file /u01/apps/oracle/udump/prod7_ora_1423.trc:
ORA-7445: exception encountered: core dump [kdtchg()+2824] [SIGBUS] [Invalid address alignment] [0xF821E214F] [] []

Hex dump of (file 103, block 581759) in trace file 
/u01/apps/oracle/bdump/prod7_dbw2_3652.trc

In the trace file,

Corrupt block relative dba: 0x19c8e07f (file 103, block 581759)  <---------- 
Bad header found during preparing block for write  <----------------- 
Data in bad block:
 type: 95 format: 4 rdba: 0x54315f49   <----------- not equal to   0x19c8e07f
 last change scn: 0xffff.50ffffff seq: 0xff flg: 0x00
 spare1: 0x45 spare2: 0x53 spare3: 0x180
 consistency value in tail: 0xffff5fff   <------- not matching either.....
 check value in block header: 0x0
 block checksum disabled

----- Call Stack Trace -----
ksedmp kgerinv kgeasnmierr kdtGetRow insidx insflush insrow insdrv inscovexe 
insExecStmtExecIniE ngine insexe ngine opiexe kpoal8 opiodr ttcpip opitsk  opiino opiodr opidrv sou2o opimai_real main 

Cause of the Problem The cause of the problem is due to Bug 5363584 (ARRAY INSERT INTO PARTITIONED TABLE CAN CORRUPT REDO). If block checking is on (db_block_checking initialization parameter is set to TRUE), corrupted QMI redo. If block checking is off (db_block_checking initialization parameter is set to FALSE), possible SGA corruption.

Explanation of the Error The error ora-00600 [kdtigetrow-2] is raised when an array of insert operations into a partitioned table fails. The failed row is deleted from the buffer and for the next insert the same buffer which has been trimmed by the delete is allocated and this operation corrupts the redo and the undo. If block checking is enabled the failing process would fail with the error ora-00600 [kddummy_blkchk] instead of ora-00600 [kdtigetrow-2] which aviods SGA corruption and induce a process failure.

Solution of the Problem
This Bug has been fixed in Oracle version 11.1 (11g) and is also included in the 10.1.0.6 and 10.2.0.4 patchsets.

In order to resolve the issue you can either:

1) Set the parameter _in_memory_undo=FALSE in the initialization parameter and restart the DB.

or,
2) Apply the one off patch for Bug 5363584, if available for your platform. Patch 5363584.

or,
3) Apply 10.1.0.6 or 10.2.0.4 if available for your platform.

ORA-600 [KDDUMMY_BLKCHK] WITH CODE 6145 SEEN FROM BEEHIVE UPDATE SQL

Problem Description
From alert log we find the followings:

Tue Jan 12 16:25:31 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1767853220_675 by 128 in tablespace SYSAUX 
Tue Jan 12 16:59:12 2010
Corrupt Block Found
         TSN = 7, TSNAME = BEE_DATA
         RFN = 1024, BLK = 3531726, RDBA = 3531726
         OBJN = 110167, OBJD = 110167, OBJECT = TM_INVS, SUBOBJECT = SYS_P44909
         SEGMENT OWNER = BEE_DATA, SEGMENT TYPE = Table Partition

and then following ORA-0600 [KDDUMMY_BLKCHK] WITH CODE 6145.

Errors in file /u01/oracle/trace/h44a_ora_22919.trc (incident=74345):
ORA-600: internal error code, arguments: [kddummy_blkchk], [15], [199541], [6145], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/trace/h44a_ora_22919_i74345.trc

From the trace file we find the followings:

Dump continued from file: 
/u01/oracle/trace/h44a_ora_22919.trc
ORA-600: internal error code, arguments: [kddummy_blkchk], [15], [199541], [6145], [], [], [], [], [], [], [], []


----- Current SQL Statement for this session (sql_id=4ndwumhpkqku4) -----
UPDATE /*+ index(o TM_OCCURS$pk)*/ TM_OCCURS O SET MODIFIED_ON = :B2 , 
CACHE_TS = :B2 , CACHE_SQ = CACHE_SQ+1 WHERE VISIBILITY=:B1 AND EID IN 
(SELECT /*+ index(i tm_invs$i_04) */ OCCUR_EID FROM TM_INVS I WHERE 
INVITEE_EID=:B8 AND ISERIES_EID=:B7 AND (:B5 IS NULL OR :B5 = :B6 OR (:B5 = 
:B4 AND EXCEPTION_LEVEL<=:B3 ))) RETURNING EID, SITE_ID, EXCEPTION_LEVEL, 
PARENT_EID, PARENT_TYPE, PARENT_SITE_ID, LOCK_ID, ORA_ROWSCN, CACHE_SQ INTO 
:O0 ,:O1 ,:O2 ,:O3 ,:O4 ,:O5 ,:O6 ,:O7 ,:O8 
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c0000004b7a84cb8       543  package body BEE_CODE.TM_INV_UPDATE_IMPL
c0000004b7a84cb8      5459  package body BEE_CODE.TM_INV_UPDATE_IMPL
c0000004dad02200       886  package body BEE_CODE.TM_INVITATION
c0000004bae3a4c0         1  anonymous block

Function List   (to Full stack)   (to Summary stack)
$cold_kco_blkchk  <- kcoapl <- kcbchg1_main <- kcbchg        
<- ktuapundo <- kdoiur <- kcoubk <- ktundo <- kturbk1         
<- ktrgcm <- ktrget2 <- kdsgrp <- qetlbr <- qertbFetchByRowID          
<- qergiFetch <- rwsfcd <- qersoProcessULS <- qersoFetch 
<- qerjoFetch <- qerupFetch <- updaul <- updThreePhaseExe 
<-  updexe <- opiexe <- opipls <- opiodr <- rpidrus <- skgmstack

Cause of the Problem This is Oracle bug 9350204 if DB_BLOCK_CHECKING is enabled then a session may raise a spurious ORA-600 [kddummy_blkchk] error on blocks of tables with row dependencies during consistent read operations even though there is no actual corruption.

Solution of the Problem Until now, Oracle has not fixed this bug. However workaround you can disable DB_BLOCK_CHECKING initialization parameter which is not advisable as it is required to be set for early detection of real block corruptions.

Allocate extent fails with ORA-00600: internal error code, arguments: [kddummy_blkchk]

Problem Description

SQL> create table lob_tab (a int, b clob)tablespace users
  2      lob (b) store as (disable storage in row)
  3      partition by range (a) (partition p1 values less than (100),
  4      partition p2 values less than (200))
  5      enable row movement;

Table created.

SQL> alter table lob_tab modify partition p1 lob (b) (allocate extent (size 100m));

Table altered.

SQL> alter table lob_tab modify partition p2 lob (b) (allocate extent (size 100m));
alter table lob_tab modify partition p2 lob (b) (allocate extent (size 100m))
*
ERROR at line 1:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [4], [2507],
[18038], [], [], [], []

From the alert log,

Errors in file d:\admin\a\udump\a_ora_4692.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [4], [2507], [18038], [], [], [], []

Thu Mar 17 15:55:31 2011
Doing block recovery for file 2 block 1513
Block recovery from logseq 85, block 542 to scn 3767234
Thu Mar 17 15:55:31 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 85 Reading mem 0
  Mem# 0: D:\ORADATA\A\REDO03.LOG
Block recovery completed at rba 85.1362.16, scn 0.3767235
Doing block recovery for file 4 block 2507
Block recovery from logseq 85, block 860 to scn 3767234
Thu Mar 17 15:55:31 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 85 Reading mem 0
  Mem# 0: D:\ORADATA\A\REDO03.LOG
Block recovery completed at rba 85.1362.16, scn 0.3767235
Thu Mar 17 15:55:32 2011
Corrupt Block Found
         TSN = 4, TSNAME = USERS
         RFN = 4, BLK = 2507, RDBA = 16779723
         OBJN = 56411, OBJD = 56411, OBJECT = SYS_LOB0000056406C00002$$, SUBOBJECT = SYS_LOB_P22
         SEGMENT OWNER = JULIA, SEGMENT TYPE = Lob Partition
Thu Mar 17 15:58:11 2011
Errors in file d:\admin\a\udump\a_ora_4692.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [4], [2507], [18038], [], [], [], []

From trace file,

*** 2011-03-17 15:55:29.234
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kddummy_blkchk], [4], [2507], [18038], [], [], [], []
Current SQL statement for this session:
alter table lob_tab modify partition p2 lob (b) (allocate extent (size 100m))
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedst+38           CALLrel  _ksedst1+0           0 1
_ksedmp+898          CALLrel  _ksedst+0            0
_ksfdmp+14           CALLrel  _ksedmp+0            3
_kgerinv+140         CALLreg  00000000             7F27FD0 3
_kseinpre+58         CALLrel  _kgerinv+0           
_ksesin+37           CALLrel  _kseinpre+0          
__VInfreq__kco_blkc  CALLrel  _ksesin+0            35455E0 3 0 4 0 0 9CB 0 0
hk+1302                                            4676 0
_kcoapl+1703         CALLrel  _kco_blkchk+0        869A1E8 11B4C000 4 2000 0
_kcbapl+157          CALLrel  _kcoapl+0            869A1E8 11B4C000 1 4 2000 0 0
_kcrfw_redo_gen+254  CALLrel  _kcbapl+0            869A1E8 11BF95F4 7F379A0 0 0
9                                                  
_kcbchg1_main+7588   CALLrel  _kcrfw_redo_gen+0    
_kcbchg1+191         CALLrel  _kcbchg1_main+0      0 2 8699D34 8699D14 0 0
_ktuchg+1002         CALLrel  _kcbchg1+0           0 2 8699D34 8699D14 0 0
_ktbchg2nt+48        CALLrel  _ktuchg+0            0 869A004 2 0 0 869A248
                                                   869AC18 869A1E8 0 0
_kteopgen+1837       CALLrel  _ktbchg2nt+0         0 869A004 0 0 869A248 869AC18
                                                   869A1E8 0 0
_kteopadd+1754       CALLrel  _kteopgen+0          869AC18 869A654 0 869A248
                                                   869A1E8 869A1E8 0 0 0 0 1
_ktsxs_add+2196      CALLrel  _kteopadd+0          869AC18 869A654 1004109 80 2
                                                   0 869ADB8 869ADBC 0
_ktsxsfr_fadd+3932   CALLrel  _ktsxs_add+0         869AC18 869AD54 80 4 3 1 1 2
                                                   869ADB8 869ADBC 0 0 869ACE0
_ktrsexec+372        CALL???  00000000             869AFB4
_atbale+368          CALLrel  _ktrsexec+0          869AFB4 32000 2000
_kkblalsi+2259       CALLrel  _atbale+0            1E9A7FA8 1E99F984 2 0 0
_atbFMmodify+582     CALLrel  _kkblalsi+0          1E781260 1E9A7FC4 1E99ACB4 0
                                                   0 1E9A84C4 0
__VInfreq__atbdrv+1  CALLrel  _atbFMmodify+0       1E781260 1E9A84A8 869C69C
223                                                869C720 869C724 1E9A8424
                                                   1E9A84C4
_opiexe+12384        CALLrel  _atbdrv+0            
_opiosq0+6112        CALLrel  _opiexe+0            4 0 869CF54
_kpooprx+232         CALLrel  _opiosq0+0           3 E 869D06C A4
_kpoal8+775          CALLrel  _kpooprx+0           869F68C 869D8D8 4D 1 0 A4
_opiodr+1099         CALLreg  00000000             5E 17 869F688
_ttcpip+996          CALLreg  00000000             5E 17 869F688 0
_opitsk+1080         CALL???  00000000             
_opiino+1087         CALLrel  _opitsk+0            0 0
_opiodr+1099         CALLreg  00000000             3C 4 869FC24
_opidrv+819          CALLrel  _opiodr+0            3C 4 869FC24 0
_sou2o+45            CALLrel  _opidrv+0            3C 4 869FC24
_opimai_real+112     CALLrel  _sou2o+0             869FC18 3C 4 869FC24
_opimai+92           CALLrel  _opimai_real+0       2 869FC50
_OracleThreadStart@  CALLrel  _opimai+0            
4+726                                              
7C80B696             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

Cause of the Problem
This is Oracle bug specifically bug 9711859. In the trace file extent map shows:
Extent Map
-----------------------------------------------------------------
0x010009c9 length: 8
0x00000000 length: 0

The trace file has an Operation related to extent map with the invalid zero dba:

Block Checking: DBA = decimal dba, Block Type = Pagetable extent map block
Incorrect extent map entry at offset offset#. DBA value is 0x00000000
TYP:0 CLS: 7 AFN:file# DBA:hex dba OBJ:obj# SCN:scn SEQ: seq# OP:14.4
kteop redo - redo operation on extent map
The 0x00000000 means length Invalid zero rdba/length.

Solution of the Problem
Workaround we have to do the following:
DROP the affected segment.

The data from the TABLE and LOB should be still accessible using EXPDP/IMPDP, CTAS , index access (in case of a table), etc ...

If the error is still produced by SMON while cleaning the temporary segment, use the next procedures from DBMS_SPACE_ADMIN to clear the segment :

segment_corrupt
segment_drop_corrupt
tablespace_rebuild_bitmaps

Example :

select SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK
from DBA_SEGMENTS where SEGMENT_TYPE = 'TEMPORARY'
and TABLESPACE_NAME = '&LOBTABLESPACE';

-- Using the HEADER_FILE and HEADER_BLOCK, execute :

exec DBMS_SPACE_ADMIN.SEGMENT_CORRUPT('&LOBTABLESPACE', &HEADER_FILE, &HEADER_BLOCK );
exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT('&LOBTABLESPACE', &HEADER_FILE, &HEADER_BLOCK );

exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('&LOBTABLESPACE');
exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('&LOBTABLESPACE');

Install patch Patch 9711859. Note that, Patch 9711859 fix replaces the fix in Patch 8198906 which in fact introduced some other bugs. :)

After you apply above patch also apply patchset 10.2.0.5 and 10.2.0.5.0 Patch 6 (10.2.0.5.6P) 32-Bit Patch:11675638 64-Bit (x64) Patch:11675644 where this bug is fixed.

This issue is fixed in Oracle version 11.2.0.1 (Base Release).

ORA-27300 ORA-27301 ORA-27302 ORA-27303 skgpspawn5

Problem Description
Operating system version is IBM AIX on POWER Systems (64-bit), oracle version is 10.2.0.4 or higher and users are getting from Oracle OS based error messages: ORA-27300 ORA-27301 ORA-27302 ORA-27303.

ksvcreate: Process(m000) creation failed
Wed Mar 16 11:13:26 2011
Process startup failed, error stack:
Wed Mar 16 11:13:26 2010
Errors in file /u01/product/10.2.0/admin/a/bdump/a_psp0_1314923.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5

Cause of the Problem
The skgpspawn5 OS error message was due to an insufficient value for maxuproc of 128 on AIX.

Solution of the Problem
Increase the value for the AIX maxuproc (OS parameter) from 128 to 1024.

ORA 7445 [qesrcDepObj_RemDependent()+8] [qesrcCacObj_Invalidate()+48]

Problem Description
Oracle database instance crash with errors including ORA-7445

- ORA 7445 [qesrcDepObj_RemDependent()+8] 1561780
- ORA 7445 [qesrcCacObj_Invalidate()+48] 1563962

From the database alert log we might get two variation of the error message like,

ORA-07445: exception encountered: core dump [qesrcDepObj_RemDependent()+8] [SIGBUS]
[ADDR:0x241104752414E5C] [PC:0x1029A7CC8] [Invalid address alignment]

or,

Exception [type: SIGBUS, Invalid address alignment] [ADDR:0x4953425552534555] [PC:0x1029A8468, qesrcDepObj_RemDependent()+8] [flags: 0x0, count: 1]

ORA-07445: exception encountered: core dump [qesrcDepObj_RemDependent()+8] [SIGBUS] [ADDR:0x4953425552534555] [PC:0x1029A8468] [Invalid address alignment] []
...
PMON failed to acquire latch

Cause of the Problem
From the trace file that is generated due to this error we will see error stack as follows:

skdstdst ksedst1 ksedst dbkedDefDump ksedmp ssexhd restore_rt qesrcDepObj_RemDependent qesrcRseObj_Invalidate qesrcDepObj_Invalidate qesrcCacMgr_PreCmt_ ktcCommitTxn ktdcmt k2lcom k2send xctctl xctcom_with_options opiexe opiosq0 kpooprx kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real ssthrdmain main libc_start_main

This is due to Oracle internal bug 9477430 9730096 9397309.

Solution of the Problem
According to Oracle support document this bug is fixed in Oracle patchset 11.2.0.2.
So upgrading to your Oracle version is a solution.

Alternatively, there is a temporary workaround if still on 11.2.0.1 by disabling the result cache by setting
"result_cache_max_size = 0"

SQL> alter system set result_cache_max_size = 0;

System altered.

Setting utl_file_dir generates ORA-02095 specified initialization parameter cannot be modified

While setting UTL_FILE_DIR parameter into memory and spfile using scope=both it fails with ORA-02095 like below.

SQL> alter system set utl_file_dir ='d:\test' scope=both;
alter system set utl_file_dir ='d:\test' scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file (pfile - within init.ora) using the UTL_FILE_DIR parameter. However, from 10g database UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically.

You can easily create a directory named utl_file_dir using following command.

SQL> create or replace directory utl_file_dir as '/u01/app/oracle';

Directory created.

Though UTL_FILE_DIR parameter is deprecated but if you still want to use this parameter for your database due to some legacy apps you can do the following:

If you want to edit within pfile first connect to database as sysdba and do the following:

1. Create pfile from spfile. (not necessary if you don't use spfile)
2. Shutdown the db.
3. Edit the pfile to include the utl_file_dir parameter
4. startup the database to point at the pfile:
startup pfile= pfile_location.
5. create spfile from pfile.
6. Shutdown the database
7. startup the database.

If you are using spfile,

1. alter system set UTL_FILE_DIR = '/u01/app/oracle' scope=spfile;
2. shutdown immediate
3. startup

Run these after connecting to database as sysdba.

Error ORA-01555 on Active Data Guard Standby Database

Problem Description
We have active data guard setup in our 11.2.0.1 version of Oracle database. It is upgraded to version 11.2.0.2 and now we see this ORA-1555 on the Standby Database. We can't logon to database with system, dbsnmp or other database users. Only sys as sysdba is working. Also we have investigated that we are running on system's undo Tablespace, - and not the one which has been created for undo.

From the alert log following entries are generated:

Wed Mar 16 11:48:22 2011
ORA-01555 caused by SQL statement below (SQL ID: dskjv8dgqdax0, Query Duration=8519 sec, SCN: 0x03cd.7fb9ca61):
SELECT TRANSMAIN.RECSEQNO,TRANSMAIN.TRANSIK,TRANSMAIN.TRAUNIEX,TRANSMAIN.TRANSEX,TRANSMAIN.DEPKEY,TRANSMAIN.SECIK,TRANSMAIN.PORIK,TRANSMAIN.CTP,TRANSMAIN.DEALER,TRANSMAIN.TRASPECIK,TRANSMAIN.CDYIK,TRANSMAIN.BLKIK,TRANSMAIN.TRCELMNO,TRANSMAIN.TRCBUSNO,TRANSMAIN.TRASTATREQ,TRANSMAIN.TRASTATACT,TRANSMAIN.TRASTATREV,TRANSMAIN.TRACAN,TRANSMAIN.BLKDATE,TRANSMAIN.TRANSORG,TRANSMAIN.TRANOLINK,TRANSMAIN.XI_TRANSCOSTTAX,TRANSMAIN.XI_TRANSSETTLE,TRANSMAIN.XI_TRANSINSTRUC,TRANSMAIN.XI_BALBOOKPFC,TRANSMAIN.NOMVAL,TRANSMAIN.NOMVALEX,TRANSMAIN.AGRDATE,TRANSMAIN.INTBEGDATE,TRANSMAIN.STLMDATE,TRANSMAIN.PMTDATE,TRANSMAIN.TRAPRICE,TRANSMAIN.TRAYIELD,TRANSMAIN.CURVALQC,TRANSMAIN.CURVALPC,TRANSMAIN.ACRINTQC,TRANSMAIN.ACRINTPC,TRANSMAIN.STLMAMTSC,TRANSMAIN.FXRATEQP,TRANSMAIN.FXRATEQS,TRANSMAIN.ACRINTDAYS,TRANSMAIN.STLMCUR,TRANSMAIN.EXTNOTEDATE,TRANSMAIN.EXTNOTEID,TRANSMAIN.BOOKMONTH,TRANSMAIN.FXRATEIP,TRANSMAIN.ACRINTSC,TRANSMAIN.AGIODISASC,TRANSMAIN.CAPCHGID,TRANSMAIN.REFIRATE,TRANSMAIN.FROMDATE,TRANSMAIN.TODATE,TRANSMAIN.EXCTYPE
Wed Mar 16 11:59:29 2011
Thread 1 cannot allocate new log, sequence 13253
Private strand flush not complete
Current log# 4 seq# 17780 mem# 0: /u02/redo/redo401.log
Thread 1 advanced to log sequence 13253 (LGWR switch)
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Wed Mar 16 11:59:32 2011
LNS: Standby redo logfile selected for thread 1 sequence 13253 for destination LOG_ARCHIVE_DEST_2
Wed Mar 16 11:59:34 2011
Archived Log entry 35541 added for thread 1 sequence 17780 ID 0x21653caa dest 1:
Wed Mar 16 12:02:20 2011
ALTER SYSTEM ARCHIVE LOG
Wed Mar 16 12:02:20 2011
Thread 1 cannot allocate new log, sequence 13254
Private strand flush not complete
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Thread 1 advanced to log sequence 13254 (LGWR switch)
Current log# 2 seq# 13254 mem# 0: /u02/redo/redo201.log
Archived Log entry 35543 added for thread 1 sequence 13253 ID 0x21653caa dest 1:

Cause of the Problem
According to Oracle support document ID 1273808.1 "ORA-01555 on Active Data Guard Standby Database" raised due to Oracle bug -
Bug 10320455: QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
which is duplicate from
Bug 10092353: ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER

Solution of the Problem
To solve this bug you have to apply the Patch 10018789 from Oracle support for your Platform on Top of 11.2.0.2.

ORA-3135: connection lost contact during RMAN backup

Problem Description
Database version is 10.2.0.3 and it is 5 nodes RAC on Red Hat Enterprise Linux. RMAN backup is integrated with Veritas Netbackup 5.1 (MP6). Whenever run backup operation is going to be hanged for long periods of time and then generates error:

ORA-3135: connection lost contact

once if we kill the backup job everything goes back to normal.

Investigation
From the OEM ADDM it is found followings:

FINDING 1: 100% impact (98213 seconds)
---------------------------------------------
Wait class "Scheduler" was consuming significant database time.

Cause of the Problem
This is Oracle bug specifically Bug 6874858 Poor performance with Resource Manager when RMAN is running. When RMAN is running with Resource Manager enabled, the performance of the system may degrade. Users may have problems logging in. When this occurs large waits will be seen with the wait event "resmgr:cpu quantum" with low CPU utilization.

Solution of the Problem
Workaround you can disable Oracle Resource Manager.
However this bug is fixed in following versions:

- 11.2.0.1 (Base Release)
- 11.1.0.7 (Server Patch Set)
- 10.2.0.5 (Server Patch Set)
- 11.1.0.6 Patch 11 on Windows Platforms
- 10.2.0.4 Patch 41 on Windows Platforms

So upgrade to your Oracle database is another solution.

RMAN login fails with ORA-12638

Problem Description
RMAN login to the target database fails with the following errors:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed.
RMAN-04005: error from target database:
ORA-12638: Credential retrieval failed

Cause of the problem
The problem is arises due to sqlnet.ora setting. The file named $ORACLE_HOME/network/admin/sqlnet.ora has the following setting:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Solution of the Problem
Change the setting

SQLNET.AUTHENTICATION_SERVICES = (NTS)

to

SQLNET.AUTHENTICATION_SERVICES = (NONE)


NONE for no authentication methods. With this a valid username and password can be used to access the database.

NTS for Windows NT native authentication.

How to add any site to your google buzz feed

With Google buzz you can share your status, updates, photos, videos, and many more as you find interesting. You might want to add a site so that whenever you provide any updates it will buzz you automatically. Now whenever you click on connected sites inside Google buzz you will notice your site might not be there. In order to add your site to the google buzz you have to do the following:

Step 01: At first, you need to create a public Google profile and also you need to claim your desired site on your public profile. You can go to http://www.google.com/profiles and create your profile. For mine my profile link is https://profiles.google.com.

Step 02: If you have already a profile then from the profile page click Edit Profile. Click on Links and there add your custom site link. Save it.

Step 03: Now you need to verify your link. For that you need to link back from your site to your Google profile using rel=me. Note that Buzz does not require verification of few site like Twitter, Flickr and your blogspot blogs. To verify the site(s), you need a rel=me link path from them to your Google Profile URL.
For example, you need to add following line of code to the <head> section of your website.
<link href="http://www.google.com/profiles/your-username" rel="me" type="text/html">
where your-username must be replaced by your google public profile name.

Step 04: Next whenever Google crawler will revisit your site, you'll be able to add that site to your connected sites in Google Buzz. As it is mentioned in https://profiles.google.com/bradfitz/posts/PPjHXDhANAC you can visit https://sgapi-recrawl.appspot.com/ tool and force a re-crawl of your site rather than waiting for Google crawler.

Step 05: Now you have verified your site. Go into Buzz in Gmail, click on "Connected Sites" and your site will be there for you to connect. Whenever your feeds are updated it will do the buzz for you automatically.

Enjoy google buzz.

Database Applications and Programming - Week 3 Homework

Already you have done week 1 and week 2 homework as it was published for exercise in the post http://arjudba.blogspot.com/2011/03/oracle-general-sql-exercise.html and http://arjudba.blogspot.com/2011/03/general-sql-function-exercise.html. This one is week 3 exercise. So you must run script from week 1 and week 2 in order to make this exercise work.

Using the tables created in week 1 and updated in week 2:
1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join. ( 3 points)

2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join. (3 points)


Using the student schema:

3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax. (3 points)

4. Write a query that that performs an inner join of inner join of the grade, student, and grade_type tables using the Oracle inner join convention. (3 points)

5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator. (4 points)

6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300. (4 points)

General sql function exercise

For this exercise you have to take the script from the link http://arjudba.blogspot.com/2011/03/oracle-general-sql-exercise.html which is given at the bottom of the post. In addition to the script also run this demo script which will add two rows into emp and dept table.

SET TERMOUT ON
DELETE FROM EMP WHERE EMPNO = 9999;
DELETE FROM DEPT WHERE DEPTNO = 60;
INSERT INTO EMP VALUES
        (9999, 'KIRK',  'CAPTAIN',     NULL,
        TO_DATE('14-APR-2002', 'DD-MON-YYYY'),  6000, NULL, 50);

INSERT INTO DEPT VALUES (60, 'STARFLEET', 'SAN FRANCISCO');

COMMIT;

SET TERMOUT ON
PROMPT Demonstration add rows is complete.

Using the two tables created in week 1 now lets see the rows.

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

  7369   SMITH     CLERK          7902     17-DEC-80       800                               20

  7499   ALLEN     SALESMAN   7698     20-FEB-81         1600        300          30

  7521   WARD     SALESMAN   7698     22-FEB-81         1250        500          30

  7566   JONES    MANAGER    7839     02-APR-81       2975                            20

  7654   MARTIN  SALESMAN    7698     28-SEP-81       1250       1400          30

  7698   BLAKE    MANAGER    7839     01-MAY-81       2850                         30

  7782   CLARK    MANAGER    7839     09-JUN-81         2450                         10

  7788   SCOTT    ANALYST      7566     09-DEC-82       3000                           20

  7839   KING       PRESIDENT               17-NOV-81        5000                           10

  7844   TURNER SALESMAN    7698     08-SEP-81       1500                           30

  7876   ADAMS   CLERK                      7788     12-JAN-83         1100              20

  7900   JAMES    CLERK                      7698     03-DEC-81       950                   30

  7902   FORD      ANALYST                  7566     03-DEC-81       3000               20

  7934   MILLER    CLERK         7782     23-JAN-82         1300                           10

  9999   KIRK         CAPTAIN                  14-APR-02        6000                          50

DEPTNO           DNAME                        LOC

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

        10             ACCOUNTING               NEW YORK

        20             RESEARCH                  DALLAS

        30             SALES                         CHICAGO

        40             OPERATIONS               BOSTON

        60             STARFLEET                  SAN FRANCISCO

Execute the script demoaddrows.sql to add rows into the two tables above. Write SQL statements to solve the following requests. 

1. Show a list of different jobs.  Eliminate repeating values. (1 point)
   
   
2. How many employees are working at each job in each department and what the sums and averages are for the salary of those employees? (1 point)
   
   
3. Show the employee name with the maximum salary.  (1 point)
   
   
4. Show the average salary for all employees that are not managers. (1 point)
   
   
5. What is the difference between the highest and lowest salary? (1 point)
    
6. Select employee number, name for all employees with salaries above the average salary. Use a subquery. (1 points)
   
   

Using the ZIPCODES table from the text schema, write a SQL query that displays the following:

7. Show the state and the number of zip codes by state.  Order the result by number of zip codes in descending sequence.  Use the ZIPCODES table. (2 points)
   
   
8. Which city has the most zip codes assigned to it?   Use a TOP-N query. (2 points)
   
   
9. Show the state and the number of zip codes by state.  Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes. (2 points)

Using the text schema, write a SQL query that displays the following:

10. Show the Student_ID, last name, and the number of enrollments for the student.  Show only students with more than 2 enrollments. Use an Inline view.  (2 points)
    
    
11. Show the number of students enrolled for each state and zip code for New York and where the city begins with ‘W’.  Use a Scalar subquery.  (3 points)

12. Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.  (3 points)
    
    

CONFIG: Error uploading configuration data to the repository while

Problem Description
While configuring dbconsole it fails with error message oracle.sysman.emdrep.config.ConfigurationException: FATAL Configuration Exceptions.
From the emca logfile,

Mar 11, 2011 6:25:25 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 11, 2011 6:25:26 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
CONFIG: Error uploading configuration data to the repository
oracle.sysman.emdrep.config.ConfigurationException: FATAL Configuration Exceptions

at oracle.sysman.emdrep.config.EMSchemaConfiguration.perform(EMSchemaConfiguration.java:232)
at oracle.sysman.emcp.EMReposConfig.uploadConfigDataToRepository(EMReposConfig.java:674)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:370)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)
Mar 11, 2011 6:25:26 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at C:\app\Administrator\cfgtoollogs\emca\irene\emca_repos_config_.log for more details.
Mar 11, 2011 6:25:26 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error uploading configuration data to the repository
Mar 11, 2011 6:25:26 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error uploading configuration data to the repository
Refer to the log file at C:\app\Administrator\cfgtoollogs\emca\irene\emca_2011_03_11_18_02_32.log for more details.
Mar 11, 2011 6:25:26 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace: 
oracle.sysman.emcp.exception.EMConfigException: Error uploading configuration data to the repository
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:382)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)

Cause of the Problem
The windows machine is in an active directory and is using IPv6. When the short hostname is pinged, it is resolved via the fully qualified hostname to an IPv6 (ISATAP) address for example:

ping dba1

Pinging dba1.blastyourblog.com [fe80::f1bb:134:7a45:1732%10] with 32 bytes of data:
Reply from fe80::f1bb:134:7a45:1732%10: time<1ms Dbconsole 11.2 is built around Grid Control 10.2.0.4 and so is not IPv6 compliant. Grid Control 11.1.0.1 is IPv6 compliant. The version of dbconsole which is built around Grid Control 11.1.0.1 will be IPv6 compliant. By invoking "emctl status agent" we can check the version on which dbconsole is built.

Solution of the Problem

Add entries into the host file, to force IPv4 style resolution:-

1) edit the windows\system32\drivers\etc\hosts file

2) add an entry for the machine of the format:

ipaddress fully-qualified-hostname short-hostname

that is

192.168.100.101 dba1.blastyourblog.com dba1

Now when pinging the short hostname, the ipv4 style ipaddress would return and should solve your problem as well.