Step by step guide to patch Oracle Grid & DB Home to 19.23 in Oracle RAC 19c on Aix 7.3

 Environment details:-

RAC 2 node Cluster
ORACLE_HOME	/u01/app/oracle/product/19.3.0/dbhome_1
GRID_HOME	/u01/app/grid/19.3.0/gridhome_1
OS	AIX  Power 7.3
DB Version	19.3.0
Grid Version	19.0.0.0

·         Download the patch from MOS (My Oracle Support)

As we are applying Patch 36233126 - GI Release Update 19.23.0.0.240416. Download this patch from MOS. This patch includes PSU patches for both Oracle Database Grid infrastructure.

 

Once downloaded, upload the patches in shared path called <UNZIPPED_PATCH_LOCATION>. This path should be accessible from both the nodes. It can be a nfs path.

 

Ensure the <UNZIPPED_PATCH_LOCATION> is empty before unzip.

Unzip the patch inside this directory.

unzip  p36233126_190000_AIX64-5L.zip

 

·         Opatch version:-

Ensure latest version of OPatch is installed on both grid and oracle home. Download latest OPatch from MOS. Patch number : 6880880

 

Before installation, take backup of existing OPatch folder inside $ORACLE_HOME

To Validate opatch version..

Perform this on Both nodes.

Login as Oracle user   -bash-5.2$ cd $ORACLE_HOME -bash-5.2$ cd OPatch -bash-5.2$ opatch version OPatch Version: 12.2.0.1.42   OPatch succeeded.     Login as Grid user -bash-5.2# su - oracle -bash-5.2$ cd $ORACLE_HOME -bash-5.2$ cd OPatch -bash-5.2$ opatch version OPatch Version: 12.2.0.1.42

·         Validation of Oracle Inventory

Check the consistency of inventory information for both Grid home and each Oracle home to be patched. Perform this on Both nodes.

Run this command as the respective Oracle home  owner to check the consistency. Run this on both the nodes.

Login as grid user $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME   Login as oracle user $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

·         Run OPatch to check Conflict. Do this on both nodes

Determine whether any currently installed one-off patches conflict with this patch 36233126 as follows: Login As the Grid home user: export UNZIPPED_PATCH_LOCATION=/orabackup/patches   $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36233263   $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36240578 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36233343 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36460248  $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36383196     Login as oracle user and check following. export UNZIPPED_PATCH_LOCATION=/orabackup/patches   $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36233263   $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $UNZIPPED_PATCH_LOCATION/36233126/36240578

·         Run OPatch System Space Check

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

Run this as grid user on both nodes.

echo > /tmp/patch_list_gihome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36233263  >> /tmp/patch_list_gihome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36240578  >> /tmp/patch_list_gihome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36233343  >> /tmp/patch_list_gihome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36460248  >> /tmp/patch_list_gihome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36383196  >> /tmp/patch_list_gihome.txt

Run OPatch command on both nodes to check if enough free space is available in the Grid home:

Run as Grid user on both nodes.

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt

Run OPatch command on both nodes to check if enough free space is available in the Oracle  home:

Run as Oracle user on both nodes.

echo > /tmp/patch_list_dbhome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36233263 >> /tmp/patch_list_dbhome.txt echo $UNZIPPED_PATCH_LOCATION/36233126/36240578 >> /tmp/patch_list_dbhome.txt

·         The following commands check for conflicts in both the 19c Grid home and the 19c DB homes.

Run as Root user on 1^st node.

