Index rebuild online in Oracle - shell script

 

[oracle@rac10p reorg]$ cat index_rebuild_EMP.sh

#!/bin/ksh

export ORACLE_HOME=/oracle/K12/19

export ORACLE_SID=K12

export PATH=$PATH:/$ORACLE_HOME/bin

export TS=`date +%d%m%y`

sqlplus -S / as sysdba << EOF

set heading off;

spool  /home/oracle/index_rebuild_EMP_output.log

set echo on feedback on

@/home/oracle/reorg/index_rebuild_EMP.sql

spool off;

exit;

EOF

[oracle@rac10p reorg]$ cat index_rebuild_EMP.sql

set time on timing on;

ALTER INDEX SAPDAT."EMP~0" REBUILD online parallel 6;

alter index sapdat."EMP~0" noparallel;

ALTER INDEX SAPDAT."EMP~001" REBUILD online parallel 6;

alter index sapdat."EMP~001" noparallel;

ALTER INDEX SAPDAT."EMP~002" REBUILD online parallel 6;

alter index sapdat."EMP~002" noparallel;

ALTER INDEX SAPDAT."EMP~003" REBUILD online parallel 6;

alter index sapdat."EMP~003" noparallel;

ALTER INDEX SAPDAT."EMP~004" REBUILD online parallel 6;

alter index sapdat."EMP~004" nonparallel;

alter table move online - table defragmentation

 

MOVE ONLINE implemented in 12cR2 maintains indexes so they do not 

become UNUSABLE and DML does not have to wait. (Oracle Database)

When you execute an "ALTER TABLE MOVE ONLINE" command in Oracle, the database essentially creates a new copy of the table in the specified target tablespace while maintaining accessibility to the original table for queries and DML operations, effectively "moving" the data without taking the table offline; this process happens in the background by allocating new data blocks, copying data over, and updating internal metadata, all while allowing concurrent user access to the table with minimal performance impact. 

Key points about "ALTER TABLE MOVE ONLINE":

Online operation:

The most important aspect is that the table remains available for reads and writes during the move, unlike a traditional offline table move which would require taking the table offline temporarily.

Data copying:

The database internally copies data from the old table segments to the new ones in the target tablespace, managing the process efficiently to minimize disruption.

Index management:

When moving a table online, Oracle automatically updates associated indexes to point to the new data location, ensuring data consistency.

Parallelism:

Depending on the Oracle version and configuration, the move operation can utilize multiple processes to speed up the data transfer.

Important considerations:

Performance impact:

While the move is designed to be online, heavy concurrent DML operations may still experience some performance degradation during the data copy process. 

Not for all scenarios:

Certain operations like parallel DML or direct path inserts might not be supported during an online table move. 

Availability requirement:

This feature is particularly useful when you need to move a large table to a different tablespace for storage optimization without impacting ongoing application operations. 

Check the size of the table and Indexes:

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

SEGMENT_NAME    SEGMENT_TYPE            GB
--------------- ------------------ ----------
EMP              TABLE              158.641602
EMP~0            INDEX              124.044922
EMP~001          INDEX              74.7177734
EMP~003          INDEX              60.8652344
EMP~004          INDEX              17.7597656
EMP~002          INDEX              15.6337891

Check Fragmentation in Table:

set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = '&TABLE_NAME'
order by 6;

OWNER      TABLE_NAME    Size(G)   Actual(G)    LAST ANAL          Diff(G)        % Frag
---------- ---------- ----------   ---------- ---------------      ----------    ----------
SAPDAT     EMP         158.641602 71.7050629    27-NOV-24               86          54.8005932

Check the status of the index, degree and last analyzed :-

col STATUS for a10
select index_name,status,degree from dba_indexes where table_name = '&table_name';

INDEX_NAME           STATUS   DEGREE
-------------------- -------- ----------
EMP~0                VALID        1
EMP~001              VALID        1
EMP~002              VALID        1
EMP~003              VALID        1
EMP~004              VALID        1


col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP    YES                 1      27-NOV-24

Check the temporary tablespace size

col Free_Space_GB for 99999999

