top of page
Search

Oracle EBS Database upgrade 12c to 19c -- Part 1

IMPORTANT Points

  1. When upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will be converted to the multitenant architecture, with a Container Database (CDB) and a single Pluggable Database. Only multitenant architecture databases are certified for Oracle E-Business Suite with Oracle Database 19c.

  2. During the upgrade, you will perform steps to migrate directories defined for PL/SQL File I/O to database directory objects.

  3. This requirement is due to the de-support in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.

  4. Oracle E-Business Suite requires Oracle homes to be writable, not read-only


Before Upgrade

Run 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.

Download hcheck.sql from 136697.1

[oracle@ebsdb ~]$ ls -lrt hcheck.sql
-rw-r--r-- 1 oracle oinstall 144729 Nov 22 10:27 hcheck.sql

SQL> @hcheck.sql
Got below errors 
FAIL -- Bug 3532977 - Space in source$ is not reused when objects are recreated (Doc ID 3532977.8)
HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1).

SOURCE$ has 283 rows for 22 OBJ# values not in OBJ$.

Drop table can leave trigger information in SOURCE$ for the dropped table. This can waste space in SOURCE$, especially on systems where tables with triggers are repeatedly created and dropped.

This problem shows as rows in SOURCE$ where the OBJ# is not in OBJ$


FAIL --HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)

ORPHAN SEG$: SegType=INDEX TS=381 RFILE/BLOCK=401/177675

ORPHAN SEG$: SegType=INDEX TS=381 RFILE/BLOCK=401/140907

ORPHAN SEG$: SegType=LOB TS=381 RFILE/BLOCK=401/134987

ORPHAN SEG$: SegType=LOB TS=381 RFILE/BLOCK=401/177659

ORPHAN SEG$: SegType=TYPE2 UNDO TS=368 RFILE/BLOCK=379/33


There is a SEG$ entry that does not appear to have a matching dictionary entry. The check here looks at data, index, lob, undo and type2 undo segments (SEG$) and tries to find the matching dictionary entry pointing at that segment. The entries listed have no matching entries found.


Typically operations work down to the SEG$ entry from the object of interest and do not try to work back up from a SEG$ to find its owning object. Hence a stray SEG$ by itself may not have much impact, other than taking up space and preventing a tablespace from being dropped.

However, if there is some other hcheck HCK* error for an object having a missing SEG$ then that entry is likely to cause ORA-600 or other errors if attempted to be used.


http://oracleappstechnology.blogspot.com/2008/05/seg-fet-uet.html


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


Patches need to apply before the upgrade

set serveroutput on;
DECLARE
TYPE p_patch_array_type is varray(30) of varchar2(10);
p_patchlist p_patch_array_type;
p_patch_status varchar2(15);
p_appl_top_id number;
p_result varchar2(15);
p_instance varchar2(15);
procedure println(msg in varchar2)
is
begin
dbms_output.enable(1000000);
dbms_output.put_line(msg);
end;
BEGIN
p_patchlist:= p_patch_array_type('26834480','28840822','25452805','26052406','26521736','30433124','31088182','31349591','31800803','33346385','30611319','28732161');
println('=============================');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('R12',-1,p_patchlist(i));
case p_patch_status
when 'EXPLICIT' then
p_result := 'APPLIED';
else
p_result := p_patch_status;
end case;
println('Patch ' || p_patchlist(i)|| ' - ' || ' - IS ' || p_result);
end loop;
println('.');
END;
/

Patches need to be applied:

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

Patch 26521736 - - IS NOT_APPLIED

Patch 30433124 - - IS NOT_APPLIED

Patch 31088182 - - IS NOT_APPLIED

Patch 31349591 - - IS NOT_APPLIED

Patch 31800803 - - IS NOT_APPLIED

Patch 33346385 - - IS NOT_APPLIED

Patch 30611319 - - IS NOT_APPLIED

Patch 28732161 - - IS NOT_APPLIED


You can use the below commands to apply patches in downtime mode.

