Day-To-Day Activities in DBA life and some useful scripts

Oracle DBA Daily Checklist
1. Check that all instances are up.
2. Monitor alert log entries (using tail -f)
3. Make sure the listener status  is running.
4. Check all last night backups were successful.
5. Check all database archiving are done.
6. Check tablespaces should not be used more that 95%.
7. Check all cronjobs and Oracle Jobs are completed without any error
8. Verify resources for acceptable performance.
9. Identify bad growth of Segments.
10. Identify  top resource consuming query through top/awr

To Find out the total size of the DB and Database growth weekly/monthly
for future necessary space arrangements
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;
Materializedviews Not Refreshed from Last 7 Days:
select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate - 7;
Total Number of Tables:
select count(1) from user_tables where table_name not like '%$%'
Total Number of Mviews:
select count(1) from user_mviews
Total Number of Indexes:
select count(1) from user_indexes where index_type in ('FUNCTION-BASED NORMAL','NORMAL')
Total Number of Invalid Objects:
select count(1) from user_objects where status = 'INVALID'
Total Number of Objects Created in last 7 days:
select count(1) from user_objects where CREATED >= sysdate - 7 
Query to find the 10 Largest Objects in DB:
 select * from
(
select
    SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME
from  dba_segments order by 3 desc )
where rownum <= 10

SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'
  FROM user_tables


du -ka * | sort -rn | head-50

To Purge the Trace/Incidents of one month older using ADRCI tool :-

purge -age 43200

Find and remove command on linux :-

find /apps/oracle/diag/tnslsnr/m5db02/listener/alert/*.xml -mtime +5 -exec rm {} \;

syntax export/import backup to take with sys user

exp \'/ as sysdba\' dump=xxx.dmp full=y log=xxx.log

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
$ORACLE_HOME/OPatch/opatch lsinventory -details
opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./



For  tablular display of clusterware resources
crsctl status res -t 

For cluster status 
crsctl check crs             # On local node
crsctl check cluster -all            # Global view of cluster daemon status



* Check index count over a specific schema

 SELECT owner AS SCHEMA, object_type, COUNT ( * ) AS "Number" FROM   all_objects
 WHERE   owner = 'LABWARE' GROUP BY   owner, object_type order by 2;

* Check what are the different object count in a schema 

 SELECT owner AS SCHEMA, object_type, COUNT ( * ) AS "Number" FROM   all_objects
 WHERE   owner = 'LABWARE' and object_type='INDEX' GROUP BY   owner, object_type order by 2;

* Monitor import datapmup progress

 select substr(sql_text,instr(sql_text,'INTO "'),30) table_name, rows_processed,
 round((sysdate- to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute 

 from   sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0;