SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/u01/app/oracle/admin/ORCL/wallet’ IDENTIFIED BY “wallet_password”;keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “wallet_password”;keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET MASTER KEY IDENTIFIED BY “wallet_password” WITH BACKUP;keystore altered.

SQL> CREATE TABLESPACE encrypted_data
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/encrypted_data01.dbf’ SIZE 100M
3 AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO
6 ENCRYPTION USING ‘AES256’ DEFAULT STORAGE (ENCRYPT);Tablespace created.

— 1. 查看加密表空间信息
SELECT tablespace_name, encrypted
FROM dba_tablespaces
WHERE encrypted = ‘YES’;– 2. 查看加密表空间的加密算法
SELECT tablespace_name, encryptionalg
FROM dba_tablespaces
WHERE encrypted = ‘YES’;– 3. 为加密表空间添加数据文件
ALTER TABLESPACE encrypted_data
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/encrypted_data02.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;– 4. 关闭和打开Wallet
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY “wallet_password”;ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “wallet_password”;– 5. 备份和恢复主密钥
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE TO ‘/u01/app/oracle/admin/ORCL/wallet/backup’ IDENTIFIED BY “wallet_password”;– 6. 轮换主密钥
ADMINISTER KEY MANAGEMENT SET MASTER KEY IDENTIFIED BY “wallet_password” WITH BACKUP;– 7. 删除加密表空间
DROP TABLESPACE encrypted_data INCLUDING CONTENTS AND DATAFILES;

— 1. 监控Wallet状态
SELECT * FROM v$encryption_wallet;– 2. 监控加密表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE encrypted = ‘YES’
)
) GROUP BY tablespace_name;– 3. 检查加密表空间的密钥版本
SELECT * FROM v$encryption_keys;– 4. 监控加密操作的性能
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE ‘%encryption%’ OR event LIKE ‘%decryption%’;– 5. 检查加密表空间的备份状态
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE encrypted = ‘YES’;– 6. 验证加密表空间的安全性
— 使用操作系统命令尝试直接读取数据文件
— hexdump -C /u01/app/oracle/oradata/ORCL/encrypted_data01.dbf | head -20

— 案例：为财务系统创建加密表空间

— 1. 配置Oracle Wallet
— 创建Wallet目录
mkdir -p /u01/app/oracle/admin/ORCL/wallet

— 设置Wallet位置
ALTER SYSTEM SET encryption_wallet_location = ‘(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/ORCL/wallet)))’ SCOPE=SPFILE;– 重启数据库
SHUTDOWN IMMEDIATE;STARTUP;– 打开Wallet并设置主密钥
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/u01/app/oracle/admin/ORCL/wallet’ IDENTIFIED BY “finance_wallet_password”;ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “finance_wallet_password”;ADMINISTER KEY MANAGEMENT SET MASTER KEY IDENTIFIED BY “finance_wallet_password” WITH BACKUP;– 2. 创建财务数据加密表空间
CREATE TABLESPACE finance_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/finance_data01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING ‘AES256’ DEFAULT STORAGE (ENCRYPT);– 3. 创建财务索引加密表空间
CREATE TABLESPACE finance_index
DATAFILE ‘/u01/app/oracle/oradata/ORCL/finance_index01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 20M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING ‘AES256’ DEFAULT STORAGE (ENCRYPT);– 4. 为财务用户分配表空间
CREATE USER finance_user IDENTIFIED BY password
DEFAULT TABLESPACE finance_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON finance_data
QUOTA UNLIMITED ON finance_index;– 5. 创建财务表
CREATE TABLE finance_user.accounts (
account_id NUMBER(10),
customer_id NUMBER(10),
account_type VARCHAR2(20),
balance NUMBER(12,2),
last_update DATE
) TABLESPACE finance_data;– 6. 创建索引
CREATE INDEX finance_user.accounts_customer_idx
ON finance_user.accounts(customer_id)
TABLESPACE finance_index;– 7. 插入测试数据
INSERT INTO finance_user.accounts VALUES (1, 101, ‘SAVINGS’, 10000.00, SYSDATE);INSERT INTO finance_user.accounts VALUES (2, 102, ‘CHECKING’, 5000.00, SYSDATE);INSERT INTO finance_user.accounts VALUES (3, 103, ‘SAVINGS’, 15000.00, SYSDATE);COMMIT;– 8. 验证数据加密
— 尝试直接读取数据文件
— hexdump -C /u01/app/oracle/oradata/ORCL/finance_data01.dbf | head -20

