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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
- 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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- 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; 在功能和效率方面有巨大的提升.
支持并行
支持任务的暂停和重启动
支持对象的过滤
备份 / 恢复效率方面那是大大的提升. 所以 10G 之后可以弃用 exp/imp.
empdp 和 impdp 是服务端的工具程序, 他们只能在 ORACLE 服务端使用, 不能在客户端使用; 使用之前需要创建目录; 如下演示
在服务器上创建目录
mkdir -p /data/ora_dir_lottu
创建逻辑目录, 给操作的用户赋予在指定目录的操作权限
- create directory dp_lottu as '/data/ora_dir_lottu';
- grant read,write on directory dp_lottu to lottu;
3.1 导出命令 expdp
按表模式导出
expdp lottu/li0924 tables=tbl_lottu,tbl_lottu_01 dumpfile=expdp_lottu01.dmp logfile=expdp_lottu01.log directory=dp_lottu
按表空间导出
expdp "'/ as sysdba'" tablespaces=tp_lottu dumpfile=expdp_lottu02.dmp logfile=expdp_lottu02.log directory=dp_lottu job_name=lottu02
导出方案
expdp "'/ as sysdba'" SCHEMAS=lottu,rax dumpfile=expdp_lottu05.dmp logfile=expdp_lottu05.log directory=dp_lottu
导出整个数据库
expdp "'/ as sysdba'" dumpfile=expdp_full.dmp full=y logfile=expdp_lottu05.log directory=dp_lottu
3.2 导入命令 impdp
impdp 相当于导入命令 expdp 的反向操作; 使用方法跟 expdp 相同. 相当于把上面 expdp 替换即可.
impdp 导入的方案, 表或者表空间与 dump 文件不一致; 可以用下列参数替换
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
REMAP_TABLE
Table names are remapped to another table.
- For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
- REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.
如示例所示:# 将 lottu 用户下的数据全部导入到表空间 tp_rax
impdp "'/ as sysdba'" directory=dp_lottu dumpfile=expdp_lottu04.dmp remap_tablespace=tp_lottu:tp_rax
四. 复制表
Oracle 中复制表方式:
create table tablename_back as select * from tablename;
来源: http://www.linuxidc.com/Linux/2018-08/153702.htm