oracle11g-R2数据库的逻辑备份（数据泵的导入导出）

一、环境：

server1迁移到server2

server1：

服务器号：201

系统：Windows server 2008 R2 x64

IP地址：192.168.2.201

oracle数据库版本：oracle 11g R2

端口号：1521

用户名：brdb_1031

密码：nc2015

迁移到

server2：

服务器号：207

系统：centos7-x64

IP地址：192.168.2.207

oracle数据库版本：oracle 11g R2

oracle安装平台：阿里云docker镜像

端口号：1521

二、准备：

1、在备份前，先检查两个数据库的字符集是否相等

SQL语句：

select userenv('language') from dual;

server1字符集

server2字符集

2、修改server2字符集

AL32UTF8字符集修改为ZHS16GBK

执行如下SQL语句就可修改：

select userenv('language') from dual;
shutdown immediate;
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database flashback off;
alter database open;
show parameter recovery;
alter system reset db_recovery_file_dest  scope=spfile sid='*';
alter system reset db_recovery_file_dest_size scope=spfile sid='*';
alter database character set internal_use ZHS16GBK;
shutdown immediate
startup
exit

演示方法二：

连接数据库

$ sqlplus / as sysdba

[oracle@dev /]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 31 09:34:26 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL>

查看字符集

SQL> select userenv('language') from dual;

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL>

关闭数据库

SQL> shutdown immediate;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

装载启动数据库到mount状态

SQL> startup mount

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             402655344 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7360512 bytes
Database mounted.

执行如下命令

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set       # 提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：


SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.

SQL>

我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的，也就是说INTERNAL_USE提供的帮助就是使Oracle数据库绕过了子集与超集的校验

关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             402655344 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7360512 bytes
Database mounted.
Database opened.

查看字符集

报错：

SQL> select userenv(‘language’) from dual;
select userenv(‘language’) from dual
               *
ERROR at line 1:
ORA-00911: invalid character


SQL>
SQL>
SQL>
SQL> select userenv(‘language’) from dual;
select userenv(‘language’) from dual
               *
ERROR at line 1:
ORA-00911: invalid character


SQL>

稍等几分钟自动恢复

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL>
SQL>

数据库字符集修改完毕

三、数据库的导出（expdp）

server1：

注意：数据库的导出和导入都在Windows命令模式下执行

1、数据库的导出（expdp）

导出某个用户的表空间

语法如下：

expdp 导出用户名/密码@数据库的SID

expdp 登陆用户名/密码@数据库的IP/orcl

schemas=指定导出用户名

dumpfile=文件名.dmp

logfile=日志名.log

directory=备份储存路径名

tables=表名（只导出指定的某张表）

_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp    （win系统显示日期）

_$(date +%Y%m%d-%H-%M)       [linux 系统显示日期]

实例：

expdp brdb_1031/nc2015@orcl dumpfile=brdb_1031_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp logfile=brdb_1031_%date:~0,4%-%date:~5,2%-%date:~8,2%.log directory=backfile

说明：备份存储路径名可以自己创建也可以查看已有的路径

查看系统中所有的路径

SQL语句：

select * from dba_directories

导出结果如下：

2、只导出指定的某张表

tables=表名

实例：

expdp brdb_0530/nc2015@orcl tables=SYS_USER dumpfile=SYS_USER_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp directory=BACKFILE

导出结果如下：

3、只导入某张表

 实例：

impdp brdb_0530_ys/123456@orcl dumpfile=SYS_USER_2019-07-17.dmp directory=BACKFILE table_exists_action=replace remap_tablespace=BRDB1031TABLESPACE:brdb_0530_ys remap_schema=brdb_0530:brdb_0530_ys

四、数据库的导入（impdp）

server2

数据库的导入：

SQL语句：

1、创建物理路径

自行创建一个完整的目录文件夹，用于存放dmp文件

例如：

/oracle-backup-import

创建一个完整的目录文件夹，用于存放表空间

例如：

/oracle-tablespace

2、创建逻辑目录

注意：路径一定要和物理路径是对应关系

语法：

create directory 路径名 as '路径'

实例：

import导入备份文件路径名

create directory import__dir as '/oracle-backup-import'

表空间存放文件路径名

create directory oracle_data as '/oracle-tablespace'

3、创建表空间

语法：

create tablespace 表空间名

datafile ‘物理地址（相当于文件路径）’

size 初始大小（单位M）

autoextend on next 每次自增的大小（单位M）

maxsize unlimited （此关键字用于不限制表空间大小）

实例：

create tablespace brdb_1031
datafile '/oracle-tablespace/brdb_1031_tablespace.dbf'
size 200M
autoextend on next 100M
maxsize UNLIMITED;

注意：

在Linux系统中需要给目录授权，否则报错：permission denied

# chown oracle:oinstall oracle-backup-import

