Infrastructure at your Service

By Franck Pachot

.
Pluggable Databases are supposed to be isolated, containing the whole of user data and metadata. This is the definition of dictionary separation coming with multitenant architecture: only system data and metadata are at CDB level. User data and metadata are in separate tablespaces belonging to the PDB. And this is what makes the unplug/plug available: because PDB tablespaces contain everything, you can transport their datafiles from one CDB to another.
However, if they are so isolated, can you explain why

  • You cannot flashback a PDB?
  • You need an auxiliary instance for PDB Point-In-Time recovery?
  • You need to put the PDB read-only before cloning it?


There is something that is not contained in your PDB but is at CDB level, and which contains user data. The UNDO tablespace is shared:

CaptureLocalUndo001

You cannot flashback a PDB because doing so requires to rollback the ongoing transactions at the time you flashback. Information was in UNDO tablespace at that time, but is not there anymore.

It’s the same idea with Point-In-Time recovery of PDB. You need to restore the UNDO tablespace to get those UNDO records from the Point-In-Time. But you cannot restore it in place because it’s shared with other PDBs that need current information. This is why you need an auxiliary instance for PDBPITR in 12.1

To clone a PDB cannot be done with ongoing transactions because their UNDO is not in the PDB. This is why it can be done only when the PDB is read-only.

12.2 Local UNDO

In 12.2 you can choose to have one UNDO tablespace per PDB, in local undo mode, which is the default in DBCA:

CaptureLocalUndo000

With local undo PDBs are truly isolated even when opened with ongoing transactions:

CaptureLocalUndo002

Look at the ‘RB segs’ column from RMAN report schema:

[[email protected] ~]$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Nov 8 18:53:46 2016
 
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
 
connected to target database: CDB1 (DBID=901060295)
 
RMAN> report schema;
 
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB1
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /u02/app/oracle/oradata/CDB1/system01.dbf
3 710 SYSAUX NO /u02/app/oracle/oradata/CDB1/sysaux01.dbf
4 215 UNDOTBS1 YES /u02/app/oracle/oradata/CDB1/undotbs01.dbf
5 270 PDB$SEED:SYSTEM NO /u02/app/oracle/oradata/CDB1/pdbseed/system01.dbf
6 560 PDB$SEED:SYSAUX NO /u02/app/oracle/oradata/CDB1/pdbseed/sysaux01.dbf
7 5 USERS NO /u02/app/oracle/oradata/CDB1/users01.dbf
8 180 PDB$SEED:UNDOTBS1 NO /u02/app/oracle/oradata/CDB1/pdbseed/undotbs01.dbf
9 270 PDB1:SYSTEM YES /u02/app/oracle/oradata/CDB1/PDB1/system01.dbf
10 590 PDB1:SYSAUX NO /u02/app/oracle/oradata/CDB1/PDB1/sysaux01.dbf
11 180 PDB1:UNDOTBS1 YES /u02/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf
12 5 PDB1:USERS NO /u02/app/oracle/oradata/CDB1/PDB1/users01.dbf
 
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 33 TEMP 32767 /u04/app/oracle/oradata/temp/temp01.dbf
2 64 PDB$SEED:TEMP 32767 /u04/app/oracle/oradata/temp/temp012016-10-04_11-34-07-330-AM.dbf
3 100 PDB1:TEMP 100 /u04/app/oracle/oradata/CDB1/PDB1/temp012016-10-04_11-34-07-330-AM.dbf

You have an UNDO tablespace in ROOT, in PDB$SEED and in each user PDB.

If you have a database in shared undo mode, you can move to local undo mode while in ‘startup migrate’. PDBs when opened will have an UNDO tablespace created. You can also create an UNDO tablespace in PDB$SEED.

Yes, in 12.2, you can open the PDB$SEED read/write for this purpose:


18:55:59 SQL> alter pluggable database PDB$SEED open read write force;
 
Pluggable database altered.
 
18:56:18 SQL> show pdbs;
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ WRITE NO
3 PDB1 READ WRITE NO
18:56:23 SQL> alter pluggable database PDB$SEED open read only force;
 
Pluggable database altered.

But remember this is only allowed for local undo migration.

The recommandation is to run in local undo mode, even in Single-Tenant.

More about it in the 12cR2 Multitenant book:

