Oracle数据库备份操作测试

精选原创

mb636d9e4ce84d1 2023-03-20 10:27:28 ©著作权

文章标签 数据库 oracle exp expdp Processing 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者mb636d9e4ce84d1的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

备份分类
exp/imp测试

exp数据导出
imp数据导入
导出用户A数据导入到用户B

expdp/impdp

expdp导出测试
impdp导入测试

RMAN

备份分类

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

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

本文重点测试 exp/expdp imp/impdp
exp和imp是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
expdp和impdp是服务端的工具程序，他们只能在oracle服务端使用，不能在客户端使用。
对于10g以上的服务器，使用exp通常不能导出0行数据的空表，而此时必须使用expdp导出
imp只适用于exp导出的文件，不适用于expdp导出文件；impdp只适用于expdp导出的文件，而不适用于exp导出文件

exp/imp测试

exp数据导出

这里创建一个expuser用户，并且赋予如下如下权限 ,我们这里没有给该用户dba权限，所以只能导出自己的数据

select * from session_privs
PRIVILEGE           |
--------------------|
CREATE SESSION      |
UNLIMITED TABLESPACE|
CREATE TABLE        |
EXPORT FULL DATABASE|

创建两张表，并插入测试数据

Oracle数据库备份操作测试_oracle

执行导出操作,可以看到数据导出，看到两条告警"EXP-00091: Exporting questionable statistics."
注意：direct=y recordlength=65535 buffer=104857600 这些参数可以优化导出效率。

[oracle@oracle100 ~]$ exp userid=expuser/expuser direct=y owner=expuser recordlength=65535 buffer=104857600 file=./exptest.dmp log=./exp20120218.log feedback=10000

Export: Release 11.2.0.1.0 - Production on Sat Jun 19 19:41:24 2021

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EXPUSER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EXPUSER
About to export EXPUSER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export EXPUSER's tables via Direct Path ...
. . exporting table NEWTABLE
1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TB01
2 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@oracle100 ~]$ ll
total 132
-rw-r--r-- 1 oracle oinstall 1652 Jun 19 18:47 exp20120218.log
-rw-r--r-- 1 oracle oinstall 131070 Jun 19 18:47 exptest.dmp
drwxr-xr-x 2 oracle oinstall 6 Jun 13 23:58 orcl_bak

百度得知是因为 exp 执行环境的 NLS_LANG 环境变量设置和服务器端的设置不同
执行端

