Oracle Database Administrators should perform regular monitoring of Oracle database systems as follows:
(1). Every day, the operational status, log file, backup, data of the Oracle database
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., using 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 the database object that is quickly extended, and takes
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 Purpose: This document has a very detailed information to record the results of one or more Oracle database daily, monthly, annual running status and inspection results, in the appendix you will You will see all checks, modified SQL and PL / SQL code. Directory 1. Daily maintenance procedure A. Check all instance b from it. Find some new warning logs c. Check if dbsnmp is running D. Check if the database backup is correct. Check if the file backup into the tape is correct. Check if the performance of the database is normal, there is enough space and resource G. Copy the document log to the backup database h. To see the DBA user manual 2. Night maintenance procedure A. Collect data of Volumetric 3. Maintenance work a weekly weekly. Find Object B that destroy rules. Find if there is a problem with a violation of the security policy. View the SQL * NET log d of the incorrect place. Archive all warning logs E. Regularly visit the supplier's home page 4. month maintenance procedure A. Viewing growth rate B that generates hazards for databases. Review the adjustment of previous database optimization performance. View I / O screen neck problem D. Review Fragmentation E. The future execution plan f. View adjustment points and maintenance 5. Appendix A. Month maintenance process B. The evening maintenance process C. Week maintenance process 6. References ----------------------------------------- --------------------- One. Day maintenance process A. See if all instances have been determined to be 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: Use the 'probe' event in the Oracle Manager to view B. Find a new warning log file 1. Connect each operational management system 2. Use the 'telnet' or compareable program 3. For each management instance, often execute $ oracle_base /
B) Run 'Space.sql' to check the remaining space percentage in the table space 2. Check the status of the rollback segment to the rollback segment is generally online, except for some dedicated segments for complex work, it is generally offline. a) Each database has a list of rollback segments. b) You can query the current state of the online or offline rollback segment. c) Use DBA_ROLLLBACK_SEGS to query the storage parameters and names of all returns. But it is not as accurate as V $ ROLLSTAT. 3. Identify some excessive growth to view the database exceeding the resource or excessive growth rate, and the storage parameters of these segments need to be adjusted. a) Collect information on day data, 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 larger than the maximum range that can be provided by the table space, then this will affect the run of the database. If we find this goal, you can 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 reviewing CPU, memory, network, and hardware resource arguments a) Check the use of CPUs, go to x: /web/phase2/default.htm => system metrics => CPU Utilization page, the maximum number of CPU is 400 When the occupation of the CPU has been more than 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 desired location of the alternate database, save the most recent data in the standby database. H. Review DBA User Manual If possible, if possible, to read, including DBA Manual, Business Magazine, News Group, or an email list. -------------------------------------------------- ------------ II. Database products in the evening maintenance process will benefit from the operation of the inspection process per day. A. Collect Volumetric Data 1. Analyze the plan and collect data more accurate analysis and save the 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 am using MS Excel and ODBC to check the growth of data and charts -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- three. Weekly maintenance process a. Find the destroyed target 1. For each given object, NEXT_EXTENT size 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, and 'NO_PK.SQL' can be used. 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 the test environment and the finished environment should be the same. a) Check if the data type in different 2 operational environments is consistent, you can use 'Datatype.sql'. b) Looking for different points of objects in 2 different instances, you can use '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. Home 1. Oracle supplier http://www.racle.com http://technet.Oracle.com http://www.racle.com/support http://www.oramag.com 2. Quest Software http://www.quests.com 3. Sun Microsystems http://www.sun.com -------------------------- -------------------------------------- IV. Month maintenance process A. Viewing growth rates for a hazard for a database 1. Changes in the growth of segments in previous records or reports are determined to determine the segment growth 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 cause the screen neck problem. D. Review Fragmentation E. Planning Databases Future Performance 1. Compare the Usage of Oracle and Operations CPUs, memory, networks, and hard drives to determine trends in some resource compensations in recent times 2. When the system will exceed the range Trend as an agreement to the service level to see f. Complete adjustment and maintenance 1. Make changes to the need to avoid the need for system resources, including adding new resources or downsworked expected.
-------------------------------------------------- -------------- Fives. Appendix A. routine procedures - free.sql --To verify free space in tablespaces --Minimum amount of free space --document your thresholds: -
- 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.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.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 What Happlened. - THEN USE COALESCE (ALTER TABLESPACE
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 Handling Program 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 the object_name, segment_type, ds.next_extent as actual_next, dt.tablespace_name, dt.next_extent as default_next from dba_tablesspace 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 EXNTENTS - - 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 -. - 12/15/98 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 - - 11/2/98 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 - - 11/30/98 Select Owner, ConsTRAINT_NAME, TABLE_NAME, STATUS from ALL_CONSTRAINTS Where Owner = '& Owner' and status = 'DISA Bled '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, 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 ('& oowner') And i.uniqueness = 'nonunique'
