Sunday, December 27, 2020

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 1

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)

Overview

EBS is already certified to be used with Oracle Database 19c which includes the version 19.3 and 19.5. Within this, EBS will be using a single tenant architecture with one CDB and one PDB. To know more about the multitenant architecture, you should visit https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234. In this post, i have tried to give the complete step by step on how to upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3) on 3 particular posts. The first post has the tasks mentioned which we need to perform before the database upgrade. The second part has the actual database upgrade tasks using DBUA. The third post has the tasks mentioned which we need to perform after the database upgrade.



Important Information Regarding the Upgrade to Oracle Database 19c

1. Your source 12c database which is currently a non CDB database will be migrated to the PDB as part of database upgrade process.

2. There will be two environment variables created once the whole migration process is completed one for PDB and one for CDB.

3. Make sure the NLS character set are same in source and target(after the 19c database installations)

4. The PDB violations should not have any errors listed in it.


Documents to be referenced as part of Database upgrade

1. Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (MOS Note 2552181.1)

2. Database Initialization Parameter Settings for Oracle E-Business Suite Release 12 (MOS Note 396009.1)

3. Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (MOS Note 2525754.1)

4. Using Oracle Database 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2 (MOS Note  2530665.1)

Database upgrade guide https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/lot.html(section B)


Upgrading an Oracle E-Business Suite Release 12 Database to Oracle Database 19c

Before the Database Installation

Before the Database Installation
Follow My Oracle Support Knowledge Document 136697.1, hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c, to run hcheck.sql, which looks for some known common Data Dictionary problems.

[oracle@apps 12.1.0]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 12:46:39 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> spool hccheck.txt
SQL> @hcheck.sql;

HCheck Version 07MAY18 on 01-DEC-2020 12:47:00
----------------------------------------------
Catalog Version 12.1.0.2.0 (1201000200)
db_name: EBSDB
Is CDB?: NO

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1201000200 <=  *All Rel* 12/01 12:47:00 PASS
.- MissingOIDOnObjCol          ... 1201000200 <=  *All Rel* 12/01 12:47:01 PASS
.- SourceNotInObj              ... 1201000200 <=  *All Rel* 12/01 12:47:01 PASS
.- OversizedFiles              ... 1201000200 <=  *All Rel* 12/01 12:48:37 PASS
.- PoorDefaultStorage          ... 1201000200 <=  *All Rel* 12/01 12:48:37 PASS
.- PoorStorage                 ... 1201000200 <=  *All Rel* 12/01 12:48:37 PASS
.- TabPartCountMismatch        ... 1201000200 <=  *All Rel* 12/01 12:48:37 PASS
.- OrphanedTabComPart          ... 1201000200 <=  *All Rel* 12/01 12:48:38 PASS
.- MissingSum$                 ... 1201000200 <=  *All Rel* 12/01 12:48:38 PASS
.- MissingDir$                 ... 1201000200 <=  *All Rel* 12/01 12:49:04 PASS
.- DuplicateDataobj            ... 1201000200 <=  *All Rel* 12/01 12:49:07 PASS
.- ObjSynMissing               ... 1201000200 <=  *All Rel* 12/01 12:49:10 PASS
.- ObjSeqMissing               ... 1201000200 <=  *All Rel* 12/01 12:49:14 PASS
.- OrphanedUndo                ... 1201000200 <=  *All Rel* 12/01 12:49:14 PASS
.- OrphanedIndex               ... 1201000200 <=  *All Rel* 12/01 12:49:14 PASS
.- OrphanedIndexPartition      ... 1201000200 <=  *All Rel* 12/01 12:49:32 PASS
.- OrphanedIndexSubPartition   ... 1201000200 <=  *All Rel* 12/01 12:49:32 PASS
.- OrphanedTable               ... 1201000200 <=  *All Rel* 12/01 12:49:32 PASS
.- OrphanedTablePartition      ... 1201000200 <=  *All Rel* 12/01 12:49:33 PASS
.- OrphanedTableSubPartition   ... 1201000200 <=  *All Rel* 12/01 12:49:33 PASS
.- MissingPartCol              ... 1201000200 <=  *All Rel* 12/01 12:49:33 PASS
.- OrphanedSeg$                ... 1201000200 <=  *All Rel* 12/01 12:49:33 FAIL

HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137666
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137650
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137642
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137658

