Oracle Database 21c新特性讲座（4）——检查数据泵转储文件的有效性、进行模式导出传输表空间元数据、导入时使用索引压缩

甲骨文云技术 2020-12-30

1701

这是Oracle Database 21c新特性讲座的第4讲，今天将通过3组实验为大家讲解Oracle Database 21c当中针对数据泵功能的增强。

实验4-1:检查Oracle数据泵转储文件的有效性

在数据泵将数据表导出时，可以向转储文件添加校验和，以便能够确认导出后转储文件仍然有效并且数据是完整的，没有损坏。Oracle Database 21c扩展了数据完整性检查，为Oracle数据泵和外部表转储文件头之外的所有剩余块添加了额外的校验和并可以指定算法。在导出操作期间可以使用CHECKSUM参数。

[oracle@henry ~]$  expdp system/HA_#hahaha6688@pdb1 TABLES=hr.employees DUMPFILE=dp_dir:emp.dmp CHECKSUM=yes REUSE_DUMPFILES=yes

Export: Release 21.0.0.0.0 - Production on Sun Dec 27 15:10:26 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@pdb1 TABLES=hr.employees DUMPFILE=dp_dir:emp.dmp CHECKSUM=yes REUSE_DUMPFILES=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  home/oracle/labs/M104786GC10/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 27 15:10:57 2020 elapsed 0 00:00:26

在导出时，可以通过CHECKSUM_ALGORITHM参数来指定算法，可用的算法有：SHA384、SHA512、CRC32。如果我们像上面的例子中使用CHECKSUM参数，则使用SHA256算法。我们设定了CHECKSUM_ALGORITHM参数之后，就不要再设定CHECKSUM参数，避免出错。

[oracle@henry ~]$ expdp system@pdb1 TABLES=hr.employees DUMPFILE=dp_dir:emp384.dmp CHECKSUM_ALGORITHM=SHA384 REUSE_DUMPFILES=yes

Export: Release 21.0.0.0.0 - Production on Sun Dec 27 15:24:24 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@pdb1 TABLES=hr.employees DUMPFILE=dp_dir:emp384.dmp CHECKSUM_ALGORITHM=SHA384 REUSE_DUMPFILES=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  home/oracle/labs/M104786GC10/emp384.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 27 15:24:44 2020 elapsed 0 00:00:13

接下来我们对导出的文件当中的一个进行破坏，然后使用数据泵对他进行校验。通过观察，当前employees这个表有三份导出文件。

[oracle@henry M104786GC10]$ ll *.dmp
-rw-r----- 1 oracle oinstall 274432 Dec 27 15:10 emp.dmp
-rw-r----- 1 oracle oinstall 274432 Dec 27 15:24 emp384.dmp
-rw-r----- 1 oracle oinstall 274432 Dec 27 15:28 emp512.dmp

我们通过下面代码，破坏其中emp.dmp文件。

sed -in '1 s/A//' home/oracle/labs/M104786GC10/emp.dmp
sed -in '1 s/B//' home/oracle/labs/M104786GC10/emp.dmp

我们将hr下面的employees表先删除，然后我们对上面三个文件进行测试，注意参数是VERIFY_ONLY=YES只是校验一下，并没有真正导入。

SQL> DROP TABLE employees CASCADE CONSTRAINTS;

Table dropped.

接下来对上面3个文件进行有效性测试。首先测试两个没有问题的，通过观察，都显示“dump file set is consistent”。

[oracle@henry ~]$ impdp system@pdb1 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_ONLY=YES

Import: Release 21.0.0.0.0 - Production on Sun Dec 27 15:40:02 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/home/oracle/labs/M104786GC10/emp512.dmp"
dump file set is consistent
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 27 15:40:14 2020 elapsed 0 00:00:03

[oracle@henry ~]$ impdp system@pdb1 FULL=yes DUMPFILE=dp_dir:emp384.dmp VERIFY_ONLY=YES

Import: Release 21.0.0.0.0 - Production on Sun Dec 27 15:40:24 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/home/oracle/labs/M104786GC10/emp384.dmp"
dump file set is consistent
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 27 15:40:32 2020 elapsed 0 00:00:02

我们发现emp.dmp文件已经损坏，并报错39411.

[oracle@henry ~]$ impdp system@pdb1 FULL=yes DUMPFILE=dp_dir:emp.dmp VERIFY_ONLY=YES

Import: Release 21.0.0.0.0 - Production on Sun Dec 27 15:41:30 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39411: header checksum error in dump file "/home/oracle/labs/M104786GC10/emp.dmp"

我们通过下面的命令查查ORA-39411是个什么错误。下面写得很清楚，是dump文件的文件头checksum错误，具体的原因在下面也有详细解释。最后请你联系我们的support部门寻求帮助。

[oracle@henry ~]$ oerr ora 39411
39411, 00000, "header checksum error in dump file \"%s\""
// *Cause:  The header block for the Data Pump dump file contained a
//          header checksum that did not match the value calculated from the
//          header block as read from disk. This indicates that the header
//          was tampered with or otherwise corrupted due to transmission or
//          media failure.
// *Action: Contact Oracle Support Services.

最后将没有损坏的dump文件导入。

[oracle@henry ~]$ impdp system@PDB1 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=YES

Import: Release 21.0.0.0.0 - Production on Sun Dec 27 15:46:14 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@PDB1 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=YES
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 27 15:46:41 2020 elapsed 0 00:00:21

实验4-2:使用数据泵进行模式导出传输表空间元数据

这个实验中将为大家演示并行导出Transportable Tablespace (TTS) metadata。我们首先创建一个新的表空间叫做users2，然后将它以并行的形式导出。

为了保证导出的一致性，我们将这个表空间user2设定为只读模式。

