Oracle init file, spfile, and pfile

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

General

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

The short names are init.ora, spfile.ora, pfile.ora, but the actual name are init<db_name>.ora, spfile<db_name>.ora, pfile<db_name>.ora

spfile.ora

AKA Server Parameter File

It is a binary file, that stores all the configuration/initialization parameters. 

It is used by oracle upon startup.

To start working with spfile:
- Create a text file init.ora
- using the CREATE SPFILE statement from init.ora - create the spfile.

pfile.ora

A text file, which can be generated from spfile

init.ora

A text file, normally. 

In older version - it was the actual configuration file. 

Nowadays, it can be reference to the spfile.

In STARTUP

By default, if you do not specify PFILE in your STARTUP command, Oracle will use a SPFILE from the default location. 

If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.

In RAC

A single copy of the spfile can be used by all instances. 

Even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.

For Example:
*.OPEN_CURSORS=500 # For database-wide setting

RACDB1.OPEN_CURSORS=1000 # For RACDB1 instance

==========

init.ora

==========

Default location

Linux : $ORACLE_HOME/dbs/ 

Windows: %ORACLE_HOME%\database\

==========

spfile

==========

It is a binary file. and it can be edited with ALTER SYSTEM SET commands.

To override default location, it should be referenced from init.ora.

For example:
initigt.ora:
spfile='/software/oracle/admin/igt/pfile/spfileigt.ora'

==========

See configuration

==========

SQL> show parameters db_name
NAME             VALUE
---------------- -------------------------------------
db_name          igt

SQL> show parameter spfile
NAME             VALUE
---------------- -------------------------------------
spfile          /software/oracle/122/dbs/spfileigt.ora

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

List Parameters

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

List All Supported Parameters

SET LINESIZE 100

COL NAME FORMAT A40

COL VALUE FORMAT A40

SET LINESIZE 100

SELECT name, value

FROM V$PARAMETER

ORDER BY 1;

List All Modified Parameters

SET LINESIZE 100

COL NAME FORMAT A40

COL VALUE FORMAT A40

SELECT name, value

FROM V$PARAMETER

WHERE isdefault = 'FALSE'

ORDER BY 1;

List All Undocumented Underscore Parameters

SET PAGESIZE 1000

SET LINESIZE 140

COL KSPPINM FORMAT A40

COL KSPPDESC FORMAT A80

SELECT ksppinm, ksppdesc

FROM X$KSPPI

WHERE SUBSTR(ksppinm,1,1) = '_'

ORDER BY ksppinm;

List Obsolete Parameters

COL NAME FORMAT A40

SELECT *

FROM V$OBSOLETE_PARAMETER

ORDER BY 2;

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

General

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

Creating SPFILE

From init.ora

CREATE SPFILE FROM PFILE='/software/oracle/122/dbs/initigt.ora';

CREATE SPFILE '/software/oracle/122/dbs/spfileigt.ora' FROM PFILE='/software/oracle/122/dbs/initigt.ora';

From memory

CREATE SPFILE '/software/oracle/122/dbs/spfileigt.ora' FROM MEMORY;

Creating PFILE

CREATE PFILE '/software/oracle/122/dbs/pfileigt.ora' FROM SPFILE='/software/oracle/122/dbs/spfileigt.ora';

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

Changing Parameters

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

ALTER SESSION SET parameter_name = value;

ALTER SYSTEM  SET parameter_name = value [DEFERRED];

ALTER SYSTEM  SET parameter_name = value SCOPE = SPFILE;

ALTER SYSTEM  SET parameter_name = value SCOPE = MEMORY;

ALTER SYSTEM  SET parameter_name = value SCOPE = BOTH;

DEFERRED - Only for Dynamic parameters. The change affects only future sessions.

MEMORY -   Only for Dynamic parameters.

SPFILE -   Change only spfile. Need a restart for the change to take effect. This is the way to change static parameters.

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

Remove parameter from SPFILE

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

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='*';

The parameter is removed from a server parameter file, causing the default value to take effect the next time you start an instance of the database.

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

Oracle 9i

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

. /etc/sh/orash/920env.sh

# Removing all oracle paths

export PATH=`echo $PATH | tr -s ":" "\n" | grep -v "/software/oracle/[0-9]*/bin" | tr "\n" ":"`

# Adding correct oracle path

export PATH=/software/oracle/920/bin:$PATH

export LD_LIBRARY_PATH=/software/oracle/920/lib:/usr/lib

