理论准备
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：备份某一时刻数据库数据，通过恢复语句能够恢复到备份时刻的数据。
切换用户后登录
[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表中的数据。
到此这篇关于oracle数据库备份还原详解的文章就介绍到这了,更多相关oracle备份还原内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！