Solution to ERRORMSG: Invalid SYSTEM database user credential when running txkPostPDBCreationTasks.pl as a part of an EBS database upgrade

When upgrading an EBS database from version 12c to 19c, you have to convert to the mulitenant architechture.

Oracle supplies script for this entire operation as outlined in Doc ID 580629.1 "Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c"

When reaching section 6 "Run the post PDB script" you are supposed to run the txkPostPDBCreationTasks.pl script which updates the PDB information.

I found that this script is essential and it must run to completion. It is restartable, so if it fails for some reason, you can fix the root cause and rerun the script.

Here is the error I ran into. The logfile shows it, and it's simple to understand:

==============================
Inside searchFileContents()...
==============================
log_file: /u01/app/oracle/product/19x00_231017/appsutil/log/TXK_POST_PDB_Fri_Jan_26_19_15_34_2024/validate_system_password.log
pattern: ERROR
================
Pattern found...
================
EXIT STATUS: 1
Invalid SYSTEM database user credentials.
LOG FILE: /u01/app/oracle/product/19x00_231017/appsutil/log/TXK_POST_PDB_Fri_Jan_26_19_15_34_2024/validate_system_password.log.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19x00_231017/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME    : Fri Jan 26 19:18:47 2024
FUNCTION: main::validateSystemSchemaCredentials [ Level 1 ]
ERRORMSG: Invalid SYSTEM database user credentials.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19x00_231017/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME    : Fri Jan 26 19:18:47 2024
FUNCTION: main::validateSystemSchemaCredentials [ Level 1 ]
ERRORMSG: Invalid SYSTEM database user credentials.

So the system password was not set correctly. Note that this is the only error in the logfile. All other tests had passed!

Since this is a non-EBS password, it can be set manually with the alter user statement, which I did.

Log on to the cdb:

export ORACLE_SID=cdb
sqlplus / as sysdba
alter user system identified by by mysecretpassword container=all;

Test your connection, preferrably from the apptier. Log on to the PDB, in this case EBSTEST, using the system account:

sqlplus system@EBSTEST
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show user
USER is "SYSTEM"

So the password is now confirmed to be correct.

You can now rerun the script

export ORACLE_SID=cdb

# source the environment
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=<your 19c OH path here>

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=<your 19c OH path here> \
-outdir=<your 19c OH path here>/appsutil/log -cdbsid=cdb -pdbsid=EBSTEST \
-appsuser=<APPSUSER> -dbport=<LISTENING PORT> -servicetype=onpremise

Why is it essential that the script runs to completion, correctly without errors?

First of all, it updates important tables in the APPS schema. During an upgrade to 19c, the UTL_FILE_DIRS are no longer found in the database init.ora file or the spfile. It is saved in the tables v$parameter and v$parameter2.

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

and 

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

should yield the same output as the text file found in your $ORACLE_HOME/dbs/EBSTEST_utlfiledir.txt file.

In my case the

$ORACLE_HOME/dbs/EBSTEST_utlfiledir.txt

showed

/sw/oracle/product/temp/EBSTEST
/usr/tmp/EBSTEST
/<oracle_19c_home>/appsutil/outbound/EBSTEST_<myservername>

Second, it creates two environment files in your $ORACLE_HOME that can be used to source the cdb and the PDB environments,respectively. The are called

cdb_<yourservername>.env

EBSTEST_<yourservername>.env

Third, it creates your database server's context file, $ORACLE_HOME/appsutil/EBS_<yourservername>.xml

Without all of the above correctly done, you cannot proceed with running autoconfig on your apptier. It will keep failing until you fix the database-part of your migration.