Oracle Database Daily Check



---------------host check-----------------------
-----1.check disk usage of db server
     #df -h

-----2.check memory info of db server
     #free -m

--------------database check-----------------
-----1.check database link point correctly
rem check host correctly or not
SELECT * FROM DBA_DB_LINKS;

-----2.check if any error occurred in alert log
rem devapp just example instance
$cat /u01/app/oracle/diag/rdbms/devapp/devapp/trace/alert_devapp.log

-----3.check if there any invalid objects
SELECT OBJECT_NAME, OBJECT_TYPE
                   FROM USER_OBJECTS
                  WHERE STATUS = 'INVALID'

               
-----4.Are there any corrupted blocks
select * from v$database_block_corruption;

-----5.Are the crucial initialization parameters setting correctly, especially for the optimizer.
show parameter cpu_count       ----6     scope=both    
show parameter processes(pos)     ----1000     
SELECT * FROM v$log;               --app 500M,pos 2G    
show parameter memory max target    --- pos:8-10G app-2-4G     scope=spfile  

-----6.Is the database character set correctly
SELECT * FROM NLS_DATABASE_PARAMETERS;

-----7.Are there all foreign keys with the corresponding index
SELECT FKC.TABLE_NAME, FKC.CONSTRAINT_NAME, FKC.FK_COLS
  FROM (SELECT UC.CONSTRAINT_NAME,
               UC.TABLE_NAME,
               LISTAGG(UCC.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY UCC.POSITION) FK_COLS
          FROM USER_CONSTRAINTS UC
         INNER JOIN USER_CONS_COLUMNS UCC
            ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
         WHERE UC.CONSTRAINT_TYPE = 'R'
         GROUP BY UC.CONSTRAINT_NAME, UC.TABLE_NAME) FKC
  LEFT JOIN (SELECT UI.INDEX_NAME,
                    UI.TABLE_NAME,
                    LISTAGG(UIC.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY UIC.COLUMN_POSITION) INDEX_COLS
               FROM USER_INDEXES UI
              INNER JOIN USER_IND_COLUMNS UIC
                 ON UI.INDEX_NAME = UIC.INDEX_NAME
              GROUP BY UI.INDEX_NAME, UI.TABLE_NAME) INC
    ON FKC.TABLE_NAME = INC.TABLE_NAME
   AND FKC.FK_COLS = INC.INDEX_COLS
 WHERE INC.INDEX_COLS IS NULL;
-----8.Is the User Profile set correctly
 SELECT LIMIT
  FROM DBA_PROFILES
 WHERE PROFILE = 'DEFAULT'
   AND RESOURCE_TYPE = 'PASSWORD'
   AND RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
   alter profile default limit password_life_time unlimited;
  
-----9.Are the materialized view log purged correctly
     SELECT MASTER FROM USER_MVIEW_LOGS T WHERE NVL(LAST_PURGE_STATUS, 0) <> 0
    
-----------database space monitoring  -------------
----1.Space usage of each tablespace as well as backed up set. Is normal?
SELECT Upper(F.TABLESPACE_NAME)         "表空间名",
       D.TOT_GROOTTE_MB                 "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
       || '%'                           "使用比",
       F.TOTAL_BYTES                    "空闲空间(M)",
       F.MAX_BYTES                      "最大块(M)"
FROM   (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM   SYS.DBA_DATA_FILES DD
        GROUP  BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  BY 1;
SELECT * FROM Dba_temp_Files;
-----2.datafile directory usage is  over range
cd /u01/oradata
du -h

------------database backup----------------------
----1.check dump log error
rem check log exists error ora-*
----2.check backup time

------------ standby database-------------------
----1 Does the standby database catch up with the primary database? Are there any archived log gap between them?
----2 Are the archived log shipped onto standby and applied correctly and purged correctly? Are they matched to the backup strategy?
----3 Space usage of the archived log? on standby database