Upgrading Oracle Database 11g to 19c with Oracle E-Business Suite Release 12.2 on Oracle Exadata Cloud Services

 Upgrading Oracle Database 11g to 19c with Oracle E-Business Suite Release 12.2 on Oracle Exadata Cloud Services (Doc ID 2567103.1)

Migrating and Upgrading Oracle Database 11g to Oracle Database 19c for Oracle E-Business Suite Release 12.1 on OCI VM DB Systems

 

Migrating and Upgrading Oracle Database 11g to Oracle Database 19c for Oracle E-Business Suite Release 12.1 on OCI VM DB Systems (Doc ID 2758990.1)

19c with R12.2 clone doc ID's

 

Cloning Oracle E-Business Suite Release 12.2 with Multitenant Database using Rapid Clone (Doc ID 2552208.1)

Using Oracle 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2 (Doc ID 2530665.1)

19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1)

Business Continuity for Oracle E-Business Release 12.1 Using Oracle 11g Release 2 Physical Standby Database (Doc ID 1070033.1)

Example Manual Migration of Oracle E-Business Suite Release 12.2 with Oracle Database 19c to VM DB Systems and Compute Cloud Service in Oracle Cloud Infrastructure (Doc ID 2743151.1)

Data Gaurd setup--

-For AD/TXK 9 above , we can use logical names.

Business Continuity for Oracle E-Business Suite Release 12.2 on Oracle Database 19c Using Logical Host Names (Doc ID 2617788.1)

Business Continuity for Oracle E-Business Suite Release 12.2 on Oracle Database 19c Using Physical Host Names (Doc ID 2617787.1)

To keep /etc/hosts permanent after adding enry

 chattr -R +i /etc/hosts

 

chattr -i /etc/hosts

OEM 13.5 -Tuning Advisor giving ORA-01031: insufficient privileges - with DBSNMP user

Sol : (Replace XXXX with DBSNMP in my case or change accordingly)

GRANT OEM_ADVISOR TO XXXX;

GRANT CONNECT TO XXXX;

GRANT OEM_MONITOR TO XXXX;

ALTER USER XXXX DEFAULT ROLE OEM_ADVISOR, OEM_MONITOR;

GRANT CREATE SESSION TO XXXX;

GRANT SELECT ANY DICTIONARY TO XXXX;

GRANT ADMINISTER SQL TUNING SET TO XXXX;

GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO XXXX;

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

Issue :

---------Run below queries to confirm the issue

---Use DBSNMP user to connect and run below  , replace sql_id with latest from your environment.

1) 

SET SERVEROUTPUT ON

declare

stmt_task VARCHAR2(40);

begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '6xdyxvdxkmuj3',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 10000, --<<< number of seconds

task_name => '6xdyxvdxkmuj3_tuning_task',

description => 'Tuning task for statement 6xdyxvdxkmuj3');

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end;

/

****

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '6xdyxvdxkmuj3_tuning_task');

end;

/

-- See the suggestions of the TUNING TASK.

SET LONG 100000

SET LONGCHUNKSIZE 1000

SET LINESIZE 200

set pagesize 500

--***************************

--TO GET DETAILED INFORMATION

--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('1djr35t9304ns_tuning_task','TEXT','ALL','ALL') FROM DUAL;

###>>>>>Check the results of the detailed query. <<<<<###

SQL> SET LONG 100000

SET LONGCHUNKSIZE 1000

SET LINESIZE 200

set pagesize 500

SQL>

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('6xdyxvdxkmuj3_tuning_task','TEXT','ALL','ALL') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('6XDYXVDXKMUJ3_TUNING_TASK','TEXT','ALL','ALL')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name : 6xdyxvdxkmuj3_tuning_task

Tuning Task Owner : DBSNMP

Tuning Task ID : 375975

Workload Type : Single SQL Statement

Execution Count : 1

Current Execution : EXEC_343213

Execution Type : TUNE SQL

Scope : COMPREHENSIVE

Time Limit(seconds): 10000

Completion Status : COMPLETED

Started at : 10/20/2021 08:35:54

Completed at : 10/20/2021 08:35:56

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

Schema Name: APPS

SQL ID : 6xdyxvdxkmuj3

SQL Text : SELECT

'PO' PO_RELEASE_FLAG,

POH.CLM_DOCUMENT_NUMBER PO_NUM,

POHA.ACCEPTANCE_DUE_DATE,

POH.APPROVED_DATE,

NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE')

AUTHORIZATION_STATUS,

POH.CLOSED_DATE,

POHA.COMMENTS,

POH.FIRM_DATE,

POH.NOTE_TO_AUTHORIZER,

POH.NOTE_TO_RECEIVER,

POH.NOTE_TO_VENDOR,

POH.PRINT_COUNT,

POH.PRINTED_DATE,

POH.RATE,

POH.RATE_DATE,

POH.RATE_TYPE,

POH.REVISED_DATE,

POH.REVISION_NUM,

POH.AGENT_ID,

POH.BILL_TO_LOCATION_ID,

POH.FROM_HEADER_ID,

POH.PO_HEADER_ID,

POH.SHIP_TO_LOCATION_ID,

POH.TERMS_ID,

POH.VENDOR_CONTACT_ID,

POH.VENDOR_ID,

POH.VENDOR_SITE_ID,

NVL(POH.CLOSED_CODE, 'OPEN') CLOSED_CODE,

POH.CURRENCY_CODE,

NVL(POH.FIRM_STATUS_LOOKUP_CODE,'N')

FIRM_STATUS_LOOKUP_CODE,

POH.FOB_LOOKUP_CODE,

POH.FREIGHT_TERMS_LOOKUP_CODE,

POH.SHIP_VIA_LOOKUP_CODE,

POH.TYPE_LOOKUP_CODE,

POCR.REQUEST_LEVEL (+)= 'HEADER' AND

POCR.CHANGE_ACTIVE_FLAG (+)= 'Y'

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

ERRORS SECTION

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

- ORA-01031: insufficient privileges

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