12c undo 表空间的管理

最新推荐文章于 2021-09-16 23:02:23 发布

wluckdog

最新推荐文章于 2021-09-16 23:02:23 发布

阅读量2.2k

点赞数

本文链接：https://blog.csdn.net/wll_1017/article/details/78555094

版权

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 表空间

wluckdog

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
12c undo 表空间的管理

1、查看undo 表空间打开所有pdbSYS@cdb1211> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PRODPDB
复制链接

扫一扫