像oracle中导入dmp格式数据库问题

这个真不知道怎么说你，system是Oracle数据库仅次于sys用户的管理账户，其中有很多对象都是依赖Oracle数据库环境的，你从其他Oracle数据库将system用户导出后要导入到到另外一个Oracle数据库里，碰到这种依赖自身数据库环境的对象，二者不一致肯定要报错的。

一个数据库创建多个实例

1 先要关闭数据库（进程和内存关闭）

[Oracle@oracle_2 ~]$ sqlplus / as sysdba

SQLPlus: Release 102010 - Production on TueNov 12 20:34:53 2013

Copyright (c) 1982, 2005, Oracle All rights reserved

Connected to:

Oracle Database 10g Enterprise Edition Release102010 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed

Database dismounted

ORACLE instance shut down

SQL> exit

2 设置环境变量

[oracle@oracle_2 ~]$ vi bash_profile

此时环境变量已经设置好了

3 创建目录

i 创建相关文件

此时我们可以看见已经有了相关目录，那是我们之前创建的实例ORCL的相关目录

创建目录是采用了批量创建目录{a,b,c,u}dump

[oracle@oracle_2 ~]$ cd $ORACLE_BASE

[oracle@oracle_2 oracle]$ ls

admin flash_recovery_area oradata oraInventory product

[oracle@oracle_2 oracle]$

[oracle@oracle_2 oracle]$ mkdir -p admin/ORA10G/{a,b,c,u}dump

[oracle@oracle_2 oracle]$ ls

admin flash_recovery_area oradata oraInventory product

[oracle@oracle_2 oracle]$ cd admin/

[oracle@oracle_2 admin]$ ls

ORA10G ORCL

[oracle@oracle_2 admin]$ cd ORA10G/

[oracle@oracle_2 ORA10G]$ ls

adump bdump cdump udump

[oracle@oracle_2 ORA10G]$

[oracle@oracle_2 ORA10G]$ ls

adump bdump cdump udump

[oracle@oracle_2 ORA10G]$ cd

[oracle@oracle_2 admin]$ ls

ORA10G ORCL

[oracle@oracle_2 admin]$ cd

[oracle@oracle_2 oracle]$ ls

admin flash_recovery_area oradata oraInventory product

[oracle@oracle_2 oracle]$ mkdir -p oradata/ORA10G

[oracle@oracle_2 oracle]$

ii 创建密码文件

创建密码文件需要到ORACLE_HOME/dbs目录下创建

[oracle@oracle_2 1020]$ cd $ORACLE_HOME/dbs

[oracle@oracle_2 dbs]$ ls

hc_ORCLdat initdwora initora lkORCL orapwORCL spfileORCLora

[oracle@oracle_2 dbs]$ orapwd

Usage: orapwd file=<fname>password=<password> entries=<users> force=<y/n>

where

file -name of password file (mand),

password- password for SYS (mand),

entries -maximum number of distinct DBA and force - whether to overwrite existing file (opt),

OPERs (opt),

There areno spaces around the equal-to (=) character

[oracle@oracle_2 dbs]$ orapwd file=orapwORA10Gpassword=oracle entries=30

[oracle@oracle_2 dbs]$ ls

hc_ORCLdat initora orapwORA10G spfileORCLora

initdwora lkORCL orapwORCL

[oracle@oracle_2 dbs]$

[oracle@oracle_2 dbs]$ cat initora |grep -v^#|grep -v ^$ >initORA10Gora

[oracle@oracle_2 dbs]$ ls

hc_ORCLdat initora lkORCL orapwORCL

initdwora initORA10Gora orapwORA10G spfileORCLora

[oracle@oracle_2 dbs]$

[oracle@oracle_2 dbs]$ vi initORA10Gora

将内容改的和下面的一样

db_name=ORA10G

db_files = 80 # SMALL

db_file_multiblock_read_count = 8 #SMALL

log_checkpoint_interval = 10000

processes = 50 # SMALL

parallel_max_servers = 5 #SMALL

log_buffer = 32768 # SMALL

max_dump_file_size = 10240 # limit trace file size to 5 Meg each

global_names = false

control_files =(/u01/app/oracle/oradata/ORA10G/ora_control1ctl,/u01/app/oracle/oradata/ORA10G/ora_control2ctl)

sga_max_size=300m

sga_target=300m

4 启动实例为ORA10G的数据库

[oracle@oracle_2 dbs]$ export $ORACLE_SID=ORA10G

[oracle@oracle_2 dbs]$ sqlplus / as sysdba

