oracle – DB Check (diverse Checks)

Dieser DB-Check prüft Oracle seitig ob alles korrekt eingestellt ist. Der Check sollte so aufgebaut / erweitert werden, das diese einfach abgefragt werden kann und wenn möglich auch gleich eine Lösung (z.b. für Pikett) ausgeben kann.

Konfiguriert werden die folgenden Skripte, wenn nicht anders definiert, unter dem User DB_CHECK.

Berechtigung
Da der DBW durch seine View’s auf Systemtabellen zugreifen soll, benötigt er dafür Select Rechte mit GRANT OPTION

grant select on dba_users       to DB_CHECK with grant option;
grant select on dba_objects     to DB_CHECK with grant option;
grant select on dba_tables      to DB_CHECK with grant option;
grant select on dba_indexes     to DB_CHECK with grant option;
grant select on dba_data_files  to DB_CHECK with grant option;
grant select on dba_free_space  to DB_CHECK with grant option;
grant select on dba_tablespaces to DB_CHECK with grant option;
grant select on dba_lobs        to DB_CHECK with grant option;

View Users
Um die Handhabung mit den einzelnen Oracle-, RO-, IS-O-Mgmt-, SAM- und Application-User zu vereinfachen muss folgende View erstellt werden. Diese Bewertet die User wodurch diese besser Gruppiert selektiert werden können.

create or replace view dbw.dbcheck_users
as
select  username,account_status,lock_date,created,
        case when username in ('SYS','SYSTEM','OUTLN','DBSNMP','APPQOSSYS','WMSYS','EXFSYS','CTXSYS','XDB','ORDSYS','ORDDATA','MDSYS','ORACLE_OCM','XS$NULL','DIP','ANONYMOUS','ORDPLUGINS','SI_INFORMTN_SCHEMA')
                then 9 --Default Oracle Users
             when username in ('AB_RO','SWE_RO')
                then 5 -- Read Only Users
             when username in ('HYPERIC_ORACLE','DBW','SNAP_MANAGER','PERFSTAT')
                then 1 -- Mgmt Users (DBA-Team)
             when username like '%USER0%'
                then 3 -- Application Access User
             when username in ('APPDDBMON')
                then 2 -- Mgmt Users (SAM-Team)
                else 0 -- Application Users
            end user_typ
  from  dba_users;

Table Skript
Um mögliche Lösungsvorschläge gleich ausgeben zu können, werden die Lösungsvorschläge in einer Tabelle abgelegt.

Die Idee ist, das einzelne Variablen im Skript ersetzt werden können um das Skript ausführbar zu machen:

create table dbcheck_skripts
    (
    msg_id          number(11)      primary key,
    beschreibung    varchar2(4000)  null,
    skript          varchar2(4000)  null
    );
 
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(1,'Object is invalid',                'alter %TYP %USERNAME.%OBJ compile;');
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(2,'User is Locked or Expired',        '--Check if this user is needed. If yes then '||chr(10)||'alter %USERNAME account unlock;');
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(3,'Tablespace Free is under 20%',     '--Add additional Datafile'||chr(10)||'select  ''ALTER TABLESPACE %OBJ ADD DATAFILE ''''''||path||substr(filename,0,length(filename)-2)||lpad(cast(inte as number(2))+1,2,''0'')||''.dbf'''' SIZE 32M AUTOEXTEND ON NEXT 32G MAXSIZE 4G;''from (select  substr(max(file_name),0,instr(max(file_name),''/'',-1)) path,replace(substr(max(file_name),instr(max(file_name),''/'',-1)+1),''.dbf'') filename,substr(replace(max(file_name),''.dbf''),-2) inte from  dba_data_files where tablespace_name = ''%OBJ'')');
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(4,'Index (NORMAL) is invalid',        'alter index %USERNAME.%OBJ rebuild;');
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(5,'Index is in wrong Tablespace',     'alter index %USERNAME.%OBJ rebuild tablespace %USERNAME_INDEXES;');
insert into dbcheck_skripts (msg_id,beschreibung,skript)values(6,'Index-LOB is in wrong Tablespace', 'alter table %USERNAME.%OBJ move lob(%DBATBL) store as (tablespace %USERNAME_INDEXES);');
 
commit;

View DBCheck
Diese View sucht mögliche Probleme in den einzelnen Systemtabellen.

create or replace view dbw.dbcheck
as
-- Search invalid USers
    select  'dba_users'dbatbl,username,username obj,'USER' typ,'User is Locked or Expired'msg,2 msg_id
      from  dba_users
      where account_status not in ('OPEN')
      and   lock_date >= trunc(sysdate -7)
    union all
