oracle11g wallet钱夹状态--备份还原/数据泵

--存储不足，先调整USERS表空间大小
查看表空间中的空闲空间
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS free_space_mb,
ROUND(SUM(blocks) * (SELECT value FROM v$parameter WHERE name = 'db_block_size')/1024/1024, 2) AS free_mb_alt
FROM dba_free_space
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name;

TABLESPACE_NAME FREE_SPACE_MB FREE_MB_ALT
------------------------------------------------------------ ------------- -----------
USERS 9079.94 9079.94

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_mzjo9qck_.dbf' RESIZE 1024M;


-----------------------------------------------------表列级加密
-- 查询表空间加密
SELECT tablespace_name, encrypted
FROM dba_tablespaces
WHERE encrypted = 'YES';

-- 查询表级加密
SELECT table_name, tablespace_name, encryption
FROM dba_tables
WHERE table_name = 'EMP';


-- 查询使用列级加密的表和列
col owner for a10
col TABLE_NAME for a20
col COLUMN_NAME for a20
col ENCRYPTION_ALG for a30
set line 200
SELECT owner, table_name, column_name, encryption_alg
FROM dba_encrypted_columns;

--创建密码文件夹
[oracle@orcl ~]$ mkdir -p ora_wallet
drwxr-xr-x 2 oracle oinstall 4096 Sep 28 02:25 ora_wallet
--sqlnet.ora参数文件中设置wallet目录
ENCRYPTION_WALLET_LOCATION=
(SROUCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/home/oracle/ora_wallet)
)
)


--创建密钥文件，指定 wallet 密码
SQL> alter system set encryption key identified by oracle;
System altered.

--关闭钱夹
SQL> alter system set encryption wallet close identified by oracle;
System altered.

--打开钱夹
SQL> alter system set encryption wallet open identified by oracle;


---对非sys对象的表列加密
alter table table_name modify(column_name encrypt);


SQL> conn scott/123
Connected.

SQL> alter table EMP modify(sal encrypt);
Table altered.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

关闭钱夹后再查询
SQL> alter system set encryption wallet close identified by oracle;
System altered.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-28365: wallet is not open


其他字段不受影响
SQL> select empno from emp;

EMPNO
----------
7369
7499
7521
7566
7654
7698

---------------------------------------表空间加密
对表空间中创建的所有对象进行加密。
当数据文件及其备份被窃取，没有密钥任然无法查看到其中的数据，对于保护诸如军事级别、his系统(hospital information system)的病人数据非常重要。
加密后的表空间加密的密钥存储在数据库中的字典表中。
不能对已经在使用的表空间进行加密, 但是可以通过向加密表空间中利用 data pump 导入数据, 或使用 create table, as select. 或 alter table.move, 把已经存在数据转储到加密表空间。

加密算法包括: 其中可以指定加密算法, 也可以不指定加密算法, 默认使用 AES128 算法。3DES168、AES128、AES192、AES256
create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf' size 1024M autoextend off encryption using (AES128) default storage(encrypt); --指定加密算法
create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf' size 1024M autoextend off encryption default storage(encrypt); --默认加密算法

--创建加密表空间
SQL> create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf' size 1024M autoextend off encryption default storage(encrypt);
Tablespace created.


SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENCRYP
------------------------------------------------------------ ------
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
TEST YES

----钱夹打开的情况下
--创建一个表指定在该加密表空间中：
SQL> create table t_wallet tablespace TEST as select * from scott.emp;
Table created.

--读取加密表空间下的表数据
SQL> select * from t_wallet;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20


--备份表空间
RMAN> backup tablespace "TEST";
Starting backup at 06-OCT-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-25
channel ORA_DISK_1: finished piece 1 at 06-OCT-25
piece handle=/u01/app/oracle/product/11204/db_1/dbs/0245ieko_1_1 tag=TAG20251006T164055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-25

--模拟误删除TEST表空间物理文件
[oracle@orcl datafile]$ cp TS_TEST.dbf TS_TEST.dbfbak
[oracle@orcl datafile]$ rm -f TS_TEST.dbf