SQLPlus: Release 102010 - Production on TueNov 12 21:08:55 2013

Copyright (c) 1982, 2005, Oracle All rights reserved

Connected to an idle instance

SYS@ORA10G>

SYS@ORA10G>create spfile from pfile;

File created

SYS@ORA10G>startup nomount;

ORACLE instance started

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 96470416 bytes

Database Buffers 213909504 bytes

Redo Buffers 2973696 bytes

SYS@ORA10G>

SYS@ORA10G>show parameter undo

NAME TYPE VALUE

------------------------------------ -----------------------------------------

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string

SYS@ORA10G>

修改参数，由于undo_management参数为静态参数，所以需要加上scope=spfile

SYS@ORA10G>alter system set undo_management=autoscope=spfile;

System altered

SYS@ORA10G>show parameter undo

NAME TYPE VALUE

------------------------------------ -----------------------------------------

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string

SYS@ORA10G>shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down

此时发现并没有更改，是由于静态参数需要重启才有效

SYS@ORA10G>startup nomount;

ORACLE instance started

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 96470416 bytes

Database Buffers 213909504 bytes

Redo Buffers 2973696 bytes

SYS@ORA10G>

此时只是改了spfile的参数还需要改pfile的参数

SYS@ORA10G>create pfile from spfile;

File created

SYS@ORA10G>

5 多个实例的切换

i 实例为ORCL启动数据库

[oracle@oracle_2 dbs]$ export ORACLE_SID=ORCL

[oracle@oracle_2 dbs]$ sqlplus / as sysdba

SQLPlus: Release 102010 - Production on TueNov 12 21:19:19 2013

Copyright (c) 1982, 2005, Oracle All rights reserved

Connected to an idle instance

SYS@ORCL>

ii 实例为ORCL10G启动数据库

SYS@ORCL>exit

Disconnected

[oracle@oracle_2 dbs]$ export ORACLE_SID=ORA10G

[oracle@oracle_2 dbs]$ sqlplus / as sysdba

SQLPlus: Release 102010 - Production on TueNov 12 21:24:54 2013

Copyright (c) 1982, 2005, Oracle All rights reserved

Connected to:

Oracle Database 10g Enterprise Edition Release102010 - Production

With the Partitioning, OLAP and Data Mining options

SYS@ORA10G>

你导出工具的客户端和服务器端的数据库版本不一致，你的客户端为11g，而服务器则只是10g的，没法兼容，所以导不了数据。

建议换一个客户端（重装客户端的软件，或者找一个别人的客户端进行导，或者直接进入服务器，使用服务器机器上的oracle进行导出）

补充回答：Toad for Oracle没有使用过，不过刚才百度了一下，有其他的版本。

无错误信息啊，怎么看啊，看导入信息，导出数据库是GBK字符集，导入数据集是UTF8的，如果字符串字段里有中文，原先2个字节的存放一个汉字，现在要3个存放一个汉字，可能会出问题。建议导入导出的数据库的字符集一致。

ORACLE10G 10201升级到10205 2014-06-06 17:23:17

OS: linux redhat 4 U8 64bit

DB: oracle 10201

升级：oracle 10205

对当前环境的查询

查看磁盘空间使用情况

