12c undo 表空间的管理

1、查看undo 表空间

打开所有pdbSYS@cdb1211> show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PRODPDB  MOUNTED 5 PDB1   MOUNTEDSYS@cdb1211>  alter pluggable database all open; Pluggable database altered.SYS@cdb1211> show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PRODPDB  READ WRITE NO 5 PDB1   READ WRITE NO查看所有的undo表空间SYS@cdb1211>  select con_id,TABLESPACE_NAME,CONTENTS  from cdb_tablespaces where contents='UNDO';     CON_ID TABLESPACE_NAME  CONTENTS---------- ------------------------------ --------------------- 3 UNDOTBS1  UNDO 5 UNDOTBS1  UNDO 1 UNDOTBS1  UNDO查看所有undo的数据文件SYS@cdb1211>  col FILE_NAME for a60 SYS@cdb1211>  select con_id,tablespace_name,file_name from cdb_data_files  where tablespace_name='UNDOTBS1';     CON_ID TABLESPACE_NAME  FILE_NAME---------- ------------------------------ ------------------------------------------------------------ 3 UNDOTBS1  /u01/app/oracle/oradata/cdb1211/prodpdb/undotbs01.dbf 1 UNDOTBS1  /u01/app/oracle/oradata/cdb1211/undotbs01.dbf 5 UNDOTBS1  /u01/app/oracle/oradata/cdb1211/pdb1/undotbs01.dbf查看 undo 相关参数 SYS@cdb1211>  col FILE_NAME for a60 SYS@cdb1211>  col name for a20 SYS@cdb1211>  select con_id,NAME,VALUE,ISINSTANCE_MODIFIABLE, ISPDB_MODIFIABLE from v$parameter where name like 'undo%';     CON_ID NAME VALUE     ISINS ISPDB---------- -------------------- -------------------- ----- ----- 1 undo_managementAUTO     FALSE TRUE 1 undo_tablespaceUNDOTBS1     TRUE  TRUE 1 undo_retention900     TRUE  TRUE查看 CDB 当前 undo 模式 SYS@cdb1211>  col property_name for a20 SYS@cdb1211>  col PROPERTY_VALUE for a20 SYS@cdb1211>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='LOCAL_UNDO_ENABLED'; PROPERTY_NAME     PROPERTY_VALUE-------------------- --------------------LOCAL_UNDO_ENABLED   TRUE 当前环境使用 12cR2 版本，DBCA 创建 cdb1 时，默认勾选了 Use Local Undo tablespace for PDBs   

 设置 CDB 使用 Shared Undo Mode

