Adopt the Oracle MAA best practices for configuring all Oracle single-instance databases to reduce or avoid outages, reduce the risk of corruption, and improve recovery performance.
Note that the following Oracle Database best practices are used to configure the Oracle MAA Bronze reference architecture, and they are also the base database base practices for the other MAA reference architectures: Silver (Oracle RAC), Gold (Oracle Data Guard), and Platinum (Oracle GoldenGate).
Use a Server Parameter File (SPFILE)
The server parameter file (SPFILE) enables a single, central parameter file to hold all database initialization parameters associated with all instances of a database. This provides a simple, persistent, and robust environment for managing database parameters. SPFILE is recommended to be placed in the DATA ASM disk group.
Enable Archive Log Mode and Forced Logging
Running the database in
ARCHIVELOG mode and using database
FORCE LOGGING mode are prerequisites for database recovery
ARCHIVELOG mode enables online database backup and is necessary to
recover the database to a point in time later than what has been restored. Features such
as Oracle Data Guard and Flashback Database require that the production database run in
If you can isolate data that never needs to be recovered within specific tablespaces,
then you can use tablespace level
FORCE LOGGING attributes instead of
FORCE LOGGING mode.
Configure an Alternate Local Archiving Destination
The local archive destination, usually
should have an alternate local destination on a different ASM disk group. This configuration
prevents database hanging due to lack of archive log space if
DB_RECOVERY_FILE_DEST fills up or is unavailable for any
Table 9-1 Alternate Local Archiving Configuration Parameters
|Database Parameter||LOG_ARCHIVE_DEST_n parameter settings for local archive destinations|
||Archive destination, for example, a RECO disk group|
Sample parameter settings:
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_FILE_RECOVERY_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=db_unique_name of the database ALTERNATE=LOG_ARCHIVE_DEST_10'
LOG_ARCHIVE_DEST_10='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=db_unique_name of the database ALTERNATE=LOG_ARCHIVE_DEST_1'
DB_RECOVERY_FILE_DEST=typically the RECO disk group
Use a Fast Recovery Area
The Fast Recovery Area is Oracle-managed disk space that provides a centralized disk location for backup and recovery files.
The Fast Recovery Area is defined by setting the following database initialization parameters:
DB_RECOVERY_FILE_DESTspecifies the default location for the fast recovery area. Set this parameter to the RECO disk group.
DB_RECOVERY_FILE_DEST_SIZEspecifies (in bytes) the hard limit on the total space to be used by database recovery files created in the recovery area location.
Set this parameter to a value large enough to store archived logs, flashback logs and any local database backup files locally. Having the files locally can reduce your recovery time after restoring a backup. RMAN will automatically manage these files according to your RMAN backup and data retention policies. Typically customers store 24 hours of data in the destination
When your system hosts many databases sharing the same
DB_RECOVERY_FILE_DEST_SIZE, space needs to manage and monitored holistically. Recommended to alert when RECO disk group for example is 90% full.
Enable Flashback Database
Flashback Database provides an efficient alternative to point-in-time recovery for reversing unwanted database changes.
Flashback Database lets you rewind an entire database backward in time, reversing the effects of database changes within a time window. The effects are similar to database point-in-time recovery. You can flash back a database by running a single RMAN command or a SQL*Plus statement instead of using a complex procedure.
To enable Flashback Database, configure a fast recovery area and set a flashback retention target using the best practices listed below. This retention target specifies how far back you can rewind a database with Flashback Database.
Know your application performance baseline before you enable flashback database to help determine the overhead and to assess the application workload implications of enabling flashback database.
Ensure that the fast recovery area space is sufficient to hold the flashback database flashback logs. A general rule of thumb is that the volume of flashback log generation is approximately the same order of magnitude as redo log generation. For example, if you intend to set
DB_FLASHBACK_RETENTION_TARGETto 24 hours, and if the database generates 20 GB of redo in a day, then allow 20 GB to 30 GB disk space for the flashback logs.
An additional method to determine fast recovery area sizing is to enable flashback database and allow the database to run for a short period of time (2-3 hours). Query
V$FLASHBACK_DATABASE_STAT.ESTIMATED_FLASHBACK_SIZEto retrieve the estimated amount of space required for the fast recovery area.
Note that the
DB_FLASHBACK_RETENTION_TARGETis a target and there is no guarantee that you can flashback the database that far. In some cases if there is space pressure in the fast recovery area where the flashback logs are stored, then the oldest flashback logs may be deleted. To guarantee a flashback point-in-time you must use guaranteed restore points.
Ensure that there is sufficient I/O bandwidth to the fast recovery area. Insufficient I/O bandwidth with flashback database on is usually indicated by a high occurrence of the
FLASHBACK BUF FREE BY RVWRwait event.
To monitor the progress of a flashback database operation you can query the
V$SESSION_LONGOPSview. An example query to monitor progress is
SELECT sofar, totalwork, units FROM v$session_longops WHERE opname = 'Flashback Database';
For repetitive tests where you must flashback to the same point, use flashback database guaranteed restore points instead of enabling flashback database. This will minimize space usage.
- Flashback PDB can rewind a pluggable database without affecting other PDBs in the CDB. You can also create PDB restore points.
Set FAST_START_MTTR_TARGET Initialization Parameter
With Fast-Start Fault Recovery, the
initialization parameter simplifies the configuration of recovery time from instance or
FAST_START_MTTR_TARGET parameter specifies a target for the expected
recovery time objective (RTO), which is the time, in seconds, that it should take to
start the instance and perform cache recovery. When you set this parameter, the database
manages incremental checkpoint writes in an attempt to meet the target. If you have
chosen a practical value for this parameter, then you can expect your database to
recover, on average, in approximately the number of seconds you have chosen.
Initially, set the
FAST_START_MTTR_TARGET initialization parameter to
300 (seconds), or to the value required for your expected recovery time objective (RTO).
As you set or lower this value, database writer (DBWR) will become more active to meet
your recovery targets.
Make sure that you have sufficient IO bandwidth to handle potential higher load. See the
Database Performance Tuning Guide for information about monitoring and tuning
Outage testing for cases such as node or instance failures during peak loads is recommended.
Protect Against Data Corruption
Oracle Database corruption prevention, detection, and repair capabilities are built on internal knowledge of the data and transactions it protects, and on the intelligent integration of its comprehensive high availability solutions.
A data block is corrupted when it is not in a recognized Oracle Database format, or its contents are not internally consistent. Data block corruption can damage internal Oracle control information or application and user data, leading to crippling loss of critical data and services.
When Oracle Database detects corruption, it offers block media recovery and data file
media recovery to recover the data. You can undo database-wide logical corruptions
caused by human or application errors with Oracle Flashback Technologies. Tools are also
available for proactive validation of logical data structures. For example, the SQL*Plus
ANALYZE TABLE statement detects inter-block corruptions.
The following are best practices for protecting your database against corruption.
Use Oracle Automatic Storage Management (Oracle ASM) to provide disk mirroring to protect against disk failures.
HIGHredundancy disk type for optimal corruption repair with Oracle ASM.
Using Oracle ASM redundancy for disk groups provides mirrored extents that can be used by the database if an I/O error or corruption is encountered. For continued protection, Oracle ASM redundancy lets you move an extent to a different area on a disk if an I/O error occurs. The Oracle ASM redundancy mechanism is useful if you have bad sectors returning media errors.
Enable Flashback technologies for fast point-in-time recovery from logical corruptions that are most often caused by human error, and for fast reinstatement of a primary database following failover.
Implement a backup and recovery strategy with Recovery Manager (RMAN) and periodically use the RMAN
BACKUP VALIDATE CHECK LOGICALscan to detect corruptions.
Use RMAN and Oracle Secure Backup for additional block checks during backup and restore operations. Use Zero Data Loss Recovery Appliance for backup and recovery validation including corruption checks and repairs, central backup validation, reduced production database impact, and Enterprise Cloud backup and recovery solutions.
- Set database initialization parameter
- Evaluate setting
FULL, but only after a full performance evaluation with the application.
Set the LOG_BUFFER Initialization Parameter to 128MB or Higher
LOG_BUFFER initialization parameter to a minimum of
128 MB for databases with flashback enabled.
Use Automatic Shared Memory Management and Avoid Memory Paging
Enable Automatic Shared Memory Management by setting the
SGA_TARGET parameter, and set the
database initialization parameter to
USE_LARGE_PAGES ASM initialization parameter to
Use the following guidelines in addition to setting
SGA_TARGET to enable
Automatic Shared Memory Management.
The sum of SGA and PGA memory allocations on the database server should always be less than your system's physical memory while still accommodating memory required for processes, PGA, and other applications running on the same database server.
To get an accurate understanding of memory use, monitor PGA memory and host-based memory use by querying
V$PGASTATfor operating systems statistics.
Avoid memory paging by adjusting the number of databases and applications, or reducing the allocated memory settings.
PGA_AGGREGATE_LIMITto specify a hard limit on PGA memory usage. If the
PGA_AGGREGATE_LIMITvalue is exceeded, Oracle Database first terminates session calls that are consuming the most untunable PGA memory. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.
Set the database initialization parameter
ONLY, and set the ASM initialization parameter
Make sure that the entire SGA of a database instance is stored in HugePages by setting the
USE_LARGE_PAGES=ONLY, or set to
AUTO_ONLYon Exadata systems.
USE_LARGE_PAGES=ONLYis recommended for database instances, because this parameter ensures that an instance will only start when it can get all of its memory for SGA from HugePages.
For ASM instances leave the parameter
USE_LARGE_PAGES=ONLY(the default value). This setting still ensures that HugePages are used when available, but also ensures that ASM as part of Grid Infrastructure starts when HugePages are not configured, or insufficiently configured.
Use Automatic Shared Memory Management, because HugePages are not compatible with Automatic Memory Management.
Use Oracle Clusterware
Oracle Clusterware lets servers communicate with each other, so that they appear to function as a collective unit. Oracle Clusterware has high availability options for all Oracle databases including for single instance Oracle databases. Oracle Clusterware is one of minimum requirements in making applications highly available.
Oracle Clusterware provides the infrastructure necessary to run Oracle Real Application Clusters (Oracle RAC), Oracle RAC One Node, and Oracle Restart. Oracle Grid Infrastructure is the software that provides the infrastructure for an enterprise grid architecture. In a cluster, this software includes Oracle Clusterware and Oracle ASM.
For a standalone server, the Grid Infrastructure includes Oracle Restart and Oracle ASM. Oracle Restart provides managed startup and restart of a single-instance (non-clustered) Oracle database, Oracle ASM instance, service, listener, and any other process running on the server. If an interruption of a service occurs after a hardware or software failure, Oracle Restart automatically restarts the component.
Oracle Clusterware manages resources and resource groups to increase their availability, based on how you configure them. You can configure your resources and resource groups so that Oracle Clusterware:
Starts resources and resource groups during cluster or server start
Restarts resources and resource groups when failures occur
Relocates resources and resource groups to other servers, if the servers are available
For more information, see Oracle Clusterware Administration and Deployment Guide topics, High Availability Options for Oracle Database and Making Applications Highly Available Using Oracle Clusterware.