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

17
2293

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.

17 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?

  5. Very detailed explanation.
    It did helped me with my first 19c upgrade with ERP.

    Thanks again and keep blogging!
    ~Mukarram.

  6. Hello Vishnu, how are you?

    I found a post of your referent an upgrade of Oracle Database 12C to 19C (E-Business Suite).
    By the way a nice post.

    I ‘m having there are problems in my try upgrade database 11g to 19c (EBS 12.1.3). Could you respond to some doubts?

    Database Home Origin 11g : SID aptst121
    Database Home Dest 19c: aptst193

    – I created NEW_HOME (19.3.0);
    – I created new SID aptst193 in New Home;
    – I Executed Steps before DBUA;
    – I executed DBUA,
    – I executed Steps:
    —- Create PDB Descriptor File;
    —- Create PDB;
    —- Post PDB Creation Script;

    But the schemas didn’t was migrated to new SID/HOME :
    aptst121 to aptst193…

    Do you know to say why?

  7. Hi Vishnu,

    Appreciate creating this wonderful post. It has been a great help. I have used it all along with the Oracle document to upgrade our EBS 12.1.3 Apps database from 12.1.0.2 to 19c. I am using Interoperability Note Oracle E-Business Suite Release 12.1 with Oracle Database 19c (Doc ID 2580629.1) for the upgrade. I am almost done with the upgrade. I have a question about remotely connecting to new upgraded PDB database. I found that we have to make changes in the tnsnames.ora file on my local PC to be able to connect remotely to new 19c PDB database.

    For example, my old TNS entry was:
    PDB=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=host1.xyz.com)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=pdb)
    (INSTANCE_NAME=pdb)
    )
    )

    With the above entry, its giving error ORA-1034 – Oracle Not available. ORA-27101 – Shared real does not exist when I try to connect from TOAD on my PC. It works after changing INSTANCE_NAME to cdb like below.

    PDB=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=host1.xyz.com)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=pdb)
    (INSTANCE_NAME=cdb) <– changed to cdb here.
    )
    )

    We have tons of other databases and applications that connect remotely to our database using tnsnames.ora, jdbc, data stage, database links etc. It will be really nightmare if we have to change connection configuration for all of them.

    How do we handle this? Is there any workaround so that we do not have to make any changes in client side connections configuration?

    Thanks in advance!

    Samir

    • Hi Samir,

      As you know, PDB connectivity is done with a simple service name. You don’t need to specify any SID or Instance details. For EBS or Non-EBS, for PDB connectivity, you need to use only the “SERVICE_NAME”.

      PDB1 =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.domain)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
      )
      )

      Thanks,
      Vishnu

      • Thanks for your reply Vishnu. We are having a post upgrade issue now with EBS 12.1.3 that when I open any forms in the application, it gives ORA-65011 Pluggable database does not exist. The cause for this error is that when I run select * from v$active_services in PDB database, it does not show anything even though listener is registered with the database. I have Sev1 with Oracle support for this but they have not been able to resolve it yet.

        Also, do you know if txkGenCDBTnsAdmin.pl command should create folder under $ORACLE_HOME/network/admin?

        Thanks again!

  8. Hi Vishnu,

    It was very nice Article and I am trying to upgrade from EBS (12.1.3) 11g DB to 19c. I want to understand how much time usually takes the following steps? Kindly help me on this.

    1) Upgrade the Database Using DBUA
    2) txkCreatePDB.pl

    Regards
    Srinivas

    • If in real-time, it depends on data and related issues. But ideally you can complete first iteration in 2-4 days.

      Thanks,
      Vishnu

  9. First of all, Thank you Vishnu for this amazing blog. It helped me a lot.

    While running txkPostPDBCreationTasks.pl I was encountered with the below error.
    SQL execution did not go through successfully.
    LOG FILE: /oraclehome/appsutil/log/TXK_POST_PDB/shutdown_IMMEDIATE.out.
    *******FATAL ERROR*******
    PROGRAM : (/oralehome/appsutil/bin/txkPostPDBCreationTasks.pl)
    TIME :
    FUNCTION: main::shutdownDatabase [ Level 1 ]
    ERRORMSG: SQL execution did not go through successfully.
    *******FATAL ERROR*******
    PROGRAM : (/oraclehome/bin/txkPostPDBCreationTasks.pl)
    TIME :
    FUNCTION: main::shutdownDatabase [ Level 1 ]
    ERRORMSG: SQL execution did not go through successfully.
    $

    • Thanks for the compliment. Do you still have the issue or did you manage to fix it?

      Did you check the alert logfile as well for any errors? It should be a simple issue ideally looking at the error.

      Thanks,
      Vishnu

LEAVE A REPLY

Please enter your comment!
Please enter your name here