FORCE LOGGING

Oracle provides the ability to enable or disable logging of certain types of dml/ddl statements. The reasons one might consider nologging with some commands or objects is driven by a desire for improved performance. However its important that applications understand the repercussions in recovery. We have seen at Bell on numerous occasions where an application has used nologging commands and then later when they needed a subsequent Oracle restore and rollforward they did not realize that some data was not useable. When they attempted to select from or refer to an object that had been loaded with nologging they saw an error like this.[@more@]Normal0falsefalsefalseMicrosoftInternetExplorer4

ORA-01578: ORACLE data block corrupted (file # 801, block # 441422)

ORA-01110: data file 114: '/ay07/oradata/PREIADB/TS_D_SBMMLIST_01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Data was lost due to NOLOGGING parameter NOT being set. As per the above explanation when NOLOGGING is NOT set the database is vulnerable to data loss in the event of a database crash. This is exactly what happened in this instance.

The default Oracle database setting for force logging is NO. You can see the setting in v$database, by issuing the following select:

select force_logging from v$database;

When force logging = NO, Application developers and Application DBA's are allowed the ability to use NOLOGGING command to avoid the generation of redo information and potentially help performance. Some but not all commands have the ability to be run with NOLOGGING mode. For example, some of the ways an application could use nologging are

insert /*+ APPEND */ into tablea select ... from tableb;

create table tablea nologging as select ... from tableb;

As well there are several other commands which can use nologging:

alter table...move partition
alter table...split partition
alter index...split partition
alter index...rebuild
alter index...rebuild partition
create table...as select
create index
direct load with SQL*Loader
direct load INSERT (using APPEND)

At Bell we have decided that recoverability takes precedence over the speed of a select number of operations. We have learned this lesson 'the hard way'. Over a period of several years there have been several restores where after we restored, users would discover that some tables were not readable because unknown to them some nologging operations had been run, Yet the data was not easily re-creatable. This is a case where an application has specifically requested to not log an operation but did not fully understand the recovery ramifications of doing so.

The NOLOGGING parameter was set to NO.

Speed was chosen over recoverability.

For this particular account the solution was to make it a standard on all new and existing databases to FORCE LOGGING. Force Logging is enabled via: alter database force logging;

As well the toolkit script, OracleAvailStdsChecker.ksh, has been modified by Lise Lavoie, 2 years ago, as per code comments

2010/02/17 L.Lavoie 1.12 add check for force_logging.

When the .param contains: CHKFORCEIND=Y

Then OracleAvailStdsChecker.ksh will check to make force logging is set.

At Bell we run this script once per week and email on any errors.

We have made it a standard on all new and existing databases to FORCE LOGGING. Force Logging is enabled via:

alter database force logging;

If force logging is enabled, you will see:

select force_logging from V$database;

FOR

---

YES

This is a persistent parameter within Oracle meaning its not in the init.ora but it does stay set and survive shutdown and startups. The setting is saved in the controlfile.

When force logging is set to YES, Oracle will ignore when an attempt is made to use NOLOGGING operations. All operations are logged regardless, hence the name Force logging.

One last query may be helpful, if you want to know if your particular databases have had or when they last had nologging operations run, use the following query:

select file#, name, TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS') from v$datafile;

The unrecoverable time will be the most recent time a nologging operation has been performed. If you have force logging =yes, this column will never again be updated. If you have many files with recent unrecoverable times you know that nologging is used regularly within your database and in a restore and rollforward you would be vulnerable to some loss of data and some objects not being useable.

We were able to present partial contents to the applications after our restore of a nologged object by using dbms_repair.skip_corrupt_blocks package. However since the real goal is complete recoverability we feel strongly still that the safest thing to do is disable the ability for an application to use nologging by enabling force logging at a database wide level.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-1059829/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-1059829/