export PROJECT_SID=gin

export DEFAULT_SID=gin

export ORACLE_SID=gin

export ORA_VER=920

export ORACLE_HOME=/software/oracle/920

export ORA_NLS33=/software/oracle/920/ocommon/nls/admin/data

export TNS_ADMIN=/software/oracle/111/network/admin

less /software/oracle/920/dbs/initgin.ora

SPFILE=/software/oracle/admin/gin/pfile/spfilegin.ora

sqlplus /nolog

connect / as sysdba

STARTUP;

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

Oracle 11

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

. /etc/sh/orash/111env.sh

# Removing all oracle paths

export PATH=`echo $PATH | tr -s ":" "\n" | grep -v "/software/oracle/[0-9]*/bin" | tr "\n" ":"`

# Adding only relevant oracle paths

export PATH=/software/oracle/111/bin:$PATH

export LD_LIBRARY_PATH=/software/oracle/111/lib:/usr/lib:/software/oracle/111/lib32

export PROJECT_SID=igt

export DEFAULT_SID=igt

export ORACLE_SID=igt

export ORA_VER=1110

export ORACLE_HOME=/software/oracle/111

export ORA_NLS33=/software/oracle/111/ocommon/nls/admin/data

export TNS_ADMIN=/software/oracle/111/network/admin

less /software/oracle/111/dbs/initigt.ora

SPFILE=/software/oracle/admin/igt/pfile/spfileigt.ora

sqlplus /nolog

connect / as sysdba

STARTUP;

sample init.ora 

db_name=DEFAULT

db_files = 80

db_file_multiblock_read_count = 8

db_block_buffers = 100     

shared_pool_size = 3500000

log_checkpoint_interval = 10000

processes = 50 

parallel_max_servers = 5 

log_buffer = 32768

max_dump_file_size = 10240

global_names = TRUE

control_files = (ora_control1, ora_control2)

How to apply patch on Oracle Golden Gate by Example - p37777884_1927000OGGRU

How to apply Oracle Golden Gate patch by Example

Need apply latest patch for Oracle Golden Gate.

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

Theory

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

Which documents to check:

Oracle Critical Patch Update Advisory - April 2023

Map of CVE to Advisory/Alert

[ Doc ID 1645495.1 ] "Primary Note for Oracle GoldenGate Core Product Patch Sets"

Per Primary Note for Oracle GoldenGate Core Product Patch Sets (Doc ID 1645495.1)

Latest patch is:

Patch 35326271: Oracle GoldenGate 19.1.0.0.230422 for Oracle 12c - Upgrades GG to 19.1.0.0.230422

   Oracle GoldenGate 19.1.0.0.230418/19.1.0.0.230422 Patch Set Availability (Doc ID 2947665.1)

   09-MAY-2023

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

Check and sync drdb

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

In case server is replicated with drdb, check step 2, else go to step 3

as root

drbdadm status

More drbd info here How to: Resolve DRBD split-brain recovery manually

 

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

Backup

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

put below zip files in resources under
/backup/ora_exp/gg_patches_backup/

p6880880_230000_Linux-x86-64_12.2.0.1.47.zip

p37777884_1927000OGGRU_Linux-x86-64.zip

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

--Create Directories

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

su - oracle

mkdir -p /backup/ora_exp/gg_patches_backup/

mkdir -p /backup/ora_exp/gg_patches_backup/old_OPatch

mkdir -p /backup/ora_exp/gg_patches_backup/old_GGHOME

mkdir -p /backup/ora_exp/gg_patches_backup/patches

mkdir -p /backup/ora_exp/gg_patches_backup/patches/OPatch

mkdir -p /backup/ora_exp/gg_patches_backup/patches/37777884_base

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

--Update OPatch

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

