Month: August 2017

File System Growth

by

SELECT (max(round(avg(a_size.size_gb),2)) – min(round(avg(a_size.size_gb),2)))/12 FROM (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS size_gb FROM mgmt$metric_daily m, mgmt$target_type t WHERE t.target_guid=HEXTORAW(‘A06F734DEB35E1CFE38C9AB4729508E2′) AND (t.target_type=’rac_database’ OR (t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND m.target_guid=t.target_guid AND m.metric_guid=t.metric_guid AND t.metric_name=’tbspAllocation’ AND (t.metric_column=’spaceAllocated’) AND m.rollup_timestamp >= sysdate-365 AND m.rollup_timestamp <= sysdate GROUP BY m.metric_column, m.rollup_timestamp) a_size, (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS used_gb … Read more

Average Usage Over The Past 12 Months

by

Get the average growth over the past 12 months. Ensure you change the TARGET_GUID. SELECT (max(round(avg(a_size.size_gb),2)) – min(round(avg(a_size.size_gb),2)))/12 FROM (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS size_gb FROM mgmt$metric_daily m, mgmt$target_type t WHERE t.target_guid=HEXTORAW(‘A06F734DEB35E1CFE38C9AB4729508E2′) AND (t.target_type=’rac_database’ OR (t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND m.target_guid=t.target_guid AND m.metric_guid=t.metric_guid AND t.metric_name=’tbspAllocation’ AND (t.metric_column=’spaceAllocated’) AND m.rollup_timestamp >= sysdate-365 AND m.rollup_timestamp … Read more

Tablespace Growth

by

SELECT b.tsname tablespace_name, MAX (b.used_size_mb) cur_used_size_mb, ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb FROM (SELECT a.days, a.tsname, used_size_mb, used_size_mb – LAG (used_size_mb, 1) OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days) inc_used_size_mb FROM ( SELECT TO_CHAR (sp.begin_interval_time, ‘MM-DD-YYYY’) days, ts.tsname, MAX ( ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024), 2)) used_size_mb FROM dba_hist_tbspc_space_usage tsu, dba_hist_tablespace_stat … Read more

ASM Disk Usage

by

SELECT target_name, diskgroup, MAX (DECODE (seq, 7, VALUE)) REDUNDANCY, MAX (DECODE (seq, 4, VALUE)) PERCENT_USED, MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB, MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB, MAX (DECODE (seq, 3, ceil(VALUE/1024))) FREE_GB, MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB, MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK, ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, … Read more

Databases with flashback not turned on

by

SELECT m.target_name, t.type_qualifier4 AS Role, m.column_label AS Flashback, m.VALUE AS Status FROM mgmt$metric_current m, mgmt$target t WHERE m.metric_label = ‘Flash Recovery’ AND m.column_label = ‘Flashback On’ AND m.VALUE = ‘NO’ AND m.target_name LIKE ‘%PRD%’ AND t.type_qualifier4 IN (‘Primary’, ‘Physical Standby’) AND t.target_name = m.target_name AND t.target_guid = m.target_guid ORDER BY t.type_qualifier4, m.VALUE

Target file system capacity report

by

SELECT DISTINCT st.target_name, st.mountpoint, ROUND ( (freeb / 1073741824), 2) AS “Free (GB)”, ROUND ( (sizeb / 1073741824), 2) AS “Size (GB)”, ROUND ( ( ( (sizeb – freeb) / sizeb) * 100), 2) AS “Used (%)” FROM MGMT$STORAGE_REPORT_LOCALFS st, mgmt$os_summary os WHERE st.target_name = os.HOST AND st.mountpoint = ‘/usr/local/oracle’ OR st.mountpoint LIKE ‘/databases%’ OR … Read more

Show all open incidents related to file system capacity

by

alter session set nls_date_format=’dd/mm/rrrr hh:mi:ss’ SELECT a.incident_id, b.creation_date, b.last_updated_date, b.ticket_id, b.summary_msg, c.target_name, DECODE (a.open_status, ‘1’, ‘Open’, ‘Closed’) FROM mgmt$INCIDENT_TARGET a, mgmt$incidents b, mgmt$target c WHERE a.incident_id = b.incident_id AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND LOWER (b.SUMMARY_MSG) LIKE ‘%filesystem%’ AND c.target_type = ‘host’ AND a.open_status = 1 ORDER BY b.last_updated_date DESC

Oracle – How to Empty the Listener.log File

by

Solution Step 1. Set the environment of the effected listener.log file If there are multiple listeners on the box, you can use the following syntax lsnrctl SET CURRENT_LISTENER [listener_name] $ lsnrctl LSNRCTL for Solaris: Version 8.1.7.4.0 – Production on 31-AUG-2012 08:21:39 (c) Copyright 1998 Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type “help” for … Read more

Update Enterprise Manager Targets from EMCLI 12c

by

How to bulk update Enterprise Manager Target Properties. Step-by-step guide Login to emcli (login to repository host as the Oracle user, enter the password when prompted) emcli login -username=sysman To extract a list of database targets, run the following (modify the output if required. For example, if you wish to emcli get_targets -noheader -format=’name:script;column_separator:|;’ -targets=’%database%’ … Read more

How To Save Database Statistics Before They Are Updated

by

Step-by-step guide Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload). The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very … Read more