Since Oracle 11g a new feature was introduced to automatically recover the physical corrupted blocks online from the Standby DB immediately once the physical corruption occurs on the Primary without any intervention from the DBA. But in order for this feature to work, The DBA has to make sure that the following two prerequisites are fulfilled in the environment:
1- Active Data Guard should be enabled, where the standby will be in Open mode while the media recovery of redo logs is running. [This requires ADG license].
On the Standby:
SQL> ALTER DATABASE OPEN;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT;
2- db_block_checksum initialization parameter should be set to at least TYPICAL in both the Primary and the Standby (which is the default value).
But what about db_block_checking parameter? Should it be set as well?
Answering this question took me hours of searching, Although Oracle Maximum Availability Architecture recommends setting db_block_checking parameter to MEDIUM or FULL; it has nothing to do with the activation of Automatic Block Corruption which only involved in recovering PHYSICAL corrupted blocks not the Logical ones, db_block_checking is for enabling the checking of LOGICAL corruption.
Please note that the default value for db_block_checking is FALSE, and this is because when it get set to MEDIUM or FULL it can cause a performance impact from 5% to 20% based on the nature of the workload on the DB.
Getting to know about Logical Corruption immediately is a nice thing, but it will come at the expense of the performance!
Another question, why Oracle enables the check for physical block corruption by default but not the Logical corruption one?
This because Physical corruption are most common to happen than the Logical ones, also as mentioned above checking logical corruption will consume significant resources as Oracle will need to dig deeper inside each block to check the data consistency.
References:
No comments:
Post a Comment