Pages

Monday, March 25, 2024

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!

Thursday, March 7, 2024

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

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 {} \;


export_schema.sh <SCHEMA_NAME>
#!/bin/bash

if [[ "$#" -ne 1 ]]; then
 echo "usage: export_schema.sh SCHEMA_NAME"
 exit 1
fi

EXP_SCHEMA=$1
VERSION=11.2
RUN_DATE=`date "+%Y%m%d"_"%H%M"`

echo "expdp system/xen86pga@igt DIRECTORY=IG_EXP_DIR LOGFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.log DUMPFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.dmp SCHEMAS=${EXP_SCHEMA} version=${VERSION}"
expdp system/xen86pga@igt DIRECTORY=IG_EXP_DIR LOGFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.log DUMPFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.dmp SCHEMAS=${EXP_SCHEMA} 
version=${VERSION}


imp_schema_data.sh <OLD_USER> <NEW_USER> <NEW_PASS> <DMP_FILE>
#!/bin/bash

if [[ "$#" -ne 4 ]]; then
 echo "usage: import_schema_data.sh OLD_USER NEW_USER USER_PASSWORD DMP_FILE"
 exit 1
fi

clear
echo "*****  Importing Data...  "

OLD_USER=$1
NEW_USER=$2
NEW_PASS=$3
DMP_FILE=$4

RUN_DATE=`date "+%Y%m%d"_"%H%M"`
CONNECTSTR=igt
DIRECTORY=IG_EXP_DIR

# Stage 1 Disable FK's
  sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@disable_cons.sql
EOD


# Stage 2 Disable triggers
  sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@disable_trigs.sql
EOD


# Stage 3 Import objects
impdp system/xxxxxxx@${CONNECTSTR} directory=${DIRECTORY} dumpfile=${DMP_FILE} logfile=impdp_${NEW_USER}_${RUN_DATE}.log table_exists_action=truncate streams_configuration=n content=data_only remap_schema=${OLD_USER}:${NEW_USER}

# Stage 4 Enable FK's
  sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@enable_cons.sql
EOD

# Stage 5 Enable triggers
  sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@enable_trigs.sql
EOD

echo "***************************************************"
echo " FINISHED Import of DataBase                       "
echo "***************************************************"

Additional Files:
disable_cons.sql
disable_trigs.sql
enable_cons.sql
enable_trigs.sql

disable_cons.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000  heading off feedback off

spool disable_cons.txt
prompt spool disable_cons.log
select 'alter table '|| table_name || ' disable constraint ' || constraint_name || ';' from user_constraints
where CONSTRAINT_TYPE='R' AND status = 'ENABLED';
/

prompt spool off
spool off

@disable_cons.txt 

disable_trigs.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000  heading off feedback off

spool disable_trigs.txt
prompt spool disable_trigs.log
select 'alter trigger '|| trigger_name || ' disable  '  || ';' from user_triggers
/

prompt spool off
spool off

@disable_trigs.txt 

enable_cons.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000  heading off feedback off

spool enable_cons.txt
prompt spool enable_cons.log
select 'alter table '|| table_name || ' enable constraint ' || constraint_name || ';' from user_constraints
where CONSTRAINT_TYPE='R' AND status = 'ENABLED';
/

prompt spool off
spool off

@enable_cons.txt

enable_trigs.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000  heading off feedback off

spool enable_trigs.txt
prompt spool enable_trigs.log
select 'alter trigger '|| trigger_name || ' enable ' || ';' from user_triggers where trigger_name!='REFRESH_VIEWS_TRG'
/

prompt spool off
spool off

@enable_trigs.txt 

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

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
1.
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
2.
cp -rp $ORACLE_HOME/ /starhome/iu/workarea/oracle_home_backup/

3.
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/

4.
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 -report

$ORACLE_HOME/OPatch/opatch apply

Expected result: 
Patch 33494256 successfully applied.
...
...
OPatch succeeded.

4. 
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
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


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
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


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]
Y
User Responded with: Y
Deleted RU/BP patch: 29517242

OPatch succeeded.