Oracle 12C R2-新特性-多租户：支持本地UNDO模式

小灰灰 2022-05-31 04:46 228阅读 0赞

在12.1中，所有的在一个实例中的PDB只能共享同一个UNDO表空间。在12.2中它们都有各自的undo表空间了。这种新的管理机制就叫做本地undo模式。与此同时，在之前的版本中现在就成为是共享undo模式。

一.shared undo 转换为local undo模式  
1.查询当前模式

SQL> select property_name, property_value from   database_properties where  property_name = 'LOCAL_UNDO_ENABLED';
    
    no rows selected
    
    
    SQL> select con_id, tablespace_name from   cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;
    
        CON_ID TABLESPACE_NAME
    ---------- ------------------------------
    	 1 UNDOTBS1
    
    
    
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/shiyu/system01.dbf
    /u01/app/oracle/oradata/shiyu/pdbseed/system01.dbf
    /u01/app/oracle/oradata/shiyu/sysaux01.dbf
    /u01/app/oracle/oradata/shiyu/pdbseed/sysaux01.dbf
    /u01/app/oracle/oradata/shiyu/undotbs01.dbf
    /u01/app/oracle/oradata/shiyu/users01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf
    
    
    SQL> alter session set container=ORCLPDB;                                                                                                  
    
    Session altered.
    
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/shiyu/undotbs01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf

2.切换为local undo 模式

SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup upgrade
    ORACLE instance started.
    
    Total System Global Area  843055104 bytes
    Fixed Size		    8798360 bytes
    Variable Size		  591400808 bytes
    Database Buffers	  239075328 bytes
    Redo Buffers		    3780608 bytes
    Database mounted.
    Database opened.
    SQL> alter database local undo on;
    
    Database altered.
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  843055104 bytes
    Fixed Size		    8798360 bytes
    Variable Size		  591400808 bytes
    Database Buffers	  239075328 bytes
    Redo Buffers		    3780608 bytes
    Database mounted.
    Database opened.
    
    SQL> show pdbs
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 ORCLPDB			  MOUNTED
    SQL> alter session set container=ORCLPDB;
    
    Session altered.
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> show pdbs
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 3 ORCLPDB			  READ WRITE NO

3.验证：

SQL> col property_name for a25;
    SQL>col property_value for a25;
    SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
    
    PROPERTY_NAME		  PROPERTY_VALUE
    ------------------------- -------------------------
    LOCAL_UNDO_ENABLED	  TRUE
    
    SQL> select con_id, tablespace_name from cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;
    
        CON_ID TABLESPACE_NAME
    ---------- ------------------------------
    	 1 UNDOTBS1
    	 3 UNDO_1
    
    SQL> alter session set container=ORCLPDB;
    
    Session altered.
    
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf
    /u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbf

二、local undo 转换为shared undo模式

1.查看当前模式

2.查看ROOT和自己定义的pdb对应的undo表空间

SQL> select con_id, tablespace_name from cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;
    
        CON_ID TABLESPACE_NAME
    ---------- ------------------------------
    	 1 UNDOTBS1
    	 3 UNDO_1

3.切换成shared undo模式

4.验证

QL> col property_name for a25;
    SQL> col property_value for a25;
    
    SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
    
    PROPERTY_NAME		  PROPERTY_VALUE
    ------------------------- -------------------------
    LOCAL_UNDO_ENABLED	  FALSE

注意：虽然已经不是本地undo模式了，但是之前存在的undo表空间不会自动删除。如果碍事，要手动删除。

SQL> select con_id, tablespace_name from   cdb_tablespaces  where  tablespace_name like 'UNDO%' order by con_id;
    
        CON_ID TABLESPACE_NAME
    ---------- ------------------------------
    	 1 UNDOTBS1
    	 3 UNDO_1

删除多余的undo表空间

SQL> alter session set container=ORCLPDB;
    
    Session altered.
    
    SQL> select file_name from dba_data_files where  tablespace_name = 'UNDOTBS1';
    
    no rows selected
    
    SQL> select file_name from dba_data_files where  tablespace_name = 'UNDO_1';
    
    FILE_NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbf
    
    SQL> drop tablespace undo_1;
    
    Tablespace dropped.
    
    SQL> select file_name from dba_data_files where  tablespace_name = 'UNDO_1';
    
    no rows selected

随着所有老的undo表空间被移除了，现在该实例就是运行在共享undo模式上了。