需求:
1.以生产环境为基准搭建一个相似的数据库环境
一、正式环境数据准备
--进入数据库环境
1.create or replace directory dir as '/home/oracle/Documents/Databackup';
--将新建的目录对象dir授权给自己的账户myaccount
2.GRANT READ,WRITE ON DIRECTORY dir TO myaccount;
--导出需要迁移的数据schemas=myaccount表示迁移出myaccount用户下的所有数据对象
3.EXPDP myaccount/pwd schemas=myaccount directory=dir dumpfile=DataBackup_20190615.dmp logfile=DataBackup_20190615_log.txt
--将导出的数据文件远程复制传输到目标服务器位置
4.1 scp -P 22 /home/oracle/Documents/Databackup/140DataBackup_20190615.dmp oracle@127.0.0.1:/home/oracle/Documents/Databackup
4.2 scp -P 22 /home/oracle/Documents/Databackup/140DataBackup_20190615_log.txt oracle@127.0.0.1:/home/oracle/Documents/Databackup
二、测试环境数据导入
--新建表空间
1.Create tablespace sitbase006 datafile '/u01/app/oracle/oradata/SIT/sitbase006.dbf' size 7168M AutoExtend On Next 100M Maxsize Unlimited extent management local segment space management auto;
--新建用户并指定表空间
2.create user sitAccount identified by password default tablespace sitbase006 temporary tablespace temp quota unlimited on sitbase006 ;
--授权用户
3.grant alter session,create cluster,create database link,create sequence,create session,create synonym,create table,create view,create procedure,create trigger,query rewrite to sitAccount ;
grant dba to sitAccount ;
--创建目录对象dir并授权 ;
4.create or replace directory dir as '/home/oracle/Documents/Databackup';
GRANT READ,WRITE ON DIRECTORY dir TO sitAccount ;
grant dba to sitAccount ;
--从存储文件导入数据
5.impdp sitAccount/password directory=dir dumpfile=DataBackup_20190615.dmp table_exists_action=replace logfile=DataBackup_20190615_log.txt schemas=myaccount REMAP_SCHEMA=myaccount:sitAccount REMAP_TABLESPACE=sitbase001:sitbase006 exclude=STATISTICS parallel=4
异常处理:
ORA-31694、ORA-19502、ORA-27072
导入的时候出现上面的异常,反查时发现是EXPDP导出的时候,数据包损坏,磁盘空间不足。删掉了一些不必要的文件之后,再此导出、导入,问题解决。