Oracle World: Oct 24, 2011

Monday, October 24, 2011

ORA-03113: end-of-file on communication channel

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Frequent Forms Processes sessions dis-connectivity

Symptoms:

on the application tier,on plsql module , database log file show the below:

Thu Feb 18 06:37:36 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel
------------------------------------------------------------------------------
Thu Feb 18 06:37:42 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel
------------------------------------------------------------------------------
Thu Feb 18 06:37:57 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel

Some users while running sql statements from sqlplus that takes roughly >15s, they got disconnected.

Other users while working on PL/SQL application got page cannot be displayed, refresh the page sometime works and sometimes not.

Sqlnet.log shows

Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(oracle_sid=)(CID=(PROGRAM=oracle@)(HOST=)(USER=oracle))))

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
Time: 18-FEB-2010 06:14:37
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

Dbstatus.log
Thu Feb 18 06:14:19 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel
------------------------------------------------------------------------------
Thu Feb 18 06:14:32 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel
------------------------------------------------------------------------------
Thu Feb 18 06:14:36 2010
Database adp encountered ORA-3113ORA-03113: end-of-file on communication channel

Alert log

Thu Feb 18 08:23:35 2010
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 18 08:25:35 2010
WARNING: inbound connection timed out (ORA-3136)

Solution:

1. During the time of error ORA-03113 please check is there is any trace files got generated under udump/bdump/cdump location

If there exist a core dump file under location cdump then please extract the call stack functions from that core file
using the steps as mentioned in the below article :

Article-ID: Note 1812.1
Title: TECH: Getting a Stack Trace from a CORE file

2. Follow Article-ID: Note 120498.1 Title: How To Resolve Most ORA-3113 Errors

3.Check if your server administrator has any scripts which abort long running or CPU intensive processes.
An ORA-3113 process can occur if someone kills your Oracle shadow process at O/S level (Eg: kill -9 on Unix).

Get Oracle Certifications for all Exams
Free Online Exams.com

DRG-12504: stoplist CTXSYS.DEFAULT_STOPLIST does not exist

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Index Creation failed with Oracle Text errors
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-12504: stoplist CTXSYS.DEFAULT_STOPLIST does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Explanation:

DEFAULT_STOPLIST Not created.

Solution:

Need to run drdefus .sql to create DEFAULT_STOPLIST .
Run the drdefus.sql as CTXSYS user.

SQL> conn ctxsys/ctxsys
SQL> @$ORACLE_HOME//ctx/admin/defaults/drdefus.sql

Drop the index.
SQL> Drop index cs_b_document_subject ;
and recreate the Index.

Get Oracle Certifications for all Exams
Free Online Exams.com

Trial balance report outputs and log files are coming with inverse question marks

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:

Reports finished with
MSG-00123: NVL(GLB.TRANSLATED_FLAG,'x') != 'R' AND GLB.CURRENCY_CODE='AED'

Trial balance report outputs and log files are coming with inverse question marks.

Explanation:

ZERO_INDICATOR format trigger was returning null value
Also referred BUG 7584735 - R12:TRIAL BALANCE DETAIL COMPLETES WITH STATUS OF ERROR

Modified the ZERO_INDICATOR format trigger. It returns 0 instead of NULL.
Fixed in the file version -- GLRTBD.rdf 120.8.12000000.7

Also referred Note 782662.1 - Trial Balance Detail Completes with Error for Selected Secured Pagebreak Values

Solution:

1) Please apply the patch 7584735:R12.GL.A
2) Bounce the server
3) Retest the issue

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-1652: unable to extend temp segment by 128 in tablespace

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2

Alert log file shows:

Wed Mar 31 04:12:05 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:29:48 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:44:35 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2

Solution:

a) Tune the queries/statements so that the sort operations are done in memory and not on the disk.
To find the owner of a sort, please refer to Note 1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts

b) If the query is well tuned then you mostly need to increase the TEMP tablespace.

Please add free space to the tablespace using one of follow ways:

