start portlet menu bar

HCLSoftware: Fueling the Digital+ Economy

Display portlet menu
end portlet menu bar
Close
Select Page

Upgrading the version of the relational database used by Workload Automation is always a customer pain point, but you could discover the secret to survive the version changes for Oracle flavor with no downtime.

Let’s see how to upgrade from Oracle 12c version to Oracle 19c version (19.3.0.0.0), an already existing Oracle DB on Microsoft Windows, where HCL Workload Automation server component 9.4 version has the instance.

Modify the Datasource property file for Workload Automation 9.4.0.6 server

Go to the path <SERVER_INSTALLATION_PATH>\wastools and make sure that the DataSourceProperties for Oracle is set to type4 as following:

  1. Redirect the output of the Datasource properties to a txt file, by running the command:<SERVER_INSTALLATION_PATH>\wastools\showDataSourceProperties.bat > DataSourceProperties.txt
  2. At the begin of the DataSourceProperties.txt file, remove the message that the previous command created (WASX7357I)
  3. Open the txt file and perform the steps:
    • Set OracleType2JndiName=jdbc/twsdb_old
    • Set OracleType4JndiName=jdbc/twsdb
    • Set OracleType4URL=jdbc:oracle:thin:@//<HOSTNAME>:<PORT>/<ORCL>

Where:

<HOSTNAME> is the ORACLE server hostname or IP address

<PORT> is the real port number of the ORACLE listener on the ORACLE server

<ORCL> is the database instance name (SERVICE_NAME) which is connected to the Workload Automation server

At the end of the process, the DataSourceProperties.txt appears as the following example:

################################################################
Oracle Type2 Resource Properties
################################################################
OracleType2JndiName=jdbc/twsdb_old
OracleType2DriverType=
OracleType2URL=jdbc:oracle:oci:@ORCL
OracleType2DatabaseName=ORCL
OracleType2ServerName=
OracleType2PortNumber=1521
OracleType2OracleLogFileSizeLimit=0
OracleType2OracleLogFileCount=1
OracleType2OracleLogFileName=
OracleType2OracleLogTraceLevel=INFO
OracleType2OracleLogFormat=SimpleFormat
OracleType2OracleLogPackageName=oracle.jdbc.driver
OracleType2TNSEntryName=
OracleType2NetworkProtocol=
OracleType2DataSourceName=
OracleType2LoginTimeout=
OracleType2Description=
OracleType2EnableMultithreadedAccessDetection=false
OracleType2Reauthentication=false
OracleType2JmsOnePhaseOptimization=false
OracleType2PreTestSQLString=SELECT 1 FROM DUAL
OracleType2DbFailOverEnabled=
OracleType2ConnRetriesDuringDBFailover=
OracleType2ConnRetryIntervalDuringDBFailover=################################################################
Oracle Type4 Resource Properties
################################################################
OracleType4JndiName=jdbc/twsdb
OracleType4DriverType=
OracleType4URL=jdbc:oracle:thin:@//x.x.x.x:1521/pdborcl
OracleType4DatabaseName=ORCL
OracleType4ServerName=localhost
OracleType4PortNumber=1521
OracleType4OracleLogFileSizeLimit=0
OracleType4OracleLogFileCount=1
OracleType4OracleLogFileName=
OracleType4OracleLogTraceLevel=INFO
OracleType4OracleLogFormat=SimpleFormat
OracleType4OracleLogPackageName=oracle.jdbc.driver
OracleType4TNSEntryName=
OracleType4NetworkProtocol=
OracleType4DataSourceName=
OracleType4LoginTimeout=
OracleType4Description=
OracleType4EnableMultithreadedAccessDetection=false
OracleType4Reauthentication=false
OracleType4JmsOnePhaseOptimization=false
OracleType4PreTestSQLString=SELECT 1 FROM DUAL
OracleType4DbFailOverEnabled=false
OracleType4ConnRetriesDuringDBFailover=100
OracleType4ConnRetryIntervalDuringDBFailover=3000

