Oracle資料庫遷移之三：dblink+impdp

資料庫的遷移方式千變萬化，各有千秋。今天為大家呈現的是第三種方法，利用dblink+impdp來完成資料庫的遷移工作。

1.實驗環境
本文實驗中的兩臺主機為紅帽5.5系統的虛擬機器，資料庫版本為Oracle 11g。

2.實驗目的
本文實驗將把主機下HR使用者下的所有表遷移到備庫。

3.實驗步驟

1）檢視主機下HR使用者下的資訊

檢視HR使用者的預設表空間
SYS@ENMOEDU > select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR EXAMPLE

檢視預設表空間大小
SYS@ENMOEDU > select TABLESPACE_NAME,BYTES from dba_data_files where TABLESPACE_NAME like 'EXAMPLE';

TABLESPACE_NAME BYTES
------------------------------ ----------
EXAMPLE 215285760

由此可得HR使用者的預設表空間大概是200MB左右。

檢視HR使用者下所有表
HR@ENMOEDU > select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

檢視資料檔案

SYS@ENMOEDU > select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME
like 'EXAMPLE';

FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/ENMOEDU/example01.dbf EXAMPLE

2)備庫上的準備工作

(1)檢視備庫上是否有HR使用者以及相關表空間和資料檔案

SYS@FRANK> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'HR';
no rows selected

SYS@FRANK> select TABLESPACE_NAME,BYTES from dba_data_files where TABLESPACE_NAME like 'EXAMPLE';
no rows selected

SYS@FRANK> select count(*) from EMPLOYEES;
select count(*) from EMPLOYEES
*
ERROR at line 1:
ORA-00942: table or view does not exist

SYS@FRANK> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'EXAMPLE';
no rows selected
由此可見，備庫上是沒有HR使用者以及相關的資料資訊的。

(2)在備庫上建立HR使用者以及相關表空間
SYS@FRANK> grant dba to hr identified by oracle;
Grant succeeded.

SYS@FRANK> create tablespace example datafile '/u01/app/oracle/oradata/FRANK/example01.dbf' size 200m;
Tablespace created.

SYS@FRANK> alter user hr default tablespace example;
User altered.

(3)檢視備庫上剛剛建立的HR使用者以及相關資料資訊
SYS@FRANK> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR EXAMPLE

SYS@FRANK>select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'EXAMPLE';

FILE_NAME TABLESPACE_NAME
------------------------------------------- ---------------
/u01/app/oracle/oradata/FRANK/example01.dbf EXAMPLE

(4)配置備庫上的tnsname.ora檔案

[oracle@FRANK ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@FRANK admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools

FRANK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.27)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = FRANK)
)
)
ENMOEDU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ENMOEDU)
)
)

(5)從備庫上嘗試連線主庫
[oracle@FRANK admin]$ sqlplus sys/oracle@ENMOEDU as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 10 23:36:43 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ENMOEDU>

連線成功。

（6）在備庫上建立dblink
SYS@FRANK> create public database link hrlink using 'ENMOEDU';
Database link created.

SYS@FRANK> conn hr/oracle
Connected.

HR@FRANK> create database link hrlink connect to hr identified by oracle;
Database link created.

HR@FRANK> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- ----------
PUBLIC HRLINK ENMOEDU 10-APR-14
HR HRLINK HR 10-APR-14

HR@FRANK> select count(*) from employees@hrlink;

COUNT(*)
----------
107
至此，dblink建立成功。

3）遷移HR下所有表到備庫
首先給HR使用者授權
SYS@FRANK> grant dba,connect,resource to hr indentified by oracle;
Grant succeeded.

開始傳輸表
[oracle@FRANK admin]$ impdp hr network_link=hrlink schemas=hr cluster=N PARALLEL=2
Import: Release 11.2.0.3.0 - Production on Fri Apr 11 00:16:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_IMPORT_SCHEMA_01": hr/******** directory=dirhrlink network_link=hrlink schemas=hr cluster=N PARALLEL=2
Estimate in progress using BLOCKS method...

.

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
Job "HR"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 00:17:01

HR@FRANK> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

至此，實驗成功。

Frank
2014.04.08

--To be continued--

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

Oracle資料庫遷移之三：dblink+impdp

相關文章