Tag Archives: Rajasekhar Amudala

YouTube

1. How to Install Oracle 19c on Linux 7

2. How to create Oracle Database using DBCA?

3. How to Drop Database Manually?

4. How to start database?

5. How to Shutdown Database?

6. How to connect as SYSDBA?

7. How to connect to database remotely in SQL Developer?

8.Install Oracle 19c database software in silent mode

9. Create database in silent mode.

Install 18c

Install Oracle 18c Release 3 on RHEL 6.8

Pre-requisites already been taken care like memory, swap, kernel parameters, add groups, profile and users…etc

Contents
___________________________________________________________________________________________________

1. Hardware Requirements
2. Verify OS version
3. Unzip 18c Binaries
4. Set the DISPLAY Environment
5. Invoke ./runInstaller
6. Verify
___________________________________________________________________________________________________

1. Hardware Requirements

The first thing we need to verify the hardware requirements for an Oracle 18c Release 3

— Check Physical RAM.
# grep MemTotal /proc/meminfo
We need at least 8192 MB of physical RAM. <----

— Check Swap Space.
# grep SwapTotal /proc/meminfo/*
RAM up to 1024MB then swap = 2 times the size of RAM
RAM between 2049MB and 8192MB then swap = equal to the size of RAM
RAM more than 8192MB then swap size = 0.75 times the size of RAM

We need at least 8192 MB of swap <----


-– Check space available in /tmp
# df -h /tmp/*
You need to have at least 2048 MB of space in the /tmp directory. <---

-– Check space for Oracle Software and pre-configured database.
# df -h

Space requirement for Oracle 18c Software:
Enterprise Edition 14G  <----

—- To determine whether the system architecture can run the software, enter the following command:

# grep "model name" /proc/cpuinfo

This command displays the processor type. Verify that the processor architecture matches the Oracle software release that you want to install. If you do not see the expected output, then you cannot install the software on this system.


2. Verify OS version

[oracle@rac1 product]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
[oracle@rac1 product]$


3. Unzip 18c Binaries

NOTE: You can't edit oracle home location while installation using OUI. It will pickup automatically ORACLE HOME location, where you have unzipped database binaries. Hence directly unzip in ORACLE HOME location and then start ./runInstaller

After unzip, it will NOT keep all files in single directory.

[oracle@rac1 ]cd /u02/app/oracle/product/18c
[oracle@rac1 18c]$ ls -ltr
total 4457672
-rw-r--r--. 1 oracle dba 4564649047 Jul 25 20:26 LINUX.X64_180000_db_home.zip
[oracle@rac1 18c]$

[oracle@rac1 18c]$ unzip LINUX.X64_180000_db_home.zip

[oracle@rac1 18c]$ ls -ltr
total 4457972
-rw-r--r--.  1 oracle dba        852 Aug 18  2015 env.ora
-rw-r--r--.  1 oracle dba       2927 Oct 14  2016 schagent.conf
-rwxr-x---.  1 oracle dba       1783 Mar  8  2017 runInstaller <----
drwxr-x---.  7 oracle dba       4096 Feb  7 23:11 xdk
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 wwg
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 usm
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 suptools
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:11 srvm
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 sqlj
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 slax
-rw-r-----.  1 oracle dba         10 Feb  7 23:11 root.sh.old.1
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 relnotes
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 racg
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 R
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 owm
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 oss
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 ordim
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 oracore
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 olap
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 mgw
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:11 log
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 has
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 dv
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 diagnostics
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 dbjava
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 data
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 css
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:12 plsql
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 dbs
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 utl
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 instantclient
drwxr-xr-x. 13 oracle dba       4096 Feb  7 23:12 dmu
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:12 ucp
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:12 jdbc
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 QOpatch
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:12 ords
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:13 sdk
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:13 apex
drwxr-xr-x. 22 oracle dba       4096 Feb  7 23:13 sqldeveloper
drwxr-xr-x.  8 oracle dba       4096 Feb  7 23:13 odbc
drwxr-xr-x. 11 oracle dba       4096 Feb  7 23:14 ctx
-rwx------.  1 oracle dba        786 Feb  7 23:14 root.sh.old
drwxr-x---. 14 oracle dba       4096 Jun  4 03:20 OPatch
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:17 sqlplus
-rwx------.  1 oracle dba        638 Jul 18 19:18 root.sh
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:39 jlib
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:41 sqlpatch
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 jdk
drwxr-xr-x. 13 oracle dba       4096 Jul 18 19:44 rdbms
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:44 precomp
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 perl
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 ord
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 opmn
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 nls
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:44 network
drwxr-xr-x.  9 oracle dba       4096 Jul 18 19:44 md
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:44 ldap
drwxr-xr-x.  8 oracle dba       4096 Jul 18 19:44 javavm
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 hs
drwxr-xr-x.  4 oracle dba       4096 Jul 18 19:44 drdaas
drwxr-xr-x.  3 oracle dba       4096 Jul 18 19:44 demo
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 cv
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:44 crs
drwxr-xr-x.  9 oracle dba       4096 Jul 18 19:44 assistants
drwxr-xr-x.  8 oracle dba       4096 Jul 18 19:44 oui
drwxr-xr-x.  3 oracle dba      16384 Jul 18 19:44 lib
drwxr-x---. 13 oracle dba       4096 Jul 18 19:44 inventory
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 deinstall
drwxr-xr-x.  4 oracle dba       4096 Jul 18 19:44 clone
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:44 bin
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:44 addnode
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:53 install
-rw-r--r--.  1 oracle dba 4564649047 Jul 25 20:26 LINUX.X64_180000_db_home.zip
[oracle@rac1 18c]$


4. Set the DISPLAY Environment

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

export DISPLAY=192.168.2.3:0.0;


5. Invoke ./runInstaller

Start the Oracle Universal Installer (OUI) by issuing the following command.

[oracle@rac1 ~]$ cd /u02/app/oracle/product/18c/
[oracle@rac1 18c]$ ./runInstaller         <-----
Launching Oracle Database Setup Wizard...

















6. Verify

[oracle@rac1 bin]$ export ORACLE_HOME=/u02/app/oracle/product/18c
[oracle@rac1 bin]$ ./sqlplus

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jul 27 13:16:55 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter user-name:

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

ASH

ASH Report – ASH (Active Session History) Reports

1. Overview
2. Ways of Gathering ASH
3. Desc V$active_session_history
4. ASH Parameters


1. Overview

Oracle ASH report introduced on Oracle 10GR2.

ASH is helpful to identify performance issues in old sessions

ASH collects samples of active sessions every second (waiting for non-idle events, or on the CPU working) from v$sessions (inactive sessions are not captured).

Sampled data collected to circular buffer in SGA and the same can be accessed through V$ views. V$ACTIVE_SESSION_HISTORY view provides the sampled session activity for the instance. 

Using the Active Session History you can examine and perform the detailed analysis on the current data in the V$ACTIVE_SESSION_HISTORY and the past data in the DBA_HIST_ACTIVE_SESS_HISTORY view.

ASH report is a small report compared to the AWR report which will provide the db/instance details for a short period of time.

ASH report covers a much shorter period of time (e.g. 5 min) compared to an AWR report (e.g. 30 min or 1 hour). 

ASH report captures the following things.

SQL identifier of SQL statement
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier

Major ASH report sections are:

Top User Events
Top Background Events
Top Cluster Events
Top Service/Module
Top SQL Command Types
Top Phases of Execution
Top Remote Instances
Top SQL with Top Events
....


2. Ways of Gathering ASH

2.1 Using ORADEBUG

ASHDUMP needs to be collected DURING the time of the issue
In case collecting from RAC , Gather ASH Dumps from ALL database instances at approximately the same time
SQL> oradebug setmypid 
SQL> oradebug unlimit
SQL> oradebug dump ashdump 5   # this will gather 5 minutes of ASH data, you may increase this if you feel necessary but try to keep it under 1 hour 
SQL> oradebug tracefile_name   # displays the trace file


2.2 Using ashrpt.sql or Enterprise Manager

ashrpt.sql
	SQL> @?/rdbms/admin/ashrpt
	Enter value for report_type:  HTML or TEXT [ HTML is default ]
	Enter value for begin_time: 
		Defaults to -15 mins
		To specify absolute begin time: [MM/DD[/YY]] HH24:MI[:SS]
		To specify relative begin time: (start with '-' sign) -[HH24:]MI
	Enter value for duration: Defualt is till current time
	Enter value for report_name:

