Oracle 数据库知识点：逻辑备份

岳彩波 twt企业IT社区 2022-07-03

一知识点简介

逻辑备份的方式有好几种，在这里我们简单的介绍一下导入导出，以方便大家在日常的测试和准生产环境使用。

二知识点内容

导出(exp) & 导入(imp)

利用Export可将数据从数据库中提取出来，就是将select的结果存到一个FS二进制文件上

利用Import则可将提取出来的数据送回到Oracle数据库中去。

要读写数据文件内数据,所以数据库必须open

不能备份活跃频繁数据

exp满足select的所有特性比如权限或读一致性

imp满足insert的所有特性比如主键或空间不足

Oracle支持三种方式基本类型的输出：

1.表方式（T方式），将指定表的数据导出。

2.用户方式（U方式），将指定用户的所有对象及数据导出。

3.全库方式（Full方式），数据库中的所有对象导出。

数据导入（Import）的过程是数据导出（Export）的逆过程，分别将数据文件导入数据库和将数据库数据导出到数据文件。

三实验过程

1.交互模式导出exp表

[oracle@dba ~]$ exp
Export: Release 10.2.0.1.0 - Production on Mon Apr 4 16:58:41 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > scott_tab_emp.dmp

(2)U(sers), or (3)T(ables): (2)U > T

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp

. . exporting table EMP 14 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dept

. . exporting table DEPT 4 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > 回车退出

Export terminated successfully with warnings.
[oracle@dba ~]$

查看备份文件

[oracle@dba ~]$ ll -thr scott_tab_emp.dmp
-rw-r--r-- 1 oracle oinstall 24K 04-04 17:00 scott_tab_emp.dmp
[oracle@dba ~]$ file scott_tab_emp.dmp
scott_tab_emp.dmp: DBase 3 data file (1380929624 records)
[oracle@dba ~]$ strings scott_tab_emp.dmp

导出的文件是二进制文件如果要经历FTP传输一定要使用binary传输模式传如果使用ASCII模式传输会损坏文件

windows cmd里的ftp 默认是ascii传输模式

备份出来的是什么内容只是对象的元数据 + 用户数据

[oracle@dba exp_dir]$ strings cmd_scott_tab.dmp | grep -i 'CREATE' --color
CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), ....
CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) ....
CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), ....
CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" ) ....

2.命令模式备份表

[oracle@dba exp_dir]$ exp USERID=SCOTT/seker TABLES=EMP,DEPT FILE=cmd_scott_tab.dmp

Export: Release 10.2.0.1.0 - Production on Mon Apr 4 17:16:22 2011

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

3.交互模式备份schema

[oracle@dba exp_dir]$ exp

Export: Release 10.2.0.1.0 - Production on Mon Apr 4 17:37:00 2011

Username: scott
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > schema_scott.dmp

(2)U(sers), or (3)T(ables): (2)U > U

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
...............
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

4.命令模式备份

schema [oracle@dba exp_dir]$ exp userid=scott/seker file=schema.scott.dmp

.............
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

5.只导出部分数据

[oracle@dba exp_dir]$ exp userid=scott/seker tables=emp query=\'where deptno=10\' statistics=none file=./emp_deptno_10.dmp log=./emp_deptno_10.log
表上有统计信息的时候会报EXP-0091错误，添加statistics=none即可
Export: Release 10.2.0.1.0 - Production on Mon Apr 4 17:59:29 2011

About to export specified tables via Conventional Path ...
. . exporting table EMP
.
3 rows exported
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

6.只导出表结构

导出时一定要加COMPRESS=n的选项否则导入表是原始表的高水位且没数据一个表大小是5M COMPRESS=n 时得到元数据: STORAGE(INITIAL 65536) COMPRESS=y(默认) 时得到元数据:STORAGE(INITIAL 5242880) 这样创建的表很大又没数据

[oracle@dba ~]$ exp scott/seker tables=ob rows=n file=./ob.dmp

[oracle@dba ~]$ exp scott/seker tables=ob rows=n COMPRESS=n file=./ob2.dmp

SQL> select segment_name,sum(bytes)/1024/1024 from user_extents where segment_name='OB' group by segment_name;

SEGMENT_NAME SUM(BYTES)/1024/1024

OB 5

SQL> drop table OB PURGE;

Table dropped.

SQL> !
[oracle@dba ~]$ ls
Desktop ob2.dmp ob.dmp
[oracle@dba ~]$ imp userid=scott/seker file=ob.dmp
[oracle@dba ~]$ exit
SQL> select segment_name,sum(bytes)/1024/1024 from user_extents where segment_name='OB' group by segment_name;

SEGMENT_NAME SUM(BYTES)/1024/1024

OB 5

SQL> !
[oracle@dba ~]$ exit
exit

SQL> drop table ob purge;

Table dropped.

SQL> !
[oracle@dba ~]$ imp userid=scott/seker file=ob2.dmp

