FORCE_LOGGING
Hi,
One of our DBA who joined the organization has following Observation about our existing Standby databases (15 in total). FYI, we have activated all the database in the past and business users have done rigorous testing without any problem.
Quote
*"I found a mandatory parameter 'FORCE LOGGING' has not been enforced in Primary databases to ensure the Data Consistency between Primary and Standby Databases. Applicable to oracle 9i and 10g versions.*
*Failure to set this parameter will lead to Data Block corruption ("ORA-26040: Data block was loaded using the NOLOGGING option") in the Standby database*
*and will result in "Standby database Recovery", which will be another disaster. In some cases, error reported only when you read the table data.*
*If a user able to perform NOLOGGING operations on the primary database would mean that changes would not be fully logged within the redo stream (minimal redo in term) to maintain the standby database. By setting FORCE LOGGING to TRUE in database level, the NOLOGGING option is not allowed and all transactions will generate full redo, so no redo data will be missed and 100% recoverable.*
*It can be enabled by:*
*SQL> ALTER DATABASE FORCE LOGGING;*
*The question here is How far our Standby databases are in SYNC with Primary today ???. I have done few checks and at this initial stage so far NO unrecoverable transactions found. (good news)*
*select file#, name, UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME from v$datafile;*
*DBVERIFY also can be used.*
*But this should be checked and verified by Oracle support properly to ensure and validate our Standby databases are 100% sync.*
*I suggest you to create a Oracle support case to get proper verification and advice for this issue. _Otherwise we may need to Re-instantiate / recreate."*
How far is this guy correct. It is not that our other DBAs were not ware of this parameter but they construed that this parameter is not so essential.
Is this parameter so critical that we have to recreate all existing standby databases. What if we do not have standby database. Is it still so important on the primary database otherwise we compromise data recovery.
Regards
Aijaz