1) SQL> alter tablespace <tablesapce_name> add datafile <datafile_name> size <integer>
2) SQL> alter database datafile <datafile_name> resize <integer>

Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons.
The FREE extents can be further used by other users that are executing sort operations. After the database restart the allocated extens are NOT released either but are FREE to be reused.

Hence, seeing the physical space of the temporary tablespace fully allocated is not a reason to be concerned . The above query of V$SORT_SEGMENT should be used to establish the
free space inside the temporary tablespace :
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

References:

Note.19047.1 OERR ORA 1652 unable to extend temp segment by %s in tablespace %s P1-914CU
Note.793380.1ORA-1652 Error Troubleshooting
Note 1025288.6 Ext/Pub How to Diagnose and Resolve ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 Unable to Extend <OBJECT > by %S in Tablespace %
Note.317441.1How Do You Find Who And What SQL Is Using Temp Segments

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-01652 Can not extend temp2 tablespace by 128.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:

select count(1) from v$rman_status takes very long time and causing
ORA-01652 Can not extend temp2 tablespace by 128.

The below statements are shown from the V$sort_usage

declare dot1st number ; dot2nd number ; dot3rd number ; comptxt varchar2 ( 255 )

select db.version_time ,db.controlfile_change# ,db.controlfile_sequence# ,dbinc.begin sys . dbms_backup_restore . createRmanOutputRow ( l0row_id => :l0row_id ,

Explanation:

Bug.5247609 RMAN slow performance during register database/open resetlogs
Note.357765.1 Ext/Mod Rman uses a lot Of Temporary Segments ORA-1652 Unable To Extend Temp Segment

Solution:

exec dbms_stats.gather_fixed_objects_stats()
References: Note 357765.1

Get Oracle Certifications for all Exams
Free Online Exams.com

IMP-00003 ORA-00942 IMP-00017

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Getting errors while importing 9208 DB to 10204 DB

My source Database is on Windows 2003 server, database version is 9.2.0.8, I have fully exports it from the 9i export utility on the O-H/bin
Exp file=xx log=xx direct=y statistics=none full=y (uploaded the export log file).

My target Database is on Windows 2008 Server, database version is 10.2.0.4, I have imported the dump file from imp 10g utility from the 10g O-H/bin directory
Imp file=xx ignore=y full=y log=xx

Then I got many errors with import log file touches 500M

Getting errors like
1.ORA-01400: cannot insert null into sh.costs.promo_id,even though promo_id column does not exists on the source database.
2.ORA-01722:invalid number
3.many errors like ORA-00942: Table or view does not exists

Solution:

Trying to import into read only table spaces.

Change any read only table spaces to read write

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-00600: internal error code, arguments: [17281], [1001]

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: ORA-600,ORA-01001,ORA-10388 during impdp

expdp.exe bkp/bkp10g dumpfile=DPBKP_LLARB:expdp_1.dmp, DATA_PUMP_DIR:expdp_2.dmp, DPBKP_LLARB:expdp_3.dmp, DATA_PUMP_DIR:expdp_4.dmp, DATA_PUMP_DIR:expdp_5.dmp logfile=dpbkp_llarb:expdp_llarb.log filesize=10G parallel=4 tablespaces=LL914ARBDATA,LL97TEMP

### Import Data Pump parameters ###
impdp content=METADATA_ONLY DUMPFILE=impdpdir1:EXPDP_1.DMP, impdpdir1:EXPDP_3.DMP, impdpd
ir2:EXPDP_2.DMP, impdpdir3:EXPDP_4.DMP, impdpdir3:EXPDP_5.DMP LOGFILE=impdpdir1:impdp.log PARALLEL=4
TABLESPACES=LL914ARBDATA,LL97TEMP

