作者：threedayman

來源：恆生LIGHT雲社群

理論準備

oracle 資料庫提供expdp和impdp命令用於備份和恢復資料庫。

具體可查閱oracle官方文件

備份和還原主要有

FULL_MODE:整個資料庫進行備份還原。
Schema Mode：預設匯出模式，Schema 模式。
Table Mode：表模式。
Tablespace Mode：表空間模式。

實踐

驗證1：備份某一時刻資料庫資料，透過恢復語句能夠恢復到備份時刻的資料。

切換使用者後登入

[root@linuxtestb538 ~]# su oracle


bash-4.2$ sqlplus / as sysdba






SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 23 14:40:45 2021


Version 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 - Production


Version 19.3.0.0.0






SQL>

連線到對應使用者下

SQL> conn test/test@mypdb


Connected.

建立了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 2021


Version 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 - Production


Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded


Starting "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/USER


ORA-31684: Object type USER:"TEST" 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-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_DATA


Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


Processing object type SCHEMA_EXPORT/STATISTICS/MARKER


Job "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 2021


Version 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 - Production


Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded


Starting "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/USER


ORA-31684: Object type USER:"TEST" 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 "TEST"."TEST_TAB"                           5.539 KB       1 rows


Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


Processing object type SCHEMA_EXPORT/STATISTICS/MARKER


Job "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 2021


Version 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 - Production


Starting "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_DATA


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/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/TABLE/TABLE


Processing object type SCHEMA_EXPORT/TABLE/COMMENT


Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX


. . exported "TEST"."TEST_TAB"                           5.539 KB       1 rows


Master 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.dmp


Job "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 2021


Version 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 - Production


Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded


Starting "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/USER


ORA-31684: Object type USER:"TEST" 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 "TEST"."TEST_TAB"                           5.539 KB       1 rows


Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


Processing object type SCHEMA_EXPORT/STATISTICS/MARKER


Job "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表中的資料。

「Oracle」Oracle 資料庫備份還原

理論準備

實踐

相關文章