Auto Commit State Persists After Connection Close on UCP

By default connections checked out of universal connection pool (UCP) will have a auto commit state of true. This could be changed by setting the auto commit state on the connection. Another way to change is to change the connection pool's default setting for auto commit.
Comparing implicit connection cache (ICC) pools and UCP it appears that in UCP the auto commit state persist even after connection is closed. This means if a connection changes the auto commit state and return it to the pool and same connection is checked out later, it will have the changed auto commit state and not the default value.
This behaviour was not seen on ICC pools (ICC is considered deprecated). Once the connection is returned to the pool and checked out again, it will have the default behaviour.
The test cases is given at the end of the post. Comment/uncomment either getUCPDS or getICCDS methods to run the desired test case. The pool size is set to 1 to keep things simple. But even if it's increased the test case would work. Below is an explanation of the output seen.

UCP test case output
First up is checking the default state of auto commit

Auto commit status after checking out of the pool 

0 true

Next up the auto commit state is changed to opposite of the default. If default is true, then set to false and vice versa. Afterwards verify if the changed state is reflected on the connection. Since default auto commit state was true the opposite was false. As such now the auto commit state is reflected as false.

Setting auto commit to opposite

0 false

Following the auto commit state change the connection is closed and returned to the pool.

Connections closed and returned to the pool

Finally the connection is checked out of the pool again and auto commit state is verified. In UCP the connection will have changed state of auto commit rather than the default value, even though this is a "new" connection checked out of the pool (pools never close the underlying physical connection. So new is a logical new rather than an actual new physical connection).

Auto commit status after checking out of the pool 

0 : false

ICC test case output
The ICC test case output is same except for last step. It will also have a true state for default auto commit setting. The default state is changed to false and connection is returned to the pool.

Auto commit status after checking out of the pool 

0 true

 Setting auto commit to opposite 

0 false

Connections closed and returned to the pool 

When the connection is checked out next time from the pool it will have the default state instead of the previously changed state (unlike UCP).

Auto commit status after checking out of the pool 

0 : true

These test were carried out using ojdbc10.jar and ucp.jar versions 19.6.



The behaviour seen on UCP is same even if the default auto commit state is changed on the pool level by setting the OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT to false. In such a case the connection checked out of the pool will have auto commit set to false. But any change to this will persist same as shown above.

Auto commit status after checking out of the pool 

0 false

 Setting auto commit to opposite 

0 true

Connections closed and returned to the pool 

Auto commit status after checking out of the pool 

0 : true

When a SR was raised with regard to the difference in behaviour, Oracle support stated "When connections are created and placed in the UCP pool the connection properties that are associated with each connection remain for the duration of the connection". However, this fact is not reflected on the UCP documentation but Oracle support insisted this has always been the case. Following that the same test case was tested against ojdbc6.jar and ucp.jar of 11.2.0.1 and showed the same test output. It seems rightly or wrongly the UCP had the same behaviour throughout.
However, this difference between ICC and UCP is not listed anywhere on the documentation. Any migration from ICC to UCP should watch out for this change in behaviour.

Related Posts
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
Change in 12c JDBC Behavior - setDate & getDate Does Not Truncate Timestamp
JDBC Auto Commit and Log File Sync
UCP Connections Fail to Connect to DB in Mount Mode With ORA-12504 TNS:listener was not given the SID in CONNECT_DATA
TimesTen JDBC Connection Pool Using UCP (Universal Connection Pool)
Using Oracle Connection Pools with ActiveMQ
JDBC Client Failover in Data Guard Configuration with PDBs

Java Test Case

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

/**
 *
 * @author Asanga
 */
public class TestUCPAutoCommit {

    static String URL = "jdbc:oracle:thin:@192.168.1.100:1521:enlt1";
    static String username = "asanga";
    static String password = "asanga";
    static int POOL_SIZE = 1;

    static DataSource ds;

