Orcale（备份导入导出）

1.备份恢复

1.1.备份定义

备份就是把数据库复制到转储设备的过程。其中，转储设备是指用于放置数据库副本的磁带或磁盘。通常也将存放于转储设备中的数据库的副本称为原数据库的备份或转储。备份是一份数据副本

1.2.备份分类

从物理与逻辑的角度来分类：从物理与逻辑的，备份可以分为物理备份和逻辑备份。

• 物理备份：对数据库操作系统的物理文件（数据文件，控制文件和日志文件）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份（热备份），前者是在关闭数据库的时候进行的，后者是以归档日志的方式对运行的数据库进行备份。可以使用oracle的恢复管理器（RMAN）或操作系统命令进行数据库的物理备份。

• 逻辑备份：对数据库逻辑组件（如表和存储过程等数据库对象）的备份。逻辑备份的手段很多，如传统的EXP，数据泵（EXPDP），数据库闪回技术等第三方工具，都可以进行数据库的逻辑备份。

从数据库的备份角度分类：从数据库的备份角度，备份可以分为完全备份、增量备份和差异备份。

• 完全备份：每次对数据库进行完整备份，当发生数据丢失的灾难时，完全备份无需依赖其他信息即可实现100%的数据恢复，其恢复时间最短且操作最方便。

• 增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。优点是备份数据量小，需要的时间短，缺点是恢复的时候需要依赖以前备份记录，出问题的风险较大。

• 差异备份：备份那些自从上次完全备份之后被修改过的文件。从差异备份中恢复数据的时间较短，因此只需要两份数据：最后一次完整备份和最后一次差异备份，缺点是每次备份需要的时间较长。

1.3.恢复定义

恢复就是发生故障后，利用已备份的数据文件或控制文件，重新建立一个完整的数据库。

1.4.恢复分类

• 实例恢复：当oracle实例出现失败后，oracle自动进行的恢复。

• 介质恢复：当存放数据库的介质出现故障时所作的恢复。介质恢复又分为完全恢复和不完全恢复。

• 完全恢复：将数据库恢复到数据库失败时的状态。这种恢复是通过装载数据库备份并应用全部的重做日志做到的。

• 不完全恢复：将数据库恢复到数据库失败前的某一时刻的状态。这种恢复是通过装载数据库备份并应用部分的重做日志做到的。进行不完全恢复后，必须在启动数据库时用resetlogs选项重设联机重做日志。

2.前提准备

数据准备，通过执行以下SQL语句，快速完成100W数据的创建工作。

• 方式一：通过create table xxx as select方式

create table t_userinfo as 
select rownum as userid,
             'user_'||rownum as username,'888888' as password,
             '测试员'||rownum as realname,
               to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as datetime,
               trunc(dbms_random.value(0, 100)) as random_id,
               dbms_random.string('x', 20) random_str
          from dual connect by level <= 1000000;

• 方式二：通过PL/SQL脚本方式

declare
  i number;
begin
  for i in 1..1000000 loop
      insert into t_userinfo values(i,'user_'||i,'888888','测试员'||i,
      to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
      trunc(dbms_random.value(0, 100)),
      dbms_random.string('x', 20));
      commit;
  end loop;
end;

该方式必须提前创建数据表，在执行脚本。

3.综合案例

3.1.PL/SQL

3.2.1.Oracle Export/Import

选择PL/SQL中的Tools -> Export Tables...或者Tools -> Import Tables...中的Oracle Export和Oracle Import方式来实现数据的导入与导出，该方式导出的文件格式为dmp方式。

注意：如果要使用该种方式必须安装完整的Oracle Client客户端，而安装的Oracle驱动是轻量版的，则没有imp.exe、exp.exe等导入和导出文件，从而无法实现PL/SQL数据的导入与导出。

3.2.2.PL/SQL Developer

• 导出：

选择PL/SQL中的Tools -> Export Tables...，找到PL/SQL Developer选项方式可以导出要导出的数据表。这种方式会导出整个表的所有数据，导出的文件是pde格式的文件，不能直接查看sql，适合整表数据导出的情况。导出表具体操作步骤如下：

点击Export Tables...，如下图所示：

• 导入：

选择PL/SQL中的Tools -> Import Tables...，找到PL/SQL Developer选项方式可以导入pde格式的数据文件。导入表具体操作步骤如下：

点击Import Tables...，如下图所示：

注意：在导入数据时，请选择Create tables；然后可以通过Log选项查看导入的日志信息。

3.2.3.SQL Inserts

• 导出：

选择PL/SQL中的Tools -> Export Tables...，找到SQL Inserts选项方式可以导出要导出的数据表。这种方式会导出整个表的所有数据，导出的文件是sql格式的文件，可以直接查看sql。导出表具体操作步骤如下：

注意：勾选Create tables选项，表示在导入该数据文件时，直接创建数据表。

• 导入：