SQL> select segment_name,sum(bytes)/1024/1024 from user_extents where segment_name='OB' group by segment_name;

SEGMENT_NAME SUM(BYTES)/1024/1024

OB .0625

SQL>

7.使用参数文件

[oracle@dba exp_dir]$ cat exp.txt
userid=scott/seker
tables=emp
query='where deptno=10'
statistics=none
file=./emp_deptno_10.dmp
buffer=100000
feedback=2
log=./emp_deptno_10.log
[oracle@dba exp_dir]$ exp PARFILE=exp.txt

Export: Release 10.2.0.1.0 - Production on Mon Apr 4 17:59:52 2011

About to export specified tables via Conventional Path ...
. . exporting table EMP
.
3 rows exported
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

当query中带有单引号时用两个引号代替

[oracle@dba exp_dir]$ cat exp.txt
userid=scott/seker
tables=emp
query='where deptno=10 and ename=''KING'''
statistics=none
file=./emp_deptno_10.dmp
buffer=100000
feedback=2
log=./emp_deptno_10.log
[oracle@dba exp_dir]$

8.闪回导出（导出历史上某一时间点的数据状态，依赖undo老镜像）:

--/home/oracle/exp.txt--
userid=system/oracle
tables=scott.emp
file=/home/oracle/expdept.dmp
flashback_time="to_timestamp('2011-03-28 15:25:06','yyyy-mm-dd hh24:mi:ss')"
buffer=100000
feedback=5000
log=/home/oracle/expdept.log

9.备份的常用参数

FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
INDEXES export indexes (Y)
TRIGGERS export triggers (Y)
LOG log file of screen output

10.导入imp

指定表导入
[oracle@dba exp_dir]$ imp userid=scott/seker file=schema.scott.dmp tables=t1 ;

Import: Release 10.2.0.1.0 - Production on Mon Apr 4 18:36:26 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T1" 24672 rows imported
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

11.导入带有外键约束的表

要连同主键表一起带入否则建立外键报错带有trigger的级联表也要这样
[oracle@dba exp_dir]$ imp userid=scott/seker file=schema.scott.dmp tables=emp ;

Import: Release 10.2.0.1.0 - Production on Mon Apr 4 18:36:59 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully with warnings.
[oracle@dba exp_dir]$

SQL> drop table emp purge;

Table dropped.

SQL>

[oracle@dba exp_dir]$ imp userid=scott/seker file=schema.scott.dmp tables=emp,dept ;

Import: Release 10.2.0.1.0 - Production on Mon Apr 4 18:38:09 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

12.导入到其他用户

这样导入到的是system模式中去了 [oracle@dba exp_dir]$ imp userid=system/oracle file=schema.scott.dmp tables=emp,dept ;

Import: Release 10.2.0.1.0 - Production on Mon Apr 4 18:41:30 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SYSTEM
. importing SCOTT's objects into SYSTEM
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

使用touser参数导入到指定用户但userid的用户必须有权限覆盖touser的权限的人才可以这样做

[oracle@dba exp_dir]$ imp userid=system/oracle touser=scott file=schema.scott.dmp tables=emp,dept ;

Import: Release 10.2.0.1.0 - Production on Mon Apr 4 18:42:01 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

导入时追加数据，表不能有主键，正常导入时先建表表存在就报错，加ignore=y选项就是忽略表存在追加数据

13.导入的常用参数

SHOW just list file contents (N)
COMMIT commit array insert (N)
BUFFER size of data buffer
很重要的两个参数大表我们必须限制使用批量提交以免回滚段小等问题
commit=y --降低导入的时候对回滚的压力。
n -- 每张表数据导入完成后提交；
y -- buffer满了以后就提交，一定要增大buffer的尺寸。

FULL import entire file (N)

14.备份表空间模式

只是备份表空间中的存储对象并不备份表空间自身的结构

[oracle@dba exp_dir]$ exp userid=system/oracle tablespaces=ts99 file=./tbs_ts99.dmp log=./tbs_ts99.log

SQL> drop tablespace ts99 including contents and datafiles;

Tablespace dropped.

SQL>

[oracle@dba exp_dir]$ imp userid=system/oracle file=./tbs_ts99.dmp full=y
.........
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TS99' does not exist
IMP-00017: following statement failed with ORACLE error 959:
.........

SQL> create tablespace ts99 datafile '/u01/oracle/oradata/ora10g/ts99.dbf' size 20M;

Tablespace created.

SQL>

[oracle@dba exp_dir]$ imp userid=system/oracle file=./tbs_ts99.dmp full=y

Import: Release 10.2.0.1.0 - Production on Tue Apr 5 03:18:47 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing U99's objects into U99
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

15.全库模式:

exp parfile=c:\exp.txt

userid=system/oracle
full=y

filesize=50m 定义文件大小，切割备份，为将来传输方便。
file=(c:\exp_full_1.dmp,c:\exp_full_2.dmp) 定义各种文件的名字，如果名字不够的话，导出进程会挂起要文件名字。
buffer=10000000
feedback=10000
log=c:\exp_full.log