.- OrphanedIndPartObj#         ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- DuplicateBlockUse           ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- FetUet                      ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- Uet0Check                   ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- SeglessUET                  ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- BadInd$                     ... 1201000200 <=  *All Rel* 12/01 12:49:34 PASS
.- BadTab$                     ... 1201000200 <=  *All Rel* 12/01 12:49:35 PASS
.- BadIcolDepCnt               ... 1201000200 <=  *All Rel* 12/01 12:49:35 PASS
.- ObjIndDobj                  ... 1201000200 <=  *All Rel* 12/01 12:49:35 PASS
.- TrgAfterUpgrade             ... 1201000200 <=  *All Rel* 12/01 12:49:35 PASS
.- ObjType0                    ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- BadOwner                    ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- StmtAuditOnCommit           ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- BadPublicObjects            ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- BadSegFreelist              ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- BadDepends                  ... 1201000200 <=  *All Rel* 12/01 12:49:36 PASS
.- CheckDual                   ... 1201000200 <=  *All Rel* 12/01 12:49:40 PASS
.- ObjectNames                 ... 1201000200 <=  *All Rel* 12/01 12:49:40 PASS
.- BadCboHiLo                  ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- ChkIotTs                    ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- NoSegmentIndex              ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- BadNextObject               ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- DroppedROTS                 ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- FilBlkZero                  ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- DbmsSchemaCopy              ... 1201000200 <=  *All Rel* 12/01 12:49:44 PASS
.- OrphanedIdnseqObj           ... 1201000200 >  1201000000 12/01 12:49:44 PASS
.- OrphanedIdnseqSeq           ... 1201000200 >  1201000000 12/01 12:49:44 PASS
.- OrphanedObjError            ... 1201000200 >  1102000000 12/01 12:49:44 PASS
.- ObjNotLob                   ... 1201000200 <=  *All Rel* 12/01 12:49:45 PASS
.- MaxControlfSeq              ... 1201000200 <=  *All Rel* 12/01 12:49:45 PASS
.- SegNotInDeferredStg         ... 1201000200 >  1102000000 12/01 12:49:45 PASS
.- SystemNotRfile1             ... 1201000200 >   902000000 12/01 12:49:45 PASS
.- DictOwnNonDefaultSYSTEM     ... 1201000200 <=  *All Rel* 12/01 12:49:45 PASS
.- OrphanTrigger               ... 1201000200 <=  *All Rel* 12/01 12:49:45 PASS
.- ObjNotTrigger               ... 1201000200 <=  *All Rel* 12/01 12:49:45 PASS
---------------------------------------
01-DEC-2020 12:49:45  Elapsed: 165 secs
---------------------------------------
Found 4 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/u01/install/APPS/12.1.0/admin/ebsdb_apps/diag/rdbms/ebsdb/ebsdb/trace/ebsdb_ora_13451_HCHECK.trc

SQL>

The script failed for the below
FAIL
HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137666
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137650
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137642
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137658

Apply Pre requisite patches 

Check the level of AD and TXK in your system

select  ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');

ABBREVIATION

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

NAME

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

CODELEVEL

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

ad

Applications DBA

C.10

 

txk

Oracle Applications Technology Stack

C.10

 

ABBREVIATION

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

NAME

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

CODELEVEL

 

Apply the latest AD and TXC patches. As of now, the current is level 12.

Run adop phase=prepare in the run file system


In the patch file system:-run etcc otherwise patching will fail using the note 12.2 E-Business Suite Applications DBA Steps To Run The EBS Technology Code Level Checker (ETCC) (Doc ID 2008451.1)


Unzip patches and run adgrants.


Download and unzip the following patches:

Patch 30628681 (R12.AD.C.Delta.12)

Patch 30735865 (R12.TXK.C.Delta.12)



Run adgrants by following the instructions in the readme of Patch 30628681, which includes the latest version of adgrants.


Run the Middle Tier EBS Technology Codelevel Checker (MT-ETCC) on the patch file system.

Run the MT-ETCC utility checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows) on the Oracle E-Business Suite application tier node's patch file system.

$ . <EBS_ROOT>/EBSapps.env run

$ adop phase=finalize

$ adop phase=cutover

$ . <EBS_ROOT>/EBSapps.env run

$ adop phase=cleanup



Once these patches are applied, 


a. Source the run edition environment file.

UNIX:

$ . <EBS_ROOT>/EBSapps.env run

Windows:

C:\> <RUN_BASE>\EBSapps\appl\envshell<CONTEXT_NAME>.cmd

b. Execute the admkappsutil.pl utility to create the appsutil.zip file in <INST_TOP>/admin/out.

$ perl <AD_TOP>/bin/admkappsutil.pl

On the database tier (as the oracle user):

a. Source the environment for RDBMS ORACLE_HOME.

UNIX:

$ cd <RDBMS ORACLE_HOME>

$ . ./<RDBMS ORACLE_HOME>/<CONTEXT_NAME>.env

Windows:

C:\> <RDBMS ORACLE_HOME>\<CONTEXT_NAME>.cmd

b. Copy or FTP the appsutil.zip file to <RDBMS ORACLE_HOME>.

c. Uncompress appsutil.zip, under <RDBMS ORACLE_HOME>.

$ cd <ORACLE_HOME>

$ unzip -o appsutil.zip

d. Run AutoConfig on <RDBMS ORACLE_HOME>.

Note: If you are a Windows customer on 12cR1 only, and have applied the "WINDOWS DB BUNDLE PATCH 12.1.0.2.180417(64bit):27440294" patch or later, you should copy the latest afinit_db121.ora and afinit_db121RAC.ora templates into the custom directory:


C:\> copy %ORACLE_HOME%/appsutil/template\afinit_db121.ora %ORACLE_HOME%/appsutil/template/custom/afinit_db121.ora

C:\> copy %ORACLE_HOME%/appsutil/template\afinit_db121RAC.ora %ORACLE_HOME%/appsutil/template/custom/afinit_db121RAC.ora

Edit the afinit_db121.ora and afinit_db121RAC.ora templates in the custom directory, by removing optimizer_adaptive_features=false and replacing it with optimizer_adaptive_plans=false.


UNIX:

$ sh <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh

Apply adop phase=fs_clone


Apply the other mandatory patches:-
Patch 26521736 - 19c interoperability patch for Release 12.2
Patch 28732161 - Apply on Release 12.2.6 or later versions.


