(I) daily work
① Check alert. Log
② Confirm the environment
● Whether the database instance works properly
▼ PS-Ef | grep ora _
Explain select status from V $ instance
● Whether the listener works properly
▼ LSNRCTL status
● Whether the tablespace is faulty
Explain select tablespace_name, status from dba_tablespace;
● Control whether files and log files are normal
▼ V $ controlfile
Invalid v $ log, V $ logfile
③ Performance monitoring
● Periodically collects database performance data based on business peaks
● Check the main performance indicators of the database
● Check the changes in the SQL statements that consume the most resources
● Check whether there are sufficient resources and pay attention to the following:
Explain the remaining space of all tablespaces
Growth identifies abnormal growth
Supervisor checks whether the CPU, memory, network, and storage are abnormal.
④ Check scheduled tasks
Run the script regularly every night.
● Backup script logs
● Collect script logs based on statistical information
● Timed data extraction/conversion/loading script
● Log File Sorting script
⑤ Read the official Oracle documents every day for one hour -_-#
(Ii) Weekly Work
① Spatial Usage Analysis
● Monitor the overall growth of data volume
● Analyze the growth of usage by tablespace
● Tablespace fragmentation
=> Alter tablespace <tablespace_name> coalesce;
● Data fragmentation and chained row/migrate row of each segment
Develop appropriate strategies based on specific situations and promptly discover potential problems
② Index fragmentation analysis
● Monitor index fragmentation and SET index reconstruction frequency based on actual conditions to improve index Efficiency
=> Analyze index <index_name> invalidate Structure
=> Select name, del_lf_rows_len, lf_rows_len, (del_lf_rows_len/lf_rows_len) * 100 from index_stats
Index fragmentation Rate (%) = (Deleted index length/total index length) * 100
=> Alter index <index Name> rebuild
=> Alter index <index Name> coalesce
● Monitor index usage, delete unused indexes based on usage, and add indexes that can improve query and processing performance
=> Alter index <index Name> monitoring usage
=> Alter index <index Name> nomonitoring usage
=> Select index_name, used from V $ object_usage
③ Data sorting
● Clean and back up the alert logs, tracking files, and dump files generated in one week
● Clean up and back up expired database performance data to ensure the continuity and effectiveness of database performance monitoring
(Iii) monthly work
● Comprehensive analysis of a statspack report/AWR
● Main database performance indicators
● Main database wait events
● SQL statements that consume the most memory resources
● SQL statements that consume the most I/O resources
● Recovery drill to ensure the backup is effective
..........
..........
..........
By David Lin
2013-06-14
Knowing youself is the height of wisdom