imp parfile=c:\imp.txt

userid=system/oracle
full=y
filesize=50m
file=(c:\exp_full_1.dmp,c:\exp_full_2.dmp)
buffer=10000000
feedback=10000
log=c:\imp_full.log

16.表空间传输

从一个数据库中将一个表空间迁移至另一个数据库 1.将本地表空间设置为只读 2.导出表空间元数据,将导出文件和表空间包含的数据文件拿到远程 3.远程库要手动建立和本地同样的用户 4.导入表空间元数据,并指定原来的datafile 5.本地和远程表空间都恢复读写正常使用

1.将本地表空间设置为只读

SQL> alter tablespace ts99 read only;

Tablespace altered.

SQL>

2.导出表空间元数据，将导出文件和表空间包含的数据文件拿到远程

[oracle@dba exp_dir]$ exp userid=\''sys/oracle as sysdba'\' tablespaces=ts99 transport_tablespace=y file=./trs_tbs_ts99.dmp

Export: Release 10.2.0.1.0 - Production on Tue Apr 5 03:27:01 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TS99 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
. . exporting table EMP
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@dba exp_dir]$

SQL> ! cp /u01/oracle/oradata/ora10g/ts99.dbf /u01/oracle/oradata/orcl/

SQL>

3.远程库要手动建立和本地同样的用户

SQL> select file_name,file_id,tablespace_name from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME

/u01/oracle/oradata/orcl/users01.dbf 4 USERS
/u01/oracle/oradata/orcl/sysaux01.dbf 3 SYSAUX
/u01/oracle/oradata/orcl/undotbs01.dbf 2 UNDOTBS1
/u01/oracle/oradata/orcl/system01.dbf 1 SYSTEM

SQL> create user u99 identified by u99;

User created.

SQL> grant connect,resource to u99;

Grant succeeded.

SQL>

4.导入表空间元数据,并指定原来的datafile

[oracle@dba exp_dir]$ export ORACLE_SID=newdb
[oracle@dba exp_dir]$ imp userid=\''sys/oracle as sysdba'\' file=./trs_tbs_ts99.dmp tablespaces=ts99 transport_tablespace=y datafiles=\''/u01/oracle/oradata/orcl/ts99.dbf'\'

Import: Release 10.2.0.1.0 - Production on Tue Apr 5 03:36:10 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing U99's objects into U99
. . importing table "DEPT"
. . importing table "EMP"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@dba exp_dir]$

SQL> select file_name,file_id,tablespace_NAME FROM DBA_DATA_FILES;

FILE_NAME FILE_ID TABLESPACE_NAME

/u01/oracle/oradata/newdb/users01.dbf 4 USERS
/u01/oracle/oradata/newdb/sysaux01.dbf 3 SYSAUX
/u01/oracle/oradata/newdb/undotbs01.dbf 2 UNDOTBS1
/u01/oracle/oradata/newdb/system01.dbf 1 SYSTEM
/u01/oracle/oradata/orcl/ts99.dbf 5 TS99

SQL>

5.本地和远程表空间都恢复读写正常使用

SQL> alter tablespace ts99 read write;

Tablespace altered.

SQL> conn u99/u99
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID

DEPT TABLE
EMP TABLE

SQL>

相关阅读：Oracle数据库知识点分享：truncate和delete区别

点击阅读原文可以进入社区Oracle主题，还有更多文章、资料及相关问答。

长按二维码关注公众号AIX专家俱乐部

高三女生醉酒后被强奸致死？检方回应

常德悲剧：让谴责无差别杀戮之声更加响亮一点

2024【公共营养师】培训报名通道已开启，不限学历，23岁及以上可报！还能领2000补贴

【惊】"以为要写遗书"! 飞温哥华航班遇炸弹气旋华人崩溃大哭连空姐都吐了; 客机颠簸盘旋3小时

女人最偏爱的十种男人

Oracle 数据库知识点：逻辑备份

exp parfile=c:\exp.txt

imp parfile=c:\imp.txt

您可能也对以下帖子感兴趣

高三女生醉酒后被强奸致死？检方回应

常德悲剧：让谴责无差别杀戮之声更加响亮一点

2024【公共营养师】培训报名通道已开启，不限学历，23岁及以上可报！还能领2000补贴

【惊】"以为要写遗书"! 飞温哥华航班遇炸弹气旋 华人崩溃大哭 连空姐都吐了; 客机颠簸盘旋3小时

女人最偏爱的十种男人

生成图片，分享到微信朋友圈

Oracle 数据库知识点：逻辑备份

exp parfile=c:\exp.txt

imp parfile=c:\imp.txt

您可能也对以下帖子感兴趣

【惊】"以为要写遗书"! 飞温哥华航班遇炸弹气旋华人崩溃大哭连空姐都吐了; 客机颠簸盘旋3小时