24 November, 2016

12.2 New Features -- 1 : Separate Undo Tablespace for each PDB

Unlike 12.1 MultiTenant, 12.2 introduces a separate Undo Tablespace for each PDB.

SQL> l
  1  select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
  2  from v$containers c, cdb_tablespaces t
  3  where c.con_id=t.con_id
  4  and t.tablespace_name like '%UNDO%'
  5* order by 1,2
SQL> /

    CON_ID CON_NAME         TABLESPACE_NAME  CONTENTS              STATUS
---------- ---------------- ---------------- --------------------- ---------
         1 CDB$ROOT         UNDOTBS1         UNDO                  ONLINE
         3 PDB1             UNDOTBS1         UNDO                  ONLINE
         5 PDB2             UNDOTBS1         UNDO                  ONLINE

SQL>


I have two PDBs and each PDB has an Undo Tablespace.

Let me create a new Undo Tablespace.

SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> create undo tablespace PDB1UNDO ;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_tablespace='PDB1UNDO';

System altered.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>
SQL> select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
  2  from v$containers c, cdb_tablespaces t
  3  where c.con_id=t.con_id
  4  and t.tablespace_name like '%UNDO%'
  5  order by 1,2
  6  /

    CON_ID CON_NAME         TABLESPACE_NAME  CONTENTS              STATUS
---------- ---------------- ---------------- --------------------- ---------
         1 CDB$ROOT         UNDOTBS1         UNDO                  ONLINE
         3 PDB1             PDB1UNDO         UNDO                  ONLINE
         5 PDB2             UNDOTBS1         UNDO                  ONLINE

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      PDB1UNDO
SQL> select tablespace_name, contents, status
  2  from dba_tablespaces
  3  where tablespace_name like '%UNDO%'
  4  /

TABLESPACE_NAME  CONTENTS              STATUS
---------------- --------------------- ---------
PDB1UNDO         UNDO                  ONLINE

SQL>


I was able to switch PDB1 to a new Undo Tablespace (and drop the old Undo Tablespace).
.
.
.

2 comments:

Anonymous said...

How to monitor this separate undo, such as which session or transaction using the UNDO, also its in past. Historical data is quite difficult to get.

Hemant K Chitale said...

You should create a separate Undo Tablespace on DAY 1 itself. So that there is no "historical baggage" to look up. What historical data do you want about Undo ?