Since the architecture of Oracle Database 12c has changed, now we have two kind of databases "Pluggable Databases (PDB)" and "Container Databases (CDB)". With these many things changes as well, for example the Logging Levels and that's what I will talk in this Articles. I will provide the definition of Oracle Documentation and also some examples for the Logging levels.

  • Table Level - Logging
  • Index Level - Logging
  • Tablespace Level - Logging
  • Tablespace Level - Force Logging
  • PDB Level - Logging
  • PDB Level - Force No logging
  • CDB - Force Logging

Table Level - Logging

This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged. LOGGING is the default.

If index is nonpartitioned, then this clause specifies the logging attribute of the index.

If index is partitioned, then this clause determines:

  • The default value of all partitions specified in the CREATE statement, unless you specify the logging_clause in the PARTITION description clause
  • The default value for the segments associated with the index partitions
  • The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE ... ADD PARTITION operations

The logging attribute of the index is independent of that of its base table.

If you omit this clause, then the logging attribute is that of the tablespace in which it resides.

Examples:

SQL> create table table_logging (col1 number ) logging;

Table created.

SQL> select table_name, logging from dba_tables where table_name='TABLE_LOGGING'

TABLE_NAME      LOG
--------------- ---
TABLE_LOGGING   YES


SQL> alter table table_logging nologging;

Table altered.

SQL> select table_name, logging from dba_tables where table_name='TABLE_LOGGING'

TABLE_NAME      LOG
--------------- ---
TABLE_LOGGING   NO

Index Level - Logging

Use the logging_clause to change the logging attribute of the index. If you also specify the REBUILD clause, then this new setting affects the rebuild operation. If you specify a different value for logging in the REBUILD clause, then Oracle Database uses the last logging value specified as the logging attribute of the index and of the rebuild operation.

An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.

Examples:

SQL> create index idx1 on table_logging(col1) logging;

Index created.

SQL> select index_name, logging from dba_indexes where table_name='TABLE_LOGGING'

INDEX_NAME      LOG
--------------- ---
IDX1            YES

SQL> alter index idx1 nologging;

Index altered.

SQL>

SQL> select index_name, logging from dba_indexes where table_name='TABLE_LOGGING'

INDEX_NAME LOG
---------- ---
IDX1       NO

Tablespace Level - Logging

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. This clause is not valid for a temporary or undo tablespace.

If you omit this clause, then the default is LOGGING. The exception is creating a tablespace in a PDB. In this case, if you omit this clause, then the tablespace uses the logging attribute of the PDB.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

Examples:

SQL> create tablespace tbs1 datafile size 5M logging;

Tablespace created.

SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
TBS1 LOGGING NO


SQL> alter tablespace tbs1 nologging;

Tablespace altered.


SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME     LOGGING   FORCE_LOGGING
------------------- --------- -------------
TBS1                NOLOGGING NO

 

Tablespace Level - Force Logging

Use this clause to put the tablespace into FORCE LOGGING mode. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.

This setting does not exclude the NOLOGGING attribute. You can specify both FORCE LOGGING and NOLOGGING. In this case, NOLOGGING is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in FORCE LOGGING mode. If you subsequently take the tablespace out of FORCE LOGGING mode, then the NOLOGGING default is once again enforced.

Examples:

SQL> create tablespace tbs2 datafile size 5M force logging;

Tablespace created.


SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name='TBS2';

TABLESPACE_NAME                LOGGING   FOR
------------------------------ --------- ---
TBS2                           LOGGING   NO


SQL> alter tablespace tbs2 no force logging;

Tablespace altered.


SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name='TBS2';

TABLESPACE_NAME                LOGGING   FOR
------------------------------ --------- ---
TBS2                           LOGGING   YES

PDB Level - Logging

Use this clause to change the default logging attribute for tablespaces subsequently created within the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING) or not (NOLOGGING).The default is LOGGING.

Examples:

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   NO              NO

SQL>

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter pluggable database pdb1 nologging;
alter pluggable database pdb1 nologging
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database


SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL>

SQL> startup restrict;
Pluggable Database opened.
SQL> alter pluggable database pdb1 nologging;

Pluggable database altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs
2 ;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       NOLOGGING NO              NO

SQL> alter pluggable database pdb1 logging;

Pluggable database altered.


SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   NO             NO

PDB Level - Force Logging

SQL> alter pluggable database pdb1 enable force logging;

Pluggable database altered.

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   YES             NO

SQL> alter pluggable database pdb1 disable force logging;

Pluggable database altered.

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   NO              NO

SQL> alter pluggable database pdb1 enable force nologging;

Pluggable database altered.

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   NO              YES

SQL> alter pluggable database pdb1 disable force nologging;

Pluggable database altered.

SQL> select pdb_name, logging, force_logging, force_nologging from cdb_pdbs;

PDB_NAME   LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- --------- --------------- ---------------
PDB1       LOGGING   NO              NO

SQL>

CDB Level - Force Logging

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, then Oracle Database waits for all ongoing unlogged operations to finish.

Examples:

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------
YES

SQL> alter database no force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------
NO

Follow me: