Oracle数据泵expdp/impdp备份还原步骤
·
个人学习记录
1 测试环境准备
在docker环境中操作，安装记录于：docker中安装Oracle 11g_oracle11gdocker镜像怎么下载-CSDN博客
安装完成后创建表空间记录于：Oracle创建表空间-CSDN博客
使用navicat导入了两张测试表数据
2 创建备份目录
[oracle@adbc580880e4 /]$ mkdir /data/oracle/bak
3 创建逻辑目录并赋权
[oracle@adbc580880e4 /]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 22 13:12:16 2024
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create directory data_dir as '/data/oracle/bak';
Directory created.
查看管理员目录是否存在：
参考博客上的命令为SQL> select * from dba_direcories; 运行报错不存在，改成directories
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_DIR
/data/oracle/bak
SYS SUBDIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/demo/schema/order_entry//2002/Se
p
SYS SS_OE_XMLDIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/demo/schema/order_entry/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS LOG_FILE_DIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/demo/schema/log/
SYS DATA_FILE_DIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/demo/schema/sales_history/
SYS XMLDIR
/ade/b/2125410156/oracle/rdbms/xml
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS MEDIA_DIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/demo/schema/product_media/
SYS DATA_PUMP_DIR
/home/oracle/app/oracle/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR
/home/oracle/app/oracle/product/11.2.0/dbhome_2/ccr/state
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
9 rows selected.
赋权给备份用户备份目录的操作权限
SQL> grant read,write on directory data_dir to helowin;
Grant succeeded.
使用sys用户不用进行这一步
4 导出数据
退出sql命令行，进入操作系统命令行操作
命令解释：
user/password@实例名：用户名和密码，这里也可以不写，直接输入命令，最后提示输入用户名的时候，直接输入 / as sysdba
directory：指定导出的目录，步骤3创建的逻辑目录
dumpfile：指定导出的文件名，存放于directory目录里
schemas：指定需要导出的数据库用户名
exclude：导出时排除特定的对象类型
tables：指定需要导出的表
logfile：指定日志文件名
parallel：指定并行导出线程的数量
cluster：是否采用多实例导出
tablespaces：指定要导出的表空间
4.1 导出整个数据库
[oracle@adbc580880e4 /]$ expdp helowin/helowin@helowin DIRECTORY=data_dir DUMPFILE=full_bak.dmp logfile=full_bak.log FULL=y;
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 14:08:46 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HELOWIN"."SYS_EXPORT_FULL_01": helowin/********@helowin DIRECTORY=data_dir DUMPFILE=full_bak.dmp logfile=full_bak.log FULL=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 140.7 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
...
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "HELOWIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HELOWIN.SYS_EXPORT_FULL_01 is:
/data/oracle/bak/full_bak.dmp
Job "HELOWIN"."SYS_EXPORT_FULL_01" successfully completed at 14:11:16
4.2 schemas按用户导出
[oracle@adbc580880e4 /]$ expdp helowin/helowin@helowin DIRECTORY=data_dir DUMPFILE=user_bak.dmp logfile=user_bak.log schemas=helowin;
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 14:21:41 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HELOWIN"."SYS_EXPORT_SCHEMA_01": helowin/********@helowin DIRECTORY=data_dir DUMPFILE=user_bak.dmp logfile=user_bak.log schemas=helowin
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
Processing object type SCHEMA_EXPORT/USER
...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HELOWIN"."dy_dslp" 8.016 MB 19859 rows
. . exported "HELOWIN"."T_AP_PAYABLE_VH" 780.1 KB 11956 rows
Master table "HELOWIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HELOWIN.SYS_EXPORT_SCHEMA_01 is:
/data/oracle/bak/user_bak.dmp
Job "HELOWIN"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:21:57
4.3 按表空间导出
[oracle@adbc580880e4 /]$ expdp helowin/helowin@helowin DIRECTORY=data_dir DUMPFILE=tablespace_bak.dmp logfile=tablespace_bak.log TABLESPACES=helowin,system;
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 14:39:43 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HELOWIN"."SYS_EXPORT_TABLESPACE_01": helowin/********@helowin DIRECTORY=data_dir DUMPFILE=tablespace_bak.dmp logfile=tablespace_bak.log TABLESPACES=helowin,system
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.31 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
...
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "HELOWIN"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HELOWIN.SYS_EXPORT_TABLESPACE_01 is:
/data/oracle/bak/tablespace_bak.dmp
Job "HELOWIN"."SYS_EXPORT_TABLESPACE_01" successfully completed at 14:39:55
4.4 按表导出-失败
报错失败
[oracle@adbc580880e4 /]$ expdp helowin/helowin@helowin DIRECTORY=data_dir DUMPFILE=table_bak.dmp logfile=table_bak.log TABLES=dy_dslp;
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 15:00:42 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HELOWIN"."SYS_EXPORT_TABLE_01": helowin/********@helowin DIRECTORY=data_dir DUMPFILE=table_bak.dmp logfile=table_bak.log TABLES=dy_dslp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object HELOWIN.DY_DSLP was not found.
ORA-31655: no data or metadata objects selected for job
Job "HELOWIN"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:00:434.5 并行操作导出整个数据库
可以通过 PARALLEL 参数为导出使用一个以上的线程来显著地加速作业。每个线程创建一个单独的转储文件，因此参数 dumpfile 应当拥有和并行度一样多的项目。可以指定通配符作为文件名，如：dumpfile=expCASES_%U.dmp "%U"表示自动生成递增的序列号。
并行方式只有在表的数量多于并行值并且表很大时才是有效的。
[oracle@adbc580880e4 /]$ expdp helowin/helowin@helowin DIRECTORY=data_dir DUMPFILE=full_pa_%U.dmp logfile=full_pa.log FULL=y parallel=4;
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 15:12:35 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HELOWIN"."SYS_EXPORT_FULL_01": helowin/********@helowin DIRECTORY=data_dir DUMPFILE=full_pa_%U.dmp logfile=full_pa.log FULL=y parallel=4
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 140.7 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
...
Processing object type DATABASE_EXPORT/AUDIT
Master table "HELOWIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HELOWIN.SYS_EXPORT_FULL_01 is:
/data/oracle/bak/full_pa_01.dmp
/data/oracle/bak/full_pa_02.dmp
/data/oracle/bak/full_pa_03.dmp
/data/oracle/bak/full_pa_04.dmp
Job "HELOWIN"."SYS_EXPORT_FULL_01" successfully completed at 15:15:12
5 导入还原准备
5.1还原环境准备
docker重新安装一个数据库用于还原测试：docker中安装Oracle 11g_docker 安装 oracle11g-CSDN博客
docker run --privileged -d --restart=always -v /home/oracle:/data/oracle -p 1522:1521 --name oracle_test akaiot/oracle_11g测试环境映射主机端口改成了1522，挂载磁盘路径和导出环境一致，再配置环境变量
5.2 创建备份目录
同步骤2，因为容器两次挂载目录一致，所以无需再次创建
5.3 创建逻辑目录
同步骤3，因为创建用户暂不赋权
SQL> create directory data_dir as '/data/oracle/bak';
Directory created.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_DIR
/data/oracle/bak
6 导入还原数据
6.1 导入整个数据库
报错ORA-01119
[oracle@82c89589c940 oracle]$ impdp system/helowin@helowin DIRECTORY=data_dir DUMPFILE=full_bak.dmp logfile=impfull_bak.log FULL=y table_exists_action=replace;
Import: Release 11.2.0.1.0 - Production on Fri Nov 22 16:43:46 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@helowin DIRECTORY=data_dir DUMPFILE=full_bak.dmp logfile=impfull_bak.log FULL=y table_exists_action=replace
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
...
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/data/oracle/helowin/helowin_temp.dbf'
ORA-27038: created file already exists
Additional information: 1
Failing sql is:
CREATE TEMPORARY TABLESPACE "HELOWIN_TEMP" TEMPFILE '/data/oracle/helowin/helowin_temp.dbf' SIZE 52428800 AUTOEXTEND ON NEXT 52428800 MAXSIZE 20480M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
原因是我这个测试的容器挂载磁盘目录和导出的一样，同名的dbf文件已存在，将原来的两个dbf文件重命名
（为了保证干净的测试环境，我这里删掉现在的测试docker重新再装了一个）
重复步骤5.3再进入测试环境导入：还是报错ORA-39083和ORA-29516
ORA-39083: Object type PROC_SYSTEM_GRANT failed to create with error:
ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3311
Bulk load of method java/lang/Object.<init> failed; insufficient shm-object space
Failing sql is:
BEGIN
DECLARE
TJP DBMS_JVM_EXP_PERMS.TEMP_JAVA_POLICY;
CURSOR C1 IS SELECT KIND,GRANTEE,TYPE_SCHEMA,TYPE_NAME,
NAME,ACTION,ENABLED FROM TEMP_JAVA_PRIVS;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO TJP;
CLOSE C1;
DBMS_JVM_EXP_PERMS.I
查询后执行
SQL> alter system set java_jit_enabled=false;
System altered报错
ORA-39083: Object type XMLSCHEMA failed to create with error:
ORA-31085: schema "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" already registered
Failing sql is:
BEGIN dbms_xmlschema.registerSchema(:1, :2, (:3 = 1), FALSE,FALSE,FALSE,FALSE, :4, options=> :5, schemaoid => :6, import_options => :7); END;
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE:"IX"."AQ$_STREAMS_QUEUE_TABLE_T"]
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
0xb1cc8a40 19028 package body SYS.KUPW$WORKER
0xb1cc8a40 8191 package body SYS.KUPW$WORKER
0xb1cc8a40 14991 package body SYS.KUPW$WORKER
0xb1cc8a40 15795 package body SYS.KUPW$WORKER
0xb1cc8a40 15433 package body SYS.KUPW$WORKER
0xb1cc8a40 3944 package body SYS.KUPW$WORKER
0xb1cc8a40 8874 package body SYS.KUPW$WORKER
0xb1cc8a40 1651 package body SYS.KUPW$WORKER
0xbe5153e8 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE:"IX"."AQ$_STREAMS_QUEUE_TABLE_T"]
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
0xb1cc8a40 19028 package body SYS.KUPW$WORKER
0xb1cc8a40 8191 package body SYS.KUPW$WORKER
0xb1cc8a40 14991 package body SYS.KUPW$WORKER
0xb1cc8a40 15795 package body SYS.KUPW$WORKER
0xb1cc8a40 15433 package body SYS.KUPW$WORKER
0xb1cc8a40 3944 package body SYS.KUPW$WORKER
0xb1cc8a40 8874 package body SYS.KUPW$WORKER
0xb1cc8a40 1651 package body SYS.KUPW$WORKER
0xbe5153e8 2 anonymous block
Job "SYSTEM"."SYS_IMPORT_FULL_02" stopped due to fatal error at 11:30:36
尝试解决：
Oracle11g impdp全库导入报错ORA-39126: Worker unexpected fatal error in-CSDN博客
再次重装测试容器后执行上面两个报错的处理步骤后，再次导入
[oracle@a4245157443b /]$ impdp system/helowin@helowin DIRECTORY=data_dir DUMPFILE=full_bak.dmp logfile=impfull_bak.log FULL=y table_exists_action=truncate;
登陆一会的结果：
...
BEGIN SYS.DBMS_IJOB.SUBMIT( JOB=> 3, LUSER=> 'SYS', PUSER=> 'SYSMAN', CUSER=> 'SYSMAN', NEXT_DATE=> TO_DATE('2024-11-22 14:11:54', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'sysdate + 1 / (24 * 60)', BROKEN=> FALSE, WHAT=> 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
ORA-31684: Object type DIMENSION:"SH"."CUSTOMERS_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."PRODUCTS_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."TIMES_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."CHANNELS_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."PROMOTIONS_DIM" already exists
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 8078 error(s) at 14:43:41最后虽然导入完成，但是还有大量报错，从navicat上使用导出环境的helowin账号密码进行连接，成功
再查询表空间及对应的表，也都有数据，报错这样不处理后续有无问题不知。
6.2 schema按用户导入
重装测试容器（步骤5.1），重复步骤3创建逻辑目录
6.2.1 同名用户导入
创建和导出环境一样的用户，赋权dba角色和逻辑目录
SQL> create directory data_dir as '/data/oracle/bak';
Directory created.
SQL> create user helowin identified by helowin;
User created.
SQL> grant dba to helowin;
Grant succeeded.
SQL> grant read,write on directory data_dir to helowin;
Grant succeeded.
尝试导入报错ORA-00959: tablespace 'HELOWIN' does not exist，表空间不存在
[oracle@230ce4e60dce ~]$ impdp helowin/helowin schemas=helowin directory=data_dir dumpfile=user_bak.dmp logfile=impdp2.log;
Import: Release 11.2.0.1.0 - Production on Wed Nov 27 14:25:37 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HELOWIN"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "HELOWIN"."SYS_IMPORT_SCHEMA_01": helowin/******** schemas=helowin directory=data_dir dumpfile=user_bak.dmp logfile=impdp2.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HELOWIN" already exists
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/TABLE/TABLE
ORA-39083: Object type TABLE:"HELOWIN"."T_AP_PAYABLE_VH" failed to create with error:
ORA-00959: tablespace 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."T_AP_PAYABLE_VH" ("FBIZVOUCHER" NUMBER, "FBIZVOUCHER1" NUMBER, "FID" NUMBER, "FBOOKID" NUMBER, "FACCTSYSTEM" NUMBER, "FGLVOUCHERI" NUMBER, "FVOUCHERGRO" NUMBER, "FVOUCHERGRO1" VARCHAR2(254 BYTE), "FVOUCHERBIL" VARCHAR2(254 BYTE), "FVOUCHERYEA" VARCHAR2(254 BYTE), "FVOUCHERCRE" NUMBER, "FVOUCHERDAT" DATE) SEGMENT CREATION IMMEDIATE PCTF
ORA-39083: Object type TABLE:"HELOWIN"."dy_dslp" failed to create with error:
ORA-00959: tablespace 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."dy_dslp" ("id" NUMBER(20,0) NOT NULL ENABLE, "platform_name" VARCHAR2(255 BYTE), "store_name" VARCHAR2(255 BYTE), "commodity_id" VARCHAR2(255 BYTE), "commodity_link" VARCHAR2(255 BYTE), "merchandis" VARCHAR2(255 BYTE), "mercha" VARCHAR2(255 BYTE), "product_title" VARCHAR2(255 BYTE), "number1" VARCHAR2(255 BYTE), "number2" VARCHAR2(255 BYTE), "
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Job "HELOWIN"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at 14:25:41
再去创建表空间：Oracle创建表空间-CSDN博客
关联表空间
SQL> alter user helowin default TABLESPACE helowin TEMPORARY TABLESPACE helowin_temp;
User altered.再次导入成功
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HELOWIN"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "HELOWIN"."SYS_IMPORT_SCHEMA_01": helowin/******** schemas=helowin directory=data_dir dumpfile=user_bak.dmp logfile=impdp2.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HELOWIN" already exists
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HELOWIN"."dy_dslp" 8.016 MB 19859 rows
. . imported "HELOWIN"."T_AP_PAYABLE_VH" 780.1 KB 11956 rows
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
Job "HELOWIN"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 15:06:28通过navicat连接能看到测试的两张表数据。
6.2.2 不同表空间、不同用户导入
重建新的测试环境
创建表空间AA、临时表空间AA_temp和用户AA并授权
SQL> CREATE TEMPORARY TABLESPACE AA_temp TEMPFILE '/data/oracle/helowin/AA_temp.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> CREATE TABLESPACE AA LOGGING DATAFILE '/data/oracle/helowin/AA.DBF' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> CREATE USER AA IDENTIFIED BY helowin DEFAULT TABLESPACE AA TEMPORARY TABLESPACE AA_temp;
User created.
SQL> grant dba to AA;
Grant succeeded.
参照步骤3创建逻辑目录并授权给用户AA
SQL> create directory data_dir as '/data/oracle/bak';
Directory created.
SQL> grant read,write on directory data_dir to AA;
Grant succeeded.将表空间HELOWIN导入到表空间AA，用户helowin的数据导入到用户AA
impdp参数：
REMAP_SCHEMA 将一个方案中的对象加载到另一个方案
REMAP_TABLE 表名重新映射到另一个表
REMAP_TABLESPACE 将表空间对象重新映射到另一个表空间
REUSE_DATAFILES 如果表空间已存在, 则将其初始化 (N)
TRANSFORM 要应用于适用对象的元数据转换
（这里我用的全库备份的文件进行恢复）
impdp AA/helowin remap_tablespace=HELOWIN:AA remap_schema=helowin:AA transform=oid:n directory=data_dir dumpfile=full_bak.dmp logfile=impdp3.log结果成功但是有很多报错
ORA-31684: Object type DIMENSION:"SH"."PRODUCTS_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."TIMES_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."CHANNELS_DIM" already exists
ORA-31684: Object type DIMENSION:"SH"."PROMOTIONS_DIM" already exists
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "AA"."SYS_IMPORT_FULL_01" completed with 8071 error(s) at 16:08:57
使用AA的账号密码用navicat查看，发现测试表数据已经导入到AA
6.3 按表空间导入
重复步骤5.1 5.2 5.3
步骤4.3导出的helowin和system两个表空间，这里导入helowin
[oracle@640424617bfe ~]$ impdp system/helowin directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=helowin;
Import: Release 11.2.0.1.0 - Production on Thu Nov 28 16:25:58 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=HELOWIN
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"HELOWIN"."T_AP_PAYABLE_VH" failed to create with error:
ORA-01918: user 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."T_AP_PAYABLE_VH" ("FBIZVOUCHER" NUMBER, "FBIZVOUCHER1" NUMBER, "FID" NUMBER, "FBOOKID" NUMBER, "FACCTSYSTEM" NUMBER, "FGLVOUCHERI" NUMBER, "FVOUCHERGRO" NUMBER, "FVOUCHERGRO1" VARCHAR2(254 BYTE), "FVOUCHERBIL" VARCHAR2(254 BYTE), "FVOUCHERYEA" VARCHAR2(254 BYTE), "FVOUCHERCRE" NUMBER, "FVOUCHERDAT" DATE) SEGMENT CREATION IMMEDIATE PCTFREE 10
ORA-39083: Object type TABLE:"HELOWIN"."dy_dslp" failed to create with error:
ORA-01918: user 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."dy_dslp" ("id" NUMBER(20,0) NOT NULL ENABLE, "platform_name" VARCHAR2(255 BYTE), "store_name" VARCHAR2(255 BYTE), "commodity_id" VARCHAR2(255 BYTE), "commodity_link" VARCHAR2(255 BYTE), "merchandis" VARCHAR2(255 BYTE), "mercha" VARCHAR2(255 BYTE), "product_title" VARCHAR2(255 BYTE), "number1" VARCHAR2(255 BYTE), "number2" VARCHAR2(255 BYTE), "number
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" completed with 2 error(s) at 16:26:02
执行报错，需要先新建用户helowin，重复步骤6.2.1新建同名用户并赋权，然后用新建的helowin账号导入
[oracle@640424617bfe ~]$ impdp helowin/helowin directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=helowin;
Import: Release 11.2.0.1.0 - Production on Thu Nov 28 16:35:02 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HELOWIN"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "HELOWIN"."SYS_IMPORT_TABLESPACE_01": helowin/******** directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=helowin
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"HELOWIN"."T_AP_PAYABLE_VH" failed to create with error:
ORA-00959: tablespace 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."T_AP_PAYABLE_VH" ("FBIZVOUCHER" NUMBER, "FBIZVOUCHER1" NUMBER, "FID" NUMBER, "FBOOKID" NUMBER, "FACCTSYSTEM" NUMBER, "FGLVOUCHERI" NUMBER, "FVOUCHERGRO" NUMBER, "FVOUCHERGRO1" VARCHAR2(254 BYTE), "FVOUCHERBIL" VARCHAR2(254 BYTE), "FVOUCHERYEA" VARCHAR2(254 BYTE), "FVOUCHERCRE" NUMBER, "FVOUCHERDAT" DATE) SEGMENT CREATION IMMEDIATE PCTF
ORA-39083: Object type TABLE:"HELOWIN"."dy_dslp" failed to create with error:
ORA-00959: tablespace 'HELOWIN' does not exist
Failing sql is:
CREATE TABLE "HELOWIN"."dy_dslp" ("id" NUMBER(20,0) NOT NULL ENABLE, "platform_name" VARCHAR2(255 BYTE), "store_name" VARCHAR2(255 BYTE), "commodity_id" VARCHAR2(255 BYTE), "commodity_link" VARCHAR2(255 BYTE), "merchandis" VARCHAR2(255 BYTE), "mercha" VARCHAR2(255 BYTE), "product_title" VARCHAR2(255 BYTE), "number1" VARCHAR2(255 BYTE), "number2" VARCHAR2(255 BYTE), "
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HELOWIN"."SYS_IMPORT_TABLESPACE_01" completed with 2 error(s) at 16:35:04
报错HELOWIN表空间不存在
新建表空间（这里只新建了表空间，没有临时表空间，也没和用户做关联），然后导入
[oracle@640424617bfe ~]$ impdp helowin/helowin directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=helowin;
Import: Release 11.2.0.1.0 - Production on Thu Nov 28 16:42:29 2024
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HELOWIN"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "HELOWIN"."SYS_IMPORT_TABLESPACE_01": helowin/******** directory=data_dir dumpfile=tablespace_bak.dmp logfile=impdp4.log tablespaces=helowin
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HELOWIN"."dy_dslp" 8.016 MB 19859 rows
. . imported "HELOWIN"."T_AP_PAYABLE_VH" 780.1 KB 11956 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HELOWIN"."SYS_IMPORT_TABLESPACE_01" successfully completed at 16:42:31导入成功，navicat可以看到测试数据。
导入测试结果：按用户和表空间导入都需要先新建用户和表空间，如新建的用户和表空间名称不一致需要使用remap参数
6.4 按表导入
遇到这类情况较少，不进行测试
6.5 并行导入测试
重置测试环境，参考步骤6.1先解决报错的问题
SQL> alter system set java_jit_enabled=false;
System altered.
步骤4.5并行导出的是4个文件
用这4个文件进行恢复
impdp system/helowin@helowin DIRECTORY=data_dir DUMPFILE=full_pa_%U.dmp logfile=impfull_bak.log FULL=y table_exists_action=truncate parallel=4;
导入速度很慢，差不多20分钟导入完成
使用navicat连接查看测试数据也导入成功
expdp参数说明：
参考：https://www.cnblogs.com/oracle-dba/p/3344230.html
关键字 说明 (默认)
---------------------------------------------------------------------------ATTACH 连接到现有作业, 例如 ATTACH [=作业名]。
COMPRESSION 减小转储文件内容的大小, 其中有效关键字 值为: ALL, (METADATA_ONLY), DATA_ONLY 和 NONE。
CONTENT 指定要卸载的数据, 其中有效关键字 值为: (ALL), DATA_ONLY 和 METADATA_ONLY。 --默认值为ALL
DATA_OPTIONS 数据层标记, 其中唯一有效的值为: 使用CLOB格式的 XML_CLOBS-write XML 数据类型
DIRECTORY 供转储文件和日志文件使用的目录对象。
DUMPFILE 目标转储文件 (expdat.dmp) 的列表,例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.d
ENCRYPTION 加密部分或全部转储文件, 其中有效关键字值为: ALL, DATA_ONLY, METADATA_ONLY,ENCRYPTED_COLUMNS_ONLY 或 NONE。
ENCRYPTION_ALGORITHM 指定应如何完成加密, 其中有效关键字值为: (AES128), AES192 和 AES256。
ENCRYPTION_MODE 生成加密密钥的方法, 其中有效关键字值为: DUAL, PASSWORD 和 (TRANSPARENT)。
ENCRYPTION_PASSWORD 用于创建加密列数据的口令关键字。
ESTIMATE 计算作业估计值, 其中有效关键字值为: (BLOCKS) 和 STATISTICS。
ESTIMATE_ONLY 在不执行导出的情况下计算作业估计值。
EXCLUDE 排除特定的对象类型, 例如 EXCLUDE=TABLE:EMP。--EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]FILESIZE 以字节为单位指定每个转储文件的大小。
FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCN。 --指定导出特定SCN时刻的表数据FLASHBACK_TIME 用于获取最接近指定时间的 SCN 的时间。--指定导出特定时间点的表数据，注意FLASHBACK_SCN和FLASHBACK_TIME不能同时使用FULL导出整个数据库 (N)。
HELP 显示帮助消息 (N)。
INCLUDE 包括特定的对象类型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME 要创建的导出作业的名称。
LOGFILE 日志文件名 (export.log)。
NETWORK_LINK 链接到源系统的远程数据库的名称。
NOLOGFILE 不写入日志文件 (N)。
PARALLEL 更改当前作业的活动 worker 的数目。
PARFILE 指定参数文件。
QUERY 用于导出表的子集的谓词子句。--QUERY = [schema.][table_name:] query_clauseREMAP_DATA 指定数据转换函数,例如 REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
REUSE_DUMPFILES 覆盖目标转储文件 (如果文件存在) (N)。
SAMPLE 要导出的数据的百分比;
SCHEMAS 要导出的方案的列表 (登录方案)。
STATUS 在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。
TABLES 标识要导出的表的列表 - 只有一个方案。--[schema_name.]table_name[:partition_name][,…]TABLESPACES 标识要导出的表空间的列表。
TRANSPORTABLE 指定是否可以使用可传输方法, 其中有效关键字值为: ALWAYS, (NEVER)。
TRANSPORT_FULL_CHECK 验证所有表的存储段 (N)。
TRANSPORT_TABLESPACES 要从中卸载元数据的表空间的列表。
VERSION 要导出的对象的版本, 其中有效关键字为:(COMPATIBLE), LATEST 或任何有效的数据库版本。
下列命令在交互模式下有效。
注: 允许使用缩写
命令 说明
---------------------------------------------------------------------------
ADD_FILE 向转储文件集中添加转储文件。
CONTINUE_CLIENT 返回到记录模式。如果处于空闲状态, 将重新启动作业。
EXIT_CLIENT 退出客户机会话并使作业处于运行状态。
FILESIZE 后续 ADD_FILE 命令的默认文件大小 (字节)。
HELP 总结交互命令。
KILL_JOB 分离和删除作业。
PARALLEL 更改当前作业的活动 worker 的数目。PARALLEL=<worker 的数目>。 REUSE_DUMPFILES 覆盖目标转储文件 (如果文件存在) (N)。
START_JOB 启动/恢复当前作业。
STATUS 在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。STATUS[=interval]
STOP_JOB 顺序关闭执行的作业并退出客户机。STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。
备注：()括号括起来的代表是expdp命令的默认选项
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
22
0- 0
已为社区贡献1条内容
所有评论(0)