关闭实例，startup upgrade 启动实例    SYS@cdb1211>  shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.SYS@cdb1211>  startup upgrade; ORACLE instance started.Total System Global Area  838860800 bytesFixed Size    8798312 bytesVariable Size  599789464 bytesDatabase Buffers  222298112 bytesRedo Buffers    7974912 bytesDatabase mounted.Database opened.关闭实例，startup upgrade 启动实例 SYS@cdb1211>  shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.SYS@cdb1211>  startup upgrade; ORACLE instance started.Total System Global Area  838860800 bytesFixed Size    8798312 bytesVariable Size  599789464 bytesDatabase Buffers  222298112 bytesRedo Buffers    7974912 bytesDatabase mounted.Database opened.关闭 local undo 模式,使用 shared undSYS@cdb1211> ALTER DATABASE LOCAL UNDO OFF; Database altered.SYS@cdb1211>  shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.SYS@cdb1211> startupORACLE instance started.Total System Global Area  838860800 bytesFixed Size    8798312 bytesVariable Size  599789464 bytesDatabase Buffers  222298112 bytesRedo Buffers    7974912 bytesDatabase mounted.Database opened.SYS@cdb1211> alter pluggable database all open; Pluggable database altered.SYS@cdb1211> show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PRODPDB  READ WRITE NO 5 PDB1   READ WRITE NO   查看 cdb1 当前 undo 模式 SYS@cdb1211>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='LOCAL_UNDO_ENABLED'; PROPERTY_NAME     PROPERTY_VALUE-------------------- --------------------LOCAL_UNDO_ENABLED   FALSE当前使用 Shared Undo Mode，CDB 中所有容器共享使用一个 Undo 表空间

 删除 pdb1 本地 undo 表空间 

 共享 undo 模式下，oracle 忽略 PDB 本地的 undo 表空间。 可以删除 PDB 本地的 undo 表空间及数据文件。 

 设置客户端的监听 pdb1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = pdb1.enmoedu.com)    )  )登录pdb1[oracle@enmoedu1 admin]$ sqlplus /nologSQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 20:37:02 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.@> conn sys/oracle@pdb1 as sysdbaConnected.SYS@pdb1> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTBS1'; TABLESPACE_NAME        FILE_NAME------------------------------ ------------------------------------------------------------UNDOTBS1       /u01/app/oracle/oradata/cdb1211/pdb1/undotbs01.dbfSYS@pdb1>  drop tablespace undotbs1 including contents and datafiles; Tablespace dropped.SYS@pdb1> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTBS1';no rows selected

 设置 CDB 使用 Local Undo Mode 

  设置当前 CDB 使用 12.2 版本默认的 Local Undo Mode， 即 CDB 中每个容器使用本地 的 undo 表空间。  SYS@cdb1211>  select con_id,TABLESPACE_NAME,CONTENTS  from cdb_tablespaces where contents='UNDO';     CON_ID TABLESPACE CONTENTS---------- ---------- --------------------- 3 UNDOTBS1   UNDO 1 UNDOTBS1   UNDO 1 SMALLUNDO  UNDO查看 CDB 当前 undo 模式 SYS@cdb1211>  col property_name for a20SYS@cdb1211>  col PROPERTY_VALUE for a20 SYS@cdb1211>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='LOCAL_UNDO_ENABLED'; PROPERTY_NAME     PROPERTY_VALUE-------------------- --------------------LOCAL_UNDO_ENABLED   FALSE设置 Local Undo Mode  关闭数据库实例 SYS@cdb1211>  shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down. startup upgrade 启动实例 SYS@cdb1211> startup upgrade; ORACLE instance started.Total System Global Area  838860800 bytesFixed Size    8798312 bytesVariable Size  599789464 bytesDatabase Buffers  222298112 bytesRedo Buffers    7974912 bytesDatabase mounted.Database opened. 设置 undo 模式  SYS@cdb1211>  ALTER DATABASE LOCAL UNDO ON; Database altered.SYS@cdb1211>  shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.SYS@cdb1211>  startup ORACLE instance started.Total System Global Area  838860800 bytesFixed Size    8798312 bytesVariable Size  599789464 bytesDatabase Buffers  222298112 bytesRedo Buffers    7974912 bytesDatabase mounted.Database opened.SYS@cdb1211>  col property_name for a20 SYS@cdb1211>  col PROPERTY_VALUE for a20 SYS@cdb1211>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='LOCAL_UNDO_ENABLED'; PROPERTY_NAME     PROPERTY_VALUE-------------------- --------------------LOCAL_UNDO_ENABLED   TRUE启动 PDB SYS@cdb1211>  alter pluggable database all open; Pluggable database altered.SYS@cdb1211> show pdbs     CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PRODPDB  READ WRITE NO 5 PDB1   READ WRITE NO  SYS@cdb1211>  show parameter undo NAME     TYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled     boolean FALSEundo_management      string AUTOundo_retention     integer 900undo_tablespace      string SMALLUNDO[oracle@enmoedu1 ~]$  sqlplus sys/oracle@pdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 22:08:26 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSYS@pdb1> show parameter undoNAME     TYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled     boolean FALSEundo_management      string AUTOundo_retention     integer 900undo_tablespace      string UNDO_1     查看 CDB 数据库中所有 undo 表空间    select con_id,TABLESPACE_NAME,CONTENTS  from cdb_tablespaces where contents='UNDO';      CON_ID TABLESPACE CONTENTS---------- ---------- --------------------- 1 UNDOTBS1   UNDO 1 SMALLUNDO  UNDO 5 UNDO_1     UNDO 3 UNDOTBS1   UNDO查看 undo 表空间对应数据文件 SYS@cdb1211>   col FILE_NAME for a60 SYS@cdb1211> select con_id,tablespace_name,file_name from cdb_data_files  where tablespace_name like '%UNDO%';      CON_ID TABLESPACE FILE_NAME---------- ---------- ------------------------------------------------------------ 1 UNDOTBS1   /u01/app/oracle/oradata/cdb1211/undotbs01.dbf 1 SMALLUNDO  /u01/app/oracle/oradata/cdb1211/smallundo01.dbf 5 UNDO_1     /u01/app/oracle/oradata/cdb1211/pdb1/system01_i1_undo.dbf 3 UNDOTBS1   /u01/app/oracle/oradata/cdb1211/prodpdb/undotbs01.dbf 设置 CDB 使用 Local Undo Mode 后，数据库在每个 PDB 中创建了本地的 undo 表空间