8i | 9i | 10g | 11g | 12c | 13c | 18c | 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.

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>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.