Opatch apply for standalone database GRID and Oracle home 19.3.0.0 to 19.4.1.0.0

Applying the patch through Root it will apply patch on both Grid home and Oracle home.


steps to follow:

Download p30134717_190000_Linux-x86-64  it contain both the oracle and grid home patch

unzip to /tmp directory.

[oracle@rac1 30134717]$ ls -lrt
total 140
drwxr-x---. 5 oracle oinstall     58 Sep 19  2019 29850993
-rw-r--r--. 1 oracle oinstall      0 Sep 19  2019 README.txt
drwxr-x---. 5 oracle oinstall     58 Sep 19  2019 29851014
drwxr-x---. 4 oracle oinstall     45 Sep 19  2019 29401763
drwxr-x---. 2 oracle oinstall   4096 Sep 19  2019 automation
drwxr-x---. 4 oracle oinstall     63 Sep 19  2019 30080447
-rw-r--r--. 1 oracle oinstall 123536 Sep 20  2019 README.html
-rw-rw-r--. 1 oracle oinstall   5058 Sep 20  2019 bundle.xml
-rw-r--r--. 1 oracle oinstall   1166 Apr 12 20:33 check_patches_19c.txt

Initial check to find the status of patch

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A32
COLUMN status FORMAT A10
COLUMN version FORMAT A10
alter session set "_exclude_seed_cdb_view"=FALSE;
spool check_patches_19c.txt

select CON_ID,
TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;

spool off


unzip <OPATCH-ZIP> -D <ORACLE_HOME>
<ORACLE_HOME>/OPatch/opatch version

update the latest Patch required ******


Grid patching


export PATH=$ORACLE_HOME/OPatch:$PATH

[oracle@rac1 OPatch]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.


ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $GRID_HOME

login to root.

. oraenv
+ASM


cd to Grid_home/Opatch   location

[root@rac1 OPatch]# ./opatchauto apply /u01/grid_patch/30134717

OPatchauto session is initiated at Sun Apr 12 19:38:18 2020

System initialization log file is /u01/app/19c/grid/cfgtoollogs/opatchautodb/systemconfig2020-04-12_07-38-25PM.log.

Session log file is /u01/app/19c/grid/cfgtoollogs/opatchauto/opatchauto2020-04-12_07-38-36PM.log
The id for this session is BHUY

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19c/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19c/db_1


Verifying SQL patch applicability on home /u01/app/oracle/product/19c/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19c/db_1


Executing OPatch prereq operations to verify patch applicability on home /u01/app/19c/grid
Patch applicability verified successfully on home /u01/app/19c/grid


Preparing to bring down database service on home /u01/app/oracle/product/19c/db_1
Successfully prepared home /u01/app/oracle/product/19c/db_1 to bring down database service


Bringing down database service on home /u01/app/oracle/product/19c/db_1
Following database has been stopped and will be restarted later during the session: soorya
Database service successfully brought down on home /u01/app/oracle/product/19c/db_1


Bringing down CRS service on home /u01/app/19c/grid
Prepatch operation log file location: /u01/app/oracle/crsdata/rac1/crsconfig/hapatch_2020-04-12_07-41-33PM.log
CRS service brought down successfully on home /u01/app/19c/grid


Start applying binary patch on home /u01/app/oracle/product/19c/db_1
Binary patch applied successfully on home /u01/app/oracle/product/19c/db_1


Start applying binary patch on home /u01/app/19c/grid
Binary patch applied successfully on home /u01/app/19c/grid


Starting CRS service on home /u01/app/19c/grid
Postpatch operation log file location: /u01/app/oracle/crsdata/rac1/crsconfig/hapatch_2020-04-12_07-53-39PM.log
CRS service started successfully on home /u01/app/19c/grid


Starting database service on home /u01/app/oracle/product/19c/db_1
Database service successfully started on home /u01/app/oracle/product/19c/db_1


Preparing home /u01/app/oracle/product/19c/db_1 after database service restarted
No step execution required.........


Trying to apply SQL patch on home /u01/app/oracle/product/19c/db_1

SQL patch applied successfully on home /u01/app/oracle/product/19c/db_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac1
SIDB Home:/u01/app/oracle/product/19c/db_1
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /u01/grid_patch/30134717/29851014
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /u01/grid_patch/30134717/29401763
Reason: This patch is not applicable to this specified target type - "oracle_database"


==Following patches were SUCCESSFULLY applied:

Patch: /u01/grid_patch/30134717/29850993
Log: /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-04-12_19-42-09PM_1.log