10 Comments

  • Harish says:

    Hi Franck,

    I have a doubt. I have created a lockdown profile in CDB. I have assigned the lockdown profile to PDB ORCLPDB1. When i query the output from cdb_lockdown_profiles i am getting the CON_ID column as 1.

    SQL> conn / as sysdba
    Connected.
    SQL> select con_id,PROFILE_NAME,RULE_TYPE,RULE,CLAUSE,CLAUSE_OPTION,OPTION_VALUE,status from cdb_lockdown_profiles where profile_name='TEST_LOCK';
     
    CON_ID PROFILE_NAME RULE_TYPE RULE CLAUSE CLAUSE_OPTION
    ---------- -------------------- -------------------- -------------------- -------------------- --------------------
    OPTION_VALUE STATUS
    -------------------- ----------
    1 TEST_LOCK STATEMENT ALTER SESSION SET
    DISABLE
     
    1 TEST_LOCK STATEMENT ALTER SESSION SET OPTIMIZER_MODE
    ENABLE
     
    1 TEST_LOCK STATEMENT ALTER SESSION SET CURSOR_SHARING
    ENABLE
     
    1 TEST_LOCK STATEMENT ALTER SYSTEM
    DISABLE
     
     
    SQL> show con_name
     
    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> show pdbs
     
    CON_ID CON_NAME OPEN MODE RESTRICTED
    ---------- ------------------------------ ---------- ----------
    2 PDB$SEED READ ONLY NO
    3 ORCLPDB1 READ WRITE NO
    4 ORCLPDB2 READ WRITE NO

    As per the con_id it should display as 3 right? why is it showing as 1? Have you tested this.

    Regards,

    Harish

    • Hi Harish,
      This is expected. The lockdown profile rules are defined – and stored – in CDB$ROOT only. The parameter that you set for the PDB references a lockdown profile name which is stored in CDB$ROOT. So CON_ID is 1 and you can see the enabled and disabled option/feature/statements only when you are in CDB$ROOT. A PDB user will see ‘insufficient privileges’ but cannot see the reason.

  • Harish says:

    Another scenario which i tested is below.

    I created a lock down profile in CDB and set the pdb_lockdown parameter. I dropped a old lockdown profile from the CDB and set the value of pdb_lockdown to the new name. When i logged in back to the PDB it contains the old value?
    Why is it so?

    SQL> conn /as sysdba
    Connected.
    SQL> drop lockdown profile LOCK_TEST;
     
    Lockdown Profile dropped.
     
    SQL> show pdbs
     
    CON_ID CON_NAME OPEN MODE RESTRICTED
    ---------- ------------------------------ ---------- ----------
    2 PDB$SEED READ ONLY NO
    3 ORCLPDB1 READ WRITE NO
    4 ORCLPDB2 READ WRITE NO
     
    SQL> show con_name
     
    CON_NAME
    ------------------------------
    CDB$ROOT
     
    SQL> alter system set pdb_lockdown='TEST_LOCK';
     
    System altered.
     
    SQL> alter session set container=ORCLPDB1;
     
    Session altered.
     
    SQL> alter system checkpoint;
     
    System altered.
     
    SQL> show parameter pdb_lockdown
     
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    pdb_lockdown string LOCK_TEST
    SQL> alter system checkpoint;
     
    System altered.

    Oracle docs say the below.

    If you set PDB_LOCKDOWN while connected to a CDB root, then the lockdown profile applies to all PDBs in the CDB. It does not apply to the CDB root.

    If you set PDB_LOCKDOWN while connected to an application root, then the lockdown profile applies to the application root and all PDBs in the application container.

    If you set PDB_LOCKDOWN while connected to a particular PDB, then the lockdown profile applies to that PDB and overrides the lockdown profile for the CDB or application container, if one exists.

    Just wanted to Know if you have faced this scenario

    Thanks,

    Harish

    • Hi,
      The parameter that you set in the CDB applies to all PDBs as a default. But if the parameter is set at PDB level, then this one is used. This is not only for lockdown profiles but for all parameters.
      You have the parameter set to LOCK_TEST in the PDB and set to TEST_LOCK at CDB level. The LOCK_TEST will be used in the PDB, unless you remove it (alter system reset) at PDB level.
      Regards,
      Franck.

  • Peter says:

    Hi Franck,
    I have tested it and can confirm :
    There is no need to have an auxiliary instance while performing PITR of a PDB in 12.2 – pretty cool!
    I’m installing new 12.2 RACs and up to know, I did not found any issues 🙂
    Best regards and thank you
    Peter

  • Satish says:

    Hi Franck,
    In my setup, PDB PIT recovery works in “Shared UNDO Mode” but fails in “Local UNDO Mode”

    Details are as follow:
    Oracle 12cR2 on Linux, PDB PIT recovery fails in “Local UNDO Mode” if I drop tablespace from pdb.
    Steps performed are as follow:
    – Create PDB [A]- Create tablespace [B] in PDB [A] – Create table “emp” in tablespace[B] – Added few rows in table
    – backup PDB [A]
    – Drop tablespace [B] from PDB [A]
    – Run PDB [A] PIT Restore recovery
    – Now i get below output, when i try to get table data in PDB [A]
    SQL> select * from emp;
    select * from emp
    *
    ERROR at line 1:
    ORA-00376: file 18 cannot be read at this time
    ORA-01111: name for data file 18 is unknown – rename to correct file
    ORA-01110: data file 18:
    ‘/u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00018’

    However same recovery works if i just make changes in table inside tablespace( instead of dropping tablespace)
    Also, PDB PIT recovery works fine in “Shared UNDO Mode” with same above steps (dropping tablespace).

    Any idea what could be wrong?

  • Sam says:

    For oracle RAC with shared undo, is it just to startup migrate and turn the local undo on, or it has more to it? Can you please share any differences when it comes to RAC database with 2 undo tablespaces each almost 400GB?
    Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team