adop phase=apply patches=33346385 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=31349591 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=30433124 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=31088182 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=26521736 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=28732161 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=30611319 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=apply patches=31800803 workers=8 patchtop=/u01/patches/preq19c apply_mode=downtime
adop phase=cleanup
adop -status
===============================================================
Node Name       Node Type  Phase           Status          Started              Finished             Elapsed
--------------- ---------- --------------- --------------- -------------------- -------------------- ------------
ebsapp1         master     APPLY           COMPLETED       2021/11/24 20:39:59  2021/11/25 11:56:11  15:16:12
                           CLEANUP         COMPLETED       2021/11/25 12:05:47  2021/11/25 13:06:33  1:00:46

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

Patch 25452805 - - IS APPLIED

Patch 26052406 - - IS APPLIED

Patch 26521736 - - IS APPLIED

Patch 30433124 - - IS APPLIED

Patch 31088182 - - IS APPLIED

Patch 31349591 - - IS APPLIED

Patch 31800803 - - IS APPLIED

Patch 33346385 - - IS APPLIED

Patch 30611319 - - IS APPLIED

Patch 28732161 - - IS APPLIED


Post-Installation steps for PATCH 31800803

1. On the Application Tier (as the APPLMGR user):

./adstpall.sh

Run AutoConfig on the RUN file system and start services.

sh $ADMIN_SCRIPS_HOME/adautocfg.sh
./adstrall.sh

source the environment variables for Oracle Applications

. ./<APPL_TOP>/APPS<CONTEXT_NAME>.env

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

[applmgr@ebsapp1 bin]$ pwd
/u01/app/ebsapp/fs1/EBSapps/appl/ad/12.0.0/bin
[applmgr@ebsapp1 bin]$ ls -lrt admkappsutil.pl
-rwxr-xr-x. 1 applmgr oinstall 7232 Nov 24  2012 admkappsutil.pl
perl $AD_TOP/bin/admkappsutil.pl
[applmgr@ebsapp1 ~]$ ls -lrt /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/out/appsutil.zip
-rw-r--r-- 1 applmgr oinstall 4045255 Nov 25 17:38 /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/out/appsutil.zip

on Database Tier,

mv  $ORACLE_HOME/appsutil  $ORACLE_HOME/appsutil_bkp

SCP the appsutil.zip file to the <RDBMS ORACLE_HOME> and Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>

[applmgr@ebsapp1 ~]$ scp /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/out/appsutil.zip  oracle@192.168.56.101:/u01/app/oracle/product/12.1.0/dbhome_1                                                                                                 
cd $ORACLE_HOME
unzip -o appsutil.zip

Run AutoConfig


VALIDATE SEC_CASE_SENSITIVE_LOGON - should be FALSE

SQL> show parameter SEC_CASE_SENSITIVE_LOGON;


NAME TYPE VALUE

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

sec_case_sensitive_logon boolean FALSE



Create the initialization parameter setup files

Run the following commands to create the $ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

$ export ORACLE_SID=PROD

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \

-outdir=$ORACLE_HOME/appsutil/log -appsuser=apps \

-dbsid=$ORACLE_SID -skipdbshutdown=yes


---Execution log for above

[oracle@ebsdb appsutil]$ pwd

/u01/app/oracle/product/12.1.0/dbhome_1/appsuti

[oracle@ebsdb appsutil]$ ls -lrt txkSetCfgCDB.env

-rw-r--r-- 1 oracle oinstall 3736 Nov 25 17:38 txkSetCfgCDB.env