Patch: /u01/grid_patch/30134717/30080447
Log: /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-04-12_19-42-09PM_1.log


Host:rac1
SIHA Home:/u01/app/19c/grid
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /u01/grid_patch/30134717/29401763
Reason: This patch is already been applied, so not going to apply again.


==Following patches were SUCCESSFULLY applied:

Patch: /u01/grid_patch/30134717/29850993
Log: /u01/app/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-04-12_19-47-23PM_1.log

Patch: /u01/grid_patch/30134717/29851014
Log: /u01/app/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-04-12_19-47-23PM_1.log

Patch: /u01/grid_patch/30134717/30080447
Log: /u01/app/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-04-12_19-47-23PM_1.log



OPatchauto session completed at Sun Apr 12 20:16:24 2020
Time taken to complete the session 38 minutes, 6 seconds

[oracle@rac1 OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.17

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

Oracle Home       : /u01/app/oracle/product/19c/db_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/19c/db_1/oraInst.loc

OPatch version    : 12.2.0.1.17

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2020-04-12_20-47-04PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-04-12_20-47-04PM.txt

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

Local Machine Information::

Hostname: rac1.localdomain

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0

There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch  30080447     : applied on Sun Apr 12 19:44:16 IST 2020

Unique Patch ID:  23123983

Patch description:  "Database Release Update Revision : 19.4.1.0.191015 (30080447)"

   Created on 19 Sep 2019, 07:09:42 hrs PST8PDT

   Bugs fixed:

     29225758, 29389889, 14735102, 17428816, 19080742, 19697993, 20313356

     21374587, 21965541, 23296836, 23606241, 25756945, 25806201, 25883179

     25986062, 25997810, 26476244, 26611353, 26739322, 26872233, 27126938

     27244999, 27359766, 27369515, 27423500, 27453490, 27666312, 27710072

     27846298, 27957203, 28064977, 28072567, 28125947, 28129791, 28181021

     28210681, 28271693, 28279456, 28313275, 28326928, 28350595, 28371123

     28379065, 28381939, 28390273, 28431445, 28463226, 28475242, 28489419

     28502773, 28513333, 28534475, 28547068, 28561704, 28569897, 28572533

     28572544, 28572667, 28572834, 28587723, 28593682, 28594086, 28597221

     28601957, 28605066, 28606598, 28612239, 28625862, 28627033, 28636532

     28643718, 28644549, 28645570, 28646200, 28646939, 28649388, 28655209

     28663782, 28673945, 28692275, 28694872, 28696373, 28705231, 28710385

     28710734, 28714461, 28715727, 28718469, 28719348, 28720418, 28722229

     28730079, 28740708, 28742555, 28749853, 28755011, 28758722, 28760206

     28767240, 28772390, 28774416, 28777214, 28781754, 28785531, 28789531

     28791852, 28795551, 28802734, 28804517, 28810381, 28811560, 28815123

     28815355, 28819640, 28824482, 28827682, 28833912, 28835937, 28849776

     28854004, 28862532, 28863432, 28867992, 28873575, 28876253, 28876639

     28884931, 28888327, 28889730, 28892794, 28897512, 28899663, 28901126

     28905457, 28907196, 28912691, 28915561, 28917080, 28918429, 28919145

     28922227, 28922532, 28922608, 28925634, 28925880, 28933158, 28936114

     28937717, 28938698, 28940179, 28940281, 28941901, 28942455, 28945421

     28945994, 28950868, 28951533, 28952168, 28954762, 28955606, 28956908

     28957292, 28957723, 28962775, 28965231, 28966444, 28974083, 28977322

     28983095, 28983486, 28986207, 28986231, 28986326, 28986481, 28988482

     28989306, 28993295, 28993353, 28994307, 28996376, 29000000, 29001305

     29001888, 29002784, 29002927, 29003738, 29006318, 29006621, 29007321

     29007775, 29008035, 29008669, 29009513, 29011936, 29012609, 29013475

     29014076, 29015118, 29017265, 29018655, 29019121, 29021063, 29021352

     29024054, 29024552, 29024732, 29026582, 29026606, 29027456, 29027694

     29027940, 29031575, 29031600, 29032234, 29032276, 29032457, 29032607

     29033052, 29033145, 29033200, 29033280, 29034587, 29037290, 29038528

     29039089, 29039510, 29040739, 29043554, 29043651, 29043725, 29044763

     29044954, 29047850, 29048178, 29048289, 29048498, 29048605, 29050357

     29050560, 29050765, 29051702, 29051953, 29052726, 29053783, 29056024

     29056270, 29056560, 29056767, 29059011, 29061959, 29062692, 29062848

     29062860, 29062868, 29110526, 29110783, 29110790, 29110797, 29110802

     29110805, 29111598, 29113282, 29113305, 29117526, 29117642, 29119077

     29120223, 29122224, 29122254, 29123297, 29123432, 29123482, 29124368

     29125036, 29125374, 29125380, 29126345, 29127957, 29128693, 29128935

     29129450, 29129497, 29129712, 29130219, 29131539, 29132938, 29134447

     29135383, 29135649, 29136111, 29138641, 29141316, 29141341, 29141685

     29142609, 29142667, 29144995, 29145214, 29145730, 29149829, 29150338

     29151520, 29152357, 29155099, 29157389, 29158680, 29158899, 29159909

     29159936, 29160174, 29162095, 29163156, 29163415, 29163437, 29163524

     29163567, 29167111, 29167342, 29167374, 29167940, 29168219, 29168433

     29169073, 29169215, 29171683, 29171942, 29172618, 29172826, 29173140

     29173373, 29173817, 29174004, 29176318, 29177466, 29177543, 29177886

     29178385, 29180313, 29180455, 29180559, 29180893, 29181153, 29181231

     29181620, 29181743, 29181923, 29182019, 29183912, 29184297, 29184666

     29185193, 29186456, 29189302, 29189307, 29189889, 29190235, 29190474

     29190740, 29191541, 29192419, 29192468, 29192685, 29193207, 29194205

     29194367, 29194493, 29194827, 29194981, 29195279, 29195337, 29195758

     29196725, 29199635, 29199733, 29200316, 29200700, 29201494, 29201539

     29202104, 29202850, 29203122, 29203166, 29203425, 29203443, 29203604

     29205281, 29205323, 29205419, 29205463, 29205767, 29205918, 29206109

     29206605, 29207073, 29208260, 29208732, 29211457, 29211724, 29212012

     29212433, 29212611, 29213351, 29213775, 29213850, 29213879, 29214561

     29214960, 29216746, 29216984, 29217294, 29217472, 29217828, 29217848

     29218570, 29219205, 29219273, 29220079, 29221248, 29221891, 29222031

     29222784, 29223833, 29223859, 29223967, 29224065, 29224605, 29225076

     29225168, 29227602, 29228869, 29229164, 29229754, 29229844, 29229955

     29230252, 29230565, 29231133, 29232117, 29232154, 29233415, 29234123

     29237538, 29240307, 29241345, 29241651, 29242017, 29242884, 29243958

     29245137, 29245160, 29246163, 29247415, 29247712, 29247906, 29248495

     29248552, 29248835, 29248858, 29249991, 29250059, 29250317, 29251259

     29253184, 29253871, 29254031, 29254930, 29255178, 29255273, 29255431

     29255435, 29256426, 29259119, 29259320, 29260452, 29261547, 29261906

     29262512, 29262887, 29265448, 29266248, 29266899, 29267292, 29268412

     29269171, 29269228, 29270585, 29273539, 29273570, 29273735, 29273812

     29273847, 29274428, 29274564, 29274627, 29275461, 29276272, 29277317

     29278218, 29279658, 29279751, 29279854, 29281527, 29281691, 29281796

     29282233, 29282898, 29285503, 29285788, 29285956, 29286037, 29287130

     29287705, 29292837, 29293072, 29293574, 29297863, 29297915, 29298220

     29299049, 29299082, 29299844, 29301463, 29301566, 29302963, 29303918

     29304781, 29306226, 29306713, 29311588, 29312310, 29312672, 29312734

     29312753, 29313347, 29313417, 29313525, 29314539, 29317756, 29318410

     29319441, 29321489, 29323946, 29324568, 29324735, 29325087, 29325105

     29325257, 29325765, 29325993, 29327044, 29329042, 29329087, 29329807

     29330361, 29331066, 29331209, 29331380, 29331493, 29332292, 29332395

     29332771, 29333500, 29336843, 29337310, 29338315, 29338453, 29338780

     29338913, 29339101, 29339155, 29341209, 29343086, 29345937, 29346057

     29346211, 29346943, 29347620, 29348176, 29350052, 29351386, 29351716

     29351735, 29351749, 29352298, 29352724, 29352867, 29352947, 29353271

     29353432, 29353960, 29355654, 29356547, 29356704, 29356711, 29356752

     29358509, 29358828, 29360285, 29360672, 29360911, 29360950, 29361472

     29361801, 29363151, 29364171, 29364177, 29366940, 29367019, 29367561

     29368253, 29372541, 29373418, 29373588, 29374179, 29375941, 29376346

     29377986, 29378029, 29378834, 29378913, 29379978, 29382784, 29382815

     29384781, 29384854, 29384864, 29385429, 29385652, 29386502, 29386635

     29386660, 29387073, 29387274, 29388020, 29388072, 29388094, 29388524

     29388830, 29390011, 29390435, 29390785, 29391030, 29394014, 29394140

     29394749, 29395657, 29397954, 29397996, 29398488, 29398863, 29399100

     29399121, 29399938, 29402131, 29404483, 29405012, 29405462, 29405651

     29405996, 29407804, 29409149, 29410311, 29410834, 29411037, 29412066

     29412269, 29417719, 29417884, 29420834, 29421059, 29423826, 29424999

     29426241, 29429017, 29429264, 29429566, 29430524, 29431192, 29431485

     29434301, 29435474, 29435652, 29436454, 29436514, 29437594, 29437712

     29438277, 29438736, 29439522, 29441196, 29443187, 29443250, 29444072

     29444282, 29444602, 29446669, 29449477, 29450421, 29451386, 29452576

     29452936, 29452953, 29455424, 29457312, 29457370, 29457807, 29460252

     29461791, 29462594, 29462767, 29462957, 29464779, 29465177, 29467622

     29476473, 29483532, 29483672, 29483685, 29483712, 29483723, 29486181

     29488894, 29489546, 29490256, 29492127, 29492939, 29494245, 29495057

     29495684, 29497311, 29500826, 29502561, 29503543, 29503631, 29503827

     29504492, 29504682, 29505668, 29507616, 29510278, 29511611, 29514479

     29515134, 29515240, 29515766, 29515834, 29516300, 29516766, 29517168

     29521187, 29521748, 29522358, 29522561, 29522662, 29523511, 29525467

     29525886, 29527595, 29529147, 29530440, 29530515, 29530812, 29531654

     29541769, 29542449, 29543034, 29546817, 29547867, 29548687, 29548722

     29549154, 29549730, 29557336, 29558975, 29559187, 29559446, 29559908

     29559981, 29565611, 29580983, 29581771, 29584261, 29586143, 29597536

     29597754, 29598039, 29598233, 29599300, 29601461, 29604002, 29608000

     29611020, 29611991, 29616244, 29616414, 29618074, 29618190, 29622936

     29625065, 29626732, 29628200, 29629650, 29629681, 29631749, 29632095

     29633697, 29635427, 29635717, 29637362, 29638285, 29641736, 29653246

     29656819, 29657422, 29664087, 29664161, 29670782, 29676089, 29677051

     29677733, 29679856, 29687459, 29687763, 29692694, 29695841, 29703932

     29707099, 29707896, 29742223, 29747648, 29751094, 29753244, 29754951

     29756274, 29757651, 29766207, 29766503, 29766679, 29775393, 29779196

     29782211, 29795957, 29838773, 29850930, 30114534

Patch  29850993     : applied on Sun Apr 12 19:42:39 IST 2020

Unique Patch ID:  22950761

Patch description:  "OCW RELEASE UPDATE 19.4.0.0.0 (29850993)"

   Created on 5 Jul 2019, 05:08:35 hrs PST8PDT

   Bugs fixed:

     25736599, 26675491, 27148384, 27222128, 27572040, 27604329, 27760043

     27877830, 28302580, 28319989, 28470673, 28554809, 28621543, 28642469

     28697242, 28699321, 28704073, 28710663, 28755846, 28772816, 28785321

     28800508, 28808652, 28815557, 28847541, 28847572, 28847655, 28852065

     28865162, 28870111, 28870496, 28871040, 28874416, 28874529, 28877252

     28881191, 28881848, 28887933, 28888083, 28892095, 28911140, 28912188

     28925250, 28925460, 28935956, 28940472, 28942694, 28951332, 28963036

     28968779, 28973989, 28980448, 28987100, 28995287, 29003207, 29003617

     29016294, 29018680, 29024876, 29026154, 29027933, 29030056, 29047127

     29051269, 29052850, 29053083, 29058476, 29111631, 29112455, 29117337

     29123444, 29125708, 29125786, 29129476, 29131772, 29132456, 29139727

     29146157, 29147849, 29149170, 29152603, 29152752, 29154631, 29154636

     29154829, 29159216, 29159661, 29160462, 29161923, 29169540, 29169739

     29170717, 29173618, 29181568, 29182920, 29183298, 29186091, 29191827

     29201143, 29201695, 29202442, 29209545, 29210577, 29210610, 29210624

     29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29231008

     29235934, 29242906, 29243749, 29244495, 29244766, 29244968, 29248723

     29249583, 29251564, 29255616, 29257308, 29260224, 29261171, 29261695

     29271019, 29273360, 29274911, 29275362, 29276220, 29280752, 29282090

     29282666, 29285453, 29285621, 29288842, 29290235, 29292232, 29293806

     29294753, 29299830, 29307090, 29307109, 29311336, 29329675, 29330791

     29338139, 29339299, 29347106, 29357821, 29360467, 29360775, 29367971

     29368725, 29376616, 29377086, 29379299, 29379381, 29380201, 29380253

     29380527, 29381000, 29382296, 29385861, 29387212, 29391301, 29392779

     29393649, 29397888, 29398418, 29402110, 29407192, 29411931, 29413360

     29413448, 29433434, 29438073, 29445982, 29450417, 29452365, 29457319

     29460775, 29465047, 29468589, 29470298, 29471399, 29473188, 29485756

     29504266, 29505022, 29505730, 29507560, 29524627, 29528119, 29529127

     29529530, 29535802, 29540165, 29542152, 29545235, 29555663, 29583036

     29585881, 29592188, 29599431, 29599530, 29601549, 29632615, 29637215

     29641885, 29655862, 29659031, 29672162, 29686671, 29760252, 29770006

     29817598, 29866585, 29921525, 3

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

OPatch succeeded.

[oracle@rac1 OPatch]$

export ORACLE_SID=soorya

export ORACLE_HOME=/u01/app/oracle/product/19c/db_1

ORACLE_HOME=/u01/app/oracle/product/19c/db_1 ORACLE_SID=soorya /u01/app/oracle/product/19c/db_1/OPatch/datapatch

Connecting to database...OK

Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

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.4.1.0.0 Release_Update_Revision 190819162456: Installed

  PDB CDB$ROOT:

    Applied 19.4.1.0.0 Release_Update_Revision 190819162456 successfully on 12-APR-20 08.15.51.491994 PM

  PDB PDB$SEED:

    Applied 19.4.1.0.0 Release_Update_Revision 190819162456 successfully on 12-APR-20 08.16.11.320018 PM

  PDB PDB01:

    Applied 19.4.1.0.0 Release_Update_Revision 190819162456 successfully on 12-APR-20 08.16.12.967140 PM

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

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED PDB01

    No interim patches need to be rolled back

    No release update patches need to be installed

    No interim patches need to be applied

SQL Patching tool complete on Sun Apr 12 20:59:10 2020

Thanks

oracle health check

## SQL - HealthCheck###
set echo off
set heading off
set lines 500 pages 500

prompt Health Check Report for Database  
select chr(9)||chr(9)||chr(9)|| name from V$database;
prompt Instance Name
select  INSTANCE_NAME, to_char(STARTUP_TIME,'dd-MON-yyyy hh24:mi') STARTUP_TIME from V$instance;

set heading on
col OWNER for a15
col OBJECT_NAME for  a30
break on owner on object_name
prompt ############################################################################
prompt Invalid Objects Currently in database
prompt ############################################################################

select owner, object_name, object_type from dba_objects where status <> 'VALID'
order by 1,2,3;

clear breaks

prompt ############################################################################
prompt Blocking Session in Database
prompt ############################################################################

select sid, serial#, username, status, event, BLOCKING_INSTANCE, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION_STATUS, FINAL_BLOCKING_SESSION
from v$session
where blocking_session is not null;

prompt ############################################################################
prompt Details of Blocking SIDs
prompt ############################################################################

select sid, serial#, username, status, event from V$session
where sid in (select distinct BLOCKING_SESSION from v$session
where blocking_session is not null);

prompt ############################################################################
prompt Sessions > 20MB PGA
prompt ############################################################################


column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a40
column logon_time format a20

select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and PGA_USED_MEM/1024/1024 > 20  -- pga_used memory over 20mb
order by PGA_USED_MEM;

prompt ############################################################################
prompt Current Wait Events in the Database
prompt ############################################################################


col wait_class for a40
col event for a60
select wait_class, event, count(*)  from v$session
where username is not null
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Detailed Sess Waits - (sesswaits.sql)
prompt ############################################################################


col seconds_in_wait heading "Wait|(Sec.)" format 9,999,999
select event,
       sid,BLOCKING_SESSION,sql_id,
       p1,
--       p1text,
       p2,
--       p2text,
SECONDS_IN_WAIT
from v$session
where event not in ('SQL*Net message from client',
                'SQL*Net message to client',
                'pipe get',
                'pmon timer',
                'rdbms ipc message',
                'Streams AQ: waiting for messages in the queue',
                'Streams AQ: qmn coordinator idle wait',
                'Streams AQ: waiting for time management or cleanup tasks',
                'PL/SQL lock timer',
                'Streams AQ: qmn slave idle wait',
                'jobq slave wait',
                'queue messages',
                'io done',
                'i/o slave wait',
                'sbtwrite2',
                'async disk IO',
                'smon timer')
order by event, p1,p2 ;


prompt ############################################################################
prompt Wait Events in Last 7 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/192
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 15 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/96
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 60 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/24
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Current IO Functions Statistics
prompt ############################################################################


col function_name    format a25         heading "File Type"
col reads            format 99,999,999  heading "Reads"
col writes           format 99,999,999  heading "Writes"
col number_of_waits  format 99,999,999  heading "Waits"
col wait_time_sec    format 999,999,999 heading "Wait Time|Sec"
col avg_wait_ms      format 999.99      heading "Avg|Wait ms"

set lines 80
set pages 10000

select
   function_name,
   small_read_reqs + large_read_reqs reads,
   small_write_reqs + large_write_reqs writes,
   wait_time/1000 wait_time_sec,
   case when number_of_waits > 0 then
          round(wait_time / number_of_waits, 2)
       end avg_wait_ms
from
   v$iostat_function
order by
    wait_time desc;


set heading off
prompt ############################################################################
prompt Load Average For Server
prompt ############################################################################
 select 'Load Average - ' ||   value  || ' NUM_CPUS  - ' || (select   value   from v$osstat where stat_name = 'NUM_CPUS') || ' LA/pCPU - ' || value/(select   value   from v$osstat where stat_name = 'NUM_CPUS')
   from v$osstat
 where stat_name = 'LOAD';

set pagesize 60
column "Tablespace" heading "Tablespace Name" format a30
column "Size" heading "Tablespace|Size (mb)" format 9999999.9
column "Used" heading "Used|Space (mb)" format 9999999.9
column "Left" heading "Available|Space (mb)" format 9999999.9
column "PCTFree" heading "% Free" format 999.99

ttitle left "Tablespace Space Allocations"
break on report
-- compute sum of "Size", "Left", "Used" on report
select /*+ RULE */
t.tablespace_name,
NVL(round(((sum(u.blocks)*p.value)/1024/1024),2),0) Used_mb,
t.Tot_MB,
NVL(round(sum(u.blocks)*p.value/1024/1024/t.Tot_MB*100,2),0) "USED %"
from v$sort_usage u,
v$parameter p,
(select tablespace_name,sum(bytes)/1024/1024 Tot_MB
from dba_temp_files
group by tablespace_name
) t
where p.name = 'db_block_size'
and u.tablespace (+) = t.tablespace_name
group by
t.tablespace_name,p.value,t.Tot_MB
order by 1,2;

prompt ############################################################################
PROMPT ======================= Total TEMP_TS consuming =======================
prompt ############################################################################
select tablespace, sum(blocks)*8192/1024/1024 consuming_TEMP_MB from
v$session, v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by tablespace;

prompt ############################################################################
PROMPT ======================= Sessions consuming TEMP_TS more than 10 MB =======================
prompt ############################################################################
select sid, tablespace,
sum(blocks)*8192/1024/1024 consuming_TEMP_MB from v$session,
v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by sid, tablespace having sum(blocks)*8192/1024/1024 > 10
order by sum(blocks)*8192/1024/1024 desc ;

prompt ############################################################################
PROMPT ======================= Current Locked Objects =======================
prompt ############################################################################

 col owner for a25
 col object_name for a35
 col oracle_username for a25
col os_user_name for a25

 SELECT B.Owner, B.Object_Name,b.object_type, A.Oracle_Username, A.OS_User_Name, A.SESSION_ID, A.LOCKED_MODE
 FROM V$Locked_Object A, All_Objects B
 WHERE A.Object_ID = B.Object_ID