--还原TEST表空间数据文件
RMAN> restore tablespace "TEST";
Starting restore at 06-OCT-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf
channel ORA_DISK_1: reading from backup piece /db_flash_recovery/ORCL/backupset/2025_10_06/o1_mf_nnndf_TAG20251006T165856_ng8oy0sn_.bkp
channel ORA_DISK_1: piece handle=/db_flash_recovery/ORCL/backupset/2025_10_06/o1_mf_nnndf_TAG20251006T165856_ng8oy0sn_.bkp tag=TAG20251006T165856
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-OCT-25

--正常打开数据库
SQL> alter database open;
Database altered.


-----钱夹关闭的情况下，数据库未关闭
--备份
RMAN> backup tablespace "TEST";
Starting backup at 06-OCT-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-25
channel ORA_DISK_1: finished piece 1 at 06-OCT-25
piece handle=/db_flash_recovery/ORCL/backupset/2025_10_06/o1_mf_nnndf_TAG20251006T170641_ng8pdk28_.bkp tag=TAG20251006T170641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-25

--还原
RMAN> restore tablespace "TEST";
Starting restore at 06-OCT-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf
channel ORA_DISK_1: reading from backup piece /db_flash_recovery/ORCL/backupset/2025_10_06/o1_mf_nnndf_TAG20251006T170641_ng8pdk28_.bkp
channel ORA_DISK_1: piece handle=/db_flash_recovery/ORCL/backupset/2025_10_06/o1_mf_nnndf_TAG20251006T170641_ng8pdk28_.bkp tag=TAG20251006T170641
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-OCT-25
--需要恢复
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf'

RMAN> recover datafile '/u01/app/oracle/oradata/ORCL/datafile/TS_TEST.dbf';
Starting recover at 06-OCT-25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00

SQL> alter database open;
Database altered.
钱夹开启和关闭状态下都可以进行备份和还原

-----------------------------------钱夹关闭状态下尝试导出导入
directory=TEST
dumpfile=expdp_1009_%u.dmp
logfile=expdp_1009.log
#parallel=8
compression=all
filesize=1G
cluster=n
tables=scott.emp
#query=(
#use r1.table_name:"where date> to_date('20250923',YYYYMMDD)",
#user2.table_name:"where ...")


directory=TEST
dumpfile=expdp_1009_%u.dmp
logfile=imppdp_1009.log
#parallel=8
compression=all
filesize=1G
cluster=n
tables=scott.emp
#query=(
#use r1.table_name:"where date> to_date('20250923',YYYYMMDD)",
#user2.table_name:"where ...")

----钱夹关闭状态下
[oracle@orcl test_directory]$ expdp \'/ as sysdba\' parfile=expdp_1009.parfile

[oracle@orcl test_directory]$ expdp \'/ as sysdba\' parfile=expdp_1009.parfile

Export: Release 11.2.0.4.0 - Production on Wed Oct 8 07:50:35 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" parfile=expdp_1009.parfile
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-28365: wallet is not open ---钱夹未打开 有生成dmp文件
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/test_directory/expdp_1009_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Wed Oct 8 07:50:39 2025 elapsed 0 00:00:04

[oracle@orcl test_directory]$ ll
-rw-r----- 1 oracle oinstall 45056 Oct 8 07:52 expdp_1009_01.dmp
-rw-r--r-- 1 oracle oinstall 1531 Oct 8 07:52 expdp_1009.log

尝试导入
[oracle@orcl test_directory]$ impdp \'/ as sysdba\' parfile=impdp_1009.parfile

Import: Release 11.2.0.4.0 - Production on Wed Oct 8 08:07:20 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" parfile=impdp_1009.parfile
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-28365: wallet is not open
Failing sql is:
CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2) ENCRYPT USING 'AES192' 'SHA-1', "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTI
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"SCOTT"."FK_DEPTNO" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 6 error(s) at Wed Oct 8 08:07:20 2025 elapsed 0 00:00:01

--打开钱夹后重新导入成功 但没数据
[oracle@orcl test_directory]$ impdp \'/ as sysdba\' parfile=impdp_1009.parfile

Import: Release 11.2.0.4.0 - Production on Wed Oct 8 08:07:57 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" parfile=impdp_1009.parfile
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 8 08:07:58 2025 elapsed 0 00:00:00

SQL> select * from emp;
no rows selected

钱夹关闭状态下无法正常导出导入作业