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
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/]
[http://www.orapub.com/]
3.
Cox,
Thomas B. The Database Administration
Maturity Model
No comments:
Post a Comment