Doc ID:
Note:240875.1 Type: REFERENCE
Last Revision Date: 11-JAN-2006 Status: PUBLISHED
ORACLE9I MEDIA RECOVERY BEST PRACTICES
With the proliferation of Oracle Data Guard for disaster recovery, data protection and scheduled maintenance advantages, optimizing media recovery for physical standby databases becomes more important.
Media recovery occurs when one or more datafiles or the controlfiles are restored from a previous backup. The goal of media recovery is to recover the datafiles and the rest of the database to a consistent point in time or to apply all transactions that has every occurred when a standby database is used to protect the primary site. This paper does not cover block media recovery, crash recovery, instance recovery, or SQL apply recovery in a logical standby database.
In order to leverage a physical standby database for fast switchover or failover in the event of a disaster and service the data needs of the enterprise, you need to tune the time it takes to apply redo to your physical standby database to keep it transactionally consistent with the production database with a minimal lag. Here are recovery best practices to help you implement transactionally consistent copies of a production database. These same best practices will assist in tuning the redo apply for any database requiring media recovery.
Recovery Best Practices and General Observations in Tuning Recovery
These following recommendations will provide significant recovery gains and will provide additional insight in tuning your database for fast media recovery. These best practices were derived after extensive media recovery testing on Oracle9i databases and part of the performance studies within the Maximum Availability Architecture (MAA) projects. For more information about MAA, please refer to http://otn.oracle.com/deploy/availability/htdocs/maa.htm.
1. Parallel Recovery
During media or standby recovery the redo log is read, and blocks that require redo application are parsed out. With parallel media recovery, these data blocks are subsequently distributed evenly to all recovery processes to be read into the buffer cache. The default is serial recovery or zero parallelism, which implies that the same recovery process reads the redo, reads the data blocks from disk and applies the redo changes. To implement parallel media recovery or standby recovery, you need to add an optional PARALLEL clause to the recovery command.
By utilizing parallel media or standby recovery, you may experience three times the performance gains over serial recovery for OLTP runs. In our testing, minimal gains were found using the SQL*Loader (batch) runs. The recommended degree of parallelism is 2 or 3 times the number of CPUs on the host. You should compare several serial and parallel recovery runs to determine your optimal recovery performance. Below are some examples of how to set recovery parallelism.
RECOVER MANAGED STANDBY DATABASE PARALLEL <#CPUS * 2>;
RECOVER STANDBY DATABASE PARALLEL <#CPUS * 2>;
RECOVER DATABASE PARALLEL <#CPUS * 2>;
Ensure that you have applied the fix for bug 2555509, which provides significant performance gains for parallel media recovery.
2. Tune I/O
Normally, the biggest bottlenecks in recovery are read and write I/O. Use native asynchronous I/O and set the database parameter, disk_asynch_io=TRUE (this is the default). Disk_asynch_io controls whether I/O to datafiles is asynchronous. Asynchronous I/O should significantly reduce db file parallel reads and should improve overall recovery time.
3. Monitor Statistics
Monitoring I/O, CPU, recovery process CPU utilization, and database statistics on the standby database to ensure that media recovery is running optimally and that there are no system bottlenecks.
1. Apply rate issues are easily detected by monitoring for database and system bottlenecks. Refer to Recovery Monitoring Best Practices section below for more details.
2. Reduce the degree of parallelism if CPU over-utilization on the standby database is observed.
3. If there are I/O bottlenecks or excessive wait I/Os, then stripe across more spindles/devices or leverage more controllers. A stripe size between 256KB to 1MB is optimal to leverage your I/O subsystem. Verify that this is not a bus or controller bottleneck or any other I/O bottleneck.
4. The top database wait events should be “db file parallel read” and ”checkpoint completed” during media recovery. Refer to recovery monitoring best practices section.
5. If the top database wait event is “free buffer waits”, which indicates recovery was not able to find a free buffer, use multiple DBWR (set DB_WRITER_PROCESSES > 1) during media recovery. Configuring multiple DBWR processes benefits performance when a single DBWR process is unable to keep up with the required workload. However before configuring multiple DBWR processes, you need to check whether asynchronous I/O is available and configured on the system. If the system supports asynchronous I/O but it is not currently used, then enable asynchronous I/O to see if this alleviates the problem. If the system does not support asynchronous I/O, or if asynchronous I/O is already configured and there is still a DBWR bottleneck, then configure multiple DBWR processes.
6. If the top PQ messaging waits are “PX Deq*”, verify that the fix for bug 2555509 has been installed. If the fix for bug 2555509 has been applied, reduce recovery parallelism or try serial recovery performance.
4. Serial Recovery
Testing revealed that serial (no parallelism) media recovery for SQL*Loader transactions or batch jobs that update contiguous blocks provide near optimal redo apply rates with the least amount of system overhead.
5. Redo Apply Rates
Redo apply (recovery) rates for Oracle9i Release 2 should surpass very high OLTP redo generation rates and at least match SQL*Loader or batch generation rates if there is sufficient system resources. Using 8 CPUs, our tests achieved approximately 12MB/sec apply rate for SQL*Loader runs and 4MB/sec apply rate for OLTP type runs.
6. Expect Larger CPU and Potential I/O Consumption with Higher Degrees of Recovery Parallelism.
With higher degrees of recovery parallelism, our tests showed higher CPU utilization, although different profiles showed different degrees of increase. With the SQL*Loader profile, the CPU utilization increased gradually from 20% to 30%. However, with the small OLTP profile, CPU utilization increased dramatically from 15% to 50%. Potentially more I/O bandwidth will be required for higher degrees of parallelism if the apply rate increases. Parallel recovery and the recovery slaves consumed more system resources due to the parallel recovery communication and IPC messaging overhead.
7. Standby Database System Utilization
Standby database system utilization compared to production database CPU utilization depends dramatically on the read/write ratio on the production database, the type of production transactions, the variance of unique blocks that are being updated in the redo, other additional activities on those hosts, and other factors. There is no simple formula to predict the standby database system utilization. Here are some general observations that we found during our testing.
1. The higher the read ratio on production, the greater the difference in CPU utilization between the production and standby databases. We have experienced 10 times more CPU utilization on the production system with query intensive applications.
2. Higher numbers of sorts or complex queries executed will require more CPU utilization on the production database. Queries and sorts do not create additional redo and thus does not create work on the standby database.
3. Additional standby database CPU utilization is required when unique blocks are updated to account for the application of redo to the distinct blocks. The SQL*Loader runs modified 5 times less unique blocks compared to an OLTP run leading to 30 to 40% less CPU utilization on the standby while having almost three times the apply rate of the OLTP runs.
8. Configuration Differentiators
Although it is recommended that the production and standby machines and databases are identically configured, you can reduce memory utilization on the standby by decreasing the database shared pool and the buffer caches. To conserve memory resources,
1. • check shared pool free memory and then reduce shared pool size accordingly
2. • check the buffer cache hit ratio and possibly lower the buffer cache if recovery rate and hit ratios are high.
Refer to the supplied recovery monitoring script below.
9. Real Application Clusters (RAC) and Redo Apply Rate
Recovery tests with and without the RAC installed on the standby site showed little difference in recovery rates. Minimal to no apply rate overhead was found when using Real Application Clusters on the standby database; however we recommend using one designated recovery instance.
10. Disable Data Block Checking for Faster Redo Apply Rates
In all our tests, DB_BLOCK_CHECKING was set to TRUE during redo apply. If redo apply rate is still not sufficient after tuning it with the above best practices, setting DB_BLOCK_CHECKING=FALSE during the redo apply on the standby database or for cases when media recovery is required will give exceptional performance gain. The additional block checking is sacrificed only on the media recovery or standby database. Assuming that block checking is always enabled on the production database, and DB_BLOCK_CHECKSUM is enabled on both production and standby, there is little danger to turning off block checking on the standby. In our isolated tests, the redo apply rate doubled.
11. Parallel Execution Message Size
Increasing the database parameter, parallel_execution_message_size, from 2K to 8K or 16K may improve parallel media recovery performance significantly. The tests used 16K.
12. Apply pl utilize synchronous I/O. This can be inefficient with a large number of datafiles. Applying the patch for bug 3497588 will allow the reads and updates to be done using async I/O.
For complete details on the testing environment and testing results see http://otn.oracle.com/deploy/availability/pdf/MAA_RecoveryBestPractices.pdf .
Recovery Monitoring Script and Monitoring Best Practices
To assess the standby database and host performance, monitor the following database statistics and wait events.
1. • Database wait events from v$system_events and v$session_waits
1. o Refer to the top 3 system wait events and tune the biggest waits first. You can determine the top system and session wait events by querying v$session_wait and v$system_event and taking the top waits with the largest “TIME_WAITED” value
2. o Once the I/O and CPU resources are tuned, then the biggest database wait events should be “db file parallel write” and “checkpoint completed”
3. o If “free buffer waits” is the top database wait event, then increase the number of db_writer_processes or the size of the database buffer cache.
4. o If PQ messaging (“PX Dequeue*”) waits are the top wait events, check for bug 2555509 and then attempt to reduce recovery parallelism or try serial recovery performance.
2. • Database SGA utilization from v$sgastat
1. o Check for excessive shared pool free memory over time. Reduce memory utilization by decreasing the shared pool size.
3. • Database system values from v$sysstat
1. o Keep a history of v$sysstat values for more in-depth debugging. Currently there’s no statspack utility for the standby database; so, you need to query the v$sysstat view and load it into a separate read write database.
2. o Check if buffer cache hit ratio > 95% and compare if the ratio dropped from previous results.
3. o Check if recovery read ratio >= 5 and compare if the value dropped from previous results.
4. • Database file I/O information from v$filestat
1. o For each file, ensure that read and write I/O timings are reasonable for your system. Refer to a systems tuning guide.
2. o Check if batch reads and writes are occurring. Refer to the database Performance Tuning Guide and Reference.
5. • System CPU utilization
1. o Ensure you have sufficient CPU resources or reduce the degree of recovery parallelism
6. • System Memory utilization
1. o Check for excessive paging and swapping.
2. o Reduce database parameters such as shared_pool_size, sort area size and possibly db_cache_size. Refer to the SGA utilization information.
7. • System I/O utilization
1. o Look for excessive wait I/Os or large queues of I/O. Assess if the read I/O and write I/Os times are excessive. Compare with the statistics found in v$filestat
2. o If there are I/O bottlenecks, then assess if more spindles or disks are required. Attempt to use SAME with a stripe size of 1 MB.
3. o Verify that the disk controllers or I/O buses are not saturated.
8. • Recover Slave Process CPU utilization
1. o If you notice that the CPU utilization is not evenly distributed across the recovery slaves, then recovery parallelism may not assist you in this environment or at this time. This implies that changes are probably occurring in the same set of blocks and those blocks are being passed to one particular slave or small subset of slaves. This may be a temporary issue attributed to a specific transaction but may explain a slowdown in recovery.
The database utility, Statspack, does not gather statistics from physical standby databases or from mounted instances. The following script helps extract the most important database statistics; however the statistics, timings and averages are cumulative. You need to manually get the difference between two runs by subtracting the data between two snapshots.
Sample Recovery Monitoring Script - Korn Shell
Notice that for the UNIX shell, the “$” is preceded with “\” to ensure that the shell does not attempt to translate the variable.
#!/bin/ksh
while true
do
sqlplus '/ as sysdba' << recovery_data
spool $1/dbstats.`date +%b%d_%T`
set pagesize 10000 echo off feedback off TERMOUT OFF
# status of recovery and current sequence# and thread#
select process, status , thread#, sequence#, blocks from v\$managed_standby;
select max(sequence#), thread# from v\$log_history group by thread#;
# session wait information
column event format a35
column p1text format a20
column p2text format a20
select sid, event, total_waits, time_waited from v\$session_event
where total_waits != 0 and time_waited !=0 and
event not in ('rdbms ipc message','smon timer') and time_waited > 100
order by time_waited;
# file I/O statistics within the database
select * from v\$filestat
where PHYWRTS >0 order by writetim ;
# system event information
select * from v\$system_event
where time_waited > 1000 order by time_waited;
# SGA stats
select * from v\$sgastat;
# recovery stats such as average recovery read I/O
select name, value from v\$sysstat where name like 'recovery%';
# buffer cache hit ratio
select (c.value + d.value – p.value)/( c.value + d.value)
from v\$sysstat c, v\$sysstat d, v\$sysstat p
where c.name = 'consistent gets' and
d.name = 'db block gets' and p.name ='physical reads';
spool off
exit
recovery_data
sleep
done
CONCLUSION
By following the above recipe and integrating standby monitoring best practices, you can achieve optimal recovery or redo apply performance. Faster media recovery leads to reduced Data Guard switchover, failover or database media recovery times. This equates to more uptime and higher availability in .
No comments:
Post a Comment