Database Daily Check List Scripts

Database Name:

Select name,open_mode from v$database;

ASM Diskgroups usage and Candidate disk details:

set linesize 400
col path for a35
select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;


select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

Database Startup Time:

Select to_char (startup_time, 'HH24:MI DD-MON-YY') "StartupTime" from v$instance;

Database Sysdate:

Select to_char(sysdate,'HH24:MI DD-MON-YY') "Date" from v$instance;

DB NAME, INSTANCE, OPEN MODE AND LOG MODE Details:

colu name format a20

colu StartTime format a30

colu SysDateTime format a30

set linesize 150

select to_char(startup_time,'DD-MM-YYYY HH24:MI:SS') StartTime, to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') SysDateTime, name,instance_name,log_mode, open_mode from dual, v$database, v$instance;

Database Size Information:

SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union

SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99)  FROM DBA_SEGMENTS union

SELECT 'SGA Size (GB):' || TO_CHAR(SUM(VALUE)/1024/1024/1024,9999999.99)  FROM V$SGA;

SELECT name,a.size_IN_GB,SYSDATE from V$DATABASE vd, (Select sum(bytes)/1024/1024/1024 size_IN_GB from dba_segments WHERE tablespace_name not like '%UNDO%') a;

Blocking session:

select blocking_session,sid,serial#,wait_class,seconds_in_wait from gv$session where blocking_session is not NULL order by blocking_session;

To Check Fregmented space:

col owner for a15

col table_name for a30

col TOTAL_SIZE for a25

col ACTUAL_SIZE for a20

col FRAGMENTED_SPACE for a25

select owner,table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",

round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",

round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",

(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"

from dba_tables WHERE owner in ('PDMUSER_OOTB','PDMUSER','WBMSTAGING_MIG', 'WBMSTAGING')  and AVG_ROW_LEN > 0 and NUM_ROWS > 0 order by 2, 6;

Non Oracle Users or Application Users:

set linesize 400

col username for a40

col ORACLE_MAINTAINED for a25

select USERNAME,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='N' order by 1;

RMAN full, Incremental and Archivelog backup Details:

Set linesize 400
set pages 4000
col output_bytes_display form a15
col input_bytes_display form a15
col STATUS form a25
col time_taken_display form a10
col INPUT_BYTES_PER_SEC_DISPLAY form a15
col OUTPUT_BYTES_PER_SEC_DISPLAY form a17
col input_rate_per_sec form a20
col output_rate_per_sec form a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
input_bytes_display,
output_bytes_display,
--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;

Database Last Analyse:

SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables
ORDER BY LAST_ANALYZED;

Database Hit Ratio:

SELECT 1- (P3.value - P4.value - P5.value) / (P1.value + P2.value) "HIT RATIO"
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3, v$sysstat P4, V$sysstat P5
     WHERE  P1.name = 'db block gets'
     AND    P2.name = 'consistent gets'
     AND    P3.name = 'physical reads'
     AND    P4.name = 'physical reads direct'
     AND    P5.name = 'physical reads direct (lob)';

Invalid Objects:

SELECT COUNT(1) FROM DBA_OBJECTS WHERE STATUS='INVALID';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS='INVALID';

Verify Rollback Segment:

Status should be ONLINE, not OFFLINE or FULL….  For names and status of ALL rollback segment,
SELECT TABLESPACE_NAME, SEGMENT_NAME, STATUS FROM  DBA_ROLLBACK_SEGS;

Optimizer Statistics:

SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables
Where TO_CHAR (last_analyzed, 'yyyy/mm/dd')>    '&date'   ORDER BY LAST_ANALYZED;
SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables
ORDER BY LAST_ANALYZED;

Database Growth:

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" ,
round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" ,
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile
union all select bytes from v$tempfile union all select bytes from v$log) used ,
(select sum(bytes) as p from dba_free_space) free group by free.p;

Tablespace Status:

SET PAGESIZE 50000
SET LINESIZE 400
COLUMN NAME FORMAT A35
COLUMN DATAFILES FORMAT A50
COLUMN TABLESPACE FORMAT A25
COLUMN OWNER FORMAT A15
Prompt TOTAL AND USED SPACE
SELECT Total.name "Tablespace Name",
                nvl(Free_space, 0) Free_space,
                nvl(total_space-Free_space, 0) Used_space,
                total_space
  FROM
    (select tablespace_name, sum(bytes/1024/1024) Free_Space
              from sys.dba_free_space
             group by tablespace_name
              ) Free,
        (select b.name,    sum(bytes/1024/1024) TOTAL_SPACE
              from sys.v_$datafile a, sys.v_$tablespace B
             where a.ts# = b.ts#
             group by b.name
       ) Total
  WHERE Free.Tablespace_name(+) = Total.name
  ORDER BY Total.name;

Locked Objects Identification And Resolution

STEP-1

Select  oracle_username, os_user_name,locked_mode,object_name,

Object_type from v$locked_object a, dba_objects b 

where a.object_id=b.object_id order by a.oracle_username;

STEP-2 (TO FIND THE SID OWNER OF LOCKED OBJECTS)

