What is Force LOGGING in Oracle

In FORCE LOGGING mode, Oracle Database logs all changes in the database except changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

 If you specify FORCE LOGGING, Oracle Database waits for all ongoing unlogged operations to finish.

 ALTER DATABASE FORCE LOGGING;       //enable

 ALTER DATABASE NO FORCE LOGGING;                                //disable

 SELECT FORCE_LOGGING FROM V$DATABASE;                 //check

 RESETLOGS | NORESETLOGS  This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during  recovery, ensuring that it will never be applied.

  Oracle Database uses NORESETLOGS automatically except in the following specific situations, which require a setting for this clause:

  1. You must specify RESETLOGS:

   – After performing incomplete media recovery or media recovery using a backup controlfile

   – After a previous OPEN RESETLOGS operation that did not complete

   – After a FLASHBACK DATABASE operation

  2. If a created controlfile is mounted, then you must specify RESETLOGS if the online logs are lost, or you must  specify NORESETLOGS if they are not lost.

 While there are several types of problem that can halt the normal operation of an Oracle database or affect database I/O operations, only two typically require DBA intervention and media recovery: media failure and user errors.

Other failures may require DBA intervention to restart the database (after an instance failure) or allocate more disk space (after statement failure due to, for instance, a full datafile) but these situations will not generally cause data loss or require recovery from backup.

1. User Error

   User errors occur when, either due to an error in application logic or a manual mis-step, data in your database is changed or deleted incorrectly. Data loss due to user error includes such missteps as dropping important tables or deleting or changing the contents of a table. While user training and careful management of privileges can prevent most user errors, your backup strategy determines how gracefully you recover the lost data when user error does cause data loss.

2. Media Failure

   A media failure is the failure of a read or write of a disk file required to run the database, due to a physical problem with the disk such as a head crash. Any database file can be vulnerable to a media failure. The appropriate recovery technique following a media failure depends on the files affected and the types of backup available.

Errors and Failures Without Requiring Recovery from Backup

1. Process Failure

   PMON will automatically perform the process recovery and rollback all involved transactions.

2. Program Failure

   It occurs when data violate the contrains, or data do not meet the data types, and so on, Oracle will automatically raise ORA- errors and Oracle will rollback the transactions automatically, or will wait for program's determination.

3. Instance Crash

   When an instance is shutdown abort or abnormally, SMON will perform recovery automatically

(roll forward + open the database [uses can access the instance/database at this time],

+  roll back the uncommitted transactions)         when the instance starts up.