The purpose of this document is to introduce to a feature of 9.2 which will log even the unrecoverable or nologging
operations such as direct path sqlldr etc.
SCOPE & APPLICATION
When ever unrecoverable or nologging changes are performed, the changes are logged in the redo logfiles but invalidation
redo are generated against the data blocks involved in the changes.
When we need to perform. a media recovery from a sufficiently old backup and we need to apply the redo having the
invalidation vectors, the recovery goes through but the blocks of the objects having the invalidation redo vectors
are marked as logically corrupt.
- Put Table x is in nologging mode.
- Load data into table x using sql loader direct path loading.
- Perform. a complete media recovery from the old backup.
- Try to select the data from the table it gives following error:
ORA-01578: ORACLE data block corrupted (file # 7, block # 52)
ORA-01110: data file 7: 'E:\ORACLE\ORADATA\SME\USERS01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
In order to overcome from the above error, New feature called 'forced logging' is introduced in 9.2.
If this feature is enabled, Oracle does a force logging even if nologging or unrecoverable loads are done.
This feature can be enabled at TWO levels:
- Database level(CREATE DATABASE ,ALTER DATABASE ..FORCE LOGGING)
- Tablespace level(CREATE TABLESPACE,ALTER TABLESPACE ..FORCE LOGGING)
Force_logging column in V$database view shows value yes if database is in a force logging mode.
If the force logging is enabled at a database level,all the operations happening in the database are logged.
This causes performance problems in case of heavy logging. In such cases,identify the important tablespaces and
enable the force logging at the tablespace level.
force_logging column in dba_tablespace view shows value yes if tablespace is in a force logging mode.
Force logging option is by default there for undo tablespace so we need not enable the same.
SQL> alter tablespace undotbs1 force logging;
alter tablespace undotbs1 force logging
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace
Force logging is not required for temporary tablespace,if we tried it gives following error:
SQL> alter tablespace temp force logging;
alter tablespace temp force logging
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27348/viewspace-767350/，如需转载，请注明出处，否则将追究法律责任。