Oracle数据库逻辑备份与恢复

195次阅读

共计 4750 个字符，预计需要花费 12 分钟才能阅读完成。

Oracle 逻辑备份的核心就是复制数据；Oracle 提供的逻辑备份与恢复的命令有 exp/imp，expdp/impdp。当然像表级复制 (create table table_back as select * from table) 也算是一种逻辑备份。Oracle 逻辑备份没有支持增量备份；对数据恢复也是非一致性的。所以一般都是用于数据迁移的工作。

创建演示对象

create tablespace lottu datafile ‘/data/oracle/data/lottu01.dbf’ size 2G autoextend on;
create user lottu identified by li0924 default tablespace lottu;
grant connect, resource to lottu ;
grant select any dictionary to lottu ;
create user rax identified by rax123 default tablespace lottu;
grant connect, resource to rax ;
grant select any dictionary to rax ;
conn lottu/li0924
create table tbl_lottu as select level as id, ‘lottu’ as name from dual connect by level <= 10000;
create table tbl_lottu_01 as select level as id, ‘lottu’||level as name from dual connect by level <= 100;
conn rax/rax123
create table tbl_rax as select level as id, ‘rax’ as name from dual connect by level <= 10000;

二. Oracle 导出 / 导入命令 exp/imp

exp/imp 命令是最原始的一种数据保护工具；效率方面确实不好；支持客户端执行操作。在这简单演示下如何操作。

备份的对象列表：

. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions

2.1 导出命令 exp

导出表中某些记录

exp lottu/li0924 GRANTS=Y TABLES=tbl_lottu QUERY=”‘where id < 100′” FILE=/home/oracle/exp/lottu01.dmp LOG=/home/oracle/exp/log/lottu01.log

导出某个 Schema 下某些表

exp lottu/li0924 GRANTS=Y TABLES=”(tbl_lottu,tbl_lottu_01)” FILE=/home/oracle/exp/lottu02.dmp LOG=/home/oracle/exp/log/lottu02.log

导出哪些 Schema 下的对象

exp system/oracle-linuxidc OWNER=”(lottu,rax)” FILE=/home/oracle/exp/system03.dmp LOG=/home/oracle/exp/log/system03.log

导出全库

exp system/oracle-linuxidc FULL=Y FILE=/home/oracle/exp/system04.dmp LOG=/home/oracle/exp/log/system04.log

2.2 导入命令 imp

imp 相当于 exp 的反向操作；操作之前；需要确认需导入的对象在数据库上面是不存在的；若是在本地做恢复；需要将恢复的对象先 drop 掉；在执行 imp 命令操作。

导入某个 Schema 下某些表；对 exp 上面的示例 2

[oracle@oracle-linuxidc ~]$ sqlplus lottu/li0924

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 4 04:42:18 2018

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table tbl_lottu;

Table dropped.

SQL> drop table tbl_lottu_01;

Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-linuxidc ~]$ imp lottu/li0924 GRANTS=Y TABLES=”(tbl_lottu,tbl_lottu_01)” FILE=/home/oracle/exp/lottu02.dmp LOG=/home/oracle/imp/lottu02.log

Import: Release 11.2.0.4.0 – Production on Sat Aug 4 04:43:25 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing LOTTU’s objects into LOTTU
. importing LOTTU’s objects into LOTTU
. . importing table “TBL_LOTTU” 10000 rows imported
. . importing table “TBL_LOTTU_01” 100 rows imported
Import terminated successfully without warnings.

三. Oracle 导出 / 导入命令 expdp/impdp

对 expdp/impdp 是在 Oracle10G 之后才出现；其实本身使用并不是需要很高的技术含量。相比 exp/imp；在功能和效率方面有巨大的提升。