数据库的迁移方式千变万化，各有千秋。今天为大家呈现的是第三种方法，利用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博客

Oracle数据库迁移之三：dblink+impdp