Select Sid, owner from v$access

where object='NAME OF THE OBJECT';

STEP-3(TO FIND THE SERIAL# AND STATUS)

Select username,sid,serial#,status from v$session where sid=   ;

STEP-4( TO KILL THE SESSION WHICH HAVE BEEN LOCKED THE SESSION)

Alter system kill session 'sid,serial#';

Invalid Indexes:

Select index_name,STATUS from dba_indexes where status='INVALID';

Running Jobs:

Select * from dba_jobs_running;
Select /*+ rule */ * from dba_jobs_running;(9i Version)

Broken Jobs:

Select count(*), broken, schema_USER, FAILURES from user_jobs where broken = 'Y' and LAST_DATE <= SYSDATE - 1/24 group by broken, schema_USER, FAILURES;

Select count(*), broken, schema_USER, FAILURES from user_jobs where broken = 'N' and LAST_DATE <= SYSDATE - 1/24 group by broken, schema_USER, FAILURES;

Full Table Scan Finding:

SELECT   sp.object_owner, sp.object_name,
         (SELECT sql_text
            FROM v$sqlarea sa
           WHERE sa.address = sp.address
             AND sa.hash_value = sp.hash_value) sqltext,
         (SELECT executions
            FROM v$sqlarea sa
           WHERE sa.address = sp.address
             AND sa.hash_value = sp.hash_value) no_of_full_scans,
         (SELECT    LPAD (NVL (TRIM (TO_CHAR (num_rows)), ' '),
                          15,
                          ' '
                         )
                 || ' | '
                 || LPAD (NVL (TRIM (TO_CHAR (blocks)), ' '), 15, ' ')
                 || ' | '
                 || BUFFER_POOL
            FROM dba_tables
           WHERE table_name = sp.object_name AND owner = sp.object_owner)
                                                           "rows|blocks|pool"
    FROM v$sql_plan sp
   WHERE operation = 'TABLE ACCESS'
     AND options = 'FULL'
     AND object_owner NOT IN ('SYS','SYSTEM')
ORDER BY 1, 2;

Tablespace Datafile Free Space:

SELECT  SUM(BYTES)/1024/1024 /1024  FROM DBA_DATA_FILES;

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024  FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

Select (bytes)/1024/1024/1024,file_name,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='TABLESPACE_NAME';

Temp query:

SELECT SUM(total_blocks*8192/1024/1024) Occupied,
       SUM(used_blocks*8192/1024/1024)  Using,
       SUM(free_blocks*8192/1024/1024)  Free
  FROM gv$sort_segment
-- WHERE tablespace_name = upper('&temp_tablespace_name');

Temp Out Of Space:

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Select tablespace_name, sum(bytes_used), sum(bytes_free) from v$temp_space_header group by tablespace_name;

Temp utilization by session:

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

         COUNT(*) sort_ops

FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE    T.session_addr = S.saddr

AND      S.paddr = P.addr

AND      T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

         S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

To See The Which Tables Need To Shrink/Re-org:

select tablespace_name, segment_name,segment_type, partition_name,recommendations,c1 from

table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))

order by reclaimable_space desc;

Find PID from SID:

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = &sid;

Long Session Details:

set lines 200 pages 200

col sid for 9999

col serial# for 999999

col spid for 999999

col status for a10

col username for a10

col TIM for 9999.99

col module for a16

col program for a20

col osuser for a15

select s.sid,s.serial#,p.spid,s.status,s.username,s.last_call_et/60 tim,

s.module,s.program,s.sql_hash_value,s.osuser

from v$session s,v$process p

where s.type<>'BACKGROUND'

and s.paddr=p.addr

and s.last_call_et/60>10

and s.program  not like '%CJQ%'

and s.program  not like '%QMN%'

order by 4,6;

Findout calling which procedure:

select 'CALLED PLSQL', vs.username, d_o.object_name -- whatever info you need

  from dba_objects d_o

       inner join

       v$session vs

          on d_o.object_id = vs.plsql_entry_object_id

union all

select 'CURRENT PLSQL', vs.username, d_o.object_name

  from dba_objects d_o

       inner join

       v$session vs

          on d_o.object_id = vs.plsql_object_id;

To Find Blocking Session History:

SELECT  distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,

a.user_id,s.sql_text,a.module

FROM  V$ACTIVE_SESSION_HISTORY a, v$sql s

where a.sql_id=s.sql_id

and blocking_session is not null

and a.user_id <> 0 

and a.sample_time between to_date('24/04/2014 11:00', 'dd/mm/yyyy hh24:mi') 

and to_date('24/04/2014 23:50', 'dd/mm/yyyy hh24:mi');

Datafile Free Size:

col FILE_NAME format a60

SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))

used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM dba_data_files df, dba_free_space dfs

WHERE df.FILE_ID = dfs.file_id(+) and df.tablespace_name='&TABLESPACE_NAME'

GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes

UNION ALL

select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) -  (user_bytes/1024/1024)) "free_space_mb" from dba_temp_files; 

Undo Check:

Select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

Create standby controlfile:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/prdstdctl_R.sql';
Database altered.

