Categories
Blog

Oracle Management Cloud (OMC) Part 2 – Monitoring Database

In my previous blog, we looked at monitoring a premises linux server (Agent Install). In this blog we will add the monitoring of the database to the agent. For this we need 2 files :

 

File 1 (A Credential file for the Oracle database, you will need to update the password to what you have set in your environment): omc_oracle_db_creds.json

{

    “credentials” : [

        {

            “id” :SQLCreds“,

            “name” :SQLCreds“,

            credType” :DBCreds“,

            “properties” : [

                {

                    “name” :DBUserName“,

                    “value” : “CLEAR[dbsnm]p”

                },

                {

                    “name” :DBPassword“,

                    “value” : “CLEAR[w1ll0w]”

                },

                {

                    “name” :DBRole“,

                    “value” : “CLEAR[Normal]”

                }

            ]

        }

    ]

}

 

File 2 (The Agent Configuration file, you will need to update the fields highlighted in yellow to your environment) : oracle_agent_add.json

{

      “entities”: [{

            “name”: “C_stest1“,

            “type”:
omc_oracle_db“,

            displayName“: “stest1“,

            timezoneRegion“: “BST”,

            credentialRefs“: [“SQLCreds“],

            “properties”: {

                  host_name“: {

                        displayName“: “host_name“,

                        “value”: “e33392

                  },

                 “port”: {

                        displayName“: “port”,

                        “value”: “1521”

                  },

                  sid“: {

                        displayName“: “sid“,

                       “value”: “stest1

                  },

                  “capability”: {

                        displayName“: “capability”,

                        “value”: “monitoring”

                  }

            }

      }]

}

Lets get started to monitor the database.

[x33245 agentsw]$/c1/oms/agent/agent_inst/bin/omcli add_entity agent oracle_agent_add.json
 -credential_file omc_oracle_db_creds.json
Oracle Management Cloud Agent
Copyright (c) 1996, 2016 Oracle Corporation.  Allrights reserved.
Operation
 Succeeded: Accepted 1 of 1 entities for processing.

Lets check the status of the upload

[x33245 agentsw]$/c1/oms/agent/agent_inst/bin/omcli status_entity agent oracle_agent_add.json
Oracle Management Cloud Agent
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
omc_oracle_db.C_stest1 : AGENT:entity fully monitored
[x33245 agentsw]$

That’s all, Now if we log onto the OMC and drill into the Infastructure Monitoring – we can see the server and see our database being monitored.

We can drill into the database to see Top SQL and Top Wait Events
Also the TableSpace Usage!

Categories
Blog

Cloud – RMAN-06026: some targets not found – aborting restore

 


 

When checking backup status in the Oracle Cloud Database
server, I noticed the following error:

RMAN-06026: some targets not found – aborting restore

RMAN-08526: channel ORA_SBT_TAPE_5: Oracle Database Backup
Service Library VER=3.16.9.21

RMAN-00571:
===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============

RMAN-00571:
===========================================================

RMAN-03002: failure of restore command at 03/19/2017
16:49:39

RMAN-06026: some targets not found – aborting restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1332 and starting SCN of 416745785 found to restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1331 and starting SCN of 416745691 found to restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1330 and starting SCN of 416633416 found to restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1329 and starting SCN of 416632713 found to restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1328 and starting SCN of 415836751 found to restore

RMAN-06025: no backup of archived log for thread 1 with
sequence 1327 and starting SCN of 414639947 found to restore

Solution

Why did this happen, what happened.  Oracle
periodically change the Cloud tooling, when they do this you can see the above
error.  By updating the Cloud Tooling this error was fixed in this
case.  To update or check the version of the cloud tooling you can follow
the steps from the Oracle website.

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/update-cloud-tooling-using-dbpatchm.html#CSDBI-GUID-852CF27F-3312-428A-979E-142D1945E62E

 
 

Categories
Blog

Oracle DBCS (Cloud) Scale Up Storage Steps

We have been alerted that our tablespace in DBCS, is reaching its limit.

Looking at our filesystem, we are using /u02 to store our datafiles and we have used 92%/

If you are running any cloud services that are using this database, you must stop them first

Before we attempt to extend this storage we need to stop all applications that maybe using this this database. Once this is done we log into our Oracle Cloud Service Portal and drill into the Oracle database cloud service we wish to add additional storage.
Once we are in the DBCS pane as per below, we select the burger bar and choose the scale up/down option.

On the next screen we have selected to extend the data storage volume by 100 GB. Then select ‘Yes’.

Once selected, we will see the notification of the ‘Scale Up – in progress’.

Once finished, if we log onto the machine and check the file system, we can see /u02 has been extended.

We can now log onto the database and add the additional data file.
ALTER TABLESPACE “SP761373353_SOAINFRA” ADD DATAFILE SIZE 250M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED

Looking at the table space, we can see the space has been extended.


Restart any cloud services that were shut down for the scale up.
Happy Days J

Categories
Blog

Enabling / Disabling Flashback Database in 11gR2

Flashback database was introduced in Oracle 10g and offer’s a simple way for performing a point in time recovery. To enable this, your database must be running in archivelog mode and flash recovery must be enabled.
In 10g We would have had to issue the following commands:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day
SQL> ALTER DATABASE FLASHBACK ON;