[root@dongyang ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 18G 11G 62G 64% /

/dev/sda1 988M 42M 896M 5% /boot

tmpfs 1002M 0 1002M 0% /dev/shm

/dev/sdb1 12G 23G 90G 21% /u01

[root@dongyang ~]#

查看 *** 作系统版本

[root@dongyang ~]# cat /proc/version

Linux version 2632-200131el5uek (mockbuild@ca-build9usoraclecom) (gcc version 412 20080704 (Red Hat 412-50)) #1 SMP Wed Jul 27 21:02:33 EDT 2011

[root@dongyang ~]# uname -r

2632-200131el5uek

[root@dongyang ~]#

查看当前数据库版本

SQL> select from v$version;

select from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 102010 - 64bi

PL/SQL Release 102010 - Production

CORE 102010 Production

TNS for Linux: Version 102010 - Production

NLSRTL Version 102010 - Production

SQL>

查看oracle用户环境变量

[oracle@dongyang ~]$ cat bash_profile

# bash_profile

# Get the aliases and functions

if [ -f ~/bashrc ]; then

~/bashrc

fi

# User specific environment and startup programs

alias uni="uniread sqlplus"

export PATH

unset USERNAME

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/1020/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export ORACLE_SID=fengzi

export NLS_LANG=AMERICAN_AMERICAZHS16GBK

export NLS_DATA_FORMAT="YYYY-MM-DD HH24:MI:SS"

export NLS_DATA_LANGUAGE="AMERICAN"

export DISPLAY=192168561:00

要先对数据库进行冷备与软件备份

备份数据库软件（磁盘空间足够）

[root@dongyang ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 18G 11G 62G 64% /

/dev/sda1 988M 42M 896M 5% /boot

tmpfs 1002M 0 1002M 0% /dev/shm

/dev/sdb1 12G 23G 90G 21% /u01

[root@dongyang ~]#

[root@dongyang ~]# mkdir /u02

[root@dongyang ~]# cd /u01

[root@dongyang u01]# cp -r /u02

关闭当前数据库

[oracle@dongyang ~]$ echo $ORACLE_SID

fengzi

[oracle@dongyang ~]$ sqlplus / as sysdba

SQLPlus: Release 102010 - Production on Thu Jun 5 13:46:04 2014

Copyright (c) 1982, 2005, Oracle All rights reserved

Connected to:

Oracle Database 10g Enterprise Edition Release 102010 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

Database closed

Database dismounted

ORACLE instance shut down

SQL>

登录RMAN 启动数据库到mount状态

[oracle@dongyang ~]$ rman target /

Recovery Manager: Release 102010 - Production on Thu Jun 5 13:48:42 2014

Copyright (c) 1982, 2005, Oracle All rights reserved

connected to target database (not started)

RMAN>startup mount

database is already started

database mounted

备份当前控制文件

RMAN> backup current controlfile;

Starting backup at 05-JUN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=322 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=+DATA/fengzi/backupset/2014_06_05/ncnnf0_tag20140605t140242_0380849448965 tag=TAG20140605T140242 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05

Finished backup at 05-JUN-14

Starting Control File and SPFILE Autobackup at 05-JUN-14

piece handle=+DATA/fengzi/autobackup/2014_06_05/s_849447979381849448969 comment=NONE

Finished Control File and SPFILE Autobackup at 05-JUN-14

备份数据库到 /u01/backup/目录下

RMAN> backup database format '/u01/backup/2014_06_01_%U';

Starting backup at 05-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=+DATA/fengzi/datafile/system277842187103

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_27pa3431_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00007 name=/u01/app/oracle/oradata/FENGZI/datafile/o1_mf_wer_9ns1wd2n_dbf

input datafile fno=00008 name=/u01/app/oracle/oradata/yyyy12dbf

input datafile fno=00009 name=/u01/app/oracle/oradata/ttdbf

input datafile fno=00004 name=+DATA/fengzi/datafile/users2dbf

input datafile fno=00005 name=+DATA/fengzi/datafile/undotbsdbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_28pa345d_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=+DATA/fengzi/datafile/sysaux279842187235

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_29pa3466_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00002 name=+DATA/fengzi/datafile/undotbs1dbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_2apa347j_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=+DATA/fengzi/datafile/users1dbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_2bpa348d_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 05-JUN-14

Starting Control File and SPFILE Autobackup at 05-JUN-14

piece handle=+DATA/fengzi/autobackup/2014_06_05/s_849447979382849449239 comment=NONE

Finished Control File and SPFILE Autobackup at 05-JUN-14

RMAN>

停止所有oracle服务，太长请自己看>

1在oracle10服务器命令行中用expdp 导出数据

expdp ts/ts@orcl directory=expdp_dir dumpfile=tianshandmp logfile=tianshanlog version=102010 (schemas=ccense)

2在oracle10g 服务器DOS 命令行中用IMPDP 导入数据：

把oracle11g 的备份文件放到oracl10g 服务器的impdp_dir 目录中，并创建好相应的用户和表空间，然后执行下面的命令：

impdp ts/ts@orcl directory=impdp_dir dumpfile=tianshandmp logfile=tianshanlog version=102010 (schemas=ccense)

运行sqlplus的步骤

同时按住”win“（一般在ctrl键和alt键中间）+”r“（字母键）

输入”cmd“，之後按回车（enter）

在命令行输入”sqlplus“，进入oracle的sqlplus，输入数据库的用户名和密码进行登录 *** 作

oracle的sqlplus是与oracle进行交互的客户端工具，借助sqlplus可以查看、修改数据库记录。在sqlplus中，可以运行sqlplus命令与sql语句。

以上就是关于像oracle中导入dmp格式数据库问题全部的内容，包括:像oracle中导入dmp格式数据库问题、oracle10G怎么里怎么创建多个数据库、oracle导出服务器数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！