This discussion is archived
8 Replies Latest reply: Sep 29, 2010 10:58 PM by BillyVerreynne RSS

Oracle DBA Daily/Weekly checks

587671 Journeyer
Currently Being Moderated
What type of Oracle Db checks/tasks do you perform on a daily/weekly basis? I found a good source of task to check/complete here

http://examples.oreilly.com/ordbacheck/dba_checklist14.doc

Can anybody add to this ?
  • 1. Re: Oracle DBA Daily/Weekly checks
    RobertGeier Oracle ACE
    Currently Being Moderated
    Using scripts like the ones recommended on Orielly will fill your inbox with garbage, and if you run hundreds or thousands of databases you will never get time to read the reports. They are OK as a way to learn the data dictionary views and how to manage a database, but you really don't want to be manually running them for very long.

    DBAs should try to avoid manually checking anything daily or weekly. All checks should be automated, and should alert the DBA only if there is an errror (by email or page or sms). This is why DBAs need good shell scripting skills. OEM or other monitoring tools (Quest, Tivoli etc) can reduce the number of scripts you need to write.

    Many of the scripts in the attachment were written for older database versions will not give any real benefit in 10g. (e.g next extent, index tablespace)

    Checks like blocking locks, alert log errors, connected sessions etc should run at least every 5 minutes. Checks for disk space should run at least every hour. Checks for inactive accounts, account sharing etc should run monthly .... it all depends on how how important your databases are and how much DBA resource you have for scripting, monitoring, and fixing ...

    ---------------------------------------------
    http://www.ContractOracle.com
  • 2. Re: Oracle DBA Daily/Weekly checks
    587671 Journeyer
    Currently Being Moderated
    We have internal daily/weekly 'Socks' checks, these cover manually checking alert logs, tablespace usage, inactive accounts etc, These get logged and check/audited on a year basis via PWC, Doing this enables us from an IT perceptive to be J-Sox (Japanese Sox) compliant
  • 3. Re: Oracle DBA Daily/Weekly checks
    RobertGeier Oracle ACE
    Currently Being Moderated
    Funny how every auditor applies SOX rules differently.

    There is no need to manually check anything for SOX. All you need to do is automate the checks, document what is being checked, and keep the output and logfiles to show the auditor if they ask.

    ---------------------------------------------
    http://www.ContractOracle.com
  • 4. Re: Oracle DBA Daily/Weekly checks
    587671 Journeyer
    Currently Being Moderated
    I agree, I feel SOX rules are different from company to company because each company has a different interpretation of the rules.. Where still using Oracle 9i, Next year where going to start using 10g with GRID !!, I’m sure its going to be a bit easier/less time consuming then ...
  • 5. Re: Oracle DBA Daily/Weekly checks
    525507 Journeyer
    Currently Being Moderated
    As already said most of these activities should be atomized. Many of the tasks mentioned bellow depends upon many factors like purpose of database,size of database etc

    Daily
    a) alert log error checking
    b) Tablespace space checking
    c) Checking for the scheduled backup for successful backup.
    d) backing up backup to tape or.. reminding system admin to back it up.
    e) archive log space checking and taking backup of same
    f) Troubleshooting if any issue reported
    g)many more

    Weekly
    a) statistics gathering
    b) Index rebuild if needed
    c) Database Health check
    d) Any other scheduled tasks

    this is only partial list of dba activity
  • 6. Re: Oracle DBA Daily/Weekly checks
    587854 Newbie
    Currently Being Moderated
    Hi

    I think it will be more helpful

    1.     Verify all instances are up.
    2.     Invalid Objects
    3.     DB Info
    column hostname format a13
    column stime format a35
    column uptime format a35
    column instance_name format a10
    prompt "DB Info"
    select host_name Hostname
    ,instance_name InstanceName
    ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
    ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
    trunc( 24*((sysdate-startup_time) -
    trunc(sysdate-startup_time))) || ' hour(s) ' ||
    mod(trunc(1440*((sysdate-startup_time) -
    trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
    mod(trunc(86400*((sysdate-startup_time) -
    trunc(sysdate-startup_time))), 60) ||' seconds' uptime
    from sys.v_$instance;

    4.     Block Contention

    SELECT class,sum(count) total_waits, sum(time) total_time FROM v$waitstat GROUP BY class;

    5.     Latch Contention

    SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/decode((b.immediate_misses+b.immediate_gets),0,1) ratio2
    FROM v$latchname a, v$latch b WHERE
    a.latch# = b.latch# AND b.sleeps > 0;

    6.     Find Chained rows

    select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt,(chain_cnt/num_rows*100) as perc from dba_tables where owner not in ('SYS','SYSTEM') and table_name not in (select table_name from dba_tab_columns
    where data_type in ('RAW','LONG RAW') )
    and chain_cnt > 0 order by chain_cnt desc;

    7.     Locks

    select /*+ ordered */ w1.sid waiting_session,
         h1.sid holding_session,
         w.kgllktype lock_or_pin,
    w.kgllkhdl address,
         decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         'Unknown') mode_held,
         decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         'Unknown') mode_requested
    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
    where
    (((h.kgllkmod != 0) and (h.kgllkmod != 1)
    and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
    and
    (((w.kgllkmod = 0) or (w.kgllkmod= 1))
    and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
    and w.kgllktype     = h.kgllktype
    and w.kgllkhdl     = h.kgllkhdl
    and w.kgllkuse = w1.saddr
    and h.kgllkuse = h1.saddr
    /

    8.     Information on top sessions ordered by Reads / executions

    COLUMN username FORMAT A15
    COLUMN machine FORMAT A25
    COLUMN logon_time FORMAT A20

    SELECT NVL(a.username, '(oracle)') AS username,
    a.osuser,
    a.sid,
    a.serial#,
    c.value AS &1,
    a.lockwait,
    a.status,
    a.module,
    a.machine,
    a.program,
    TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
    FROM v$session a,
    v$sesstat c,
    v$statname d
    WHERE a.sid = c.sid
    AND c.statistic# = d.statistic#
    AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
    'EXECS', 'execute count',
    'CPU', 'CPU used by this session',
    'CPU used by this session')
    ORDER BY c.value DESC;


    9.     Top SQL statements that are using the most resources

    SELECT *
    FROM (SELECT Substr(a.sql_text,1,50) sql_text,
    Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
    a.buffer_gets,
    a.disk_reads,
    a.executions,
    a.sorts,
    a.address
    FROM v$sqlarea a
    ORDER BY 2 DESC) WHERE rownum <= 10


    10.     Undo information on relevant database sessions

    SELECT s.username,
    s.sid,
    s.serial#,
    t.used_ublk,
    t.used_urec,
    rs.segment_name,
    r.rssize,
    r.status
    FROM v$transaction t,
    v$session s,
    v$rollstat r,
    dba_rollback_segs rs
    WHERE s.saddr = t.ses_addr
    AND t.xidusn = r.usn
    AND rs.segment_id = t.xidusn
    ORDER BY t.used_ublk DESC;


    11.     I/O information on top Physical reads/ Consistent get sessions


    COLUMN username FORMAT A15

    SELECT NVL(s.username, '(oracle)') AS username,
    s.osuser,
    s.sid,
    s.serial#,
    si.block_gets,
    si.consistent_gets,
    si.physical_reads,
    si.block_changes,
    si.consistent_changes
    FROM v$session s,
    v$sess_io si
    WHERE s.sid = si.sid
    ORDER BY s.username, s.osuser;

    12.     List of Non Indexed Foreign key.

    SELECT t.table_name,
    c.constraint_name,
    c.table_name table2,
    acc.column_name
    FROM all_constraints t,
    all_constraints c,
    all_cons_columns acc
    WHERE c.r_constraint_name = t.constraint_name
    AND c.table_name = acc.table_name
    AND c.constraint_name = acc.constraint_name
    AND NOT EXISTS (SELECT '1'
    FROM all_ind_columns aid
    WHERE aid.table_name = acc.table_name
    AND aid.column_name = acc.column_name)
    ORDER BY c.table_name;


    13.     SQL statements for the current database sessions with PID.

    COLUMN username FORMAT A15
    COLUMN machine FORMAT A25
    COLUMN logon_time FORMAT A20

    SELECT NVL(a.username, '(oracle)') AS username,
    a.osuser,
    a.sid,
    a.serial#,
    c.value AS &1,
    a.lockwait,
    a.status,
    a.module,
    a.machine,
    a.program,
    TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
    FROM v$session a,
    v$sesstat c,
    v$statname d
    WHERE a.sid = c.sid
    AND c.statistic# = d.statistic#
    AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
    'EXECS', 'execute count',
    'CPU', 'CPU used by this session',
    'CPU used by this session')
    ORDER BY c.value DESC;

    14.     Memory allocation for the current running sessions.

    COLUMN username FORMAT A20
    COLUMN module FORMAT A20

    SELECT NVL(a.username,'(oracle)') AS username,
         a.sid,
    a.module,
    a.program,
    Trunc(b.value/1024) AS memory_kb
    FROM v$session a,
    v$sesstat b,
    v$statname c
    WHERE a.sid = b.sid and a.username is not null
    AND b.statistic# = c.statistic#
    AND c.name = 'session pga memory'
    AND a.program IS NOT NULL ORDER BY b.value DESC;


    VENKAT
  • 7. Re: Oracle DBA Daily/Weekly checks
    manish (vnl) Newbie
    Currently Being Moderated
    @Venkat

    Can you please help me understanding about these scripts. Please elaborate the script functionality and how to correct the issue if output shows unwanted result.
  • 8. Re: Oracle DBA Daily/Weekly checks
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Please do not resurrect old threads to deal with your issues.

    Use a new posting for your issue. By all means add a URL to an old posting for reference.

    But using a 2 year old thread to ask a question is not really acceptable in any forum.

    Besides by doing this, you're likely shooting yourself in the foot. The person that you are addressing your question at has not posted in OTN for over a year if I'm not mistaken. So who do you expect will provide you with meaningful answers?

    If instead you have posted a new posting asking your question, that would have been aimed at all forum members here.. and not just a single person that no longer seems to participate on OTN, via an old thread that many will simply ignore.