But with 11gR2, We can now turn flashback on / off, when the database is OPEN

SQL> select * from v$version;
BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 PL/SQL Release 11.2.0.4.0 - Production
 CORE 11.2.0.4.0 Production
 TNS for Linux: Version 11.2.0.4.0 - Production
 NLSRTL Version 11.2.0.4.0 - Production

We can check the current status of flashback.

SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
NO OPEN

Lets set the flashback retention target

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day

Now with the database open, we will set the flashback on.

SQL> alter database flashback on;
Database altered.
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 YES OPEN

Now with the database open, we will set the flashback off.

SQL> alter database flashback off;
Database altered.
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 NO OPEN

Lets enable the flashback.

SQL> alter database flashback on;
 Database altered.
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 YES OPEN

Check the flashback retention

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_flashback_retention_target integer 1440

After a while if you run the following you can see how far we can flashback the database.

SQL> alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
 -------------------- -------------------
 5034013702 2016-02-12 16:33:35
Categories
Blog

Standalone Oracle Database Patching with ASM

Some Useful links:
Information Center: Patching and Maintaining Oracle Database Server/Client Installations (Doc ID 1351428.2)
Introduction to Oracle Recommended Patches (Doc ID 756388.1)
Oracle Recommended Patches — Oracle Database (Doc ID 756671.1)
Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)
Before you download any patches, you need to find out what is your current Software version. You should check all the Oracle Homes and patch them as needed. Notice that Grid Infrastructure (with ASM) must be Patched first as it must be on a same or higher level than any Oracle Database Homes.
Log in to the servers as Oracle Software owner (usually oracle), set the proper Oracle environment and run the opatch utility.
. oraenv -> +ASM
$ORACLE_HOME/OPatch/opatch version (opatch version)
$ORACLE_HOME/OPatch/opatch lspatches (show Patches only)
$ORACLE_HOME/OPatch/opatch lsinventory (show Software details)
. oraenv -> DB SID
$ORACLE_HOME/OPatch/opatch version (opatch version)
$ORACLE_HOME/OPatch/opatch lspatches (show Patches only)
$ORACLE_HOME/OPatch/opatch lsinventory (show Software details)
Repeat same steps on all the Oracle Database Homes.
IN Action:
Patch GRID_HOME and ORACLE HOME
1.
[oracle@skynet ~]$. ./asm_profile.sh
[oracle@skynet ~]$ cd $ORACLE_HOME
[oracle@skynet grid]$ cd OPatch
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lspatches
There are no Interim patches installed in this Oracle Home.
[oracle@skynet OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/grid/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2015-10-14_09-19-58AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_09-19-58AM.txt
——————————————————————————–
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————–
OPatch succeeded.
[oracle@skynet ~]$ . ./db_profile.sh
[oracle@skynet ~]$ cd $ORACLE_HOME
[oracle@skynet dbhome_1]$ cd OPatch
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lspatches
There are no Interim patches installed in this Oracle Home.
[oracle@skynet OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-14_09-28-19AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_09-28-19AM.txt
——————————————————————————–
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————–
OPatch succeeded.
Lets download the Patch’s
Now, log in to MOS and, click on “Patches & Updates”, then choose “Recommended Patch Advisor”.
Under “Product”, type “Real Application Clusters”, choose proper Release “11.2.0.4” and Platform “Linux x86-64”, now click on “Check recommendations when used with another product.” and choose “Oracle Clusterware”, “11.2.0.4” and “Linux x86-64”, then “Search”
Result should be this: “Patch 20485808: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.6 (APR2015)” (This includes all the Patches, including Patches for the Database Homes)
Click “Download” to get the Patch
Click “Read Me” to read the Patch details and instructions.
I would recommend to click on “View Related Knowledge to this Patch”. Sometimes it has very useful information about the Patch itself, like “Known issues”, Bug details etc.
Before applying any Oracle patches, make sure you have the latest version of OPatch! The Patch ReadMe has a note about it, and you can download it from here: https://updates.oracle.com/download/6880880.html
Choose Release: “11.2.0.0.0”, Platform: “Linux x86-64”, should get this to download:
“OPatch patch of version 11.2.0.3.11 for Oracle software releases 11.2.0.x (JUN 2015)”
When your download completes, copy these patches on all the Servers you plan to patch and you’re ready for Patching.
1. Log in to the Server as Oracle Software Owner (“oracle”), run the following to check the databases)
select Comp_name, status, Version From Dba_Registry order by Comp_name; (Check that all Components are VALID!)
2. Shutdown all the databases, then the asm,
3. Leave has/listener running.
4.
. Set the environment for the Grid Infrastructure:
. oraenv -> +ASM
5. Backup the current Opatch directory:
[root@skynet grid]# mv OPatch OPatch14102015
3. Check OPatch version and then Update OPatch:
[oracle@skynet OPatch]$ su
Password:
[root@skynet OPatch]# echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/grid
[oracle@skynet OPatch]$ cd /skynet1/source/kiran_oracle_patch_oct2015/OPatch
[oracle@skynet OPatch]$ ls
p6880880_112000_Linux-x86-64.zip
[oracle@skynet OPatch]$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
Archive: p6880880_112000_Linux-x86-64.zip
creating: /u01/app/oracle/product/11.2.0/grid/OPatch/
inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatchdiag
inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatch.pl
creating: /u01/app/oracle/product/11.2.0/grid/OPatch/jlib/
..
..
.
[root@skynet grid]# chown -R oracle:oinstall OPatch
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
. Set the environment for the Oracle Infrastructure:
. oraenv -> DB
1. Backup the current Opatch directory:
[root@skynet grid]# mv OPatch OPatch14102015
3. Check OPatch version and then Update OPatch:
[oracle@skynet OPatch]$ cd /skynet1/source/kiran_oracle_patch_oct2015/OPatch
[oracle@skynet OPatch]$ ls
p6880880_112000_Linux-x86-64.zip
[oracle@skynet OPatch]$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
Archive: p6880880_112000_Linux-x86-64.zip
creating: /u01/app/oracle/product/11.2.0/grid/OPatch/
inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatchdiag
inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatch.pl
creating: /u01/app/oracle/product/11.2.0/grid/OPatch/jlib/
..
..
.
[oracle@skynet OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
4. Create the OCM “dummy” response file:
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -output /tmp/ocm.rsp (You don’t need to specify any details)
[oracle@skynet Grid]$ pwd
/skynet1/source/kiran_oracle_patch_oct2015/Grid
[oracle@skynet Grid]$ unzip p20996923_112040_Linux-x86-64.zip
5. Patch Oracle Grid Infrastructure and Oracle RAC Database Homes together, as “root” user:
. oraenv -> +ASM
$ORACLE_HOME/OPatch/opatch auto /stage/<UNZIPPED_PATCH_LOCATION> -ocmrf /tmp/ocm.rsp
[oracle@skynet OPatch]$ cd /u01/app/oracle/product/11.2.0/grid/OPatch
[oracle@skynet OPatch]$ su
Password:
[root@skynet OPatch]#
[root@skynet OPatch]# ./opatch auto /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923 -ocmrf /tmp/ocm.rsp
Executing /u01/app/oracle/product/11.2.0/grid/perl/bin/perl ./crs/patch11203.pl -patchdir /skynet1/source/kiran_oracle_patch_oct2015/Grid -patchn 20996923 -ocmrf /tmp/ocm.rsp -paramfile /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
This is the main log file: /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2015-10-14_14-20-44.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2015-10-14_14-20-44.report.log
2015-10-14 14:20:44: Starting Oracle Restart Patch Setup
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Stopping RAC /u01/app/oracle/product/11.2.0/dbhome_1 …
Stopped RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully
patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20760982 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20831122/custom/server/20831122 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Stopping CRS…
Stopped CRS successfully
patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20760982 apply successful for home /u01/app/oracle/product/11.2.0/grid
patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20831122 apply successful for home /u01/app/oracle/product/11.2.0/grid
patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20299019 apply successful for home /u01/app/oracle/product/11.2.0/grid
Starting CRS…
CRS-4123: Oracle High Availability Services has been started.
Starting RAC /u01/app/oracle/product/11.2.0/dbhome_1 …
Started RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully
opatch auto succeeded.
You have new mail in /var/spool/mail/oracle
*Note that this is the simplest way to Patch everything. Most of the time I have done this myself, but on more complex systems I choose to apply patches in several different steps, where first I patch Grid Infrastructure and if successful then Oracle Database Homes.
6. When Patching completes, check the screen and log files and confirm if it was successful. Check the software version:
. oraenv -> +ASM
$ORACLE_HOME/OPatch/opatch version (opatch version)
$ORACLE_HOME/OPatch/opatch lspatches (show Patches only)
$ORACLE_HOME/OPatch/opatch lsinventory (show Software details)
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lspatches
20299019;ACFS Patch set update : 11.2.0.4.6 (20299019)
20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122)
20760982;Database Patch Set Update : 11.2.0.4.7 (20760982)
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/grid/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2015-10-14_15-09-05PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_15-09-05PM.txt
——————————————————————————–
Local Machine Information::
Hostname: skynet.op.skynetglobal.net
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (3) :
Patch 20299019 : applied on Wed Oct 14 15:02:37 BST 2015
Unique Patch ID: 18573450
Patch description: “ACFS Patch set update : 11.2.0.4.6 (20299019)”
Created on 27 Mar 2015, 15:26:30 hrs
Bugs fixed:
17510275, 17172303, 16318126, 19690653, 17203009, 17376318, 17503605
20140148, 17611362, 17721778, 17164243, 19053182, 17696547, 17699423
17488768, 18915417, 18155334, 18321597, 19919907, 18168684, 17363999
17636008, 18143006, 17428148, 17070158, 17475946
Patch 20831122 : applied on Wed Oct 14 15:01:59 BST 2015
Unique Patch ID: 18923533
Patch description: “OCW Patch Set Update : 11.2.0.4.7 (20831122)”
Created on 1 Jul 2015, 06:26:45 hrs PST8PDT
Bugs fixed:
19270660, 18328800, 18691572, 20365005, 17750548, 17387214, 17617807
14497275, 17733927, 18180541, 18962892, 17292250, 17378618, 16759171
20110156, 17065496, 13991403, 17273020, 17155238, 18261183, 18053580
20218012, 17039197, 17947785, 16317771, 10052729, 20340620, 18199185
18399991, 20186278, 18024089, 20746251, 20246071, 14270845, 18414137
17001914, 17927970, 14378120, 16346413, 15986647, 18068871, 21222147
18143836, 16206997, 19168690, 20235511, 18343490, 16613232, 19276791
17722664, 12928658, 18520351, 16249829, 18226143, 18265482, 18229842
17172091, 17818075, 18231837, 14373486, 17483479, 18120545, 18729166
13843841, 21225209, 17405302, 18709496, 18330979, 18744838, 20531190
14525998, 18187697, 14385860, 18348155, 19479503, 12928592, 17516024
18370031, 17764053, 17551223, 14671408, 18272135, 14207615, 17500165
18875012, 18464784, 19558324, 18848125, 19241857, 14851828, 17955615
20315294, 14693336, 16284825, 17352230, 20014326, 17238586, 17089344
17405605, 17531342, 17159489, 17640316, 16543190, 17983675, 17481314
16281493, 18346135, 15986311, 17208793, 18700935, 18999857, 14076173
18428146, 18352845, 17435488, 18352846, 17391726, 17387779, 16206882
20141091, 17305100, 15832129, 19885321, 16901346, 17985714, 18536826
17780903, 18752378, 18946768, 16876500, 16875342, 19955755, 16429265
18336452, 17273003, 19319357, 17059927, 17046460, 18053631, 16867761
20235486, 15869775, 19642566, 17447588, 15920201
Patch 20760982 : applied on Wed Oct 14 14:56:51 BST 2015
Unique Patch ID: 18908105
Patch description: “Database Patch Set Update : 11.2.0.4.7 (20760982)”
Created on 4 Jun 2015, 00:23:20 hrs PST8PDT
Sub-patch 20299013; “Database Patch Set Update : 11.2.0.4.6 (20299013)”
Sub-patch 19769489; “Database Patch Set Update : 11.2.0.4.5 (19769489)”
Sub-patch 19121551; “Database Patch Set Update : 11.2.0.4.4 (19121551)”
Sub-patch 18522509; “Database Patch Set Update : 11.2.0.4.3 (18522509)”
Sub-patch 18031668; “Database Patch Set Update : 11.2.0.4.2 (18031668)”
Sub-patch 17478514; “Database Patch Set Update : 11.2.0.4.1 (17478514)”
Bugs fixed:
17288409, 21051852, 18607546, 17205719, 17811429, 17816865, 20506699
17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817
16992075, 17446237, 14015842, 19972569, 17449815, 17375354, 19463897
17982555, 17235750, 13866822, 18317531, 17478514, 18235390, 14338435
20803583, 13944971, 20142975, 17811789, 16929165, 18704244, 20506706
17546973, 20334344, 14054676, 17088068, 18264060, 17346091, 17343514
19680952, 18471685, 19211724, 13951456, 16315398, 18744139, 16850630
19049453, 18673304, 17883081, 19915271, 18641419, 18262334, 17006183
18277454, 16833527, 10136473, 18051556, 17865671, 17852463, 18554871
17853498, 18334586, 17588480, 17551709, 19827973, 17842825, 17344412
18828868, 17025461, 11883252, 13609098, 17239687, 17602269, 19197175
18316692, 17313525, 12611721, 19544839, 18964939, 17600719, 18191164
19393542, 17571306, 20777150, 18482502, 19466309, 17040527, 17165204
18098207, 16785708, 17174582, 16180763, 17465741, 16777840, 12982566
19463893, 12816846, 16875449, 17237521, 19358317, 17811438, 17811447
17945983, 18762750, 17184721, 16912439, 18061914, 17282229, 18331850
18202441, 17082359, 18723434, 19554106, 14034426, 18339044, 19458377
17752995, 17891943, 17258090, 17767676, 16668584, 18384391, 17040764
17381384, 15913355, 18356166, 14084247, 20506715, 13853126, 18203837
14245531, 16043574, 17848897, 17877323, 17468141, 17786518, 17912217
17037130, 18155762, 16956380, 17478145, 17394950, 18189036, 18641461
18619917, 17027426, 16268425, 19584068, 18436307, 17265217, 17634921
13498382, 20004087, 17443671, 18000422, 20004021, 17571039, 21067387
16344544, 18009564, 14354737, 18135678, 18614015, 18362222, 17835048
16472716, 17936109, 17050888, 17325413, 14010183, 18747196, 17761775
16721594, 17082983, 20067212, 21179898, 17302277, 18084625, 15990359
18203835, 17297939, 16731148, 17811456, 17215560, 13829543, 14133975
17694209, 18091059, 17385178, 8322815, 17586955, 17201159, 17655634
18331812, 19730508, 18868646, 17648596, 16220077, 16069901, 17348614
17393915, 17957017, 17274537, 18096714, 17308789, 18436647, 14285317
19289642, 14764829, 18328509, 17622427, 16943711, 14368995, 17346671
18996843, 17783588, 16618694, 17672719, 18856999, 18783224, 17851160
17546761, 17798953, 18273830, 19972566, 16384983, 17726838, 17360606
13645875, 18199537, 16542886, 17889549, 14565184, 17071721, 20299015
17610798, 20657441, 17397545, 18230522, 16360112, 19769489, 12905058
18641451, 12747740, 18430495, 17042658, 17016369, 14602788, 19972568
19788842, 18508861, 14657740, 17332800, 13837378, 19972564, 17186905
18315328, 19699191, 17437634, 19006849, 19013183, 17296856, 18674024
17232014, 16855292, 21051840, 14692762, 17762296, 17705023, 19121551
19854503, 19309466, 18681862, 20558005, 17390160, 18554763, 18456514
13955826, 16306373, 18139690, 17501491, 17299889, 17752121, 17889583
18673325, 18293054, 17242746, 17951233, 17649265, 18094246, 19615136
17011832, 16870214, 17477958, 18522509, 20631274, 16091637, 17323222
16595641, 16524926, 18228645, 18282562, 17596908, 17156148, 18031668
16494615, 17545847, 17614134, 13558557, 17341326, 17891946, 17716305
16392068, 19271443, 18092127, 17614227, 18440047, 14106803, 16903536
18973907, 18673342, 17389192, 16194160, 17006570, 17612828, 17721717
17570240, 17390431, 16863422, 18325460, 19727057, 16422541, 17267114
19972570, 18244962, 18765602, 18203838, 16198143, 17246576, 14829250
17835627, 18247991, 14458214, 21051862, 16692232, 17786278, 17227277
16042673, 16314254, 16228604, 16837842, 17393683, 17787259, 20331945
20074391, 15861775, 16399083, 18018515, 21051858, 18260550, 17036973
16613964, 17080436, 16579084, 18384537, 18280813, 20296213, 16901385
15979965, 18441944, 16450169, 9756271, 17892268, 11733603, 17587063
16285691, 16538760, 18180390, 18193833, 21051833, 17238511, 17824637
16571443, 18306996, 14852021, 18674047, 17853456, 12364061
——————————————————————————–
OPatch succeeded.
[oracle@skynet OPatch]$
. oraenv -> DB SID
$ORACLE_HOME/OPatch/opatch version (opatch version)
$ORACLE_HOME/OPatch/opatch lspatches (show Patches only)
$ORACLE_HOME/OPatch/opatch lsinventory (show Software details)
[oracle@skynet OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lspatches
20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122)
20760982;Database Patch Set Update : 11.2.0.4.7 (20760982)
OPatch succeeded.
[oracle@skynet OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-14_15-10-17PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_15-10-17PM.txt
——————————————————————————–
Local Machine Information::
Hostname: skynet.op.skynetglobal.net
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (2) :
Patch 20831122 : applied on Wed Oct 14 14:27:35 BST 2015
Unique Patch ID: 18923533
Patch description: “OCW Patch Set Update : 11.2.0.4.7 (20831122)”
Created on 1 Jul 2015, 06:26:39 hrs PST8PDT
Bugs fixed:
19270660, 18328800, 18691572, 20365005, 17750548, 17387214, 17617807
14497275, 17733927, 18180541, 18962892, 17292250, 17378618, 16759171
20110156, 17065496, 13991403, 17273020, 17155238, 18261183, 18053580
20218012, 17039197, 17947785, 16317771, 10052729, 20340620, 18199185
18399991, 20186278, 18024089, 20746251, 20246071, 14270845, 18414137
17001914, 17927970, 14378120, 16346413, 15986647, 18068871, 21222147
18143836, 16206997, 19168690, 20235511, 18343490, 16613232, 19276791
17722664, 12928658, 18520351, 16249829, 18226143, 18265482, 18229842
17172091, 17818075, 18231837, 14373486, 17483479, 18120545, 18729166
13843841, 21225209, 17405302, 18709496, 18330979, 18744838, 20531190
14525998, 18187697, 14385860, 18348155, 19479503, 12928592, 17516024
18370031, 17764053, 17551223, 14671408, 18272135, 14207615, 17500165
18875012, 18464784, 19558324, 18848125, 19241857, 14851828, 17955615
20315294, 14693336, 16284825, 17352230, 20014326, 17238586, 17089344
17405605, 17531342, 17159489, 17640316, 16543190, 17983675, 17481314
16281493, 18346135, 15986311, 17208793, 18700935, 18999857, 14076173
18428146, 18352845, 17435488, 18352846, 17391726, 17387779, 16206882
20141091, 17305100, 15832129, 19885321, 16901346, 17985714, 18536826
17780903, 18752378, 18946768, 16876500, 16875342, 19955755, 16429265
18336452, 17273003, 19319357, 17059927, 17046460, 18053631, 16867761
20235486, 15869775, 19642566, 17447588, 15920201
Patch 20760982 : applied on Wed Oct 14 14:26:47 BST 2015
Unique Patch ID: 18908105
Patch description: “Database Patch Set Update : 11.2.0.4.7 (20760982)”
Created on 4 Jun 2015, 00:23:20 hrs PST8PDT
Sub-patch 20299013; “Database Patch Set Update : 11.2.0.4.6 (20299013)”
Sub-patch 19769489; “Database Patch Set Update : 11.2.0.4.5 (19769489)”
Sub-patch 19121551; “Database Patch Set Update : 11.2.0.4.4 (19121551)”
Sub-patch 18522509; “Database Patch Set Update : 11.2.0.4.3 (18522509)”
Sub-patch 18031668; “Database Patch Set Update : 11.2.0.4.2 (18031668)”
Sub-patch 17478514; “Database Patch Set Update : 11.2.0.4.1 (17478514)”
Bugs fixed:
17288409, 21051852, 18607546, 17205719, 17811429, 17816865, 20506699
17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817
16992075, 17446237, 14015842, 19972569, 17449815, 17375354, 19463897
17982555, 17235750, 13866822, 18317531, 17478514, 18235390, 14338435
20803583, 13944971, 20142975, 17811789, 16929165, 18704244, 20506706
17546973, 20334344, 14054676, 17088068, 18264060, 17346091, 17343514
19680952, 18471685, 19211724, 13951456, 16315398, 18744139, 16850630
19049453, 18673304, 17883081, 19915271, 18641419, 18262334, 17006183
18277454, 16833527, 10136473, 18051556, 17865671, 17852463, 18554871
17853498, 18334586, 17588480, 17551709, 19827973, 17842825, 17344412
18828868, 17025461, 11883252, 13609098, 17239687, 17602269, 19197175
18316692, 17313525, 12611721, 19544839, 18964939, 17600719, 18191164
19393542, 17571306, 20777150, 18482502, 19466309, 17040527, 17165204
18098207, 16785708, 17174582, 16180763, 17465741, 16777840, 12982566
19463893, 12816846, 16875449, 17237521, 19358317, 17811438, 17811447
17945983, 18762750, 17184721, 16912439, 18061914, 17282229, 18331850
18202441, 17082359, 18723434, 19554106, 14034426, 18339044, 19458377
17752995, 17891943, 17258090, 17767676, 16668584, 18384391, 17040764
17381384, 15913355, 18356166, 14084247, 20506715, 13853126, 18203837
14245531, 16043574, 17848897, 17877323, 17468141, 17786518, 17912217
17037130, 18155762, 16956380, 17478145, 17394950, 18189036, 18641461
18619917, 17027426, 16268425, 19584068, 18436307, 17265217, 17634921
13498382, 20004087, 17443671, 18000422, 20004021, 17571039, 21067387
16344544, 18009564, 14354737, 18135678, 18614015, 18362222, 17835048
16472716, 17936109, 17050888, 17325413, 14010183, 18747196, 17761775
16721594, 17082983, 20067212, 21179898, 17302277, 18084625, 15990359
18203835, 17297939, 16731148, 17811456, 17215560, 13829543, 14133975
17694209, 18091059, 17385178, 8322815, 17586955, 17201159, 17655634
18331812, 19730508, 18868646, 17648596, 16220077, 16069901, 17348614
17393915, 17957017, 17274537, 18096714, 17308789, 18436647, 14285317
19289642, 14764829, 18328509, 17622427, 16943711, 14368995, 17346671
18996843, 17783588, 16618694, 17672719, 18856999, 18783224, 17851160
17546761, 17798953, 18273830, 19972566, 16384983, 17726838, 17360606
13645875, 18199537, 16542886, 17889549, 14565184, 17071721, 20299015
17610798, 20657441, 17397545, 18230522, 16360112, 19769489, 12905058
18641451, 12747740, 18430495, 17042658, 17016369, 14602788, 19972568
19788842, 18508861, 14657740, 17332800, 13837378, 19972564, 17186905
18315328, 19699191, 17437634, 19006849, 19013183, 17296856, 18674024
17232014, 16855292, 21051840, 14692762, 17762296, 17705023, 19121551
19854503, 19309466, 18681862, 20558005, 17390160, 18554763, 18456514
13955826, 16306373, 18139690, 17501491, 17299889, 17752121, 17889583
18673325, 18293054, 17242746, 17951233, 17649265, 18094246, 19615136
17011832, 16870214, 17477958, 18522509, 20631274, 16091637, 17323222
16595641, 16524926, 18228645, 18282562, 17596908, 17156148, 18031668
16494615, 17545847, 17614134, 13558557, 17341326, 17891946, 17716305
16392068, 19271443, 18092127, 17614227, 18440047, 14106803, 16903536
18973907, 18673342, 17389192, 16194160, 17006570, 17612828, 17721717
17570240, 17390431, 16863422, 18325460, 19727057, 16422541, 17267114
19972570, 18244962, 18765602, 18203838, 16198143, 17246576, 14829250
17835627, 18247991, 14458214, 21051862, 16692232, 17786278, 17227277
16042673, 16314254, 16228604, 16837842, 17393683, 17787259, 20331945
20074391, 15861775, 16399083, 18018515, 21051858, 18260550, 17036973
16613964, 17080436, 16579084, 18384537, 18280813, 20296213, 16901385
15979965, 18441944, 16450169, 9756271, 17892268, 11733603, 17587063
16285691, 16538760, 18180390, 18193833, 21051833, 17238511, 17824637
16571443, 18306996, 14852021, 18674047, 17853456, 12364061
 
——————————————————————————–
OPatch succeeded.
etc.
7. Patch post-installation instructions. These are the steps you need to run on every Database part of the Oracle Database Homes you just patched.
. oraenv -> DB SID
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL> @catbundle.sql psu apply
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_REPODEV_APPLY_2015Oct14_15_15_18.log
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_SANADEV_APPLY_2015Oct14_15_19_13.log
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_S82DEV1_APPLY_2015Oct14_15_57_27.log
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_S82DEV2_APPLY_2015Oct14_16_02_20.log
SQL> @utlrp.sql
Check Patch status in the database:
SQL> select * from dba_registry_history;
SQL> set lines 140 pages 100
SQL> col Comp_name Format a40
SQL> col Status Format a12
SQL> select Comp_name, status, Version From Dba_Registry order by Comp_name; (Check that all Components are VALID!)
!!! Notice that:
If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
And you’re done! 🙂
 

Categories
Blog

Oracle OpenWorld 2015

Oracle Openworld is an annual Oracle convention for business decision-makers, IT management, and line of business end users. The conference normally takes place around mid-September, this year it was late October. Presentations take place by Oracle, Consulting companies as well as community members. I believe the presentations by the community provide the best information.
Around February/March I submitted a paper to present, unfortunately was not selected this year. This year Oracle were looking for the word cloud in the topics. The extent people go to so that there paper is selected is amazing. I was speaking to one person and they submitted the same paper 3 times worded differently, 1 of them had the word cloud in the title and it was accepted. So it’s very important that you pay attention to what they are looking for and base your presentation around these areas.
I have to say I really enjoy going to Openworld and this was my fourth time, you will always learn new things and also get to network with different Oracle product teams as well as community members all in 1 place.
On my flight over, I think half the people on the plane were attending Openworld and it was great to pass the time speaking to various community members on the flight over to San Francisico.
For me, the following announcements were important, If you are thinking of upgrading, I have marked the key benefits to upgrade with a ‘*’:
1) OBIEE 12C – Oracle BI 12c is now generally available. BI 12c is the strategic foundation of Oracle’s analytics platform, delivering significant new value and lower total cost of ownership.
*Easy to upgrade: BI 12c offers a radically simple and robust upgrade from 11g, saving customers time and effort in moving to the new version. BI 12c includes a free utility to automate regression testing, the Baseline Validation Tool, which verifies data, visuals, catalog object metadata, and system-generated SQL across 11g and 12c releases.
*Faster: Sophisticated in-memory processing includes BI Server optimizations and support for multiple in-memory data stores, while in-memory Essbase on Exalytics offers enhanced concurrency and scalability, as well as significant performance gains.
*Friendlier: Usability updates throughout BI 12c demonstrate Oracle’s continued commitment to making analytics as fast, flexible, and friendly as they are powerful and robust. A new user interface simplifies the layout for the homepage, Answers, and Dashboards, making it easier for users to quickly see what’s important; HTML-5 graphics improve view rendering; and it’s easier for users to create new groups, calculations, and measures, for simpler, more direct interaction with results.
(Chargeable) More Visual: A consistent set of Data Visualization capabilities are now available across Oracle BI Cloud Service and Oracle BI 12c, as well as the upcoming Oracle Data Visualization Cloud Service, offering customers a continuity of visual experience unmatched by our competitors. Business users can point and click to upload personal data and blend it with IT-managed data in BI 12c, which automatically infers connections between data sets. Visualizing data is as easy as dragging attributes onto the screen, with optimal visualizations automatically displayed – no modeling or upfront configuration required. Related data is automatically connected, so highlighting data in one visual highlights correlated data in every other visual, immediately showing patterns and revealing new insights. These insights, along with narrative comments, can be shared as interactive visual stories, enabling seamless collaboration that drives fact-based decisions. – This is an additional license if you want to use the Visual Analyzer (VA) which is a shame, you will need to speak to your local Oracle Representative for pricing.
*More Advanced: Predictive analysis is more tightly integrated, enabling customers to more easily forecast future conditions based on existing data points, group elements that are statistically similar, and expose outliers. BI 12c includes the ability to run the free Oracle R distribution on BI Server, and extend existing analytics with custom R scripts, which can point to any engine (R, Oracle Database, Spark, etc.) without needing to change the BI RPD to deliver results.
More Mobile: Keyword search (“BI Ask”) empowers users to literally talk to their data, asking questions and having visualizations automatically created as responses, opening up an easy entry point for authoring. Additionally, the interface for iOS has been completely redesigned; and Mobile BI for Android offers sharing and following for nearby devices, as well as the ability to project any dashboard or story to GoogleCast-enabled devices.
Bigger Data: BI 12c enables customers to use new data, from local, Oracle, and Big Data sources, including personal files uploaded by users; direct access to data in Hyperion Financial Management and Hyperion Planning applications; and ODBC access to Cloudera Impala. Apache Spark will be accessible via native Spark SQL in an upcoming update.
Higher ROI, Lower TCO: Streamlined administration and life cycle management reduce the time and resources required to manage BI 12c, decreasing costs and increasing value for this and future releases. Enhancements include separating metadata from configuration; simpler, more robust security; easier backup, restore, and disaster recovery; hot patching of metadata; and many more.
2) Oracle Database 12c Release 2 – Beta
Continuing to lead the database industry with ongoing innovations, Oracle today introduced beta availability for Oracle Database 12c Release 2. With its unique multitenant architecture and dual format in-memory database technology, Oracle Database 12c is already helping customers embrace the cloud, deliver real-time analytics and harness Big Data.
Oracle Database 12c Release 2 further simplifies the journey to the cloud and provides efficient delivery of database services, with greater consolidation density and more administrative agility at cloud scale. Focus areas that are planned for Oracle Database 12c Release 2 include:
* Improved Agility and Capacity with Enhancements to Oracle Multitenant: Planned enhancements include online movement of pluggable databases between servers, data centers and the Oracle Cloud.
* Refinements to Oracle Database In-Memory: Features to boost performance, automate management, now you can use in-memory on your active standby
Enhanced Support for Big DataAnalytics: In addition to enhancements to Database In-Memory, plans include spatial performance improvements and improved support for graph analytics.
Hundreds of other new features: Drivers for all the key programming languages and frameworks have been enhanced. The release includes improvements to all the other main areas of customer interest including security, high availability, scalability, performance, and manageability.
If you were thinking of going, make sure you get ready for OpenWorld 2016, which will take place around 16th-20th September 2016.

Categories
Blog

Identifying Top SQL using statspack

So you don’t have diagnostic or the tuning pack for your databases, but you have started collecting information using Oracle Statspack that i mentioned in one of my previous posts. As well as running normal reports, you can also run sql directly against the tables to gather information on SQL Performance.   If you log into your DB using the user collecting the stats and run the following sql.

col TEXT_SUBSET format a32
col SQL_TEXT format a90
set linesize 200
set pagesize 2000
alter session set nls_date_format = 'DD-MON-YY hh24:mi:ss';
select * from
(
 select SQL_ID, OLD_HASH_VALUE, EXECUTIONS
 -- , READS/EXECUTIONS READS_PER_EXEC
 -- , WRITES/EXECUTIONS WRITES_PER_EXEC
 -- , (CPU/1000000)/EXECUTIONS CPU_SEC_PER_EXEC
 , (ELAPSED/1000000)/EXECUTIONS ELAPSED_SEC_PER_EXEC
 , TEXT_SUBSET
 -- , SQL_TEXT
 from
 (
 select SQL_ID, OLD_HASH_VALUE, TEXT_SUBSET, SQL_TEXT, sum(EXECUTIONS) EXECUTIONS, sum(DISK_READS) READS, sum(DIRECT_WRITES) WRITES, sum(CPU_TIME) CPU, sum(ELAPSED_TIME) ELAPSED
 from
 PERFSTAT.STATS$SQL_SUMMARY sum,
 PERFSTAT.STATS$SNAPSHOT snap
 where sum.snap_id=snap.snap_id
 and
 -- Last hour
-- SNAP_TIME > sysdate -1/24
 -- Last day
 -- SNAP_TIME > sysdate -1
 -- Yesterday
 SNAP_TIME between trunc(sysdate -1)+8/24 and trunc(sysdate -1)+20/24
 --SNAP_TIME between to_date('14-MAY-2015 08:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('14-MAY-2015 20:00:00','DD-MON-YYYY HH24:MI:SS')
 group by SQL_ID, OLD_HASH_VALUE, TEXT_SUBSET, SQL_TEXT
 )
 where EXECUTIONS > 0
 order by 4 desc
)
-- limit the amount of rows returned
where rownum < 75
;

You will see a list like this:

SQL_ID        OLD_HASH_VALUE EXECUTIONS ELAPSED_SEC_PER_EXEC TEXT_SUBSET
------------- -------------- ---------- -------------------- --------------------------------
ba9xcjfhwgfum     3360270111          1            511.96071 INSERT /*+APPEND*/ INTO S_ETL_I
c16hxw57t3tck     1238139196          1           468.807817 select trunc(a.last_upd),a.evt_
cwy7xav47930r     2774232425          1           462.835921 insert into siebel.email_resp_2
fg7jqcyf81nz0      404877695          1           366.523411 SELECT /*+ ALL_ROWS */       T9
d7cz4p5b6nq64      931021206          1           326.530129 INSERT /*+APPEND*/ INTO S_ETL_I
63tfvvwpn9wyq     3808620702         10            257.67863 SELECT       T33.CONFLICT_ID,
0qg98ks85f5mv     4182116347          3           228.984559 select * from CX_ERROR_MSG m wh
7ap024kdpncy4     2300540696          3           155.879721 select * from CX_ERROR_MSG m wh
0j4jsdd258tyn     1522532907          1           153.883791 INSERT /*+APPEND*/ INTO S_ETL_I
aa9ggv3h5dfrw     1185165122          1           144.548821 SELECT /*+ ALL_ROWS */       T9
f0p618u0609sz     1397351685          9           140.105373 SELECT       T31.CONFLICT_ID,
cqmwa23np60dg      484508739          2           121.701425 update S_ORDER o set O.STATUS_C
g38k23ujhtqzh     3885134350          1           114.190877 INSERT /*+APPEND*/ INTO S_ETL_I
7jrnvpmkbu1r8      358282260          1           112.995241 INSERT /*+APPEND*/ INTO S_ETL_I
Ideally you want to import this in Excel, then filter.  This allow you to order on any of the columns such as the sql that took the longest, to what sql was being executed repeatedly and taking time.  Thereafter find the sql using the sql hash value to see the full sql.  You can amend the sql to choose how many days you want to go back, the above sql looks back 24 hours.