[oracle@ebsdb appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

Oracle Home being passed: /u01/app/oracle/product/12.1.0/dbhome_1


[oracle@ebsdb bin]$ pwd

/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/bin


[oracle@ebsdb bin]$ ls -lrt txkOnPremPrePDBCreationTasks.pl

-rwxr-xr-x 1 oracle oinstall 94076 Nov 25 17:38 txkOnPremPrePDBCreationTasks.pl


[oracle@ebsdb bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \

> -outdir=$ORACLE_HOME/appsutil/log -appsuser=apps \

> -dbsid=$ORACLE_SID -skipdbshutdown=yes

Enter the APPS Password:

NOTE: It creates some SQL files to get db_details and removes them automatically.

[oracle@ebsdb dbs]$ ls -lrt | tail -5

-rw-r----- 1 oracle oinstall 6656 Nov 25 18:18 spfilePROD.ora

-rw-r--r--. 1 oracle oinstall 21764 Nov 25 18:19 initPROD_noaq.ora

-rw-r--r--. 1 oracle oinstall 0 Nov 25 18:19 PROD_ebsdb_ifile.ora

-rw-r--r-- 1 oracle oinstall 3408 Nov 25 18:49 PROD_initparam.sql

-rw-r--r-- 1 oracle oinstall 414 Nov 25 18:49 PROD_datatop.txt

-rw-r--r-- 1 oracle oinstall 41136 Nov 25 18:49 PROD_PDBDesc.xml_Thu_Nov_25_18_49_17_2021

-rw-r--r-- 1 oracle oinstall 41136 Nov 25 18:49 PROD_PDBDesc.xml




19C DATABASE Installtion

[root@ebsdb ~]# yum install -y oracle-database-preinstall-19c

Dependency Installed:

net-tools.x86_64 0:2.0-0.25.20131004git.el7

Complete!

[oracle@ebsdb limits.d]$ ls -lrt

-rw-r--r--. 1 root root 1253 Oct 7 14:11 oracle-ebs-server-R12-preinstall.conf

-rw-r--r-- 1 root root 1205 Nov 25 19:11 oracle-database-preinstall-19c.conf

[oracle@ebsdb limits.d]$ pwd

/etc/security/limits.d


[root@ebsdb ~]# usermod -G oper,dba oracle

[root@ebsdb ~]# id oracle

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper)


As ORACLE USER

mkdir /u01/app/oracle/product/19.3.0/dbhome_1/

nohup unzip 19c_rdbms_sw.zip &

Take a NEW TERMINAL

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/

./runInstaller -- setup software only

[root@ebsdb ~]#

--Installation completed


Setting ENV file

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

export ORACLE_SID=PROD

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

export ORACLE_BASE=/u01/app/oracle

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

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


Applying 19c DB patches

Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)

[oracle@ebsdb 19c]$ ls -lrt

-rw-r--r-- 1 oracle oinstall 174277 Nov 26 08:09 p33186784_1913000DBRU_Linux-x86-64.zip --- EBS Consolidated patch

-rw-r--r-- 1 oracle oinstall 1537520814 Nov 26 08:09 p33248420_190000_Linux-x86-64.zip --Database OJVM and DB PSU

-rw-r--r-- 1 oracle oinstall 45245 Nov 26 08:22 p31424070_1913000DBRU_Generic.zip -----one off patch

-rw-r--r-- 1 oracle oinstall 114365 Nov 26 09:20 p17537119_R12_GENERIC.zip ---Latest ETCC checker

-rw-r--r-- 1 oracle oinstall 121955282 Nov 26 09:24 p6880880_190000_Linux-x86-64.zip ----Latest Opatch


1. unzip p17537119_R12_GENERIC.ZIP

./CheckDBpatch.sh

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

A consolidated zip file with the required patches for Database release

12.1.0.2.210420 is available on My Oracle Support via:


Patch 33186784

- EBS RELEASE 12.2 CONSOLIDATED DATABASE FIXES FOR OCT 2021

--Latest OPatch

mv OPatch/ OPatch_bkp

[oracle@ebsdb 19c]$ unzip p6880880_190000_LINUX.zip -d $ORACLE_HOME



unzip p33186784_1913000DBRU_Linux-x86-64.zip

[oracle@ebsdb 19.13.0.0.211019DBRU]$ pwd

/u01/SOFTWARE/PATCHES/19c/etcc-bundle/LINUX_X86-64/database/19.13.0.0.211019DBRU

[oracle@ebsdb 19.13.0.0.211019DBRU]$ ls -lrt

-rw-r--r-- 1 oracle oinstall 126250 Nov 3 03:32 p28318139_1913000DBRU_Generic.zip

-rw-r--r-- 1 oracle oinstall 45245 Nov 3 03:32 p31424070_1913000DBRU_Generic.zip


cd /u01/SOFTWARE/19c/33248420/33192793

[oracle@ebsdb 33192793]$$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@ebsdb 33192793]$$ opatch apply

OPatch succeeded.

[oracle@ebsdb 33192793]$ opatch lsinventory | grep 33192793

33192793, 29517242, 33192793

33192793, 29517242, 33192793

33192793, 29517242, 33192793

33192793, 29517242, 33192793

