手机版
你好,游客 登录 注册
背景:
阅读新闻

Oracle数据库逻辑备份与恢复

[日期:2018-08-25] 来源:Linux社区  作者:lottu [字体: ]

  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;

更多Oracle相关信息见Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-08/153702.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款