Davis Apps DBA: July 2019

Monday, July 29, 2019

Error 404 -- Not Found From RFC 2068 hypertext Transfer Protocol -- HTTP/1.1

In my environment we are having EBS(12.2.5) running with 12.1.0.2 database.

One day in our DEV Instance Front-End is not opening and faced the below error.

So I followed the below steps as a solution and it worked.

First, generate the JAR files

Next, reload the generated jar files to the respective database

Now compile the APPS schema to reduce the count of Invalid objects.

Once everything completed, you can clear your browser cache and try to login to the application., but normally the above steps will be accompanied by clearing the middle tier cache. So I gave the below steps to clear the middle tier cache from backend.

To clear the cache, follow the below steps.

Stop the application services
Navigate to $OA_HTML/cabo/images/cache
Take a backup of contents of the folder cache and then clear all the contents inside that cache folder
Navigate to $OA_HTML/cabo/styles/cache
Take a backup of contents of the folder cache and then clear all the contents inside that cache folder

Now recompile all the jsp files manually as below

Navigate to $FND_TOP/patch/115/bin
Run the ojspcompile command as below to compile the files manually

Finally, clear your browser cache, close the browser, open a new session, launch the application and try to login.

****************************நன்றி****************************

Wednesday, July 24, 2019

Running GSS with Auto Option

In this post, we'll see how to run Gather Schema Statistics with Auto option.

My Env:

Oracle DB : 12.1.0.2
Oracle EBS : 12.2.5

One important aspect of a healthy Oracle database is database statistics. It’s the main data on which CBO feeds to generate good execution plans for SQLs. Implementing a periodic and consistent gather statistics procedure in Oracle E-Business Suite is every Applications DBA’s duty. But often I see that they are not set up properly to take advantage of the latest database features. So let’s take advantage of new features around gathering database statistics.

GATHER AUTO : Initially introduced in 11gR2 as DBMS_STATS.AUTO_SAMPLE_SIZE, which yields a significant reduction in the time it takes to collect highly accurate statistics. Oracle decides which objects to gather fresh statistics for and also decides how to get them. Oracle determines the estimate percent to be used. The parallelism used is based on the init.ora setting. This also used *_TAB_MODIFICATIONS to decide on the eligible candidates.

Let us see how to submit GSS Auto in Oracle EBS.

After sometime, the request failed with error and on checking the log file we found the below issue.

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.2

FNDGSCST: Gather Schema Statistics

+---------------------------------------------------------------------------+

Current system time is 11-JUL-2019 14:10:42

+---------------------------------------------------------------------------+

**Starts**11-JUL-2019 14:10:43

**SQL error and free**11-JUL-2019 14:12:41

ORACLE error 600 in FDPSTP

Cause: FDPSTP failed due to ORA-00600: internal error code, arguments: [kqllod:no stub for dependency parent], [], [], [], [], [], [], [], [], [], [], []

The SQL statement being executed at the time of the error was: SELECT 11-JUL-2019 14:12:41

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 100 degree = 8 internal_flag= NOBACKUP

stats on table WF_NOTIFICATION_OUT is locked

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

No completion options were requested.

Output file size:

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 11-JUL-2019 14:12:41

+-------------------------------------------------------------------

--------+

With this above log files info we couldn't debug much on the issue. Now we need to debug info from the alert log. What we should do is, we should check for the ORA-600 error from alert log file and should check in the trace file created for this ORA-600 error. In this trace file we can get some more detailed info which will be helpful in debugging.

Trace file /ebsdev/oracle/R12DEV/12.1.0/admin/R12DEV_ebsdev/diag/rdbms/r12dev/R12DEV/trace/R12DEV_j001_15186.trc

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

ORACLE_HOME = /ebsdev/oracle/R12DEV/12.1.0

System name: Linux

Node name: ebsdev.sify.net

Release: 4.1.12-37.4.1.el6uek.x86_64

Version: #2 SMP Tue May 17 07:23:38 PDT 2016

Machine: x86_64