Patch 33192793: applied on Fri Nov 26 11:33:40 IST 2021

Patch description: "Database Release Update: 19.13.0.0.211019 (33192793)"




[oracle@ebsdb 33192694]$$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@ebsdb 33192694]$$ opatch apply

OPatch succeeded.

[oracle@ebsdb 33192694]$ pwd

/u01/SOFTWARE/PATCHES/19c/33248420/33192694

[oracle@ebsdb 33248420]$ opatch lsinventory | grep 33192694

Patch 33192694: applied on Fri Nov 26 11:17:40 IST 2021

Patch description: "OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)"



[oracle@ebsdb 31424070]$$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@ebsdb 31424070]$$ opatch apply

OPatch succeeded.

[oracle@ebsdb 31424070]$ opatch lsinventory | grep 31424070

Patch 31424070: applied on Fri Nov 26 13:15:35 IST 2021

31424070

[oracle@ebsdb 28318139]$$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@ebsdb 28318139]$ opatch apply

[oracle@ebsdb 28318139]$ opatch lsinventory | grep 28318139

Patch 28318139: applied on Fri Nov 26 13:14:01 IST 2021

28318139

[oracle@ebsdb 28318139]$ pwd

/u01/SOFTWARE/PATCHES/19c/etcc-bundle/LINUX_X86-64/database/19.13.0.0.211019DBRU/28318139



Create the nls/data/9idata directory

As ORACLE user on DB Tier

perl $ORACLE_HOME/nls/data/old/cr9idata.pl

[oracle@ebsdb EBS_Con_patch]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Creating directory /u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata ...

Copying files to /u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata!

[oracle@ebsdb ~]$ cat source_19c.env

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

export ORACLE_SID=PROD

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

export ORACLE_BASE=/u01/app/oracle

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

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

export ORA_NLS10=/u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata



Create appsutil.zip and copy it to 19C ORACLE_HOME

[applmgr@ebsapp1 ~]$ perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/log/MakeAppsUtil_11262101.log

output located at /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/out/appsutil.zip

MakeAppsUtil was completed successfully.


scp /u01/app/ebsapp/fs1/inst/apps/PROD_ebsapp1/admin/out/appsutil.zip oracle@192.168.56.101:/u01/app/oracle/product/19.3.0/dbhome_1/

[oracle@ebsdb dbhome_1]$

19c_rdbms_sw.zip client dbjava env.ora jdbc network oracore perl relnotes schagent.conf srvm

addnode clone dbs has jdk nls oraInst.loc plsql root.sh sdk suptools

apex crs deinstall hs jlib odbc ord precomp root.sh.old slax ucp

appsutil.zip css demo install ldap olap ords QOpatch root.sh.old.1 sqldeveloper usm

assistants ctx diagnostics instantclient lib OPatch oss R root.sh.old.2 sqlj utl

bin cv drdaas inventory md OPatch_bkp oui racg root.sh.old.3 sqlpatch wwg

cfgtoollogs data dv javavm mgw opmn owm rdbms runInstaller sqlplus xdk

[oracle@ebsdb dbhome_1]$ unzip -o appsutil.zip



Install JRE 8

$ cd $ORACLE_HOME/appsutil

$ cp -r $ORACLE_HOME/jdk/jre .

$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext


[oracle@ebsdb dbhome_1]$ cd $ORACLE_HOME/appsutil

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

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


Create the CDB

On the database server node:

DB NAME = CDBPROD

  1. Run the Database Configuration Assistant (DBCA) to create the container database (CDB).

  2. When prompted, click on the "Create Database", "Advanced Configuration", and "General Purpose or Transaction Processing" options.

  3. In the Specify Database Identification screen, check to create an empty container database (CDB) without a PDB.

  4. Set the Global Database Name, and the SID to the new CDB SID (maximum of 8 characters), and check the "Use Local Undo tablespace for PDBs" checkbox. The CDB SID has to be different from the current ORACLE_SID, which will be the PDB SID.

  5. In the "Network Configuration" section, do not create a listener. In the "Specify Configuration Options" section, set the SGA and PGA sizes to 2G and 1G respectively.

  6. Click on the Character Sets tab and choose the Character Set and National Character Set to be the same as in the source database. If the appropriate Character Set does not show up, uncheck the "Show recommended character sets only" box.

  7. In the "Select Database Creation Option" section, click on the "Customize Storage Locations" button. Set the size of the redo log files to be the same as in the source database. Other options can be configured as appropriate.

