Oracle Upgrade Decoded: Insights from an App Developer's Perspective
Tags: oracle, learning, oracleupgrade, database
Published: 2023-07-07 06:45:22

Drawing inspiration from the wisdom of Vincent Van Gogh, who wisely remarked that "Great things are not accomplished by impulsive actions, but rather by the culmination of small steps," I recently completed Oracle migration for a critical application.

This article explores the thorough planning required for an Oracle upgrade, highlighting key factors. Moreover, it provides an insightful account of a firsthand experience upgrading a colossal database from Oracle 11g to 19c.

There are a few points which you consider before upgrading Oracle upgrade:

## Types of Upgrades
 Before proceeding with the upgrade, it's crucial to determine the appropriate upgrade path. Oracle documentation specifies that not all versions can be directly upgraded to Oracle 19c. For instance, while 11.2.0.4 can be upgraded directly to 19c, 11.2.0.3 must first be moved to 11.2.0.4 before upgrading further. Fortunately, in our case, a direct upgrade path was available.

**Reference:** **[Oracle Database 19c Upgrade Guide](https://www.oracle.com/a/tech/docs/twp-upgrade-oracle-database-19c.pdf)**

## Out-of-place Upgrade vs. In-Place Upgrade
 There are two main types of upgrades: out-of-place and in-place upgrades. An out-of-place upgrade involves installing a newer version in a separate Oracle Clusterware home, allowing you to have both versions available while copying data from the old database to the new Oracle 19c. This approach enables easy rollback to the previous version in case of issues. On the other hand, an in-place upgrade overwrites the software in the current Oracle Grid Infrastructure home without the need for additional hardware. However, it doesn't provide the option of easily reverting to the previous version in case of problems. Considering the time constraints and hardware procurement challenges, we opted for an in-place upgrade. In this article, I will also share how we mitigated the potential downsides of an in-place upgrade.

## Compatibility of the Application:
Assessing the compatibility of the application with the new Oracle version is crucial. In our case, we encountered two compatibility challenges:

### Code Compatibility
Oracle Database 19c does not support connecting using SID (System Identifier). Starting from Oracle Database 12c, Oracle introduced a new connection method using service names instead of SIDs. Service names provide a more flexible and scalable approach to database connections. 

When connecting to Oracle Database 19c, it is recommended to use the service name instead of the SID for proper connectivity. This adjustment ensures compatibility and aligns with Oracle's recommended connection method. The SID serves as a unique name assigned to each Oracle instance, facilitating identification and connection to specific databases. When connecting using the SID, the syntax typically follows the format `jdbc:oracle:thin:@host:port:SID`. 

On the other hand, a service name represents a logical name associated with a database service, offering a more abstract and adaptable approach to establishing connections. When connecting using the service name, the syntax generally adheres to the format `jdbc:oracle:thin:@host:port/service_name`.


### Driver Compatibility
During an Oracle upgrade, it is recommended to review the documentation provided by Oracle to identify the supported driver versions for the target database release. If the existing driver version is not compatible with the new Oracle version, an updated driver version may need to be installed or integrated into the application. This ensures that the application can establish a successful connection and effectively utilize the functionalities provided by the upgraded Oracle database.

We were upgrading to Oracle 19c so we preferred to use the ojdbc8.jar. 

A quick reference from the Oracle Documentation for compatible libraries.

```

+----------------------+--------------------------------------+
| ojdbc Driver Version | Compatible Oracle Database Versions  |
+----------------------+--------------------------------------+
| ojdbc6.jar           | Oracle Database 11g                  |
| ojdbc7.jar           | Oracle Database 12c                  |
| ojdbc8.jar           | Oracle Database 12c, 18c, 19c        |
| ojdbc10.jar          | Oracle Database 19c                  |
+----------------------+--------------------------------------+

```

## Compatibility of Other Software Components
In addition to considering the compatibility of your Oracle database upgrade, it is crucial to assess the compatibility of other components you may be utilizing. For instance, if you are employing Informatica, it is essential to note that based on Informatica 10.1 documentation, it is not compatible with Oracle 19c. This information highlights the need to plan the upgrade carefully and make necessary adjustments to ensure seamless compatibility between all components.


## Downtime Considerations
Downtime consideration is a crucial aspect when planning an Oracle upgrade. The duration of downtime can vary depending on the size of the database, complexity of the upgrade, and any specific requirements or constraints. It is essential to carefully evaluate the impact of downtime on business operations and user accessibility.

During the upgrade process, the database may need to be taken offline, resulting in temporary unavailability of services and applications relying on it. This downtime can disrupt business activities, affect productivity, and potentially lead to financial implications.

In some cases, organizations may explore alternative solutions to reduce downtime, such as utilizing backup systems, implementing high availability configurations, or leveraging data replication techniques. These strategies can provide continuous availability or minimize downtime during the upgrade process.

Given the substantial size of our database, using the replicated server was not feasible during the in-place upgrade. This was primarily because our backup database was intended to serve as a contingency plan in case any issues arose during the release. so we had to plan for 24 hours of downtime of our application. 



## Testing Scope

The testing scope can vary for different applications, and it's important to define the scope comprehensively. To ensure a smooth upgrade, we conducted a full regression test, covering all interfacing applications and relevant functionalities. Based on the testing timeline, we defined the production go-live date accordingly.

## Rollback Plan

As part of our in-place upgrade strategy, we needed a reliable rollback plan in case any issues arose during the upgrade. Our fallback option was to leverage the backup database. Before initiating the upgrade, we ensured that the production database and backup database were in sync. If a rollback became necessary, we would postpone the activity, make the backup server the primary server, and replicate the database to create another backup instance. This plan allowed us to proceed with confidence, knowing that we had a reliable fallback option available.

## Outcome

Thanks to the collaborative efforts and meticulous planning, we successfully upgraded the database. The entire process was a valuable learning experience, as it involved coordinating with multiple teams and collaborating with new individuals to ensure a successful outcome.