「Oracle」Oracle 資料庫備份還原

恆生LIGHT雲社群發表於2021-12-06
Oracle資料庫

作者：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表中的資料。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70001864/viewspace-2845943/，如需轉載，請註明出處，否則將追究法律責任。

相關文章