Upgrade Oracle EBS 12.2.8 Database to 19.3 (19c): Part 3 – Upgrade DB and Convert to PDB

6
1618

This will be the last post of the series in which we will upgrade our database to 19c. Then, we will convert the database to PDB.

Upgrade Database using DBUA:

Upgrade the database now using DBUA. It’s a very simple and straight forward method using DBUA so follow the screens as below:

Run the Post Upgrade Script generated during the Pre-Upgrade Steps. Then perform miscellaneous post-upgrade tasks needed such as Gathering Stats, Compiling Invalids etc.

SQL> @postupgrade_fixups.sql
Session altered.
SQL> alter trigger SYSTEM.EBS_LOGON compile;
Trigger altered.
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL>@?/rdbms/admin/dbmsxdbschmig.sql
SQL>@?/rdbms/admin/prvtxdbschmig.plb
Gather Statistics:
Copy adgrants.sql from $APPL_TOP/admin to DB tier and run it
SQL> @adgrants.sql APPS
Connected.
PL/SQL procedure successfully completed.
-- adgrants.sql started at 2019-10-21 03:
SQL> @adctxprv.sql Welcome1 CTXSYS
Connecting to SYSTEM
Connected.
PL/SQL procedure successfully completed.
Commit complete.
SQL> grant text datastore access to public;
Grant succeeded.
Compile invalids:
sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

There were around 130k+ objects on Vision Instance I used for this Upgrade. Might be around the same for every customer. The most thing is to ensure you compile all the invalids before you proceed to the next step. You can below for utilizing parallel execution using the CPU threads. I have shown “8” as an example below and you can change it based on the number CPUs you have. Check CPU_COUNT Initialization Parameter.

sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlprp.sql 8
SQL> select count() from dba_objects where status='INVALID'; 138588 SQL> select count() from dba_objects where status='INVALID';
45
SQL> grant text datastore access to public;
Grant succeeded.
Gather statistics for the SYS schema
Copy the adstats.sql from $APPL_TOP/admin/adstats.sql to Database Tier and run as below:
$ sqlplus "/ as sysdba"
SQL>alter system enable restricted session;
SQL>@adstats.sql
$ sqlplus "/ as sysdba"
SQL>alter system disable restricted session;
SQL>exit;

Create PDB Descriptor File:

Now that we have successfully upgraded our EBS Database from 12.1.0.2 to 19.3, we need to convert our current Non-CDB Database to PDB.
We need to create the necessary PDB XML descriptor files needed using “txkOnPremPrePDBCreationTasks.pl”. This will also shutdown the database i.e. 19c Non-CDB Database which we have upgraded. You should not open the database again till the entire PDB migration process is complete.