    public static void main(String[] args) throws SQLException, UniversalConnectionPoolException, InterruptedException {

        ds = getUCPDS(); // UCP test case

//        ds = getICCDS(); // ICC test case
        
        Connection[] cons = new Connection[POOL_SIZE];

        System.out.println("Auto commit status after checking out of the pool \n");
        for (int i = 0; i < cons.length; i++) {

            cons[i] = ds.getConnection();
            System.out.println(i + " " + cons[i].getAutoCommit());

        }

        System.out.println("\n Setting auto commit to opposite \n");
        for (int i = 0; i < cons.length; i++) {

            cons[i].setAutoCommit(!cons[i].getAutoCommit());
            System.out.println(i + " " + cons[i].getAutoCommit());

        }
        System.out.println("\nConnections closed and returned to the pool \n");
        for (Connection conn : cons) {
            conn.close();
        }

        System.out.println("Auto commit status after checking out of the pool \n");
        for (int i = 0; i < cons.length; i++) {

            cons[i] = ds.getConnection();
            System.out.println(i + " : " + cons[i].getAutoCommit());

        }

    }

    static DataSource getUCPDS() throws SQLException {

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setURL(URL);
        ds.setUser(username);
        ds.setPassword(password);
        ds.setInitialPoolSize(POOL_SIZE);
        ds.setMinPoolSize(POOL_SIZE);
        ds.setMaxPoolSize(POOL_SIZE);

        /* uncomment below to set default state of auto commit to false */ 
        
//        Properties connProps = new Properties();
//        connProps.put(OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT, "false");
//        ds.setConnectionProperties(connProps);


        return ds;

    }

    static DataSource getICCDS() throws SQLException {

        OracleDataSource ds = new OracleDataSource();
        ds.setURL(URL);
        ds.setUser(username);
        ds.setPassword(password);

        Properties properties = new Properties();
        properties.setProperty("MinLimit", POOL_SIZE + "");
        properties.setProperty("MaxLimit", POOL_SIZE + "");
        properties.setProperty("InitialLimit", POOL_SIZE + "");

        ds.setConnectionCachingEnabled(true);

        OracleConnectionCacheManager cache = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
        cache.createCache("asa", ds, properties);

        return ds;
    }

}

Installing Enterprise Manager Cloud Control 13c (13.4)

This post shows the summary of steps for installing cloud control 13.4. There's an earlier post showing detail installation of cloud control 13.3. Installation of 13.4 is very similar if not identical to that of the installation of 13.3. Hence this post only shows the difference between the two installation. If installing 13.4 cloud control then refer this post in-conjunction with earlier post on 13.3.
One of the main changes is that 13.4 now support 12.2, 18c and 19c as the repository storage DB. For this installation a 19c (19.6) database was created with only the following components installed.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> select comp_id,status from dba_registry;

COMP_ID                        STATUS
------------------------------ --------------
CATALOG                        VALID
CATPROC                        VALID
RAC                            OPTION OFF
XDB                            VALID
OWM                            VALID
CONTEXT                        VALID

Once the database that will be used as the repository is created then further parameter changes are needed before EM installation could proceed. In 13.3 installation if the database did not meet the pre-reqs then a details of those pre-reqs that aren't met are shown on the OUI. This is not the case with 13.4 installation. In 13.4 a general message will be shown as below.

Exact pre-reqs that failed could be found on the log files available at the temporary directory specified at the beginning of the EM installation (more on this location later). Assuming /opt/tmp was given as the temporary directory then log files will be available in /opt/tmp/OraInstall2020-02-27_03-06-40PM/emdbprereqs/LATEST/componentLog and the log files to look for are repos_perf.log and repository.log.
Following if the list of parameters that were changed so the installation could proceed.

alter system set "_allow_insert_with_update_check"=true scope=both;
alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='*';
alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='*';
alter system set "_px_adaptive_dist_method" = OFF scope=both sid='*';
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='*';
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='*';
alter system set "_optimizer_use_feedback" = FALSE scope=both sid='*';
alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='*';
alter system set "_optimizer_performance_feedback" = OFF scope=both sid='*';