SQL> CREATE TABLESPACE users2 DATAFILE '/u02/app/oracle/oradata/DB21c_icn1b6/DB21C_ICN1B6/users02.dbf'  SIZE 50M;

Tablespace created.

SQL> ALTER TABLESPACE users2 READ ONLY;

Tablespace altered.

接下来我们使用并行度为4执行TTS操作。

[oracle@henry ~]$ expdp \"sys@pdb1 AS SYSDBA\" dumpfile=dp_dir:pdb1.dmp  TRANSPORT_TABLESPACES=users2 TRANSPORT_FULL_CHECK=YES LOGFILE=tts.log REUSE_DUMPFILES=YES PARALLEL=4 ENCRYPTION_PASSWORD='hahaha'

Export: Release 21.0.0.0.0 - Production on Sun Dec 27 16:12:53 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/********@pdb1 AS SYSDBA" dumpfile=dp_dir:pdb1.dmp TRANSPORT_TABLESPACES=users2 TRANSPORT_FULL_CHECK=YES LOGFILE=tts.log REUSE_DUMPFILES=YES PARALLEL=4 ENCRYPTION_PASSWORD=********
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  home/oracle/labs/M104786GC10/pdb1.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS2:
  u02/app/oracle/oradata/DB21c_icn1b6/DB21C_ICN1B6/users02.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Dec 27 16:13:09 2020 elapsed 0 00:00:10

导出之后，将users2表空间设定为可读写模式。

SQL> alter tablespace users2 read write;

Tablespace altered.

SQL>

实验4-3:数据泵导入时使用索引压缩

我们首先了解一下hr.employees这张表的压缩及索引情况。通过观察发现employees这张表没有启动压缩。这张表对应的6个index也没有使用压缩技术。其中第一个是创建唯一约束的时候对应的唯一性索引，第二个是主键对应的唯一性索引，其他4个是创建的普通索引。

SQL> SELECT compression, compress_for FROM DBA_TABLES WHERE table_name='EMPLOYEES' and owner='HR';

COMPRESSION          COMPRESS_FOR
-------------------- --------------------
DISABLED

SQL> SELECT index_name, compression FROM dba_indexes WHERE table_name='EMPLOYEES' and owner='HR';

INDEX_NAME                     COMPRESSION
------------------------------ --------------------
EMP_EMAIL_UK                   DISABLED
EMP_EMP_ID_PK                  DISABLED
EMP_DEPARTMENT_IX              DISABLED
EMP_JOB_IX                     DISABLED
EMP_MANAGER_IX                 DISABLED
EMP_NAME_IX                    DISABLED

6 rows selected.

SQL>

接下来我们将employees表导出成dump文件。

[oracle@henry ~]$ expdp hr@pdb1 DUMPFILE=hr_employees.dmp DIRECTORY=dp_dir TABLES=EMPLOYEES REUSE_DUMPFILES=YES

Export: Release 21.0.0.0.0 - Production on Sun Dec 27 16:32:22 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@pdb1 DUMPFILE=hr_employees.dmp DIRECTORY=dp_dir TABLES=EMPLOYEES REUSE_DUMPFILES=YES
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  home/oracle/labs/M104786GC10/hr_employees.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 27 16:32:38 2020 elapsed 0 00:00:11

为了向大家演示导入employees表时，使用索引压缩技术，我们先将employees表删除，当然我相信您一定有其他办法在不删除原表的情况下也进行导入。

SQL> DROP TABLE hr.employees CASCADE CONSTRAINTS;

Table dropped.

接下来我们导入这张表，并启动表和索引压缩技术。注意数据泵的参数：

TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\"
TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\"

[oracle@henry ~]$ impdp hr@pdb1 FULL=Y DUMPFILE=hr_employees.dmp DIRECTORY=dp_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT

Import: Release 21.0.0.0.0 - Production on Sun Dec 27 16:36:46 2020
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01":  hr/********@pdb1 FULL=Y DUMPFILE=hr_employees.dmp DIRECTORY=dp_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:"COMPRESS BASIC" TRANSFORM=INDEX_COMPRESSION_CLAUSE:"COMPRESS ADVANCED LOW" EXCLUDE=CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 27 16:36:58 2020 elapsed 0 00:00:06

导入之后，我们再次查询表的压缩属性已经被启用，并设定为basic压缩。我们之前查询这个表有6个索引，其中2个是创建约束时自动创建的索引，4个是手工创建的索引。那么在下面查询索引压缩情况是，为什么只有4个索引启动了压缩，相信大家自己已经有了答案。

SQL> SELECT compression, compress_for FROM DBA_TABLES WHERE table_name='EMPLOYEES';

COMPRESSION          COMPRESS_FOR
-------------------- --------------------
ENABLED              BASIC

SQL> SELECT index_name, compression FROM dba_indexes WHERE table_name='EMPLOYEES' and owner='HR';

INDEX_NAME                     COMPRESSION
------------------------------ --------------------
EMP_DEPARTMENT_IX              ADVANCED LOW
EMP_JOB_IX                     ADVANCED LOW
EMP_MANAGER_IX                 ADVANCED LOW
EMP_EMP_ID_PK                  DISABLED
EMP_NAME_IX                    ADVANCED LOW

今天的内容就到这里。在下一讲，也是本套实验教材的最后一讲当中，将为大家介绍Oracle Database 21c在安全方面的新特性，比如区块链表和表空间加密算法，期待您的关注，谢谢。

文章被以下合辑收录

Oracle Database 21c 专栏（共73篇）

本专栏收录 Oracle 21c相关文章、文档。

Oracle Database 21c新特性讲座（4）——检查数据泵转储文件的有效性、进行模式导出传输表空间元数据、导入时使用索引压缩

文章被以下合辑收录

评论