imp 邏輯恢復（匯入）：
測試環境接【移動資料】expdp的應用

準備操作：
——刪除 st 使用者，方便 imp 測試，命令如下：

SYS@ORA11GR2>drop user st cascade;

User dropped.

——重新建立 st 使用者並賦予相應的角色

SYS@ORA11GR2>create user st identified by oracle;

User created.

SYS@ORA11GR2>grant connect,resource to st;

Grant succeeded.

SYS@ORA11GR2>conn st/oracle

Connected.
——驗證（st使用者下沒有任何表）；

ST@ORA11GR2>select * from tab;

no rows selected

實驗一：匯入單表到相同使用者名稱下
前提：存在相同的使用者名稱，並且存在相同的表空間，將 T 表匯入到 st 使用者下，我們使用先前（測試環境接【移動資料】expdp的應用）匯出的exp_st_t.dmp 備份檔案完成恢復

[oracle@wang ~]$ imp st/oracle file=/home/oracle/backup/exp_st_t.dmp

（用st使用者執行匯入，即將資料匯入到st使用者下）

Import: Release 11.2.0.4.0 - Production on Mon Oct 3 17:57:56 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing ST's objects into ST

. . importing table "T" 10 rows imported

Import terminated successfully without warnings.

[oracle@wang ~]$

——驗證：

ST@ORA11GR2>select * from tab;

TNAME TABTYPE CLUSTERID

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

T TABLE

ST@ORA11GR2>select count(*) from t;

COUNT(*)

----------

10

ST@ORA11GR2>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

T USERS

HAHA TABLE

T TABLE

DT@ORA11GR2>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

HAHA USERS

T USERS

實驗二：利用 exp、imp 傳輸表空間
源庫： 192.168.10.2 ORACLE_SID:ORA11GR2
目標庫：192.168.10.3 ORACLE_SID:PROD
本實驗版本、平臺均一致：

將 TS_USERS 表空間從源庫傳輸到目標庫
1) TS_USERS 表空間中，有1張表

SYS@ORA11GR2>select owner,table_name from dba_tables where TABLESPACE_NAME='TS_USERS';

OWNER TABLE_NAME

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

SYS T2

2) 檢查是否違反自包含

SYS@ORA11GR2>exec sys.dbms_tts.transport_set_check('TS_USERS',true);

PL/SQL procedure successfully completed.

SYS@ORA11GR2>select * from sys.transport_set_violations;

no rows selected（無內容表示可以沒有違反自包含）

3) 將 TS_USERS 表空間置為只讀

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS ONLINE

SYS@ORA11GR2>alter tablespace ts_users read only;

Tablespace altered.

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS READ ONLY

4) 透過 exp 匯出 ts_users 表空間的後設資料
注： 必須以 sysdba 身份匯出，單引號要轉義

[oracle@wang ~]$ exp \'/ as sysdba\' transport_tablespace=y tablespaces=ts_users file=ts_users.dmp

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 19:16:25 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TS_USERS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table T2

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

[oracle@wang ~]$

5) 複製TS_USERS 表空間的資料檔案及剛剛透過 exp 匯出的後設資料備份檔案
--複製後設資料備份檔案

[oracle@wang ~]$ scp ts_users.dmp 192.168.10.3:/home/oracle/.

oracle@192.168.10.3's password:

ts_users.dmp 100% 16KB 16.0KB/s 00:00

[oracle@wang ~]$

——驗證：

[oracle@bing ~]$ ls

import.log ts_ora11gr2.dmp ts_users.dmp

[oracle@bing ~]$ pwd

/home/oracle

--複製資料檔案

[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls ts_users_01.dbf

ts_users_01.dbf

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ scp ts_users_01.dbf 192.168.10.3:/u01/app/oracle/oradata/PROD/.

oracle@192.168.10.3's password:

ts_users_01.dbf 100% 10MB 10.0MB/s 00:00

[oracle@wang ORA11GR2]$

——驗證：

[oracle@bing PROD]$ ls ts_users_01.dbf

ts_users_01.dbf

[oracle@bing PROD]$ pwd

/u01/app/oracle/oradata/PROD

[oracle@bing PROD]$

6) imp 完成傳輸表空間，要求，表空間中的資料的 owner 是 scott
--解鎖 scott 使用者，驗證，scott 中，並無ts_users 表空間中的那兩個表（因為還沒執行匯入）

[oracle@bing PROD]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 3 19:32:46 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD>conn scott/tiger

Connected.

SCOTT@PROD>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

SALGRADE USERS

BONUS USERS

EMP USERS

DEPT USERS

——執行匯入導致

[oracle@bing ~]$ imp \'/ as sysdba\' file=ts_users.dmp fromuser=sys touser=scott transport_tablespace=y tablespaces=ts_users datafiles=/u01/app/oracle/oradata/PROD/ts_users_01.dbf

Import: Release 11.2.0.4.0 - Production on Mon Oct 3 19:37:00 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

About to import transportable tablespace(s) metadata...

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SYS's objects into SCOTT

. . importing table "T2"

Import terminated successfully without warnings.

[oracle@bing ~]$

--驗證

SCOTT@PROD>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

T2 TS_USERS

SALGRADE USERS

BONUS USERS

EMP USERS

DEPT USERS

7) 收尾工作（這一步千萬要記住，不要忘記了）
--源庫

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS READ ONLY

SYS@ORA11GR2>alter tablespace ts_users read write;

Tablespace altered.

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS ONLINE

--目標庫

SCOTT@PROD>conn / as sysdba

Connected.

SYS@PROD>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS READ ONLY

SYS@PROD>alter tablespace ts_users read write;

Tablespace altered.

SYS@PROD>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_USERS';

TABLESPACE_NAME STATUS

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

TS_USERS ONLINE

完成！！！！！！！！！！！！！！

【移動資料】imp的應用

相關文章