Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
Optional implementation: use Oracle Enterprise Manager's 'probe' event.
· Connect to each managed system.
· Use 'telnet' or comparable program.
· For each managed instance, go to the background dump destination, usually $ORACLE_BASE/
· At the prompt, use the Unix ‘tail’ command to see the alert_
· If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in
For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of
Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.
Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.
Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.
Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE
If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.
The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.
Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.
Most production databases (and many development and test databases) will benefit from having certain nightly batch processes run.
This example collects table row counts. This can easily be extended to other objects such as indexes, and other data such as average row sizes.
The idea here is to use the more time consuming and more accurate ANALYZE COMPUTE command and save the results, which show up in the data dictionary, to a more permanent store.
I use MS Excel and an ODBC connection to examine and graph data growth.
For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed.
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m
, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
GROUP BY tablespace_name
-- To check free, pct_free, and allocated space within a tablespace
SELECT tablespace_name, largest_free_chunk
, nr_free_chunks, sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
GROUP BY tablespace_name
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
-- To analyze tables and indexes quickly, using a 5% sample size
-- (do not use this script if you are performing the overnight
-- collection of volumetric data)
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
-- To find out any object reaching
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace
-- Lastly, add another datafile to the tablespace if needed.
SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk
-- mk_volfact.sql (only run this once to set it up; do not run it nightly!)
-- -- Table UTL_VOL_FACTS
CREATE TABLE utl_vol_facts
-- Public Synonym
CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts
-- Grants for UTL_VOL_FACTS
GRANT SELECT ON utl_vol_facts TO public
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
insert into utl_vol_facts
, NVL ( num_rows, 0) as num_rows
, trunc ( last_analyzed ) as meas_dt
from all_tables -- or just user_tables
where owner in ('&OWNER') -- or a comma-separated list of owners
-- 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
-- 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
-- To find tables without PK constraint
WHERE owner = '&OWNER'
WHERE owner = '&&OWNER'
AND constraint_type = 'P'
-- To find out which primary keys are disabled
SELECT owner, constraint_name, table_name, status
WHERE owner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P'
-- 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
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
-- Rebuild indexes to have correct storage parameters
SELECT 'alter index ' || index_name || ' rebuild '
, 'tablespace INDEXES storage '
|| ' ( initial 256 K next 256 K pctincrease 0 ) ; '
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
AND owner = '&OWNER'
-- To check datatype consistency between two environments
FROM all_tab_columns -- first environment
WHERE owner = '&OWNER'
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26706/viewspace-64593/，如需转载，请注明出处，否则将追究法律责任。