选择PL/SQL中的Tools -> Import Tables...，找到SQL Inserts选项方式可以导入sql格式的数据文件。导入表具体操作步骤如下：

注意：这里需要选中Use Command Windows选项来导入数据，如果要使用Use SQL*Plus方式，则需要额外配置SQL*Plus执行文件的位置。

3.2.exp/imp

Oracle的Exp和Imp工具是两个非常有用的实用程序（客户端工具程序），可用于从Oracle数据库导出数据以及将数据导入数据库。这些工具可以处理各种数据传输需求，包括在不同数据库之间迁移数据。

注意：可以执行exp help=y、imp help=y查看帮助命令，以及执行exp或者imp查看对应版本号。

3.2.1.exp导出

• 方式一：完全模式导出

将整个数据库内容导出，但是操作时需要具备管理员的权限。

exp scott/密码@orcl buffer=32000 file=位置 full=y

在使用该模式导出数据时，需要有多给选项操作，可以直接全部回车执行。

• 方式二：用户模式导出

将指定用户的所有对象进行导出。

exp scott/密码@orcl buffer=32000 file=位置 owner=用户名

• 方式三：表模式导出

指定导出数据库中的数据表。

exp scott/密码@orcl buffer=32000 file=位置 tables=(表1,表2,...)

3.2.2.imp导入

导入数据，请使用imp，语法格式如下：

imp 用户名/密码@orcl file=dmp文件路径 log=输出日志路径 full=y ignore=y

温馨提示：采用imp导入过程经常会遇到问题，建议多查阅资料，总有方法解决。

3.3.expdp/impdp

ORCALE 10G后提供了新的导入导出工具，数据泵（Data Pump），其中expdp和impdp是两个最常用的命令行工具。它们可以快速、高效地迁移大量数据，并且提供了丰富的参数供用户定制化操作。

• 数据泵导出：使用工具EXPDP将数据库对象的元数据Meta-Data（对象结构）或数据导出到转储文件中。

• 数据泵导入：使用工具IMPDP将转储文件中的元数据及其数据导入到Oracle数据库中。

EXPDP和IMPDP属于逻辑备份与恢复。其逻辑备份的二进制文件成为转储文件，以dmp格式存储；EXPDP和IMPDP都是服务端程序，因此转储文件只能放在由DIRECTORY对象指定的特定数据库服务器操作系统目录中。

数据泵提供的主要特性：

• 支持并行处理导入、导出任务

• 支持暂停和重启动导入、导出任务

• 支持通过Database Link的方式导出或导入远端数据库中的对象

• 支持在导入时通过Remap_schema、Remap_datafile、Remap_tablespace几个参数实现导入过程中自动修改对象属主、数据文件或数据所在表空间。

• 导入/导出时提供了非常细粒度的对象控制。通过Include、Exclude两个参数，甚至可以详细制定是否包含或不包含某个对象。

3.3.1.创建directory对象

1.什么是Directory对象？

Directory对象是Oracle10g版本提供的一个新功能。它是一个指向，指向了操作系统中的一个路径。每个Directory都包含Read,Write两个权限，可以通过Grant命令授权给指定的用户或角色。拥有读写权限的用户就可以读写该Directory对象指定的操作系统路径下的文件。

最终，无论在什么地方使用expdp，生成的文件最终也是在服务器上的Directory指定的位置。

案例演示：

-- 查询目录对象是否已定义
select * from dba_directories t where t.directory_name = 'MYDIR';
-- 若不存在，则创建，若提示权限不足，请授权
create directory mydir as 'c:\mydir';
-- 权限授予
grant create any directory to scott; 
grant read, write on directory mydir to scott; 
-- 权限查询
select * from dba_sys_privs t where grantee='SCOTT';
select * from dba_tab_privs t where grantee='SCOTT';

3.3.2.expdp导出

expdp命令用于将数据库对象和数据导出到dump文件中。以下是使用expdp的基本语法：

expdp 'username/password@db' directory=dir_name dumpfile=dump_file.dmp logfile=log_file.log

参数说明：

• username/password@db: 指定要连接的数据库的用户名、密码和数据库标识符。

• directory=dir_name: 指定一个有效的目录对象，该对象指向服务器上的文件系统目录。

• dumpfile=dump_file.dmp: 指定要创建的dump文件的名称。

• logfile=log_file.log: 指定要创建的日志文件的名称。

• nologfile=y：不输入日志信息。

额外参数说明：

• content={ALL | DATA_ONLY | METADATA_ONLY}: ALL表示所有，DATA_ONLY只导出数据，不导出对象；METADATA_ONLY只导出表结构

• parallel=4: 并行处理，指定并行度。

• query: 指定过滤查询条件。

并行处理：在使用expdp进行数据导出时，可以使用并行处理来加快导出速度；而在使用impdp进行数据导入时，也可以使用并行处理来加快导入速度。但是，并行处理的使用方式和效果可能会有所不同，具体取决于数据库的配置和系统资源。

3.3.3.impdp导入