Create the initialization parameter setup files

[oracle@apps ~]$ . ebsdb_apps.env
[oracle@apps ~]$ cd $ORACLE_HOME/appsutil
[oracle@apps appsutil]$ pwd
/u01/install/APPS/12.1.0/appsutil
[oracle@apps appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u01/install/APPS/12.1.0

Oracle Home being passed: /u01/install/APPS/12.1.0
[oracle@apps appsutil]$ echo $ORACLE_SID
ebsdb
[oracle@apps appsutil]$ cd bin
[oracle@apps bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/install/APPS/12.1.0 -outdir=/u01/install/APPS/12.1.0/appsutil/log -appsuser=apps -dbsid=ebsdb -skipdbshutdown=yes
Enter the APPS Password:



Script Name    : txkOnPremPrePDBCreationTasks.pl
Script Version : 120.0.12020000.13
Started        : Wed Dec  2 11:51:45 GMT 2020

Log File       : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/txkOnPremPrePDBCreationTasks.log


-----------
Values used
-----------
DB Oracle Home        : /u01/install/APPS/12.1.0
OUT Directory         : /u01/install/APPS/12.1.0/appsutil/log
Skip DB shutdown      : yes
EBS SID               : ebsdb
APPS Schema Username  : apps
Is RAC?               : No
Logical Hostname      :




=========================
Validating oracle home...
=========================
Oracle Home: /u01/install/APPS/12.1.0 exists.


===========================
Validating out directory...
===========================
Out directory: /u01/install/APPS/12.1.0/appsutil/log exists.


============================
Inside getDBHostDetails()...
============================
DB Hostname : apps
DB Domain   : example.com
Logical hostname is not passed, hence using physical hostname details.
Logical hostname: apps.example.com


==========================
Inside setContextName()...
==========================
CONTEXT_NAME: ebsdb_apps


============================
Inside setFileLocations()...
============================


=====================
Inside getDBName()...
=====================
Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_get_db_name.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql

EXIT STATUS: 0
Getting the value of DB Name...
db_name: ebsdb

PDB_DESC_XML: /u01/install/APPS/12.1.0/dbs/ebsdb_PDBDesc.xml
INITPARAM_FILE: /u01/install/APPS/12.1.0/dbs/ebsdb_initparam.sql
DATATOP_FILE: /u01/install/APPS/12.1.0/dbs/ebsdb_datatop.txt


========================
Inside getDBVersion()...
========================
DB_VERSION = db121



==================================
Validating content of TNS_ADMIN...
==================================
File /u01/install/APPS/12.1.0/network/admin/ebsdb_apps/sqlnet.ora exists.
File /u01/install/APPS/12.1.0/network/admin/ebsdb_apps/tnsnames.ora exists.


**** Setting ORACLE_SID to ebsdb
**** Setting TNS_ADMIN to /u01/install/APPS/12.1.0/network/admin/ebsdb_apps
**** Setting ORACLE_HOME to /u01/install/APPS/12.1.0
**** Setting PATH to /u01/install/APPS/12.1.0/bin:/u01/install/APPS/12.1.0/perl/bin:/u01/install/APPS/12.1.0/bin:/usr/bin:/usr/sbin:/u01/install/APPS/12.1.0/appsutil/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/install/APPS/12.1.0/perl/bin:/u01/install/APPS/12.1.0/bin:/usr/bin:/usr/sbin:/u01/install/APPS/12.1.0/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:.




=====================================
Validating APPS schema credentials...
=====================================
Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/validate_apps_password.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/validate_apps_password.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/validate_apps_password.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/validate_apps_password.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/validate_apps_password.log
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Validated APPS credentials.


=============================
Inside checkRACInstances()...
=============================
SQL Command: SELECT DECODE(COUNT(instance_name),0,'RAC-INSTANCES-DOWN','RAC-INSTANCES-RUNNING') FROM gv$instance WHERE instance_name NOT IN (SELECT instance_name from v$instance)

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_rac_instances.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_rac_instances.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_rac_instances.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_rac_instances.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_rac_instances.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_rac_instances.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for RAC instances status.
All the other RAC instances are down. Proceeding further.



==================================
Inside checkOnlineADOPSession()...
==================================
SQL Command: SELECT DECODE(COUNT(adop_session_id),0,'NO-OPEN-SESSION','OPEN-SESSION-EXISTS') FROM ad_adop_sessions WHERE ((prepare_status IN ('N','R')) OR apply_status IN ('N','P') OR cutover_status NOT IN ('X','Y') OR abort_status IN ('N','R')) AND abort_status <> 'Y' AND prepare_status <> 'X' AND node_name IN (SELECT node_name FROM adop_valid_nodes)

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_online_session.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_online_session.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_adop_online_session.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_online_session.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_online_session.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_online_session.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for open ONLINE ADOP session.
No ONLINE ADOP patching cycle. Proceeding further.



====================================
Inside checkHotpatchADOPSession()...
====================================
SQL Command: SELECT DECODE(COUNT(adop_session_id),0,'NO-OPEN-SESSION','OPEN-SESSION-EXISTS') FROM ad_adop_sessions WHERE status <> 'C' AND prepare_status = 'X' AND cutover_status = 'X' AND abort_status = 'X' AND apply_status IN ('N','P') AND cleanup_status = 'N' AND node_name IN (SELECT node_name FROM adop_valid_nodes)

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_hotpatch_session.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_hotpatch_session.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_adop_hotpatch_session.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_hotpatch_session.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_hotpatch_session.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_adop_hotpatch_session.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for open HOTPATCH ADOP session.
No HOTPATCH ADOP patching cycle. Proceeding further.



================================
Inside checkEBSPatchService()...
================================


=====================
Inside getDBName()...
=====================
Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_get_db_name.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_name.sql

EXIT STATUS: 0
Getting the value of DB Name...
db_name: ebsdb



=======================
Inside getDBDomain()...
=======================
Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_domain.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_domain.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_get_db_domain.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_domain.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_domain.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/get_db_domain.sql

EXIT STATUS: 0
Getting the value of DB Name...
db_domain:



==================================
Inside checkServiceNameExists()...
==================================
service_name: ebsdb_ebs_patch
SQL Command: SELECT DECODE(COUNT(service_id),0,'SERVICE-NOT-PRESENT','SERVICE-EXISTS') FROM DBA_SERVICES WHERE upper(name)=upper('ebsdb_ebs_patch')

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_service_exists.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for existence of service: ebsdb_ebs_patch
Service ebsdb_ebs_patch exists.


==================================
Inside checkServiceNameExists()...
==================================
service_name: ebs_patch
SQL Command: SELECT DECODE(COUNT(service_id),0,'SERVICE-NOT-PRESENT','SERVICE-EXISTS') FROM DBA_SERVICES WHERE upper(name)=upper('ebs_patch')

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_service_exists.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_exists.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for existence of service: ebs_patch
Service ebs_patch exists.
=================================

Inside deleteEBSPatchService()...

=================================



=================================
Inside checkServiceIsRunning()...
=================================
service_name: ebs_patch
SQL Command: SELECT DECODE(COUNT(service_id),0,'SERVICE-NOT-RUNNING','SERVICE-RUNNING') FROM sys.v_$active_services WHERE upper(name)=upper('ebs_patch')

Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_running.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_running.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_check_service_running.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_running.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_running.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/check_service_running.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking for service status: ebs_patch
Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/delete_service.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/delete_service.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/delete_service.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/delete_service.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Deletion of service ebs_patch is successful.
LOG FILE: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/delete_service.out.


================================
Inside generateInitParamSQL()...
================================


Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_init_param.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_init_param.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_generate_init_param.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_init_param.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_init_param.out
pattern: ERROR:
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the parameter values and writing to text file.

Adding the parameter "PROCESSES" whose data type is "3".
Adding the parameter "SESSIONS" whose data type is "3".
Ignoring the parameter "EVENT" as it is in the EXCLUDED LIST.
Adding the parameter "SHARED_POOL_SIZE" whose data type is "6".
Adding the parameter "SHARED_POOL_RESERVED_SIZE" whose data type is "6".
Mandatory parameter "PGA_AGGREGATE_LIMIT" is customized.
Adding the parameter "PGA_AGGREGATE_LIMIT" whose data type is "6".
Adding the parameter "NLS_TERRITORY" whose data type is "2".
Adding the parameter "NLS_SORT" whose data type is "2".
Adding the parameter "NLS_DATE_FORMAT" whose data type is "2".
Adding the parameter "NLS_NUMERIC_CHARACTERS" whose data type is "2".
Adding the parameter "NLS_COMP" whose data type is "2".
Adding the parameter "NLS_LENGTH_SEMANTICS" whose data type is "2".
Ignoring the parameter "SERVICE_NAMES" as it is in the EXCLUDED LIST.
Adding the parameter "SGA_TARGET" whose data type is "6".
Adding the parameter "DB_BLOCK_CHECKSUM" whose data type is "2".
Adding the parameter "DB_BLOCK_SIZE" whose data type is "3".
Ignoring the parameter "COMPATIBLE" as it is in the EXCLUDED LIST.
Adding the parameter "LOG_ARCHIVE_FORMAT" whose data type is "2".
Adding the parameter "LOG_BUFFER" whose data type is "6".
Adding the parameter "LOG_CHECKPOINT_INTERVAL" whose data type is "3".
Adding the parameter "LOG_CHECKPOINT_TIMEOUT" whose data type is "3".
Adding the parameter "DB_FILES" whose data type is "3".
Adding the parameter "LOG_CHECKPOINTS_TO_ALERT" whose data type is "1".
Adding the parameter "DML_LOCKS" whose data type is "3".
Adding the parameter "UNDO_MANAGEMENT" whose data type is "2".
Ignoring the parameter "UNDO_TABLESPACE" as it is in the EXCLUDED LIST.
Adding the parameter "TEMP_UNDO_ENABLED" whose data type is "1".
Adding the parameter "DB_BLOCK_CHECKING" whose data type is "2".
Adding the parameter "RECYCLEBIN" whose data type is "2".
Adding the parameter "SEC_CASE_SENSITIVE_LOGON" whose data type is "1".
Ignoring the parameter "O7_DICTIONARY_ACCESSIBILITY" as it is in the EXCLUDED LIST.
Adding the parameter "AUDIT_SYS_OPERATIONS" whose data type is "1".
Ignoring the parameter "LOCAL_LISTENER" as it is in the EXCLUDED LIST.
Adding the parameter "SESSION_CACHED_CURSORS" whose data type is "3".
Adding the parameter "PLSQL_CODE_TYPE" whose data type is "2".
Adding the parameter "JOB_QUEUE_PROCESSES" whose data type is "3".
Adding the parameter "_SYSTEM_TRIG_ENABLED" whose data type is "1".
Adding the parameter "CURSOR_SHARING" whose data type is "2".
Adding the parameter "PARALLEL_MIN_SERVERS" whose data type is "3".
Adding the parameter "PARALLEL_MAX_SERVERS" whose data type is "3".
Ignoring the parameter "DB_NAME" as it is in the EXCLUDED LIST.
Adding the parameter "OPEN_CURSORS" whose data type is "3".
Adding the parameter "_SORT_ELIMINATION_COST_RATIO" whose data type is "3".
Adding the parameter "SQL92_SECURITY" whose data type is "1".
Adding the parameter "_B_TREE_BITMAP_PLANS" whose data type is "1".
Adding the parameter "_FAST_FULL_SCAN_ENABLED" whose data type is "1".
Adding the parameter "_LIKE_WITH_BIND_AS_EQUALITY" whose data type is "1".
Adding the parameter "PGA_AGGREGATE_TARGET" whose data type is "6".
Adding the parameter "WORKAREA_SIZE_POLICY" whose data type is "2".
Adding the parameter "_OPTIMIZER_AUTOSTATS_JOB" whose data type is "1".
Adding the parameter "OPTIMIZER_SECURE_VIEW_MERGING" whose data type is "1".
Adding the parameter "PARALLEL_FORCE_LOCAL" whose data type is "1".
Ignoring the parameter "OPTIMIZER_ADAPTIVE_FEATURES" as it is in the EXCLUDED LIST.
Adding the parameter "AQ_TM_PROCESSES" whose data type is "3".
Ignoring the parameter "OLAP_PAGE_POOL_SIZE" as it is in the EXCLUDED LIST.
Adding the parameter "_TRACE_FILES_PUBLIC" whose data type is "1".
Adding the parameter "MAX_DUMP_FILE_SIZE" whose data type is "2".
number_of_event_params: 1
Adding the MANDATORY parameter "_PDB_NAME_CASE_SENSITIVE" whose data type is "1".
Adding the MANDATORY parameter "_DISABLE_ACTUALIZATION_FOR_GRANT" whose data type is "1".


============================
Inside generateDataTops()...
============================


Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_data_tops.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_data_tops.out
Spool File          : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/spool_generate_data_tops.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_data_tops.sql
Removing the file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_data_tops.sql



==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/generate_data_tops.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the data tops and writing to text file.



==================================
Inside createPDBDescriptorXML()...
==================================


==================================
Inside backupPDBDescriptorXML()...
==================================


File /u01/install/APPS/12.1.0/dbs/ebsdb_PDBDesc.xml does not exist.


File /u01/install/APPS/12.1.0/dbs/ebsdb_PDBDesc.xml does not exist.



Generating SQL file : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/create_pdb_desc_xml.sql
SQL output file     : /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/create_pdb_desc_xml.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...

Execute SYSTEM command : sqlplus /nolog @/u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/create_pdb_desc_xml.sql


==============================
Inside searchFileContents()...
==============================
log_file: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/create_pdb_desc_xml.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
PDB Descriptor XML /u01/install/APPS/12.1.0/dbs/ebsdb_PDBDesc.xml created successfully.
LOG FILE: /u01/install/APPS/12.1.0/appsutil/log/TXK_PRE_PDB_Wed_Dec_2_11_51_42_2020/create_pdb_desc_xml.out.




Exiting from the script.
Ended: Wed Dec  2 11:52:03 GMT 2020


Install Oracle Database 19c(reference:-Oracle Database Upgrade Guide 19c(chapter B)

created this directory for 19c OH

/u01/install/APPS/19.3.0

 

Oracle Database Releases That Support Direct Upgrade Review the supported options for direct upgrades to the latest Oracle Database release. You can perform a direct upgrade to the new release from the following releases: • 11.2.0.4 • 12.1.0.2 • 12.2.0.1 • 18 The path that you must take to upgrade to the latest Oracle Database release depends on the release number of your current database. If your current Oracle Database is a release earlier than 11.2.0.4, then you cannot directly upgrade your Oracle Database to the latest release. In this case, you are required to upgrade to an intermediate release before upgrading to Oracle Database 19c. If you cannot carry out a direct upgrade, then carry out an upgrade to the most recent release where direct upgrades are supported.

 

Check the compataibility parameter of the existing database. It should be set to min 11.2.0

 

In our database:-

SQL> SELECT name, value FROM v$parameter

 WHERE name = 'compatible';  2

 

NAME

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

VALUE

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

compatible

12.1.0

 

 Before using DBUA to upgrade your system, Oracle strongly recommends that you run the Pre-Upgrade Information Tool manually. DBUA runs the Pre-Upgrade Information Tool as part of the prerequisite checks it performs before starting the upgrade. However, to reduce downtime, Oracle recommends that you run the Pre-Upgrade Information Tool as part of your upgrade planning, so that you can analyze the database, and take proactive steps before your planned upgrade date.


















Create a new environment file for 19c 

 

[oracle@apps ~]$ cat cdb_19c.env

export ORACLE_HOME=/u01/install/APPS/19.3.0

export ORACLE_SID=ebscdb

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin

export ORACLE_BASE=/u01/install/APPS

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1

##export ORA_NLS10=/u02/oratest/app/oracle/19.3/nls/data/9idata

[oracle@apps ~]$


Apply additional 19c RDBMS patches


Apply the latest Release Update for your platform documented in Table 1.6.1 or Table 1.7 of Section 3.3 of My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes.

Create the nls/data/9idata directory


In the 19c Oracle Home


[oracle@apps bin]$ cd /u01/install/APPS/19.1.3/nls/data/old

[oracle@apps old]$ $ORACLE_HOME/perl/bin/perl cr9idata.pl

Creating directory /u01/install/APPS/19.1.3/nls/data/9idata ...

Copying files to /u01/install/APPS/19.1.3/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u01/install/APPS/19.1.3/nls/data/9idata!



Create appsutil.zip and copy it to the database tier for new 19c home.


[oracle@apps ~]$ cd $AD_TOP/bin

[oracle@apps bin]$ perl admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/log/MakeAppsUtil_12031012.log

output located at /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

[oracle@apps bin]$ cp /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/out/appsutil.zip /u01/install/APPS/19.3.0

[oracle@apps bin]$



Install JRE 8


[oracle@apps ~]$ cd $ORACLE_HOME/appsutil

[oracle@apps appsutil]$ cp -r $ORACLE_HOME/jdk/jre .

[oracle@apps appsutil]$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext

[oracle@apps appsutil]$


Create the CDB

Some considerations:-

SID for CDB will be different than the present 12c SID. The 12c SID will become the 19c PDB and hence all the data will be migrated to 19c PDB.

run DBCA from 19c Oracle HOME









SID mentioned as ebscdb












Choose the NLS characterset properly and it should match the source 12c NLS characterset.





Run datapatch on the CDB

[oracle@apps oradata]$ export ORACLE_SID=ebscdb
[oracle@apps oradata]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 14 14:31:57 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


[oracle@apps ~]$ $ORACLE_HOME/OPatch/datapatch
SQL Patching tool version 19.3.0.0.0 Production on Fri Dec  4 09:20:40 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /u01/install/APPS/cfgtoollogs/sqlpatch/sqlpatch_24399_2020_12_04_09_20_40/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.3.0.0.0 Release_Update 190410122720: Installed
  PDB CDB$ROOT:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 03-DEC-20 12.45.41.769217 PM
  PDB PDB$SEED:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 03-DEC-20 12.54.54.421806 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

SQL Patching tool complete on Fri Dec  4 09:21:12 2020


Create the CDB MGDSYS schema

 

[oracle@apps admin]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 4 09:23:35 2020

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> @catmgd.sql;

 

Session altered.

 

 

Session altered.

 

.. Creating MGDSYS schema

 

User created.

 

.. lock the user and expire the password

 

User altered.

 

.. Granting permissions to MGDSYS

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Call completed.

 

 

Call completed.

 

 

Call completed.

 

 

Call completed.

 

 

Call completed.

 

 

Call completed.

 

 

Session altered.

 

 

Session altered.

 

 

Call completed.

 

.. Load java components for tag translation

 

Session altered.

 

 

Call completed.

 

 

Session altered.

 

.. Check whether java has been loaded successfully

 

OWNER

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

OBJECT_NAME

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

STATUS

-------

PUBLIC

oracle/mgd/idcode/IDCodeTranslator

VALID

 

MGDSYS

oracle/mgd/idcode/IDCodeTranslator

VALID

 

OWNER

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

OBJECT_NAME

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

STATUS

-------

 

 

.. Creating Oracle IDCode Types

 

Session altered.

 

 

Type created.

 

No errors.

 

Type created.

 

No errors.

.. the MGD_ID type object

 

Type created.

 

No errors.

 

Type created.

 

No errors.

 

Type created.

 

No errors.

 

Session altered.

 

.. Creating Oracle IDCode Dictionary Tables

 

Session altered.

 

.. Creating mgd_id_xml_validator table

 

Table created.

 

 

Comment created.

 

 

Comment created.

 

.. Creating the mgd_id_category_tab table

 

Table created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

.. Creating mgd_id_scheme_tab table

 

Table created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

.. Creating mgd_id_lookup_table table

 

Table created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Sequence created.

 

 

Session altered.

 

.. Creating Oracle IDCode views

 

Session altered.

 

.. Creating the mgd_id_category views

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

.. Creating the mgd_id_scheme views

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Session altered.

 

.. Creating Oracle IDCode Utility Package Specification in MGDSYS

 

Session altered.

 

 

Package created.

 

No errors.

 

Package created.

 

 

Session altered.

 

.. Creating Oracle IDCode Internal Utility Package Specification in MGDSYS

 

Session altered.

 

 

Package created.

 

No errors.

 

Package created.

 

 

Session altered.

 

.. Creating Oracle IDCode Type Body in MGDSYS

 

Session altered.

 

 

Type body created.

 

No errors.

 

Type body created.

 

 

Session altered.

 

.. Creating Oracle IDCode Utility Package Body in MGDSYS

 

Session altered.

 

 

Package body created.

 

No errors.

 

Package body created.

 

 

Session altered.

 

.. Creating Oracle IDCode Internal Utility Package Body in MGDSYS

 

Session altered.

 

 

Package body created.

 

No errors.

 

Package body created.

 

 

Session altered.

 

.. Creating Oracle IDCode triggers

 

Session altered.

 

.. Creating the user_mgd_id_scheme_ins_trig trigger for inserting into user_mgd_id_category view

 

Trigger created.

 

No errors.

.. Creating the user_mgd_id_category_del_trig trigger for deleting from user_mgd_id_category view

 

Trigger created.

 

No errors.

.. Creating the user_mgd_id_category_upd_trig trigger for updating user_mgd_id_category view

 

Trigger created.

 

No errors.

.. Creating the idcode_scheme_before_ins_trig trigger which validates each TDT before insertion and sets the type_name and encoding fields appropriately

 

Trigger created.

 

No errors.

.. Creating the idcode_scheme_before_upd_trig trigger which validates the new scheme after update and refreshes the category to which it belongs

 

Trigger created.

 

No errors.

.. Creating the user_mgd_id_scheme_ins_trig trigger for inserting into user_mgd_id_scheme view

 

Trigger created.

 

No errors.

.. Creating the user_mgd_id_scheme_del_trig trigger for deleting from user_mgd_id_scheme view

 

Trigger created.

 

No errors.

.. Creating the user_mgd_id_scheme_upd_trig trigger for updating user_mgd_id_scheme view

 

Trigger created.

 

No errors.

.. Creating the mgd_id_lookup_table_ins_trig trigger for inserting into mgd_id_lookup_table

 

Trigger created.

 

No errors.

.. Creating the mgd_id_lookup_table_upd_trig trigger for updating mgd_id_lookup_table

 

Trigger created.

 

No errors.

 

Session altered.

 

.. Creating Public Synonyms

 

Session altered.

 

.. Creating Oracle IDCode Privileges for Types and Packages

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

.. Creating Oracle IDCode Public Synonymns

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Synonym created.

 

 

Procedure created.

 

No errors.

 

Procedure created.

 

 

Session altered.

 

.. Load metadata

 

Session altered.

 

 

PL/SQL procedure successfully completed.

 

No errors.

 

PL/SQL procedure successfully completed.

 

No errors.

 

PL/SQL procedure successfully completed.

 

No errors.

Make sure these values look OK:

 

Call completed.

 

 

XML_VALIDATOR_CHAR_LENGTH

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

                     5780

 

 

URL

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

DBMS_LOB.GETLENGTH(CONTENT) L

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

http://www.onsepc.com/managertranslation.xml

                       9732 Y

 

 

 

CATEGORY_N CATEGORY_ID

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

EPC                  1

 

 

CATEGORY_ID TYPE_NAME  ENCODINGS              XML_TDTS_CHAR_LENGTH

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

          1 GIAI-64    LEGACY,BINARY,PURE_IDE                14995

                       NTITY,TAG_ENCODING

 

          1 GIAI-96    LEGACY,BINARY,PURE_IDE                14486

                       NTITY,TAG_ENCODING

 

          1 GID-96     LEGACY,BINARY,PURE_IDE                 2686

                       NTITY,TAG_ENCODING

 

          1 GRAI-64    LEGACY,BINARY,PURE_IDE                18019

                       NTITY,TAG_ENCODING

 

CATEGORY_ID TYPE_NAME  ENCODINGS              XML_TDTS_CHAR_LENGTH

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

 

          1 GRAI-96    LEGACY,BINARY,PURE_IDE                17590

                       NTITY,TAG_ENCODING

 

          1 SGLN-64    LEGACY,BINARY,PURE_IDE                19454

                       NTITY,TAG_ENCODING

 

          1 SGLN-96    LEGACY,BINARY,PURE_IDE                18995

                       NTITY,TAG_ENCODING

 

          1 SGTIN-64   ONS_HOSTNAME,LEGACY,BI                22884

 

CATEGORY_ID TYPE_NAME  ENCODINGS              XML_TDTS_CHAR_LENGTH

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

                       NARY,PURE_IDENTITY,TAG

                       _ENCODING

 

          1 SGTIN-96   ONS_HOSTNAME,LEGACY,BI                22531

                       NARY,PURE_IDENTITY,TAG

                       _ENCODING

 

          1 SSCC-64    LEGACY,BINARY,PURE_IDE                16644

                       NTITY,TAG_ENCODING

 

          1 SSCC-96    LEGACY,BINARY,PURE_IDE                16450

 

CATEGORY_ID TYPE_NAME  ENCODINGS              XML_TDTS_CHAR_LENGTH

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

                       NTITY,TAG_ENCODING

 

          1 USDOD-64   LEGACY,BINARY,PURE_IDE                 2273

                       NTITY,TAG_ENCODING

 

          1 USDOD-96   LEGACY,BINARY,PURE_IDE                 2293

                       NTITY,TAG_ENCODING

 

 

13 rows selected.

 

 

Session altered.

 

 

Session altered.

 

.. lock the user and expire the password

 

User altered.

 

.. Validate MGD installation

MGD is valid

 

PL/SQL procedure successfully completed.

 

 

Session altered.

 

SQL>


Create the CDB TNS files

[oracle@apps ~]$ cd $ORACLE_HOME/appsutil

[oracle@apps appsutil]$ echo $ORACLE_HOME

/u01/install/APPS/19.3.0

[oracle@apps appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u01/install/APPS/19.3.0

 

Oracle Home being passed: /u01/install/APPS/19.3.0

 

 

[oracle@apps log]$ cd $ORACLE_HOME/appsutil/bin

[oracle@apps bin]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 4 09:42:10 2020

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> select name from v$database;

 

NAME

---------

EBSDBCDB

 

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@apps bin]$ perl txkGenCDBTnsAdmin.pl -dboraclehome=/u01/install/APPS/19.1.3 -cdbname=ebscdb -cdbsid=ebscdb -dbport=1521 -outdir=/u01/install/APPS/19.1.3/appsutil/log

 

 

Script Name    : txkGenCDBTnsAdmin.pl

Script Version : 120.0.12020000.9

Started        : Fri Dec  4 09:42:45 GMT 2020

 

Log File       : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/txkGenCDBTnsAdmin.log

 

 

-----------

Values used

-----------

Database Oracle Home    : /u01/install/APPS/19.1.3

CDB NAME                : ebscdb

CDB SID                 : ebscdb

Database port           : 1521

OUT Directory           : /u01/install/APPS/19.1.3/appsutil/log

Is RAC?                 : No

Virtual Hostname        :

 

 

 

 

=========================

Validating oracle home...

=========================

Oracle Home: /u01/install/APPS/19.3.0 exists.

 

 

===========================

Validating out directory...

===========================

Out directory: /u01/install/APPS/19.3.0/appsutil/log exists.

 

 

============================

Inside getDBHostDetails()...

============================

DB Hostname : apps

DB Domain   : example.com

 

 

=====================

Inside getDBPort()...

=====================

DB Port passed as an argument, using the same.

DB Port: 1521

 

 

======================================

Inside generateCDBTNSAdminContent()...

======================================

Creating the directory: /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_bkp

Creating the directory: /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp

listener_template: listener_ora_cdb_db19.tmp

sqlnet_template: sqlnet_ora_cdb_db19.tmp

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/template/listener_ora_cdb_db19.tmp

TARGET : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/listener.ora

 

 

===================================

Inside replaceContextVariables()...

===================================

File /u01/install/APPS/19.3.0/network/admin/listener.ora does not exist.

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/listener.ora

TARGET : /u01/install/APPS/19.3.0/network/admin/listener.ora

 

 

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/template/tnsnames_ora_cdb_db121.tmp

TARGET : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/tnsnames.ora

 

 

===================================

Inside replaceContextVariables()...

===================================

File /u01/install/APPS/19.3.0/network/admin/tnsnames.ora does not exist.

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/tnsnames.ora

TARGET : /u01/install/APPS/19.3.0/network/admin/tnsnames.ora

 

 

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/template/sqlnet_ora_cdb_db19.tmp

TARGET : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/sqlnet.ora

 

 

===================================

Inside replaceContextVariables()...

===================================

File /u01/install/APPS/19.3.0/network/admin/sqlnet.ora does not exist.

 

 

Copying the file

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

SOURCE : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/sqlnet.ora

TARGET : /u01/install/APPS/19.3.0/network/admin/sqlnet.ora

 

 

 

 

==============================

Inside updateCDBSqlNetOra()...

==============================

Updating the IFILE entry...

 

 

 

 

 

Exiting from the script.

Ended: Fri Dec  4 09:42:45 GMT 2020

 

 

[oracle@apps bin]$


 Shut down the CDB

Use SQL*Plus to connect to the CDB as SYSDBA and use the following command to shut down the database:

sqlplus "/ as sysdba"
SQL> shutdown;

 

 

 

SQL> show con_name;

 

CON_NAME

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

CDB$ROOT

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.


This completes the database steps needed before you start the database upgrade process which is continued in the next post https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-2.html














16 comments:

  1. Hi,

    Did you take any corrective action for the following objects?

    The script failed for the below
    FAIL
    HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
    ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137666
    ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137650
    ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137642
    ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137658

    Or were they resolved by the adop patches you applied?

    Thanks.

    ReplyDelete
    Replies
    1. Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download Now

      >>>>> Download Full

      Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download LINK

      >>>>> Download Now

      Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download Full

      >>>>> Download LINK uF

      Delete
  2. Hi

    In this situation, you need to raise a SR. I did the same. As this was for my test environment, so i proceeded with the next steps, which you shouldn't do when working on a customer environment.

    ReplyDelete
    Replies
    1. Thanks Samrat. Actually, just wanted to check if you skipped it on your test environment. If yes, did you face any issues with 19c upgrade?

      After 12c got in extended support mode and Oracle is not providing assistance ever for such scenario if the client does not have extended coverage. And just for the 4 objects, it appears to be difficult to convince Oracle to support or the client to buy that.

      Thanks again for your reply. Let me know if my understanding/assumption of your test trial with these objects was correct.

      Delete
  3. Hi

    Yes i skipped it and did not face any issues with 19c upgrade

    Thanks

    ReplyDelete
  4. hi, the part isnt loading.. can you please share the part 2 link.

    thanks

    ReplyDelete
  5. is the CDB database creation part is mandetory

    ReplyDelete
  6. Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download Now

    >>>>> Download Full

    Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download LINK

    >>>>> Download Now

    Oracle Apps Dba Pieces: Upgrade Ebs 12.2 Database 12C(12.1.0.2) To 19C(19.3)-Part 1 >>>>> Download Full

    >>>>> Download LINK

    ReplyDelete