激情久久久_欧美视频区_成人av免费_不卡视频一二三区_欧美精品在欧美一区二区少妇_欧美一区二区三区的

服務器之家：專注于服務器技術及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數據庫技術|

服務器之家 - 數據庫 - Oracle - Oracle數據庫備份還原詳解

Oracle數據庫備份還原詳解

2022-01-12 18:25恒生LIGHT云社區 Oracle

大家好，本篇文章主要講的是Oracle數據庫備份還原詳解，感興趣的同學趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽

理論準備

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：備份某一時刻數據庫數據，通過恢復語句能夠恢復到備份時刻的數據。

切換用戶后登錄

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[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>

連接到對應用戶下

?
1
2
SQL> conn test/test@mypdb
Connected.

創建了test_tab表

?
1
2
3
4
create table test_tab(
id number(9) not null,
title varchar2(20)
);

插入一條數據

?
1
insert into test_tab values(1,'hello world');

導出數據文件(推出數據庫連接)

?
1
expdp test/test@mypdb schemas=test dumpfile=test20211119_all.dmp logfile=20211119_all.dmp DIRECTORY=DATA_PUMP_DIR

插入一條數據

?
1
insert into test_tab values(2,'hello test');

目前數據庫中存在兩條數據,而數據導出的時候只有一條hello world的數據。

?
1
2
3
4
5
6
SQL> select * from test_tab;
 
        ID TITLE
---------- --------------------
         1 hello world
         2 hello test

現在我們通過impdp命令恢復數據庫數據

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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的參數

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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的時候，驗證通過。

?
1
2
3
4
5
SQL> select * from test_tab;
 
        ID TITLE
---------- --------------------
         1 hello world

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

我們再創建一張his開頭的表

?
1
2
3
4
create table his_test_tab(
id number(9) not null,
title varchar2(20)
);

插入數據

?
1
insert into his_test_tab values(1,'hello world');

導出數據

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 表中新增數據

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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表中

還原數據

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

確認結果

?
1
2
3
4
5
6
7
8
9
10
11
12
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備份還原內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！

原文鏈接：https://blog.51cto.com/u_7932852/4753329

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板： 在线播放免费人成毛片乱码 | 嫩呦国产一区二区三区av | 欧美日韩专区国产精品 | 92自拍视频| 国产精品久久久久无码av | 中文字幕爱爱视频 | 国产精品999在线 | 久久久成人动漫 | 亚洲精品成人久久久 | 久在线播放 | 精品一区二区三区网站 | 国产成人精品免费视频大全最热 | 视频一区二区在线观看 | 成人在线观看免费观看 | 国产精品一区二区三区在线 | av在线等 | 一区二区三区日韩在线观看 | 91美女福利视频 | 成人一区二区三区在线 | 亚洲一级簧片 | 毛片大全免费 | 看国产精品 | www.91在线观看| 欧美男女爱爱视频 | 国产精品久久久久久久久久久久午夜 | 亚洲一级片免费观看 | 欧美精品一区二区性色 | 素人视频免费观看 | 国产影院在线观看 | 免费视频观看 | 国产日韩大片 | 性片免费看 | 国产成人网 | 成人毛片在线免费看 | 久久午夜国产 | 国产精品免费成人 | 亚洲91网站| 亚洲成a人在线 | 妇女毛片 | 国产成人免费高清激情视频 | 中国美女一级黄色大片 |