Oracle系统表空间的加解密

实验环境

数据库选择的是orclpdb1，当前系统表空间未加密：

SQL> show con_nameCON_NAME
------------------------------
ORCLPDB1SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from dba_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
USERS                          ONLINE    NO

加密

在线加密系统表空间：

SQL> alter tablespace system encryption online encrypt;Tablespace altered.SQL> alter tablespace sysaux encryption online encrypt;Tablespace altered.SQL> alter tablespace undotbs1 encryption online encrypt;Tablespace altered.SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from dba_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    YES
SYSAUX                         ONLINE    YES
UNDOTBS1                       ONLINE    YES
TEMP                           ONLINE    NO
USERS                          ONLINE    NO

就剩下临时表空间了，按照文档3.6.1 About Encryption Conversion for Tablespaces and Databases中表Table 3-2 Offline and Online Tablespace and Database Encryption Conversions的说明，在线和离线加解密都不可行：

SQL> alter tablespace temp encryption online encrypt;
alter tablespace temp encryption online encrypt
*
ERROR at line 1:
ORA-28370: ENCRYPT, DECRYPT or REKEY option not allowed

对于临时表空间，只能新建一个加密的临时表空间，然后指定其为默认临时表空间。过程如下。

首先从SQL Developer中得到当前临时表空间的SQL：

SQL>
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf' SIZE 246415360AUTOEXTEND ON NEXT 655360 MAXSIZE 32767MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

然后新建加密的临时表空间：

CREATE TEMPORARY TABLESPACE "TEMP_TDE" TEMPFILE 'temp01_tde.dbf' SIZE 246415360 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576ENCRYPTION ENCRYPT;Tablespace created.SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from dba_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    YES
SYSAUX                         ONLINE    YES
UNDOTBS1                       ONLINE    YES
TEMP                           ONLINE    NO
USERS                          ONLINE    NO
TEMP_TDE                       ONLINE    YES6 rows selected.

当前默认的临时表空间：

SQL>
col PROPERTY_NAME for a40
col PROPERTY_VALUE for a10
col DESCRIPTION for a40
set lines 120
select * from database_properties where property_name like 'DEFAULT%SPACE%';PROPERTY_NAME                            PROPERTY_V DESCRIPTION
---------------------------------------- ---------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE             USERS      Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE                  TEMP       Name of default temporary tablespace

设置新的临时表空间，删除老的临时表空间：

SQL> alter database default temporary tablespace temp_tde;Database altered.SQL> drop tablespace temp including contents and datafiles;Tablespace dropped.SQL> select * from database_properties where property_name like 'DEFAULT%SPACE%';PROPERTY_NAME                            PROPERTY_V DESCRIPTION
---------------------------------------- ---------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE             USERS      Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE                  TEMP_TDE   Name of default temporary tablespaceSQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from dba_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    YES
SYSAUX                         ONLINE    YES
UNDOTBS1                       ONLINE    YES
USERS                          ONLINE    NO
TEMP_TDE                       ONLINE    YES

解密

解密当然也可以在线，但为了涵盖加解密功能，此处使用离线解密。

实例运行时，offline是不行的：

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

需要关闭数据库：

SQL> shutdown immediate
Pluggable Database closed.SQL> show pdbsCON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------3 ORCLPDB1                       MOUNTED

此时表空间已经离线了，可以解密：

SQL> alter tablespace system encryption offline decrypt;Tablespace altered.SQL> alter tablespace sysaux encryption offline decrypt;Tablespace altered.SQL> alter tablespace undotbs1 encryption offline decrypt;Tablespace altered.

临时表空间还是同样的问题，不能直接解密：

SQL> alter tablespace temp_tde encryption offline decrypt;
alter tablespace temp_tde encryption offline decrypt
*
ERROR at line 1:
ORA-28370: ENCRYPT, DECRYPT or REKEY option not allowed

启动数据库，目前状态如下：

SQL> alter database open;Database altered.SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from user_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
USERS                          ONLINE    NO
TEMP_TDE                       ONLINE    YES

同前例，创建一个新的未加密的临时表空间，然后取而代之：

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf' SIZE 246415360AUTOEXTEND ON NEXT 655360 MAXSIZE 32767MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;alter database default temporary tablespace temp;drop tablespace temp_tde including contents and datafiles;

现在，全部解密了：

SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from user_tablespaces;TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
USERS                          ONLINE    NO

参考

• Oracle Temporary Tablespace
• ORA-01541: System Tablespace Cannot Be Brought Offline; Shut Down If Necessary