Oracle DBA Weekly Procedures Checklist:-

A.  Look for objects that break rules

For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed.

1.   Every object in a given tablespace should have the exact same size for NEXT_EXTENT, which should match the tablespace default for NEXT_EXTENT.  As of 12/14/98, default NEXT_EXTENT for DATAHI is 1 gig (1048576 bytes), DATALO is 500 mb (524288 bytes), and INDEXES is 256 mb (262144 bytes).

a)  To check settings for NEXT_EXTENT, run nextext.sql.

b)  To check existing extents, run existext.sql

2.   All tables should have unique primary keys. 

a)  To check missing PK, run no_pk.sql.

b)  To check disabled PK, run disPK.sql.

c)   All primary key indexes should be unique.  Run nonuPK.sql to check.

3.   All indexes should use INDEXES tablespace.  Run mkrebuild_idx.sql. 

4.   Schemas should look identical between environments, especially test and production.

a)  To check data type consistency, run datatype.sql.

b)  To check other object consistency, run obj_coord.sql.

c)   Better yet, use a tool like Quest Software's Schema Manager.

B.  Look for security policy violations

C.  Look in SQL*Net logs for errors, issues

1.   Client side logs

2.   Server side logs

D.  Archive all Alert Logs to history

E.  Visit home pages of key vendors

1.   Oracle Corporation

http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.oramag.com

2.   Quest Software

http://www.quests.com

3.   Sun Microsystems

http://www.sun.com

Make Scripts Weekly Procedures:-

 

1.   nextext.sql

 nextext.sql

 To find tables that don't match the tablespace default for NEXT extent.

 The implicit rule here is that every table in a given tablespace should

 use the exact same value for NEXT, which should also be the tablespace's

 default value for NEXT.

 This tells us what the setting for NEXT is for these objects today.

SELECT segment_name, segment_type, ds.next_extent as Actual_Next

, dt.tablespace_name,  dt.next_extent as Default_Next

FROM dba_tablespaces dt, dba_segments ds

WHERE dt.tablespace_name = ds.tablespace_name

  AND dt.next_extent !=ds.next_extent

AND ds.owner = UPPER ( '&OWNER' )

ORDER BY tablespace_name, segment_type, segment_name

2.   existext.sql

 existext.sql

 To check existing extents

 This tells us how many of each object's extents differ in size from

 the tablespace's default size. If this report shows a lot of different

sized extents, your free space is likely to become fragmented.  If so,

 this tablespace is a candidate for reorganizing.

SELECT segment_name, segment_type

, count(*) as nr_exts

, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts

, dt.tablespace_name, dt.next_extent as dflt_ext_size

FROM dba_tablespaces dt, dba_extents dx

WHERE dt.tablespace_name = dx.tablespace_name

  AND dx.owner = '&OWNER'

GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent

3.   No_pk.sql

 no_pk.sql

 To find tables without PK constraint

SELECT table_name

FROM all_tables

WHERE owner = '&OWNER'

MINUS

SELECT table_name

FROM all_constraints

WHERE owner = '&&OWNER'

AND constraint_type = 'P'

4.   disPK.sql

 disPK.sql

 To find out which primary keys are disabled

SELECT owner, constraint_name, table_name, status

FROM all_constraints

WHERE owner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P'

5.   nonuPK.sql

  nonuPK.sql

To find tables with nonunique PK indexes.  Requires that PK names

follow a naming convention.  An alternative query follows that

does not have this requirement, but runs more slowly.

SELECT index_name, table_name, uniqueness

FROM all_indexes

WHERE index_name like '&PKNAME%'

  AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE'

SELECT c.constraint_name, i.tablespace_name, i.uniqueness

FROM all_constraints c , all_indexes i

WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE'

AND c.constraint_type = 'P' AND i.index_name = c.constraint_name

6.   mkrebuild_idx.sql

 mkrebuild_idx.sql

Rebuild indexes to have correct storage parameters

SELECT 'alter index ' || index_name || ' rebuild '

     , 'tablespace INDEXES storage '

     || ' ( initial 256 K next 256 K pctincrease 0 ) ; '

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES'

        OR next_extent != ( 256 * 1024 )

      )

  AND owner = '&OWNER'

/

7.   datatype.sql

datatype.sql

 To check datatype consistency between two environments

SELECT

   table_name,

   column_name,

   data_type,

   data_length,

   data_precision,

   data_scale,

   nullable

FROM all_tab_columns -- first environment

WHERE owner = '&OWNER'

MINUS

SELECT

   table_name,

   column_name,

   data_type,

   data_length,

   data_precision,

   data_scale,

   nullable

FROM all_tab_columns@&my_db_link  -- second environment

WHERE owner = '&OWNER2'

order by table_name, column_name

8.   obj_coord.sql

obj_coord.sql

To find out any difference in objects between two instances

SELECT object_name, object_type

FROM user_objects

MINUS

SELECT object_name, object_type

FROM user_objects@&my_db_link

References:-

1.      Loney, Kevin  Oracle8 DBA Handbook 

2.      Cook, David  Database Management from Crisis to Confidence  
[http://www.orapub.com/]

3.      Cox, Thomas B.  The Database Administration Maturity Model