oracle使用expdp/impdp对几百G的大量数据进行迁移

最新推荐文章于 2024-07-04 21:30:00 发布

独孤尚良dugushangliang

最新推荐文章于 2024-07-04 21:30:00 发布

阅读量7.4k

点赞数 4

分类专栏： ORACLE 文章标签： oracle impdp expdp 大量数据迁移

本文链接：https://blog.csdn.net/dugushangliang/article/details/89531597

版权

ORACLE 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

本例是采用数据泵的方式，把A机器数据库中的数据迁移到B机器的新建数据库中。首先肯定是要在新机器安装oracle数据库并调试好，这方面如有问题请参阅本人相关文章，这里不再赘述。本例的两台机器都是win系统，oracle都是11g版本。

首先是要A机器expdp导出数据，本例是导出某用户下所有的表。这需要先在A机器上执行sql语句设置directory：

create directory oracleDB as 'D:\temp\q';

附注：
oracleDB是directory名
'D:\temp\q'是创建的这个directory指向路径

创建后授予读写权限给用于导出数据的用户。

Grant read,write on directory oracleDB to 用户;

附注：
此处的用户使用具有dba权限的用户，即现在的登录用户。

此处附上关于directory的操作，来源：http://unixso.com/Oracle/directory.html

--查询所有directory
select * from dba_directories;

--创建directory
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

--修改directory
create or replace directory dumpdir as '/home/dumpfiles';

--授权directory
grant read,write on directory dumpdir to username;

--删除directory
drop directory DIRENAME;

授权后可关闭sql窗口，打开cmd执行expdp数据泵导出操作。

expdp 用户/密码@192.168.1.120/orcl owner=LOCATIONUSER directory=oracleDB dumpfile =qy424.dmp logfile=qy424.log;

附注：
用户/密码和上文保持一致，即当前登录oracle所用的用户及密码。

192.168.1.120是本例的A机器的host。
owner是指定导出哪个用户下的所有数据。
directory是指定导出数据到哪里，oracleDB即代表上文设置的路径。
dumpfile是导出文件名，建议同变量设置一样，首位用英文字母，后可接英文字母、阿拉伯数字，后缀为.dmp。
logfile是导出数据时的日志文件。

这个过程会因为数据量大而出现所谓的假死现象，意即：如下图所示，感觉cmd不再显示信息，导出文件也没变大，让人觉得好像好久没有进展。

注意：如果没有报错，则只需要耐心等待，过一会cmd就会出现许多导出动态。提示导出成功后，经检查无误，把dmp文件从A机器转移到B机器，本例因数据量较大，所以选择通过移动硬盘来完成从A机器复制dmp文件到B机器的操作。

不要忘了查询导出数据所在表空间，本例是导出的locationuser用户的所有表，所以查询这个用户对应的表空间。

select username,default_tablespace from dba_users;

查这个表空间是为了在B机器的新数据库中创建一个同名的表空间。

比如：A机器上，用system用户导出locationuser用户的所有表，这些表在locationuser_data表空间中，则导入到B机器上时，用system用户导入，导入到locationuser_data表空间。用impdp导入不需要locationuser用户已存在，最好是不存在，因为impdp导入时会试着新建这个用户的。

导入数据之前先创建同名的表空间，本例即locationuser_data表空间。A机器上此表空间对应的dbf文件较大，上百G，本人在此踩坑多次。

本例创建bigfile表空间，参考操作可直接跳过下面的问题解决办法。

————下为踩坑问题解决办法————

直接创建表空间后，执行impdp导入到新数据库时发生错误。错误为：

ORA-01658无法为表空间中的段创建INITIAL区

现在导入操作进展不下去了，没办法，只好打算删除新创建的locationuser用户和locationuser_data表空间重新来执行操作。在删除用户的时候，被提示：ORA-01940:无法删除当前已连接的用户。找了好些帖子，然而查询了oracle各用户的连接情况，并没有要删除的这个用户。只好重启oracle服务进程或机器，又发现再次登录被提示发生错误，提示为：ORA-01033: ORACLE initialization or shutdown in progress，即：ORA-01033: ORACLE正在初始化或关闭。解决办法如下：

https://blog.csdn.net/qq_31250157/article/details/54340792或https://blog.csdn.net/rrrrroy_ha/article/details/80601497

后来经过原数据库的表空间和新数据库的表空间的属性对比，对于ora-01658这个问题，发现是bigfile这个参数值为no导致的。即空间不足是直接原因，bigfile的值no是不能向普通表空间导入大量数据的根本原因。

当时创建这个表空间的语句为：

create tablespace LOCATION_DATA
logging  
datafile 'E:\app\Administrator\oradata\orcl\LOCATION_DATA.dbf' 
SIZE 5000M 
autoextend on  
next 50m maxsize UNLIMITED  
extent management local;

经查验，表空间出问题时，容量已用99.9%以上，此时大小在32GB左右，这应该是不能再向其中导入数据而出现了问题。原数据库的表空间大小为近200GB，结合http://blog.itpub.net/17203031/viewspace-702593/所言，即如下图所示，可能症结就在这里。即：bigfile这个参数不是yes，即使扩展无最大限制，也会受制于32G。

所以本例需要向bigfile参数为yes的表空间中进行导入的操作。

本人又删除了这个用户和表空间，又重新创建bigfile表空间。

————上为踩坑问题解决办法————

创建bigfile表空间的sql语句如下。

CREATE BIGFILE tablespace LOCATION_DATA
logging  
datafile 'E:\app\Administrator\oradata\orcl\LOCATION_DATA.dbf' 
SIZE 5000M 
autoextend on  
next 50m maxsize UNLIMITED  
extent management local;

创建后执行sql语句：

SELECT * FROM dba_tablespaces;

此时新建表空间的bigfile是yes则可执行下一步，否则还会报错：ORA-01658无法为表空间中的段创建INITIAL区。

设置了bigfile后再执行sql语句：

impdp 用户/密码 DIRECTORY=dpdata1 DUMPFILE=qy424.dmp SCHEMAS=LOCATIONUSER


用户/密码最好用和expdp一样的，具有dba权限的。
dpdata1是设置的需要导入的文件的路径
qy424.dmp是expdp导出的文件，也是需要导入的文件
LOCATIONUSER是新建的bigfile为yes的表空间