alter system set parallel_min_servers=0 scope=both;
alter system set parallel_max_servers=8  scope=both;
alter system set sga_max_size=7G scope=spfile;
alter system set sga_target=7G scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;
alter system set shared_pool_size=600M scope=spfile;

Also manually disable the automatic stat gathering before the installation. This will be enabled by EM installation in the end. So no need to manually enable it.

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

Finally similar to 13.3, the redo file size must be at least 600M.

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          9  629145600        512          1 NO  INACTIVE                624704 27-FEB-20       624707 27-FEB-20          0
         2          1         11  629145600        512          1 NO  CURRENT                 726543 27-FEB-20   9.2954E+18                    0
         3          1         10  629145600        512          1 NO  INACTIVE                624707 27-FEB-20       726543 27-FEB-20          0

The start of the installation will fail if the default temporary location doesn't have enough storage. The installer will prompt for another location. But even a location with sufficient storage is provided the installation will fail to run.

./em13400_linux64.bin 
ERROR: Temporary directory /tmp does not have enough free space. At least 12289 MB of free space are required.
Please input another directory or [Exit]: /opt/tmp
ERROR: Cannot find component oracle.jdk.
ERROR: Cannot find components specified in JRE_COMPONENT=oracle.jdk,oracle.jre.
ERROR: Installer execution failed (1).

To fix this provide an alternative temp directory location along side the installer as below (refer 2637530.1).

./em13400_linux64.bin  -J-Djava.io.tmpdir=/opt/tmp

As said earlier the installation steps are same as 13.3 as such they are not shown here. Summary of the installation is shown below.

When prompted run the root script.

# /opt/app/software/em/middleware/allroot.sh

Starting to execute allroot.sh .........

Starting to execute /opt/app/software/em/middleware/root.sh ......
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/software/em/middleware

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
/etc exist
/opt/app/software/em/middleware

Finished product-specific root actions.
/etc exist
Finished execution of  /opt/app/software/em/middleware/root.sh ......

Starting to execute /opt/app/software/em/agent/agent_13.4.0.0.0/root.sh ......

Finished product-specific root actions.
/etc exist
Finished execution of  /opt/app/software/em/agent/agent_13.4.0.0.0/root.sh ......

Installation summary.

Adding Targets on 13.4

There's an earlier post on adding targets on 13.3. Steps for adding targets on 13.4 is also the same. However, there were few issues unseen on 13.3 when adding targets. This section list those issues and the solutions for them.
Even though the sudo command list mentioned on 13.3 add target was used for 13.4, the agent deploying gave the following warning.

This issue could be resolved in two ways. One is to by unchecking the "!" in the visiblepw line in /etc/sudoers file. From

Defaults   !visiblepw

to

Defaults   visiblepw

Second method is to use -S option in the previlege delegation setting (refer 1490407.1). This option was used for agent deployment in 13.4. Use of -S option is shown below.

If the listener control is restricted to IPC protocol then agent may not be able to determine the listener status without some additional work. On the EM console listener will be listed as down and agent log will have following line.

TNS-01194: The listener command did not arrive in a secure transport

To resolve this modify the listener monitor configuration on the EM console by speechifying the IPC key.

To avoid bug 22088515 grant execute on dbms_lock to dbsnmp on the target database and reload the agent (Refer 2366263.1).

SQL> GRANT EXECUTE ON sys.dbms_lock TO dbsnmp;

Grant succeeded.

 ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.4.0.0.0
