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 需資料庫版本相同