[oracle@oracle100 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

服务服务器端

select userenv('language') from dual;
USERENV('LANGUAGE')                  |
-------------------------------------|
SIMPLIFIED CHINESE_CHINA.WE8MSWIN1252|

执行导出前增加环境变量设置即可避免警告

[oracle@oracle100 ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

导出结果如下：

[oracle@oracle100 ~]$ ll
total 132
-rw-r--r-- 1 oracle oinstall   1498 Jun 19 19:49 exp20120218.log
-rw-r--r-- 1 oracle oinstall 131070 Jun 19 19:49 exptest.dmp
drwxr-xr-x 2 oracle oinstall      6 Jun 13 23:58 orcl_bak

exp 有一个特点就是如果表里面没有数据，那么导出时不会导出这张表，这个要记住

imp数据导入

我们在两张表中分别再次插入一条数据,模拟数据修改,然后使用expuser用户进行用户数据导入
发现导入报错，提示表已经存在所以，imp数据导入不能直接覆盖原有数据。

[oracle@oracle100 ~]$ imp expuser/expuser  fromuser=expuser touser=expuser file=./exptest.dmp log=./imp.log

Import: Release 11.2.0.1.0 - Production on Sat Jun 19 20:03:25 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V11.02.00 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "NEWTABLE" ("NAME" VARCHAR2(100), "SEX" VARCHAR2(100))  PCTFRE"
 "E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 "
 "MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)            "
 "        LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "TB01" ("ID" VARCHAR2(100), "NAME" VARCHAR2(100))  PCTFREE 10 "
 "PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEX"
 "TENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 "
 "   LOGGING NOCOMPRESS"
Import terminated successfully with warnings.
You have new mail in /var/spool/mail/root

删除expuser用户下的两张表再次导入，提示导入成功

[oracle@oracle100 ~]$ imp expuser/expuser  fromuser=expuser touser=expuser file=./exptest.dmp log=./imp.log

Import: Release 11.2.0.1.0 - Production on Sat Jun 19 20:05:56 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V11.02.00 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table                     "NEWTABLE"          1 rows imported
. . importing table                         "TB01"          2 rows imported
Import terminated successfully without warnings.

导出用户A数据导入到用户B

继续使用上述 expuser 导出的数据，新建userb赋予和expuser相同权限。导入一般由人工操作，这里我们使用dba权限的用户来做导入，如下所示，导入成功

[oracle@oracle100 ~]$ imp zxm/zxm  fromuser=expuser touser=userb  file=./exptest.dmp log=./imp2.log
Import: Release 11.2.0.1.0 - Production on Sat Jun 19 20:18:43 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V11.02.00 via direct path
Warnng: the objects were exported by EXPUSER, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing EXPUSER's objects into USERB
. . importing table                     "NEWTABLE"          1 rows imported
. . importing table                         "TB01"          2 rows imported
Import terminated successfully without warnings.

实际场景总我们往往是自动化执行导出操作，人工执行导入操作。因此导出操作使用的用户最好权限控制做到最小,且不影响程序用户。而且要考虑数据导出和导入时，用户表空间不一致的情况，后续有空再做进一步测试

expdp/impdp

expdp导出测试

准备安装好的oracle主机，切换到oralce 用户执行命令

[oracle@oracle100 db]$ cd ~
[oracle@oracle100 ~]$ ll
total 0
[oracle@oracle100 ~]$ mkdir orcl_bak
[oracle@oracle100 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 10 04:24:54 2021
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create directory  back_dir as '/home/oracle/orcl_bak';
Directory created.
SQL> grant read,write on directory back_dir to zxm;

Grant succeeded.

导出用户数据
在新终端中执行导出命令如下,可以看到导出成功，这里我们只导出了用户 zxm 的数据，也可以根据表空间等进行导出

[oracle@oracle100 ~]$  expdp /  dumpfile=exp01.dmp schemas=zxm logfile=explog.log;

Export: Release 11.2.0.1.0 - Production on Thu Jun 10 04:39:52 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

UDE-01017: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied

Username: zxm
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "ZXM"."SYS_EXPORT_SCHEMA_01":  zxm/******** dumpfile=exp01.dmp schemas=zxm logfile=explog.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ZXM"."TEST"                                5.023 KB       1 rows
Master table "ZXM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZXM.SYS_EXPORT_SCHEMA_01 is:
  /db/app/oracle/admin/orcl11g/dpdump/exp01.dmp
Job "ZXM"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:40:24

You have new mail in /var/spool/mail/root
[oracle@oracle100 ~]$ ll /db/app/oracle/admin/orcl11g/dpdump/
total 180
-rw-r----- 1 oracle oinstall    116 Jun  9 04:02 dp.log
-rw-r----- 1 oracle oinstall 176128 Jun 10 04:40 exp01.dmp
-rw-r--r-- 1 oracle oinstall   1631 Jun 10 04:40 explog.log

导出全量数据
可以看到这次数据导出到了传入的目录 /home/oracle/orcl_bak/

[oracle@oracle100 ~]$  expdp zxm/zxm dumpfile=expdp_full01.dmp directory=back_dir full=y logfile=expdp.log
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "ZXM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZXM.SYS_EXPORT_FULL_01 is:
  /home/oracle/orcl_bak/expdp_full01.dmp
Job "ZXM"."SYS_EXPORT_FULL_01" successfully completed at 05:00:19

导出表空间数据

查询表空间

SQL> select tablespace_name ,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

TABLESPACE_NAME                                                      MB
------------------------------------------------------------ ----------
UNDOTBS1                                                             30
SYSAUX                                                              480
USERS                                                                35
SYSTEM                                                              680

[oracle@oracle100 ~]$ expdp zxm/zxm tablespaces=USERS dumpfile=expdp_tsp01.dmp directory=back_dir  logfile=expdp.log

导出表数据

[oracle@oracle100 ~]$ expdp zxm/zxm tables=test dumpfile=expdp_tb01.dmp directory=back_dir  logfile=expdp.log

impdp导入测试

导入时，需要指定上述逻辑目录，oracle到逻辑目录中获取数据文件。
导入全量数据库
impdp zxm/zxm directory=data_dir dumpfile=expdp.dmp full=y

用户zxm数据导入给zxm

impdp zxm/zxm schemas=zxm directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

用户A数据导入给用户B
impdp B/passwd tables=A.table1,A.table2 remap_schema=A:B directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

将表空间TBS01、TBS02导入到表空间A_TBS，将用户B的数据导入到A，并生成新的oid防止冲突；
impdp A/passwd remap_tablespace=TBS01:A_TBS,TBS02:A_TBS remap_schema=B:A FULL=Y transform=oid:n directory=data_dir dumpfile=expdp.dmp logfile=impdp.log

导入表空间
impdp zxm/zxm tablespaces=tbs1 directory=data_dir dumpfile=expdp.dmp logfile=impdp.log