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

版权声明：本文为博主原创文章，未经博主允许不得转载。

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>