一、文档说明
1.0 需求：需要将生产环境PICC用户导出，在测试环境中docker 测试数据库导入，只需要表结构；
2.0 思路：根据开发人员提供的需求，数据库源端aix 11.2.0.4, 目标端11.2.0.3
工具：使用expdp进行导出，impdp进行导入，scp进行传输
expdp导出，开并行，只导出元数据，排除JOB等存储过程对象
scp服务器相关权限端口，账户密码索取
impdp导入前，存储空间，用户权限提前准备
二、操作记录
--第一次导入操作failed
--失败原因，导入表创建语法DDL失败，原因
1.导入环境为XE学习环境，数据库容量最大11g，如果空间足够，不会报错
2.导入建表语法，dump文件小于200M，导入建表语句还未插入数据，已占用11G存储，由于表的STORAGE(INITIAL 65536 NEXT 1048576)参数导致，对每个段都分配了空间，导致创建空表占用11g达到XE阀值报错
3 .测试11g新特效，延迟段创建对数据泵导入的对象无效，此参照只能影响手工新建的对象
1）资料获取 数据泵INCLUDE and EXCLUDE对象类型视图 Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects (Doc ID 341733.1) ~database_export_objects /schema_export_objects /table_export_objects 2)进行导出 SQL> create directory dump as '/home/oracle/tools'; expdp \'/ as sysdba\' directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=system EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel --导出元数据，加了并行，但实质只导出一个dump文件,说明导出元数据只能串行导出 --附上导出日志，如果愿意，后缀的导出类型，均可过滤，保留表、索引即可，最小化原则处理需求 Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=system EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel=2 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/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is: /home/oracle/tools/system01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov 29 14:48:14 2018 elapsed 0 00:00 3）scp 省略 4） 导入操作 --docker 命令进行SQL*Plus docker run -d --name "oradb" -v /home/dmuser/my_oracle_data:/u01/app/oracle IP:10082/sath89/oracle-xe-11g --正常环境，登陆服务器端，即可使用impdp工具，本次场景为docker 有些特殊 --创建表空间 --源端用户所在表空间 SQL> select tablespace_name from dba_segments where owner='SYSTEM' group by tablespace_name; TABLESPACE_NAME ------------------------------ SYSAUX SYSTEM--目标端创建表空间 crate tablesapce SYSTEM datafile '/u01/app/oracle/oradata/XE/system.dbf' size 10m uniform size 128k autoextend on next 30m maxsize 10g; sqlplus as sysdba create directories abc as 'xxx'; --导入 impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc.log REMAP_TABLESPACE=LIFELOB_DATA:LIFEDATA_T_L,LIFEINDEX_T_L:LIFEDATA_T_L --导入报错 ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM --对system表空间进行扩容 --再次导入报错 CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE, "NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2(1000 BYTE), "CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 IN ORA-39171: Job is experiencing a resumable wait. ORA-12953: The request exceeds the maximum allowed database size of 11 GB --搜索MOS 提示，学习XE类 Express Edition (简化版本，数据库允许最大11g) ORA-12953: The request exceeds the maximum allowed database size of 11 GB (Doc ID 2414879.1) [Release 11.2 to 12.2] This is an Oracle Database XE environment, and Oracle Database XE has a limitation of 11 GB of user data 疑问？测试库怎么有11g这么大？ SQL> select round( sum(bytes)/1024/1024) m from dba_segments where owner not in('PICCPROD'); M ---------- 1708 --导入用户后 >11g出发XE峰值，报错 SQL> select sum(bytes)/1024/1024 from dba_segments; SUM(BYTES)/1024/1024 -------------------- 11706.8125 --什么类型的对象占用空间 SQL> select sum(bytes/1024/1024) ,segment_type from dba_segments where owner='PICCPROD' group by owner,segment_type; SUM(BYTES/1024/1024) SEGMENT_TYPE -------------------- ------------------ 9999 TABLE ？dump文件大小182，导入后，表大小9G oracle@c46bf408bfad:~/dmpfile$ ls PICCPROD01.dmp picc.log oracle@c46bf408bfad:~/dmpfile$ du -sm PICCPROD01.dmp 182 PICCPROD01.dmpfile 查询表数据： select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='PICCPROD' and segment_type='TABLE' and rownum=1; SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 ------------------------------ ------------------ --------------- T_DIAGNOSIS_TYPE TABLE .5 select count(*) from PICCPROD.T_DIAGNOSIS_TYPE; COUNT(*) ---------- 0 查询表的区的分布 SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE'; TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024 BLOCKS --------------- -------------------- ---------- ---------- ---------- ---------- LIFEDATA_T_L T_DIAGNOSIS_TYPE 0 128 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 1 144 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 160 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 176 128 16 尝试对表move（本次不再次对数据文件resize 进行分析，如果需要resize减少数据文件，需要从数据文件max_block_id 进行move，随后对数据文件进行resize） alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L; * ERROR at line 1: ORA-12953: The request exceeds the maximum allowed database size of 11 GB *Move时，需要存在一倍对象空闲空间，此时空闲空间不足无法Move* *释放空间，找出max最大对象 select segment_name,sum(blocks) from dba_extents where owner='PICCPROD' group by segment_name order by 2; SEGMENT_NAME SUM(BLOCKS) ------------------------------------------- T_POLICY_AUTO 221312 T_POLICY_PRODUCT 309120 drop table PICCPROD.T_POLICY_AUTO purge; drop table PICCPROD.T_POLICY_PRODUCT purge; alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L; select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE'; TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024 BLOCKS -------------------- -------------------- ---------- ---------- ---------- ---------- LIFEDATA_T_L T_DIAGNOSIS_TYPE 0 34720 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 1 34736 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 34752 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 34768 128 16 *尝试对数据文件进行resize* select file_id,bytes/1024/1024 from dba_data_files where tablespace_name='LIFEDATA_T_L'; FILE_ID BYTES/1024/1024 ---------- --------------- 5 10000 alter database datafile 5 resize 5000M; * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value --虽然数据为空，但是表对应的段，分配了存储，占用了存储 *truncate table SQL> truncate table PICCPROD.T_DIAGNOSIS_TYPE; Table truncated. SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE'; TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024 BLOCKS -------------------- -------------------- ---------- ---------- ---------- ---------- LIFEDATA_T_L T_DIAGNOSIS_TYPE 0 34720 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 1 34736 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 34752 128 16 LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 34768 128 16 *数据库参数，延迟段创建 SQL> show parameter defer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE --参数并未关闭--false，但是之前的导入还是创建表，因此延迟段创建参数，对于数据泵导入的对象是不起作用的 *查询建表语句 --导入 impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc%U.log sqlfile=sql01.sql ORA-39002: invalid operation ORA-31694: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unload ORA-02354: error in exporting/importing data ORA-39776: fatal Direct Path API error loading table "SYS"."SYS_SQL_FILE_FULL_01" ORA-12953: The request exceeds the maximum allowed database size of 11 GB -- CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE, "NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2(1000 BYTE), "CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 IN ORA-39171: Job is experiencing a resumable wait. ORA-12953: The request exceeds the maximum allowed database size of 11 GB 为何impdp，提取ddl语句也报错，因为数据泵导入会在数据库内，创建相关对象，占用临时存储，数据库都满了，因此即使不导入数据，临时创建个对象都是报错的
--第二次操作，successful
总结：第一次导入失败，主要原因是，数据泵导入后，分配段初始化空间，避免导入分配第一个extent，即避免现象
impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc.log REMAP_TABLESPACE=LIFELOB_DATA:LIFEDATA_T_L,LIFEINDEX_T_L:LIFEDATA_T_L
TRANSFORM = SEGMENT_ATTRIBUTES:N三、问题反思TRANSFORM = SEGMENT_ATTRIBUTES:N 参数作用何在，有什么特性 实验测试： 1.创建一个测试表a, 创建主键，创建一个索引 2.对对象进行元数据导出 3.实验参数，进行导入 4.查询及验证表及索引是否存在 5.查询表及索引的段是否分配 6.impdp sqlfile 查询与表 and 索引 get ddl 差异
1.创建一个测试表a, 创建主键，创建一个索引 SQL> create table yang.ceshi as select * from scott.emp; SQL> alter table yang.ceshi add constraint pk_id primary key(empno); SQL> create index yang.id2 on yang.ceshi(deptno); 2.对对象进行元数据导出 expdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log SCHEMAS=yang include=TABLE:"IN('CESHI')" CONTENT=METADATA_ONLY 3.实验参数，进行导入 impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott 4.查询及验证表及索引是否存在 select object_name,object_type,status from user_objects where created>sysdate-1 OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- ID2 INDEX VALID PK_ID INDEX VALID CESHI TABLE VALID 5.查询表及索引的段是否分配 SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1); no rows selected 6.impdp sqlfile 查询与表 and 索引 get ddl 差异 [oracle@t2 tools]$ impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott sqlfile=test_table.sql --查询创建语法，进行说明： 第一部分 alter session level trace文件 第二部分，创建表语法，没有了storage语法 第三部分，创建索引，对索引取消并行，在真实导入数据过程中，parallel并行能帮助快速创建索引，创建索引后数据泵自动noparallel 第四部分，导入完毕后，对数据泵临时创建对象进行清理 [oracle@t2 tools]$ more test_table.sql -- CONNECT SYS ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/TABLE/TABLE CREATE TABLE "SCOTT"."CESHI" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) ; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT SCOTT CREATE UNIQUE INDEX "SCOTT"."PK_ID" ON "SCOTT"."CESHI" ("EMPNO") ; ALTER INDEX "SCOTT"."PK_ID" NOPARALLEL; CREATE INDEX "SCOTT"."ID2" ON "SCOTT"."CESHI" ("DEPTNO") ; ALTER INDEX "SCOTT"."ID2" NOPARALLEL; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYS ALTER TABLE "SCOTT"."CESHI" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO") USING INDEX "SCOTT"."PK_ID" ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,: 2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_ID'; i_o := 'SCOTT'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:17:22',df),NV; DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END; / DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,: 2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; i_n := 'ID2'; i_o := 'SCOTT'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,3,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:18:08',df),NV; DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END; / ---查询表的ddl语法 select dbms_metadata.get_ddl('TABLE','CESHI','SCOTT') ddl_text from dual DDL_TEXT -------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."CESHI" ( "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_ID" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "USERS" ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" --表不存在storage属性后 insert 数据进行测试 SQL> insert into scott.ceshi select * from scott.emp; --查询表及索引的extent分配，发现表其实并没有extent storage属性，是怎么分配的呢？ SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT/1024 INITIAL_EXTENT_K,NEXT_EXTENT/1024 NEXT_EXTENT_K from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1); SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS INITIAL_EXTENT_K NEXT_EXTENT_K --------------- ------------------ ---------- ---------- ---------------- ------------- CESHI TABLE 8 1 64 1024 PK_ID INDEX 8 1 64 1024 ID2 INDEX 8 1 64 1024 --查询表空间属性 SQL> select tablespace_name from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1) group by tablespace_name; TABLESPACE_NAME ------------------------------ USERS SQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT/1024 INITIAL_EXTENT_K,NEXT_EXTENT/1024 NEXT_EXTENT_K,SEGMENT_SPACE_MANAGEMENT,ALLOCATION_TYPE,EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name='USERS'; TABLESPACE BLOCK_SIZE INITIAL_EXTENT_K NEXT_EXTENT_K SEGMEN ALLOCATIO EXTENT_MAN ---------- ---------- ---------------- ------------- ------ --------- ---------- USERS 8192 64 AUTO SYSTEM LOCAL --表空间本地管理表空间，系统自动扩展分配， 8K,1M,8M,64M --可见，对于表来说，如果未配置storage属性，则表使用表空间的默认storage属性分配extents 大小
四、问题总结
1.根据问题现象，寻找解决方法，路不止一条，在能接受的范围内解决即可
2.根据问题现象，寻找合适的技术点，使用自己能运用且无风险的方案解决问题，测试后方可使用