Oracle DBA by Example. Code samples, Scripts, Reference.: 2024

Thursday, April 11, 2024

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

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

Wednesday, April 10, 2024

drbd sync SOW

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
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!!

Monday, March 25, 2024

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;

V$LOCKED_OBJECT + V$SESSION

SELECT 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# ||''';' 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

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;

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!

Thursday, March 7, 2024

Oracle 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)

This documents lists the latest patches for Oracle 19
for example:

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:

Monday, March 4, 2024

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

Thursday, February 22, 2024

Code by example: expdp from schema + scp to a backup server

exp_schema.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

EXP_DIR=/starhome/iu/workarea/ora_exp
EXP_NAME_PREFIX=exp_schema
EXP_DIR_BACKUP=/starhome/schema/workarea/schema_exp_backup

REMOTE_USER=rem_user
REMOTE_SERVER=10.20.30.40
REMOTE_PATH=/starhome/workarea/ora_exp
LOCAL_DMP_FILE=exp_schema_${RUN_DATE}.dmp
LOCAL_DIR=/starhome/schema/workarea/schema_exp_backup

KEEP_DAYS=30

expdp user/password@ora_inst DIRECTORY=IG_EXP_DIR DUMPFILE=${EXP_NAME_PREFIX}.dmp LOGFILE=${EXP_NAME_PREFIX}.log REUSE_DUMPFILES=YES

echo "scp -p ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${EXP_NAME_PREFIX}.dmp ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp"

scp -p ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${EXP_NAME_PREFIX}.dmp ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp

gzip ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp

find ${LOCAL_DIR} -type f -name "${EXP_NAME_PREFIX}*" -mtime ${KEEP_DAYS} -exec rm {} \;

Monday, February 12, 2024

SQL Tuning II DBMS_XPLAN, V$SQL_PLAN

SQL Tuning II

=============================
Contents
=============================
V$SQL_PLAN and other tables
DBMS_XPLAN

=============================
V$SQL_PLAN and other tables
=============================
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL

V$SQL_PLAN
V$SQL_PLAN contains the execution plan for every statement stored in the shared SQL area.
Its definition is similar to the PLAN_TABLE.

V$SQL_PLAN_STATISTICS
The V$SQL_PLAN_STATISTICS view provides the actual execution statistics.
All statistics, except the number of output rows, are cumulative.

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.

V$SQL_PLAN_STATISTICS_ALL
This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information.

=============================
DBMS_XPLAN Package
=============================
DBMS_XPLAN Package is used to format and display execution plan.
DBMS_XPLAN Package got following functions:

DISPLAY - From plan table.

DISPLAY_AWR - For a stored SQL statement in the AWR.

DISPLAY_CURSOR - For execution plan of any loaded cursor.

DISPLAY_SQL_PLAN_BASELINE - For execution plans for the SQL statement identified by SQL handle.

DISPLAY_SQLSET - For execution plan of statements stored in a SQL tuning set.

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

DBMS_XPLAN Full Syntax
=============================

Full Syntax:

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,

format IN VARCHAR2 DEFAULT 'TYPICAL');

sql_id - is DBA_HIST_SQL_PLAN.sql_id

plan_hash_value - If omitted, the function will return ALL

stored execution plans for a given sql_id.

db_id - is V$DATABASE.db_id

To find the correct sql_id:
SELECT sql_id, plan_hash_value
FROM DBA_HIST_SQL_PLAN,
DBA_HIST_SQLTEXT
WHERE DBA_HIST_SQLTEXT.sql_text LIKE '%KUKU%';

format - is optional.
- typical - All data except parallel info.
- basic - Less data than typical.
- serial - Same as typical including parallel info.
- all - Most detailed.

=============================
1. DISPLAY - From plan table.
=============================

EXPLAIN PLAN FOR
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SET LINESIZE 130
SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

=============================
2. DISPLAY_CURSOR - For execution plan of any loaded cursor.
=============================

To display the execution plan of the last executed statement for that session.
SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of specific statement, use V$SQL.sql_id:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

where:
'gwp663cqh5qbf' is the sql_id
0 is the child_number