2.3 Using v$active_session_history

Please note covering only ashrpt.sql here.

Output: Only ashrpt.sql

Example: If you want to take ASH report between 

Start time: 31-Oct-16 14:30:00
End time  : 31-Oct-16 14:45:00

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @?/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3175693255 W148P               1 w148p


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3175693255        1 W148P        w148p        rac2.rajasek
                                                har.com


Defaults to current database

Using database id: 3175693255

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  31-Oct-16 16:07:48   [     64 mins in the past]
Latest ASH sample available:  31-Oct-16 17:11:43   [      0 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: 10/31/16 14:30:00 <---- MM/DD/YY HH24:MI:SS
Report begin time specified: 10/31/16 14:30:00

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 15 <--- Enter duration in minutes starting from begin time
Report duration specified:   15

Using 31-Oct-16 14:30:00 as report begin time
Using 31-Oct-16 14:45:00 as report end time
..
..
..
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_1031_1445.html.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: raj_ash.html <----

Using the report name raj_ash.html

Summary of All User Input
-------------------------
Format         : HTML
DB Id          : 3175693255
Inst num       : 1
Begin time     : 31-Oct-16 14:30:00
End time       : 31-Oct-16 14:45:00
Slot width     : Default
Report targets : 0
Report name    : raj_ash.html
..
..
Report written to raj_ash.html
SQL>


3. Desc V$active_session_history


TIME_WAITED

TIME_WAITED  If the session was in a WAITING state, this column will show how long it had been waiting when Active Session History took the sample It captures information on active sessions every second
WAIT_TIME  If the session is doing productive work—not in a WAITING state—this column will show how long the session waited for the last wait event.


DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. 
This view contains snapshots of V$ACTIVE_SESSION_HISTORY.


4. ASH Parameters

_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:
–https://savvinov.com/2013/10/14/ash-basics/

Thank you,
Rajasekhar Amudala

AWR

AWR Report – AWR (Automatic Workload Repository) reports

0. Overview
1. AWR Setup
2. Find AWR snapshot interval and retention settings
3. Display snapshots
4. AWR Report Generation
5. Change AWR snapshot interval
6. Change AWR snapshot retention
7. Take AWR snapshot manually
8. Purge AWR Snapshots
9. How to find the snap_id for sql_text
10. Generating an AWR Report for a Single SQL Statement
11. Useful views

0. Overview

AWR Report – AWR (Automatic Workload Repository) reports are a useful diagnostic tool for the determination of the potential cause of database wide performance issues. 

Typically when a performance issue is detected you would collect an AWR report covering the period of the poor performance.

It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost.

Capture performed by MMON process

Captures data from V$ performance views

		–  Cumulative data e.g. V$SYSSTAT
		–  Data based on deltas e.g. V$SQL
		–  Active Session History
		–  Metrics
		
Stores data in AWR tables in SYSAUX  tablespace

     – WRH$_xxx for data 
     – WRM$_xxx for metadata
	 
Access data directly using  DBA_HIST_xxx  views

AWR snapshots resides in SYSAUX tablespace. 

By default snapshot are generated once every 60 min and maintained for 7 days. 

Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in "dba_hist_snapshot" view. 

$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR reports can be generated by running various SQL scripts to satisfy various requirements. 

Each report is available in HTML or TXT format:

 awrrpt.sql - Displays various statistics for a range of snapshots Ids

awrrpti.sql - Displays statistics for a range of snapshot Ids on a specified database and instance

awrsqrpt.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.

awrsqrpi.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

awrddrpt.sql - Compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql - Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

awrgrpt.sql - AWR GLOBAL Report(RAC)

awrgrpti.sql - Workload Repository RAC (Global) Report

awrgdrpt.sql - AWR GLOBAL DIFF Report (RAC)

awrinfo.sql - Script to Output General AWR Information

awrblmig.sql - AWR Baseline Migrate

awrload.sql  - AWR LOAD: load awr from dump file

awrextr.sql  - AWR Extract

awrddinp.sql - Get inputs for diff report

awrgdinp.sql - Get inputs for global diff reports

awrgdrpi.sql - Workload Repository Global Compare Periods Report

awrginp.sql  - AWR Global Input

awrinpnm.sql - AWR Input Name

awrinput.sql - Get inputs for AWR report


1. AWR Setup

SQL> show parameter statistics_level

NAME                  TYPE        VALUE
--------------------- ----------- ----------
statistics_level      string      TYPICAL  

SQL>

To active the AWR change the system parameter statistics_level to one of three values. 

BASIC - this option disables the AWR
TYPICAL (default) - activates standard level of collection
ALL - same as typical but includes execution plans and timing info from the O/S

If the parameter is set to BASIC, you simply need to modify the parameter in order to start gathering AWR statistics for your database

alter system set statistics_level=TYPICAL scope=both;


SQL> set pages 999
SQL> set lines 180
SQL> col STATISTICS_NAME for a50
SQL> SELECT statistics_name, activation_level, system_status FROM v$statistics_level;

STATISTICS_NAME                                    ACTIVAT SYSTEM_S
-------------------------------------------------- ------- --------
Buffer Cache Advice                                TYPICAL ENABLED
MTTR Advice                                        TYPICAL ENABLED
Timed Statistics                                   TYPICAL ENABLED <--
Timed OS Statistics                                ALL     DISABLED
Segment Level Statistics                           TYPICAL ENABLED
PGA Advice                                         TYPICAL ENABLED
Plan Execution Statistics                          ALL     DISABLED
Shared Pool Advice                                 TYPICAL ENABLED
Modification Monitoring                            TYPICAL ENABLED
Longops Statistics                                 TYPICAL ENABLED
Bind Data Capture                                  TYPICAL ENABLED
Ultrafast Latch Statistics                         TYPICAL ENABLED
Threshold-based Alerts                             TYPICAL ENABLED
Global Cache Statistics                            TYPICAL ENABLED
Active Session History                             TYPICAL ENABLED
Undo Advisor, Alerts and Fast Ramp up              TYPICAL ENABLED
Streams Pool Advice                                TYPICAL ENABLED
Time Model Events                                  TYPICAL ENABLED
Plan Execution Sampling                            TYPICAL ENABLED
Automated Maintenance Tasks                        TYPICAL ENABLED
SQL Monitoring                                     TYPICAL ENABLED
Adaptive Thresholds Enabled                        TYPICAL ENABLED
V$IOSTAT_* statistics                              TYPICAL ENABLED

23 rows selected.

SQL>


2. Find AWR snapshot interval and retention settings

In below example the snapshot interval is one hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

SQL> 

-- OR --

SQL> select
   extract( day from snap_interval) *24*60+
  2    3     extract( hour from snap_interval) *60+
  4     extract( minute from snap_interval ) "Snapshot Interval Minutes",
  5     extract( day from retention) *24*60+
  6     extract( hour from retention) *60+
  7     extract( minute from retention ) "Retention Interval Minutes"
  8  from
  9     dba_hist_wr_control;

Snapshot Interval Minutes Retention Interval Minutes
------------------------- --------------------------
                       60                      11520 <--- 11520/60/24=8 Days

SQL>


3. Display snapshots

SQL> set lines 180
SQL> col MIN(BEGIN_INTERVAL_TIME) for a30
SQL> col max(BEGIN_INTERVAL_TIME) for a30
SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME)       MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME)
------------ ------------------------------ ------------ ------------------------------
          94 26-OCT-16 02.42.53.000 PM                98 29-OCT-16 02.51.39.000 PM

SQL>

SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ----------------------------------------
        96 26-OCT-16 03.30.53.159 PM
        98 29-OCT-16 02.51.39.000 PM
        94 26-OCT-16 02.42.53.000 PM
        97 26-OCT-16 04.30.55.068 PM
        95 26-OCT-16 02.53.52.122 PM

SQL>


4. AWR Report Generation

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 7 <---

Listing the last 7 days of Completed Snapshots

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                 94 26 Oct 2016 14:53      1
                                 95 26 Oct 2016 15:30      1
                                 96 26 Oct 2016 16:30      1
                                 97 26 Oct 2016 19:16      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 95 <---
Begin Snapshot Id specified: 95 <----

