您好,登錄后才能下訂單哦!
這篇文章主要介紹了Oracle數(shù)據(jù)庫備份如何還原,具有一定借鑒價(jià)值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
oracle 數(shù)據(jù)庫提供expdp和impdp命令用于備份和恢復(fù)數(shù)據(jù)庫。
具體可查閱oracle官方文檔 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/database-utilities.pdf
備份和還原主要有
FULL_MODE:整個(gè)數(shù)據(jù)庫進(jìn)行備份還原。
Schema Mode:默認(rèn)導(dǎo)出模式,Schema 模式。
Table Mode:表模式。
Tablespace Mode:表空間模式。
驗(yàn)證1:備份某一時(shí)刻數(shù)據(jù)庫數(shù)據(jù),通過恢復(fù)語句能夠恢復(fù)到備份時(shí)刻的數(shù)據(jù)。
切換用戶后登錄
[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>
連接到對(duì)應(yīng)用戶下
SQL> conn test/test@mypdb Connected.
創(chuàng)建了test_tab表
create table test_tab( id number(9) not null, title varchar2(20) );
插入一條數(shù)據(jù)
insert into test_tab values(1,'hello world');
導(dǎo)出數(shù)據(jù)文件(推出數(shù)據(jù)庫連接)
expdp test/test@mypdb schemas=test dumpfile=test20211119_all.dmp logfile=20211119_all.dmp DIRECTORY=DATA_PUMP_DIR
插入一條數(shù)據(jù)
insert into test_tab values(2,'hello test');
目前數(shù)據(jù)庫中存在兩條數(shù)據(jù),而數(shù)據(jù)導(dǎo)出的時(shí)候只有一條hello world的數(shù)據(jù)。
SQL> select * from test_tab; ID TITLE ---------- -------------------- 1 hello world 2 hello test
現(xiàn)在我們通過impdp命令恢復(fù)數(shù)據(jù)庫數(shù)據(jù)
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表已經(jīng)存在所以相關(guān)的備份數(shù)據(jù)跳過不處理,但我們的本意需要讓備份數(shù)據(jù)去覆蓋現(xiàn)有數(shù)據(jù)不管現(xiàn)在表 是否已經(jīng)存在。那我們需要增加 table_exists_action=replace的參數(shù)
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
連接到數(shù)據(jù)庫后,查詢test_tab表,發(fā)現(xiàn)數(shù)據(jù)已經(jīng)恢復(fù)到只有一條hello world的時(shí)候,驗(yàn)證通過。
SQL> select * from test_tab; ID TITLE ---------- -------------------- 1 hello world
驗(yàn)證2:備份數(shù)據(jù)的時(shí)候不想備份所有表,要根據(jù)條件過濾掉某些表進(jìn)行備份,恢復(fù)的時(shí)候只恢復(fù)備份出來的表數(shù)據(jù)。
我們?cè)賱?chuàng)建一張his開頭的表
create table his_test_tab( id number(9) not null, title varchar2(20) );
插入數(shù)據(jù)
insert into his_test_tab values(1,'hello world');
導(dǎo)出數(shù)據(jù)
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 表中新增數(shù)據(jù)
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
插入數(shù)據(jù)后test_tab和his_test_tab表中
還原數(shù)據(jù)
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
確認(rèn)結(jié)果
SQL> select * from his_test_tab; ID TITLE ---------- -------------------- 1 hello world 2 hello test SQL> select * from test_tab; ID TITLE ---------- -------------------- 1 hello world
結(jié)果符合預(yù)期test_tab數(shù)據(jù)被還原,his_test_tab數(shù)據(jù)沒有被還原。通過備份日志也可以看到我們只備份了test_tab表中的數(shù)據(jù)。
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“Oracle數(shù)據(jù)庫備份如何還原”這篇文章對(duì)大家有幫助,同時(shí)也希望大家多多支持億速云,關(guān)注億速云行業(yè)資訊頻道,更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。