To get the sql_id and child_number:
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%KUKU%';

=============================
3, DISPLAY_AWR Function.
=============================
This table function displays the contents of an execution plan stored in the AWR.

The full syntax is below.
Only sql_id is mandatory.

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

Usage Example:
SET LINESIZE 120

SET PAGESIZE 1000

This will show "historic" explain plan from AWR collected data.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

This will show up to date explain plan
SELECT sql_id, child_number

FROM V$SQL

WHERE sql_id = 'fw4tmpkt79r4r';

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('fw4tmpkt79r4r',5,'OUTLINE'));

=============================
Reference
=============================

Explain Plan Reference

DBMS_XPLAN Reference

Wednesday, February 7, 2024

Upgrade to Oracle 19.12 from Oracle 19.x by example

Minimum space required:

6Gb for p33494256 and p6880880 patches

16Gb for /software/oracle

Steps:

as root user

mkdir /starhome/iu/workarea/oracle_home_backup

chown oracle:dba /starhome/iu/workarea/oracle_home_backup

mkdir /starhome/iu/workarea/ora_upgrade

chown oracle:dba /starhome/iu/workarea/ora_upgrade

as oracle user

cp -rp $ORACLE_HOME/ /starhome/iu/workarea/oracle_home_backup/

cd /starhome/iu/workarea/ora_upgrade

sftp p6880880_190000_Linux-x86-64.zip ora_upgrade/

unzip -d TOP_6880880 p6880880_190000_Linux-x86-64.zip

cd $ORACLE_HOME/OPatch/

rm -rf *

cd /starhome/iu/workarea/ora_upgrade/TOP_6880880/OPatch

