There are several best practices that you could observe when configuring your database that would affect the availability, mean time to repair and performance of your Oracle systems. Four Cornerstone recommends the following:
- Apply two control files. This way, you could have a backup when one of your control files is damaged. You can just overwrite the damaged control file with the good one, avoiding the need to recover your databases.
- Set a large CONTROL_FILE_RECORD_KEEP_TIME. You should allow for around 200 megabytes per control file that you have. CONTROL_FILE_RECORD_KEEP_TIME allows you to set the amount of days for your records to be saved before being overwritten and, as a rule, you should set a value that is older – when compared to the oldest backup that you have on your disk, as seen by the volume of your flash recovery area. For instance, if your flash recovery area has the capacity to maintain three full backups every 14 days, then your value should be longer than 14 days, something like 42. All records that are older than 42 days will be reduced.
- Set the group and redo log files’ size properly. Your online redo log files must all have the same sizes and you should set it to switch at least once per hour. During peak activity, your redo log files should not switch more than thrice in an hour.
- Multiplex your online redo log files. You should make multiple redo log files in each group using Oracle log multiplexing. This way, you will be protected from failures involving your redo logs, such as user errors that inadvertently remove members. If there are more than one redo log files available, your database instances will continue to work.
- Enable your ARCHIVELOG. ARCHIVELOG mode allows for hot backups of your database, that is you can back up your database even when you are online. And if you need to restore your DB to a state later than your current recovery point, then you would need to enable this as well.
Other best practices you should note:
- Allow block checksums and database block checking.
- Set your LOG_CHECKPOINT_TO_ALERT to TRUE.
- Use Fast-Start Checkpointing so that your recovery time from node or instance failure is predictable.
- Gather performance statistics about timing by setting TIMED_STATISTICS to TRUE.
- Use automatic undo management to help your database efficiently and effectively manage your undo space.
- Use locally managed tablespaces and automatic segment space administration.
- Use temporary tablespaces that improve the concurrency of a variety of sort operations.
- Make use of resumable space allocation so that you could suspend and then resume database operations should there be space problems.
- Utilize a flash recovery area, and turn on flashback database.
- Make use of the Database Resource Manager to help you allocate resources for your business objectives.
You should also utilize a server parameter file to enable a centralized file to hold your initialization parameters within a database so that you could easily manage it.
Lastly, you should make sure that your database is secure with well thought-out security policies. This way, you can safeguard your data.
Let Four Cornerstone help you run your database using these best practices. If you need help, call us at 817-377-1144 and get started today!
Screenshot courtesy of Oracle.
