Sunday, 19 July 2020

ERROR at line 1: ORA-03113: end-of-file on communication channel ERROR: ORA-03114: not connected to ORACLE

ERROR:
======
sqlplus -s APPS/***** @/mwdb-pl/ebsap/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/mwdb-pl/ebsap/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/ADZDWRKR.sql &un_apps &systempwd CUTOVER 0 3 10'
Connected.
PL/SQL procedure successfully completed.
Connected.
Session altered.
PL/SQL procedure successfully completed.
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE

CAUSE:
======

Database Auditing is Enabled.

SOLUTIONS:
=========

Disable Auditing with the following steps:

1- SHOW PARAMETER AUDIT

2- Check audit_trail='DB','EXTENDED'

none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

3-  NOAUDIT ALL;

4-  alter system set audit_trail='NONE';

5- Reboot database

6- Retest the issue, it should go successful.





ORA-20000: Please manually fix the following invalid packages: "PACKAGE BODY SYSTEM.APPS_ARRAY_DDL"

ERROR:
======

ATTENTION: All workers either have failed or are waiting:

FAILED: file adinvset.pls on worker  1.

ERROR at line 1:
ORA-20000: Please manually fix the following invalid packages: "PACKAGE BODY
SYSTEM.APPS_ARRAY_DDL"
ORA-06512: at "SYSTEM.AD_INVOKER", line 776
ORA-06512: at "SYSTEM.AD_COMPILE", line 103
ORA-06512: at "SYSTEM.AD_INVOKER", line 762
ORA-06512: at line 2

SOLUTIONS:
=========

Please check list of invalid objects and compile.

SELECT owner, object_name FROM all_objects
WHERE object_name= 'APPS_ARRAY_DDL'
AND object_type = 'PACKAGE';

>> execute below steps from application.
SQL>show user
APPS
SQL>@/mwdb-pl/ebsap/fs1/EBSapps/appl/ad/12.0.0/admin/sql/adinvset.pls Superbarwa20 10 0 TRUE FALSE

Connect with sysdba
SQL>utlrp.sql

>> Restart failed worker from adctrl utility.

Autoconfig failed in EBS R12.1.3 with 19c database

ERROR:
======
WARNING: [CVM Error Report]
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /mwdb-pl/ebsap/fs1/EBSapps/appl/fnd/12.0.0/bin/txkCfgUtlfileDir.sh  1

 No. of scripts failed in CVM phase: 1

Database Tier:
==========

>>Set the PDB environment:

$. erpdev_>host>.env
$perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \ -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir
Enter the full path of Oracle Home: /mwdb-pl/oracle/19.3.0.0/db_3
Enter the APPS Password:

SQL> select value from v$parameter where name='utl_file_dir';

VALUE
--------------------------------------------------------------------------------
/mwdb-pl/oracle/19.3.0.0/temp/erpdev,/mwdb-pl/oracle/19.3.0.0/db_3/appsutil/outbound/erpdev_bbhoeccdbdev,/tmp

Application Tier:
============

>> Validate the s_appltmp entries in run/patch context file environment.

OLD Entries:
=========
<APPLTMP oa_var="s_appltmp">/mwdb-pl/ebsap/fs1/inst/apps/erpdev_bbhoeccdbdev/appltmp</APPLTMP>
<APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>

NEW: Entries:
==========
<APPLRGF oa_var="s_applrgf">/mwdb-pl/ebsap/fs1/inst/apps/erpdev_bbhoeccdbdev/logs/appl/rgf</APPLRGF>
<APPLTMP oa_var="s_appltmp">/mwdb-pl/oracle/19.3.0.0/temp/erpdev</APPLTMP>

Execute adautoconfig.sh in application tier and sure it's completed successfully.


Error in adop session

ERROR:
======
    [ERROR]     Error occurred while executing <adpatch  workers=8    options=hotpatch    flags=autoskip   console=no interactive=no  defaultsfile=/mwdb-pl/ebsap/fs1/EBSapps/appl/admin/erpdev/adalldefaults.txt patchtop=/mwdb-pl/ebsap/fs_ne/EBSapps/patch/13543062 driver=u13543062.drv logfile=u13543062.log>
    [ERROR]     Please check the adpatch log files.
    [STATEMENT] Autopatch completed with errors/warnings. Please check logfiles
    [STATEMENT] SQL Statement:       update ad_adop_sessions
        set status='F'
         where adop_session_id = 2 and appltop_id = 2233 and node_name='bbhoeccdbdev'

    [STATEMENT] SQL Statement:         update ad_adop_sessions
        set apply_end_date=to_date('07-07-2020 12:25:38','DD-MM-YYYY HH24:MI:SS')
        where  adop_session_id=2

    [STATEMENT] APPLY Phase END TIME: 07-07-2020 12:25:38
    [STATEMENT] [START 2020/07/07 12:25:42] Unlocking sessions table
      [STATEMENT] SQL stmt: <       begin
          AD_ZD_ADOP.UNLOCK_SESSIONS_TABLE('bbhoeccdbdev',60,2);
       end;
[STATEMENT] Online patching tool cannot proceed when a previous patching session is incomplete
[STATEMENT] Please ensure no pending patching sessions exist before trying a new patch
[ERROR]     Unrecoverable error occured. Exiting the current session.
[STATEMENT] [START 2020/07/07 13:46:35] Unlocking sessions table
[STATEMENT] [END   2020/07/07 13:46:36] Unlocking sessions table
[STATEMENT] Log file: /adop_20200707_134510.log
[STATEMENT] [START 2020/07/07 13:46:41] Unlocking sessions table
[STATEMENT] [END   2020/07/07 13:46:43] Unlocking sessions table

CAUSE:
======
Node Name       Node Type       Phase       Status          Started                        Finished                       Elapsed
--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------
bbhoeccdbdev    master          APPLY       FAILED          07-JUL-20 12:24:49 +03:00      07-JUL-20 12:25:38 +03:00      0:00:49
                                PREPARE     NOT APPLICABLE
                                CUTOVER     NOT APPLICABLE
Session was in incomplete in ad_adop_sessions tables.

SOLUTIONS:
==========

>> Check the adop cycle status.

SQL>select adop_session_id,status from ad_adop_sessions where status='F';

Output:
adop_session_id status
2 F

>> Update the status to completed.

SQL>update ad_adop_sessions set status='C' where status='F';

SQL>commit;

$adop -status
Node Name       Node Type       Phase       Status          Started                        Finished                       Elapsed
--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------
bbhoeccdbdev    master          APPLY       ACTIVE          07-JUL-20 12:24:49 +03:00      07-JUL-20 12:25:38 +03:00      0:00:49

Now you can start the adop patch.

ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

ERROR:
======
SQL> alter index CSM.CSM_DASHBOARD_SEARCH_COLS_U1 rebuild online;
alter index CSM.CSM_DASHBOARD_SEARCH_COLS_U1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

SOLUTIONS:
==========
SQL> alter index CSM.CSM_DASHBOARD_SEARCH_COLS_U1 rebuild;

Index altered.

FAILED: File ADZDPREP.sql In Online Patching with ORA-44303: service name exists

ERROR:
======
sqlplus -s APPS/***** @/mwdb-pl/ebsap/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/mwdb-pl/ebsap/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/ADZDPREP.sql &un_apps &pw_apps &systempwd'
Connected.

PL/SQL procedure successfully completed.

Connected.

Session altered.

PL/SQL procedure successfully completed.
Commit complete.

begin
*
ERROR at line 1:
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 316
ORA-06512: at "APPS.AD_ZD_PREP", line 388
ORA-01403: no data found
ORA-06512: at "APPS.AD_ZD_PREP", line 382
ORA-06512: at line 4

CAUSE:
======
Found to have ebs_patch already in all_services

SQL> select SERVICE_ID,NAME from all_services;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
  1 SYS$BACKGROUND
  2 SYS$USERS
  5 ebs_patch
Online Enable patch will try to create the ebs_patch service in the all_services table. 
If the service is already present in all_services table, then the patch worker will fail while executing ADZDPREP.sql script with below error.

SOLUTIONS:
==========
Check the output of Below query

select SERVICE_ID,NAME from dba_services;

If ebs_patch exists then Please perform below action plan.

begin

  DBMS_SERVICE.STOP_SERVICE(service_name =>'ebs_patch');
  DBMS_SERVICE.DELETE_SERVICE(service_name =>'ebs_patch');
end;

Please check below query and make sure that "ebs_patch" does not exist.

select SERVICE_ID,NAME from dba_services;

If no entry found then commit the session.

Then, restart the patch.

ORA-20100: ORA-20100: File o0148195.tmp creation failed. after upgrade database to 19c

ERROR:
======

SQL> EXEC FND_STATS.GATHER_SCHEMA_STATISTICS ('ALL');
BEGIN FND_STATS.GATHER_SCHEMA_STATISTICS ('ALL'); END;

*
ERROR at line 1:
ORA-20100: ORA-20100: File o0148195.tmp creation failed.
File could not be opened or operated on as requested.
Action: Make sure the directory - /mwdb-pl/oracle/19.3.0.0/temp/erpdev - is a
valid directory with write permissions and is accessible from the database
server node
ORA-06512: at "APPS.FND_FILE", line 319
ORA-06512: at "APPS.FND_FILE", line 364
ORA-06512: at "APPS.FND_FILE", line 421
ORA-06512: at "APPS.FND_STATS", line 585
ORA-06512: at "APPS.FND_STATS", line 1044
ORA-06512: at "APPS.FND_STATS", line 1044
ORA-06512: at "APPS.FND_STATS", line 1275
ORA-06512: at "APPS.FND_STATS", line 1275
ORA-06512: at "APPS.FND_STATS", line 815
ORA-06512: at line 1

Solutions:
======

Step:1 Check UTL_FILE_DIR path sis exits.

SQL> select value from v$parameter where name='utl_file_dir';

VALUE
--------------------------------------------------------------------------------
/mwdb-pl/oracle/19.3.0.0/temp/erpdev,/mwdb-pl/oracle/19.3.0.0/db_3/appsutil/outb
ound/erpdev_host,/tmp


Step 2: Change the permission.

cd /mwdb-pl/oracle/19.3.0.0/temp
chmod 777 erpdev

Step: 3 Updating UTL_FILE_DIR in an Oracle E-Business Suite Instance on Oracle Database 19c (2525754.1)

oradev$perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=addUtlFileDir

Step: 4 Re-execute step.1




DB Upgrade to 19c using DBUA

ERROR:
======
Error in preupgrade tool execution. ERROR - Unable to run the preupgrade due to:ERROR - Unable to run preupgrade due to:
ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at "SYS.DBMS_PREUP", line 3352
ORA-06512: at "SYS.DBMS_PREUP", line 10500
ORA-06512: at line 8

declare
*
ERROR at line 1:
ORA-29284: file read error
ORA-06512: at line 56

SOLUTION:
==========
1) unset the env variable ORA_NLS10
unset ORA_NLS10

Verify:
echo $ORA_NLS10

2) Re-run DBUA upgrade.

DB Pre-clone failed perl adpreclone.pl dbTier

ERROR:
======
$perl adpreclone.pl dbTier
Can't locate strict.pm in @INC (@INC contains: /mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/5.8.3 /mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/site_perl/5.8.3 /mwdb-pl/oracle/11.2.0.4/db_2/appsutil/perl ../lib/5.10.0/sun4-solaris-thread-multi-64 ../lib/5.10.0 ../lib/site_perl/5.10.0/sun4-solaris-thread-multi-64 ../lib/site_perl/5.10.0 .) at adpreclone.pl line 34.
BEGIN failed--compilation aborted at adpreclone.pl line 34.

Cause:
====
PERL5LIB path is not updated in context environment file.

Solutions:
======
Update PERL5LIB parmater value in erpdev_<Host>.env file.

OLD: PERL5LIB=/mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/5.8.3:/mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/site_perl/5.8.3:/mwdb-pl/oracle/11.2.0.4/db_2/appsutil/perl

NEW: PERL5LIB=/mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/5.10.0:/mwdb-pl/oracle/11.2.0.4/db_2/perl/lib/site_perl/5.10.0:/mwdb-pl/oracle/11.2.0.4/db_2/appsutil/perl

Set the environment and execute  again command. perl adpreclone.pl dbTier

#############################################################
StageDBTier Finished at Sun Jun 14 13:28:47 GMT+03:00 2020
Status: Completed Successfully
#############################################################

OS Watcher Installation in RAC

 Step:1 Download and untar the oswbb812.tar under the grid user in RAC on the both nodes. Follow the OS Watcher User's Guide (Doc ID 153...