What is force logging:

If force logging is enabled, all the database changes will be logged in redo log files, even for nologging operations.The above means that if
FORCE LOGGING is enabled at a higher level, the NOLOGGING at a lower level has no effect. That means that even for a nologging table redo information may still be logged.

This feature can be enabled at TWO levels:

– Database level
– Tablespace level

Database level:

If the force logging is enabled at a database level, all the operations happening in the database are logged.

Enable/Disable force logging:

SQL> select name,force_logging from v$database;

NAME      FORCE_LOGGING
--------- ---------------------------------------
TESTDB    NO

-- Enable 

SQL> alter database force logging;

Database altered.

SQL> select name,force_logging from v$database;

NAME      FORCE_LOGGING
--------- ---------------------------------------
TESTDB    YES


-- Disable

SQL> alter database no force logging;

Database altered.

SQL> select name,force_logging from v$database;

NAME      FORCE_LOGGING
--------- ---------------------------------------
TESTDB    NO

Tablespace Level:

Sometimes database level force_logging can cause performance problems in case of heavy logging. In such cases, identify the important tablespaces and enable the force logging at the tablespace level.

SQL> select tablespace_name,FORCE_LOGGING from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                FOR
------------------------------ ---
USERS                          NO

-- Enable 

SQL> alter tablespace users force logging;

Tablespace altered.

SQL> select tablespace_name,FORCE_LOGGING from dba_tablespaces where tablespace_name='USERS';


TABLESPACE_NAME                FOR
------------------------------ ---
USERS                          YES

-- Disable

SQL> SQL> alter tablespace users no force logging;

Tablespace altered.

SQL>  select tablespace_name,FORCE_LOGGING from dba_tablespaces where tablespace_name='USERS';


TABLESPACE_NAME                FOR
------------------------------ ---
USERS                          NO

NOTES:

1. Force logging option is by default there for undo tablespace.

SQL> ALTER TABLESPACE UNDOTBS1 FORCE LOGGING;
ALTER TABLESPACE UNDOTBS1 FORCE LOGGING
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace

2. Force logging is not applicable for TEMP tablespace.

SQL> alter tablespace TEMP FORCE LOGGING;
alter tablespace TEMP FORCE LOGGING
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE