oracle import full,Oracle 10.2.0.1 exp 之 full database export and import(实战)

全库导出，导入实战(数据库版本需相同)

1

、全库导出

[ora@dg-ss ~]$

exp system/oracle@test file=/home/ora/full.dmp full=y

2

、查看源库的表空间

sys@TEST>

select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

DOUDOU

3

、迁移dmp文件到恢复库

[ora@dg-ss ~]$

scp full.dmp

ora@192.168.0.16:/home/ora

4

、恢复库建立表空间(源库与恢复库对比，如需建立表空间，请建立)

建立空间小技巧(源库执行)

永久表空间

select 'create tablespace  ' || tablespace_name ||' datafile '''||replace(file_name,'源库数据文件目录/','目的

库数据文件目录/') ||'''size 500m autoextend on ;' from dba_data_files ;

临时表空间

select 'create temporary tablespace  ' || tablespace_name ||' tempfile '''||replace(file_name,’

源库数据文件目录/','目的

库数据文件目录/') ||'''size 500m autoextend on ;' from dba_temp_files ;

恢复库

sys@TEST>

select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

USERS

DOUDOU

TEMP

6

、全库导入

[ora@dg-pp ~]$

imp system/oracle@test file=/home/ora/full.dmp full=y ignore=y

7

、成功后，建议再次全库导出已做备份

附表：

不同版本的全库导入

导致了

oracle

组件出现问题

EXP

全库 oracle：10.2.0.1

IMP

全库 oracle:10.2.0.4

sys@TEST> select comp_name, version, status from sys.dba_registry;

COMP_NAME

VERSION

STATUS

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

Oracle Enterprise Manager

10.2.0.4.0

VALID

Spatial

10.2.0.4.0

INVALID

Oracle interMedia

10.2.0.4.0

INVALID

OLAP Catalog

10.2.0.4.0

VALID

Oracle XML Database

10.2.0.4.0

VALID

Oracle Text

10.2.0.4.0

VALID

Oracle Expression Filter

10.2.0.4.0

VALID

Oracle Rule Manager

10.2.0.4.0

VALID

Oracle Workspace Manager

10.2.0.1.0

DOWNGRADED

Oracle Data Mining

10.2.0.4.0

VALID

Oracle Database Catalog Views

10.2.0.4.0

VALID

Oracle Database Packages and Types

10.2.0.4.0

VALID

JServer JAVA Virtual Machine

10.2.0.4.0

VALID

Oracle XDK

10.2.0.4.0

VALID

Oracle Database Java Packages

10.2.0.4.0

VALID

OLAP Analytic Workspace

10.2.0.4.0

VALID

Oracle OLAP API

10.2.0.4.0

VALID

解决方法：

sys@TEST>

shutdown immediate;

sys@TEST>

startup upgrade

sys@TEST>

@?/rdbms/admin/catupgrd.sql----

重新升级组件

sys@TEST>

@?/rdbms/admin/utlrp.sql

---

重新编译plsql失效的包

sys@TEST>

select comp_name, version, status from sys.dba_registry;

COMP_NAME

VERSION

STATUS

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

Oracle Enterprise Manager

10.2.0.4.0 VALID

Spatial

10.2.0.4.0 VALID

Oracle interMedia

10.2.0.4.0 VALID

OLAP Catalog

10.2.0.4.0 VALID

Oracle XML Database

10.2.0.4.0 VALID

Oracle Text

10.2.0.4.0 VALID

Oracle Expression Filter

10.2.0.4.0 VALID

Oracle Rule Manager

10.2.0.4.0 VALID

Oracle Workspace Manager

10.2.0.4.3 VALID

Oracle Data Mining

10.2.0.4.0 VALID

Oracle Database Catalog Views

10.2.0.4.0 VALID

Oracle Database Packages and Types

10.2.0.4.0 VALID

JServer JAVA Virtual Machine

10.2.0.4.0 VALID

Oracle XDK

10.2.0.4.0 VALID

Oracle Database Java Packages

10.2.0.4.0 INVALID

OLAP Analytic Workspace

10.2.0.4.0 VALID

Oracle OLAP API

10.2.0.4.0 VALID

Oracle Database Java Packages

失效，不使用暂不处理

总结：full database export and import 需数据库版本相同