pcs cluster switch from cluster mode to local mount mode

Stop the cluster mode

root@rock8-19c-2:~>% pcs property set maintenance-mode=true

root@rock8-19c-2:~>% pcs status

Cluster name: igt055_cluster

Status of pacemakerd: 'Pacemaker is running' (last updated 2024-04-11 11:03:14Z)

Cluster Summary:

  * Stack: corosync

  * Current DC: rock8-19c-1 (version 2.1.5-8.1.el8_8-a3f44794f94) - partition with quorum

  * Last updated: Thu Apr 11 11:03:14 2024

  * Last change:  Thu Apr 11 11:03:09 2024 by root via cibadmin on rock8-19c-2

  * 2 nodes configured

  * 6 resource instances configured

              *** Resource management is DISABLED ***

  The cluster will not attempt to start, stop or recover services

Node List:

  * Online: [ rock8-19c-1 rock8-19c-2 ]

Full List of Resources:

  * Resource Group: ora_igt_rg (unmanaged):

    * db1_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2 (unmanaged)

    * online_igt_fs     (ocf::heartbeat:Filesystem):     Started rock8-19c-2 (unmanaged)

    * exp_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2 (unmanaged)

    * db2_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2 (unmanaged)

    * ora_igt_vip       (ocf::heartbeat:IPaddr2):        Started rock8-19c-2 (unmanaged)

    * ora_igt_ap        (lsb:dbora_ctl):         Started rock8-19c-2 (unmanaged)

Daemon Status:

  corosync: active/enabled

  pacemaker: active/enabled

  pcsd: active/enabled

Can change oracle archive mode now

For example:

oracle@rock8-19c-2:~>% sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 11 11:15:45 2024

Version 19.22.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.22.0.0.0

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 8589932704 bytes

Fixed Size                  8960160 bytes

Variable Size            4328521728 bytes

Database Buffers         4244635648 bytes

Redo Buffers                7815168 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> ARCHIVE LOG LIST;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle_db/db1/db_igt/arch

Oldest online log sequence     496

Next log sequence to archive   498

Current log sequence           498

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Now start cluster mode

root@rock8-19c-2:~>% pcs property set maintenance-mode=false

root@rock8-19c-2:~>% pcs resource show

