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