写点什么

「Oracle」Oracle 数据库备份还原

  • 2021 年 12 月 06 日
  • 本文字数:4407 字

    阅读完需:约 14 分钟

作者:threedayman

来源:恒生LIGHT云社区

理论准备

oracle 数据库提供 expdp 和 impdp 命令用于备份和恢复数据库。


具体可查阅 oracle 官方文档 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/database-utilities.pdf


备份和还原主要有


  • FULL_MODE:整个数据库进行备份还原。

  • Schema Mode:默认导出模式,Schema 模式。

  • Table Mode:表模式。

  • Tablespace Mode:表空间模式。

实践

验证 1:备份某一时刻数据库数据,通过恢复语句能够恢复到备份时刻的数据。


切换用户后登录


[root@linuxtestb538 ~]# su oraclebash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 23 14:40:45 2021Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0
SQL>
复制代码


连接到对应用户下


SQL> conn test/test@mypdbConnected.
复制代码


创建了 test_tab 表


create table test_tab(id number(9) not null,title varchar2(20));
复制代码


插入一条数据


insert into test_tab values(1,'hello world');
复制代码


导出数据文件(推出数据库连接)


expdp test/test@mypdb schemas=test dumpfile=test20211119_all.dmp logfile=20211119_all.dmp DIRECTORY=DATA_PUMP_DIR 
复制代码


插入一条数据


insert into test_tab values(2,'hello test');
复制代码


目前数据库中存在两条数据,而数据导出的时候只有一条 hello world 的数据。


SQL> select * from test_tab;
ID TITLE---------- -------------------- 1 hello world 2 hello test
复制代码


现在我们通过 impdp 命令恢复数据库数据


bash-4.2$ impdp test/test@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;
Import: Release 19.0.0.0.0 - Production on Tue Nov 23 14:52:21 2021Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_SCHEMA_01": test/********@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEORA-39151: Table "TEST"."TEST_TAB" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Tue Nov 23 14:52:37 2021 elapsed 0 00:00:14
复制代码


从输入信息中看到 test_tab 表已经存在所以相关的备份数据跳过不处理,但我们的本意需要让备份数据去覆盖现有数据不管现在表 是否已经存在。那我们需要增加 table_exists_action=replace 的参数


impdp test/test@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;
Import: Release 19.0.0.0.0 - Production on Tue Nov 23 14:55:57 2021Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_SCHEMA_01": test/********@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."TEST_TAB" 5.539 KB 1 rowsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 23 14:56:25 2021 elapsed 0 00:00:27
复制代码


连接到数据库后,查询 test_tab 表,发现数据已经恢复到只有一条 hello world 的时候,验证通过。


SQL> select * from test_tab;
ID TITLE---------- -------------------- 1 hello world
复制代码


验证 2:备份数据的时候不想备份所有表,要根据条件过滤掉某些表进行备份,恢复的时候只恢复备份出来的表数据。


我们再创建一张 his 开头的表


create table his_test_tab(id number(9) not null,title varchar2(20));
复制代码


插入数据


insert into his_test_tab values(1,'hello world');
复制代码


导出数据


bash-4.2$ expdp test/test@mypdb schemas=test dumpfile=test20211123-1_all.dmp logfile=20211123-1_all.dmp DIRECTORY=DATA_PUMP_DIR EXCLUDE=table:\"like \'HIS%\'\";
Export: Release 19.0.0.0.0 - Production on Tue Nov 23 15:16:39 2021Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionStarting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@mypdb schemas=test dumpfile=test20211123-1_all.dmp logfile=20211123-1_all.dmp DIRECTORY=DATA_PUMP_DIR EXCLUDE=table:"like 'HIS%'" 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/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX. . exported "TEST"."TEST_TAB" 5.539 KB 1 rowsMaster table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is: /opt/oracle/admin/ORCLCDB/dpdump/D0F96921D5E99512E0534390140A837F/test20211123-1_all.dmpJob "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 23 15:17:39 2021 elapsed 0 00:01:00
复制代码


在 test_tab 和 his_test_tab 表中新增数据


SQL> insert into test_tab values(2,'hello test');
1 row created.
SQL> insert into his_tab values(2,'hello test');insert into his_tab values(2,'hello test') *ERROR at line 1:ORA-00942: table or view does not exist

SQL> select * from test_tab;
ID TITLE---------- -------------------- 1 hello world 2 hello test
SQL> select * from his_test_tab;
ID TITLE---------- -------------------- 1 hello world 2 hello test
复制代码


插入数据后 test_tab 和 his_test_tab 表中


还原数据


bash-4.2$ impdp test/test@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211123-1_all.dmp logfile=20211123_recov.dmp;
Import: Release 19.0.0.0.0 - Production on Tue Nov 23 15:24:37 2021Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_SCHEMA_01": test/********@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211123-1_all.dmp logfile=20211123_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."TEST_TAB" 5.539 KB 1 rowsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 23 15:24:47 2021 elapsed 0 00:00:09
复制代码


确认结果


SQL> select * from his_test_tab;
ID TITLE---------- -------------------- 1 hello world 2 hello test
SQL> select * from test_tab;
ID TITLE---------- -------------------- 1 hello world
复制代码


结果符合预期 test_tab 数据被还原,his_test_tab 数据没有被还原。通过备份日志也可以看到我们只备份了 test_tab 表中的数据。

发布于: 2021 年 12 月 06 日阅读数: 8
用户头像

还未添加个人签名 2018.11.07 加入

还未添加个人简介

评论

发布
暂无评论
「Oracle」Oracle 数据库备份还原