📋Steps in this guide1/3

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 in Oracle Database 12c Release 1 (12.1.0.2).

oracle 12cconfigurationintermediate

by OracleDba

13 views

CREATE PLUGGABLE DATABASE

Adding the clause during PDB creation sets the default logging mode for all subsequent tablespaces in the resulting PDB. The view displays the default logging clause for the PDB. If we create a new tablespace in the PDB without an explicit logging clause, we can see the default logging clause is used. The default logging clause can* be overridden if an explicit logging clause is used during tablespace creation.

Code/Command (click line numbers to comment):

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>

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>

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 command. In this case, the affect is seen in during creation of new tablespaces in the PDB. The default logging clause can be overridden if an explicit logging clause is used during tablespace creation.

Code/Command (click line numbers to comment):

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>

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 statement. There are some restrictions associated with this, which you can read about here . For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!