# chown oracle:oinstall oracle-tablespace/

[root@dev /]# chown oracle:oinstall oracle-backup-import
[root@dev /]# chown oracle:oinstall oracle-tablespace/
[root@dev /]#
[root@dev /]# ll
total 24
dr-xr-xr-x.   2 root   root     4096 Dec  3  2013 bin
dr-xr-xr-x.   4 root   root       29 Dec  3  2013 boot
drwxr-xr-x.   5 root   root      340 May 31 09:14 dev
drwxr-xr-x.   1 root   root       21 May 31 09:18 etc
drwxr-xr-x.   1 root   root       20 Aug 29  2014 home
dr-xr-xr-x.   8 root   root      168 Aug 23  2014 lib
dr-xr-xr-x.   7 root   root     8192 Aug 23  2014 lib64
drwxr-xr-x.   2 root   root        6 Sep 23  2011 media
drwxr-xr-x.   2 root   root        6 Sep 23  2011 mnt
drwxr-xr-x.   2 root   root        6 Sep 23  2011 opt
drwxr-xr-x.   2 oracle oinstall    6 May 31 09:02 oracle-backup-import
drwxr-xr-x.   2 oracle oinstall    6 May 31 09:08 oracle-tablespace
dr-xr-xr-x. 249 root   root        0 May 31 09:14 proc
dr-xr-x---.   3 root   root      124 Aug 26  2014 root
dr-xr-xr-x.   2 root   root     4096 Aug 23  2014 sbin
drwxr-xr-x.   2 root   root        6 Sep 23  2011 selinux
drwxr-xr-x.   2 root   root        6 Sep 23  2011 srv
dr-xr-xr-x.  13 root   root        0 May 26 09:46 sys
drwxrwxrwt.   1 root   root        6 May 31 09:14 tmp
drwxr-xr-x.   1 root   root       17 Aug 23  2014 usr
drwxr-xr-x.   1 root   root       17 Aug 23  2014 var
[root@dev /]#

 查看已经创建的表空间文件

[root@dev /]# cd oracle-tablespace/
[root@dev oracle-tablespace]#
[root@dev oracle-tablespace]# ll
total 204808
-rw-r-----. 1 oracle oinstall 209723392 May 31 11:32 brdb_1031_tablespace.dbf

4、创建用户并指定表空间

语法：

create user 用户名 identified by 口令[即密码] default tablespace 表空间名;

实例：

create user brdb_1031 identified by 123456 default tablespace brdb_1031

5、目录授权

语法：

grant read,write on directory 路径名 to 用户;

实例：

grant read,write on directory oracle_data to brdb_1031

6、用户授权

一般情况下，我们可以直接赋予角色三种权限connect、resource、dba

语法：

grant 权限1, 权限2, 权限3…… to 用户名

实例：

grant connect, resource, dba to brdb_1031

7、查询数据库用户信息

select * from dba_users

在导出服务器上查询，主要看表空间名（server1）

8、导入dmp文件

server2：
语法：
impdp 用户名/密码@数据库的SID

directory=备份文件存放位置

dumpfile=导出的文件名

logfile=导出的日志名

schemas=指定导入用户名

remap_tablespace=转换表空间（原表空间：新表空间,多个转换用逗号隔开）

remap_schema=转换用户名（原用户名：新用户名）

Oracle11g使用数据泵方式导入出现ORA-39151错误时
导入的数据库中已经有相同的用户名和老旧的表
可以在后边加上参数
table_exists_action=replace（若表存在则替换）

table_exists_action=append/truncate/replace    

--append为追加数据

truncate为先删除原表数据再插入数据

replace先drop表，然后创建表，最后插入数据（建议使用replace）

nologfile=y （不写入日志文件）

exclude=user（忽略用户对象已经存在的错误）

tables=表名（只导入指定的某张表）
实例：

$ impdp brdb_1031/123456@helowin directory=IMPORT_DIR dumpfile=BRDB_1031_2019-05-31.DMP logfile=brdb_1031_2019-05-31.log remap_tablespace=BRDB1031TABLESPACE:brdb_1031

 导入完成，时间较慢，耐心等待

9、相同数据库的备份还原

注意：导入数据前需要先删除序列

实例：

impdp ys_nk/nc2015@orcl directory=BACKUP dumpfile=YS_NK_2019-06-14.DMP table_exists_action=replace

table_exists_action=replace（先删除原来的表，然后创建表，最后插入数据）

10、tables=表名（只导入指定的某张表）

实例：

impdp brdb_bjsc_0718/cnbi2018@orcl directory=dmpdir2 dumpfile=BRDB_BJSC_0718-2019-07-02.DUMP tables=(brdb_bjsc_0718.sys_user,brdb_bjsc_0718.SYS_USERCOMPANY) table_exists_action=replace

说明：全库备份文件只恢复某张表的情况

end