cp -rp  /software/ogg/191/OPatch/* /backup/ora_exp/gg_patches_backup/old_OPatch/

rm -rf /software/ogg/191/OPatch/*

mv /backup/ora_exp/gg_patches_backup/p6880880_230000_Linux-x86-64_12.2.0.1.47.zip /backup/ora_exp/gg_patches_backup/patches/OPatch/

cd /backup/ora_exp/gg_patches_backup/patches/OPatch/

unzip p6880880_230000_Linux-x86-64_12.2.0.1.47.zip

cp -p /backup/ora_exp/gg_patches_backup/patches/OPatch/* /software/ogg/191/OPatch/

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

--Backup $GG_HOME/

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

cp -rp /software/ogg/191/ /backup/ora_exp/gg_patches_backup/old_GGHOME/

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

Apply OPatch p6880880_121010_Linux-x86-64.zip

==================
Because of compatibility issues, need to upgrade Golden Gate Opath to latest version.

Download p6880880_121010_Linux-x86-64.zip from here:
https://updates.oracle.com/download/6880880.html

cd /software/ogg/191/OPatch

mkdir OPatch_12.2.0.1.17

mv * OPatch_12.2.0.1.17/

cp /software/oracle/oracle/patches/p6880880_121010_Linux-x86-64.zip /software/ogg/191/OPatch

unzip p6880880_121010_Linux-x86-64.zip

mv -f OPatch/* .

rm -r OPatch

rm p6880880_121010_Linux-x86-64.zip

--test: 

./opatch version

expected result: OPatch Version: 12.2.0.1.37

--list existing patches

./opatch lsinventory

./opatch lspatches

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

How to find latest Patch

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

Parent Document: Latest Oracle GoldenGate For Oracle Database & Oracle Database Patch Recommendations (Doc ID 2193391.1) List of available Patches for Golden Gate: Primary Note for Oracle GoldenGate Core Product Patch Sets (Doc ID 1645495.1) Actual Patch Download, Example: Oracle GoldenGate 19.27.0.0.250415 Patch Set Availability (Doc ID 3081098.1) Naming convention Oracle DB Patches have naming convention: p38151250_1928000DBRU_Linux-x86-64.zip

Golden gate Patches have naming convention: p37777884_1927000OGGRU_Linux-x86-64.zip

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

Apply Patch

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

Follow Oracle document

https://docs.oracle.com/en/middleware/goldengate/core/19.1/installing/install-installing-patches-classic-architecture.html

In this Example: Release 19c (19.0.0.230418) - April 2023

p35326271_19100230422_Linux-x86-64.zip - 

NOTE - Oracle Golden Gate has it own jdk, OPatch, *.so files, etc. 

       So path should be done there.

       - GoldenGate_Installation_Path = /software/ogg/191

As oracle user prepare Opatch

Point ORACLE_HOME to OGG_HOME

GG_HOME=/software/ogg/191

export ORACLE_HOME=$GG_HOME

PATH=${ORACLE_HOME}/OPatch:$PATH

which opatch

should be: /software/ogg/191/OPatch/opatch

Check current inventory

opatch lsinventory

  

Check compatibility

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

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

oracle@server:~/patches/35326271/35326271>% opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.37

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

PREREQ session

Oracle Home       : /software/ogg/191

Central Inventory : /software/ogg/installers

   from           : /software/ogg/191/oraInst.loc

OPatch version    : 12.2.0.1.37

OUI version       : 12.2.0.4.0

Log file location : /software/ogg/191/cfgtoollogs/opatch/opatch2023-05-24_07-50-24AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

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

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

Stop GG processes Before patching Oracle GoldenGate

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

On a pcs cluster, before stopping GG MGR, 
Option A - Stop GG resource group

Option B - Put cluster in maintenance mode!

Option A - disable ora_gg_rg
pcs status

pcs resource cleanup ora_gg_rg 

pcs resource disable ora_gg_rg

pcs status

Expected result:

* Resource Group: ora_gg_rg (disabled):

  * ora_gg_fs (ocf::heartbeat:Filesystem):  Stopped (disabled)

  * ora_gg_ap (lsb:gginit_ctl):             Stopped (disabled)

Now, MGR is stopped by cluster.

GGSCI (ro8-ogg-ora1) 1> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

>>>>>>>>>>> Do maintenance now!

ogg - is now secondary on both sites!!

root@ro8-ogg-ora1:~>%drbdadm status

ogg role:Secondary

  disk:UpToDate open:no

  ro8-ogg-ora2 role:Secondary

    peer-disk:UpToDate

root@ro8-ogg-ora2:~>%drbdadm status

ogg role:Secondary

  disk:UpToDate open:no

  ro8-ogg-ora1 role:Secondary

    peer-disk:UpToDate

After the maintenance was completed:

pcs resource enable ora_gg_rg 

pcs status

drbdadm status

The node where oracle is running,  should be the Primary node to ogg as well.

Expected result:

* Resource Group: ora_gg_rg:

  * ora_gg_fs (ocf::heartbeat:Filesystem): Started ro8-ogg-ora1

  * ora_gg_ap (lsb:gginit_ctl):            Started ro8-ogg-ora1 (Monitoring)

After some time:

* Resource Group: ora_gg_rg:

  * ora_gg_fs (ocf::heartbeat:Filesystem): Started ro8-ogg-ora1

  * ora_gg_ap (lsb:gginit_ctl):            Started ro8-ogg-ora1 

Option B - Use maintenance mode
pcs property set maintenance-mode=true

pcs status

Cluster Summary:

  * Stack: corosync (Pacemaker is running)

  * Current DC: ro8-ogg-ora1 (version 2.1.7-5.3.el8_10-0f7f88312) - partition with quorum

  * Last updated: Mon Nov 17 21:01:20 2025 on ro8-ogg-ora1

  * Last change:  Mon Nov 17 21:01:13 2025 by root via root on ro8-ogg-ora1

  * 2 nodes configured

  * 8 resource instances configured

              *** Resource management is DISABLED ***

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

Node List:

  * Online: [ ro8-ogg-ora1 ro8-ogg-ora2 ]

Full List of Resources:

  * Resource Group: ora_igt_rg (maintenance):

    * db1_igt_fs    (ocf::heartbeat:Filesystem):   Started ro8-ogg-ora1 (maintenance)

    * online_igt_fs (ocf::heartbeat:Filesystem):   Started ro8-ogg-ora1 (maintenance)

    * exp_igt_fs    (ocf::heartbeat:Filesystem):   Started ro8-ogg-ora1 (maintenance)

    * db2_igt_fs    (ocf::heartbeat:Filesystem):   Started ro8-ogg-ora1 (maintenance)

    * ora_igt_vip   (ocf::heartbeat:IPaddr2):      Started ro8-ogg-ora1 (maintenance)

    * ora_igt_ap    (lsb:dbora_ctl):               Started ro8-ogg-ora1 (maintenance)

  * Resource Group: ora_gg_rg (maintenance):

    * ora_gg_fs (ocf::heartbeat:Filesystem):       Started ro8-ogg-ora1 (maintenance)

    * ora_gg_ap (lsb:gginit_ctl):                  Started ro8-ogg-ora1 (maintenance)

Daemon Status:

  corosync: active/enabled

  pacemaker: active/enabled

  pcsd: active/enabled

Stop MGR

cd /software/ogg/191

./ggsci

STOP *

STOP MGR!

INFO ALL

All should be stopped

EXIT

Apply patch to Oracle GoldenGate

cd patch_top_dir/patch_number_dir 

opatch apply

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

oracle@server:~/patches/35326271/35326271>% opatch apply

Oracle Interim Patch Installer version 12.2.0.1.37

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

Oracle Home       : /software/ogg/191

Central Inventory : /software/ogg/installers

   from           : /software/ogg/191/oraInst.loc

OPatch version    : 12.2.0.1.37

OUI version       : 12.2.0.4.0

Log file location : /software/ogg/191/cfgtoollogs/opatch/opatch2023-05-24_07-52-52AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   35326271

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/software/ogg/191')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '35326271' to OH '/software/ogg/191'

Patching component oracle.oggcore.ora12c, 19.1.0.0.0...

Patch 35326271 successfully applied.

Log file location: /software/ogg/191/cfgtoollogs/opatch/opatch2023-05-24_07-52-52AM_1.log

OPatch succeeded.

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

Check inventory

opatch lsinventory

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

oracle@server:~/patches/35326271/35326271>% opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.37

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

Oracle Home       : /software/ogg/191

Central Inventory : /software/ogg/installers

   from           : /software/ogg/191/oraInst.loc

OPatch version    : 12.2.0.1.37

OUI version       : 12.2.0.4.0

Log file location : /software/ogg/191/cfgtoollogs/opatch/opatch2023-05-24_07-54-44AM_1.log

Lsinventory Output file location : /software/ogg/191/cfgtoollogs/opatch/lsinv/lsinventory2023-05-24_07-54-44AM.txt

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

Local Machine Information::

Hostname: server

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle GoldenGate Core                                              19.1.0.0.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  35326271     : applied on Wed May 24 07:53:57 GMT 2023

Unique Patch ID:  25225415

Created on 28 Apr 2023, 00:46:05 hrs PST8PDT

Bugs fixed:

16564626, 16814471, 18294101, 20513471, 24611231, 25475406, 26078270

26752745, 26812342, 27313577, 27844596, 28045394, 28480385, 28725556

28796786, 29156819, 29166143, 29209870, 29225584, 29227058, 29271448

29299615, 29314744, 29346116, 29358314, 29376018, 29400201, 29488053

29500119, 29557298, 29585511, 29706933, 29717473, 29742654, 29744500

29751899, 29768950, 29774881, 29805211, 29809446, 29814575, 29856640

29869207, 29902799, 29909549, 29910461, 29915014, 29916615, 29920698

29926335, 30006961, 30017628, 30024507, 30030081, 30030259, 30032166

30033430, 30033958, 30043836, 30048129, 30076679, 30080233, 30102653

30102893, 30150540, 30162025, 30207560, 30233950, 30246124, 30265723

30267467, 30275490, 30283060, 30286459, 30301634, 30310568, 30310604

30321894, 30333176, 30340476, 30342450, 30342778, 30358775, 30372831

30404307, 30404574, 30427030, 30441555, 30446367, 30446832, 30453041

30453885, 30458313, 30468226, 30473280, 30473302, 30473323, 30475749

30484169, 30484525, 30485152, 30493260, 30494573, 30494613, 30494732

30494822, 30503529, 30507570, 30511946, 30517837, 30523539, 30534012

30539518, 30540184, 30548038, 30572581, 30577266, 30579002, 30582001

30585160, 30585161, 30587460, 30594864, 30598252, 30600424, 30600473

30601512, 30601520, 30609604, 30612519, 30614202, 30618355, 30622854

30622974, 30623521, 30633479, 30634544, 30634903, 30635206, 30637766

30638977, 30640908, 30641360, 30650848, 30651192, 30651405, 30651501

30651691, 30657209, 30662222, 30663342, 30663522, 30665877, 30669929

30680081, 30680258, 30680613, 30680756, 30680818, 30681006, 30682568

30685842, 30686209, 30688349, 30693366, 30693987, 30696013, 30700666

30703607, 30713018, 30717771, 30719522, 30719897, 30722842, 30724310

30729495, 30729766, 30731018, 30734990, 30737450, 30738970, 30739999

30753086, 30763551, 30778094, 30782649, 30786365, 30791492, 30796546

30797198, 30797378, 30806586, 30811831, 30812800, 30815503, 30818230

30818690, 30818727, 30823108, 30823432, 30826774, 30827735, 30827972

30832255, 30838154, 30838254, 30845653, 30846419, 30846594, 30851346

30856970, 30861257, 30861743, 30864484, 30865197, 30865462, 30871350

30873300, 30875325, 30877853, 30880051, 30881232, 30881572, 30883131

30884891, 30889577, 30890481, 30892202, 30896679, 30897196, 30897713

30902327, 30903305, 30903771, 30905623, 30905753, 30905759, 30906693

30911601, 30911868, 30913807, 30917522, 30920035, 30926412, 30932343

30938120, 30944394, 30945407, 30947828, 30950624, 30952137, 30955351

30955775, 30962699, 30964666, 30966524, 30966604, 30966810, 30972344

30973756, 30973776, 30975972, 30979167, 30987480, 30988993, 30990451

30990596, 30993340, 30994687, 30995568, 31000610, 31003735, 31010508

31013450, 31015014, 31027089, 31027128, 31027137, 31039410, 31040380

31040907, 31045117, 31056957, 31062378, 31066223, 31071409, 31071657

31072446, 31091288, 31097457, 31098304, 31099592, 31107854, 31113468

31114345, 31118592, 31122004, 31123944, 31129752, 31131773, 31139595

31139615, 31142667, 31143243, 31157419, 31158148, 31165352, 31168595

31170155, 31172278, 31175237, 31175404, 31175842, 31176144, 31177621

31178248, 31188252, 31189911, 31190369, 31192285, 31198252, 31198464

31202227, 31206379, 31208291, 31208886, 31209070, 31213929, 31215503

31219406, 31222222, 31225570, 31226166, 31227988, 31228480, 31232100

31240314, 31256737, 31271859, 31278701, 31303118, 31305105, 31311503

31316835, 31318412, 31319298, 31323568, 31323909, 31323928, 31326414

31326425, 31329767, 31336850, 31346319, 31346587, 31349966, 31350053

31350725, 31367261, 31367923, 31373499, 31373504, 31374076, 31381472

31381479, 31386231, 31401692, 31410995, 31418833, 31425169, 31425300

31427347, 31431124, 31433207, 31440697, 31441320, 31441364, 31445469

31449007, 31451676, 31454801, 31456379, 31456620, 31458308, 31460112

31461836, 31476229, 31480365, 31482479, 31484993, 31487146, 31497291

31507116, 31507433, 31507455, 31507736, 31516459, 31517762, 31518720

31521735, 31526548, 31528537, 31536652, 31541257, 31547747, 31548760

31551177, 31556722, 31560266, 31561650, 31561710, 31563090, 31568477

31575532, 31578150, 31581443, 31581999, 31582715, 31587315, 31587607

31591273, 31596246, 31596830, 31601424, 31601988, 31606188, 31606636

31607165, 31607315, 31607593, 31616216, 31625576, 31626902, 31627080

31627129, 31637694, 31644179, 31645696, 31647619, 31650786, 31653611

31656230, 31659809, 31668277, 31669209, 31680465, 31682817, 31689380

31695862, 31703323, 31704543, 31709315, 31709430, 31711659, 31729354

31730873, 31734485, 31738301, 31738375, 31745011, 31758036, 31760611

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

OPatch succeeded.

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

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

Start GG MGR After patching Oracle GoldenGate

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

./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.230422 

cd /software/ogg/191

./ggsci

INFO ALL
START MGR

START *

INFO ALL

On pcs cluster, after starting MGR, MUST put cluster out of maintenance mode.

do:

pcs property set maintenance-mode=false

pcs status

root@ro8-ogg-ora1:~>% pcs property set maintenance-mode=false

root@ro8-ogg-ora1:~>% pcs status

Cluster name: igt034_cluster

Cluster Summary:

  * Stack: corosync (Pacemaker is running)

  * Current DC: ro8-ogg-ora1 (version 2.1.7-5.3.el8_10-0f7f88312) - partition with quorum

  * Last updated: Mon Nov 17 21:36:57 2025 on ro8-ogg-ora1

  * Last change:  Mon Nov 17 21:36:47 2025 by root via root on ro8-ogg-ora1

  * 2 nodes configured

  * 8 resource instances configured

Node List:

  * Online: [ ro8-ogg-ora1 ro8-ogg-ora2 ]

Full List of Resources:

  * Resource Group: ora_igt_rg:

    * db1_igt_fs        (ocf::heartbeat:Filesystem):     Started ro8-ogg-ora1

    * online_igt_fs     (ocf::heartbeat:Filesystem):     Started ro8-ogg-ora1

    * exp_igt_fs        (ocf::heartbeat:Filesystem):     Started ro8-ogg-ora1

    * db2_igt_fs        (ocf::heartbeat:Filesystem):     Started ro8-ogg-ora1

    * ora_igt_vip       (ocf::heartbeat:IPaddr2):        Started ro8-ogg-ora1

    * ora_igt_ap        (lsb:dbora_ctl):                 Started ro8-ogg-ora1

  * Resource Group: ora_gg_rg:

    * ora_gg_fs (ocf::heartbeat:Filesystem):             Started ro8-ogg-ora1

    * ora_gg_ap (lsb:gginit_ctl):                        Started ro8-ogg-ora1

Daemon Status:

  corosync: active/enabled

  pacemaker: active/enabled

  pcsd: active/enabled

In case of drbd

After maintenance, the drbd would be out of sync.

Need to manually sync the sites.

Follow solution How to: Resolve DRBD split-brain recovery manually

AFTER LOGON ON DATABASE Trigger by Example

--Create Logon events table

 CREATE TABLE LOGON_LOG(
 os_user      VARCHAR2(30),
 user_name    VARCHAR2(30),
 session_user VARCHAR2(30),
 ip_address   VARCHAR2(15),
 program      VARCHAR2(30),
 machine      VARCHAR2(30),
 logon_time   DATE 
) TABLESPACE IGT_TABLE;

--Create Log  table

CREATE TABLE SGA_W_LOG

(

  procedure_name   VARCHAR2(100) not null,

  data             VARCHAR2(1500) not null,

  ts_last_modified DATE not null

)

TABLESPACE IGT_TABLE

--Create Logon Trigger

CREATE OR REPLACE TRIGGER ON_LOGON_MY_USER AFTER LOGON ON DATABASE

-- As sys, grant

--GRANT SELECT ON SYS.V_$MYSTAT TO MY_USER;

--GRANT SELECT ON SYS.V_$SESSION TO MY_USER;

DECLARE

  v_os_user     VARCHAR2(30);

  v_user_name   VARCHAR2(30);

  v_sess_user   VARCHAR2(15);

  v_sid         NUMBER;

  v_program     VARCHAR2(30);

  v_machine     VARCHAR2(30);

  v_ip          VARCHAR2(15);

  v_msg_text    SGA_W_LOG.data%TYPE;

  v_step        VARCHAR2(30);

BEGIN

  v_step := '1';

  SELECT SYS_CONTEXT('userenv','SESSION_USER') INTO v_sess_user FROM DUAL;  

  --Limit by session user

  IF v_sess_user NOT IN ('MY_USER') THEN 

    RETURN;

  END IF;

  v_step := '2';  

  SELECT DISTINCT sid INTO v_sid FROM SYS.V_$MYSTAT;

  

  v_step := '3';

  SELECT osuser, username, SUBSTR(program,1,30), SUBSTR(machine,1,30)

    INTO v_os_user, v_user_name, v_program, v_machine 

    FROM SYS.V_$SESSION WHERE sid = v_sid;

  --Limit by machine and program, skip Jobs

  IF v_machine = 'my_server' AND v_program LIKE '%J0%' THEN

    RETURN;

  END IF;    

  v_step := '4';  

  SELECT SYS_CONTEXT('userenv','IP_ADDRESS') INTO v_ip FROM DUAL;

  

  v_step := '5';

  INSERT INTO LOGON_LOG (os_user, user_name, session_user, ip_address, program, machine, logon_time)

  VALUES (v_os_user, v_user_name, v_sess_user, v_ip, v_program, v_machine, SYSDATE);

  commit;

EXCEPTION

  WHEN OTHERS THEN

    v_msg_text := 'Unexpected Error in step '||v_step||' : '||SQLERRM;

    INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)  

    VALUES ('ON_LOGON_CGW', v_msg_text, SYSDATE);

    commit;

END ON_LOGON_MY_USER;

How to setup correct redo log size

Oracle recommendation is to have log file switch event once every 20 minutes

To see current status

SELECT sequence#, TO_CHAR(first_time,'YYYYMMDD hh24:mi:ss') first_time

  FROM V$LOG_HISTORY 

 WHERE sequence# > (SELECT MAX(sequence#)-21 FROM V$LOG_HISTORY )

  ORDER BY 1 DESC; 

 

sequence first_time

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

1056665  20230522 12:48:31

1056664  20230522 12:47:19

1056663  20230522 12:46:46

1056662  20230522 12:45:52

1056661  20230522 12:45:18

1056660  20230522 12:44:43

1056659  20230522 12:43:07

1056658  20230522 12:42:04

1056657  20230522 12:41:04

1056656  20230522 12:40:04

1056655  20230522 12:39:19

1056654  20230522 12:38:40

1056653  20230522 12:37:52

1056652  20230522 12:37:13

1056651  20230522 12:36:21

1056650  20230522 12:35:36

1056649  20230522 12:34:39

1056648  20230522 12:33:45

1056647  20230522 12:33:31

1056646  20230522 12:32:56

1056645  20230522 12:32:08

1056644  20230522 12:31:55

1056643  20230522 12:31:22

1056642  20230522 12:30:25

1056641  20230522 12:29:50

1056640  20230522 12:28:08

1056639  20230522 12:26:29

1056638  20230522 12:24:50

1056637  20230522 12:23:11

1056636  20230522 12:21:29

1056635  20230522 12:19:50

1056634  20230522 12:18:14

1056633  20230522 12:16:50

1056632  20230522 12:15:52

1056631  20230522 12:15:06

1056630  20230522 12:13:47

1056629  20230522 12:12:08

1056628  20230522 12:10:29

1056627  20230522 12:08:52

1056626  20230522 12:07:46

1056625  20230522 12:06:31

1056624  20230522 12:05:25

1056623  20230522 12:04:31

1056622  20230522 12:03:13

1056621  20230522 12:02:10

1056620  20230522 12:01:13

1056619  20230522 12:00:16

1056618  20230522 11:59:35

1056617  20230522 11:57:56

1056616  20230522 11:56:17

Per these evidences, it is way too frequent!

Need to increase size from current 1024Mb tp 2048Mb

Current status:

COL member FOR A50
COL status FOR A10

SELECT GROUP#, STATUS, MEMBER FROM v$logfile;

    GROUP# STATUS     MEMBER

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

         1            /oracle_db/db1/db_igt/ora_redo_01.rdo

         2            /oracle_db/db1/db_igt/ora_redo_02.rdo

         3            /oracle_db/db1/db_igt/ora_redo_03.rdo

SELECT group#, blocksize, members, status, 

       bytes/1024/1024 As size_mb 

  FROM V$LOG;

group#  blocksize members status  size_mb 

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

1 512   1   CURRENT 512

2 512   1   ACTIVE  512

3 512   1   ACTIVE  512

Increase steps
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04.log') SIZE 1024M;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05.log') SIZE 1024M;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06.log') SIZE 1024M; 

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01.log') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02.log') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03.log') SIZE 1024M REUSE;

Monitor

SELECT * FROM V$LOGFILE;

SELECT group#, blocksize, members, status,

       bytes/1024/1024 As size_mb

  FROM V$LOG;

    GROUP#  BLOCKSIZE    MEMBERS STATUS        SIZE_MB

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

         1        512          1 UNUSED           1024

         2        512          1 UNUSED           1024

         3        512          1 ACTIVE           1024

         4        512          1 ACTIVE           1024

         5        512          1 CURRENT          1024

         6        512          1 UNUSED           1024

Creating tablespace with 16K blocksize

Creating tablespace with 16K blocksize

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

How To Create 16K tablespace

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

Check on current status:

SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;

TABLESPACE_NAME                BLOCK_SIZE

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

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS                              8192

TEMPORARY                            8192

IGT_TABLE                            8192

IGT_INDEX                            8192

WORKAREA                             8192

IGT_TABLE_BIG                       16384

GG_TBS                               8192

Check on space usage

SELECT TABLESPACE_NAME, 

       ROUND(SUM(BYTES)/1024/1024) used_mb, 

       ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

  FROM DBA_DATA_FILES 

GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                   USED_MB     MAX_MB

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

IGT_TABLE                            9200      24000

IGT_INDEX                            3000      20000

WORKAREA                              900       6000

SYSAUX                               1000       6000

GG_TBS                                100          0

UNDOTBS                              9400      12000

SYSTEM                                700       6000

IGT_TABLE_BIG                         100          0

 SELECT FILE_NAME, 

        ROUND(SUM(BYTES)/1024/1024) used_mb, 

        ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

   FROM DBA_DATA_FILES  

   GROUP BY FILE_NAME;

FILE_NAME                                          USED_MB  MAX_MB

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

/oracle_db/db1/db_igt/ora_igt_table_01.dbf            9200   24000

/oracle_db/db1/db_igt/ora_igt_index_01.dbf            3000   20000

/oracle_db/db1/db_igt/ora_sysaux_01.dbf               1000    6000

/oracle_db/db1/db_igt/ora_workarea_01.dbf              900    6000

/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf         100       0

/oracle_db/db1/db_igt/data/ora_gg_tbs_01.dbf           100       0

/oracle_db/db1/db_igt/ora_system_01.dbf                700    6000

/oracle_db/db1/db_igt/ora_undotbs_01.dbf              9400   12000

ALTER SYSTEM SET db_16k_cache_size = 1G SCOPE=BOTH;

SELECT name, value 
  FROM V$PARAMETER 
 WHERE name = 'db_16k_cache_size';

db_16k_cache_size
-----------------
1073741824

CREATE TABLESPACE IGT_TABLE_BIG LOGGING DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf' SIZE 100M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO  BLOCKSIZE 16k;

Use the new tablespace:

impdp user/passw@orainst DIRECTORY=IG_EXP_DIR LOGFILE=imp_user.log DUMPFILE=exp_full_20210729.dmp TABLES=BIG_TABLE REMAP_TABLESPACE=IGT_TABLE:IGT_TABLE_BIG

ALTER TABLE USER.SGA_W_IPN_SUBSCRIBER MOVE TABLESPACE IGT_TABLE_BIG;

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

When to use  tablespace with 16k block

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

As rule of Thumb:

1. Use 8K block size for OLTP applications

2. Use 16K Data Warehouse applications. Optionally use 32K

3. For mixed block sizes, it is possible, provided there is enough SGA to create buffer caches for each block size in the database.

In Details, when to use and not to use 16k blocksize

1. Full Table Scans - no difference between 8k and 16k

2. For Index Range scans - no major difference between 8k and 16k

3. When there is row chaining having 16K block size might be applicable. And need to review application design.

4. The minus in using 8k and 16k tablespaces, is the need to manage their buffer cache manually.