---COMPLETED


export ORACLE_SID=cdpprod

[oracle@ebsdb ~]$ . oraenv

ORACLE_SID = [cdpprod] ?

The Oracle base remains unchanged with value /u01/app/oracle


[oracle@ebsdb ~]$ datapatch -verbose

sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

SQL Patching tool version 19.13.0.0.0 Production on Sat Nov 27 06:48:59 2021

Copyright (c) 2012, 2021, Oracle. All rights reserved.


Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2098_2021_11_27_06_48_59/sqlpatch_invocation.log

SQL Patching tool complete on Sat Nov 27 06:49:25 2021


Create the CDB MGDSYS schema

sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql



Create the CDB TNS files

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

cd $ORACLE_HOME/appsutil/bin

perl txkGenCDBTnsAdmin.pl -dboraclehome=$ORACLE_HOME \

-cdbname=CDBPROD -cdbsid=CDBPROD -dbport=1531 \

-outdir=$ORACLE_HOME/appsutil/log


[oracle@ebsdb admin]$ ls -lrt

total 16

-rw-r--r-- 1 oracle oinstall 1536 Feb 14 2018 shrept.lst

drwxr-xr-x 2 oracle oinstall 64 Apr 17 2019 samples

-rw-r--r-- 1 oracle oinstall 1522 Nov 27 07:06 listener.ora

-rw-r--r-- 1 oracle oinstall 1203 Nov 27 07:06 tnsnames.ora

-rw-r--r-- 1 oracle oinstall 843 Nov 27 07:06 sqlnet.ora


Complete patching cycle and remove adop created editions

On the current run file system:

$ adop phase=prepare

$ adop phase=actualize_all

$ adop phase=finalize finalize_mode=full

$ adop phase=cutover

On the new run file system:

$ adop phase=cleanup cleanup_mode=full



Set UTL_FILE_DIR location

retrieve the directory path values from the source UTL_FILE_DIR database initialization parameter:

source the present DB env

. $ORACLE_HOME/<sid>_<hostname>.env

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/dbs \

-upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1/ -mode=getUtlFileDir -servicetype=onpremise


[oracle@ebsdb ~]$ echo $ORACLE_HOME/

/u01/app/oracle/product/12.1.0/dbhome_1/

[oracle@ebsdb ~]$ . PROD_ebsdb.env

