12c pdb的資料泵匯入匯出簡單示例

snowdba發表於2015-02-26
12c推出了可插拔資料庫，在一個容器cdb中以多租戶的形式同時存在多個資料庫pdb。在為pdb做資料泵匯入匯出時和傳統的資料庫有少許不同。
1，需要為pdb新增tansnames
2，匯入匯出時需要在userid引數內指定其tansnames的值，比如userid=user/pwd@tans

下面透過一個例子演示pdb的資料泵匯入匯出操作



1，指定當前的sid為可插拔資料庫。如果資料庫中安裝了多個例項，其中有普通單例項的，有插拔資料庫的等等，為了減少錯誤首先確定其sid
[oracle@snow ~]$ export ORACLE_SID=cdb

2，登入cdb，檢視pdb。如果此時pdb1是mount狀態可以切換到pdb1下執行alter database open命令和普通資料庫一樣。或者使用alter pluggable database all open開啟所有的pdb。
[oracle@snow ~]$ sqlplus / as sysdba

SYS@cdb >show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

3，切換到pdb1
SYS@cdb >alter session set container=pdb1;

4，解鎖示例使用者hr，以後的schema級別匯入匯出演示就使用該使用者的資料。
SYS@cdb >alter user hr identified by hr account unlock;

5，單獨建立一個dba許可權的資料泵使用者
SYS@cdb >grant dba to dp identified by dp;

6，建立一個資料泵目錄dp_dir，路徑為oracle家目錄
SYS@cdb >create or replace directory dp_dir as '/home/oracle';

7，dp使用者在資料泵路徑有讀寫許可權（如果是dba許可權的這一步可以省略，為了試驗的完整性這裡保留）
SYS@cdb >grant read,write on directory dp_dir to dp;

SYS@cdb >exit

8，設定tnsnames.ora，增加pdb1的連結。HOST按照自己主機的地址新增，SERVICE_NAME為pdb的示例名，這裡為pdb1
[oracle@snow ~]$ cd $ORACLE_HOME/network/admin
[oracle@snow admin]$ cat tnsnames.ora
pdb1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

測試tnsnames.ora的有效性，如果返回OK (0 msec)表示配置成功
[oracle@snow admin]$ tnsping pdb1

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-FEB-2015 18:26:29

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (0 msec)

9，使用資料泵匯出
  • 使用者名稱密碼為dp/dp，並且透過tnsnames指向pdb1。命令列模式userid引數可以省去
  • 資料泵目錄為：dp_dir, OS路徑是/home/oracle
  • 匯出檔案為：/home/oracle/hr_pdb1.dmp
  • 匯出日誌為：/home/oracle/hr_pdb1.log
  • 匯出模式為SCHEMA，也可以理解為使用者：hr

[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1.log schemas=hr

Export: Release 12.1.0.1.0 - Production on Mon Feb 9 18:29:37 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DP"."SYS_EXPORT_SCHEMA_01": dp/********@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "HR"."COUNTRIES" 6.437 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows
. . exported "HR"."EMPLOYEES" 17.06 KB 107 rows
. . exported "HR"."JOBS" 7.085 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows
. . exported "HR"."LOCATIONS" 8.414 KB 23 rows
. . exported "HR"."REGIONS" 5.523 KB 4 rows
Master table "DP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DP.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr_pdb1.dmp
Job "DP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 9 18:30:27 2015 elapsed 0 00:00:39

10，測試匯出效果
10-1 刪除pdb1的hr使用者
SYS@cdb >alter session set container=pdb1;

Session altered.

SYS@cdb >select count(*) from hr.employees;

COUNT(*)
----------
107

SYS@cdb >
SYS@cdb >drop user hr cascade;

User dropped.

此時訪問該使用者的表已經不存在了
SYS@cdb >select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

10-2 匯入hr使用者

[oracle@snow ~]$
[oracle@snow ~]$ impdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1_imp.log schemas=hr

Import: Release 12.1.0.1.0 - Production on Mon Feb 9 18:37:42 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "DP"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "DP"."SYS_IMPORT_SCHEMA_01": dp/********@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1_imp.log schemas=hr
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.437 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.101 KB 27 rows
. . imported "HR"."EMPLOYEES" 17.06 KB 107 rows
. . imported "HR"."JOBS" 7.085 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.171 KB 10 rows
. . imported "HR"."LOCATIONS" 8.414 KB 23 rows
. . imported "HR"."REGIONS" 5.523 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DP"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Feb 9 18:38:01 2015 elapsed 0 00:00:16

10-3 測試匯入結果
SYS@cdb >select count(*) from hr.employees;

COUNT(*)
----------
107

匯入成功

全文完！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1442122/，如需轉載，請註明出處，否則將追究法律責任。

相關文章