select TABLESPACE_NAME ,tablespace_size/1024/1024/1024 total_GB ,ALLOCATED_SPACE/1024/1024/1024 Allocate_Space_GB ,free_space/1024/1024/1024 Free_Space_GB

from dba_temp_free_space;

Perform De-fragmentation on table online :-

set time on;
set timing on;

SQL> alter table sapdat.EMP move online parallel 5;

Table altered.

Elapsed: 01:22:54.21

Check the degree of the table:-

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP    YES                 1     05-FEB-25

Check the Status and Degree of the Indexes:-

col STATUS for a10
select index_name,status,degree from dba_indexes where table_name = '&table_name';

INDEX_NAME      STATUS     DEGREE
--------------- ---------- ----------
EMP~0           VALID        1
EMP~001         VALID        1
EMP~002         VALID        1
EMP~003         VALID        1
EMP~004         VALID        1

Note: Index status is still VALID 

Check the table and indexes size:-

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB from dba_segments where SEGMENT_NAME LIKE 'EMP%' AND OWNER='SAPDAT' order by GB desc;

SEGMENT_NAME         SEGMENT_TYPE               GB
-------------------- ------------------ ----------
EMP                   TABLE              85.6797485
EMP~0                 INDEX              56.4254761
EMP~003               INDEX              31.8675537
EMP~001               INDEX              31.8223877
EMP~004               INDEX              11.7949829

Note: You can see that indexes size also reduced when table is moved online so no need to 'REBUILD INDEX' and status of indexes are also VALID

Perform gather stats on table:-

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SAPDAT','EMP',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto', degree => DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:04:17.70

Check the LAST_ANALYZED for the table :

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP         YES           1      05-FEB-25

Check Fragmentation on Table:

set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = '&TABLE_NAME'
order by 6;

OWNER      TABLE_NAME    Size(G)  Actual(G)   LAST ANAL          Diff(G)     % Frag
---------- ---------- ----------  ---------- ---------------    ---------- ----------
SAPDAT     EMP         85.6797485 70.8516945  05-FEB-25               14    17.3063697

Online Operations in Oracle 11g/ 12c (12.1) /12c (12.2) / 18c /19c Databases

 

Online Operations in Oracle 11g/ 12c (12.1) /12c (12.2) / 18c Databases

Online Operations in Oracle 11g/12c (12.1)/12c (12.2)/18c Databases

Oracle 11g (11.2) & Prior

--------------------------

1. Create index online

2. rebuild index online

3. rebuild index partition online

4. Add Column

5. Add Constraint enable novalidate

Oracle 12c (12.1)

------------------

1. Online move partition

2. Drop index online

3. Set unused column online

4. alter column visible/invisible

5. alter index unusable online

6. alter index visible/invisible 

7. alter index parallel/noparallel

Oracle 12c (12.2)

------------------

1. Alter table  move online for non-partitioned tables

2. Alter table  from non-partitioned to partitioned online

3. Alter table  split partition online

4. Create table for exchange (usable for online partition exchange)

5. Move/merge/split partition maintenance operations can now do data filtering

Oracle 18c/19c

----------------

1. Alter table modify partitioned table to a different partitioning method

2. Alter table merge partition/subpartition online

Data loading script

 

Cursor for loading data on W_ASSESS_F_PART table 

******************************************************

alter session force parallel DDL parallel 16 ;

alter session force parallel DML parallel 16 ;

alter session set db_file_multiblock_read_count=256;

CREATE OR REPLACE PROCEDURE Load_W_ASSESS_F_PART (p_array_size IN PLS_INTEGER DEFAULT 10000)

IS

TYPE ARRAY IS TABLE OF OLAP.W_ASSESS_F%ROWTYPE;

l_data ARRAY;

CURSOR c IS select * from olap.W_ASSESS_F where X_ASSESS_TMPL_NAME<>'PDI Template';

BEGIN

    OPEN c;

    LOOP

    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT

    INSERT /*+ appand */ INTO OLAP.W_ASSESS_F_PART VALUES l_data(i);

    commit;

    EXIT WHEN c%NOTFOUND;

    END LOOP;

    CLOSE c;

