What is Database Maintenance
Maintenance Tasks
Consistency Checks
Consistency checks validate database blocks and look for corruption in datafiles. Consistency checks look at the physical integrity of the data blocks and rows of objects. They can also validate the structures of objects and whether the tables and indexes still have corresponding values.
Oracle checks for block corruption as database writers handle the blocks of data. The DB_BLOCK_CHECKSUM parameter determines if blocks will be checked in memory. The TYPICAL setting for this parameter (the default) verifies checksums before writing to disk. With more data movement possibly happening in memory, it is detecting the corruption here, before even writing to disk, is useful.
To have Oracle check the blocks in memory (the buffer cache), set DB_BLOCK_CHECKSUM to FULL. This setting will perform checksums on all changes before and after writing to the log. This does add overhead to the system, but FULL is the only setting that will check for block corruption in the buffer cache. This parameter is dynamic, so it can be altered to check its effects on your environment.
Enroll in this professional Oracle DBA Training to excel in your career!
Health Checks
Updating Statistics
In both SQL Server and Oracle, statistics are updated by default. In SQL Server, the AUTO_UPDATE_STATISTICS database option, when turned on, will update the statistics when they become stale. We can also run updates manually, using sp_updatestats or UPDATE STATISTICS.
In Oracle, the parameter STATISTICS_LEVEL set to TYPICAL or ALL enables automatic statistics gathering. In Oracle Database 10g, the GATHER_STATS_JOB job is scheduled to gather stale statistics and keep them updated.
Intellipaat provides SQL Certification to make you industry ready.
System Statistics
The gathered statistics information is used by the cost-based optimizer to create query plans. Capturing statistics at different times for various activities is especially useful when the workload on the database is different, such as batch processing or reporting at night and processing transactions during the day.
sqlplus> exec dbms_stats.gather_system_stats('Start');
-- gather for an hour during peak activity
sqlplus> exec dbms_stats_gather_system_stats('Stop');
We can also capture system statistics on the fixed data dictionary tables, which should be done during the regular workload and run once.
Object Statistics
If you want to get certified in Oracle DBA, learn Oracle DBA from top Oracle DBA experts with Intellipaat’s Oracle DBA Certification!
Object Maintenance
Along with gathering statistical information about objects, some maintenance and checks need to be done on them. There might be fragmentation, so the objects need to be rebuilt. Invalid objects might need to be recompiled. Even grants and permissions can be considered part of object maintenance.
Index Rebuild
While examining the database objects, some of them might appear fragmented and in need of a rebuild. Such rebuilds increase log activity, put additional resources on the system, and may put locks on the objects. Therefore, we should be selective about which indexes to include in tasks. We can generate reports to plan maintenance on indexes at another time, if necessary.
Table Reorganization
Like indexes, tables can become fragmented, due to chained rows or changes occurred by updates or deletions, which leaves space available that is not being reused. In some cases, these tables can benefit from reorganization. For example, a table might need to be reorganized after doing some data cleanup or if monitoring shows that the free space can be reclaimed.
Oracle’s ASSM feature manages the space within a segment. Allowing Oracle to manage the space in segments for tables reduces the fragmentation of the tables. Segment Advisor again comes into the play with tables, checking for chained rows and space that can be freed up.
Invalid Objects
Objects such as procedures, functions, and views can become invalid if a dependent object is altered. Normally, an object will recompile the next time the procedure is executed or the view is accessed, as long as there are no errors in the code. However, making sure that the objects are valid should be included in the maintenance plan. Alerts will pop up in OEM about the invalid objects in a schema.
We can recompile the invalid objects in a few ways:
- Recompiling all database objects that are invalid
- Recompiling objects at the schema level
- Recompiling individual objects
Grants
The dba_tab_privs and dba_col_privs views show the current grants that have been added to either a role or a user. One way to maintain grants is to have a copy of the grants that have been granted in a table and compare that information against the current dba_tab_privs view.
Synonyms
Users other than the schema owner may need access to a particular table or view, which requires them to fully qualify the object with schema_name.object_name. Alternatively, a synonym can be created for that object.
In Oracle, when tables are altered, the synonyms created on the object are not changed and they remain in place. However, if an object is dropped, the synonym will become invalid, and when the object is recreated, the synonym might need to be recompiled. The object will appear with the INVALID status in the dba_objects table.
Job Scheduling
Creating a Job in Oracle Scheduler
Oracle Scheduler is available from the Server tab in OEM (Oracle Database 11g). Selecting Jobs will show the current jobs scheduled against the database, and jobs can be viewed, edited, executed, and created from here.
For Command Type, we have the following options:
- Program name
- PL/SQL (enter the code in the text box)
- Stored procedure
- Executable
- Chain (to create steps and chain the jobs together)
Check out the Top Oracle DBA Interview Questions to learn what is expected from Oracle DBA professionals!
Using DBMS_SCHEDULER
We can also create jobs with the DBMS_SCHEDULER package. It takes parameters for job name, description, and action. We can set up a one-time job on a repeated interval, which can be by time, days, weeks, and so on. The start date could be either the current time for immediate execution or a future date.
File Maintenance
Datafiles, log files, error logs, history logs, trace files—oh my! File maintenance is very important to the health of the database and maintaining a stable environment. Developing tasks and scripts will be useful for managing different files.
Shrinking and Resizing Files
In Oracle, logs are sized and remain that same size, so shrinking a file is not an issue.
However, depending on how many times the transactions are looping through the redo logs, there might be a reason to adjust the size of the logs.
Datafiles are slightly different in this case because they are normally growing. But, we might need to clean up data or start an archive process that frees up space.
- Logs: If we resize the redo logs, we can create new groups with the bigger size. Then, as the older redo logs become inactive, they can be dropped. All of the redo log groups should have the same size set for the redo logs.
- Datafiles: To resize a datafile (to be either smaller or larger than its current size), we will use the ALTER DATABASE DATAFILE command.
Tablespace Monitoring
Oracle tablespaces are created with one or more datafiles. As the database grows, the tablespaces and datafiles need to be maintained to allow for the growth. Although, planning the size of the system tablespaces is recommended.
Oracle datafiles are set to a fixed size or to auto-extend. We can monitor space at the tablespace level in OEM. From the Server tab, under the Storage category, choose Tablespaces to see the list of tablespaces.
The free space available threshold can be a specific amount or percentage. The actual size of the free space is useful for very large tablespaces. For example, 20 percent free space of a 10 GB datafile and 20 percent free space of a 2 TB datafile may have very different levels of urgency. The percentage of allocated space does not take into account auto-extend for the datafiles.
Come to Intellipaat’s Oracle DBA Community to post your queries and get them clarified!
Error Logs, Alert Logs, and Trace Files
Oracle alert logs contain information about the status of the database and error messages. The alert log errors are on the first page of OEM. When the instance is restarted, the alert log is not cycled to the next log; writing continues to the current log.
The directories, as set by the parameters background_dump_dest, user_dump_dest, and background_core_dump, contain trace files and log files. In Oracle 11g, there is also diagnostic_dest which can be the single directory for the trace and log files. If there is enough space allocated to the server to have unlimited files, not much maintenance needs to be done. Otherwise, these directories should be purged by days.
Using operating system commands, we can find the files in the dump destinations, and mtime sets how many days the files should be retained.
find "/u01/oracle/product/11.2.0/db_1/admin/orcl/udump"
-name "*.trc" -type f -mtime +15 -exec rm -f {} ;
find "/u01/oracle/product/11.2.0/db_1/admin/orcl/bdump"
-name "*.trc" -type f -mtime +15 -exec rm -f {} ;
find "/u01/oracle/product/11.2.0/db_1/admin/orcl/cdump"
-name "*.trc" -type f -mtime +15 -exec rm -f {} ;
We should clean up the older alert logs as well. The command to do so might include the date or *.log, depending on how the alert log is named when rotating logs.