RMAN>COPY CURRENT CONTROLFILE FOR STANDBY TO '/tmp/pdistdctl_R.sql';
RMAN>backup current controlfile for standby format '/u01/backup/standbycontrol.ctl';

Kill Multiple Sessions:

select 'alter system kill session '''||sid||','||serial#||'''immediate;' from v$session where username not in ('SYS','SYSTEM') and sid IN (select s.sid from v$session s,v$process p where s.paddr=p.addr
and s.last_call_et > (60*60*24));

select 'alter system kill session '''||sid||','||serial#||'''immediate;' from v$session where sid IN (select s.sid from v$session s,v$process p where s.paddr=p.addr );
select 'alter system kill session '''||sid||','||serial#||'''immediate;' from v$session where username='BTM';

select 'alter system kill session '''||sid||','||serial#||'''immediate;' from v$session where username not in ('SYS','SYSTEM') and sid IN
(select s.sid from v$session s,v$process p where s.paddr=p.addrand s.username='BTM');

select username,osuser,sid || ',' || serial# "ID",status,to_char(logon_time, 'hh24:mi dd/mm/yy') login_time,last_call_et
from v$session where username is not null and username like '%BTM%';

Longoops:

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;
Select owner,job_name,job_type,job_action,ENABLED,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs where job_name like '%MILESTONE%';

Number Of CPU's In Windows:

WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

Change The Table Columns for Multiple Tables:

select  'alter table ' || table_name || ' modify EMPL_CODE char(8);' from user_tables;

Ex: Alter table D_EMPL_FAMILY MODIFY EMPL_CODE char(8);

Archive Log History:

Set linesize 400;
Select thread#, sequence#, first_change#,  to_char(first_time,'dd/mm/yyyy:hh:mi:ss') FirstTime,
next_change#, applied, to_char(completion_time,'dd/mm/yyyy:hh:mi:ss') CompTime from v$archived_log;
Select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

How To Check Archives Generation Per Hour:

set line 2000
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
where to_date(first_time) > sysdate-60
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time);

Generate Snap Id Manually:

EXECUTE dbms_workload_repository.create_snapshot();

Monitor Session With Trace File:

exec sys.dbms_system.set_sql_trace_in_session(684,8305,TRUE);

exec sys.dbms_system.set_sql_trace_in_session(684,8305,FALSE); 

exec dbms_monitor.session_trace_enable(684,8305,TRUE);

select spid from v$process where addr=(select paddr from v$session where sid=684); 

Set And Remove ORADIM On Windows:

Delete old: ORADIM

Go to E:\oracle\DNK\11203\BIN

oradim -DELETE -SRVC OracleServiceDNK

oradim -NEW -SID DNK -SRVC OracleServiceDNK

oradim -DELETE -SID DNK

oradim -NEW -SID DNK -STARTMODE manual -PFILE E:\oracle\DNK\11203\database\initDNK.ora

oradim -STARTUP -SID DNK -STARTTYPE inst -PFILE E:\oracle\DNK\11203\database\initDNK.ora

Generate Backup Dump Mysql:

mysqldump -u root --all-databases  /mysql/mysqldb/dbbackup/all-database.sql

mysqldump -u root -p'Root@123' --all-databases > /mysql/mysqldb/dbbackup/all-database+`date '+%Y%m%d_%H%M%S'`.sql;

Rman Backup Verify:

SELECT BS_KEY, I.NAME, BACKUP_TYPE, B.PIECES,B.CONTROLFILE_INCLUDED, COMPLETION_TIME, KEEP_UNTIL FROM rman.RC_DATABASE_INCARNATION i, rman.RC_BACKUP_SET bWHERE i.DB_KEY = b.DB_KEY AND i.CURRENT_INCARNATION = 'YES' AND COMPLETION_TIME>SYSDATE-1
ORDER BY 6;

Data guard:

show parameter db_name;

archive log list;

select max(sequence#),applied from v$archived_log group by applied;

select sequence#,applied from v$archived_log order by 1;

select count(*) from v$archived_log where applied='NO' order by 1;

select * from v$archive_gap;

select thread#,sequence#,applied from v$archived_log;

Select process, status, thread#, sequence#, block#  from v$managed_standby;

Select name,open_mode,database_role,protection_mode,current_scn from v$database;

set linesize 400;

set echo off;

archive log list;

Set linesize 400;

Select thread#, max(sequence#) as "last_applied_log" from v$log_history group by thread#;

Select status as Dest_1_Status, error from v$archive_dest where dest_id=1;

Select status as Dest_3_Status, error from v$archive_dest where dest_id=3;

Select process, status, thread#, sequence#, block#  from v$managed_standby;

Verify Success Of Database Archiving:

SELECT NAME, TO_CHAR (COMPLETION_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > '22-FEB-2013';

ARCHIVE LOG LIST;

CPU PID:

ps aux |head -1 |ps aux |sort -nr +2 | head -20

CPU Usage:

RAM: svmon -G

CPU:vmstat 2 3

Topas

Paging Size AIX:

lsps -as

###################################### Copyrights @ Sreenu Allipudi 2016 ######################################