1、查看undo 表空间
打开所有pdb
SYS@cdb1211> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PRODPDB MOUNTED
5 PDB1 MOUNTED
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
查看所有的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_management AUTO FALSE TRUE
1 undo_tablespace UNDOTBS1 TRUE TRUE
1 undo_retention 900 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 bytes
Fixed Size 8798312 bytes
Variable Size 599789464 bytes
Database Buffers 222298112 bytes
Redo Buffers 7974912 bytes
Database 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 bytes
Fixed Size 8798312 bytes
Variable Size 599789464 bytes
Database Buffers 222298112 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
关闭 local undo 模式,使用 shared und
SYS@cdb1211> ALTER DATABASE LOCAL UNDO OFF;
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 bytes
Fixed Size 8798312 bytes
Variable Size 599789464 bytes
Database Buffers 222298112 bytes
Redo Buffers 7974912 bytes
Database 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 /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 20:37:02 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
@> conn sys/oracle@pdb1 as sysdba
Connected.
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.dbf
SYS@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 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 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 bytes
Fixed Size 8798312 bytes
Variable Size 599789464 bytes
Database Buffers 222298112 bytes
Redo Buffers 7974912 bytes
Database 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 bytes
Fixed Size 8798312 bytes
Variable Size 599789464 bytes
Database Buffers 222298112 bytes
Redo Buffers 7974912 bytes
Database 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 FALSE
undo_management string AUTO
undo_retention integer 900
undo_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 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@pdb1> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_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 表空间