What Oracle DBA should do on a daily, weekly, and monthly basis (I) Check alert on a daily basis. log ② check the environment ● check whether the database instance is working properly ▼ ps-ef | grep ora _ explain select status from v $ instance ● whether the listener is working properly ▼ lsnrctl status ● whether the tablespace is faulty ▼ select tablespace_name, status from dba_tablespace; ● control file, log file is normal running v $ controlfile running v $ log, v $ logfile ③ performance monitoring ● by business peak, perform regular collection of database performance data ● check the main performance indicators of the database ● check the changes in SQL statements that consume the most resources ● check whether there are sufficient resources and pay attention to the following: when the remaining space of all tablespaces exist, identify abnormal growth; check CPU, memory, network, storage, and so on; ④ check scheduled tasks; execute scripts on a daily basis in the evening; ● backup scripts log ● statistical information collection script log ● regular data extraction/conversion/loading script ● Log File Sorting script ⑤ keep reading the Oracle official documentation every day for an hour-_-# (ii) work space per week use Analysis ● monitor the overall growth of data volume ● analyze the growth of usage by tablespace ● tablespace fragmentation => alter tablespace <tablespace_name> coalesce; ● data fragmentation, chained row/migrate row, and other issues of each segment should be formulated based on specific situations, and potential problems should also be discovered in a timely manner ② index fragmentation analysis ● index fragmentation monitoring, set the index reconstruction frequency as needed 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 and delete unused indexes based on usage, 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 up and back up Alert logs, trace 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 ● Primary database performance indicators ● Primary database wait events ● SQL statements that consume the most memory resources ● SQL statement that consumes the most I/O resources ● recovery drill to ensure the validity of backup