Oracle系统表空间的加解密

实验环境

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

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1

SQL> 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

加密

在线加密系统表空间：

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

就剩下临时表空间了，按照文档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
1
2
3
4
5

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

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

SQL>
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE 
  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf' SIZE 246415360
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
1
2
3
4
5

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

CREATE TEMPORARY TABLESPACE "TEMP_TDE" TEMPFILE 
  'temp01_tde.dbf' SIZE 246415360 
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
  ENCRYPTION 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    YES

6 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

当前默认的临时表空间：

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
1
2
3
4
5
6
7
8
9
10
11

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

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 tablespace

SQL> 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

解密

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

实例运行时，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
1
2
3
4
5

需要关闭数据库：

SQL> shutdown immediate
Pluggable Database closed.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB1                       MOUNTED
1
2
3
4
5
6
7
8

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

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.
1
2
3
4
5
6
7
8
9
10
11

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

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
1
2
3
4
5

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

alter database default temporary tablespace temp;

drop tablespace temp_tde including contents and datafiles;
1
2
3
4
5
6
7
8

现在，全部解密了：

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
1
2
3
4
5
6
7
8
9

参考

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