$ORACLE_HOME/OPatch/opatchauto apply $UNZIPPED_PATCH_LOCATION/36233126 -analyze   -bash-5.2# /u01/app/grid/19.3.0/gridhome_1/OPatch/opatchauto apply $UNZIPPED_PATCH_LOCATION/36233126 -analyze   OPatchauto session is initiated at Tue Jun 11 13:03:21 2024   System initialization log file is /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchautodb/systemconfig2024-06-11_01-03-46PM.log.   Following home(s) will not be included as part of current opatchauto session as they do not run from the current host.         Database Name: PROD         Oracle Home: /u01/app/oracle/product/19.3.0/dbhome_1         Host:  db1  db2   To complete the patching process for the above databases, execute it on host where the databases are running. Session log file is /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/opatchauto2024-06-11_01-04-08PM.log The id for this session is P4XT   Executing OPatch prereq operations to verify patch applicability on home /u01/app/grid/19.3.0/gridhome_1 Patch applicability verified successfully on home /u01/app/grid/19.3.0/gridhome_1     Executing patch validation checks on home /u01/app/grid/19.3.0/gridhome_1 Patch validation checks successfully completed on home /u01/app/grid/19.3.0/gridhome_1   OPatchAuto successful.   --------------------------------Summary--------------------------------   Analysis for applying patches has completed successfully:   Host:db1 CRS Home:/u01/app/grid/19.3.0/gridhome_1 Version:19.0.0.0.0     ==Following patches were SUCCESSFULLY analyzed to be applied:   Patch: /orabackup/patches/36233126/36240578 Log: /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2024-06-11_13-04-41PM_1.log   Patch: /orabackup/patches/36233126/36233343 Log: /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2024-06-11_13-04-41PM_1.log   Patch: /orabackup/patches/36233126/36383196 Log: /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2024-06-11_13-04-41PM_1.log   Patch: /orabackup/patches/36233126/36460248 Log: /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2024-06-11_13-04-41PM_1.log   Patch: /orabackup/patches/36233126/36233263 Log: /u01/app/grid/19.3.0/gridhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2024-06-11_13-04-41PM_1.log       OPatchauto session completed at Tue Jun 11 13:05:57 2024 Time taken to complete the session 2 minutes, 12 seconds -bash-5.2#

• Before applying the patch, the readiness of the Grid_Home can be verified by cluvfy utility.

Run this as grid user fron any cluster node

bash-5.2$ cluvfy stage -pre patch   Verifying cluster upgrade state ...PASSED Verifying Software home: /u01/app/grid/19.3.0/gridhome_1 ...PASSED   Pre-check for Patch Application was successful.   CVU operation performed:      stage -pre patch Date:                         Jun 11, 2024 1:23:08 PM CVU home:                     /u01/app/grid/19.3.0/gridhome_1/ User:                         grid

If it doesn’t report any issue, we are good to go.

·         Execute opatchauto utility to start the patching on GI and DB home. Opatchauto will be executed as root user. This will apply patch on a rolling fashion.

The utility must be executed by an operating system (OS) user with root privileges, and it must be executed on each node in the cluster if the Grid home or Oracle RAC database home is in non-shared storage. The utility can be run in parallel on the cluster nodes except for the first (any) node.

Depending on command line options specified, one invocation of OPatchAuto can patch the Grid home, Oracle RAC database homes, or both Grid and Oracle RAC database homes of the same Oracle release version as the patch. You can also roll back the patch with the same selectivity.

Add the directory containing the OPatchAuto to the $PATH environment variable. For example:

export PATH=$PATH:<GI_HOME>/OPatch   Or, when using -oh flag: # export PATH=$PATH:<oracle_home_path>/OPatch To patch the Grid home and all Oracle RAC database homes of the same version: # opatchauto apply <UNZIPPED_PATCH_LOCATION>/36233126 To patch only the Grid home: # opatchauto apply <UNZIPPED_PATCH_LOCATION>/36233126 -oh <GI_HOME> To patch one or more Oracle RAC database homes: # opatchauto apply <UNZIPPED_PATCH_LOCATION>/36233126 -oh <oracle_home1_path>,<oracle_home2_path> To roll back the patch from the Grid home and each Oracle RAC database home: # opatchauto rollback <UNZIPPED_PATCH_LOCATION>/36233126 To roll back the patch from the Grid home: # opatchauto rollback <UNZIPPED_PATCH_LOCATION>/36233126 -oh <path to GI home>  To roll back the patch from the Oracle RAC database home: # opatchauto rollback <UNZIPPED_PATCH_LOCATION>/36233126 -oh <oracle_home1_path>,<oracle_home2_path>

·         In our setup , we are going to use opatchauto for both GI and DB homes using a single command

Login to 1^st node as root user and execute following

export PATH=$PATH:/u01/app/grid/19.3.0/gridhome_1/OPatch   -bash-5.2# opatchauto apply /orabackup/patches/36233126   Once patches are applied on 1st node, login to 2nd node as root user and execute same command   export PATH=$PATH:/u01/app/grid/19.3.0/gridhome_1/OPatch   -bash-5.2# opatchauto apply /orabackup/patches/36233126   Opatchauto itself will shutdown the GI services and apply patch and run post installation steps related to patch.

Login to each node as oracle user and shutdown oracle instance running on that node

$ srvctl stop database -d PROD -i PROD1 $ srvctl status database -d PROD -i PROD1   export PATH=$PATH:/u01/app/oracle/product/19.3.0/dbhome_1/OPatch   opatch apply -oh /u01/app/oracle/product/19.3.0/dbhome_1 -local /orabackup/patches/36233126/36233263   Once patch is applied, start database instance in node 1   $ srvctl start database -d PROD -i PROD1   After database instance started on node 1, switch to node 2 and start patching using same method as mentioned above.

