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)

How to recovery PDB when PDB database is dropped in Oracle

 

How to recovery PDB when PDB database is dropped :)

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 12:20:24 2025
Version 21.13.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> alter pluggable database PDB1 close;

Pluggable database altered.

SQL>
SQL>
SQL> drop pluggable database PDB1 including datafiles;

Pluggable database dropped.




SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ WRITE NO

============Alert log file contents======

2025-01-11T12:20:48.610884+01:00
alter pluggable database PDB1 close
2025-01-11T12:20:48.621225+01:00
PDB1(3):Pluggable database PDB1 closing
PDB1(3):JIT: pid 92739 requesting stop
PDB1(3):Closing sequence subsystem (204729984896).
PDB1(3):Buffer Cache flush started: 3
PDB1(3):Buffer Cache flush finished: 3
Pluggable database PDB1 closed
Completed: alter pluggable database PDB1 close
2025-01-11T12:21:10.490974+01:00
drop pluggable database PDB1 including datafiles
2025-01-11T12:21:10.596417+01:00
Deleted file +DATA/CDBAM/PDB1/undo_seed01.dbf
Deleted file +DATA/CDBAM/PDB1/users01.dbf
Deleted file +DATA/CDBAM/PDB1/temp01.dbf
Deleted file +DATA/CDBAM/PDB1/sysaux01.dbf
Deleted file +DATA/CDBAM/PDB1/system01.dbf
2025-01-11T12:21:10.640983+01:00
Stopped service pdb1
Completed: drop pluggable database PDB1 including datafiles

======Execute the below script to recover PDB1 ======

Note: Never use RESTORE command as per Oracle support suggestion

run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" 
auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}

log for recovery :-

[oracle@rac01 AUX]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 11 14:07:44 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBAM (DBID=1604254345)

RMAN>

RMAN> run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
2> recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}3> 4> 5> 6> 7> 8>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting recover at 11-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='aiAi'

initialization parameters used for automatic instance:
db_name=CDBAM
db_unique_name=aiAi_pitr_pdb1_CDBAM
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/applications/oracle/d19.3.0/oracle_base
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=6144M
processes=200
db_create_file_dest=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/
log_archive_dest_1='location=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDBAM

Oracle instance started

Total System Global Area    6442447480 bytes

Fixed Size                     9192056 bytes
Variable Size               1124073472 bytes
Database Buffers            5301600256 bytes
Redo Buffers                   7581696 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 11-JAN-25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01 tag=TAG20250111T121656
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl
Finished restore at 11-JAN-25

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for datafile  20 to new;
set newname for datafile  21 to new;
set newname for datafile  22 to new;
set newname for datafile  23 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6, 20, 21, 22, 23;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-JAN-25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA/CDBAM/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to +DATA/CDBAM/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to +DATA/CDBAM/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to +DATA/CDBAM/PDB1/undo_seed01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-JAN-25

datafile 1 switched to datafile copy
input datafile copy RECID=29 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_mr4v9vv6_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_mr4v9vvc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=31 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=32 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_mr4v9vvg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  6 online";
sql clone 'PDB1' "alter database datafile
 20 online";
sql clone 'PDB1' "alter database datafile
 21 online";
sql clone 'PDB1' "alter database datafile
 22 online";
sql clone 'PDB1' "alter database datafile
 23 online";
#recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'PDB1'  delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB1 unplug into ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB1 using ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse";
alter pluggable database PDB1 open;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  20 online

sql statement: alter database datafile  21 online

sql statement: alter database datafile  22 online

sql statement: alter database datafile  23 online

Starting recover at 11-JAN-25
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 4, 7, 15 offline
Executing: alter database datafile 16, 17, 18, 19 offline
starting media recovery

archived log for thread 1 with sequence 269 is already on disk as file +ARCH/cdbam/arch/1_269_1189847369.dbf
archived log for thread 1 with sequence 270 is already on disk as file +ARCH/cdbam/arch/1_270_1189847369.dbf
archived log file name=+ARCH/cdbam/arch/1_269_1189847369.dbf thread=1 sequence=269
archived log file name=+ARCH/cdbam/arch/1_270_1189847369.dbf thread=1 sequence=270
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JAN-25

sql statement: alter database open resetlogs

sql statement: alter pluggable database PDB1 unplug into ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''

sql statement: create pluggable database PDB1 using ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse

Statement processed

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl deleted
Finished recover at 11-JAN-25

RMAN>

