About
We have developed several scripts over years to manage Oracle databases for our internal and external projects. We would like to share them for public use at no cost. These scripts help DBA to maintan Oracle databases pro actively, monitor various metrics, troubleshoot performance issues and automate routine housekeeping tasks.
Features
- ASH/AWR - To identify top waits, plan history, performance metrics, etc.
- Data Pump - To perform logical backups of the database using Data Pump.
- Data Guard - To start/stop, monitor replication gap, switch over, etc.
- Golden Gate - To monitor Golden Gate processes and replication lag.
viewWarning:- These scripts should be used by certified and/or experienced DBA. Test these scripts in lower environment before using them in live production system.
Shows TPS history captured in awr
/*
* Title : awr_tps_hist.sql
* Author : EXADIME LLC
* Created: 20-Apr-2021
* Updated: 21-May-2021
* Release: 1.0.0
* Purpose: Shows TPS history captured in awr.
* History: N/A
*/
start oracle.ini
set echo off
set feedback off
set serverout on
set termout on
set linesize 100
set pagesize 0
set pause off
set long 80
set heading on
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
accept p_bsnap prompt "Enter Begin Snap Id: "
accept p_esnap prompt "Enter End Snap Id: "
accept p_order prompt "Enter sort order [asc|desc]: "
spool &&rpt_dir/awr_tps_hist.rpt
select
row_number() over(order by snap_id &&p_order) sno,
snap_id snid,
to_char(begin_interval_time,'yyyymmdd hh24:mi') time,
metric_name name,
avalue aval,
mvalue mval
from
(
select
s.snap_id,
s.begin_interval_time,
t.metric_name,
avg(value) avalue,
max(value) mvalue
from
dba_hist_snapshot s, dba_hist_sysmetric_history t
where
s.snap_id = t.snap_id
and s.instance_number = t.instance_number
and s.dbid = t.dbid
and s.instance_number = (select instance_number from v$instance)
and s.dbid = (select dbid from v$database)
and t.metric_name = 'User Transaction Per Sec'
and s.snap_id between &p_bsnap and &p_esnap
group by
s.snap_id,
s.begin_interval_time,
t.metric_name
order by
s.snap_id desc
);
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
* Title : awr_tps_hist.sql
* Author : EXADIME LLC
* Created: 20-Apr-2021
* Updated: 21-May-2021
* Release: 1.0.0
* Purpose: Shows TPS history captured in awr.
* History: N/A
*/
start oracle.ini
set echo off
set feedback off
set serverout on
set termout on
set linesize 100
set pagesize 0
set pause off
set long 80
set heading on
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
| column | sno | format | 9999 | heading | "#" |
| column | snid | format | 999999 | heading | "SnapId" |
| column | time | format | a15 | heading | "Begin Time" |
| column | name | format | a45 | heading | "Name" |
| column | aval | format | 99,999,999 | heading | "Average" |
| column | mval | format | 99,999,999 | heading | "Maximum" |
accept p_bsnap prompt "Enter Begin Snap Id: "
accept p_esnap prompt "Enter End Snap Id: "
accept p_order prompt "Enter sort order [asc|desc]: "
spool &&rpt_dir/awr_tps_hist.rpt
select
row_number() over(order by snap_id &&p_order) sno,
snap_id snid,
to_char(begin_interval_time,'yyyymmdd hh24:mi') time,
metric_name name,
avalue aval,
mvalue mval
from
(
select
s.snap_id,
s.begin_interval_time,
t.metric_name,
avg(value) avalue,
max(value) mvalue
from
dba_hist_snapshot s, dba_hist_sysmetric_history t
where
s.snap_id = t.snap_id
and s.instance_number = t.instance_number
and s.dbid = t.dbid
and s.instance_number = (select instance_number from v$instance)
and s.dbid = (select dbid from v$database)
and t.metric_name = 'User Transaction Per Sec'
and s.snap_id between &p_bsnap and &p_esnap
group by
s.snap_id,
s.begin_interval_time,
t.metric_name
order by
s.snap_id desc
);
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
Shows free space in ASM disk groups
/*
* Title : asm_free.sql
* Author : EXADIME LLC
* Created: 16-Nov-2020
* Updated: 16-Nov-2020
* Release: 1.0.0
* Purpose: Shows free space in ASM disk groups.
* History: N/A
*/
start oracle.ini
set echo off
set feedback off
set serverout on
set termout on
set linesize 125
set pagesize 0
set pause off
set long 80
set heading on
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
compute sum of total on report
compute sum of used on report
compute sum of free on report
break on report
accept p_order char prompt "Enter order by column {1|2|3|4|5|6} [asc|desc] " default 6
spool &&rpt_dir/asm_free.rpt
select
row_number() over(order by &&p_order) sno,
name name,
total_mb total,
total_mb-free_mb used,
free_mb free,
round(((total_mb-free_mb)/total_mb)*100,2) pct_used
from(
select
name,
total_mb,
free_mb
from
v$asm_diskgroup
)
order by
&&p_order
/
spool off
clear columns
clear computes
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
* Title : asm_free.sql
* Author : EXADIME LLC
* Created: 16-Nov-2020
* Updated: 16-Nov-2020
* Release: 1.0.0
* Purpose: Shows free space in ASM disk groups.
* History: N/A
*/
start oracle.ini
set echo off
set feedback off
set serverout on
set termout on
set linesize 125
set pagesize 0
set pause off
set long 80
set heading on
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
| column | dummy | noprint | |||
| column | sno | format | 9999 | heading | "#" |
| column | name | format | a30 | heading | "Name" |
| column | total | format | 99,999,999,999 | heading | "Total" |
| column | used | format | 99,999,999,999 | heading | "Used" |
| column | free | format | 99,999,999,999 | heading | "MB|Free" |
| column | pct_used | format | 999.99 | heading | "%|Used" |
compute sum of total on report
compute sum of used on report
compute sum of free on report
break on report
accept p_order char prompt "Enter order by column {1|2|3|4|5|6} [asc|desc] " default 6
spool &&rpt_dir/asm_free.rpt
select
row_number() over(order by &&p_order) sno,
name name,
total_mb total,
total_mb-free_mb used,
free_mb free,
round(((total_mb-free_mb)/total_mb)*100,2) pct_used
from(
select
name,
total_mb,
free_mb
from
v$asm_diskgroup
)
order by
&&p_order
/
spool off
clear columns
clear computes
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
To create baseline for good SQL plan captured in AWR
/*
* Title : awr_base_line.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To create baseline for good SQL plan captured in AWR.
* History: N/A
*/
start oracle.ini
set echo off
set feedback on
set serverout on
set termout on
set linesize 80
set pagesize 0
set pause off
set long 80
set heading off
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
accept p_bsnap char prompt "Enter Begin Snap Id: "
accept p_esnap char prompt "Enter End Snap Id: "
accept p_sqlid char prompt "Enter SQL Id: "
accept p_plan_hash char prompt "Enter Plan Hash Value: "
spool &&rpt_dir/awr_base_line.rpt
DECLARE
cur sys_refcursor;
int pls_integer;
BEGIN
--1.Create SQL Tuning Set.
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline (SQL Id: &&p_sqlid).'
);
--2.Load SQL from AWR into SQL Tuning Set.
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>&&p_bsnap, end_snap=>&&p_esnap,basic_filter=>'sql_id = ''&&p_sqlid''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'awr_base_line_&&p_sqlid', populate_cursor=>cur);
CLOSE cur;
--3.Filter good SQL based on plan has value and create baseline.
int:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
basic_filter=>'plan_hash_value = ''&&p_plan_hash'''
);
if int=1 then
dbms_output.put_line('Baseline created.');
else
dbms_output.put_line('Baseline creation failed!');
end if;
END;
/
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
* Title : awr_base_line.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To create baseline for good SQL plan captured in AWR.
* History: N/A
*/
start oracle.ini
set echo off
set feedback on
set serverout on
set termout on
set linesize 80
set pagesize 0
set pause off
set long 80
set heading off
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
accept p_bsnap char prompt "Enter Begin Snap Id: "
accept p_esnap char prompt "Enter End Snap Id: "
accept p_sqlid char prompt "Enter SQL Id: "
accept p_plan_hash char prompt "Enter Plan Hash Value: "
spool &&rpt_dir/awr_base_line.rpt
DECLARE
cur sys_refcursor;
int pls_integer;
BEGIN
--1.Create SQL Tuning Set.
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline (SQL Id: &&p_sqlid).'
);
--2.Load SQL from AWR into SQL Tuning Set.
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>&&p_bsnap, end_snap=>&&p_esnap,basic_filter=>'sql_id = ''&&p_sqlid''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'awr_base_line_&&p_sqlid', populate_cursor=>cur);
CLOSE cur;
--3.Filter good SQL based on plan has value and create baseline.
int:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
basic_filter=>'plan_hash_value = ''&&p_plan_hash'''
);
if int=1 then
dbms_output.put_line('Baseline created.');
else
dbms_output.put_line('Baseline creation failed!');
end if;
END;
/
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
To run SQL Tuning Advisor and report recommendations
/*
* Title : run_tune_task.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To run SQL Tuning Advisor and report recommendations.
* History: N/A
*/
start oracle.ini
set echo off
set feedback on
set serverout on
set termout on
set linesize 200
set pagesize 1000
set pause off
set long 65536
set heading
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
accept p_sql_id char prompt "Enter SQL Id "
spool &&rpt_dir/run_tune_task.rpt
declare
sql_tune_task_id varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task(
sql_id => '&&p_sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 1000,
task_name => 'sql_tune_task_&&p_sql_id',
description => 'Tuning task for statement &&p_sql_id');
dbms_output.put_line('SQL Tune Task Id: ' || sql_tune_task_id);
end;
/
exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_task_&&p_sql_id');
select
dbms_sqltune.report_tuning_task('sql_tune_task_&&p_sql_id')
from
dual;
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off
* Title : run_tune_task.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To run SQL Tuning Advisor and report recommendations.
* History: N/A
*/
start oracle.ini
set echo off
set feedback on
set serverout on
set termout on
set linesize 200
set pagesize 1000
set pause off
set long 65536
set heading
set headsep on
set underline on
set colsep " "
set define on
set verify off
set escape off
set embedded on
accept p_sql_id char prompt "Enter SQL Id "
spool &&rpt_dir/run_tune_task.rpt
declare
sql_tune_task_id varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task(
sql_id => '&&p_sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 1000,
task_name => 'sql_tune_task_&&p_sql_id',
description => 'Tuning task for statement &&p_sql_id');
dbms_output.put_line('SQL Tune Task Id: ' || sql_tune_task_id);
end;
/
exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_task_&&p_sql_id');
select
dbms_sqltune.report_tuning_task('sql_tune_task_&&p_sql_id')
from
dual;
spool off
clear columns
set embedded off
set escape off
set verify on
set define on
set colsep " "
set underline on
set headsep on
set heading on
set pause off
set pagesize 24
set linesize 80
set long 80
set termout on
set serverout off
set feedback on
set echo off