import.log shows

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 07 June, 2010 9:03:01

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "BKP"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "BKP"."SYS_IMPORT_TABLESPACE_01": bkp/******** content=DATA_ONLY DUMPFILE=impdpdir1:EXPDP_1.DMP, impdpdir1:EXPDP_3.DMP, impdpdir2:EXPDP_2.DMP, impdpdir3:EXPDP_4.DMP, impdpdir3:EXPDP_5.DMP LOGFILE=impdpdir1:impdp_data.log PARALLEL=4 TABLESPACES=LL914ARBDATA,LL97TEMP
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LL914ARB"."LLATTRBLOBDATA" 9.434 GB 12569399 rows
. . imported "LL914ARB"."DTREEACL" 6.830 GB 209164495 rows
. . imported "LL914ARB"."DAUDIT" 2.651 GB 43994139 rows
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 4 with process name "DW04" prematurely terminated
ORA-31672: Worker process DW04 died unexpectedly.
Job "BKP"."SYS_IMPORT_TABLESPACE_01" stopped due to fatal error at 11:42:11
ORA-39014: One or more workers have prematurely exited.

alert_llarb.log

Mon Jun 7 11:42:35 2010
Errors in file /orasys/admin/bdump/llarb_p000_15150.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x53EE97CF8], [], [], [], [], []
ORA-01001: invalid cursor
ORA-10388: parallel query server interrupt (failure)
Mon Jun 7 11:42:35 2010
Errors in file /orasys/admin/bdump/llarb_p001_15152.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x53EE97CF8], [], [], [], [], []
ORA-01001: invalid cursor
ORA-10388: parallel query server interrupt (failure)

Solution:

You had probably a temporary corruption in memory,
Import again would finish with no errors

It is tricky to identify the exact root cause,

Get Oracle Certifications for all Exams
Free Online Exams.com

Database bounce while running adpreclone DBTier and passing wrong apps password

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: By running adpreclone on the database and passing wrong apps password, it causes database bounce

Problem Description: By running adpreclone on the database and passing wrong apps password, it causes database bounce

Trace Files shows:

Started StageDBTier at Thu Jun 10 12:45:49 GST 2010
Version:
StageDBTier.java : 120.6.12000000.3
#############################################################

---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------

getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL='null'
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : erpdev
sDbDomain : auh.police
sDbPort : 1524
sDbSid : addev
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@erpdev.auh.police:1524:addev
Exception occurred: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev)))
Exception occurred: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev.auh.police)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev.auh.police)))

Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

StackTrace:
java.lang.NullPointerException
at oracle.apps.ad.clone.util.CreateCloningStage.promptYesNoQuestion(CreateCloningStage.java:981)
at oracle.apps.ad.clone.StageDBTier.main(StageDBTier.java:576)
...............
................
................

---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------

getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL='null'
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : erpdev
sDbDomain : auh.police
sDbPort : 1524
sDbSid : addev
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@erpdev.auh.police:1524:addev
Exception occurred: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev)))
Exception occurred: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev.auh.police)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdev.auh.police)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=addev.auh.police)))

Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

RC-40000: Unable to start up the database
Raised by oracle.apps.ad.clone.StageDatabase
RC-40000: Unable to start up the database
Raised by oracle.apps.ad.clone.StageDatabase

Solution:

This is a known bug
Bug 7533866 - R12 PRECLONE SCRIPT BOUNCING PRODUCTION DATABASE WITHOUT PROMPTING THE USER

This is an enhancement to avoid bouncing the Database (to force a fresh
start) when there is no Database connection when the user runs adpreclone.
This also happens when the user enters the wrong password for Apps User.
The issue was detected on 11i under Bug 7362282.

Bug created the following file versions to fix this issue

Product Directory File Version
==== ================= ============== ==================
ad java/clone StageDBTier.java 120.6.12000000.5
ad java/clone StageDatabase.java 120.10.12000000.14
ad java/util MessageResourceBundle.java 120.2.12000000.6
ad patch/115/manualsteps ad_apply_patch.xml 120.4

There is no patch created for bug 7533866 but the fixed files are available in
12.0 RAPIDCLONE CONSOLIDATED FIXES JAN/2010 - 8360494:R12.OAM.A

Get Oracle Certifications for all Exams
Free Online Exams.com

The concurrent manager has timed out waiting for the Output Post-processor to finish this request

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: The concurrent manager has timed out waiting for the Output Post-processor to finish this request.

Issue Clarification ===
On R12.0.6, the request Create Accounting is failing with the following error message in the log file:
Post-processing of request 465575 failed at 10-JUN-2010 13:29:30 with the error message:
The concurrent manager has timed out waiting for the Output Post-processor to finish this request.

Check that there are enough Output Post-processor service processes running.

OPP LOG FILE

The uploaded log for the OPP shows that for the problematic request 465575, OPP was started, but no evidence of it completing:

[6/10/10 1:22:16 PM] [OPPServiceThread1] Post-processing request 465575.
[6/10/10 1:22:16 PM] [40904:RT465575] Executing post-processing actions for request 465575.
[6/10/10 1:22:16 PM] [40904:RT465575] Starting XML Publisher post-processing action.
[6/10/10 1:22:16 PM] [40904:RT465575]
Template code: XLAACCPB01
Template app: XLA
Language: en
Territory: US
Output type: RTF
[6/10/10 1:24:12 PM] [OPPServiceThread1] Post-processing request 465593.
....
....
....
[6/10/10 1:29:30 PM] [UNEXPECTED] [40904:RT465575] java.lang.ThreadDeath
at java.lang.Thread.stop(Thread.java:698)
at oracle.apps.fnd.cp.opp.OPPRequestThreadManager.terminateThread(OPPRequestThreadManager.java:210)
at oracle.apps.fnd.cp.opp.OPPTerminateCommand.execute(OPPTerminateCommand.java:48)
at oracle.apps.fnd.cp.opp.OPPServiceThread.processMessage(OPPServiceThread.java:243)
at oracle.apps.fnd.cp.opp.OPPServiceThread.waitForMessages(OPPServiceThread.java:230)
at oracle.apps.fnd.cp.opp.OPPServiceThread.mainLoop(OPPServiceThread.java:131)
at oracle.apps.fnd.cp.gsf.BaseServiceThread.run(BaseServiceThread.java:136)

Solution:

Please increase the value of the profile option "Concurrent: OPP Process Timeout" to 6 hours.

The following justify the solution:
Note 352518.1 - Concurrent Request Fails Due to Timeout of the Post Processing Action

Get Oracle Certifications for all Exams
Free Online Exams.com

While cloning getting ORA-01034:Oracle Not available

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: While cloning getting ORA-01034:Oracle Not available

When attempting to Use rapidclone to clone the database
the following error occurs.

ERROR
-----------------------
Control file created.

alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata1/addev/system01.dbf'

On alert log file,

alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2232104 bytes
Variable Size 435744984 bytes
Database Buffers 624951296 bytes
Redo Buffers 10813440 bytes

Control file created.

alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata1/addev/system01.dbf'

The issue can be reproduced at will with the following steps:
1. perl adcfgclone.pl dbTier

Justification:

When using rapidclone the source database always need to be stopped using normal. see from: Note:230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone :

Copy the Database Tier File System
Log on to the Source System Database Node as the ORACLE user.

* Perform a normal shutdown of the Source System Database
* Copy the Database (DBF) files from the Source to the Target System

Solution:

The issue is caused by the following: source database not stopped correctly causing a DB not to be consistent

alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata1/addev/system01.dbf'

This means the source database was not stopped properly. When cloning the DB the source needs to be stopped using shutdown normal.

Do the following to fix:

1 - Stop the source database using shutdown normal
2 - Copy the datafiles again
3 - execute the rapidclone scripts again.

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-600 [17059] errors occurring intermittently when insert over db links

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: ORA-00600 via db links

On DB1, we have package estr.TR_TIC_GATEWAY,
this package is inserting data into synonym estr.TR_TIC_GATEWAY_IN_QUEUE

This synonym is built over a database link to a table tr.TR_TIC_GATEWAY_IN_QUEUE@gdp.ae on DB2

any time an insert is taken place,ora-600 is fired on DB1 and file system is filling up rapidly.

Alert Log File:

Mon Mar 15 13:05:12 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
Mon Mar 15 13:05:16 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
...
Mon Mar 15 14:09:34 2010
Errors in file /orasys/ipay/udump/ipay_ora_1294.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []

Trace file shows:

*** 2010-06-28 11:27:10.158
*** SERVICE NAME:(SYS$USERS) 2010-06-28 11:27:10.158
*** CLIENT ID:() 2010-06-28 11:27:10.158
*** SESSION ID:(104.42096) 2010-06-28 11:27:10.158
LIBRARY OBJECT HANDLE: handle=3a8ca3cc8 mtx=3a8ca3df8(0) cdp=32768
name=
INSERT INTO TR_TIC_GATEWAY_IN_QUEUE ( TIQ_SERIAL_NO, TIQ_TIC_SOURCE_CODE, TIQ_TIC_SOURCE_IN, TIQ_TIC_NO, TIQ_TIC_DATE, TIQ_TRANSACTION_TYPE, TIQ_LOG_SERIAL, TIQ_FLS_CODE, TIQ_TIC_TYPE, TIQ_TIC_TYPE_IN, TIQ_TIC_AMOUNT, TIQ_TIC_LOCATION, TIQ_LOCAL_AUTHORITY, TIQ_IS_PAYABLE, TIQ_IS_PAYABLE_IN, TIQ_LIC_NO, TIQ_LIC_SOURCE_CODE, TIQ_LIC_SOURCE_IN, TIQ_PNO, TIQ_PNO_ORG, TIQ_PCOLOR_CODE, TIQ_PKIND_CODE, TIQ_PTYPE_CODE, TIQ_PSOURCE_CODE, TIQ_PCOLOR_IN, TIQ_PKIND_IN, TIQ_PSOURCE_IN, TIQ_VIOLATION_DESC_A, TIQ_VIOLATIO
hash=7649571625c71326d46805477b7157a4 timestamp=06-27-2010 06:02:32
namespace=CRSR flags=RON/KGHP/TIM/OBS/PN0/DBN/[10410040]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=b8e8 hlc=b8e8
lwt=3a8ca3d70[3a8ca3d70,3a8ca3d70] ltm=3a8ca3d80[3a8ca3d80,3a8ca3d80]
pwt=3a8ca3d38[3a8ca3d38,3a8ca3d38] ptm=3a8ca3d48[3a8ca3d48,3a8ca3d48]
ref=3a8ca3da0[3a8ca3da0,3a8ca3da0] lnd=3a8ca3db8[3ba8c8328,4043271e8]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
3b8e99e70 49b1254e8 49b1254e8 1 N [00]
485979d38 49b1c6ed8 49b1c6ed8 1 N [00]
3b081a1d0 41c11c418 41c11c418 1 N [00]
3af961a88 41c149828 41c149828 1 N [00]
LIBRARY OBJECT: object=46a241340
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=32768
child# table reference handle
------ -------- --------- --------
0 49933a578 49933a1e8 3a87cb658
...
32767 400e3d888 3a20fa2d8 39dc0c408
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 39d70a2c0 46a241458 I/P/A/-/- 0 NONE 00
*** 2010-06-28 11:27:10.596
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17059], [0x46A241340], [], [], [], [], [], []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
object line object
handle number name
3ea1099f0 24 ESTR.INSERT_ORG_TABLE
3ba684c30 119 package body ESTR.XML_API
3ba684c30 176 package body ESTR.XML_API
3bbd495c0 1522 package body ESTR.TR_TIC_GATEWAY
39db9df30 1 anonymous block
----- Call Stack Trace -----
ksedmp kgeriv kgesiv kgesic1 kgltba kglhdgc kglget kxsGetLookupLock kksfbc kkspbd0 kksParseCursor opiosq0 opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxtexe kxtex1 rpiswu2 kxtexe insbrp insrow insdrv inscovexe insExecStmtExecIniEngine insexe opiexe opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXIM pfrinstr_EXIM pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main _start
----- End of Call Stack Trace -----

The process state dump shows:

Process global information:
process: 49b024988, call: 49d3eb1c0, xact: 49cd8c2b8, curses: 49b1254e8, usrses: 49b1254e8
----------------------------------------
SO: 49b024988, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=150, calls cur/top: 49d3eb1c0/49d3f9360, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
...
O/S info: user: oracle, term: UNKNOWN, ospid: 26993
OSD pid info: Unix process pid: 26993, image: oracleipay@Gateway2.auh-police
...
SO: 49b1254e8, type: 4, owner: 49b024988, flag: INIT/-/-/0x00
(session) sid: 104 trans: 49cd8c2b8, creator: 49b024988, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-0096-005D6F52, short-term DID: 0000-0000-00000000
txn branch: 49ce44a18
oct: 2, prv: 0, sql: 404c63f90, psql: 3ba74b450, user: 171/ESTR
O/S info: user: NETWORK?SERVICE, term: ACMPWEBSERVER, ospid: 8176:3372, machine: TEMPWORKGROUP\ACMPWEBSERVER
program: w3wp.exe
application name: w3wp.exe, hash value=0
last wait for 'latch: library cache' blocking sess=0x0 seq=239 wait_time=2052878 seconds since wait started=2
address=3b9f53c98, number=d6, tries=0
...
SO: 49d3eb1c0, type: 3, owner: 49d3e7208, flag: INIT/-/-/0x00
(call) sess: cur 49b1254e8, rec 0, usr 49b1254e8; depth: 2
----------------------------------------
SO: 3aa4ac650, type: 59, owner: 49d3eb1c0, flag: INIT/-/-/0x00
cursor enqueue
child: 39ef80bd0, flag: 37, number: 32769
parent: 46a241458
----------------------------------------
SO: 3af8166a0, type: 54, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=3af8166a0 handle=489613408 mode=S lock=4086ada58
user=49b1254e8 session=49b1254e8 count=1 mask=0005 savepoint=0x18a flags=[00]
----------------------------------------
SO: 4086ada58, type: 53, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=4086ada58 handle=489613408 mode=S
call pin=3af8166a0 session pin=0 hpc=0000 hlc=0000
htl=4086adad8[4086d2a60,48558d318] htb=48558d318 ssga=48558c860
user=49b1254e8 session=49b1254e8 count=1 flags=PNC/[0400] savepoint=0x18a
LIBRARY OBJECT HANDLE: handle=489613408 mtx=489613538(0) cdp=0
name=SYS.XMLTYPE_LIB

Justification:

Encountering ORA-600 [17059] errors when using SESSION_CACHED_CURSORS.

Solution:

This looks to be due to bug:5177766 fixed in 11.1 and 10.2.0.4 given that SESSION_CACHED_CURSORS is being used.

Upgrade to 10.2.0.5 or back out merge patch number 7007477 and apply merge patch number 8645328 instead which contains all of the fixes in merge patch number 7007477 plus the following extra fixes:

Bug:5177766 - OERI[17059] with SESSION_CACHED_CURSORS
Bug:6085625 - Wrong child cursor may be executed which has mismatching bind information
Bug:5254759 - ORA-12801/ORA-1008 occurs on a parallel query with bind variables
Bug:6452485 - SGA memory corruption / OERI [17182] with fix for bug 6085625

To clear the problem temporarily in the instance, you can flush the shared pool using:

connect / as sysdba
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
exit

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-1555/ORA-22924 error while export LOB objects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Getting ORA-1555/ORA-22924 error while export LOB objects.

LOB objects are located in MSSM tablespace (Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace)

Modify the PCTVERSON of LOB segment to 50

Solution: alter table lobpctversion modify lob(lobLoc) (pctversion 50);

SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800

SQL> alter table lobpctversion modify lob(lobLoc) (pctversion 50);

Table altered.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to modify a the PCTVERSION for a LOB segment?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: alter table ecs.CS_B_DOCUMENT MODIFY LOB SYS_LOB0000356268C00007$$ (PCTVERSION 50);

ERROR at line 1:
ORA-00902: invalid datatype

Main Issue:

Getting ORA-1555/ORA-22924 error while export LOB objects.

LOB objects are located in MSSM tablespace (Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace)

Have advised customer to modify the PCTVERSON of LOB segment to 50 but it failing with the following error:

alter table ecs.CS_B_DOCUMENT MODIFY LOB SYS_LOB0000356268C00007$$ (PCTVERSION 50);

ERROR at line 1:
ORA-00902: invalid datatype

Solution by Example: alter table lobpctversion modify lob(lobLoc) (pctversion 50);

SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800

SQL> alter table lobpctversion modify lob(lobLoc) (pctversion 50);

Table altered.

Get Oracle Certifications for all Exams
Free Online Exams.com

Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924 with lobobjects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924

### Export Data Pump parameters ###
exp using DP,schema level
expdp bkp/*** dumpfile=work_dir:expdp_ecs.dmp logfile=DATA_PUMP_DIR:expdp_ecs.log schemas=ecs

While exporting using DP:
ORA-31693: Table data object "ECS"."CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

Problem does not show while using normal exp , however during imp it gave the following errors:

Here what I got after I imported the healthy exp dmp file !

. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_2013" 0 rows imported
. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL"
IMP-00064: Definition of LOB was truncated by export
IMP-00028: partial import of previous table rolled back: 351622 rows rolled back
. . importing table "CS_B_DOCUMENT_FOLLOWUP" 0 rows imported

Explanation:

ORA-1555/ORA-22924 may occur when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.
This may occur when LOB column resides in a MSSM (Manual Segment Space Management) tablespace.

It is also confirmed that the LOB column resides in a MSSM.

Please note that LOB RETENTION parameter has no effect if the LOB resides in a tablespace using MANUAL space management (MSSM). In order for LOB RETENTION to honour the UNDO_RETENTION period Automatic Segment Space Managemetn (ASSM) should be used.

If you want to use LOB retention for LOB columns to avoid ORA-1555, you must use ASSM (Automatic Segment Space Management) tablespace.

You cannot change the segment space management mode of a tablespace. If your LOB column is store on MSSM tablespace and you would like to use the ASSM option, you'd have to create a new tablespace using 'segment space management auto' followed by moving the objects to the new tablespace which was created to use Automatic Segment Space Management.

create tablespace assm_ts datafile
...
autoextend on
extent management local
segment space management auto; <==

alter table <table_name> move tablespace <ASSM_tablespace_name>;

If you can't move to ASSM and you need to store your LOB data on MSSM tablespace, you'd have to use PCTVERSION instead of RETENTION.

-- Example: Setting PCTVERSION to 20 percent
SQL> alter table <table_name> modify lob (<LOB_column>) (pctversion 20);

Please also refer the following note:

Note 800386.1
ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace

Solution:

From the above query identify the LOB name and also the PCT version.
If the PCT version is less than 50, then advise you to increase the PCTversion to 50% for the table CS_B_DOCUMENT_FOLLOWUP as follows:

SQL>alter table CS_B_DOCUMENT_FOLLOWUP MODIFY LOB <LOB NAME> ( PCTVERSION 50);

Once done, take a fresh export using exp. and re-attempt the import.

if the output for the file contains row data, then the LOB is corrupted.

Please refer note 787004.1, where clear steps is provided on how to identify the corrupt blocks and work around the issue.

set serverout on
exec dbms_output.enable(100000);
declare
pag number;
len number;
c varchar2(10);
charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength (documentcontent) len
from ecs.CS_B_DOCUMENT) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (documentcontent, 1, 1+ (page * charpp))
into c
from ecs.CS_B_DOCUMENT
where rowid = r.rid;

exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/

there are corrupted LOB rows withing the segment. Please perform the action plan as per note 874562.1 to confirm the same.

Reference:
Note ID 452341.1 for detecting corrupted clob's

Note 874562.1
EXP-00056,ORA-24801 During Export

Get Oracle Certifications for all Exams
Free Online Exams.com

Arabic characters are shown in reverse with HTML output

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: HTML Reports shows Arabic in reverse order

Solution:

In file /8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgbEnsure the the profile option Viewer:Text is set to Browser

From the System Administrator responsibility,

navigate to Install > Viewer Options
ADD new record
File Formst: Text
Mime Type: apps/bidi
Description: Pasta Viewer for bidi

Set the profile option Viewer:Application for Text
Value: Pasta viewer for bidi

In your browser choose enocoding Unicode.

please also review the following:

notes 839520.1 and 816879.1

change the following line
Tk2Motif*fontMapCs: iso8859-1=UTF8 ( or whatever it is set to )
to be
Tk2Motif*fontMapCs: iso8859-1=AR8MSWIN1256

Make sure that the prt files in $FND_TOP\reports contains the following:

code "bold on" esc "[1m"
code "bold off" esc "[0m"
code "underline on" esc "[3m"
code "underline off" esc "[2m"

nls locale "arabic"
nls datastorageorder "logical"
nls contextuallayout "no"
nls contextualshaping "yes"

If you find the following Lines in the .prt files then remove them then add them to the Environment file :

REPORTS60_PRINTER_CODE_BEFORE=&5
export REPORTS60_PRINTER_CODE_BEFORE
REPORTS60_PRINTER_CODE_AFTER=&4
export REPORTS60_PRINTER_CODE_AFTER

In the enviroment file that you source before starting the concurrent manager :

set both the IX_Printing and
IX_Rendering to the full path of Pasta.cfg file.

Ensure you are using the Pasta Universal printer type and not the Pasta Postscript type.

Then retest for the issue.

Reference:

Note 552977.1 for reverse Arabic
notes 839520.1 and 816879.1

Get Oracle Certifications for all Exams
Free Online Exams.com

Audit trail tables does not show historical changes

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Audit trail tables does not show historical changes

running AuditTrail Update Tables concurrent request finsihed with error
TOLERANCE_ID
Fatal error in fdasql, quitting...
Fatal error in fdacv, quitting...

Explanation:

he FNDATUPD concurrent request fails to complete successfully when auditing is enabled on "AP_SYSTEM_PARAMETERS_ALL" because this table has too many columns and by default auditing is enabled on every single column of this table. When the concurrent request runs, it tries to create a view for this table and hits a database limit on the number of columns of a table that can be audited (128 columns).

Therefore, if you really want to audit the "AP_SYSTEM_PARAMETERS_ALL" table then you will need to select fewer columns of this table to be able to successfully run the concurrent request.

If you do not want to audit this table, then do the following to remove auditing on this table and resolve the error:

1. Navigate to the audit group tables, and query the "AP_SYSTEM_PARAMETERS_ALL" table.

2. Set the Group state to "Disable - Purge Table" for this table.
Note: This option drops the auditing triggers and views and deletes all data from the shadow table.

3. Run the "AuditTrail Update Tables" concurrent program again.It should now run successfully.

Solution:

As per Note id 60828.1 point II,d) Set the profile option "AuditTrail:Activate" to yes to begin auditing data.

Now _AC tables is showing historical data per invoice id

AP_SYSTEM_PARAMETERS_ALL table is still in the audit, please review Note.274755.1 for the steps to take to clean up the audit trail.

Follow the steps in the referenced note to clean up the audit trail, recreate audit trail groups and respecify audit trail information via audit trail forms in apps and then run the FNDATUPD progr

As you would have seen when you ran the FNDATUPD program there are a number of views created, there are 2 types _AC and _AV each giving a different view of the data. To build up a change history of the data you need to join the data from a number of views and not just query the one single view.

Each view allows slightly different access to data, one allows you to reconstruct the value for a row at a given time (_AC), while the other provides simple access to when the value was changed (_AV). You will need to join views to get a change history of the data, this is explained in the documentation.

Get Oracle Certifications for all Exams
Free Online Exams.com