NOTE:
If you do not know the values to use for the parameters specified above, you can find this information in the file <oracle_home>\network\admin\tnsnames.ora on the machine where the database is installed.
By opening the file, you can find the following section:
<hostname> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <HOST-VALUE>)(PORT = <PORT-VALUE>))
)
(CONNECT_DATA =
(SERVICE_NAME = <SERVICE-VALUE>)
)
)

Where:
<HOST-VALUE> is the <HOSTNAME> hostname value
<PORT-VALUE> is the <PORT> port value
<SERVICE-VALUE> is the <ORCL> DB instance name

Example of tnsnames.ora file:

# tnsnames.ora Network Configuration File: C:\app\oracle\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
GLOBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxx.hclpnp.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL = —> SID for type 2 (deprecated)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx.nonprod.hclpnp.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl) —> SERVICE_NAME for type 4
)

4. Stop the WebSphere® Application Server

5. To apply all the changes, run:
<SERVER_INSTALLATION_PATH>\wastools\changeDataSourceProperties.bat DataSourceProperties.txt

6. Start the WebSphere® Application Server

7. Verify that all changes have been implemented by verifying the output of the command:
<SERVER _INSTALLATION_PATH>\wastools>showDataSourceProperties.bat

8. Backup all the following files:
<SERVER _INSTALLATION_PATH>\WAS\TWSProfile\bin\setupCmdLine.bat
<SERVER _INSTALLATION_PATH>\WAS\TWSProfile\logs\manageSdkEnableAdmin.trace
<SERVER _INSTALLATION_PATH>\TWS\tws_tools\postUpgradeEARs.bat
<SERVER _INSTALLATION_PATH>\TWS\tws_tools\createProfile.bat
<SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\updateSetupCmdLine.bat
<SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\createdb_root.bat
<SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\migratedb_root.bat
<SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\launchdb_root_ora.bat
<SERVER _INSTALLATION_PATH>\TWS\dblighttool\launchUpgradeIWSDB.bat
<SERVER _INSTALLATION_PATH>\TDWB\dbtools\oracle\scripts\createdb_root.bat
<SERVER _INSTALLATION_PATH>\TDWB\dbtools\oracle\scripts\migratedb_root.bat
<SERVER _INSTALLATION_PATH>\TDWB\bin\tdwb_env.bat
<SERVER _INSTALLATION_PATH>\wastools\InstallOracleDataSource.bat

Install Oracle 19c and upgrade your existing instance

Upgrade your Oracle installation by performing the following steps:

  1. Make sure that your current version of Oracle 12c is supported, before upgrading to Oracle 19c

enforce the database instance of Workload Automation

2. Download Oracle 19c image in a folder and run the setup: C:\images\ORACLE19c\WINDOWS.X64_193000_db_home\setup.exe
a. Choose the “Set up Software Only” option and click Next

enforce the database instance of Workload Automation

b. Choose the “Single instance database installation” option and click Next

enforce the database instance of Workload Automation

c. Choose the “Enterprise Edition” option and click Next

enforce the database instance of Workload Automation

d. Choose the “Use Existing Windows User”, fill with the User Name and click Next

enforce the database instance of Workload Automation

e. Specify a path for the Oracle base directory, click Next and proceed to install the Product

enforce the database instance of Workload Automation

3. After the installation, launch C:\images\ORACLE19c\WINDOWS.X64_193000_db_home\bin\dbua.bat to upgrade Oracle 12c instance to the new version:
a. Insert the SYSDBA Username and Password and click Next

enforce the database instance of Workload Automation

b. Insert the Oracle Home User and Password and click Next

enforce the database instance of Workload Automation

c. Fix all warnings

enforce the database instance of Workload Automation

d. Check the options “Enable Parallel Upgrade“, Recompile Invalid Objects During Post Upgrade andUpgrade Timezone Data” and click Next

enforce the database instance of Workload Automation

e. Choose the “Create a New Offline RMAN Backup” option and browse a backup location, then click Next

enforce the database instance of Workload Automation

f. Check the Listener retrieved and click Next