Once patch is applied on both database home, apply datapatch from any node.

-bash-5.2$ /u01/app/oracle/product/19.3.0/dbhome_1/OPatch/datapatch -verbose

Verify patch details

-bash-5.2$ opatch lsinventory | grep 19.23 Patch description:  "Database Release Update : 19.23.0.0.240416 (36233263)"

Automating Object Privileges in Oracle with Stored Procedures

 

Automating Object Privileges in Oracle with Stored Procedures

Introduction

Granting object privileges in Oracle can be a repetitive and time-consuming task, especially when dealing with multiple objects within a schema. This blog post demonstrates how to automate granting SELECT, INSERT, UPDATE, and DELETE privileges on all tables and views of a schema to a specified user using PL/SQL stored procedures.

Creating Users

SQL> CREATE USER test IDENTIFIED BY test;

 

Assigning Initial Grants

We grant the necessary roles and privileges to these users to allow them to connect to the database and perform specific operations:

SQL>grant connect, resource to test;

 

Creating Stored Procedures for Granting Privileges

Granting SELECT on All Tables

SQL> CREATE OR REPLACE PROCEDURE grant_select(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = 'TABLE'     )     LOOP         EXECUTE IMMEDIATE 'GRANT SELECT ON '||r.owner||'.'||r.object_name||' TO ' || grantee;     END LOOP; END; /

 

 

 

Granting SELECT on All Valid Views

CREATE OR REPLACE PROCEDURE grant_select_view(     p_username VARCHAR2,     p_grantee  VARCHAR2 ) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(p_username)           AND object_type = 'VIEW'           AND status = 'VALID'  -- Skip invalid views     )     LOOP         BEGIN             EXECUTE IMMEDIATE 'GRANT SELECT ON ' || r.owner || '.' || r.object_name || ' TO ' || p_grantee;         EXCEPTION             WHEN OTHERS THEN                 DBMS_OUTPUT.PUT_LINE('Skipping view: ' || r.owner || '.' || r.object_name || ' due to error: ' || SQLERRM);         END;     END LOOP; END; /

 

Granting INSERT on All Tables

SQL>CREATE OR REPLACE PROCEDURE grant_insert(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = 'TABLE'     )     LOOP         EXECUTE IMMEDIATE 'GRANT INSERT ON '||r.owner||'.'||r.object_name||' TO ' || grantee;     END LOOP; END; /

 

Granting UPDATE on All Tables

CREATE OR REPLACE PROCEDURE grant_update(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = 'TABLE'     )     LOOP         EXECUTE IMMEDIATE 'GRANT UPDATE ON '||r.owner||'.'||r.object_name||' TO ' || grantee;     END LOOP; END; /

 

Granting DELETE on All Tables

CREATE OR REPLACE PROCEDURE grant_delete(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = 'TABLE'     )     LOOP         EXECUTE IMMEDIATE 'GRANT DELETE ON '||r.owner||'.'||r.object_name||' TO ' || grantee;     END LOOP; END;

 

Executing the Procedures

Once the procedures are created, we can grant privileges to specific users using simple PL/SQL execution. Test is username  on which all grant are being given & HOSPITAL is the schema name which holds all the objects for which grants are being given.

EXEC grant_delete('HOSPITAL', 'test'); EXEC grant_select('HOSPITAL', 'test'); EXEC grant_select_view('HOSPITAL', 'test'); EXEC grant_update('HOSPITAL', 'test'); EXEC grant_insert('HOSPITAL', 'test');

 

Conclusion

Using these stored procedures, we can efficiently grant different types of privileges on all objects within a schema. This approach is particularly useful when dealing with multiple users and large schemas, saving time and ensuring consistency. You can modify these procedures to include additional privileges as per your requirements.

 

How to Configure WebUtil in Oracle Forms 11g – A Step-by-Step Guide

 

Introduction

Oracle Forms 11g lacks native support for client-side operations, such as file selection dialogs, clipboard access, or interactions with Microsoft Office. WebUtil bridges this gap by enabling Oracle Forms applications to perform these client-side operations.

This guide provides a detailed step-by-step procedure to configure WebUtil in Oracle Forms 11g, complete with actual commands.

1.      Prepare the Database Schema for WebUtil

Before using WebUtil, you must create the necessary database objects by executing the create_webutil_db.sql script.

 

