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

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

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

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.

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:

find . -type f -name "*.trc" | xargs grep -l cyzmshqj3a6h6 | xargs grep Bind#15 -A 4 | grep value | sort -u | grep -v JAN

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