CNDBA社区
Oracle 12C R2-新特性-多租户：支持本地UNDO模式
在12.1中，所有的在一个实例中的PDB只能共享同一个UNDO表空间。在12.2中它们都有各自的undo表空间了。这种新的管理机制就叫做本地undo模式。与此同时，在之前的版本中现在就成为是共享undo模式。本地undo模式为新建数据库的默认模式。
1 转换为共享undo模式
1.1 查询当前的模式：
SQL>COLUMN property_name FORMAT A30
SQL>COLUMN property_value FORMAT A30
SQL>SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SQL>1.2 查看ROOT和自己定义的pdb对应的undo表空间
SELECT con_id, tablespace_name
FROM cdb_tablespaces
WHERE tablespace_name LIKE 'UNDO%'
ORDER BY con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDOTBS1
SQL>2 切换为共享undo模式
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 1308622848 bytes
Fixed Size 8792440 bytes
Variable Size 822085256 bytes
Database Buffers 469762048 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> ALTER DATABASE LOCAL UNDO OFF;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1308622848 bytes
Fixed Size 8792440 bytes
Variable Size 822085256 bytes
Database Buffers 469762048 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL>
2.1 验证
SQL>COLUMN property_name FORMAT A30
SQL>COLUMN property_value FORMAT A30
SQL>SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED FALSE
SQL>虽然已经不是本地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 UNDOTBS1
SQL>--删除多余的undo表空间
SQL>ALTER SESSION SET CONTAINER = pdb1;
SQL>SELECT file_name
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb1/pdb1/undotbs01.dbf
SQL>
SQL>DROP TABLESPACE undotbs1;
Tablespace dropped.
SQL>随着所有老的undo表空间被移除了，现在该实例就是运行在共享undo模式上了。
3 切换为本地undo模式
利用上面的环境，重新切换回去。
查询：可以看到是运行在共享undo模式上，而且只有一个undo表空间。
SQL>SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED FALSE
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>3.1 切换为本地undo模式
和切换为共享undo模式步骤相同。
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 1308622848 bytes
Fixed Size 8792440 bytes
Variable Size 822085256 bytes
Database Buffers 469762048 bytes
Redo Buffers 7983104 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 1308622848 bytes
Fixed Size 8792440 bytes
Variable Size 822085256 bytes
Database Buffers 469762048 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL>3.2 验证
SQL>COLUMN property_name FORMAT A30
SQL>COLUMN property_value FORMAT A30
SQL>SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE--可以看到Oracle自动为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
SQL>3.3 新创建一个数据库
可以看到新创建的数据库也是运行在本地undo模式上。
SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1;
SQL>ALTER PLUGGABLE DATABASE pdb2 SAVE STATE;
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
4 UNDOTBS1
SQL>版权声明：本文为博主原创文章，未经博主允许不得转载。
本地UNDO
