阅读 256

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


文章分类
百科问答
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