[oracle@ebsdb ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

> -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/dbs \

> -upgradedhome=/u01/app/or[oracle@ebsdb ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

> -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/dbs \

> -upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1/ -mode=getUtlFileDir -servicetype=onpremise

Enter the APPS Password:

acle/product/19.3.0/dbhome_1/ -mode=getUtlFileDir -servicetype=onpremise

Enter the APPS Password:

Successfully generated the below file with UTL_FILE_DIR content:

/u01/app/oracle/product/12.1.0/dbhome_1/dbs/PROD_utlfiledir.txt


[oracle@ebsdb ~]$ cat /u01/app/oracle/product/12.1.0/dbhome_1/dbs/PROD_utlfiledir.txt | tail -4

/u01/app/oracle/product/19.3.0/dbhome_1/temp/PROD

/u01/app/oracle/product/19.3.0/dbhome_1/temp/PROD

/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/outbound/PROD_ebsdb

/u01/app/oracle/product/19.3.0/dbhome_1/temp/PROD


[oracle@ebsdb appsutil]$ ls -ld /u01/app/oracle/product/19.3.0/dbhome_1/temp/PROD

drwxr-xr-x 2 oracle oinstall 6 Nov 27 14:43 /u01/app/oracle/product/19.3.0/dbhome_1/temp/PROD

[oracle@ebsdb appsutil]$ ls -ld /u01/app/oracle/product/19.3.0/dbhome_1//appsutil/outbound/PROD_ebsdb

drwxr-xr-x 2 oracle oinstall 6 Nov 27 14:44 /u01/app/oracle/product/19.3.0/dbhome_1//appsutil/outbound/PROD_ebsdb


$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/dbs \

-upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1/ -mode=setUtlFileDir -servicetype=onpremise


[oracle@ebsdb appsutil]$ echo $ORACLE_HOME/

/u01/app/oracle/product/12.1.0/dbhome_1/

[oracle@ebsdb appsutil]$ echo $ORACLE_SID

PROD


[oracle@ebsdb appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

> -oraclehome=$ORACLE_HOME -outdir=<Output/Log location> \

> -upgradedhome=/u01/app/oracle/product/1^C

[oracle@ebsdb appsutil]$

[oracle@ebsdb appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

> -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/dbs \

> -upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1/ -mode=setUtlFileDir -servicetype=onpremise

Enter the APPS Password:

Enter the SYSTEM Password:

Script Name : txkCfgUtlfileDir.pl

Script Version : 120.0.12020000.15

Started : Sat Nov 27 14:48:33 IST 2021

Log File : /u01/app/oracle/product/12.1.0/dbhome_1/dbs/TXK_UTIL_DIR_Sat_Nov_27_14_48_25_2021/txkCfgUtlfileDir.log

Context file: /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/PROD_ebsdb.xml exists.

** WARNING: Incorrect value s_applptmp detected on Apps Tier nodes. Please check log for details

Completed : Sat Nov 27 14:48:36 IST 2021

Successfully Completed the script

ERRORCODE = 0 ERRORCODE_END


OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID

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

SYS EBS_DB_DIR_UTIL /u01/app/oracle/temp/PROD 0

SYS EBS_UTL_FILE_DIR_961 /u01/app/oracle/temp/PROD 0

7001803862

SYS EBS_UTL_FILE_DIR_401 /u01/app/oracle/product/19.3.0/dbhome_1/appsutil/outbound/PROD_ebsdb 0

9014799009 /appsutil/outbound/PROD_ebsdb


SYS EBS_UTL_FILE_DIR_802 /u01/app/oracle/product/19.3.0/dbhome_1/ temp/PROD 0

3744430776




Shutdown the APPLICATION Services

./adstpall.sh



Drop SYS.ENABLED$INDEXES (Conditional)

Source 12c DB environment

$ sqlplus "/ as sysdba"

SQL> select count(*) from sys.enabled$indexes;

select count(*) from sys.enabled$indexes

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> drop table sys.enabled$indexes;

drop table sys.enabled$indexes

*

ERROR at line 1:

ORA-00942: table or view does not exist


Remove the MGDSYS schema (Conditional)

Not applicable


Shutdown the Listener - 12c

[oracle@ebsdb appsutil]$ lsnrctl stop prod

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-NOV-2021 15:02:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebsdb.localdomain)(PORT=1531)))

The command completed successfully


[oracle@ebsdb appsutil]$ lsnrctl status prod

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-NOV-2021 15:02:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebsdb.localdomain)(PORT=1531)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

[root@ebsdb ~]# cat /etc/oratab | tail -2

CDBPROD:/u01/app/oracle/product/19.3.0/dbhome_1:N

PROD:/u01/app/oracle/product/12.1.0/dbhome_1:N


Configuraing parameter for 19C upgrade

create pfile from spfile;

Edit newly create pfile with below

Unset the olap_page_pool_size to 0

Add the event EVENT='10946 trace name context forever, level 8454144'

unset local_listener

process = 400

--COMMENT below parameter in pfile

O7_DICTIONARY_ACCESSIBILITY

optimizer_adaptive_features

utl_file_dir

_system_trig_enabled

_sort_elimination_cost_ratio

_b_tree_bitmap_plans

_fast_full_scan_enabled

_like_with_bind_as_equality

_optimizer_autostats_job

_trace_files_public



SQL> show parameter local

NAME TYPE VALUE

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

local_listener string PROD_LOCAL

SQL> alter system set local_listener='';

System altered.

SQL> show parameter local

NAME TYPE VALUE

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

local_listener string


Please check on the below link for Upgrade Part-2

65 views0 comments

Recent Posts

See All

Find a patch that modified a file in EBS R12.2

Retrieve the file details from the apps.ad_files table SELECT file_id, filename, subdir FROM apps.ad_files WHERE filename LIKE '%afcpprog%' FETCH FIRST 1 ROWS ONLY; Get the patch_run_bug_id using the

Comments


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page