Enter value for end_snap: 96
End   Snapshot Id specified: 96



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_95_96.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: test_report.html  <--- Specify Report Name
..
-- < Output of report is shown across the screen > ---
..
Report written to test_report.html
SQL>

SQL> !ls -ltr test_report.html
-rw-r--r-- 1 oracle oinstall 488688 Oct 29 14:55 test_report.html

SQL>


5. Change AWR snapshot interval

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        interval  => 30); <--- Minutes (1/2 Hour)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL> 

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

The above example the snapshot interval is 1/2 hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.


6. Change AWR snapshot retention

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <-- Minutes (7 Days)
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (604800)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 2


SQL>

---------
10080 Minutes = This value i given, convert to seconds 10080*60=604800 (604800/60/60/24 = 7 Days)
691200 Seconds =  691200/60/60/24 = 8 days
---------

SQL> col BASELINE_NAME for a30
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  8 <---

SQL>


SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 7);
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  7 <--

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <---- Minutes (7 Days)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00007 00:00:00.0    DEFAULT 

SQL>

Now retention changed from 8 to 7 days.


7. Take AWR snapshot manually

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         102 <---

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         103 <---

SQL>


8. Purge AWR Snapshots

If you decide you do not need old snaps then you can purge using below example

SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
          94          103

SQL>

exec dbms_workload_repository.drop_snapshot_range(94,99);

-- OR ---

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 94,
    high_snap_id => 99);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

In the below output we don't see snaps b/w 94 to 99 <---
SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         100          103

SQL>


9. How to find the snap_id for sql_text

SQL> conn scott/tiger;
Connected.
SQL> set lines 180
SQL> select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

SQL> show user
USER is "SYS"
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL>

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         107 <---

SQL>
------------------------------------
col parsed format a6
col sql_text format a40
set lines 200
set pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 and
 lower(sql_text) like '%&t%' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;
------------------------------------

SQL> col parsed format a6
SQL> col sql_text format a40
SQL> set lines 200
SQL> set pages 300
SQL>
SQL> select
  2   sql_text,
  3   parsing_schema_name as parsed,
  4   elapsed_time_delta/1000/1000 as elapsed_sec,
  5   stat.snap_id,
  6   to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
  7   txt.sql_id
  8  from
  9   dba_hist_sqlstat stat,
 10   dba_hist_sqltext txt,
 11   dba_hist_snapshot snap
 12  where
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 13   14   15   snap.begin_interval_time>=sysdate-1 and
 16   lower(sql_text) like '%&t%' and
 17   parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
 18  order by elapsed_time_delta asc;
Enter value for t: select * from emp where ename
old  16:  lower(sql_text) like '%&t%' and
new  16:  lower(sql_text) like '%select * from emp where ename%' and

SQL_TEXT                                 PARSED ELAPSED_SEC    SNAP_ID SNAPTIME       SQL_ID
---------------------------------------- ------ ----------- ---------- -------------- -------------
select * from emp where ename='SMITH'    SCOTT      .020678        107 29.10 18:17:32 cgf95c3k5mszx

SQL> 


10. Generating an AWR Report for a Single SQL Statement

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Output

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1 <---

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                100 29 Oct 2016 16:00      1
                                101 29 Oct 2016 16:30      1
                                102 29 Oct 2016 16:41      1
                                103 29 Oct 2016 16:42      1
                                104 29 Oct 2016 17:00      1
                                105 29 Oct 2016 17:30      1
                                106 29 Oct 2016 18:00      1
                                107 29 Oct 2016 18:17      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 106 <--
Begin Snapshot Id specified: 106

Enter value for end_snap: 107
End   Snapshot Id specified: 107 <---




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cgf95c3k5mszx <----
SQL ID specified:  cgf95c3k5mszx

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_106_107.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: cgf95c3k5mszx_txt1 <----

Using the report name cgf95c3k5mszx_txt1


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
CAT            730180430 cat                 1 29-Oct-16 14:51 11.2.0.3.0  NO

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       106 29-Oct-16 18:00:44        28       1.1
  End Snap:       107 29-Oct-16 18:17:32        30       1.1
   Elapsed:               16.80 (mins)
   DB Time:                0.01 (mins)

SQL Summary                                   DB/Inst: CAT/cat  Snaps: 106-107

                Elapsed
   SQL Id      Time (ms)
------------- ----------
cgf95c3k5mszx         21
Module: SQL*Plus
select * from emp where ename='SMITH'

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

SQL ID: cgf95c3k5mszx                         DB/Inst: CAT/cat  Snaps: 106-107
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select * from emp where ename='SMITH'

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   3956160932                     21             1           107            107
          -------------------------------------------------------------


Plan 1(PHV: 3956160932)
-----------------------

Plan Statistics                               DB/Inst: CAT/cat  Snaps: 106-107
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                   Statement   Per Execution % Snap
--------------------------- ---------- -------------- -------
Elapsed Time (ms)                   21           20.7     2.5
CPU Time (ms)                        2            2.0     0.2
Executions                           1            N/A     N/A
Buffer Gets                         85           85.0     0.5
Disk Reads                           8            8.0    14.5
Parse Calls                          1            1.0     0.1
Rows                                 1            1.0     N/A
User I/O Wait Time (ms)             18            N/A     N/A
Cluster Wait Time (ms)               0            N/A     N/A
Application Wait Time (ms)           0            N/A     N/A
Concurrency Wait Time (ms)           0            N/A     N/A
Invalidations                        0            N/A     N/A
Version Count                        1            N/A     N/A
Sharable Mem(KB)                    19            N/A     N/A
-------------------------------------------------------------

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
cgf95c3k5msz select * from emp where ename='SMITH'


Report written to cgf95c3k5mszx_txt1
SQL>


11. Useful views

dba_hist_active_sess_history --	ASH info (see below)
dba_hist_baseline -- baseline info
dba_hist_database_instance -- environment data
dba_hist_sql_plan -- sql execution path data
dba_hist_wr_control	-- AWR settings
dba_hist_snapshot -- snapshot info in the AWR
-- how to find the sqls between two snaps manually --
select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 100 and 107;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.datadisk.co.uk/html_docs/oracle/awr.htm
http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html

Thank you,
Rajasekhar Amudala

DBMS_SCHEDULER

How to Create and Monitor DBMS_SCHEDULER Jobs

Step 0: Overview
Step 1: Create DBMS_SCHEDULER JOB
Step 2: Find the current status of job
Step 3: Find the SID
Step 4: How to find job is enabled(or)disabled
Step 5: How to Disbale DBMS_SCHEDULER job
Step 6: How to Enable DBMS_SCHEDULER job
Step 7: How to stop DBMS_SCHEDULER job
Step 8: How to start DBMS_SCHEDULER job
Step 9: How to change repeat_interval
Step 10: DBMS_SCHEDULER tables for Job monitoring


Step 0: Overview

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package.

The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. 

From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.


Step 1: Create DBMS_SCHEDULER JOB

-- Create Procedure --

SQL> show user
USER is "EP"
SQL> CREATE PROCEDURE SP1
AS
C INTEGER;
BEGIN
select count(*) INTO C
from test t1, test t2 where t1.c=t2.c and t1.c=1;
DBMS_OUTPUT.PUT_LINE(C);
END;
/
  2    3    4    5    6    7    8    9
Procedure created.

SQL>

-- How to create DBMS_SCHEDULER JOB ---
SQL> BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
  2    3         job_name => '"EP"."SP_RAJ"', <-- Job name
  4              job_type => 'STORED_PROCEDURE', <-- procedure
  5              job_action => 'EP.SP1', <-- exec Procedure
  6              number_of_arguments => 0,
  7              start_date => sysdate, <--- Run immediately after execution
  8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
  9              end_date => NULL,
 10              enabled => FALSE,
 11              auto_drop => FALSE,
 12              comments => 'Testing');
 13
 14      DBMS_SCHEDULER.enable(
 15               name => '"EP"."SP_RAJ"');
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> 


Step 2: Find the current status of job

-- Please get the job_name from application team

SQL> select OWNER, JOB_NAME, STATE from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME                       STATE
---------- ------------------------------ ---------------
EP         SP_RAJ                         RUNNING

SQL>


Step 3: Find the SID

SQL> col OWNER for a10
SQL> SELECT OWNER,SESSION_ID,JOB_NAME,RUNNING_INSTANCE FROM dba_scheduler_running_jobs where JOB_NAME='SP_RAJ';

