ITPub博客

首页 > 数据库 > Oracle > Oracle数据库迁移之三:dblink+impdp

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

原创 Oracle 作者:Frank_dba 时间:2014-04-11 01:53:16 1 删除 编辑
数据库的迁移方式千变万化,各有千秋。今天为大家呈现的是第三种方法,利用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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-01-11

  • 博文量
    7
  • 访问量
    77990