记录一次将dmp文件导入oracle数据库(本地导线上)，所遇到的问题及解决方法

原创 2023-08-25 12:28:41 8.9k 阅读 AI写同款

收录于

oracle

if时光重来

码龄8年墨衍会员 · AI 创作全网分发

248粉丝217篇原创

关注

Oracle 导入导出 dmp 数据文件实战 DMP文件操作指南摘要 DMP文件是Oracle数据库专用的二进制文件，用于数据备份、迁移和恢复。通过expdp/impdp或exp/imp工具生成，包含元数据和实际数据。导出时可通过并行处理、压缩和加密优化性能及安全性；导入时支持用户重映射、表空间调整等参数。关键操作包括全库/用户/表三种导入模式，并需注意字符集兼容性。高级场景可采用增量同步（按条件筛选变更数据）或分段导出提升效率，同时结合日志记录确保流程可追溯。传统工具（exp/imp）适用于低版本，而数据泵工具（expdp/impdp）提供更强大的功阅读详情

AI摘要

本文介绍了如何在已安装Oracle数据库的环境中安装InstantClient以进行数据导入操作。通过Navicat连接Oracle数据库，创建表空间、用户并分配权限。详细步骤包括创建表空间、增加数据文件、创建用户并分配CONNECT、RESOURCE、DBA角色。随后，使用IMP命令导入DMP文件，并解决导入过程中可能出现的统计信息、权限和表空间不足的问题。还涵盖了表空间迁移的方法，包括离线、修改数据文件路径、在线等步骤。

安装oracle环境(instantclient)

因线上已安装oracle完成版数据库，则本地只需安装instantclient即可进行导入数据操作
具体安装步骤请参考：https://blog.youkuaiyun.com/admin_web/article/details/125103786

使用navicat工具查看oracle数据

打开navicat工具后在菜单栏中选择文件->新建连接->oracle，输入连接的地址、端口、服务名、用户名密码。
服务名是安装oracle时设置的，默认是ORCL
在这里插入图片描述
如需使用sysdba用户选择高级后将角色选择为sysdba

准备开始导入工作

1.登录到线上服务器oracle数据库，使用sqlplus / as sysdba超管角色进入后新建表空间、用户以及分配给用户分配权限
创建表空间

（表空间每次自增200m）

create tablespace MY_TAB datafile 'D:\oracle\oradata\DANTE_TEMP.DBF' size 4096M AUTOEXTEND ON NEXT 200M

给同一个表空间配置多个存储文件

-- 根据需要添加一定数量的数据文件
alter tablespace MY_TAB add datafile 'D:\oracle\oradata\DANTE_TEMP2.dbf' size 320m autoextend ON next 100 m maxsize unlimited;
alter tablespace MY_TAB add datafile 'D:\oracle\oradata\DANTE_TEMP3.dbf' size 300m autoextend ON next 100 m maxsize unlimited;
alter tablespace MY_TAB add datafile 'D:\oracle\oradata\DANTE_TEMP4.dbf' size 300m autoextend ON next 100 m maxsize unlimited;

创建用户并为其分配表空间

create user mytest（用户名） identified by 123456（密码） default tablespace MY_TAB（表空间名）;

给用户分配权限

GRANT CONNECT,RESOURCE,DBA TO c##mytest;

Connect 角色
是授予最终用户的典型权利，最基本的权利，能够连接到Oracle数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。

Alter session–修改会话；

Create cluster–建立聚簇；

Create database link–建立数据库连接；

Create sequence–建立序列；

Create session–建立会话；

Create synonym–建立同义词；

Create view–建立视图。

Resoure角色
是授予开发人员的，能在自己的方案中创建表、序列、视图等。

Create cluster–建立聚簇；

Create procedure–建立过程；

Create sequence—建立序列；

Create table–建表；

Create trigger–建立促发器；

Create type–建立类型。

DBA角色
是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。

2.使用imp命令将本地dmp文件导入至线上oracle中
使用imp -help命令可查看所有参数
imp 用户名/密码@ip地址:端口号/服务名 file=需导入的dmp文件 full=y indexes=N log=日志 statistics=none

full=y ：导入文件全部数据，
indexes=N ：是否导入表索引 N否 Y是，默认Y
rows=N ：导入数据行 (Y) ，N只导入表结构
statistics=none ：不导入或者重新统计数据库统计信息
DATA_ONLY=Y ：只导入数据

imp mytest/123456@10.4.xx.xxx:1521/ORCL file=D:\upload_file\orcl1.dmp full=y indexes=N log=D:\upload_file\orcl.log statistics=none

只导出表机构

exp mytest/123456@127.0.0.1/orcl file=D:\tablejg.sql rows=n  //导出表结构
exp mytest/123456@127.0.0.1/orcl file=D:\a.dmp tables=user // 导出指定的表

使用bat批量将dmp文件导入至服务器
如图：将需导入的dmp文件放置同一目录中
在这里插入图片描述
a.bat

@echo off

setlocal EnableDelayedExpansion
set localPath=./
set host=xx.x.xx.xxx
set user=mytest
set pwd=123456
set num=0

for /r %localPath% %%i in (*.dmp) do (
    set /a num+=1
    imp %user%/%pwd%@%host%/ORCL file=%%i full=y statistics=none indexes=N DATA_ONLY=Y log=%localPath%\!num!_s2.log
)


pause

