人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

Oracle數(shù)據(jù)庫備份還原詳解

發(fā)布日期：2022-01-31 20:59 | 文章來源：CSDN

理論準(zhǔn)備

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:整個數(shù)據(jù)庫進(jìn)行備份還原。

Schema Mode：默認(rèn)導(dǎo)出模式，Schema 模式。

Table Mode：表模式。

Tablespace Mode：表空間模式。

實踐

驗證1：備份某一時刻數(shù)據(jù)庫數(shù)據(jù)，通過恢復(fù)語句能夠恢復(fù)到備份時刻的數(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>

連接到對應(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)出的時候只有一條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的時候，驗證通過。

SQL> select * from test_tab;
        ID TITLE
---------- --------------------
         1 hello world

驗證2：備份數(shù)據(jù)的時候不想備份所有表，要根據(jù)條件過濾掉某些表進(jìn)行備份，恢復(fù)的時候只恢復(fù)備份出來的表數(shù)據(jù)。

我們再創(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ù)。

到此這篇關(guān)于Oracle數(shù)據(jù)庫備份還原詳解的文章就介紹到這了,更多相關(guān)Oracle備份還原內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

美國服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。

實時開通

自選配置、實時開通

免備案

全球線路精選！

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問服務(wù)

1對1客戶咨詢顧問

在線
客服

在線客服：7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部