enforce the database instance of Workload Automation

g. Specify Configure Management if needed and proceed

enforce the database instance of Workload Automation

h. Click Finish to upgrade and Close

enforce the database instance of Workload Automation

At the end of the upgrade, you can see:

enforce the database instance of Workload Automation

And from the Oracle command line, you can check the successfully upgrade action:

enforce the database instance of Workload Automation

4. After the upgrade, if the Oracle home has changed, you need to set the new value in all the following files:
<SERVER_INSTALLATION_PATH>\WAS\TWSProfile\bin\setupCmdLine.bat
< SERVER _INSTALLATION_PATH>\WAS\TWSProfile\logs\manageSdkEnableAdmin.trace
< SERVER _INSTALLATION_PATH>\TWS\tws_tools\postUpgradeEARs.bat
< SERVER _INSTALLATION_PATH>\TWS\tws_tools\createProfile.bat
< SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\updateSetupCmdLine.bat
< SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\createdb_root.bat
< SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\migratedb_root.bat
< SERVER _INSTALLATION_PATH>\TWS\dbtools\oracle\scripts\launchdb_root_ora.bat
< SERVER _INSTALLATION_PATH>\TWS\dblighttool\launchUpgradeIWSDB.bat
< SERVER _INSTALLATION_PATH>\TDWB\dbtools\oracle\scripts\createdb_root.bat
< SERVER _INSTALLATION_PATH>\TDWB\dbtools\oracle\scripts\migratedb_root.bat
< SERVER _INSTALLATION_PATH>\TDWB\bin\tdwb_env.bat
< SERVER _INSTALLATION_PATH>\InstallOracleDataSource.bat

5. Run showDataSourceProperties.bat and redirect output to a txt file as <SERVER_INSTALLATION_PATH>\wastools>showDataSourceProperties.bat > myDSPs.txt

6. Edit the file myDSPs.txt changing the Oracle Home

7. Stop the WebSphere® Application Server

8. Run changeDataSourceProperties.bat to apply all the changes as
< SERVER _INSTALLATION_PATH>\wastools>changeDataSourceProperties.bat myDSPs.txt

9. Check that the ojdbc8.jar exists in the oracle lib folder (C:\app\oracle\product\12.2.0\dbhome_1\jdbc\lib). If it does not exist, download it from the official Oracle website and put it under the oracle lib folder.

10. Change the ojdbc6.jar to ojdbc8.jar in all the following files:
<SERVER_INSTALLATION_PATH>\WAS\TWSProfile\config\cells\TWSNodeCell\nodes\TWSNode\servers\server1\resources.xml
<SERVER_INSTALLATION_PATH>\TWS\dblighttool\launchUpgradeIWSDB.bat
< SERVER _INSTALLATION_PATH>\TWS\tws_tools\postTWSUpgradeChanges.jacl
< SERVER _INSTALLATION_PATH>\TWS\tws_tools\postTWSDMInstallationChanges.jacl
< SERVER _INSTALLATION_PATH>\TWS\tws_tools\postTWSInstallationChanges.jacl
< SERVER_INSTALLATION_PATH>\TDWB\dblighttool\launchUpgradeIWSDB.bat
< SERVER _INSTALLATION_PATH>\TDWB\bin\tdwb_env.bat

11. Start the WebSphere® Application Server

Comment wrap

Start a Conversation with Us

We’re here to help you find the right solutions and support you in achieving your business goals.

Automation | February 10, 2023
Banking Case Study: New Client Adoption and Reverse Check: HWA+DRYiCE-iControl
We look at a banking case study wherein we are going through the “New Client Adoption and Reverse Check” business process.
Automation | September 28, 2022
See your scheduling metrics on Prometheus and Grafana
HCL Workload Automation has exposed its metrics for the main components, the back-end which reports metrics around job execution.
Automation | September 26, 2022
Easy Websphere's Liberty Management for the Workload Automation Administrator
Learn about how HWA main components are deployed on WebSphere liberty, that was designed to be highly efficient and optimized for modern cloud technologies.