-- Search Invalid Indexes
    select  'dba_indexes',owner,object_name,object_type,initcap(object_type)||' is invalid',1
      from  dba_objects
      where status !='VALID'
    union all
-- Search Invalid Objects
    select  'dba_objects',owner,index_name,index_type,'Index ('||index_type||') is invalid',case when index_type = 'NORMAL' then 4 else null end
      from  dba_indexes
      where status !='VALID'
      and   owner not in (select username from dbw.dbcheck_users where user_typ in (9) )
    union all
-- Search Tablespaces with less than 20% free space
    select  'dba_tablespaces',null,tsname,'TABLESPACE','Tablespace need extend ('||round(pct,2)||'% Free ('||round(mfb,0)||'MB Free))',3
      from (SELECT dt.tablespace_name tsname,
                   CASE
                     WHEN ((t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0))>= 0)
                       AND(nvl(u.s / (1024*1024),0)-t.mb / (1024*1024) < 0)
                     THEN (t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0))
                     ELSE  nvl(u.s / (1024*1024),0)
                   END mfb,
                   CASE
                     WHEN ((t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0)) >= 0)
                       AND(nvl(u.s / (1024*1024),0)-t.mb / (1024*1024) < 0)
                     THEN ((t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0)))/(t.mb / (1024*1024))*100
                     ELSE  nvl(u.s / (1024*1024),0)/(t.t / (1024*1024))*100
                   END pct
              FROM dba_tablespaces dt,
                   (SELECT tablespace_name,
                           SUM(bytes) s,
                           MAX(bytes) m,
                           COUNT(*) c
                    FROM   dba_free_space
                    GROUP BY tablespace_name
                   ) u,
                   (SELECT tablespace_name,
                           SUM(bytes) t,
                           SUM(maxbytes) mb
                    FROM   dba_data_files
                    GROUP BY tablespace_name
                   ) t
            WHERE dt.tablespace_name = u.tablespace_name(+)
            AND   dt.tablespace_name = t.tablespace_name)
      where pct <=20
    union all
-- Search Tables in Wrong Tablespaces
    select  'DBA_TABLES',owner,table_name,'TABLE','Table is in wrong Tablespace('||tablespace_name||')',null
      from  dba_tables
      where(    tablespace_name != owner||'_TABLES'
            and tablespace_name != owner)
      and   owner not in (select username from dbw.dbcheck_users where user_typ in (9,1) )
    union all
-- Search Indexes in Wrong Tablespaces
    select  'DBA_INDEXES',owner,index_name,index_type||' (INDEX)','Index is in wrong Tablespace('||tablespace_name||')' ,5
      from  dba_indexes
      where(    tablespace_name !=owner||'_INDEXES'
            and tablespace_name !=owner)
      and   owner not in (select username from dbw.dbcheck_users where user_typ in (9,1) )
      and   index_type !='LOB'
    union all
-- Search LOBS in Wrong Tablespaces
    select  column_name,owner,index_name,'LOB','Index is in wrong Tablespace('||tablespace_name||')' ,6
      from  dba_lobs
      where(    tablespace_name !=owner||'_INDEXES'
            and tablespace_name !=owner)
      and   owner not in (select username from dbw.dbcheck_users where user_typ in (9,1) )
    union all
-- Search new created Users
    select  'dba_users',username,username,'USER','New user, check this user',null
      From  dba_users
      where created >= trunc(sysdate-7)
    union all
-- Search new Ojects in RO / Mgmt Users
    select  'dba_objects',owner,object_name,object_type,'User '||owner||' is not allowed to created objects',null
      from  dba_objects
      where owner in (  select  username
                          from  dbw.dbcheck_users
                          where user_typ in (5,3,2))
      and   object_type not in ('SYNONYM');

View TryThis
Zum einfachen Abfragen der Lösungen

select  a.dbatbl,
        a.USERNAME,
        a.obj,
        a.typ,
        a.msg,
       (select  '--'||s.beschreibung||chr(10)||
                replace(replace(replace(replace(
                    s.skript,
                        '%TYP',     a.typ),
                        '%OBJ',     a.obj),
                        '%DBATBL',  a.dbatbl),
                        '%USERNAME',a.username)||chr(10)
                        script
        from dbw.dbcheck_skripts s
        where s.MSG_ID = a.msg_id) trythis
  from  dbw.dbcheck a;