Connect to the database as SYSDBA:

$  sqlplus / as sysdba

Create a dedicated WebUtil user :

SQL>CREATE USER webutil IDENTIFIED BY webutil DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;   SQL> GRANT CONNECT, RESOURCE, CREATE SYNONYM, CREATE PUBLIC SYNONYM TO webutil;

Run the WebUtil database script from application server:

$ sqlplus webutil/webutil@DB_SID $ORACLE_HOME/forms/create_webutil_db.sql

1.      Download and Place the JACOB Library Files

WebUtil depends on the JACOB library to interact with Windows-based applications.

Download the Required JACOB Version

·         For Forms 11g Release 1 (11.1.1.X) → JACOB 1.10.1

·         For Forms 11g Release 2 (11.1.2.X) → JACOB 1.14.3

 

The JACOB version can be downloaded from following github site

https://github.com/freemansoft/jacob-project/releases

1.      Copy JACOB Files to Oracle Forms

After downloading the JACOB files, extract to the  ZIP file:

Copy the files in following path of application server

copy jacob.jar $ORACLE_HOME/forms/java/

1.        Place the JACOB DLL Files

Place the JACOB DLL files in the correct directories:

For Forms 11g Release 1 (11.1.1.X):

copy jacob.dll $ORACLE_HOME/forms/webutil/

Forms 11g Release 2 (11.1.2.X):

cd jacob-1.14.3 cp jacob-1.14.3-x86.dll $ORACLE_HOME/forms/webutil/win32/ cp jacob-1.14.3-x64.dll $ORACLE_HOME/forms/webutil/win64/

1.      Sign the JACOB JAR File

To avoid security issues when running Forms, sign the jacob.jar file using Oracle's provided script.

Before sign, change the following in $ORACLE_INSTANCE/bin/sign_webutil.sh  file

SET JAR_KEY_PASSWORD=Password123#

SET KEYSTORE_PASSWORD=Password123#

SET VALIDDAYS=3600

 

cd $ORACLE_INSTANCE/bin $ sh sign_webutil.sh $ORACLE_HOME/forms/java/jacob.jar

1.      Update CLASSPATH in default.env

Modify default.env to include the necessary JAR files.

cd $DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.2/config   Add following parameter   CLASSPATH=$ORACLE_HOME/forms/java/frmall.jar

1.      Modify the formsweb.cfg Configuration File

cd $DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.2/config   Add following [webutil] configuration   [webutil] WebUtilArchive=frmwebutil.jar,jacob.jar baseHTMLjinitiator=webutiljini.htm baseHTMLjpi=webutiljpi.htm archive_jini=frmall_jinit.jar archive=frmall.jar Form=webutil_demo.fmx Userid=username/password@DB_SID ##Provide db username and password and DBsid to connect the DB

Save and exit.

1.      Compile and Deploy webutil_demo.fmb

First download webutil_demo.fmb from following url and place it in application server

https://www.dropbox.com/s/mdkhjh7997r8arh/webutil_demo.fmb?dl=0

 

Open form builder and compile the form webutil_demo.fmb and place it under

$ORACLE_HOME/forms location. 

Or compile it using command

$ Cd $ORACLE_HOME/bin $ frmcmp module=webutil_demo.fmb module_type=form userid=webutil/webutil_password@yourdb compile_all=yes

1.      Compile webutil.pll to create webutil.plx

$ cd $ORACLE_HOME/bin $ frmcmp module=webutil.pll module_type=library userid=webutil/webutil_password@yourdb compile_all=yes

Restart Oracle Forms and weblogic services

$ cd $ORACLE_INSTANCE/bin $ ./opmnctl stopall     #Stop reports services cd $DOMAIN_HOME/bin   ./stopManagedWebLogic.sh WLS_REPORTS   #stop forms services   ./stopManagedWebLogic.sh WLS_FORMS     #stop weblogic admin server   ./stopWebLogic.sh   #start weblogic admin server   cd $DOMAIN_HOME/bin   nohup ./startWebLogic.sh > /dev/null 2>&1 &   #Wait for sometime to start the admin server   #start forms services   nohup ./startManagedWebLogic.sh WLS_FORMS > /dev/null 2>&1 &     #Start reports services   nohup ./startManagedWebLogic.sh WLS_REPORTS > /dev/null 2>&1 &   #start opmn   $ cd $ORACLE_INSTANCE/bin ./opmnctl startall ./opmnctl status

1.      Test WebUtil Configuration

http://your_server_hostname:port/forms/frmservlet?config=webutil

This should open a window like below