DBA Daily Task

If you're into the Production support perform below things (DAILY CHECK)
Health check of the Database should be done and it includes

     Check the Database Uptime
    Also check the Listener status and the listener uptime
    Make sure all the backups are Successful as per scheduled in the Crontab
    Backups include Full incremental backup,Export Job,Archive log backup,Hot backup as per your company backup strategy.
    Make sure there are no Blocking Locks in your database
    Also check the Process & Sessions parameter, If process and sessions parmeter reach above threshold then no new users will be able to connect to DB.
    If there are any ORA Errors in your alert log act accordingly and work for resolution.
    Try to resolve the frequent recurring Internal errors ORA-600 & ORA-07445 with Oracle Support.
    Make sure all your Table space usage is below threshold.
    If it's a DG enabled database make sure you check the sync between primary DB & the DR for every 15 minutes.
    Make sure all your File System  (Mount Points) are below threshold.

Check the Database Uptime

set linesize 200
set trimout on
set space 1
col INSTANCE for a10
col MODE for a11
col ROLE for a10
col HOST for a20
col STATUS for a7
col STARTUP_TIME for a20
select NAME "DB_NAME",INSTANCE_NAME "INSTANCE",OPEN_MODE "MODE",STATUS,DATABASE_ROLE "ROLE",HOST_NAME "HOST", to_char(STARTUP_TIME,'MON/DD/YYYY hh24:mi:ss')startup_time from gv$database NATURAL JOIN gv$instance;

DB_NAME   INSTANCE   MODE    STATUS  ROLE   HOST       STARTUP_TIME
--------- ---------- ----------- ------- ---------- -------------------- --------------------
Pikku    Pikku1      READ WRITE  OPEN    PRIMARY         a12ft5    MAY/25/2016 02:36:57
Checking the Listener status and the listener uptime

 Lsnrctl status <Listener name>
lsnrctl status LISTENER_PROD LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 24-MAY-2016 13:39:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PROD.WORLD))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_PROD
Version                   TNSLSNR for HPUX: Version 11.2.0.4.0 - Production
Start Date                12-MAY-2016 01:01:39
Uptime                    232 days 12 hr. 27 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Checking for Blocking Locks

 Connect to you Database and query the "dba_blockers" view

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
           2285
 Query to check which Session  is blocking

SQL> select s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' ) is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
 from v$lock l1, v$session s1, v$lock l2, v$session s2
 where s1.sid=l1.sid and s2.sid=l2.sid
 and l1.BLOCK=1 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------

( SID=2285 ) is blocking  ( SID=2675 )
 Killing Blocking Locks
 Checking Process & Sessions Usage

set lines 1000
select * from v$resource_limit;
select count(*),status from gv$session group by status;
select value from v$parameter where name = 'processes'; select count(*) from v$session;
 
Here we have lots of Inactive sessions
SQL> select SID,SERIAL#,STATUS from v$session;

       SID    SERIAL# STATUS
---------- ---------- --------
         2      17815 INACTIVE
         3      24181 INACTIVE
         4      38429 INACTIVE
         6      31187 INACTIVE
  You can kill the Inactive sessions at O.S level or at Database level
Killing at O.s Level
  kill -9 Server PID
Killing at Database Level
Sync Check between primary DB & DR (Standby DB)

 SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- ---------------------     ---------------------
         1                   5869                    5869                     0