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.dmpora@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 需数据库版本相同