Skip to main content

Posts

Showing posts from August, 2017

12c. Monthly Database Growth from OEM 12c.

SELECT alloc.TARGET_NAME,TO_DATE(TO_CHAR(alloc.timestamp, 'MON RR'), 'MON RR') AS CALENDAR_MONTH,        alloc.tablespace AS TABLESPACE,        round(avg(alloc.avg_size_mb), 2) AS SIZE_MB,        round(avg(used.avg_used_mb), 2) AS USED_MB,        round(avg(alloc.avg_size_mb - used.avg_used_mb), 2) AS FREE_MB,        round(avg((used.avg_used_mb*100)/ decode(alloc.avg_size_mb, 0, 1, alloc.avg_size_mb)), 2) AS USED_PCT,        round(max(alloc.max_size_mb), 2) AS SIZE_MAX_MB,        round(max(used.max_used_mb), 2) AS USED_MAX_MB,        round(max(alloc.avg_size_mb - used.avg_used_mb), 2) AS FREE_MAX_MB,        round(max((used.avg_used_mb*100)/decode(alloc.avg_size_mb, 0, 1, alloc.avg_size_mb)), 2) AS USE...

Oracle: Tracking failed logon attempts

The Oracle auditing utility has this command: audit create session whenever not successful; To enable auditing of failed sign-on attempts: 1 - Add initialization parameters & bounce instance: audit_trail=true audit_file_dest='/u01/app/oracle/mysid/mydir/' 2 - Enable auditing of failed logion attempts as SYSDBA: SQL> audit create session whenever not successful; 3 - You can now view failed login attempts in dba_audit_trail: select    os_username,    username,    terminal,    to_char(timestamp,'MM-DD-Y YYY HH24:MI:SS') from    dba_audit_trail; OS_USERNAME     USERNAME        TERMINAL        TO_CHAR(TIMESTAMP,' --------------- --------------- --------------- ------------------- fred         SCOTT              app93   ...

Finding the origin of failed login attempts

Finding the origin of failed login attempts The Oracle auditing utility has this command: audit create session whenever not successful; To enable auditing of failed sign-on attempts: 1 - Add initialization parameters & bounce instance: audit_trail=true  audit_file_dest='/u01/app/oracle/mysid/mydir/' 2 - Enable auditing of failed logion attempts as SYSDBA: SQL> audit create session whenever not successful; 3 - You can now view failed login attempts in dba_audit_trail: select    os_username,    username,    terminal,    to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') from    dba_audit_trail;  OS_USERNAME     USERNAME        TERMINAL        TO_CHAR(TIMESTAMP,'  --------------- --------------- --------------- ------------------- fred         SCOTT        ...

OEM SQL

DB Growth   Month wise....    ================================================= SELECT TARGET_NAME,        decode(metric_column, 'spaceAllocated', 'AVERAGE_SIZE_GB', 'spaceUsed', 'AVERAGE_USED_GB'),        month_timestamp,        avg(value) FROM   (SELECT m.TARGET_NAME,           m.metric_column AS metric_column,           TO_DATE(TO_CHAR(m.rollup_timestamp, 'YYYY-MM'), 'YYYY-MM') AS month_timestamp,           sum(m.average/1024) AS value    FROM mgmt$metric_daily m,         mgmt$target_type t    WHERE (t.target_type='rac_database'           OR t.target_type='oracle_pdb'        ...

Database Growth SQL for OEM Repo DB.

SELECT b.TARGET_NAME,        c.HOST_NAME,        MAX(b.VALUE),        MIN(b.value),        MAX(b.MONTH_timestamp),        MIN(b.MONTH_timestamp),        ROUND((MAX(b.VALUE) - MIN(b.value)), 1) Diff,        ROUND(((ROUND((MAX(b.VALUE) - MIN(b.VALUE)), 2) * 100) / (MIN(b.VALUE))), 0) Per,   (SELECT '%'    FROM dual) "%" FROM   (SELECT a.TARGET_NAME,           a.METRIC_COLUMN,           a.MONTH_timestamp,           AVG(a.VALUE) Value    FROM      (SELECT DISTINCT m.TARGET_NAME,                     ...