OMS Version            : 13.4.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /opt/agent/agent_inst
Agent Log Directory    : /opt/agent/agent_inst/sysman/log
Agent Binaries         : /opt/agent/agent_13.4.0.0.0
Core JAR Location      : /opt/agent/agent_13.4.0.0.0/jlib
Agent Process ID       : 10845
Parent Process ID      : 10808
Agent URL              : https://rhel71.domain.net:3872/emd/main/
Local Agent URL in NAT : https://rhel71.domain.net:3872/emd/main/
Repository URL         : https://hpc8.domain.net:4903/empbs/upload
Started at             : 2020-03-02 10:26:24
Started by user        : asanga
Operating System       : Linux version 3.10.0-957.el7.x86_64 (amd64)
Number of Targets      : 10
Last Reload            : (none)
Last successful upload                       : 2020-03-05 12:58:05
Last attempted upload                        : 2020-03-05 12:58:05
Total Megabytes of XML files uploaded so far : 5.09
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 8.61%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2020-03-05 12:57:48
Last successful heartbeat to OMS             : 2020-03-05 12:57:48
Next scheduled heartbeat to OMS              : 2020-03-05 12:58:48

---------------------------------------------------------------
Agent is Running and Ready
[asanga@rhel71 bin]$ ./emctl reload agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully

[asanga@rhel71 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.4.0.0.0
OMS Version            : 13.4.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /opt/agent/agent_inst
Agent Log Directory    : /opt/agent/agent_inst/sysman/log
Agent Binaries         : /opt/agent/agent_13.4.0.0.0
Core JAR Location      : /opt/agent/agent_13.4.0.0.0/jlib
Agent Process ID       : 10845
Parent Process ID      : 10808
Agent URL              : https://rhel71.domain.net:3872/emd/main/
Local Agent URL in NAT : https://rhel71.domain.net:3872/emd/main/
Repository URL         : https://hpc8.domain.net:4903/empbs/upload
Started at             : 2020-03-02 10:26:24
Started by user        : asanga
Operating System       : Linux version 3.10.0-957.el7.x86_64 (amd64)
Number of Targets      : 10
Last Reload            : 2020-03-05 12:58:26
Last successful upload                       : 2020-03-05 12:58:05
Last attempted upload                        : 2020-03-05 12:58:05
Total Megabytes of XML files uploaded so far : 5.09
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 8.61%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2020-03-05 12:57:48
Last successful heartbeat to OMS             : 2020-03-05 12:57:48
Next scheduled heartbeat to OMS              : 2020-03-05 12:58:48

---------------------------------------------------------------
Agent is Running and Ready

Useful Metalink notes
EM 13.4 : Input of Different Temp Location fails with 'Cannot find component oracle.jdk.' [ID 2637530.1]
EM 12c, EM 13c: Why does Agent execute "BEGIN Dbms_lock.sleep(60)" statement Having SQL ID "g0bggfqrddc4w" in the Target Database ? [ID 2366263.1]
EM12c EM13c: Metric Collection Error for Listener Target TNS-12564: TNS:connection refused TNS-01194: The listener command did not arrive in a secure transport [ID 2296762.1]

Related Posts
Upgrading Enterprise Manager Cloud Control from 13.4 to 13.5 - 1
Adding Targets on EM Cloud Control 13c
Installing Enterprise Manager Cloud Control 13c (13.3)
Installing Grid Control 11gR1 and Deploying Agents
Upgrading Grid Control 11g to 12c - 1

Upgrading Oracle Single Instance with ASM (Oracle Restart) from 11.2.0.4 to 19c (19.6) on RHEL 7

Oracle 19c is the last of the 12c series and is the long term support version. There's an earlier post which shows upgrading from 11.2.0.4 to 12.2.0.1. The 19c could be considered as 12.2.0.3. However, previous upgrade post was on a system running on RHEL 6. The 19c could only be installed on RHEL 7 (or higher when RHEL 8 gets certified). This post is based on upgrading a 11.2.0.4 Oracle restart environment on RHEL 7 to 19c. However, in this case the the Oracle restart setup is not role separated. Both GI and OH are installed under Oracle user.
The binaries used for 19c upgraded are gold images created from environment which had January 2020 PSU applied (19.6).
1. The current resources and software versions are as below.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.DATA.dg               ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-7-244
ora.FRA.dg                ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-7-244
ora.LISTENER.lsnr         ora.listener.type         ONLINE             ONLINE             ip-172-31-7-244
ora.asm                   ora.asm.type              ONLINE             ONLINE             ip-172-31-7-244
ora.cssd                  ora.cssd.type             ONLINE             ONLINE             ip-172-31-7-244
ora.diskmon               ora.diskmon.type          OFFLINE            OFFLINE
ora.evmd                  ora.evm.type              ONLINE             ONLINE             ip-172-31-7-244
ora.ons                   ora.ons.type              OFFLINE            OFFLINE
ora.test.db               ora.database.type         ONLINE             ONLINE             ip-172-31-7-244

$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]