Warning: This command is deprecated and will be removed. Please use 'pcs resource status' instead.

  * Resource Group: ora_igt_rg:

    * db1_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * online_igt_fs     (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * exp_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * db2_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * ora_igt_vip       (ocf::heartbeat:IPaddr2):        Started rock8-19c-2

    * ora_igt_ap        (lsb:dbora_ctl):         Started rock8-19c-2

root@rock8-19c-2:~>% pcs resource disable ora_igt_rg

root@rock8-19c-2:~>% pcs resource enable ora_igt_rg

root@rock8-19c-2:~>% pcs status

Cluster name: igt055_cluster

Status of pacemakerd: 'Pacemaker is running' (last updated 2024-04-11 12:34:54Z)

Cluster Summary:

  * Stack: corosync

  * Current DC: rock8-19c-1 (version 2.1.5-8.1.el8_8-a3f44794f94) - partition with quorum

  * Last updated: Thu Apr 11 12:34:54 2024

  * Last change:  Thu Apr 11 11:35:59 2024 by root via cibadmin on rock8-19c-2

  * 2 nodes configured

  * 6 resource instances configured

Node List:

  * Online: [ rock8-19c-1 rock8-19c-2 ]

Full List of Resources:

  * Resource Group: ora_igt_rg:

    * db1_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * online_igt_fs     (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * exp_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * db2_igt_fs        (ocf::heartbeat:Filesystem):     Started rock8-19c-2

    * ora_igt_vip       (ocf::heartbeat:IPaddr2):        Started rock8-19c-2

    * ora_igt_ap        (lsb:dbora_ctl):         Started rock8-19c-2

Daemon Status:

  corosync: active/enabled

  pacemaker: active/enabled

  pcsd: active/enabled

ORA-12547: TNS:lost contact

ERROR:
ORA-12547: TNS:lost contact

=============================================
Correct permissions should be:
ls -l ${ORACLE_HOME}bin/oracle
-rwsr-s--x 1 oracle dba 409028888 Aug 27  2019 bin/oracle

But now:
ls -l ${ORACLE_HOME}bin/oracle
-rwxr-x--x 1 oracle dba 457057360 Mar 28 08:07 /software/oracle/19c/bin/oracle
=============================================

How to fix:
Per Oracle technote "Troubleshooting ORA-12547 TNS: Lost Contact (Doc ID 555565.1)"
Solution is to make sure file system for database home has setuid/suid set, database binary($RDBMS_HOME/bin/oracle) has correct ownership and permission
su - oracle
cd $ORACLE_HOME/bin/
chmod 6751 oracle

drbd sync SOW with drbdadm and reference

A. Activity Overview 

1. drdb syncrinization

B. Prerequisite and Pre-checks:

Steps:

1. On servers BD902G and BD901G

pcs status

Server which is running oracle service ora_igt_rg  is Active. (Primary)

The other server is Passive (Secondary)

2. drbd General Note:

Data is written on Active server by oracle service, and synchronized by drbd to Standby server.

drbd has its own CLI, drbdadm

Command are run per LV Group (db2, db1, Ora_Exp, Ora_Online). Each Volume sync can take few minutes to complete.

drbdadm status during synchronization, will show progress, in percent.

drbdadm commands should be run as root.

drbdadm commads are run either on Active (Primary), or on Passive (Secondary) node, per context, see below.

3. Check status before starting synchronization

Run drbdadm status on Active server

Expected result:

connection:StandAlone – meaning there is no sync with the Secondary node

root>% drbdadm status

Ora_Exp role:Primary

  disk:UpToDate

  DBD902G connection:StandAlone

Ora_Online role:Primary

  disk:UpToDate

  DBD902G connection:StandAlone

db1 role:Primary

  disk:UpToDate

  DBD902G connection:StandAlone

db2 role:Primary

  disk:UpToDate

  DBD902G connection:StandAlone

4. Synchronization commands.

Yellow: commands on secondary site

Green: commands on primary site

drbdadm status

drbdadm status

drbdadm secondary Ora_Exp

drbdadm disconnect Ora_Exp

drbdadm -- --discard-my-data connect Ora_Exp

// per another documentation, use this: 
//drbdadm connect --discard-my-data Ora_Exp

drbdadm connect Ora_Exp

drbdadm status

 

drbdadm secondary Ora_Online

drbdadm disconnect Ora_Online

drbdadm -- --discard-my-data connect Ora_Online

drbdadm connect Ora_Online

drbdadm status

drbdadm secondary db1

drbdadm disconnect db1

drbdadm -- --discard-my-data connect db1

drbdadm connect db1

drbdadm status

drbdadm secondary db2

drbdadm disconnect db2

drbdadm -- --discard-my-data connect db2

drbdadm connect db2

drbdadm status

 

drbdadm status

drbdadm status

1.After synchronization check status

Now, Primary node is aware of secondary node

Secondary node is aware on Primary node.

In this example:

DBD901G - is Primary

DBD902G - is Secondary

root@DBD901G:~>% drbdadm  status

Ora_Exp role:Primary

  disk:UpToDate

  SRV902G role:Secondary

    peer-disk:UpToDate

Ora_Online role:Primary

  disk:UpToDate

  SRV902G role:Secondary

    peer-disk:UpToDate

db1 role:Primary

  disk:UpToDate

  SRV902G role:Secondary

    peer-disk:UpToDate

db2 role:Primary

  disk:UpToDate

  SRV902G role:Secondary

    peer-disk:UpToDate

root@DBD902G:~>% drbdadm  status

Ora_Exp role:Secondary

  disk:UpToDate

  DBD901G role:Primary

    peer-disk:UpToDate

Ora_Online role:Secondary

  disk:UpToDate

  DBD901G role:Primary

    peer-disk:UpToDate

db1 role:Secondary

  disk:UpToDate

  DBD901G role:Primary

    peer-disk:UpToDate

db2 role:Secondary

  disk:UpToDate

  DBD901G role:Primary

    peer-disk:UpToDate

completed!!

Reference: https://linbit.com/drbd-user-guide/drbd-guide-9_0-en/#p-work

Additional commands

drbdadm verify <resource>

drbdadm verify all

- used for monitoring

drbdadm primary <resource>

drbdadm secondary <resource>

- set resource as Primary or Secondary

drbdsetup status home

- get status for current node

drbdsetup status home --verbose --statistics

or

drbdsetup status --verbose --statistics

- get detailed status for current node

drbdsetup events2 --now r0

- Get current stats

drbdsetup events2 r0

- Get constant stats, updating each second

drbdsetup events2 --statistics --now r0

- Get current stats, with details

drbdadm cstate

drbdadm cstate <resource>:<peer>

- Get the node state
- <resource>:<peer> - The default is the peer’s hostname as given in the configuration file.

A resource may have one of the following connection states:

StandAlone

No network configuration available. 

The resource has not yet been connected, or has been administratively disconnected (using drbdadm disconnect), or has dropped its connection due to failed authentication or split brain.

Disconnecting

Temporary state during disconnection. The next state is StandAlone.

Unconnected

Temporary state, prior to a connection attempt. Possible next states: Connecting.

Timeout

Temporary state following a timeout in the communication with the peer. Next state: Unconnected.

BrokenPipe

Temporary state after the connection to the peer was lost. Next state: Unconnected.

NetworkFailure

Temporary state after the connection to the partner was lost. Next state: Unconnected.

ProtocolError

Temporary state after the connection to the partner was lost. Next state: Unconnected.

TearDown

Temporary state. The peer is closing the connection. Next state: Unconnected.

Connecting

This node is waiting until the peer node becomes visible on the network.

Connected

A DRBD connection has been established, data mirroring is now active. This is the normal state.

drbdadm role

Resource Roles

You may see one of the following resource roles:

Primary

The resource is currently in the primary role, and may be read from and written to. 

This role only occurs on one of the two nodes, unless dual-primary mode is enabled.

Secondary

The resource is currently in the secondary role. 

It normally receives updates from its peer (unless running in disconnected mode), but may neither be read from nor written to. 

This role may occur on one or both nodes.

Unknown

The resource’s role is currently unknown. 

The local resource role never has this status. 

It is only displayed for the peer’s resource role, and only in disconnected mode

drbdadm dstate <resource>

Get the Disk State

The disk state may be one of the following:

Diskless

No local block device has been assigned to the DRBD driver. This may mean that the resource has never attached to its backing device, that it has been manually detached using drbdadm detach, or that it automatically detached after a lower-level I/O error.

Attaching

Transient state while reading metadata.

Detaching

Transient state while detaching and waiting for ongoing I/O operations to complete.

Failed

Transient state following an I/O failure report by the local block device. Next state: Diskless.

Negotiating

Transient state when an Attach is carried out on an already-Connected DRBD device.

Inconsistent

The data is inconsistent. This status occurs immediately upon creation of a new resource, on both nodes (before the initial full sync). Also, this status is found in one node (the synchronization target) during synchronization.

Outdated

Resource data is consistent, but outdated.

DUnknown

This state is used for the peer disk if no network connection is available.

Consistent

Consistent data of a node without connection. When the connection is established, it is decided whether the data is UpToDate or Outdated.

UpToDate

Consistent, up-to-date state of the data.
This is the normal state.

Oracle Locks; Locks in Oracle

V$LOCKED_OBJECT

SELECT LO.session_id, LO.oracle_username, LO.os_user_name, 

       OBJ.object_name , OBJ.owner
  FROM V$LOCKED_OBJECT LO, 
       DBA_OBJECTS OBJ
WHERE LO.object_id = OBJ.object_id;

Gemini version

SELECT 

    l.session_id AS sid,

    s.serial#,

    s.username,

    s.osuser,

    s.machine,

    s.program,

    o.object_name,

    l.locked_mode

FROM V$LOCKED_OBJECT l

JOIN DBA_OBJECTS o ON l.object_id = o.object_id

JOIN V$SESSION s ON l.session_id = s.sid;

V$LOCKED_OBJECT + V$SESSION

SET LINESIZE 300

COL schemaname FOR A30

COL machine FOR A20

COL program FOR A20
COL module FOR A20

COL BLOCKING_SESSION FOR 999999

COL OS_USER_NAME FOR A20 

COL OBJECT_NAME FOR A30

COL kill_session for A60

SELECT DISTINCT

       SESS.sid, SESS.serial#, 

       SESS.schemaname, SESS.machine, 

       SESS.program, SESS.module, 

       SESS.blocking_session, LO.os_user_name, 

       OBJ.object_name , OBJ.owner, 

       SESS.sql_id,

'ALTER SYSTEM KILL SESSION ''' || SESS.sid || ',' || SESS.serial# ||''' IMMEDIATE;' kill_session

  FROM V$LOCKED_OBJECT LO, 

       DBA_OBJECTS OBJ, 

       V$SESSION SESS

WHERE LO.object_id = OBJ.object_id

  AND LO.session_id = SESS.sid;

V$LOCK

SELECT A.sid "blocking session", 

       B.sid "blocked session"  , 

       DECODE (A.block,1, A.sid ||' is Blocking '||B.sid,'Not Blocking'),  

       A.type "Lock Type"

FROM V$LOCK A, 

     V$LOCK B 

WHERE A.sid != B.sid 

  AND A.id1 = B.id1  

  AND A.id2 = B.id2 

  AND A.block = 1

  AND B.request > 0;

V$SESSION - lock time

SET LINESIZE 200

COL USERNAME FOR A20

COL MACHINE FOR A20

COL PROGRAM FOR A40

SELECT 

  sid, serial#, username, machine, program,

  blocking_session "blocking_session",

  sid "blocked_session",

  serial# "blocked_serial#", 

  seconds_in_wait/60 "wait_time(minutes)"

FROM V$SESSION

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;

V$SESSION - lock time + lock info

SELECT 

  ROUND(V_SESS_LOCKED.seconds_in_wait/60) "WAIT_TIME (MIN)",

  V_SESS_LOCKED.sid       locked_sid, 

  V_SESS_LOCKED.serial#   locked_serial, 

  V_SESS_LOCKED.username  locked_username, 

  V_SESS_LOCKED.machine   locked_machine, 

  V_SESS_LOCKED.program   locked_program,

  V_SESS_LOCKED.blocking_session blocking_session,

  V_SESS_LOCKED.sql_id    locked_sql,

  V_SESS_LOCKING.sid      locking_session,

  V_SESS_LOCKING.serial#  locking_serial,

  V_SESS_LOCKING.username locking_username,

  V_SESS_LOCKING.machine  locking_machine,

  V_SESS_LOCKING.program  locking_program,

  V_SESS_LOCKING.sql_id   locking_sql

FROM V$SESSION V_SESS_LOCKED,

     V$SESSION V_SESS_LOCKING

WHERE V_SESS_LOCKED.blocking_session IS NOT NULL

  AND V_SESS_LOCKED.blocking_session = V_SESS_LOCKING.sid

ORDER BY ROUND(V_SESS_LOCKED.seconds_in_wait/60) DESC;

Other useful SQLs

Kill Sessions
ALTER SYSTEM DISCONNECT SESSION '111,22222' IMMEDIATE;

ALTER SYSTEM KILL SESSION '111,22222' IMMEDIATE; 
SELECT paqddr FROM V$SESSION WHERE sid = '111';
SELECT spid FROM V$PROCESS WHERE addr = '0000000074C78SD1';

V$SESSION_LONGOPS

SELECT * FROM V$SESSION_LONGOPS 

 WHERE start_time > SYSDATE-4/24 

   AND elapsed_seconds > 1 

 ORDER BY start_time DESC;

Running SQLs

SELECT * FROM V$SQLAREA 

 WHERE sql_id IN 

    (SELECT sql_id FROM V$SESSION 

      WHERE status = 'ACTIVE' 

        AND sql_id IS NOT NULL 

        AND user# > 0) 

  AND sql_text NOT LIKE 'SELECT * FROM V$SQLAREA%'

ORA-01873: the leading precision of the interval is too small

SELECT * FROM USER_JOBS
ORA-01873: the leading precision of the interval is too small

SELECT * FROM DBA_SCHEDULER_JOBS
ORA-01882: timezone region not found

After a removal of the suspected job, and recreating it again the issue was fixed.

"suspected job" - there was a lock in database, and the job session was terminated with ALTER SYSTEM KILL SESSION command.

BEGIN

 DBMS_JOB.remove(23);
 commit;
END;
/
SELECT * FROM USER_JOBS

Now is working!

Oracle Patches. URL, Patch types

Oracle Patch types

Interim patches - contain a single bug fix or a collection of bug fixes provided as required

Diagnostic patches - intended to help diagnose or verify a fix

Bundle Patch Updates (BPUs) - a cumulative collection of fixes for a specific product or component

Patch Set Updates (PSUs) - a cumulative collection of high impact, low risk, and proven fixes for a specific product or component and Security Patch Updates

Security Patch Updates (SPU) - a cumulative collection of security bug fixes.  SPUs were formerly known as Critical Patch Updates (CPU).

System Patch - contains several sub-patches in a format that can be used by OPatchAuto.

Merge Label Request (MLR) - a merge of two or more fixes. MLR creation requires a label for the new set of merged code and a Patch Set Exception.

What to install?

RU - Release Update

Release Update

RU - Release Update

Oracle recommends that you keep your database software current by applying Release Updates (RUs).

RUs are listed in Primary Note for Database Proactive Patch Program (Doc ID 888.1)

Release Updates (RUs) are release quarterly: Third Tuesday of January, April, July and October. 

Each RU will be given a maximum of six Monthly Recommended Patches (MRPs), released monthly.

RUs are highly tested bundles of critical fixes which enable you to avoid known issues. 

They usually contain the following type of fixes: security, regression (bug), optimizer, and functional.

Monthly Recommended Patches (MRP)

Starting with update 19.17, Oracle is providing MRPs for Linux x86-64 to provide proactive patching between Release Updates.

Which patches to install for Oracle 19?

A. Oracle Database 19c Important Recommended One-off Patches (Doc ID 555.1)

B. Oracle Database 19c Proactive Patch Information (Doc ID 2521164.1)

KB188772 - Oracle Database 19c and Oracle AI Database 26ai Important Recommended One-off Patches

KB111276 - Oracle Database 19c Proactive Patch Information

This documents lists the latest patches for Oracle 19
DB RU - Database Release Update

19.29 DB RU - 21-Oct-2025 Patch 38291812

19.28 DB RU - 15-Jul-2025 Patch 37960098
19.27 DB RU - 15-Apr-2025 Patch 37642901
...

...
19.22 DB RU - Patch 35943157

19.21 DB RU - Patch 35643107 

19.20 DB RU - Patch 35320081

19.19 DB RU - Patch 35042068

B. Primary Note for Database Proactive Patch Program (Doc ID 888.1)

This documents lists patches for All oracle versions.
for example:

Oracle Database 19c Proactive Patch Information - KB111276

Applying a new patch:

Example: Apply patch 38291812

unzip p38291812_<version>_<platform>.zip

cd 38291812

Set PATH

PATH=$PATH:$ORACLE_HOME/OPatch

which opatch

/software/oracle/19c/OPatch/opatch

oracle@myhost:~/patch_19-29/p6880880/OPatch>% opatch version

OPatch Version: 12.2.0.1.48

OPatch succeeded.

Check for conflict with the patch being installed

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

See opatch apply output report without applying the patch
opatch apply -report

opatch napply -report  (ONLY for a bundle patch! )

Apply the Patch

opatch apply

Run datapatch utility. 

It executes any SQL scripts associated with the patches installed in the Oracle Home.

$ORACLE_HOME/OPatch/datapatch -verbose

Fix Permissions

Database release update patch installation involves relinking of libraries and executables to be updated. 

This can sometimes result in permissions changing and needing to be updated. 

Execute the following as root:

chown root $ORACLE_HOME/bin/extjob

chmod 4750 $ORACLE_HOME/bin/extjob

 

====================

By Example

====================

oracle@myhost:~/patch_19-29/38291812/38291812>% opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.41

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /software/oracle/19c

Central Inventory : /software/oracle/oraInventory

   from           : /software/oracle/19c/oraInst.loc

OPatch version    : 12.2.0.1.41

OUI version       : 12.2.0.7.0

Log file location : /software/oracle/19c/cfgtoollogs/opatch/opatch2025-12-29_15-12-29PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

oracle@myhost:~/patch_19-29/38291812/38291812>%

>%opatch lsinventory

Oracle Database 19c                                                  19.0.0.0.0

There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch  35943157     : applied on Sun Mar 17 11:24:52 UTC 2024

Unique Patch ID:  25527362

Patch description:  "Database Release Update : 19.22.0.0.240116 (35943157)"

   Created on 13 Jan 2024, 05:42:55 hrs UTC

Patch  29585399     : applied on Thu Apr 18 07:21:33 UTC 2019

Unique Patch ID:  22840393

Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"

After patch Database Release Update : 19.29:

>%opatch lsinventory

Patch  38291812     : applied on Mon Dec 29 16:24:21 UTC 2025

Unique Patch ID:  28130960

Patch description:  "Database Release Update : 19.29.0.0.251021 (38291812)"

   Created on 6 Oct 2025, 17:36:33 hrs UTC

   Bugs fixed:

Remove the patch backups, to save space

cd /software/oracle/19c/.patch_storage

du -sh *

2.9G    38291812_Oct_6_2025_17_36_33

rm -r 38291812_Oct_6_2025_17_36_33

>% datapatch -verbose

SQL Patching tool version 19.29.0.0.0 Production on Mon Dec 29 20:39:14 2025

Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /software/oracle/cfgtoollogs/sqlpatch/sqlpatch_49907_2025_12_29_20_39_14/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

  No interim patches found

Current state of release update SQL patches:

  Binary registry:

    19.29.0.0.0 Release_Update 251002005342: Installed

  SQL registry:

    Applied 19.10.2.0.0 Release_Update_Revision 210515144132 successfully on 25-OCT-21 03.26.11.413859 PM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

  No interim patches need to be rolled back

  Patch 32819074 (Database Release Update Revision : 19.10.2.0.0 (32819074)):

    Rollback from 19.10.2.0.0 Release_Update_Revision 210515144132 to 19.10.0.0.0 Release_Update 210108185017

  Patch 38291812 (Database Release Update : 19.29.0.0.251021 (38291812)):

    Apply from 19.10.0.0.0 Release_Update 210108185017 to 19.29.0.0.0 Release_Update 251002005342

  No interim patches need to be applied

Installing patches...

Patch installation complete.  Total patches installed: 2

Validating logfiles...done

Patch 32819074 rollback: SUCCESS

  logfile: /software/oracle/cfgtoollogs/sqlpatch/32819074/24234915/32819074_rollback_IGT_2025Dec29_20_40_16.log (no errors)

Patch 38291812 apply: SUCCESS

  logfile: /software/oracle/cfgtoollogs/sqlpatch/38291812/28130960/38291812_apply_IGT_2025Dec29_20_40_29.log (no errors)

SQL Patching tool complete on Mon Dec 29 20:44:25 2025

oracle@myhost:/backup/ora_exp/patch_19-29/38291812>%

ORA-12034: materialized view log on "SCHEMA"."MY_SERVICE" younger than last refresh

Issue: Refresh Materialized View is giving this error:

ERROR at line 1:

ORA-12034: materialized view log on "SCHEMA"."MY_MVIEW" younger than last refresh

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851

ORA-06512: at "SYS.DBMS_IREFRESH", line 683

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at "SCHEMA.REFRESH_PKG", line 19

ORA-06512: at line 2

Solution:

Complete refresh and then Fast Refresh the Materialized View

Example

BEGIN

 DBMS_MVIEW.refresh('MY_MVIEW' ,'C');

END;

/

BEGIN

 DBMS_MVIEW.refresh('MY_MVIEW' ,'F');

END;

/

Code Generator

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

--Generate DBMS_REFRESH.refresh script

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

COLUMN sql_cmd FORMAT A120 WORD_WRAP

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET NEWPAGE NONE

SET VERIFY OFF

SET ECHO OFF

SET SHOW OFF

SET TERMOUT OFF

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

--Generate DBMS_REFRESH.refresh script

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

COLUMN sql_cmd FORMAT A120 WORD_WRAP

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET NEWPAGE NONE

SET ECHO OFF

SET TERMOUT OFF

spool manual_refresh_mviews.sql 

PROMPT SET FEEDBACK ON

SELECT 'spool refresh_'||name||'.log;'||CHR(10)||

'PROMPT Start DBMS_MVIEW.refresh() of '||name||CHR(10)||

'BEGIN'||CHR(10)||

' DBMS_MVIEW.refresh('''||name||''' ,''C'');' ||CHR(10)||

' DBMS_MVIEW.refresh('''||name||''' ,''F'');' ||CHR(10)||

' COMMIT;'||CHR(10)||

'END;'||CHR(10)||

'/'||CHR(10)||

'spool off;' as sql_cmd

FROM DBA_REFRESH_CHILDREN

WHERE rname = 'MASTER_GROUP'

order by name;

PROMPT spool off

spool off;

@manual_refresh_mviews.sql