expdp与impdp迁移数据---通过逻辑导出导入Schema我们需要做哪些准备工作

2020-08-12 00:00:06

今天下班有点晚，因为工作的一个失误，看错数据库，还好操作错误提示可以避免进一步犯错，也没有对生产数据和用户做任何操作。虽然被领导批评，还是庆幸没有动生成数据，也没有影响业务用户。似乎成了魔咒，每个DBA都要犯几次错误，好吧。我们暂且放下，来讨论数据泵迁移为。

需求是将一个用户迁移到另一个数据库，也就是迁移一个schema.我们在学习Oracle的数据库时，对schema的定义是数据库对象的集合，这个集合包括：表，索引，约束，触发器，存储过程，视图等对象，也就我们通过dba_obects可以查到的对象类型，下面我们查询看一下：

SYS@orcl1>select distinct object_type from dba_objects;

OBJECT_TYPE
-------------------
EDITION
INDEX PARTITION
TABLE SUBPARTITION
CONSUMER GROUP
SEQUENCE
TABLE PARTITION
SCHEDULE
QUEUE
RULE
PROCEDURE

.....

在我的11.2.0.4版本数据库中，有37中对象类型，下面我们查询一个用户和索引的定义

DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

这是一个索引的定义，我们看到的是一个定义语句，其实对于这个对象，Oracle在逻辑导出时，就是将这些语句导出，然后根据对象定义顺序依次再导入数据库，执行这些语句。

下面是表的定义

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"??MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

表这个表定义很复杂，其实有很多参数是默认行为，这是Oracle在导出整个对象定义时，要完全导出，那么我们用如下语句导出一个用户的数据

[oracle@p1]$expdp 'userid="/ as sysdba"' directory=DIR schemas=scott dumpfile=scott%U.dmp parallel=8;

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_04": userid="/******** AS SYSDBA" directory=DMPDIR schemas=pay dumpfile=pay%U.dmp parallel=8
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
. . exported "SCOTT"."EMP1" 2.124 GB 5936487 rows

...

注意，这一部分就是在导出用户定义，权限，对象定义如触发器，存储过程，索引，约束，序列号以及相关对的统计信息

那么如果我们要讲该用户导入另个一数据库，我的问题是我们需要做哪些工作，比如需要提前建用户吗，需要提前给用户根据生产库的权限授权吗，需要提前建好表空间？

其实，从上面的逻辑定义就知道，我们不必给出用户定义，也没有必要比对源库做授权，我们需要的仅仅是创建同名的表空间，保证这个空间足够即可（生产库往往需要某个用户对应一个具体表空间）其他信息逻辑导出的文件中都已经包含

在提前创建了足够表空间大小的前提下，我们使用如下方式导入数据。

[oracle@w]$impdp 'userid="/ as sysdba"' directory=DIR schemas=scott dumpfile=scott%U.dmp parallel=8;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": userid="/******** AS SYSDBA" directory=DIR schemas=scott dumpfile=scott%U.dmp parallel=8
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1" 0 KB 0 rows

.......

这里继续导入表数据

下面是创建对象对象，这个过程有点慢（相比前面操作）

Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/JOB
我们在早导入导出中，使用了并行，这是我们必须指定dumpfile=scott%U.dmp 否则不会开启并行导出，导入时对使用dumpfile=scott%U.dmp 开启并行。

导入完毕，我们还需要验证是否有对象，比如存储过程失效，此时需要重新编译，这里就不演示了,

select object_name,object_type,status from dba_objects where owner='SCOTT';

比如存储过程失效可以如下编译即可

alter procedure p1 compile

总结：数据泵迁移一个用户，目标库只需要创建表空间，且大小足够即可，为了加速迁移可以通过并行解决，同时对于小文件网络传输效率更高，我就遇到通过SecureFX传输大文件速度极慢

后改并行使用小文件。

看看时间已经11：59 赶紧发布今天的文章吧