[oratest@ebn1 dbs]$ cd $ORACLE_HOME/appsutil
[oratest@ebn1 appsutil]$ pwd
/u02/oratest/app/oracle/19.3/appsutil
[oratest@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oratest/app/oracle/19.3
Oracle Home being passed: /u02/oratest/app/oracle/19.3
[oratest@ebn1 appsutil]$ export ORACLE_SID=TEST
[oratest@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin
[oratest@ebn1 bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log -appsuser=apps -dbsid=TEST

Update CDB Initialization Parameters:

In previous posts, we have generated initparam files needed for the CDB and we are going to use them now.

[oratest@ebn1 bin]$ cd $ORACLE_HOME/appsutil
[oratest@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oratest/app/oracle/19.3
Oracle Home being passed: /u02/oratest/app/oracle/19.3
[oratest@ebn1 appsutil]$ export ORACLE_SID=testcdb
[oratest@ebn1 appsutil]$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/dbs/TEST_initparam.sql
SQL> alter system set LOCAL_LISTENER="ebn1:1521" scope=both;
System altered.

Check for PDB Violations:

Before we migrate to PDB, we need to check for any violations that will impact the Non-CDB to PDB conversion. I have seen only violation and that can be ignored.

PDB$SEED SQL Patch ERROR RESOLVED
‘19.3.0.0.0 Release_Update 1904101227’ is installed in the CDB but no release updates are installed in the PDB

[oratest@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil
[oratest@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oratest/app/oracle/19.3
Oracle Home being passed: /u02/oratest/app/oracle/19.3
[oratest@ebn1 appsutil]$ export ORACLE_SID=testcdb
[oratest@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin
[oratest@ebn1 bin]$ perl txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -cdbsid=testcdb -pdbsid=TEST -servicetype=onpremise

Create PDB:

Finally, we have reached the important step of converting the Non-CDB database to PDB. Before running the PDB creation script “txkCreatePDB.pl” ensure that you have
– Compiled all Invalid Objects
– There is enough Disk Space
– Check SGA sizing

I have used the same location of Data Files as current to be used for PDB and alternatively you can a different location as well. Ensure you have enough space needed in case you want to have a different location.

[oratest@ebn1 bin]$ cd $ORACLE_HOME/appsutil
[oratest@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oratest/app/oracle/19.3
Oracle Home being passed: /u02/oratest/app/oracle/19.3
[oratest@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin
[oratest@ebn1 bin]$ perl txkCreatePDB.pl -dboraclehome=/u02/oratest/app/oracle/19.3 \
-outdir=/u02/oratest/app/oracle/19.3/appsutil/log -cdbsid=testcdb -pdbsid=TEST \
-dbuniquename=testcdb -servicetype=onpremise

Post PDB Creation Script:

Now that PDB conversion is complete, you can verify that the PDB has been successfully created. We now have to run a very important script to configure the EBS Database for PDB. This also runs the Autoconfig automatically.

[oratest@ebn1 bin]$ cd $ORACLE_HOME/appsutil
[oratest@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oratest/app/oracle/19.3
Oracle Home being passed: /u02/oratest/app/oracle/19.3
[oratest@ebn1 appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl \
-dboraclehome=/u02/oratest/app/oracle/19.3 -outdir=/u02/oratest/app/oracle/19.3/appsutil/log \ > -cdbsid=testcdb -pdbsid=TEST -appsuser=apps -dbport=1521 -servicetype=onpremise

Change Initialization Parameters:

Follow Document 396009.1: Database Initialization Parameter Settings for Oracle E-Business Suite Release 12 to change the necessary parameters. I have changed below. This marks the completion of all the steps in Database Tier.

SQL> alter system set event='10946 trace name context forever, level 8454144' scope=spfile;
System altered.
SQL> alter system set sec_case_sensitive_logon=FALSE scope=spfile;
System altered.

Pre-Autoconfig Steps in Application Tier:

Running Autoconfig on the Application Tier is the final step we need to perform in the Upgrade proceed but before that we need to make couple of minor changes in Application Tier. Add the CDB entry in tnsnames.ora of Application as below:
Add a entry as below in the Application tnsnames.ora to ensure it connects to the CDB and the Service Name “TEST” which is nothing but our EBS PDB.

testcdb=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebn1.vishnu.ae)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME= TEST)
(INSTANCE_NAME=testcdb)
)
)

Add new value for APPLPTMP in Context File instead of /usr/tmp
As you know we cannot use /usr/tmp any more from 18c, we need to use of the locations present in database “utl_file_dir” to update in the Context File.

[oratest@ebn1 TEST_ebn1]$ sqlplus apps/apps@TEST
SQL> select value from v$parameter where name='utl_file_dir';
/u02/oratest/app/oracle/temp/TEST,/u02/oratest/app/oracle/temp/TEST,/u02/oratest
/app/oracle/19.3/appsutil/outbound/TEST_ebn1,/u02/oratest/app/oracle/temp/TEST
So I updated as below:
[appltest@ebn1 appsutil]$ grep APPLPTMP $CONTEXT_FILE
/u02/oratest/app/oracle/temp/TEST
[appltest@ebn1 appsutil]$

Run Autoconfig on Application tier:

The most important step of the upgrade is to run the Autoconfig on the Application Tier.

$ $INST_TOP/admin/scripts/adautocfg.sh

Start Application Services and Check the Database Version:

Now you are ready to start the Application Services.

[appltest@ebn1 admin]$ adstrtal.sh apps/apps
You are running adstrtal.sh version 120.24.12020000.11
******************************************************
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0

Run Autoconfig on Patch File System of Application tier:

Make sure you run the Autoconfig on the Patch File system as well in the same way you did on Run File System changing the APPLPTMP value and adding testcdb TNS Entry. Failing to do this, you will not be able to run adop cycles in future since the patch file doesn’t recognize the CDB changes we made.

6 COMMENTS

  1. Hi Vishnu,

    Great document, Is it possible to share sample listener.ora, tnsnames.ora, I am struck at autoconfig,

    perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl
    -dboraclehome=/d01/oracle/TEST/19.3.0
    -outdir=/d01/oracle/TEST/19.3.0/appsutil/log
    -cdbsid=CDB -pdbsid=TEST -appsuser=apps -dbport=1527 -servicetype=onpremise

    ERROR
    ———————–
    Could not Connect to the Database : ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 4376
    Additional information: 608955647

    Any help is appreciated

  2. Hi Vishnu,

    Very helpful document. please help below error

    cd $ORACLE_HOME/appsutil
    $ . ./txkSetCfgCDB.env dboraclehome= /oracle12c/oracle/19.3.0.0/19.3.0
    $ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl \
    -dboraclehome=$ORACLE_HOME -outdir=$ORACLEHOME/appsutil/log \
    -cdbsid=cdbprod -pdbsid=PROD -appsuser=apps -dbport=1521 \
    -servicetype=onpremise

    ERROR
    ———————–
    Could not connect to the Database : ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    HP UX Error: 2: No such file or directory
    Additional information: 4376
    Additional information: 608955647

  3. Hi Vishnu,

    Thank you for walkthrough, I have question though.

    After dbua finishes and closes, and you connect for first time to the upgraded 19c database via sqlplus to run post upgrade steps such as @postupgrade_fixups.sql, etc., did you source your CDB.env which you created in previous article (part2)? In other words, is your ORACLE_SID = TEST or is it TESTCDB when performing those post upgrade sqlplus scripts/commands? Thanks!
    -Benny

    • Hello. Its TESTCDB. Since we have not yet created or migrated CDB to PDB. Till you convert to PDB anything tasks on 19c will be on TESTCDB.

  4. Nice article. I’m using it together with the Oracle documentation. I’m having difficulties right after DB upgrade. With the originalSID (now at 19c) , the DB is up. The listener though will only come up with the CDB SID. And i believe this is why I’m unable to recompile invalids because of DB Link issues (APP_TO_APPS).
    Did I miss a step, such that my 19c listener is only for the CDB?

LEAVE A REPLY

Please enter your comment!
Please enter your name here