mv ./* $ORACLE_HOME/OPatch/

cd /starhome/iu/workarea/ora_upgrade

unzip -d TOP_33494256 p33494256_190000_Linux-x86-64.zip

cd TOP_33494256/33494256

NOTE - Stop Oracle Server and Listener and Golden Gate before applying patch!!!

$ORACLE_HOME/OPatch/opatch apply

Expected result:

Patch 33494256 successfully applied.

...

OPatch succeeded.

cd /starhome/iu/workarea/ora_upgrade

rm -rf TOP_6880880

rm -rf TOP_33494256

The issue with GG+oracle SE lower than 19.12:
In case using Golden gate on oracle 19.x on SE - there is an error when starting EXTRACT

2024-02-05 13:13:18 ERROR OGG-02030 Failed to set logmining server parameters back to default values.

2024-02-05 13:13:18 ERROR OGG-02042 OCI Error 23605.

The Reason:

Golden gate start the EXTRACT in PARALLEL mode, but parallel functionality is not available in SE, and no way to control this parameter.

The Solution:

Upgrade Oracle SE to 19.12 or higher.

Clean up old patches

Old patches are stored under $ORACLE_HOME/.patch_storage, and can use up space.

By default only 2 patches are required:

- Previous patch, for rollback

- Current Patch

Older patches are not required, and these should be cleaned up manually using OPatch utility:

Step 1. List Inactive Patches

opatch util listorderedinactivepatches

Step 2. Delete Inactive Patches

opatch util deleteinactivepatches

Example:

oracle@orahost:/software/oracle>% cd $ORACLE_HOME/OPatch

oracle@orahost:/software/oracle/1910/OPatch>% ./opatch util listorderedinactivepatches

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /software/oracle/1910

Central Inventory : /software/oracle/oraInventory

from : /software/oracle/1910/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-02-12_04-26-16AM_1.log

Invoking utility "listorderedinactivepatches"

List Inactive patches option provided

The oracle home has the following inactive patch(es) and their respective overlay patches:

The number of RU chains is 1

***** There are 2 inactive RU patches in chain 1

-Inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 UTC 2019, with no overlays

-Inactive RU/BP 32819074:Database Release Update Revision : 19.10.2.0.0 (32819074), installed on: Wed Oct 20 14:09:36 UTC 2021, with no overlays

-Active RU/BP 33494256:Database Release Update Revision : 19.12.2.0.220118 (33494256), installed on: Mon Feb 12 04:19:12 UTC 2024, with no overlays

OPatch succeeded.

oracle@orahost:/software/oracle/1910/OPatch>% ./opatch util deleteinactivepatches

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /software/oracle/1910

Central Inventory : /software/oracle/oraInventory

from : /software/oracle/1910/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-02-12_04-27-54AM_1.log

Invoking utility "deleteinactivepatches"

Inactive Patches Cleanup option provided

Delete Inactive Patches .......

***** There are 2 inactive RU patches in chain 1

***** 1 inactive patches will be deleted

-To be deleted inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 UTC 2019, with no overlays

-To be retained inactive RU/BP 32819074:Database Release Update Revision : 19.10.2.0.0 (32819074), installed on: Wed Oct 20 14:09:36 UTC 2021, with no overlays

-Active RU/BP 33494256:Database Release Update Revision : 19.12.2.0.220118 (33494256), installed on: Mon Feb 12 04:19:12 UTC 2024, with no overlays

Do you want to proceed? [y|n]

User Responded with: Y

Deleted RU/BP patch: 29517242

OPatch succeeded.

Thursday, February 1, 2024

How to see bind variable value in Oracle

See bind variable value in Oracle using trace 10046

Turn on trace:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';

Turn off trace: (once trace file were generated)

ALTER SYSTEM SET EVENTS '10046 trace name context off';

Get the sql_id

SELECT sql_text, sql_id

FROM V$SQLAREA

WHERE sql_text LIKE '%INSERT INTO GA_W_COUNTERS_HISTORY%'

AND module = 'JDBC Thin Client';

sql_id = cyzmshqj3a6h6

show parameter _diag_adr_trace_dest;

NAME VALUE

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

_diag_adr_trace_dest /software/oracle/diag/rdbms/igt/igt/trace

cd to trace directory

cd /software/oracle/diag/rdbms/igt/igt/trace

To search for a value toi the 15 bind variable:

grep Bind#15 -A 4 : will fetch 4 rows after Bind#15, for example:

grep -v JAN : will filter out irrelevant parameters.

 Bind#15
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=496
  kxsbbbfp=7f4179755d48  bln=22  avl=02  flg=01
  value=1

The output would be:

./igt_ora_10061.trc-  value=1
./igt_ora_11508.trc-  value=1
./igt_ora_1222.trc-  value=1
./igt_ora_18480.trc-  value=1
./igt_ora_19637.trc-  value=1
./igt_ora_19659.trc-  value=1
./igt_ora_19733.trc-  value=1
./igt_ora_22060.trc-  value=1
./igt_ora_2624.trc-  value=1
./igt_ora_2777.trc-  value=1
./igt_ora_4301.trc-  value=1
./igt_ora_5119.trc-  value=1

Sample output:

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

PARSING IN CURSOR #140173184644272 len=294 dep=0 uid=82 oct=2 lid=82 tim=3901074549204 hv=2721389062 ad='14ed60668' sqlid='cyzmshqj3a6h6'

INSERT INTO GA_W_COUNTERS_HISTORY (STATIC_ID , DYN1 , DYN2 , DYN3 , DYN4 , DYN5, DYN_S1, DYN_S2, DYN_S3 ,DYN_S4, DYN_S5, COUNTER_SUM , COUNTER_DELTA, TS_LAST_MODIFIED, NODE_ID, DAY, AFFILIATE_ID, SITE_ID) values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 )

END OF STMT

PARSE #140173184644272:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=3901074549203

BINDS #140173184644272:

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=472 off=0

kxsbbbfp=7f7c97477c50 bln=22 avl=09 flg=05

value=1011500104010002

Bind#1

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24

kxsbbbfp=7f7c97477c68 bln=22 avl=01 flg=01

value=0

 Bind#2

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=48

  kxsbbbfp=7f7c97477c80  bln=22  avl=01  flg=01

  value=0

 Bind#3

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=72

  kxsbbbfp=7f7c97477c98  bln=22  avl=01  flg=01

  value=0

 Bind#4

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=96

  kxsbbbfp=7f7c97477cb0  bln=22  avl=01  flg=01

  value=0

 Bind#5

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=120

  kxsbbbfp=7f7c97477cc8  bln=22  avl=01  flg=01

  value=0

Bind#6 #NULL - no value

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=144

  kxsbbbfp=7f7c97477ce0  bln=32  avl=00  flg=01

 Bind#7 #NULL - no value

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=176

  kxsbbbfp=7f7c97477d00  bln=32  avl=00  flg=01

 Bind#8 #NULL - no value

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=208

  kxsbbbfp=7f7c97477d20  bln=32  avl=00  flg=01

 Bind#9 #NULL - no value

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=240

  kxsbbbfp=7f7c97477d40  bln=32  avl=00  flg=01

 Bind#10 #NULL - no value

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=272

  kxsbbbfp=7f7c97477d60  bln=32  avl=00  flg=01

Bind#11

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=304

kxsbbbfp=7f7c97477d80 bln=22 avl=03 flg=01

value=168

Bind#12

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=328

kxsbbbfp=7f7c97477d98 bln=22 avl=02 flg=01

value=2

Bind#13

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00

oacflg=03 fl2=9000000 frm=00 csi=00 siz=0 off=352

kxsbbbfp=7f7c97477db0 bln=11 avl=07 flg=01

value=01-FEB-24 11.45.00 AM

Bind#14

oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=368

kxsbbbfp=7f7c97477dc0 bln=32 avl=03 flg=01

value="108"

 Bind#15
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=400
  kxsbbbfp=7f7c97477de0  bln=22  avl=02  flg=01
  value=1
 Bind#16
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=424
  kxsbbbfp=7f7c97477df8  bln=22  avl=02  flg=01
  value=69
 Bind#17
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=448
  kxsbbbfp=7f7c97477e10  bln=22  avl=02  flg=01
  value=1

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

PARSING IN CURSOR #140173090707784 len=173 dep=1 uid=0 oct=3 lid=0 tim=3901074550196 hv=3830682485 ad='11f235bb8' sqlid='3c32a4bk574vp'

select d.sumobj# from sys.sumdetail$ d, sys.sum$ s where d.sumobj# = s.obj# and bitand(d.flags, 2) = 2 and bitand(s.xpflags, 34359738368) = 34359738368 and d.detailobj# = :1

END OF STMT

PARSE #140173090707784:c=126,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1505437449,tim=3901074550195

BINDS #140173090707784:

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=00 fl2=1000001 frm=01 csi=00 siz=24 off=0

kxsbbbfp=7f7c97473558 bln=22 avl=04 flg=05

value=25760

etc...

Tuesday, January 30, 2024

AWR Report - Wait event "cursor: pin S wait on X"

===========================
General
===========================
Checking the AWR report, the top foreground event is "cursor: pin S wait on X".
What does it mean?

===========================
Oracle Documentation
===========================
Per Oracle Tech Note Troubleshooting 'cursor: pin S wait on X' waits. (Doc ID 1349387.1)

What is a 'Cursor: pin S wait on X' wait?

A cursor wait is associated with parsing in some form.
A session may wait for this event when it is trying to get a mutex pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive.
Frequently, waits for 'Cursor: pin S wait on X' is a symptom and not the cause.
There may be underlying tuning requirements or known issues.

Apparently up to Oracle 11, there was a limit to child cursors, which was 1024.
In Oracle 11, this limit does not exists, which can lead to high number of child cursors.

==========================================
Look for a specific SQL that might be responsible for the wait.
==========================================
SELECT *
FROM DBA_HIST_SNAPSHOT;
To get the snap_id

SELECT sql_id, count(*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE snap_id = 31405
AND event = 'cursor: pin S wait on X'
GROUP BY sql_id
ORDER BY count (*) DESC

6jb4173jbysjp 3526

So one sql_id is responsible for all the 'cursor: pin S wait on X' waits

SELECT *
FROM V$SQL_TEXT
WHERE sql_id = '6jb4173jbysjp'

INSERT INTO DEBUG_GEN_W_SDR
(CDR_ID, PRODUCT_ID, PRODUCT_NAME, SYSTEM_ID, BILLING_TAG, IMSI, MSISDN, IMEI, EVENT_NAME, HOME_COUNTRY_ID, HOME_COUNTRY_NAME, VISITED_COUNTRY_ID, VISITED_COUNTRY_NAME, HOME_NETWORK_ID,HOME_NETWORK_NAME, HOME_PLMN_CODE, VISITED_NETWORK_ID, VISITED_NETWORK_NAME, CLI_IN, CLI_OUT, ORIG_DN, DN_IN, DN_OUT, DN_IN_NOA, REDIRECTION_NUMBER, CALL_TYPE, ROAMING_TYPE, SERVICE_KEY_IN, CURRENT_MSC_GT,SCCP_CALLED_GT, SCCP_CALLING_GT, CALL_END_CLEAR_CAUSE_ID, CALL_END_CLEAR_CAUSE_NAME, CALL_END_NETWORK_CLEAR_CAUSE, SESSION_START_TS, SESSION_END_TS, SESSION_DESC, SESSION_TS_ANSWER, SESSION_TS_DISCONNECT, SESSION_DUR_SEC, EXTERNAL_CALL_REF_NUM, PARAM1, PARAM2, PARAM3,PARAM4,PARAM5, PARAM6, PARAM7, PARAM8, PARAM9, PARAM10, PARAM11, PARAM12, PARAM13, PARAM14, PARAM15, PARAM16, PARAM17, PARAM18, PARAM19, PARAM20, PARAM21, PARAM22, PARAM23, PARAM24,PARAM25, PARAM26, PARAM27, PARAM28, PARAM29, PARAM30, PARAM31,PARAM32,PARAM33,PARAM34,PARAM35, PARAM36, PARAM37, PARAM38, PARAM39, PARAM40, PARAM41, PARAM42, PARAM43, PARAM44)
VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 ,:24 ,:25 ,:26 ,:27 ,:28 ,:29 ,:30 ,:31 ,:32 ,:33 ,:34 ,:35 ,:36 ,:37 ,:38 ,:39 ,:40 ,:41 ,:42 ,:43 ,:44 ,:45 ,:46 ,:47 ,:48 ,:49 ,:50 ,:51 ,:52 ,:53 ,:54 ,:55 ,:56 ,:57 ,:58 ,:59 ,:60 ,:61 ,:62 ,:63 ,:64 ,:65 ,:66 ,:67 ,:68 ,:69 ,:70 ,:71 ,:72 ,:73 ,:74 ,:75 ,:76 ,:77 ,:78 ,:79 ,:80 ,:81 ,:82 ,:83 ,:84 ,:85 )

This SQL is using bind variable, seems that there is nothing wrong with that...

SELECT version_count, executions, parse_calls, parsing_schema
FROM V$SQLAREA
WHERE sql_id = '6jb4173jbysjp'

version_count = 2717
executions = 5646
parse_calls = 1631
parsing_schema_name = MY_USER

So the issue is the big number of version_count.
Why did this happen?

===========================
High number of cusror version count
===========================

This is a very nice post

Oracle 11.2: Cursor Mutex S wait event and too many child cursors

In short, when java application is doing UPDATE or INSERT with a column having NULL value, it does not grantee that the datatype for the NULL value parameter, would be the same in all the executions.

To grantee that, application must set parameter to a specific datatype, in addition to setting value to NULL.

In case there are many such NULL values columns, the combinations number would be high, leading to the high number of child cursors.

Changing the data type of a bind variable invalidates the child cursor (“bind mismatch”).

In detail...

jdbs driver by default is using setNull.NULL.

In this case the setter method will not use the data type of the field in the table.

Instead, the default is VARCHAR2 is used.

If in another execution a value is passed, the setter would use the data type of the field in the table.

In theory, setting over 30 numeric values to null and non-null in an alternating matter, creates 2^30 child cursors.

The solution would be set the bind variable to the correct datatype in addition to setting value to NULL.

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

V$SQL_SHARED_CURSOR

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

To see child cursors:

SELECT * FROM V$SQL_SHARED_CURSOR

WHERE address LIKE 'f3g84j69n0tjh%'

This table has many types, for which cursor could not be shared, and child cursors were created.

DESC V$SQL_SHARED_CURSOR

Name Type Nullable

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

SQL_ID VARCHAR2(13 BYTE) Y

ADDRESS RAW(8) Y

CHILD_ADDRESS RAW(8) Y

CHILD_NUMBER NUMBER Y

UNBOUND_CURSOR VARCHAR2(1 BYTE) Y

SQL_TYPE_MISMATCH VARCHAR2(1 BYTE) Y

OPTIMIZER_MISMATCH VARCHAR2(1 BYTE) Y

OUTLINE_MISMATCH VARCHAR2(1 BYTE) Y

STATS_ROW_MISMATCH VARCHAR2(1 BYTE) Y

LITERAL_MISMATCH VARCHAR2(1 BYTE) Y

FORCE_HARD_PARSE VARCHAR2(1 BYTE) Y

EXPLAIN_PLAN_CURSOR VARCHAR2(1 BYTE) Y

BUFFERED_DML_MISMATCH VARCHAR2(1 BYTE) Y

PDML_ENV_MISMATCH VARCHAR2(1 BYTE) Y

INST_DRTLD_MISMATCH VARCHAR2(1 BYTE) Y

SLAVE_QC_MISMATCH VARCHAR2(1 BYTE) Y

TYPECHECK_MISMATCH VARCHAR2(1 BYTE) Y

AUTH_CHECK_MISMATCH VARCHAR2(1 BYTE) Y

BIND_MISMATCH VARCHAR2(1 BYTE) Y

and much more...

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

Workaround

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

Flush the shared pool for the specific statement, using DBMS_SHARED_POOL.purge.

Execution of DBMS_SHARED_POOL.PURGE needs an EXECUTE permission from sysdba.

SQL> SELECT address, hash_value, version_count FROM V$SQLAREA WHERE sql_id LIKE 'f3g84j69n0tjh%';

ADDRESS HASH_VALUE VERSION_COUNT

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

000000009F8E4970 2470471216 102

SQL> exec DBMS_SHARED_POOL.PURGE ('000000009F8E4970, 2470471216', 'C');

PL/SQL procedure successfully completed.

SQL> SELECT address, hash_value, version_count FROM V$SQLAREA WHERE sql_id LIKE 'f3g84j69n0tjh%';

no rows selected

Or via PL/SQL block:

CREATE OR REPLACE PROCEDURE purge_sql_sp AS

v_sql_id V$SQLAREA.sql_id%TYPE := '';

v_sql_address V$SQLAREA.address%TYPE := '';

v_hash_value V$SQLAREA.hash_value%TYPE := '';

BEGIN

SELECT sql_id INTO v_sql_id

FROM V$SQLAREA

WHERE sql_text LIKE 'MERGE into SOME_TABLE%';

--EXECUTE IMMEDIATE 'SELECT address, hash_value FROM V$SQLAREA

WHERE sql_id = ''someSQLIDfoo''' INTO v_sql_address,v_hash_value;

EXECUTE IMMEDIATE 'SELECT address, hash_value FROM V$SQLAREA WHERE sql_id = '''||v_sql_id||'''' INTO v_sql_address,v_hash_value;

DBMS_SHARED_POOL.purge (v_sql_address||','||v_hash_value||','C');

END;

The SQL ID should remain the same, so this piece of code could be wrapped into a scheduled job, as needed.

Check status:

SELECT 'SPE7' as source_db,

TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi'),

sql_id,

executions_delta,

loaded_versions,

invalidations_delta,

parse_calls_delta,

disk_reads_delta

FROM DBA_HIST_SQLSTAT,

DBA_HIST_SNAPSHOT

WHERE sql_id = '71bmcyg2f5td0'

AND DBA_HIST_SQLSTAT.snap_id = DBA_HIST_SNAPSHOT.snap_id

ORDER BY 2 DESC;

Pages

Thursday, April 11, 2024

Wednesday, April 10, 2024

Monday, March 25, 2024

Thursday, March 7, 2024

Monday, March 4, 2024

Thursday, February 22, 2024

Monday, February 12, 2024

Wednesday, February 7, 2024

Thursday, February 1, 2024

Tuesday, January 30, 2024