— 9. 监控加密表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
WHERE tablespace_name IN (‘FINANCE_DATA’, ‘FINANCE_INDEX’)
) GROUP BY tablespace_name;

— 1. Wallet无法打开问题
— 查看Wallet状态
SELECT * FROM v$encryption_wallet;– 解决方案：打开Wallet
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “wallet_password”;– 2. 主密钥丢失问题
— 查看密钥状态
SELECT * FROM v$encryption_keys;– 解决方案：从备份恢复密钥
ADMINISTER KEY MANAGEMENT RESTORE KEYSTORE FROM ‘/u01/app/oracle/admin/ORCL/wallet/backup’ IDENTIFIED BY “wallet_password”;– 3. 加密表空间无法访问问题
— 检查表空间状态
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE encrypted = ‘YES’;– 解决方案：确保Wallet已打开，主密钥正确
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “wallet_password”;– 4. 加密表空间性能问题
— 监控加密操作的性能
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE ‘%encryption%’ OR event LIKE ‘%decryption%’;– 解决方案：使用支持AES-NI指令集的CPU，优化SQL语句

— 5. 加密表空间备份问题
— 检查RMAN备份配置
RMAN>
CONFIGURE ENCRYPTION FOR DATABASE ON;CONFIGURE ENCRYPTION ALGORITHM ‘AES256’;– 解决方案：确保RMAN配置了加密备份

— 1. 数据库启动时Wallet无法打开
— 查看告警日志
SELECT * FROM v$diag_info;– 解决方案：在启动脚本中添加打开Wallet的命令
— 在$ORACLE_HOME/bin/dbstart脚本中添加：
— sqlplus / as sysdba << EOF -- ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password"; -- EOF -- 2. 加密表空间数据文件损坏 -- 检查数据文件状态 SELECT file_name, status, online_status FROM dba_data_files WHERE tablespace_name IN ( SELECT tablespace_name FROM dba_tablespaces WHERE encrypted = 'YES' );-- 解决方案：从备份恢复数据文件 ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/encrypted_data01.dbf' OFFLINE;-- 从备份恢复数据文件 -- 恢复后使数据文件在线 ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/encrypted_data01.dbf' ONLINE;-- 3. 主密钥轮换失败 -- 查看错误日志 SELECT * FROM v$diag_info;-- 解决方案：确保Wallet已打开，尝试使用不同的密钥轮换策略 ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password";ADMINISTER KEY MANAGEMENT SET MASTER KEY IDENTIFIED BY "wallet_password" WITH BACKUP;-- 4. 加密表空间扩展失败 -- 检查表空间状态 SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb, SUM(bytes - free_bytes)/1024/1024 AS used_mb, SUM(free_bytes)/1024/1024 AS free_mb FROM ( SELECT tablespace_name, bytes, CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END - bytes AS free_bytes FROM dba_data_files WHERE tablespace_name IN ( SELECT tablespace_name FROM dba_tablespaces WHERE encrypted = 'YES' ) ) GROUP BY tablespace_name;-- 解决方案：检查文件系统空间，添加数据文件 ALTER TABLESPACE encrypted_data ADD DATAFILE '/u01/app/oracle/oradata/ORCL/encrypted_data03.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;-- 5. 加密表空间迁移问题 -- 解决方案：使用表空间传输技术，确保目标数据库有正确的Wallet配置