8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 is feature does not work in the stock 12.1.0.2 release due to bug 18902135. The PDB logging clause is ignored when creating a new tablespace.
- After you apply the 18902135 patch, if you set the PDB logging clause to
NOLOGGING
, the PDB logging clause is *always* used to determine the logging setting of the tablespace. It can't be overridden by explicitly setting the logging clause in theCREATE TABLESPACE
statement. This goes against what the documentation states, so it appears the bug fix has introduced a new bug! If you set the PDB logging clause toLOGGING
, the setting can still be overridden at the tablespace level. - The feature has finally been fixed if you apply the 20961627 patch.
This article is written with the assumption the 20961627 patch has been applied to the 12.1.0.2 database.
Related articles.
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
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:
- PDB Tablespace Logging
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...