Instance name: R12DEV

Redo thread mounted by this instance: 1

Oracle process number: 217

Unix process pid: 15186, image: oracle@ebsdev.sify.net (J001)

*** 2019-07-11 14:10:00.851

*** SESSION ID:(1260.1524) 2019-07-11 14:10:00.851

*** CLIENT ID:() 2019-07-11 14:10:00.851

*** SERVICE NAME:(SYS$USERS) 2019-07-11 14:10:00.851

*** MODULE NAME:() 2019-07-11 14:10:00.851

*** CLIENT DRIVER:() 2019-07-11 14:10:00.851

*** ACTION NAME:() 2019-07-11 14:10:00.851

ORA-00600: internal error code, arguments: [kqllod:no stub for dependency parent], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 85

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2199

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2778

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058

ORA-06512: at "SYS.DBMS_IREFRESH", line 687

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at line 1

After applying the RDBMS patch 27883586, the particular ORA-600 issue got resolved.

Now resubmitted the concurrent with same set of parameters and this time it completed successfully.

Ref : Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)

Refresh or Create MVIEW in Edition = ORA-600[kqllod:no Stub For Dependency Parent] (Doc ID 2408062.1)

****************************நன்றி****************************

Monday, July 22, 2019

Latest Patch Wizard Patch Applied or Not

Use the below query to identify which patches you already applied to keep your patch wizard updated. Based on your Oracle EBS Version use the respective query to identify your patch wizard status.