PDB1 recovered successfully.

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 13:00:24 2025
Version 21.13.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> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
	 3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

ORA-11183: ASMLIB initialization error [driver/agent not installed] in oracle database in Linux server

 

ORA-11183: ASMLIB initialization error [driver/agent not installed]

After reinstalling grid infrastructure and oracle software  or applying patch (PSU) in linux server. 

You may face below error (and in alert log ORA-11183 error) when you will try to startup the oracle database.

Only three command give you the solution.  

Solution is below.

SQL> host rman target /

Recovery Manager: Release 19.23.0.0.0 - Production on Wed Jan 11 11:05:21 2025

Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup     

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/11/2025 11:05:41
ORA-03113: end-of-file on communication channel
Process ID: 16205
Session ID: 199 Serial number: 44057

RMAN> shutdown immediate

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 01/16/2025 01:23:44
RMAN-12001: could not open channel default
RMAN-01008: could not create channel context
RMAN-01005: error opening cursor
RMAN-01002: ORACLE error: ORA-03114: not connected to ORACLE

RMAN> exit

Alert log content:

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

Errors in file /applications/oracle/d19.3.0/oracle_base/diag/rdbms/pocdb/POCDB02/trace/POCDB02_rbal_602138.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
2025-01-11T09:03:10.764438+01:00
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
2025-01-11T09:03:10.978131+01:00
NOTE: ASMB (index:0) (602142) connected to ASM instance +ASM2, osid: 602152 (Flex mode; client id 0xea3b5ea7aa6dc66d)
NOTE: initiating MARK startup
Starting background process MARK

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:4060941312) (new connection)
Wed Jan 11 11:05:37 2025
Errors in file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_rbal_16178.trc:
ORA-11183: ASMLIB initialization error [driver/agent not installed]
Wed Jan 11 11:05:37 2025
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
Wed Jan 11 11:05:37 2025
starting up 1 shared server(s) ...
Wed Jan 11 11:05:37 2025
NOTE: ASMB connected to ASM instance +ASM osid: 16186 (Flex mode; client id 0xffffffffffffffff)
NOTE: initiating MARK startup
Starting background process MARK
Wed Jan 11 11:05:37 2025
MARK started with pid=27, OS id=16192
Wed Jan 11 11:05:37 2025
NOTE: MARK has subscribed
ORACLE_BASE from environment = /userdata/app/oracle
Wed Jan 11 11:05:39 2025
alter database mount
Wed Jan 11 11:05:40 2025
NOTE: ASMB mounting group 1 (DATA)
Wed Jan 11 11:05:40 2025
WARNING: cellinit.ora is missing. RBAL is terminating the instance.
RBAL (ospid: 16178): terminating the instance due to error 27625
Wed Jan 11 11:05:41 2025
System state dump requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
System State dumped to trace file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_diag_16112_20250111101541.trc
Wed Jan 11 11:05:41 2025
Dumping diagnostic data in directory=[cdmp_20250111101541], requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
Wed Jan 11 11:05:41 2025
Instance terminated by RBAL, pid = 16178

Cause:

This is an Ownership issue.

After reinstalling grid and oracle software, or applying Patch, it changed the group (ASMADMIN) to (OINSTALL) for ORACLE user and   

$ORACLE_HOME/bin folder. 

So now you have to change the group back again to ASMADMIN and give appropriate permission also.

Before :- 
With root user :-

[oracle@rac01]$ su - root
Password:
[root@rac01 ~]#
[root@rac01 ~]#
[root@rac01 ~]# cd $ORACLE_HOME/bin
[root@rac01 bin]#  ls -lrt oracle
-rwsr-s--x 1 oracle oinstall 462314040 Jan 10 10:45 oracle

After :- Change the ownership and permission 

[root@rac01 bin]# chgrp asmadmin oracle
[root@rac01 bin]#
[root@rac01 bin]# chmod 6751 oracle
[root@rac01 bin]#
[root@rac01 bin]#

[root@rac01 bin]# ls -lrt oracle
-rwxr-x--x 1 oracle asmadmin 462314040 Jan 10 10:45 oracle

After that Database will be startup ..

SQL>
SQL>
SQL>
SQL> host rman target /

Recovery Manager: Release 19.23.0.0.0 - Production on Thu Jan 16 01:24:01 2025

Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount ;

Oracle instance started
database mounted

Total System Global Area    4101417920 bytes

Fixed Size                     2932096 bytes
Variable Size               2197811936 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13844480 bytes

RMAN>