• 售前

  • 售后

热门帖子
入门百科

Oracle数据库备份还原详解

[复制链接]
或许你会吞 显示全部楼层 发表于 2022-1-7 22:19:58 |阅读模式 打印 上一主题 下一主题
理论准备

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. [root@linuxtestb538 ~]# su oracle
  2. bash-4.2$ sqlplus / as sysdba
  3. SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 23 14:40:45 2021
  4. Version 19.3.0.0.0
  5. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  6. Connected to:
  7. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  8. Version 19.3.0.0.0
  9. SQL>
复制代码
毗连到对应用户下
  1. SQL> conn test/test@mypdb
  2. Connected.
复制代码
创建了test_tab表
  1. create table test_tab(
  2. id number(9) not null,
  3. title varchar2(20)
  4. );
复制代码
插入一条数据
  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. SQL> select * from test_tab;
  2.         ID TITLE
  3. ---------- --------------------
  4.          1 hello world
  5.          2 hello test
复制代码
如今我们通过impdp下令规复数据库数据
  1. bash-4.2$ impdp test/test@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;
  2. Import: Release 19.0.0.0.0 - Production on Tue Nov 23 14:52:21 2021
  3. Version 19.3.0.0.0
  4. Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  5. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  6. Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
  7. Starting "TEST"."SYS_IMPORT_SCHEMA_01":  test/********@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp
  8. Processing object type SCHEMA_EXPORT/USER
  9. ORA-31684: Object type USER:"TEST" already exists
  10. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  11. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  12. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  13. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  14. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  15. ORA-39151: Table "TEST"."TEST_TAB" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
  16. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  17. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  18. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  19. 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. impdp test/test@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;
  2. Import: Release 19.0.0.0.0 - Production on Tue Nov 23 14:55:57 2021
  3. Version 19.3.0.0.0
  4. Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  5. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  6. Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
  7. 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
  8. Processing object type SCHEMA_EXPORT/USER
  9. ORA-31684: Object type USER:"TEST" already exists
  10. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  11. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  12. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  13. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  14. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  15. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  16. . . imported "TEST"."TEST_TAB"                           5.539 KB       1 rows
  17. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  18. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  19. 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. SQL> select * from test_tab;
  2.         ID TITLE
  3. ---------- --------------------
  4.          1 hello world
复制代码
验证2:备份数据的时间不想备份所有表,要根据条件过滤掉某些表举行备份,规复的时间只规复备份出来的表数据。
我们再创建一张his开头的表
  1. create table his_test_tab(
  2. id number(9) not null,
  3. title varchar2(20)
  4. );
复制代码
插入数据
  1. insert into his_test_tab values(1,'hello world');
复制代码
导出数据
  1. 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%\'";
  2. Export: Release 19.0.0.0.0 - Production on Tue Nov 23 15:16:39 2021
  3. Version 19.3.0.0.0
  4. Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  5. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  6. 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%'"
  7. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  8. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  9. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  10. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  11. Processing object type SCHEMA_EXPORT/USER
  12. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  13. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  14. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  15. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  16. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  17. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  18. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  19. . . exported "TEST"."TEST_TAB"                           5.539 KB       1 rows
  20. Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
  21. ******************************************************************************
  22. Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  23.   /opt/oracle/admin/ORCLCDB/dpdump/D0F96921D5E99512E0534390140A837F/test20211123-1_all.dmp
  24. 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. 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 existSQL> select * from test_tab;
  2.         ID TITLE
  3. ---------- --------------------
  4.          1 hello world
  5.          2 hello testSQL> select * from his_test_tab;        ID TITLE---------- --------------------         1 hello world         2 hello test
复制代码
插入数据后test_tab和his_test_tab表中
还原数据
  1. 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;
  2. Import: Release 19.0.0.0.0 - Production on Tue Nov 23 15:24:37 2021
  3. Version 19.3.0.0.0
  4. Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  5. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  6. Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
  7. 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
  8. Processing object type SCHEMA_EXPORT/USER
  9. ORA-31684: Object type USER:"TEST" already exists
  10. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  11. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  12. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  13. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  14. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  15. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  16. . . imported "TEST"."TEST_TAB"                           5.539 KB       1 rows
  17. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  18. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  19. 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. SQL> select * from his_test_tab;        ID TITLE---------- --------------------         1 hello world         2 hello testSQL> select * from test_tab;
  2.         ID TITLE
  3. ---------- --------------------
  4.          1 hello world
复制代码
效果符合预期test_tab数据被还原,his_test_tab数据没有被还原。通过备份日志也可以看到我们只备份了test_tab表中的数据。
到此这篇关于Oracle数据库备份还原详解的文章就介绍到这了,更多相关Oracle备份还原内容请搜刮草根技术分享从前的文章或继续欣赏下面的相关文章渴望各人以后多多支持草根技术分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作