--Check for the Latest 11i Patch Wizard Patches Applied
Select Bugs.Bug_Number as PATCH,
Decode(Bugs.Bug_Number,
10405353, '10405353 for Release 11i (included in RECOMMENDED patch 14350791)',
15998913, '15998913 for Release 11i (included in RECOMMENDED patch 14350791)',
10123716, '10123716 for Release 11i (included in RECOMMENDED patch 14350791)',
14350791, '14350791 for the latest RECOMMENDED version of Patch Wizard in Oracle E-Business Suite (Release 11i)') as "11i Patch Wizard Patches",
decode(Ad_Patch.Is_Patch_Applied('11i',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT APPLIED') as APPLIED
From
(select '10405353' as bug_number From Dual
UNION ALL
select '15998913' as bug_number from Dual
UNION ALL
select '10123716' as bug_number From Dual
UNION ALL
select '14350791' as bug_number From Dual) Bugs;

--Check for the Latest R12.0 Patch Wizard Patches Applied
Select Bugs.Bug_Number as PATCH,
Decode(Bugs.Bug_Number,
10629916, '10629916 for Release 12.0.x is superseded in 15998913',
15998913, '15998913 for Release 12.0.x is superseded by 16541210',
16541210, '16541210:R12.AD.A for the latest RECOMMENDED version of Patch Wizard in Oracle E-Business Suite (Release 12.0)') as "R12.0 Patch Wizard Patches",
decode(Ad_Patch.Is_Patch_Applied('11i',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT APPLIED') as APPLIED
From
(select '10629916' as bug_number From Dual
UNION ALL
select '15998913' as bug_number from Dual
UNION ALL
select '16541210' as bug_number From Dual) Bugs;

--Check for the Latest R12.1 Patch Wizard Patches Applied
Select Bugs.Bug_Number as PATCH,
Decode(Bugs.Bug_Number,
12729041, '12729041 for Release 12.1.x is recommended by Development.',
11710489, '11710489 for Release 12.1.x is superseded by 14061105',
14061105, '14061105 for Release 12.1.x is superseded by 16248018',
16248018, '16248018 for Release 12.1.x is superseded by 16357991',
16357991, '16357991 for Release 12.1.x is superseded by 11071638',
11071638, '11071638 for Release 12.1.x is superseded by 16992893',
16992893, '16992893 for Release 12.1.x is superseded by 17244500',
17244500, '17244500 for Release 12.1.x is superseded by 14504286',
14504286, '14504286 for Release 12.1.x is superseded by 17270136',
17270136, '17270136 for Release 12.1.x is replaced by 18598941',
18598941, '18598941-R12.ADO.B.delta.5 is superseded by R12.ADO.B.delta.6 (19277598)',
17842189, '17842189:R12.ADO.B 1OFF:12.1.3 - Recommended to apply on top of 18598941 (Release 12.1)',
19278051, '19278051:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B.delta.6 (19277598)',
19277598, '19277598:R12.ADO.B.delta.6 for Release 12.1.x is replaced by R12.ADO.B (19491900)',
19491900, '19491900:R12.ADO.B for Release 12.1.x is replaced by R12.ADO.B (19663882)',
19663882, '19663882:R12.ADO.B for Release 12.1.x is replaced by R12.ADO.B (17701355)',
17701355, '17701355:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (20448886)',
20448886, '20448886:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B.Delta.7 (20390398)',
20390398, '20390398:R12.ADO.B.Delta.7 is superseded by R12.ADO.B.Delta.8 (21285343)',
20121898, 'Patch:20121898:R12.ADO.B 1OFF:20390398:R12.ADO.B.Delta.7, Adds VIEW ALL option for Files and Menus',
21285343, 'Patch:21285343:R12.ADO.B.delta.8 (Patch 9703082 prereqs ADO delta 7 which is superceded by 21285343:R12.ADO.B.delta.8',
9703082, 'Patch:9703082:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (22490144)',
22490144, 'Patch:22490144:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (23618634)',
23618634, 'Patch 23618634:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (22902616)',
22902616, 'Patch 22902616:R12:ADO.B for Release 12.1.x is superseded by R12.ADO.B (23150570)',
23150570, 'Patch 23150570:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (24505803)',
24505803, 'Patch 24505803:R12.ADO.B PATCH WIZARD RECOMMENDS AD/TXK DELTA BUNDLE PATCHES AFTER APPLYING AD/TXK DELTA8',
20689262, 'Patch 20689262:R12.ADO.B for Release 12.1.x is superseded by R12.ADO.B (28064828)',
28064828, 'Patch 28064828:R12.ADO.B for Release 12.2.x is superseded by R12.ADO.B (28389569)',
28389569, '28389569:R12.ADO.B Patch Wizard latest recommended Patch for 12.1') as "R12.1 Patch Wizard Patches",
decode(Ad_Patch.Is_Patch_Applied('R12',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT APPLIED') as APPLIED
From
(select '12729041' as bug_number From Dual
UNION ALL
select '11710489' as bug_number From Dual
UNION ALL
select '14061105' as bug_number From Dual
UNION ALL
select '16248018' as bug_number From Dual
UNION ALL
select '16357991' as bug_number From Dual
UNION ALL
select '11071638' as bug_number From Dual
UNION ALL
select '16992893' as bug_number From Dual
UNION ALL
select '17244500' as bug_number From Dual
UNION ALL
select '14504286' as bug_number From Dual
UNION ALL
select '17270136' as bug_number From Dual
UNION ALL
select '18598941' as bug_number From Dual
UNION ALL
select '17842189' as bug_number From Dual
UNION ALL
select '19278051' as bug_number From Dual
UNION ALL
select '19277598' as bug_number From Dual
UNION ALL
select '19491900' as bug_number From Dual
UNION ALL
select '19663882' as bug_number From Dual
UNION ALL
select '17701355' as bug_number From Dual
UNION ALL
select '20448886' as bug_number From Dual
UNION ALL
select '20390398' as bug_number From Dual
UNION ALL
select '20121898' as bug_number From Dual
UNION ALL
select '21285343' as bug_number From Dual
UNION ALL
select '9703082' as bug_number From Dual
UNION ALL
select '22490144' as bug_number From Dual
UNION ALL
select '23618634' as bug_number From Dual
UNION ALL
select '22902616' as bug_number From Dual
UNION ALL
select '23150570' as bug_number From Dual
UNION ALL
select '24505803' as bug_number From Dual
UNION ALL
select '20689262' as bug_number From Dual
UNION ALL
select '28064828' as bug_number From Dual
UNION ALL
select '28389569' as bug_number From Dual) Bugs;

--Check for the Latest R12.2 Patch Wizard Patches Applied
Select Bugs.Bug_Number as PATCH,
Decode(Bugs.Bug_Number,
18184883, '18184883:R12.ADO.C for Release 12.2.x is replaced by 18427809',
18427809, '18427809:R12.ADO.C for Release 12.2.x is superseded by 18978837',
18978837, '18978837:R12.ADO.C for Release 12.2.x is superseded by 19412224',
19412224, '19412224:R12.ADO.C for Release 12.2.x is superseded by 19871279',
19871279, '19871279:R12.ADO.C for Release 12.2.x is superseded by 20231593',
20231593, '20231593:R12.ADO.C for Release 12.2.x is superseded by 20448886',
20448886, '20448886:R12.ADO.C for Release 12.2.x is superseded by 19663882',
19663882, '19663882:R12.ADO.C for Release 12.2.x is superseded by 21184697',
21184697, '21184697:R12.ADO.C for Release 12.2.x is superseded by 21806033',
21806033, '21806033:R12.ADO.C for Release 12.2.x is superseded by 22490144',
9703082, '9703082:R12.ADO.C for Release 12.2.x is superseded by 22490144',
22490144, '22490144:R12.ADO.C for Release 12.2.x is superseded by 23150570',
23150570, '23150570:R12.ADO.C for Release 12.2.x is superseded by 24505803',
24505803, '24505803:R12.ADO.C for Release 12.2.x is superseded by 25470620',
20689262, '20689262:R12.ADO.C for Release 12.2.x is superseded by 26359440',
26359440, '26359440:R12.ADO.C for Release 12.2.x is superseded by 28064828',
28064828, '28064828:R12.ADO.C for Release 12.2.x is superseded by 28389569',
28389569, '28389569:R12.ADO.C for the latest version of Patch Wizard in Oracle E-Business Suite (Release 12.2)') as "R12.2 Patch Wizard Patches",
decode(Ad_Patch.Is_Patch_Applied('R12',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT APPLIED') as APPLIED
From
(select '18184883' as bug_number From Dual
UNION ALL
select '18427809' as bug_number from Dual
UNION ALL
select '18978837' as bug_number From Dual
UNION ALL
select '19412224' as bug_number From Dual
UNION ALL
select '19871279' as bug_number From Dual
Union All
select '20231593' as bug_number From Dual
UNION ALL
select '20448886' as bug_number From Dual
Union All
select '19663882' as bug_number From Dual
Union All
select '21184697' as bug_number From Dual
Union All
select '21806033' As Bug_Number From Dual
Union All
select '9703082' As Bug_Number From Dual
Union All
select '22490144' As Bug_Number From Dual
Union All
select '23150570' As Bug_Number From Dual
UNION ALL
select '24505803' as bug_number From Dual
Union All
select '20689262' As Bug_Number From Dual
Union All
select '26359440' As Bug_Number From Dual
Union All
select '28064828' As Bug_Number From Dual
Union All
select '28389569' As Bug_Number From Dual) Bugs;

Ref : Patch Wizard FAQ (Doc ID 976688.2)

****************************நன்றி****************************

Patch Impact Analysis through Patch Wizard

When you going to run Patch Wizard : Impact Analysis for the first time, follow the below step by step process.

Download the Patch Information Bundle from the MOS document '741129.1' as given below

Create a staging directory as below

Copy the InfoBundleZip to staging directory we declared above

Now start the patch impact analysis for the entire EBS application using the bundle patch.

Once the concurrent request is submitted, you can monitor the status from the respective log created for the request.

The request you submitted for the patch impact analysis will submit a child request which inturn creates many requests to check all the products for the compliance of codelevel and other patchset level. Based on this analysis only the final impact report will be generated. Technically speaking this will not create the impact analysis report, rather it will create the patches already applied and the patches needs to be applied based on the information read from the patch bundle.