END Load_W_ASSESS_F_PART;

/

BULK Update Cursour 

**************************************************************

Source Table : OLAP.W_ASSESS_F

Target Table : OLAP.W_ASSESS_F_PART

**************************************************************

13:50:09 SQL> exec Load_W_ASSESS_F_PART;

PL/SQL procedure successfully completed.

Elapsed: 02:03:04.82

15:53:22 SQL> 

15:53:22 SQL>

How to reduce the size of Data file in Oracle

 

file_downsize.sql

set lines 300 pages 300 ;

col file_name for a70 ;

col "Tablespace NAME" format a30

select b.file_id ,b.file_name ,a.tablespace_name "Tablespace_NAME", round((b.totalspace - a.freespace),1)"USED_SPACE_GB",

round(a.freespace,1) "FREE_SPACE_GB",round(b.totalspace) "TOTAL_SPACE_GB"

from

(select file_id ,tablespace_name,file_name,(bytes)/1024/1024/1024 TotalSpace

from dba_data_files) b,

(select file_id,tablespace_name,sum(bytes)/1024/1024/1024 FreeSpace

from dba_free_space

group by tablespace_name,file_id ) a

where b.tablespace_name = a.tablespace_name(+)

and a.file_id=b.file_id

and b.tablespace_name like upper('%&tablespace_name%')

order by 3,1;

How to Patch 19.26 on Oracle Restart 19c (GRID and ORACLE)

 

GI and database home are currently on 19.24

What to achieve:-

1) Patch to 19.26

2) Patch both the GI and Database home in one operation

List of the steps to follow during the activity :

1. Check the current version of Opatch Tool
2. Upgrade the Opatch utility if needed
3. Take the backup of GRID HOME and ORACLE_HOME
4. Apply "AUTO" patch using root for GRID HOME and ORACLE_HOME
5. Validate applied patches on GRID HOME and ORACLE_HOME patch in inventory
6. Execute Post-installation Steps (datapatch)
7. Verify the status of the patch applied from dba_registry_sqlpatch.
8. Verify INVALID objects

Note:- Before applying patch, we have to change the compatibility of rdbms for diskgroup as we faced issue with "datapatch" auto applying

[grid@gms04dbaora01t ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 5 08:09:40 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;  2

DISKGROUP                      ASM_COMPAT   DB_COMPAT
------------------------------ ------------ ------------
ARCH_ORCL                  19.0.0.0.0   10.1.0.0.0
DATA_ORCL                  19.0.0.0.0   10.1.0.0.0

SQL>
SQL>
SQL> ALTER DISKGROUP DATA_ORCL SET ATTRIBUTE 'compatible.rdbms' = '19.0';

Diskgroup altered.

SQL>
SQL> ALTER DISKGROUP ARCH_ORCL SET ATTRIBUTE 'compatible.rdbms' = '19.0';

Diskgroup altered.

SQL>
SQL>
SQL> SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;  2

DISKGROUP                      ASM_COMPAT   DB_COMPAT
------------------------------ ------------ ------------
ARCH_ORCL                      19.0.0.0.0   19.0.0.0.0
DATA_ORCL                      19.0.0.0.0   19.0.0.0.0

Check the OPatch version for GRID and Oracle:-

1. Download p6880880_190000_Linux-x86-64.zip OPatch for GRID HOME and Oracle Home:-

2. Copy p6880880_190000_Linux-x86-64.zip to GRID_HOME as root user and ORACLE_HOME as Oracle users

Update the OPatch version for GRID_HOME:- 

[grid@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

[root@gms04dbaora01t grid_home]# mv OPatch OPatch_bkp
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]# pwd
/applications/oracle/g19.3.0/grid_home
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
root@gms04dbaora01t grid_home]# unzip p6880880_190000_Linux-x86-64.zip
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]# chown -R grid:oinstall OPatch
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#

