Oracle Database Administrators should perform regular monitoring of Oracle database systems as follows: (1). Run status, log files, backup, data for Oracle databases every day
The space usage of the library, the use of system resources is checked, discovered and resolved
problem.
(2). Monitor the spatial extension of database objects, data growth, and perform health checks for databases, check the status of database objects.
(3). Monthly table and index, analyzes, check the table space fragment, find the database
The opportunity for performance adjustment, performing database performance adjustment, and presents the next step spatial management
plan. A comprehensive examination is performed in the Oracle database status.
Everyday work
(1). Confirm that all instance status is normal
Log in to all databases or routines, detect Oracle Background Process:
$ PS-EF | GREP ORA
(2). Check the use of the file system (remaining space). If the remaining space of the file system is less than 20%, you need to delete the unused file to release the space.
$ DF -K
(3). Check the log file and the trace file to record errors in the Alert and Trace files.
Connect to each need to manage
? Use 'telnet'
? For each database, CD to bdump directory, usually $ Oracle_Base /
• Use the UNIX 'tail' command to view the Alert_
? If you have discovered any new ORA-error, record and resolve
(4) Check the validity of the database on the day of date.
Method for RMAN backup:
Check the backup log of a third-party backup tool to determine if the backup is successful.
For Export backup:
Check the EXP log file to determine if the backup is successful
For other backup methods:
Check the corresponding log file
(5). Check the status recording status of the data file is not "Online" data file, and make recovery.
SELECT FILE_NAME from DBA_DATA_FILES where status = 'offline'
(6). Check the use of the table space
SELECT TABLESPACE_NAME, MAX_M, Count_BLOCKS Free_BLK_CNT, SUM_FREE_M, TO_CHAR (100 * SUM_FREE_M / SUM_M, '99990 ') ||'% 'AS PCT_FREE
From (SELECT TABLESPACE_NAME, SUM (BYTES) / 1024/1024 AS SUM_M from DBA_DATA_FILES GROUP BY TABLESPACE_NAME,
(SELECT TABLESPACE_NAME AS FS_TS_NAME, MAX (BYTES) / 1024/1024 AS MAX_M, Count (Blocks) AS Count_Blocks, Sum (Bytes / 1024/1024) AS SUM_FREE_M from DBA_Free_Space Group by TableSpace_name)
Where tablespace_name = fs_ts_name
(7). Check the remaining tablespace
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
From DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
(8). Monitor database performance
Run BSTAT / ESTAT Generating System Report
Or use Statspack to collect statistics
(9). Check the database performance, record the CPU usage of the database, IO, Buffer hits, etc.
Use VMSTAT, IOSTAT, GLANCE, TOP, etc.
(10). Processing in daily problems.
Weekly work
(1). Space extension of the control database object
According to the daily check in this week, find a fast-expanding database object, and take corresponding measures
- Delete historical data
--- Expansion space
Alter TableSpace
--- Adjust the storage parameters of the data object
Next extent
PCT_INCREASE
(2). Growth of monitoring data volume
According to the daily inspection of this week, find a database object that has grown quickly, and
Take a corresponding measure
- Delete historical data
--- Expansion space
Alter TableSpace
(3). System health check
Check the following:
INIT
Controlfile
Redo log file
Archiving
Sort Area Size
TableSpace (System, Temporary, TableSpace Fragment)
DataFiles (AutoExtend, Location)
Object (Number of Extent, Next Extent, INDEX)
Rollback segment
Logging & Tracing (alert.log, max_dump_file_size, sqlnet)
(4). Check if the invalid database object
SELECT OWNER, Object_name, Object_type from DBA_OBJECTS
Where status = 'invalid'.
(5). Check the constraint that does not work
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME,
Constraint_type, status
From DBA_CONSTRAINTS
Where status = 'disabled' and constraint_type = 'p'
(6). Check if Trigger
Select Owner, Trigger_name, Table_Name, Status
From DBA_TRIGGERS
Where status = 'disabled'
Monthly work
(1). Analyze Tables / INDEXES / Cluster
Analyze Table
(2) Check the watch space fragmentation
According to the weekly check analysis of the data library fragmentation this month, find the corresponding solution
(3) Find opportunities for database performance adjustments
Compare the monitoring report for database performance daily, determine if there is necessary to adjust the database performance
(4). Database performance adjustment
Performance adjustment if necessary
(5). Propose Next Space Management Plan
Based on weekly monitoring, a method of improvement of spatial management is proposed.
Oracle DBA daily management
OBJECTIVE: This document has a very detailed information to record a daily, monthly,
The results of the annual running state and the results of the inspection, you will see all checks, modified SQL in the appendix of the document
And PL / SQL code.
table of Contents
Daily maintenance procedure
A. Check all instances
B. Find some new warning log
C. Check if dbsnmp is running
D. Check if the database backup is correct
E. Check if the file backup to the tape is correct
F. Check if the performance of the database is normal, do you have enough space and resources?
G. Copy the document log into the backup database
H. To see the DBA user manual
2. Night maintenance procedure
A. Collect data from Volumetric
3. Weekly maintenance work
A. Find the Objectb that destroyed rules. Find if there is a problem with violations of security strategy
C. View Error Local SQL * NET Log
D. Archive all warning logs
E. Always visited the supplier's homepage
4. Month maintenance procedure
A. View growth rates for databases
B. Review the adjustment of previous database optimization performance
C. View I / O screen neck problem
D. Review Fragmentation
E. In the future implementation plan
F. View adjustment points and maintenance
5. Appendix
A. Month maintenance process
B. Night maintenance process
C. Week maintenance process
6. References
-------------------------------------------------- ----------------
One. Day maintenance process
A. See if all instances have been
Determine the database is available, write each instance to the log and run the day report or run the test
file. Of course, some operations We hope that it can run automatically.
Optional Execution: View using 'probe' in Oracle Manager
B. Find a new warning log file
Connect each operating management system
2. Using 'Telnet' or compareable program
3. For each management instance, frequent execution $ ORACLE_BASE /
Do it back to control the SID of the database.
4. Under the prompt, use the 'tail' command in UNIX to view Alert_
Use other ways to check the recent warning log in the file
5. If some Ora_ERRORS that appeared before, record it to the database
Recover the log and study them carefully, this database recovery log in
C. View DBSNMP operation
Check each managed 'dbsnmp' process and record them into the log.
In UNIX, in the command line, type PS-EF | GREP DBSNMP, will see 2
DBSNMP processes are running. If not, restart dbsnmp.
D. Check if the database backup is successful
E. Check if the backup tape document is successful
F. Check if there is enough resources for reasonable performance
1. Check if there is any remaining space in the table space.
For each instance, check if there is a remaining space in the table space to meet the day
The expected need. When the data existing in the database is stable, the daily growth has an average
The number is also possible to calculate, and the smallest remaining space must be at least to meet the growth of daily data.
A) Run 'Free.SQL' to check the remaining space of the table space.
B) Run 'Space.SQL' to check the percentage of remaining space in the table space
2. Check the rollback segment
The status of the rollback segment is generally online, except for some special segments prepared for complex work, it is generally offline.
a) Each database has a list of rollback segments.
b) You can use V $ ROLLSTAT to query the current state of the online or offline retraction.
c) The storage parameters and names for all returns are available.
DBA_ROLLBACK_SEGS to query. But it is not as accurate as V $ ROLLSTAT.
3. Identify some excessive growth
The storage parameters of these segments need to be adjusted in the database.
a) Collect information on day data size, you can use
'Analyze5pct.sql'. If you collect every night, you can skip this step.
b) Check the current range and use 'nr.extents.sql'.
c) Query the size information of the current table.
d) Query information about the current index size.
e) query growth trend.
4. Determine the range of space.
If the NEXT_EXTENT of the range spatial object is the maximum pattern that can be provided by the table space
There is still big, then this will affect the run of the database. If we find this goal,
To investigate its location with 'ALTER TABLESPACE COALESCE', or add additional data files.
A) Run 'SpaceBound.sql'. If it is normal, you will not return any rows.
5. Review the process of CPU, memory, network, and hardware resource arguments
A) Check the use of CPUs, go to x: .htm => system
Metrics => CPU Utilization page, the maximum number of CPUs is 400, when the occupation of the CPU
If you have more than a period of 350, we need to view and study problems.
G. Copy the archive log into the standby database
If there is an alternate database, copy the appropriate archive log to the expectation of the alternate database
The most recent data is saved in the location and spare database.
H. Review DBA User Manual
If possible, to read extensive reading, including DBA manuals, industry magazines, newsgroups or mailing lists.
-------------------------------------------------- -----------
two. Night maintenance process
Most database products will benefit from the operation of the inspection process determined every night.
A. Collect Volumetric Data
1. Analyze plan and collect data
More accurate analysis of calculations and save results.
a) If you don't do this now, use 'mk volFact.sql' to create a meter of the measurement volume.
b) Collect information on the size of the evening, use 'analyze comp.sql'.
c) Collect statistics and use 'pop vol.sql'.
d) Check data, possible words, per week, or every month when free.
I use MS Excel and ODBC to check the growth of data and charts.
-------------------------------------------------- -----------
three. Weekly maintenance process
A. Find destroyed goals
1. For the object of each given space, the size of Next_EXTENT is the same, such as
12/14/98, the default Next_EXTENT DataHi is 1G, Datalo is 500MB,
INDEXES is 256MB.
A) Check the setting of Next_extent, available 'NEXTEXT. SQL '.
B) Check existing extents, available 'Existext. SQL '.
2. All tables should have a unique primary key
a) See those tables There are no primary keys, available with 'NO_PK.SQL'.
b) Find that the primary key is not played, you can use 'DIS_PK.SQL'.
c) All primary keys for indexes are unique, available in 'Nonupk. SQL 'to check.
3. All indexes are placed in the index table space. Run 'mkrebuild_idx. SQL '
4. The plan between different environments should be the same, especially between test environment and finished environment, should be the same.
a) Check if the data type in different 2 operational environments is consistent, available
'DataType.sql'.
b) Looking for different points of objects in 2 different instances, available
'Obj_coord.sql'.
c) Better approach is to use a tool that is like a tool for seeking software. B. Check if there is a problem that harms the security policy.
C. View the error SQL * NET log.
1. Client log.
2. Server-end logs.
D. Archive all warning logs
E. Supplier's homepage
Oracle supplier
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
-------------------------------------------------- ----------------
four. Month maintenance process
A. View growth rates for databases
1. From the previous record or change in the report, the growth of the segment growth is to determine the growth of the segment.
B. Review the adjustment of previous database optimization performance
1. Review the adjustment point of the general Oracle database, compare the previous report to determine harmful development trends.
C. View I / O screen neck problem
1. View the activity of the previous database file, compare the previous output to determine the trend that it is possible to lead to the problem.
D. Review Fragmentation
E. Planning database future performance
1. Compare the CPU, memory, network, and hard disk utilization of Oracle and operating systems
To determine the trend of some resources that will be in recent times
2. When the system will take advantage of the performance trend as the protocol of the service level
F. Complete adjustment and maintenance work
1. Make changes to the need to avoid the competition for system resources, including adding new resources or dispelled.
-------------------------------------------------- ----------------
Fives. appendix
A. Daily program
- free.sql
--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
From DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
Space.sql
Space.sql
- To Check Free, PCT_FREE, And Allocated Space within a TableSpace
- 11/24/98
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 ') ||'% '
AS PCT_FREE
From (SELECT TABLESPACE_NAME, SUM (Blocks) As SUM_ALLOC_BLOCKS
From DBA_DATA_FILES 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 from DBA_FREE_SPACE
Group by TableSpace_name) Where TableSpace_name = fs_ts_name
2. Analyze5pct.sql
- Analyze5pct.sql
- 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)
- 11/30/98
Begin
DBMS_UTILITY.Alyze_schema ('& Owner', 'Estimate', NULL, 5);
END;
/
3. NR_EXTENTS.SQL
- NR_EXTENTS.SQL
- 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)
- 11/30/98
Select E.WNER, 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.WNER, E.SEGMENT_TYPE, E.SEGMENT_NAME, S.MAX_EXTENTS
Having count (*)> & threshold
OR ((s.max_extents - count (*)) <&& threshold)
Order by count (*) DESC
4. SpaceBound.sql
- SpaceBound.sql
- 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.
- the use coalesce (Alter TableSpace
- Lastly, Add Another DataFile To The TableSpace If Needed.
- 11/30/98
SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
From all_tables a,
(Select TableSpace_name, max (bytes) AS BIG_CHUNKFROM DBA_FREE_SPACE
Group by tablespace_name) f
Where f.tablespace_name = a.tablespace_name
And a.next_extent> F.BIG_CHUNK
B. Processing per night
1. mk_volfact.sql
- mk_volfact.sql (ONLY Run this overce to set it; do not run it verytem ")
- - TABLE UTL_VOL_FACTS
CREATE TABLE UTL_VOL_FACTS
Table_name varchar2 (30),
Num_Rows Number,
MEAS_DT DATE)
TableSpace Platab
STORAGE
Initial 128k
Next 128k
Pctincrease 0
Minextents 1
MaxExtents unlimited
)
/
- 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
/
2. Analyze_comp.sql
-
- analyze_comp.sql
-
Begin
Sys.dbms_utility.Analyze_schema ('& Owner', 'Compute');
END;
/
3. Pop_vol.sql
-
Pop_vol.sql
-
INSERT INTO UTL_VOL_FACTS
SELECT TABLE_NAME
, 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
/
Commit
/
C. Weekly handler
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 shouth Also Be the tablespace's
- Default value for next.
-
- this Tells US What The Setting For Next Is for these Objects Today.
-
- 11/30/98
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_EXTENTANT 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 Difer in Size from
- The TableSpace's Default Size. IF this Report Shows a Lot of Different
- Size Extens, Your Free Space Is Likey to Become Fragment. if So,
- this TableSpace is a Candidate for reorganizing.
-
- 12/15/98
Select segment_name, segment_type
COUNT (*) AS NR_EXTS
, SUM (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
-
- 11/2/98
SELECT TABLE_NAME
From all_tables
Where = '& oowner'
Minus
SELECT TABLE_NAME
From all_constraints
Where = '&& owner'
And constraint_type = 'p'
4. Dispk.sql
-
DISPK.SQL
-
- To Find Out Which Primary Keys Are Disabled
-
- 11/30/98
SELECT OWNER, Constraint_name, Table_name, Status
From all_constraints
WHERE OWNER = '& OWNER' and status = 'disk' 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.
-
- 11/2/98
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESSFROM All_indexes
WHERE INDEX_NAME LIKE '& PKNAME%'
And Owner = '& Owner' and uniqueess = 'NonUniQue'
Select c.constraint_name, i.tablespace_name, I.Unique
From all_constraints c, all_indexes i
WHERE C.OWNER = Upper ('& oowner') And i.unique = '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
-
- 11/2/98
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
-
- 11/30/98
SELECT
Table_name,
COLUMN_NAME,
DATA_TYPE,
Data_length,
Data_PRecision,
Data_scale,
Nullable
From all_tab_columns - first environment
Where = '& oowner'
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
-
- 12/08/98
Select Object_name, Object_Type
From user_Objects
Minus
Select Object_name, Object_Type
From user_Objects @ & my_db_link
VI. Reference
1. LONEY, Kevin Oracle8 DBA Handbook2. Cook, David Database Management from Crisis To Confidence
[http://www.orapub.com/]
3. COX, Thomas B. The Database Administration Maturity Model