$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [11.2.0.4.0]

2. The 11.2.0.4 environment also has the latest available PSU applied. At the time of the post latest PSU was Jan-2020. MOS doc 2539751.1 list patches to apply before upgrade. In this upgrade path 28553832 and 23186035 were the only applicable patches as per doc. Installing the latest PSU, meant these patches were installed as part of the PSU. As such no need to do one-off patch instillations. Patch list shown below.

opatch lspatches

30503372;OJVM PATCH SET UPDATE 11.2.0.4.200114
30298532;Database Patch Set Update : 11.2.0.4.200114 (30298532)
29938455;OCW Patch Set Update : 11.2.0.4.191015 (29938455)

3. Make the directories for GI home unzip the gold image to these directories.

mkdir -p /opt/app/oracle/product/19.x.0/grid
unzip giGold_19.6.zip -d /opt/app/oracle/product/19.x.0/grid

4. Run the cluster verification tool from 19c GI home with hacfg option. The failure listed below is due to 0 SWAP size on AWS node. This could be ignored.

./runcluvfy.sh stage -pre hacfg

Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...FAILED (PRVF-7573)
Verifying Free Space: ip-172-31-7-244:/usr,ip-172-31-7-244:/var,ip-172-31-7-244:/etc,ip-172-31-7-244:/sbin,ip-172-31-7-244:/tmp ...PASSED
Verifying User Existence: oracle ...
  Verifying Users With Same UID: 501 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Run Level ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying Package: kmod-20-21 (x86_64) ...PASSED
Verifying Package: kmod-libs-20-21 (x86_64) ...PASSED
Verifying Package: binutils-2.23.52.0.1 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: libgcc-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.8.2 (x86_64) ...PASSED
Verifying Package: sysstat-10.1.5 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.82 ...PASSED
Verifying Package: glibc-2.17 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.17 (x86_64) ...PASSED
Verifying Package: libaio-0.3.109 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.109 (x86_64) ...PASSED
Verifying Package: nfs-utils-1.2.3-15 ...PASSED
Verifying Package: smartmontools-6.2-4 ...PASSED
Verifying Package: net-tools-2.0-0.17 ...PASSED
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...PASSED
Verifying Package: libxcb-1.11 (x86_64) ...PASSED
Verifying Package: libX11-1.6.3 (x86_64) ...PASSED
Verifying Package: libXau-1.0.8 (x86_64) ...PASSED
Verifying Package: libXi-1.7.4 (x86_64) ...PASSED
Verifying Package: libXtst-1.2.2 (x86_64) ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was unsuccessful.

Failures were encountered during execution of CVU verification request "stage -pre hacfg".

5. ASM will be upgraded as part of the upgrade process. Therefore stop the database before starting the GI upgrade. Upgrade process will prompt to stop the database during gridSetup run as well.

srvctl stop database -d test

6. Start the GI upgrade by running the gridSetup.sh from the grid home. Not all steps are shown below.

$ ./gridSetup.sh

Select upgrade GI.

Similar to previous 12c versions, unzip location is the GI home. Only Oracle base could be changed at install time.

GI Upgrade summary.

When prompted run rootupgrade.sh