[grid@gms04dbaora01t grid_home]$
[grid@gms04dbaora01t grid_home]$ cd OPatch
[grid@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.45

OPatch succeeded.

grid@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

[root@gms04dbaora01t grid_home]# mv OPatch OPatch_bkp
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]# pwd
/applications/oracle/g19.3.0/grid_home
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
root@gms04dbaora01t grid_home]# unzip p6880880_190000_Linux-x86-64.zip
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]# chown -R grid:oinstall OPatch
[root@gms04dbaora01t grid_home]#
[root@gms04dbaora01t grid_home]#

[grid@gms04dbaora01t grid_home]$
[grid@gms04dbaora01t grid_home]$ cd OPatch
[grid@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.45

OPatch succeeded.

Update the OPatch version for ORACLE_HOME:- 

[oracle@gms04dbaora01t ~]$ cd /applications/oracle/d19.3.0/oracle_home/OPatch
[oracle@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.41

OPatch succeeded.

[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$ mv OPatch OPatch_bkp_030225
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$ unzip p6880880_190000_Linux-x86-64.zip
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$
[oracle@gms04dbaora01t oracle_home]$

[oracle@gms04dbaora01t oracle_home]$ cd OPatch
[oracle@gms04dbaora01t OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.45

OPatch succeeded.

Determine whether any currently installed interim patches conflict with the patch being installed.

Downloaded 19.26 Patch and kept in /tmp/19.26_Grid - location 

[grid@gms04dbaora01t OPatch]$ cd /tmp/19.26_Grid/37257886/37268031
[grid@gms04dbaora01t 37268031]$
[grid@gms04dbaora01t 37268031]$
[grid@gms04dbaora01t 37268031]$
[grid@gms04dbaora01t 37268031]$ ls -lrt
[grid@gms04dbaora01t 37257886]$ ls -lrt
total 164
drwxrwxr-x 5 grid oinstall     62 Jan 20 14:03 37268031
drwxrwxr-x 5 grid oinstall     62 Jan 20 14:04 37266638
drwxrwxr-x 4 grid oinstall     48 Jan 20 14:05 37461387
drwxrwxr-x 5 grid oinstall     81 Jan 20 14:05 37260974
drwxrwxr-x 4 grid oinstall     48 Jan 20 14:05 36758186
-rwxrwxr-x 1 grid oinstall      0 Jan 20 14:08 README.txt
drwxrwxr-x 2 grid oinstall   4096 Jan 20 14:08 automation
-rwxrwxr-x 1 grid oinstall   5824 Jan 20 15:34 bundle.xml
-rwxrwxr-x 1 grid oinstall 152404 Jan 21 11:08 README.html
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ export PATH=$PATH:/applications/oracle/g19.3.0/grid_home/OPatch
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37268031
-bash: ./opatch: No such file or directory
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ cd /applications/oracle/g19.3.0/grid_home/OPatch
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37268031
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-13-31PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37268031
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-14-48PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37266638
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-15-05PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.





[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37461387
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-15-21PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.





[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37260974
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-15-38PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.




[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/36758186
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-02-04_17-16-06PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.





[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ ./opatch prereq CheckSystemSpace -phBaseFile /home/grid/patch_list_gihome.txt
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/g19.3.0/grid_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/g19.3.0/grid_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatch/opatch2025-01-30_14-35-07PM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.


Check Conflict with ORACLE_HOME:-

[oracle@gms04dbaora01t ~]$ . oraenv
ORACLE_SID = [ORCL] ?
The Oracle base remains unchanged with value /applications/oracle/d19.3.0/oracle_base
[oracle@gms04dbaora01t ~]$
[oracle@gms04dbaora01t ~]$
[oracle@gms04dbaora01t ~]$ export PATH=$PATH:/applications/oracle/d19.3.0/oracle_home/OPatch
[oracle@gms04dbaora01t ~]$
[oracle@gms04dbaora01t ~]$
[oracle@gms04dbaora01t ~]$ cd /applications/oracle/d19.3.0/oracle_home/OPatch
[oracle@gms04dbaora01t OPatch]$
[oracle@gms04dbaora01t OPatch]$
[oracle@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37268031
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/d19.3.0/oracle_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/d19.3.0/oracle_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/d19.3.0/oracle_home/cfgtoollogs/opatch/opatch2025-02-04_17-17-00PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.





[oracle@gms04dbaora01t OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/19.26_Grid/37257886/37260974
Oracle Interim Patch Installer version 12.2.0.1.45
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /applications/oracle/d19.3.0/oracle_home
Central Inventory : /applications/oracle/oraInventory
   from           : /applications/oracle/d19.3.0/oracle_home/oraInst.loc
OPatch version    : 12.2.0.1.45
OUI version       : 12.2.0.7.0
Log file location : /applications/oracle/d19.3.0/oracle_home/cfgtoollogs/opatch/opatch2025-02-04_17-17-21PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Now apply the Patch using ROOT user and Patch will be applied to GRID_HOME and ORACLE_HOME automatically. 

Note:- No need to shutdown any services from our end.

[root@gms04dbaora01t OPatch]# opatchauto apply /tmp/19.26_Grid/37257886

OPatchauto session is initiated at Wed Feb  5 08:38:29 2025

System initialization log file is /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchautodb/systemconfig2025-02-05_08-38-33AM.log.

Session log file is /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/opatchauto2025-02-05_08-38-37AM.log
The id for this session is 68HD

Executing OPatch prereq operations to verify patch applicability on home /applications/oracle/d19.3.0/oracle_home
Patch applicability verified successfully on home /applications/oracle/d19.3.0/oracle_home


Executing patch validation checks on home /applications/oracle/d19.3.0/oracle_home
Patch validation checks successfully completed on home /applications/oracle/d19.3.0/oracle_home


Verifying SQL patch applicability on home /applications/oracle/d19.3.0/oracle_home
SQL patch applicability verified successfully on home /applications/oracle/d19.3.0/oracle_home


Executing OPatch prereq operations to verify patch applicability on home /applications/oracle/g19.3.0/grid_home
Patch applicability verified successfully on home /applications/oracle/g19.3.0/grid_home


Executing patch validation checks on home /applications/oracle/g19.3.0/grid_home
Patch validation checks successfully completed on home /applications/oracle/g19.3.0/grid_home


Preparing to bring down database service on home /applications/oracle/d19.3.0/oracle_home
Successfully prepared home /applications/oracle/d19.3.0/oracle_home to bring down database service


Bringing down database service on home /applications/oracle/d19.3.0/oracle_home
Following database has been stopped and will be restarted later during the session: r01esb01
Database service successfully brought down on home /applications/oracle/d19.3.0/oracle_home


Performing prepatch operations on CRS - bringing down CRS service on home /applications/oracle/g19.3.0/grid_home
Prepatch operation log file location: /applications/oracle/g19.3.0/grid_base/crsdata/gms04dbaora01t/crsconfig/hapatch_2025-02-05_08-42-06AM.log
CRS service brought down successfully on home /applications/oracle/g19.3.0/grid_home


Start applying binary patch on home /applications/oracle/d19.3.0/oracle_home
Binary patch applied successfully on home /applications/oracle/d19.3.0/oracle_home


Running rootadd_rdbms.sh on home /applications/oracle/d19.3.0/oracle_home
Successfully executed rootadd_rdbms.sh on home /applications/oracle/d19.3.0/oracle_home


Start applying binary patch on home /applications/oracle/g19.3.0/grid_home
Binary patch applied successfully on home /applications/oracle/g19.3.0/grid_home


Running rootadd_rdbms.sh on home /applications/oracle/g19.3.0/grid_home
Successfully executed rootadd_rdbms.sh on home /applications/oracle/g19.3.0/grid_home


Performing postpatch operations on CRS - starting CRS service on home /applications/oracle/g19.3.0/grid_home
Postpatch operation log file location: /applications/oracle/g19.3.0/grid_base/crsdata/gms04dbaora01t/crsconfig/hapatch_2025-02-05_08-57-30AM.log
CRS service started successfully on home /applications/oracle/g19.3.0/grid_home


Starting database service on home /applications/oracle/d19.3.0/oracle_home
Database service successfully started on home /applications/oracle/d19.3.0/oracle_home


Preparing home /applications/oracle/d19.3.0/oracle_home after database service restarted
No step execution required.........


Trying to apply SQL patch on home /applications/oracle/d19.3.0/oracle_home
SQL patch applied successfully on home /applications/oracle/d19.3.0/oracle_home

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:gms04dbaora01t
SIDB Home:/applications/oracle/d19.3.0/oracle_home
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /tmp/19.26_Grid/37257886/37266638
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /tmp/19.26_Grid/37257886/36758186
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /tmp/19.26_Grid/37257886/37461387
Reason: This patch is not applicable to this specified target type - "oracle_database"


==Following patches were SUCCESSFULLY applied:

Patch: /tmp/19.26_Grid/37257886/37260974
Log: /applications/oracle/d19.3.0/oracle_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-43-00AM_1.log

Patch: /tmp/19.26_Grid/37257886/37268031
Log: /applications/oracle/d19.3.0/oracle_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-43-00AM_1.log


Host:gms04dbaora01t
SIHA Home:/applications/oracle/g19.3.0/grid_home
Version:19.0.0.0.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /tmp/19.26_Grid/37257886/36758186
Log: /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-49-43AM_1.log

Patch: /tmp/19.26_Grid/37257886/37260974
Log: /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-49-43AM_1.log

Patch: /tmp/19.26_Grid/37257886/37266638
Log: /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-49-43AM_1.log

Patch: /tmp/19.26_Grid/37257886/37268031
Log: /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-49-43AM_1.log

Patch: /tmp/19.26_Grid/37257886/37461387
Log: /applications/oracle/g19.3.0/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2025-02-05_08-49-43AM_1.log



OPatchauto session completed at Wed Feb  5 09:01:41 2025
Time taken to complete the session 23 minutes, 8 seconds

Crosscheck the Applied patch details.

[root@gms04dbaora01t OPatch]# su - grid
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /applications/oracle/g19.3.0/grid_base
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ export PATH=$PATH:/applications/oracle/g19.3.0/grid_home/OPatch
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$
[grid@gms04dbaora01t ~]$ cd /applications/oracle/g19.3.0/grid_home/OPatch
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ opatch lspatches
37461387;TOMCAT RELEASE UPDATE 19.0.0.0.0 (37461387)
37268031;OCW RELEASE UPDATE 19.26.0.0.0 (37268031)
37266638;ACFS RELEASE UPDATE 19.26.0.0.0 (37266638)
37260974;Database Release Update : 19.26.0.0.250121 (37260974)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0 (36758186)

OPatch succeeded.

Post-check for checking GRID Version :-

[grid@gms04dbaora01t OPatch]$ crsctl query has releasepatch
Oracle Clusterware release patch level is [117906255] and the complete list of patches [36758186 37260974 37266638 37268031 37461387 ] have been applied on the local node. The release patch string is [19.26.0.0.0].
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[grid@gms04dbaora01t OPatch]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$
[grid@gms04dbaora01t OPatch]$ crsctl query has softwarepatch
Oracle Clusterware patch level on node gms04dbaora01t is [117906255].

Note:- Apply SQL changes (datapatch)

When we applied 19.16 patch SQL changes automatically reflected/update by autopatch only so we don't need to run datapatch -verbose  explicitly 

[oracle@gms04dbaora01t bin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 5 09:06:00 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> 
SET LINESIZE 400
SET PAGESIZE 100
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A85
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
status,
description,
patch_id
FROM   sys.dba_registry_sqlpatch
ORDER by action_time; 

																								    
ACTION_TIME          ACTION     STATUS     DESCRIPTION                                                PATCH_ID
-------------------- ---------- ---------- -------------------------------------------------------- ----------
05-FEB-2025 09:01:10 APPLY      SUCCESS    Database Release Update : 19.26.0.0.250121 (37260974)      37260974
19-JUN-2024 13:28:25 APPLY      SUCCESS    Database Release Update : 19.23.0.0.240416 (36233263)      36233263

[oracle@gms04dbaora01t OPatch]$ ./opatch lspatches
37268031;OCW RELEASE UPDATE 19.26.0.0.0 (37268031)
37260974;Database Release Update : 19.26.0.0.250121 (37260974)