导入时所遇到的问题

问题------------

在这里插入图片描述
原因分析：经过查询，发现这个错误发生的原因在于数据库统计信息。但是更深层次的原因，还不清楚。暂时理解的是，由于默认的导入策略是statistics=always，这个策略要求的是一定要导入原先的数据库统计信息，这个策略，还是觉得有点问题，数据库统计信息，由于是使用了新的数据库版本，原先的统计信息，基本上80%左右的统计信息应该是不正确的，所以为什么导入策略还要默认是statistics=always呢。所以，我的决定是不导入或者重新统计数据库统计信息。
参考：https://blog.youkuaiyun.com/weixin_35986494/article/details/116441481
解决方法：在导入是加上statistics=none

问题------------
在这里插入图片描述
原因分析：可能是由于权限或者导入的dmp文件中的表字段和服务器上的表字段不一致（我所遇到的是后者）
解决方法：
1.统一需要的表字段后重新导出
2.将缺少的字段追加到导入的数据表中，如上图：将"DATASRC"字段追加到导入的表中

问题------------
IMP-00058: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 16 in tablespace OLDAGE6110
在这里插入图片描述
原因分析：表空间不足/当前文件所在盘符空间不足
解决方法：
如果是表空间不足使用以下语句添加

alter tablespace JNINFO add datafile '/opt/oracle/oradata/orcl/JNINFO02.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;

如果是盘符空间不足，扩大盘符空间或者将表空间迁移至空间充足的盘符中
表空间前移详细参考：https://blog.youkuaiyun.com/weixin_41607523/article/details/122414853

一、数据库本地迁移
1.登录对应数据库
SQL>sqlplus / as sysdba

2.确认表空间以及要迁移的空间
[root@inlinkdb1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.7G 0 3.7G 0% /dev
tmpfs 3.7G 0 3.7G 0% /dev/shm
tmpfs 3.7G 648K 3.7G 1% /run
tmpfs 3.7G 0 3.7G 0% /sys/fs/cgroup
/dev/vda1 40G 12G 26G 33% /
tmpfs 756M 0 756M 0% /run/user/0
tmpfs 756M 0 756M 0% /run/user/1000
/dev/vdb1 689G 639G 16G 98% /oracle
/dev/vdb2 319G 13G 291G 5% /oracle2
如上所示/oracle目录已达到98%，现实现目标将/oracle目录的表空间迁移到/oracle2下

SQL> @/home/oracle/tbs

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)

SYSTEM 350 44800 340.06 97.16 9.94
SYSAUX 2350 300800 2228.06 94.81 121.94
DATA 10240 1310720 8833.75 86.27 1406.25
USERS 35071.25 4489120 27328.37 77.92 7742.88
WB_*** 10240 1310720 7058.37 68.93 3181.63
KETTLE_DATA 61440 7864320 29705.06 48.35 31734.94
UNDOTBS1 5770 738560 2400.81 41.61 3369.19
flexsche_data 20480 2621440 6667.19 32.55 13812.81
****_DATA 1024 131072 144.87 14.15 879.13
****_DATA 3244 415232 325.19 10.02 2918.81
***_DATE 1024 131072 13.5 1.32 1010.5

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)

****_DATA 1024 131072 9.87 .96 1014.13
****_DATA 10240 1310720 4.12 .04 10235.88
****_TEMP 1024 131072 0
****DATA_TEMP 1024 131072 0
TEMP 795 101760 0
TEMP 1024 131072 0
WB_TEMP 1024 131072 0
****_TEMP 1024 131072 0
****_TEMP 1024 131072 0
****_TEMP 1024 131072 0
KETTLE_TEMP 3072 393216 0

22 rows selected.

SQL>
3.确认要迁移表空间位置
示例：USERS

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files where tablespace_name =‘USERS’;

FILE_NAME FILE_ID TABLESPACE_NAME

/oracle/app/oracle/oradata//users01.dbf 6 USERS
/oracle/app/oracle/oradata//users02.dbf 7 USERS
4.将表空间状态设为offline
SQL>alter tablespace USERS offline;

Tablespace altered.
5.拷贝dbf数据文件到新路径
注：USERS现有所有dbf文件都要拷贝哦

[oracle@***db1 ~]$ cp /oracle/app/oracle/oradata//users01.dbf /oracle2/datafile/
[oracle@*db1 ~]$ cp /oracle/app/oracle/oradata//users02.dbf /oracle2/datafile/
6.数据库中修改表空间指向地址
SQL>alter database rename file '/oracle/app/oracle/oradata//users01.dbf’ to '/oracle2/datafile//users01.dbf’;

Database altered.
SQL>alter database rename file ‘/oracle/app/oracle/oradata//users02.dbf’ to '/oracle2/datafile/**/users02.dbf’;

Database altered.
7.表空间状态设为online
SQL> alter tablespace USERS online;

Tablespace altered.
8.确认变更结果
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files where tablespace_name =‘USERS’;

FILE_NAME FILE_ID TABLESPACE_NAME

/oracle2/datafile//users01.dbf 6 USERS
/oracle2/datafile//users02.dbf 7 USERS
9.原路径位置删除dbf数据文件
[oracle@*****db1 ~]$ rm -rf /oracle/app/oracle/oradata//users01.dbf
[oracle@*****db1 ~]$ rm -rf /oracle/app/oracle/oradata//users02.dbf