WORKLOAD AUTOMATION COMMUNITY
  • Home
  • Blogs
  • Forum
  • Resources
  • Events
    • IWA 9.5 Roadshows
  • About
  • Contact
  • What's new

It’s time to enforce the database instance of Workload Automation 9.4.0.6 by upgrading from Oracle12c to Oracle 19c version

3/11/2022

0 Comments

 
Picture
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 DataSourceProperties.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
Picture
     2. Download Orachle 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
Picture
​                 b. Choose the “Single instance database installation” option and click Next
Picture
​                  c. Choose the “Enterprise Edition” option and click Next
Picture
​                 d. Choose the "Use Existing Windows User", fill with the User Name and click Next
Picture
​                  e. Specify a path for the Oracle base directory, click Next and proceed to install the Product
Picture
     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
Picture
​                    b. Insert the Oracle Home User and Password and click Next
Picture
​                    c. Fix all warnings
Picture
                d. Check the options "Enable Parallel Upgrade", "Recompile Invalid Objects During Post Upgrade" and                     "Upgrade Timezone Data" and click Next
Picture
​                    e. Choose the "Create a New Offline RMAN Backup" option and browse a backup location, then click                       Next
Picture
​                  f. Check the Listener retrieved and click Next
Picture
​                    g. Specify Configure Management if needed and proceed
Picture
​                    h. Click Finish to upgrade and Close
Picture
​At the end of the upgrade, you can see:
Picture
​And from the Oracle command line, you can check the successfully upgrade action:
Picture
​     7. 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

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

    9. Edit the file myDSPs.txt changing the Oracle Home
    10. Stop the WebSphere® Application Server 
    11. Run changeDataSourceProperties.bat to apply all the changes as                                                                                                   
     < SERVER _INSTALLATION_PATH>\wastools>changeDataSourceProperties.bat myDSPs.txt

    12. 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.                                                                                        
     13. 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

     14. Start the WebSphere® Application Server

​For additional information and technical specification, please refer to the following TechNote

Authors Bio
Picture
Simone Grammatico, IT Specialist and QA Tester

Simone is an IT Specialist and QA Tester at HCL Technologies with 18 years of experience as a consultant to public administration clients and later, since 2012, as a QA business software tester. Today, he is in charge of customer support (L3), FixPacks release, and Security Test (PSIRT) of Workload Automation.
View my profile on LinkedIn

Picture
Elvira Zanin, Software Engineer Workload Automation Development Team

Elvira Zanin is a Software Engineer on the Workload Automation development team located in HCL Rome Hub. She is currently based in the Workload Automation Support team but she was involved in Test automation team, WebUI and Add-ons development team also. Elvira has experience with Dynamic Workload Console. She completed her degree in Computer Science at University of Salerno and currently lives in Rome, Italy.
View my profile on LinkedIn
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Archives

    March 2023
    February 2023
    January 2023
    December 2022
    September 2022
    August 2022
    July 2022
    June 2022
    May 2022
    April 2022
    March 2022
    February 2022
    January 2022
    December 2021
    October 2021
    September 2021
    August 2021
    July 2021
    June 2021
    May 2021
    April 2021
    March 2021
    February 2021
    January 2021
    December 2020
    November 2020
    October 2020
    September 2020
    August 2020
    July 2020
    June 2020
    May 2020
    April 2020
    March 2020
    January 2020
    December 2019
    November 2019
    October 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017

    Categories

    All
    Analytics
    Azure
    Business Applications
    Cloud
    Data Storage
    DevOps
    Monitoring & Reporting

    RSS Feed

www.hcltechsw.com
About HCL Software 
HCL Software is a division of HCL Technologies (HCL) that operates its primary software business. It develops, markets, sells, and supports over 20 product families in the areas of DevSecOps, Automation, Digital Solutions, Data Management, Marketing and Commerce, and Mainframes. HCL Software has offices and labs around the world to serve thousands of customers. Its mission is to drive ultimate customer success with their IT investments through relentless innovation of its products. For more information, To know more  please visit www.hcltechsw.com.  Copyright © 2019 HCL Technologies Limited
  • Home
  • Blogs
  • Forum
  • Resources
  • Events
    • IWA 9.5 Roadshows
  • About
  • Contact
  • What's new