/opt/app/oracle/product/19.x.0/grid/rootupgrade.sh
Performing root user operation.
...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/app/oracle/product/19.x.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /opt/app/oracle/crsdata/ip-172-31-7-244/crsconfig/roothas_2020-01-21_04-17-39PM.log
2020/01/21 16:17:40 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2020/01/21 16:17:42 CLSRSC-363: User ignored prerequisites during installation
2020/01/21 16:17:43 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2020/01/21 16:17:45 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2020/01/21 16:17:45 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2020/01/21 16:17:46 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.

ASM has been upgraded and started successfully.

2020/01/21 16:21:40 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2020/01/21 16:21:40 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
2020/01/21 16:21:48 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node ip-172-31-7-244 successfully pinned.
2020/01/21 16:21:51 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2020/01/21 16:21:52 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2020/01/21 16:21:52 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.service'
2020/01/21 16:22:40 CLSRSC-595: Executing upgrade step 11 of 12: 'UpgradeSIHA'.

ip-172-31-7-244     2020/01/21 16:24:11     /opt/app/oracle/crsdata/ip-172-31-7-244/olr/backup_20200121_162411.olr     1748936676

ip-172-31-7-244     2020/01/21 14:25:40     /opt/app/oracle/product/11.2.0/grid/cdata/ip-172-31-7-244/backup_20200121_142540.olr     -
2020/01/21 16:24:11 CLSRSC-595: Executing upgrade step 12 of 12: 'InstallACFS'.
2020/01/21 16:26:36 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

Conclude the GI upgrade.

7. Verify the post upgrade versions and setup.

$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]

$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]

cluvfy stage -post hacfg

Verifying Oracle Restart Integrity ...PASSED
Verifying OLR Integrity ...PASSED

Post-check for Oracle Restart configuration was successful.

8. Next step is to upgrade the oracle home. This is a simple installation of 19c software as DB is upgraded later. The 12.2 series introduced a new OS group for RAC DB management. If this is used then add the user group to both oracle and grid. Not adding the group to grid user result in an issue which is mentioned in 12.1.0.2 to 12.2.1.0 single instance upgrade post.

# groupadd racdba
# usermod -g oinstall -G dba,oper,asmoper,asmdba,asmadmin,backupdba,dgdba,kmdba,racdba oracle
# id oracle
uid=501(oracle) gid=502(oinstall) groups=502(oinstall),501(dba),503(oper),504(asmoper),505(asmdba),506(asmadmin),1001(backupdba),1002(dgdba),1003(kmdba),1004(racdba)

9. Unzip the OH gold image to 19c Oracle home.

mkdir -p /opt/app/oracle/product/19.x.0/dbhome_1
unzip ohGold_19.6.zip -d /opt/app/oracle/product/19.x.0/dbhome_1

10. Run the installer and select software only option. Rest of the steps are similar to installing a standalone OH.

11. When prompted run root.sh.

# /opt/app/oracle/product/19.x.0/dbhome_1/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/19.x.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA - Standalone Mode) is available at :
    /opt/app/oracle/product/19.x.0/dbhome_1/bin/tfactl

Note :
1. tfactl will use TFA Service if that service is running and user has been granted access
2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed

12. Final step is upgrade the 11.2.0.4 DB to 19.6. Oracle has introduced AutoUpgrade Tool (2485457.1) to automate the upgrade process. However, in this case manual upgrade is done using dbua. Copy the preupgrade.jar (available on 19c OH rdbms/admin) to a temporary location run with terminal option.

