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数据库备份还原详解的文章就介绍到这了
原文链接:https://blog.51cto.com/u_7932852/4753329