Oracle 12C数据库的备份还原：通过数据泵expdp/impdp的导出与导入操作【测试成功】

艺帆风顺 发布于 2025-04-03 50 次阅读

直接输入orcl实例名无法连接，则需要指定主机IP

@127.0.0.1/orcl

数据库基本信息Oracle 12C

    [oracle@Aliyun data]$ sqlplus / as sysdba
    SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 11 08:50:59 2023
    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productio

    一、oracle数据泵导出

    1、全量导出

    expdp fuelMS/fuelMS@127.0.0.1/orcl full=y directory=dump_dir dumpfile=FUELMS_20231010.DMP logfile=FUELMS_20231010.log

    2、指定模式导出：schemas=FUELMS

    expdp fuelMS/fuelMS@127.0.0.1/orcl schemas=FUELMS directory=dump_dir dumpfile=FUELMS_20231011.DMP logfile=FUELMS_20231011.log
      [oracle@Aliyun ~]$ expdp fuelMS/fuelMS@127.0.0.1/orcl schemas=FUELMS directory=dump_dir dumpfile=FUELMS_20231011.DMP logfile=FUELMS_20231011.log
      Export: Release 12.2.0.1.0 - Production on Wed Oct 11 08:44:44 2023
      Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
      Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionStarting "FUELMS"."SYS_EXPORT_SCHEMA_04": fuelMS/********@127.0.0.1/orcl schemas=FUELMS directory=dump_dir dumpfile=FUELMS_20231011.DMP logfile=FUELMS_20231011.log Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT. . exported "FUELMS"."HIS_CARD_INFO" 93.20 MB 326037 rows. . exported "FUELMS"."HIS_CARD_CONSUMER" 81.56 MB 253296 rows. . exported "FUELMS"."HIS_CARD_CONSUMER_DETAIL" 7.578 MB 77092 rows. . exported "FUELMS"."CARD_INFO" 4.695 MB 13159 rows. . exported "FUELMS"."HIS_CARD_RECHARGE" 4.771 MB 24735 rows. . exported "FUELMS"."MEM_INFO" 2.590 MB 12908 rows. . exported "FUELMS"."HIS_CARD_DISTRIBUTE" 1.537 MB 13160 rows. . exported "FUELMS"."HIS_CARD_RECHARGE_BATCH" 1.343 MB 12740 rows. . exported "FUELMS"."HIS_OIL_PRICE" 776.5 KB 4139 rows. . exported "FUELMS"."CARD_INFO_ORG" 777.5 KB 7671 rows. . exported "FUELMS"."HIS_DOC_CAR" 238.6 KB 1137 rows. . exported "FUELMS"."SYS_EXPORT_SCHEMA_01" 214.7 KB 1228 rows. . exported "FUELMS"."SYS_EXPORT_SCHEMA_02" 214.7 KB 1228 rows. . exported "FUELMS"."SYS_EXPORT_SCHEMA_03" 214.7 KB 1228 rows. . exported "FUELMS"."DOC_CAR" 181.4 KB 921 rows. . exported "FUELMS"."SYS_LOG" 110.6 KB 645 rows. . exported "FUELMS"."SYS_USER_ORG" 68.36 KB 605 rows. . exported "FUELMS"."SYS_ROLE_AUTH" 61.04 KB 548 rows. . exported "FUELMS"."HIS_DOC_ORG" 42.25 KB 265 rows. . exported "FUELMS"."SYS_DOC_ORG" 37.24 KB 245 rows. . exported "FUELMS"."DOC_OIL_GUN" 41.64 KB 248 rows. . exported "FUELMS"."SYS_USER" 32.88 KB 114 rows. . exported "FUELMS"."DOC_OIL_PRICE" 26.71 KB 82 rows. . exported "FUELMS"."DOC_PERSON" 22.12 KB 64 rows. . exported "FUELMS"."SYS_AUTH" 19.21 KB 166 rows. . exported "FUELMS"."HIS_DOC_PERSON" 21.34 KB 77 rows. . exported "FUELMS"."SYS_MENU_AUTH" 17.85 KB 160 rows. . exported "FUELMS"."SYS_USER_ROLE" 16.89 KB 109 rows. . exported "FUELMS"."SYS_MENU" 17.30 KB 160 rows. . exported "FUELMS"."CARD_SETTLE" 12.72 KB 15 rows. . exported "FUELMS"."DOC_CARCLASS" 12.21 KB 14 rows. . exported "FUELMS"."SYS_CONF" 12.12 KB 12 rows. . exported "FUELMS"."DOC_OIL" 11.44 KB 7 rows. . exported "FUELMS"."DOC_OIL_CLASS" 10.82 KB 6 rows. . exported "FUELMS"."HIS_DOC_CARCLASS" 10.35 KB 15 rows. . exported "FUELMS"."CARD_SETTLE_CONSUMER" 9.843 KB 39 rows. . exported "FUELMS"."HIS_DOC_OIL" 9.445 KB 9 rows. . exported "FUELMS"."SYS_ROLE" 8.148 KB 9 rows. . exported "FUELMS"."HIS_OIL_CLASS" 7.031 KB 2 rows. . exported "FUELMS"."SYS_ACCESS_CONTROL" 0 KB 0 rows. . exported "FUELMS"."SYS_ENUM" 0 KB 0 rows. . exported "FUELMS"."SYS_MSG" 0 KB 0 rows. . exported "FUELMS"."SYS_ORG_AUTH" 0 KB 0 rowsMaster table "FUELMS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded******************************************************************************Dump file set for FUELMS.SYS_EXPORT_SCHEMA_04 is: /mnt1/oracle/data/FUELMS_20231011.DMPJob "FUELMS"."SYS_EXPORT_SCHEMA_04" successfully completed at Wed Oct 11 08:45:44 2023 elapsed 0 00:00:58

      二、oracle数据泵导入

      全库导入：

      impdp fuelMS/fuelMS@127.0.0.1/ORCL full=y directory=dump_dir dumpfile=FUELMS_20230803160039.DMP logfile=FUELMS_20230803160039.log

      注：导入时，tables、schemas、full这三个参数不能同时出现

      三、查询oracle数据库的默认数据泵路径

      select * from dba_directories t;

        SQL> select * from dba_directories t;
        OWNER--------------------------------------------------------------------------------DIRECTORY_NAME--------------------------------------------------------------------------------DIRECTORY_PATH--------------------------------------------------------------------------------ORIGIN_CON_ID-------------SYSDUMP_DIR/mnt1/oracle/data