$ORACLE_HOME/jdk/bin/java -jar preupgrade.jar TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-01-22T11:03:49

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  TEST
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
     DB Patch Level:  PSU 11.2.0.4.200114
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  1.  (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.

      The database contains 2 objects in the recycle bin.

      The recycle bin must be completely empty before database upgrade.

  RECOMMENDED ACTIONS
  ===================
  2.  Remove the EM repository.

      - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
      19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

      Step 1: If database control is configured, stop EM Database Control,
      using the following command

        $> emctl stop dbconsole

      Step 2: Connect to the database using the SYS account AS SYSDBA

        SET ECHO ON;
        SET SERVEROUTPUT ON;
        @emremove.sql

      Without the set echo and serveroutput commands, you will not be able to
      follow the progress of the script.

      The database has an Enterprise Manager Database Control repository.

      Starting with Oracle Database 12c, the local Enterprise Manager Database
      Control does not exist anymore. The repository will be removed from your
      database during the upgrade.  This step can be manually performed before
      the upgrade to reduce downtime.

  3.  (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
      owner of the trigger or drop and re-create the trigger with a user that
      was granted directly with such. You can list those triggers using: SELECT
      OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
      TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
      DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').

      There is one or more database triggers whose owner does not have the
      right privilege on the database.

      The creation of database triggers must be done by users granted with
      ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
      directly.

  4.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  INFORMATION ONLY
  ================
  5.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      TEMP                                20 MB       150 MB
      UNDOTBS1                           380 MB       411 MB

      Minimum tablespace sizes for upgrade are estimates.

  6.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database TEST
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/opt/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  7.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 14 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  8.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  9.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database TEST
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/opt/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /opt/app/oracle/cfgtoollogs/test/preupgrade/preupgrade.log
  /opt/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql
  /opt/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/opt/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/opt/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-01-22T11:03:49

13. Any item marked with "(AUTOFIXUP)" could be fixed by running the preupgrade_fixups.sql. If an item is not marked (AUTOFIXUP) then manual intervention is needed.

SQL> @/opt/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-22 11:03:47

For Source Database:     TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  purge_recyclebin          YES         None.
    2.  em_present                YES         None.
    3.  trgowner_no_admndbtrg     YES         None.
    4.  pre_fixed_objects         YES         None.
    5.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    6.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

14. Run the DBUA from the 19c home to upgrade the DB.

The 19c uses timezone file version 32. It is possible to upgrade the timezone file at the same time as DB.

If guaranteed restore point is used as a backup option to recover from upgrade failure then this restore point must be dropped before advancing the compatible parameters on ASM and DB. Otherwise database mount will fail with

ORA-38880 signalled during: ALTER DATABASE MOUNT

38880, 00000, "Cannot advance compatibility from %s to %s due to guaranteed restore points"
// *Cause: Flashback database cannot undo the advance of database
//         compatibility.  Therefore, one cannot advance the compatibility
//         of the database while there are guaranteed restore points in the
//         database.
// *Action: Drop all guaranteed restore points first and retry, or delay
//          the advance of database compatibility to a later time.

Pre Uprade Summary

Post ugprade Summary

15. Run postupgrade_fixups.sql to complete the upgrade.

@/opt/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-22 11:13:31

For Source Database:     TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    3.  old_time_zones_exist      YES         None.
    4.  post_dictionary           YES         None.
    5.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

16. Check the database components and versions.

COMP_NAME                           STATUS     VERSION         VERSION_FULL
----------------------------------- ---------- --------------- ---------------
Oracle Database Catalog Views       VALID      19.0.0.0.0      19.6.0.0.0
Oracle Database Packages and Types  VALID      19.0.0.0.0      19.6.0.0.0
Oracle Real Application Clusters    OPTION OFF 19.0.0.0.0      19.6.0.0.0
Oracle Workspace Manager            VALID      19.0.0.0.0      19.6.0.0.0
Oracle Text                         VALID      19.0.0.0.0      19.6.0.0.0
Oracle XML Database                 VALID      19.0.0.0.0      19.6.0.0.0

Registory history output.

ACTION_TIME                    ACTION     NAMESPACE  VERSION         COMMENTS
------------------------------ ---------- ---------- --------------- ------------------------------------------------------------
21-JAN-20 03.30.26.273084 PM   APPLY      SERVER     11.2.0.4        Patchset 11.2.0.2.0
21-JAN-20 04.07.12.218350 PM   APPLY      SERVER     11.2.0.4        PSU 11.2.0.4.200114
                               BOOTSTRAP  DATAPATCH  19              RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217
22-JAN-20 11.50.55.143749 AM   RU_APPLY   SERVER     19.0.0.0.0      Patch applied on 19.6.0.0.0: Release_Update - 191217155004
22-JAN-20 11.52.39.733756 AM   UPGRADE    SERVER     19.0.0.0.0      Upgraded from 11.2.0.4.0 to 19.6.0.0.0

17. Check the timezone file version is 32.

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

18. If satisfied with upgrade advance the compatible parameters. This is a irreversible change. On DB instance

SQL> alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;

On ASM instance

SQL> alter diskgroup FRA SET attribute 'compatible.asm'='19.0.0.0.0';
SQL> alter diskgroup DATA  SET attribute 'compatible.asm'='19.0.0.0.0';
SQL> alter diskgroup fra set attribute 'compatible.rdbms'='19.0.0.0.0';
SQL> alter diskgroup data set attribute 'compatible.rdbms'='19.0.0.0.0';

SQL> select g.name,a.name,a.value from v$asm_diskgroup g, v$asm_attribute a where g.group_number=a.group_number and a.name like '%compat%';

NAME                 NAME                 VALUE
-------------------- -------------------- --------------------
DATA                 compatible.asm       19.0.0.0.0
DATA                 compatible.rdbms     19.0.0.0.0
FRA                  compatible.asm       19.0.0.0.0
FRA                  compatible.rdbms     19.0.0.0.0

19. Run orachk with post upgrade check option on the upgraded setup.
This concludes the upgrading from 11.2.0.4 to 19c.

Known Issues
If rootupgrade.sh fails with the following output

2020/01/23 12:33:30 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2020/01/23 12:33:33 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.

CRS-6705: Oracle Clusterware Release Version ('19.0.0.0.0') does not match Software Version ('11.2.0.4.0'). Oracle Clusterware cannot be started.

CRS-4000: Command Start failed, or completed with errors.

2020/01/23 12:33:50 CLSRSC-318: Failed to start Oracle OHASD service

Died at /u01/app/oracle/product/19.0.0.0/grid/crs/install/crsupgrade.pm line 6269.

Check if the 11.2.0.4 HA stack is up and running at the time of the rootupgrade.sh execute.

High Versions Count resulting in poor performance
12.2 series has changed the default value for cursor obsolete threshold. This could lead to poor performance once upgraded to 19c (or any other 12.2 version). More on 2431353.1. To resolve any potential performance issues set the hiddne parameter to 11.2.0.4 value.

alter system set "_cursor_obsolete_threshold"=1024 scope=spfile;

Useful Metalink notes
Oracle 19c - Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c [ID 2539778.1]
DATABASE UPGRADE TO 19C REPORTS ERROR "ORA-01403: NOT DATA FOUND" [ID 2579106.1]
Bug 30238715 - ORA-600 [qctfrc: csform] After Upgrade To 19c When Query Has Interval YEAR- MONTH Partition Type [ID 30238715.8]
Patches to apply before upgrading Oracle GI and DB to 19c or downgrading to previous release [ID 2539751.1]

Related Posts
Upgrading Oracle Restart from 19c to 21c on RHEL 7
Upgrading Oracle Restart from 18c (18.6) to 19c (19.3)
Upgrading Oracle Restart from 12.2.0.1 to 18c
Upgrading Oracle Single Instance with ASM (Oracle Restart) from 11.2.0.4 to 12.2.0.1
Upgrading Oracle Single Instance with ASM (Oracle Restart) from 12.1.0.2 to 12.2.0.1
Upgrading Single Instance on ASM from 11.2.0.3 to 11.2.0.4
Upgrading Grid Infrastructure Used for Single Instance from 11.2.0.4 to 12.1.0.2