8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Multitenant : PDB Logging Clause in Oracle Database 12c Release 1 (12.1.0.2)

The PDB logging clause is used to set the default tablespace logging clause for a PDB. If a tablespace is created without an explicit logging clause, the default PDB logging clause is used.

There are some issues with this feature unless you apply the relevant patch.

This article is written with the assumption the 20961627 patch has been applied to the 12.1.0.2 database.

Related articles.

Logging Clause

CREATE PLUGGABLE DATABASE

Adding the NOLOGGING clause during PDB creation sets the default logging mode for all subsequent tablespaces in the resulting PDB. The DBA_PDBS view displays the default logging clause for the PDB.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb5
  ADMIN USER pdb_adm IDENTIFIED BY Password1
  NOLOGGING;

ALTER PLUGGABLE DATABASE pdb5 OPEN;

COLUMN pdb_name FORMAT A20

SELECT pdb_name, logging
FROM   dba_pdbs
ORDER BY pdb_name;

PDB_NAME             LOGGING
-------------------- ---------
PDB$SEED             LOGGING
PDB1                 LOGGING
PDB2                 LOGGING
PDB5                 NOLOGGING

4 rows selected.

SQL>

If we create a new tablespace in the PDB without an explicit logging clause, we can see the default logging clause is used.

ALTER SESSION SET CONTAINER = pdb5;

CREATE TABLESPACE test1_ts;

SELECT tablespace_name, logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                LOGGING
------------------------------ ---------
SYSAUX                         LOGGING
SYSTEM                         LOGGING
TEMP                           NOLOGGING
TEST1_TS                       NOLOGGING

4 rows selected.

SQL>

The default logging clause can* be overridden if an explicit logging clause is used during tablespace creation.

ALTER SESSION SET CONTAINER = pdb5;

DROP TABLESPACE test1_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE test1_ts LOGGING;

SELECT tablespace_name, logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                LOGGING
------------------------------ ---------
SYSAUX                         LOGGING
SYSTEM                         LOGGING
TEMP                           NOLOGGING
TEST1_TS                       LOGGING

4 rows selected.

SQL>

ALTER PLUGGABLE DATABASE

The PDB logging clause can also be set using the ALTER PLUGGABLE DATABASE command. In this case, the affect is seen in during creation of new tablespaces in the PDB.

ALTER SESSION SET CONTAINER = pdb5;

ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE RESTRICTED FORCE;
ALTER PLUGGABLE DATABASE pdb5 LOGGING;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE FORCE;

DROP TABLESPACE test1_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE test1_ts;

SELECT tablespace_name, logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                LOGGING
------------------------------ ---------
SYSAUX                         LOGGING
SYSTEM                         LOGGING
TEMP                           NOLOGGING
TEST1_TS                       LOGGING

4 rows selected.

SQL>

The default logging clause can be overridden if an explicit logging clause is used during tablespace creation.

DROP TABLESPACE test1_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE test1_ts NOLOGGING;

SELECT tablespace_name, logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                LOGGING
------------------------------ ---------
SYSAUX                         LOGGING
SYSTEM                         LOGGING
TEMP                           NOLOGGING
TEST1_TS                       NOLOGGING

4 rows selected.

SQL>

FORCE LOGGING Clause

In addition to the basic logging clause, we can also set the force logging clause during PDB create, or after creation. Here are some examples using the ALTER PLUGGABLE DATABASE statement.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb5;


-- Enable FORCE LOGGING.
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE RESTRICTED FORCE;
ALTER PLUGGABLE DATABASE pdb5 ENABLE FORCE LOGGING;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE FORCE;

COLUMN pdb_name FORMAT A20
COLUMN force_logging FORMAT A15
COLUMN force_nologging FORMAT A15

SELECT pdb_id,
       pdb_name,
       logging,
       force_logging,
       force_nologging
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME             LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- -------------------- --------- --------------- ---------------
         7 PDB5                 LOGGING   YES             NO

SQL>


-- Enable FORCE NOLOGGING.
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE RESTRICTED FORCE;
ALTER PLUGGABLE DATABASE pdb5 ENABLE FORCE NOLOGGING;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE FORCE;

SELECT pdb_id,
       pdb_name,
       logging,
       force_logging,
       force_nologging
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME             LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- -------------------- --------- --------------- ---------------
         7 PDB5                 LOGGING   YES             NO

SQL>


-- Disable FORCE LOGGING.
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE RESTRICTED FORCE;
ALTER PLUGGABLE DATABASE pdb5 DISABLE FORCE NOLOGGING;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE FORCE;

SELECT pdb_id,
       pdb_name,
       logging,
       force_logging,
       force_nologging
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME             LOGGING   FORCE_LOGGING   FORCE_NOLOGGING
---------- -------------------- --------- --------------- ---------------
         7 PDB5                 LOGGING   NO              NO

SQL>

There are some restrictions associated with this, which you can read about here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.