OWNER      SESSION_ID JOB_NAME  RUNNING_INSTANCE
---------- ---------- --------- ----------------
EP                 39 SP_RAJ                   1

SQL>

SQL> col USERNAME for a10
SQL> col EVENT for a30
SQL> select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where SID='39';

       SID    SERIAL# USERNAME   STATUS   SQL_ID        LOGON                EVENT
---------- ---------- ---------- -------- ------------- -------------------- ------------------------------
        39         99 EP         ACTIVE   2p6db5q97bjvt 28-OCT-2016 23:47:02 asynch descriptor resize

SQL>

SQL> select program, module from v$session where sid='39';

PROGRAM                                          MODULE
------------------------------------------------ -----------------
oracle@rac2.rajasekhar.com (J000)                DBMS_SCHEDULER

SQL>

SQL> select vs.program, spid from v$session vs, v$process vp where vs.sid = 39 and vs.paddr = vp.addr;

PROGRAM                                          SPID
------------------------------------------------ ------------------------
oracle@rac2.rajasekhar.com (J000)                2278

SQL>

SQL> !ps -ef | grep 2278
oracle    2278     1 48 Oct28 ?        00:09:31 ora_j000_w148p <---
oracle    2559  4445  0 00:06 pts/2    00:00:00 /bin/bash -c ps -ef | grep 2278
oracle    2561  2559  0 00:06 pts/2    00:00:00 grep 2278

SQL>


Step 4: How to find currently job is enabled(or)disabled

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         TRUE

SQL>


Step 5: How to Disbale DBMS_SCHEDULER job

BEGIN
      DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);
END;
/

-- OR --

exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);


SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         FALSE

SQL>


Step 6: How to Enable DBMS_SCHEDULER job

BEGIN
      DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');
END;
/

-- OR --

exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         TRUE

SQL>


Step 7: How to stop DBMS_SCHEDULER job

exec sys.dbms_scheduler.STOP_JOB(job_name=>'EP.SP_RAJ', force=>true);


Step 8: How to start DBMS_SCHEDULER job

exec sys.dbms_scheduler.RUN_JOB(job_name=>'EP.SP_RAJ');


Step 9: How to change repeat_interval

SQL> set lines 180
SQL> col REPEAT_INTERVAL for a30
SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          RUNNING         FREQ=MINUTELY;INTERVAL=15

SQL>

-- Current status is running, once job completed. Then do the below or if is it ok stop job then stop job.


SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          SCHEDULED       FREQ=MINUTELY;INTERVAL=15

SQL> 

SQL> exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);

PL/SQL procedure successfully completed.

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME        ENABL
--------------- -----
SP_RAJ          FALSE

SQL>

SQL> BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
        name => '"EP"."SP_RAJ"',
        attribute => 'repeat_interval',
        value => 'FREQ=MINUTELY;INTERVAL=30');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          DISABLED        FREQ=MINUTELY;INTERVAL=30

SQL>

SQL> exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');

PL/SQL procedure successfully completed.

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME        ENABL
--------------- -----
SP_RAJ          TRUE

SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          SCHEDULED       FREQ=MINUTELY;INTERVAL=30

SQL>


Step 10: DBMS_SCHEDULER tables for Job monitoring

- To review the execution times of a job
SELECT JOB_NAME, STATUS, ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

- To review the errors of a Job:

select status, error#, substr(additional_info,1,500)
from dba_scheduler_job_run_details where job_name='SP_RAJ';

For jobs run by current user:

To view all running jobs --> USER_SCHEDULER_RUNNING_JOBS
To view job statuses --> USER_SCHEDULER_JOB_RUN_DETAILS
To view log details --> USER_SCHEDULER_JOB_LOG

For all jobs :

To view all running jobs --> DBA_SCHEDULER_RUNNING_JOBS
To view job statuses --> DBA_SCHEDULER_JOB_RUN_DETAILS
To view log details --> DBA_SCHEDULER_JOB_LOG

Monitoring job-scheduling

-- To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details;

-- To show running jobs:

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

-- To show job history:

select log_date
, job_name
, status
from dba_scheduler_job_log;

-- show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

-- show all jobs and their attributes:
select * from dba_scheduler_jobs;

-- show all program-objects and their attributes

select * from dba_scheduler_programs;

-- show all program-arguments:

select * from dba_scheduler_program_args;

-- For more help "desc dbms_scheduler;"

desc dbms_scheduler;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.oradev.com/dbms_scheduler.html
http://www.vsevolod.com/magz/ORplSQLl/0596005873/oracleplsqldba-chp-8-sect-7.html
http://www.pafumi.net/Scheduler_DBMS_SCHEDULER.html

Thank you,
Rajasekhar Amudala

Uninstall SQLT

How to Uninstall SQLT

[oracle@rac1 install]$ pwd
/home/oracle/sqlt/install
[oracle@rac1 install]$ ls -ltr sqdrop.sql
-rw-r--r-- 1 oracle oinstall 1365 Oct 30  2014 sqdrop.sql
[oracle@rac1 install]$

[oracle@rac1 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 13 19:36:26 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @sqdrop.sql <--

output:

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait
TADOBJ completed.

PL/SQL procedure successfully completed.


SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.


SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    my_count INTEGER;
  3
  4  BEGIN
  5    SELECT COUNT(*)
  6      INTO my_count
  7      FROM sys.dba_users
  8     WHERE username = 'TRCADMIN'; <---
  9
 10    IF my_count = 0 THEN
 11      BEGIN
 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
 13      EXCEPTION
 14        WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
 16      END;
 17
 18      FOR i IN (SELECT directory_name
 19                  FROM sys.dba_directories
 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
 21      LOOP
 22        BEGIN
 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
 25        EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
 28        END;
 29      END LOOP;
 30    END IF;
 31  END;
 32  /
Dropped directory TRCA$INPUT2.
Dropped directory TRCA$INPUT1.
Dropped directory SQLT$BDUMP.
Dropped directory SQLT$UDUMP.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$STAGE.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER SQLTXADMIN CASCADE <---

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER SQLTXPLAIN CASCADE <---

User dropped.

SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE SQLT_USER_ROLE <---

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

STATSPACK

STATSPACK Utility

Step 1: Overview
Step 2: How to find statspack installed or not
Step 3: Install statspack
Step 4: Available SNAP_LEVEL
Step 5: Set timed_statistics to true
Step 6: Taking STATSPACK snapshots
Step 7: List snapshots
Step 8: Generate STATSPACK reports
Step 9: How to find current SNAP level
Step 10: Modify SNAP_LEVEL (Only if required)
Step 11: Schedule automatic STATSPACK
Step 12: View the JOB
Step 13: Purge a snapshot
Step 14: Uninstall Statspack
Step 15: Other statspack scripts


Step 1: Overview

STATSPACK is a performance diagnosis tool, available since Oracle8i: Oracle 8.1.6 introduced statspack as a replacement for the UTLBSTAT/UTLESTAT scripts.

The Statspack package is a set of SQL, PL/SQL and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data.

Using Statspack we can collect statistics which are put in specific tables.

When we need, we can run reports based on these tables (snapshots) to tune the database.

Please note Statspack makes data collection easy to automate using either DBMS_JOB or an operating system utility to schedule collection tasks.


Step 2: How to find statspack installed or not

SQL> select * from stats$level_description;
select * from stats$level_description
              *
ERROR at line 1:
ORA-00942: table or view does not exist <-- STATSPACK not installed before

SQL>


Step 3: Install statspack

SQL> CREATE TABLESPACE perfstat
DATAFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/w148p/perfstat.dbf' SIZE 500M
autoextend on maxsize 2G;  2    3

Tablespace created.

SQL>

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba 
@spcreate.sql     -- Enter tablespace names when prompted
-- Please note user PERFSTAT automatically will get create. No need to create manually(tested in 10gR2).

Output

[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -ltr spcreate.sql
-rw-r--r-- 1 oracle oinstall 861 May 17  2002 spcreate.sql
[oracle@rac1 admin]$

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Oct 27 13:05:56 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spcreate.sql <---

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: PERFSTAT <----
PERFSTAT


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT <----

Using tablespace PERFSTAT as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP <----

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user  ---


... Installing required packages


... Creating views


... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

...
--- trimed text content
--- trimed text content
...

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>


Step 4: Available SNAP_LEVEL

There are five snap shot levels (0, 5, 6, 7, 10) with 5 being the default.

SQL> set pages 999 lines 180
SQL> col DESCRIPTION for a60
SQL> select * from stats$level_description;

SNAP_LEVEL DESCRIPTION
---------- ------------------------------------------------------------
         0 This level captures general statistics, including rollback s
           egment, row cache, SGA, system events, background events, se
           ssion events, system statistics, wait statistics, lock stati
           stics, and Latch information

         5 This level includes capturing high resource usage SQL Statem
           ents, along with all data captured by lower levels

         6 This level includes capturing SQL plan and SQL plan usage in
           formation for high resource usage SQL Statements, along with
            all data captured by lower levels

         7 This level captures segment level statistics, including logi
           cal and physical reads, row lock, itl and buffer busy waits,
            along with all data captured by lower levels

        10 This level includes capturing Child Latch statistics, along
           with all data captured by lower levels


5 rows selected.

SQL>


Step 5: Set timed_statistics to true

SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

ALTER SYSTEM SET timed_statistics = true; 
or 
ALTER SESSION SET timed_statistics = true;


Step 6: Taking STATSPACK snapshots

exec statspack.snap; <---- Default Level 5
-- Run your job ---
exec statspack.snap;

-- OR --

SQL> exec statspack.snap(i_snap_level => 7);

PL/SQL procedure successfully completed.

SQL>

-- Run your job ---

SQL> exec statspack.snap(i_snap_level => 7);

PL/SQL procedure successfully completed.

SQL>

-- OR --

For a particular session, in this case SID = 158

exec statspack.snap(i_session_id=>158); 


Step 7: List snapshots

SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
     "Date/Time" from stats$snapshot,v$database;  2

NAME         SNAP_ID Date/Time
--------- ---------- --------------------
W148P              1 27-OCT-2016:13:25:41
W148P              2 27-OCT-2016:13:48:01

2 rows selected.

SQL>


Step 8: Generate STATSPACK reports

[oracle@rac1 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -ltr spreport.sql
-rw-r--r-- 1 oracle oinstall 1284 Apr 23  2001 spreport.sql
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL> @spreport.sql
-- Please note it will create the file w148p_snap_1_2.txt on current directory

Output

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL> @spreport.sql <-----

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3175692859 W148P               1 w148p



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3175692859        1 W148P        w148p        rac1.rajasek
                                               har.com

Using 3175692859 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
w148p        W148P                1 27 Oct 2016 13:25     7
                                  2 27 Oct 2016 13:48     7



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1 <----
Begin Snapshot Id specified: 1

Enter value for end_snap: 2 <----
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: w148p_snap_1_2.txt

Using the report name w148p_snap_1_2.txt

STATSPACK report for

Database    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3175692859 w148p               1 27-Oct-16 12:40 10.2.0.5.0  NO

Host  Name:   rac1.rajasekhar. Num CPUs:    1        Phys Memory (MB):    3,013
~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          1 27-Oct-16 13:25:41       17       3.9
  End Snap:          2 27-Oct-16 13:48:01       18       5.1
   Elapsed:               22.33 (mins)

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:       648M             Std Block Size:         8K
           Shared Pool Size:       212M                 Log Buffer:     5,954K

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              1,504.88            224,059.56
              Logical reads:                 16.82              2,504.00
              Block changes:                  5.33                793.56
             Physical reads:                  0.10                 15.56
            Physical writes:                  1.12                166.33
                 User calls:                  0.04                  5.22
                     Parses:                  0.93                138.11
                Hard parses:                  0.09                 13.11
                      Sorts:                  0.57                 85.56
                     Logons:                  0.01                  1.11
                   Executes:                  2.23                332.33
               Transactions:                  0.01

  % Blocks changed per Read:   31.69    Recursive Call %:    99.83
 Rollback per transaction %:    0.00       Rows per Sort:    38.26

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.38    In-memory Sort %:  100.00
            Library Hit   %:   92.10        Soft Parse %:   90.51
         Execute to Parse %:   58.44         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  125.00     % Non-Parse CPU:   96.24

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   44.32   46.56
    % SQL with executions>1:   47.98   62.40
  % Memory for SQL w/exec>1:   48.77   66.29

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                         1          62.7
db file sequential read                            145           0      2   12.6
log file parallel write                             92           0      2    9.5
control file parallel write                        486           0      0    8.4
os thread startup                                    6           0     13    3.8
          -------------------------------------------------------------
Host CPU  (CPUs: 1)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       0.00    0.00      0.41    0.42   99.14    0.05

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:    0.13
              % of busy  CPU for Instance:   15.45
  %DB time waiting for CPU - Resource Mgr:

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):      3,012.6      3,012.6
                   SGA use (MB):        876.0        876.0
                   PGA use (MB):         49.1         51.5
    % Host Mem used for SGA+PGA:         30.7         30.8
          -------------------------------------------------------------

...
--- trimed text content
--- trimed text content
...

End of Report ( w148p_snap_1_2.txt )

SQL>


Step 9: How to find current SNAP level

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7

4 rows selected.


Step 10: Modify SNAP LEVEL (Only if required)

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7

4 rows selected.

SQL> exec statspack.modify_statspack_parameter(i_snap_level=>10, i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7
         5         10  <--- snap level changed

5 rows selected.

SQL>


Step 11: Schedule automatic STATSPACK

The collection of system snapshots can be automated with the DBMS_JOB package.
The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour (default).
$ORACLE_HOME/rdbms/admin/spauto.sql

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
[oracle@rac1 admin]$ sqlplus PERFSTAT/PERFSTAT; <---

SQL> @spauto.sql

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         1 <--- Please note this


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
         1 27-OCT-16 18:00:00

SQL>


Step 12: View the JOB

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SQL> set lines 180
SQL> col SCHEMA_USER for a20
SQL> col INTERVAL for a30
SQL> col WHAT for a30
SQL> select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;

       JOB SCHEMA_USER          INTERVAL                       B WHAT
---------- -------------------- ------------------------------ - ------------------------------
         1 PERFSTAT             trunc(SYSDATE+1/24,'HH')       N statspack.snap;

SQL>

SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
     "Date/Time" from stats$snapshot,v$database;

NAME         SNAP_ID Date/Time
--------- ---------- --------------------
..
W148P              5 27-OCT-2016:17:00:58
W148P              6 27-OCT-2016:18:00:01
W148P              7 27-OCT-2016:19:02:40

REM Change the snap interval from 1 hr to 1/2 hour <— please test before use
REM SQL> exec dbms_job.interval(1, ‘trunc(SYSDATE+1/48,”HH”)’);
REM
—- FYI —-
REM — Every one hour from now: sysdate+1/24
REM — Every 1/2 hour from now: sysdate+1/48
REM — Every 15 Minuts from now: sysdate+15/1440
REM — Gather STATS FOR PERFSTAT SCHMEA TO SEE CHANGES —
conn PERFSTAT / PERFSTAT
select JOB, LOG_USER, LAST_DATE, NEXT_DATE, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;


Step 13: Purge a snapshot

Remove a range of snapshots using ORACLE_HOME/rdbms/admin/sppurge.sql

-- Remove all snapshots using ORACLE_HOME/rdbms/admin/sptrunc.sql ---

SQL> @sppurge.sql


Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 3175692859 W148P             1 w148p


Snapshots for this database instance
====================================

                               Base-  Snap
 Snap Id   Snapshot Started    line? Level Host            Comment
-------- --------------------- ----- ----- --------------- --------------------
       1  27 Oct 2016 16:58:00           7 rac1.rajasekhar
       2  27 Oct 2016 16:58:52           7 rac1.rajasekhar
       3  27 Oct 2016 17:00:19           7 rac1.rajasekhar
       4  27 Oct 2016 17:00:21           7 rac1.rajasekhar
       5  27 Oct 2016 17:00:58          10 rac1.rajasekhar
       6  27 Oct 2016 18:00:01          10 rac1.rajasekhar
       7  27 Oct 2016 19:02:40          10 rac1.rajasekhar



Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1
Using 1 for lower bound.

Enter value for hisnapid: 4
Using 4 for upper bound.

Deleting snapshots 1 - 4. <----

Number of Snapshots purged: 4 <--- selected 4 snapshots deleted.
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Purge of specified Snapshot range complete.


SQL>
SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
     "Date/Time" from stats$snapshot,v$database;  2

NAME       Snap Id Date/Time
--------- -------- --------------------
W148P            5 27-OCT-2016:17:00:58 
W148P            6 27-OCT-2016:18:00:01 
W148P            7 27-OCT-2016:19:02:40


6 rows selected.

SQL>


Step 14: Uninstall Statspack

If you decide you do not need Statspack installed any more you can remove/uninstall Statspack with ORACLE_HOME/rdbms/admin/spdrop.sql
The spdrop.sql needs be dropped by a user with SYSDBA. 
Remember to remove any jobs you might have created to manage the Statspack environment.

Output

SQL> @spdrop.sql <----
Dropping old versions (if any)

Synonym dropped.


Sequence dropped.


Synonym dropped.


Table dropped.
..
..
..
User dropped.

NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.

SQL>
SQL>
SQL>


Step 15: Other statspack scripts

Some of the other statspack scripts are:
sppurge.sql - Purge (delete) a range of Snapshot Id's between the specified begin and end Snap Id's
spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
spreport.sql - Report on differences between values recorded in two snapshots
sptrunc.sql - Truncates all data in Statspack tables

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value
http://www.orafaq.com/wiki/Statspack

Thank you,
Rajasekhar Amudala

SQL Query is really hanged or not

Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not

How to see the progress a query is making from within the execution plan used.
Using this we can find sql query is really hanged or not.

1. Find the active seesions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR


1. Find the active seesions

select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';
SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';

Output:
———–

SQL> select sid, serial#, username, status, sql_id, event from v$session where username='SH';

       SID    SERIAL# USERNAME                       STATUS   SQL_ID        EVENT
---------- ---------- ------------------------------ -------- ------------- ----------
        24         23 SH                             ACTIVE   5mxdwvuf9j3vp direct path read
        27         61 SH                             ACTIVE   5mxdwvuf9j3vp direct path read
SQL>


2. Monitoring Progress of a SQL Execution Plan

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%' and sid='&sid' <---
ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;

Ouput:

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23714 <---
                                        4 TABLE ACCESS FULL              23714 <---
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23781 <---
                                        4 TABLE ACCESS FULL              23781 <---
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23737 <-- see diff
                                        4 TABLE ACCESS FULL              23737 <-- see diff
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23804 <--- see diff
                                        4 TABLE ACCESS FULL              23804 <--- see diff
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23794 <-- see diff
                                        4 TABLE ACCESS FULL              23794 <-- see diff
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23861 <--- see diff
                                        4 TABLE ACCESS FULL              23861 <--- see diff
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL>

We can see the difference. Hence the query is not hanged


3. REPORT_SQL_MONITOR in HTML (OR) TEXT format

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '5mxdwvuf9j3vp', <--- SQLID
  type         => 'TEXT',         <--- HTML
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Output
=======

SQL Monitoring Report

SQL Text
------------------------------
SELECT ......

Global Information
------------------------------
 Status              :  EXECUTING <----
 Instance ID         :  1
 Session             :  SH (27:61)
 SQL ID              :  5mxdwvuf9j3vp
 SQL Execution ID    :  16777216
 Execution Started   :  10/26/2016 20:19:01 <---
 First Refresh Time  :  10/26/2016 20:19:05
 Last Refresh Time   :  10/26/2016 20:38:25
 Duration            :  1164s <---
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@rac2.rajasekhar.com (TNS V1-V3)

Global Stats
======================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Gets  | Reqs | Bytes |
======================================================================
|    1172 |      77 |     1095 |        0.00 |    15M | 537K | 114GB |
======================================================================

SQL Plan Monitoring Details (Plan Hash Value=2043253752)
===========================================================================================================================================================
| Id   |        Operation        |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |     Activity Detail     |
|      |                         |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |       (# samples)       |
===========================================================================================================================================================
|    0 | SELECT STATEMENT        |           |         |      |           |        |     1 |          |      |       |          |                         |
|    1 |   SORT AGGREGATE        |           |       1 |      |       811 |   +220 |     1 |        0 |      |       |          |                         |
| -> 2 |    NESTED LOOPS         |           |    5557 | 370M |      1167 |     +4 |     1 |        4 |      |       |          |                         |
| -> 3 |     PARTITION RANGE ALL |           |    919K |  499 |      1167 |     +4 |     1 |    10325 |      |       |          |                         |
| -> 4 |      TABLE ACCESS FULL  | SALES     |    919K |  499 |      1167 |     +4 |     5 |    10325 |    1 | 208KB |          |                         |
| -> 5 |     TABLE ACCESS FULL   | CUSTOMERS |       1 |  403 |      1170 |     +1 | 10326 |        4 | 535K | 114GB |   100.00 | Cpu (35)                |
|      |                         |           |         |      |           |        |       |          |      |       |          | direct path read (1128) |
===========================================================================================================================================================

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

How to Run SQLT

How to Run SQLT

Step 1: Grant SQLT_USER_ROLE to appication user
Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query
Step 3: Run sqltxtract.sql
Step 4: Find the ZIP file created by above sql
Step 5: Review the mail.html


Step 1: Grant SQLT_USER_ROLE to appication user

SQL> grant SQLT_USER_ROLE to <application_user>;


Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query

sqlplus / as sysdba
set echo on
set linesize 200 pagesize 1000
col sql_text format a50
select sid, serial#,status,sql_id,event from v$session where username='&username';
select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from test t1, test t2%';
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'TYPICAL'));
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'ADVANCED ALLSTATS LAST'));


Step 3: Run sqltxtract.sql

-- sqltxtract.sql script gather the details from memory or else from AWR snapshots.
cd sqlt/run
sqlplus ep/ep; -- connect as application user
@sqltxtract.sql sqlid sqltpassword
@/home/oracle/sqlt/run/sqltxtract.sql dkz7v96ym42c6 SQLTXPLAIN


Step 4: Find the ZIP file created by above sql

cd /home/oracle/sqlt/run
ls -ltr *sqlid*

eg: 
ls -ltr *dkz7v96ym42c6*
-rw-r--r-- 1 oracle oinstall 1842900 Oct 17 22:49 sqlt_s41093_xtract_dkz7v96ym42c6.zip

Please copy the zip file to your desktop for review.


Step 5: Review the mail.html

This mail.html file would have all the previous and current execution plans used by the sql along with timestamp when they were used.

-- click on Execution plans hyperlink under plans

Please check the execution plan currently used by the sql, by comparing the timestamp. Also identify the best execution plan for the sql.

The plan hash value for the best execution plan would have suffix [B]
Then plan hash value for the worst exection plan would have a suffix[W]

In case if you find the BEST EXECUTION PLAN, go ahead and create the custom sql profile. 

If you want to know how to create CUSTOM SQL PROFILE, please click below
How to create Custome SQL Profile

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

How to Install SQLT

How to Install SQLT

Step 0: Find SQLT Already Instlled or Not
Step 1: Download SQLT
Step 2: How to Install SQLT
Step 3: Verify


Step 0: Find SQLT Already Instlled or Not

SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;

output

SQL> SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;  2    3    4    5
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
                        *
ERROR at line 4:
ORA-00904: "SQLTXPLAIN"."SQLT$A"."GET_PARAM": invalid identifier <-- Not installed

SQL>


Step 1: Download SQLT

All About the SQLT Diagnostic Tool (Doc ID 215187.1)


Step 2: How to Install SQLT

unzip sqlt_latest.zip
cd /home/oracle/sqlt/install
sqlplus / as sysdba
@sqcreate.sql

output

[oracle@rac1 ~]$ cd /home/oracle/sqlt/install
[oracle@rac1 install]$ ls -ltr sqcreate.sql
-rw-r--r-- 1 oracle oinstall 4771 Oct 30  2014 sqcreate.sql
[oracle@rac1 install]$

[oracle@rac1 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 17 12:18:23 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @sqcreate.sql

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

PL/SQL procedure successfully completed.


Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD): <-- Just hit enter


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN: <--- Enter the password
Re-enter password:


PL/SQL procedure successfully completed.

... please wait

TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------
EXAMPLE                                  216
USERS                                  32735


Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS <---

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP


Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP <---

PL/SQL procedure successfully completed.


The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: scott  <-----

PL/SQL procedure successfully completed.


SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: <----

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

TADOBJ completed.

PL/SQL procedure successfully completed.
..
..
SQLT users must be granted SQLT_USER_ROLE before using this tool. <----

SQCREATE completed. Installation completed successfully.
SQL>


Step 3: Verify

SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;

output

SQL> SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;  2    3    4    5

SQLT_VERSION
-----------------------------------------
SQLT version number: 12.1.14
SQLT version date : 2015-12-06
Installation date : 2016-10-17/12:20:51


SQL>

Reference:

FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
All About the SQLT Diagnostic Tool (Doc ID 215187.1)
SQLT Usage Instructions (Doc ID 1614107.1)

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

Enable Real Time Apply

How to Enable Real Time Apply

Real-time apply : When real-time apply is enabled, the log apply services can apply redo data as it is received,
                                     without waiting for the current standby redo log file to be archived.

Pre-requisites: Configure Physical Standby database. Click here

0. Enviroment
1. Set up log_archive_dest_2 on Primary
2. Verify Real-Tme Apply Status on Standby
3. Try to enable real-time apply
4. Add standby redo logs on primary and standby
5. Enable Real-Time Apply
6. Quick Testing


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=w5005prg LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=w5005p
                                                 rg


2. Verify Real-Tme Apply Status on Standby

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO  MANAGED

SQL>

Note: If it is not in Real-Time apply mode then SRL (Standby Redo Logs) will not be in use.

SRL=NO
RECOVERY_MODE = Managed : It means Redo-Apply only, Not Real Time Aapply.


3. Try to enable real-time apply

SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

SQL>

/*
standby redo logs are populated with redo information as fast as the primary redo logs, 
rather than waiting for the redo log to be archived and shipped to the standby.  
This results in faster switchover and failover times because the standby redo log files have been applied 
already to the standby database by the time the failover or switchover begins.
Oracle recommends the below formula to calculate the number of Standby redo logs file as 
(maximum number of logfiles for each thread + 1) * maximum number of threads
*/


4. Add standby redo logs

On Primary

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> col member for a50
SQL> set lines 180
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA/w5005pr/onlinelog/group_3.263.900456463      NO
         2         ONLINE  +DATA/w5005pr/onlinelog/group_2.266.900456463      NO
         1         ONLINE  +DATA/w5005pr/onlinelog/group_1.267.900456461      NO

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

no rows selected

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

no rows selected

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 +DATA/w5005pr/onlinelog/group_4.259.900718771        52428800
         5 +DATA/w5005pr/onlinelog/group_5.258.900718779        52428800
         6 +DATA/w5005pr/onlinelog/group_6.257.900718785        52428800
         7 +DATA/w5005pr/onlinelog/group_7.256.900718791        52428800

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED

SQL>

On Standby

SQL> alter database add standby logfile group 4 size 50M;
alter database add standby logfile group 4 size 50M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL>


5. Enable Real-Time Apply on Standby

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO  MANAGED REAL TIME APPLY

SQL>

On Primary

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2             VALID     PHYSICAL       YES MANAGED REAL TIME APPLY

SQL>


6. Quick Testing

On Primary

SQL> set time on
00:03:15 SQL> create table test (name varchar2(30));

Table created.


00:03:24 SQL> insert into test values ('Rajasekhar Amudala');

1 row created.

00:05:22 SQL>  COMMIT;

Commit complete.

00:05:23 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:24 SQL>

On Standby

SQL> set time on
00:05:30 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:21 SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

About


Rajasekhar Amudala – Independent Oracle Consultant

Email: contact@br8dba.com

—————————————————————————————————————–

Hello, I’m Rajasekhar Amudala, an Oracle Database Administrator (DBA) with extensive experience in Oracle database Administration. I’m thrilled to announce that I’m stepping into a new role as an independent Oracle consultant, fully dedicated to pursuing work on my own.

I bring over 15 years of expertise to the table. My focus is on providing comprehensive support for Oracle DBA tasks, ranging from database installation, upgrade, Patching, Migration and performance tuning to backup and recovery. Whether you’re looking for assistance with specific DBA tasks or seeking guidance on Oracle database management, I’m here to help.

As I embark on this exciting journey of working independently, I’m eager to connect with businesses and professionals in need of Oracle expertise. If you have any questions or if there’s a specific Oracle-related challenge you’re facing, please feel free to reach out. I’m here to collaborate and ensure the success of your Oracle environment. Looking forward to connecting with you!

————————————————————————————————————————-

Oracle Database Service Portfolio

Clock Coverage

Our team of DBA experts offers flexible scheduling to accommodate our clients’ business needs. We are always ready to assist and address any concerns, providing peace of mind for our clients even during non-business hours or maintenance windows.

SUCCESS Stories

Successfully migrated a client’s Oracle Database from 11g to 12c and established a weekly DB refresh from production to development, ensuring data consistency and minimizing disruption to the business.

Recovered a corrupted 20TB Oracle Database through point in time recovery and optimized RMAN backup for faster backup time, ensuring business continuity and reducing the risk of data loss.

Created a PDB clone from production to dev, established standby DBs for high availability, resolved lag issues, and resynced Data guard, ensuring data availability and minimizing downtime for the client.

Created new 19c RAC databases with failover services, ensuring high availability and reducing the risk of data loss. Successfully migrated and refreshed specific schemas from 12c to 19c, ensuring data integrity and compatibility across systems.

Migrated 12c & 19c databases from one DC to another with minimal downtime, ensuring business continuity and minimizing disruption to the client. Upgraded an Oracle Database from 12c to 19c, enabling the client to take advantage of the latest features and capabilities.

Patched 19.12 Grid infrastructure with zero downtime patching feature and moved to 19.13 Grid home, ensuring that the client’s systems were up to date and running smoothly with minimal disruption.

Installed and configured 19.3 Grid infrastructure for multi-instance databases with auto-failover services, providing the client with a highly available and scalable database environment.

How Do We Charge?

We work with each customer to develop customized Service Level Agreements that outline the projected hours and costs based on their specific needs and desired level of support. This ensures that our services are tailored to meet each client’s unique requirements and budget.

Any Questions?

If you have any questions or require further information, please do not hesitate to reach out to us. Our team is always available to assist you and provide the necessary support.

 

 

******************************************************************************************

Certifications

RAJASEKHAR AMUDALA

Welcome to Bright DBA website.  

Please use the menu bar (or) Index below to navigate for Oracle/MS SQL Server/MySQL/MariaDB/DB2 LUW, PostgreSQL DBA articles !!!

Topics
DBA Monitor
OS
Database
Network
Storage
Install Oracle
Install Oracle 10gR2
Install Oracle Software in silent mode 11gR2
Install Oracle Software in silent mode 12cR2
Install Oracle 18c using OUI - GUI
Install Oracle 19c using OUI - GUI
Install Oracle Software in silent mode 19c
YouTube - Install Oracle Database 19c on Linux 7
Deinstall Oracle
Deinstall Oracle 11gR2
Deinstall Oracle 11gR2 Client on Windows
Deinstall Oracle 18c
Deinstall Oracle 19c
Create Database Oracle
Create Database in silent mode 11gR2
Create Non-CDB Oracle Database 12c on Linux using DBCA (GUI)
CREATE NON-CONTAINER DATABASE ON ORACLE 12C IN SILENT MODE USING DBCA
CREATE PLUGGABLE DATABASE MANUALLY USING SEED(PDB$SEED)
CREATE NEW PLUGGABLE DATABASE (PDB2) BY CLONING AN EXISTING PLUGGABLE DATABASE (PDB1)
CREATE PLUGGABLE DATABASE USING DBCA (from PDB$SEED)
Convert 12.2 Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB (CDB1)
Create Non-CDB Oracle Database 19c on Linux using DBCA (GUI)
CREATE NON-CONTAINER DATABASE ON ORACLE 19C IN SILENT MODE USING DBCA
YouTube - How to create Oracle Database using DBCA?
Drop Database
Delete database using DBCA silent mode 11gR2
How to Drop the Database Manually
How to Drop Pluggable Database Manually 12cR2
YouTube - How to Drop Database
Database Startup/Shutdown
HOW TO STARTUP/SHUTDOWN CDB AND PDB 12cR2
YouTube - How to Start Database
Youtube - How to Shutdown Database
How to connect to Database
YouTube - How to connect to Database as SYSDBA
YouTube - How to connect to Database Remotely using SQL Developer?
Proxy User and Connect Through
Database Patching / Rollback
Patch Terminology
Apply Database Patch on 12.2 - Release Update
Oracle 18c Database Patching
Apply Database Patch on 19c - Release Update
How To Apply Database PSU Patch where Standby in Place 11gR2
ROLLBACK DATABASE RELEASE UPDATE from Oracle Database 12.2.0.1
Datapump
SCHEMA REFRESH FROM 11G TO 12C PDB
Backup and Restore - Database Refresh
How to Backup ORACLE_HOME binaries and Oracle INVENTORY
RMAN Catalog
RMAN Database Restore RAC – RAC
RMAN Database Restore from ASM (NON-RAC) TO ASM (NON-RAC)
RMAN Database Restore from ASM (NON-RAC) TO File System (NON-RAC)
RMAN ACTIVE DUPLICATION ASM TO ASM without downtime
Backup Based RMAN Duplicate Database
Database Upgrade
Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1
Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA
Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA
Upgrade Oracle Database Manually from 12.2.0.1 to 19c (NON-CDB)
Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place
Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby
Dataguard - Configuration
Creating Active Data Guard Physical Standby Environment
Creating Active Data Guard Physical Standby Environment on ASM
Covert Physical Standby database to Snapshot Standby database R/W mode
Convert Snapshot database to Physical Standby database
Configure Cascading Standby Database on 11G
How to setup Data Guard Broker Configuration
DG Broker Error – ORA-16714
Disable Data Guard Broker Before Patch or Upgrade
Creating Physical Standby Active DataGuard Configuration in 12.2.0.1 Container Database
How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command
How to Create Physical Standby Database using RMAN Backup With Duplicate Command
12c DG Broker Configuration
Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place
Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby
Create RAC Physical Standby Database using RMAN Active Duplicate Command
Dataguard - Switchover
Performing Switchover with No Broker (Manual)
Performing Switchover with DG Broker
Dataguard - Failover
Failover with DG Broker (No-Flashback)
Failover with No DG Broker (No Flashback) / Failover using Manual Method
Reinstate a failed over Data Guard using Flashback Database
Failover with No DG Broker using Flashback Database
Dataguard - Patching
How To Apply Database PSU Patch where Standby in Place
Dataguard - Other
How to Enable Real Time Apply
Refresh Standby Database using RMAN Incremental SCN Backup - Huge GAP
Resetlogs on Primary Database where Standby in place
How to drop the standby redo logs
ASM
How to move ASM SPFILE from +DATA to +VOTE diskgroup
ASM Commands - Non-RAC
Instantiating disk: failed while creating ASM disk
After server reboot, CSSD and ASM are not came up automatically
RAC
RAC Install 11.2.0.3
Upgrade RAC GI to 11.2.0.4 from 11.2.0.3
Downgrade RAC GI to 11.2.0.3 from 11.2.0.4
root.sh failed with ORA-29783 on RAC
Delete Node from Cluster in 11gR2 (11.2.0.3)
Add Node to 11gR2 Oracle RAC Cluster (11.2.0.3)
Delete Node without remove GI and RDBMS binaries
Add Node Back to Cluster which was deleted without removing GI and RDBMS binaries
How to take OCR backup on 11.2.0.4
How to display Oracle Cluster name
How to Move/Relocate OCR from +DATA to +VOTE diskgroup
Restore loss of all VOTE disks
Cluster Startup Issues
Cluster Verification Utility Command Reference
How to Configure OSWatcher
Create RAC Physical Standby Database using RMAN Active Duplicate Command
Create ACFS File System on RAC
Performance Tuning
About Oracle Statistics
Oracle Histograms
Export and Import schema statistics from one database to another
How to Disable CONTROL_MANAGEMENT_PACK_ACCESS
Generate/Customize Explain Plan
Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not
STATSPACK Utility
ASH Report – ASH (Active Session History) Reports
AWR Report – AWR (Automatic Workload Repository) reports
How to mark SQL_ID as Colored
Troubleshooting Flow For Long Running Queries
How to run SQL Tuning Advisor Manually
How to Install SQLT
How to Run SQLT
How to Create Custom SQL Profile
How to Uninstall SQLT
How to Enable Trace
Trace Analyzer TRCANLZR (TRCA)
DB Generic
How to change SQL prompt to show connected user and database name
How to change Archive destination
Enable the database in Archivelog mode
How to Recover Loss of DATA (Without a Backup!) using Oracle Flash Back Query
Proxy User and Connect Through
ORA-00054 resource busy and acquire with NOWAIT specified
How to find Blocking Sessions
Create Service using DBMS_SERVICE
Error TNS-12543 while connect to a remote database on a Linux
How to Change MAX_STRING_SIZE on Physical Standby Environment
OEM
Steps to configure Oracle 11g OEM DBConsole manually for database
Enable Oracle Enterprise Manager Express 12c
Oracle Enterprise Manager Cloud Control 13c Release 2 (13.2.0.0) Installation on Oracle Linux 64 bit
Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0) Installation on Oracle Linux 64 bit
Add Database Target on EM Cloud Control 13c
How to upgrade the Enterprise Manager 13.1 Cloud Control OMSPatcher to the Latest Version
Apply Oracle Enterprise Manager 13c Release 1 Patch Set Update 13.1.0.0.170718 for Oracle Management Service
Apply Patch on Oracle Management Agent 13c Release 1 (13.1.0.0.0)
Database Link
Database link
DBMS SCHEDULER
How to Create and Monitor DBMS_SCHEDULER Jobs
Enable/Disable JOBS during Maintenance
Microsoft SQL Server DBA
Steps to Install Microsoft SQL Server Express 2014
How to Install Only SQL Server Management Studio
Create Database using the SQL Server Management Studio
Enable TCP/IP Connection
Allow Remote Connection
Authentication Method
How to Create SQL Server Authenticated Login in SQL Server
How to create Windows Authentication Login in SQL Server
How to Create Windows Group Authentication Login in SQL Server
Create a new domain user account in Windows Server 2016
Microsoft SQL Server – Backup and Recovery
Recovery Models
Database Backup: FULL,DIFFERENTIAL and T-LOG
Restore & Recover Database
MySQL DBA
Install MySQL 8 on Linux 7 Using RPM Packages
Create MySQL Database
Create New User Account in MySQL
Grant/Revoke Privileges in MySQL
Create Roles in MySQL
MySQL – Backup/Restore
How To Backup MySQL Database Using mysqldump?
How to Restore Database from Single MySQL Database Backup?
How to Restore A Single Database From A Backup File Which Contains Multiple Databases?
How To Restore Single Table From Full Database Backup in MySQL?
MySQL – Replication
How To Configure MySQL Master-Slave Replication? (One-Way Replication)
MariaDB DBA
Install MariaDB with yum on Linux7 -- Coming soon !!!
Create MariaDB Database --- Coming soon !!!
Create New User Account in MairaDB --- Coming soon !!!
Grant/Revoke Privileges in MariaDB --- coming soon !!!!
Create Roles in MariaDB -- Coming soon !!!
MariaDB – Backup/Restore
Coming Soon !!!
Coming Soon !!!
MariaDB – Replication
How To Configure Maria Master-Slave Replication? (One-Way Replication) -- Coming soon !!!
MariaDB MaxScale Set up
Setup MaxScale -- Coming soon !!!
Load Balancing -- Coming soon !!!
Switchover -- Coming soon !!!
Failover -- Coming soon !!!
PostgreSQL DBA
How to Install PostgreSQL 13 ON RHEL 7?
How to START/STOP PostgreSQL 13 ON RHEL 7?
How to Create Database in PostgreSQL?
Linux
Download Oracle files on Linux via wget
How to Install SQL Developer on Linux
How to Disable the Firewall on Linux 6
DBA Scripts
Oracle DBA Sheet v5.8
Oracle DBA Sheet v6.1
DBA Daily Report
GoldenGate
Installing Oracle GoldenGate V11.2.x for Oracle 10gR2 on Linux x86-64
Unidirectional DML Replication
Zero Downtime Migration
DDL Replication
GoldenGate 12C Installation
Integrated Capture
Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c
Oracle GoldenGate Security – Credential Store for Database Logins
ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION
Installing Oracle GoldenGate 19.1.0.0 for Oracle DB 12c on Linux 7
Install Oracle GoldenGate 19.1.0.0.4 for Oracle Database 19c on Linux 7