impdp命令用于从dump文件中导入数据和对象到数据库中。以下是使用impdp的基本语法：

impdp 'username/password@db' directory=dir_name dumpfile=dump_file.dmp logfile=log_file.log

参数说明：

• username/password@db: 指定要连接的数据库的用户名、密码和数据库标识符。

• directory=dir_name: 指定一个有效的目录对象，该对象指向服务器上的文件系统目录。

• dumpfile=dump_file.dmp: 指定要导入的dump文件的名称。

• logfile=log_file.log: 指定要创建的日志文件的名称。

额外参数说明：

• table_exists_action=replace: 如果目标表已存在，则替换它；先drop表，再创建表，最后插入数据。

• table_exists_action=truncate: 如果目标表已存在，则截断它；先删除数据再添加。

• table_exists_action=append: 如果目标表已存在，则追加数据。

• remap_schema: 用于重命名模式。例如，remap_schema=’old_schema’:’new_schema’。

• remap_tablespace: 用于重命名表空间。例如，remap_tablespace=’old_tablespace’:’new_tablespace’

3.3.4.案例演示

• 方式一：全库模式

全库模式将导出导入整个数据库，对应的命令参数是full=y，但是必须具备dba或者exp_full_database和imp_full_database权限的用户才能执行。

全库模式expdp导出：

执行命令如下：

expdp scott/密码@orcl directory=mydir dumpfile=full.dmp full=y logfile=full.log

expdp全库模式下报错ORA-31631、ORA-39161权限不足，如下图所示：

将exp_full_database权限赋予scott用户，最后使用expdp全库模式导出：

-- 登录sysdba
sqlplus / as sysdba;
-- 将exp_full_database权限赋予scott用户
grant exp_full_database to scott;
-- 退出sysdba
exit
-- 最后，再次执行上述的expdp全库导出模式(开启并行处理)
expdp scott/密码@orcl directory=mydir dumpfile=full.dmp full=y logfile=full.log parallel=4

全库模式impdp导入：

将imp_full_database权限赋予scott用户，最后使用impdp全库模式导入：

-- 登录sysdba
sqlplus / as sysdba;
-- 将imp_full_database权限赋予scott用户
grant imp_full_database to scott;
-- 退出sysdba
exit
-- 最后，再次执行上述的impdp全库导入模式(开启并行处理)
impdp scott/密码@orcl directory=mydir dumpfile=full.dmp full=y nologfile=y parallel=4

• 方式二：用户模式

导出或导入Schema下的自有对象，对应impdp/expdp命令中的Schema参数，这是默认的操作模式。如果拥有dba或者exp_full_database和imp_full_database权限的用户执行的话，就可以导出或导入多个Schema中的对象。

用户模式expdp导出：

执行命令如下：

expdp scott/密码@orcl directory=mydir dumpfile=schema.dmp schemas=scott logfile=schema.log

用户模式impdp导入：

执行命令如下：

impdp scott/密码@orcl directory=mydir dumpfile=schema.dmp schemas=scott logfile=schema.log

• 方式三：表模式

导出指定的表或者表分区（如果有分区的话）以及依赖该表的对象（如该表的索引，约束等，不过前提是这些对象在同一个Schema中，或者执行的用户有相应的权限）。对应impdp/expdp命令中的Table参数。指定的表只能是在一个Schema下。

表模式expdp导出：

执行命令如下：

expdp scott/密码@orcl directory=mydir dumpfile=table.dmp tables=表1,表2,... logfile=table.log

表模式impdp导入：

执行命令如下：

impdp scott/密码@orcl directory=mydir dumpfile=table.dmp tables=表1,表2,... logfile=table.log

具体导入参数可以参考上述impdp。

• 方式四：表空间模式

导出指定的表空间中的内容。对应impdp/expdp中的Tablespaces参数，这种模式类似于表模式和Schema模式的补充。

表空间模式expdp导出：

执行命令如下：

expdp scott/密码@orcl directory=mydir dumpfile=tablespace.dmp tablesapces=表空间1,表空间2,... logfile=tablespace.log

表空间模式impdp导入：

执行命令如下：

impdp scott/密码@orcl directory=mydir dumpfile=tablespace.dmp tablespaces=表空间1,表空间,... logfile=tablespace.log

• 方式五：条件过滤

-- 通过expdp数据泵导出，加入query和content参数，分别用于指定导出数据的条件和导出数据的形式。
expdp scott/密码@orcl directory=mydir dumpfile=tables.dmp tables=t_score query='t_score:"where cid=02"' content=data_only logfile=tables.log
-- 删除t_score中的cid=02的数据并提交事务
delete from t_score where cid=02;
commit;
-- 通过impdp数据泵导入数据，通过添加table_exists_action参数来控制数据的导入方式，此处使用的是append追加数据模式，如果目标数据表存在，则追加数据
impdp scott/un1xR00t@orcl directory=mydir dumpfile=tables.dmp tables=t_score table_exists_action=append logfile=tables.log