Oracle转移历史数据技术,实战：将正式数据库上的部分数据导入历史数据库(oracle)...

2个Server：

1. History DB : HP-UX oracle 10g

2 正式DB ：HP-UX oracle 10g

目的：将正式DB上的用户MESPRD的表CONSUMABLEHISTORY中CONSUMABLEHISTORY201004，CONSUMABLEHISTORY201005，CONSUMABLEHISTORY201006 3个分区导出到HistoryDB

History DB下：

建立用于放置dmp的文件夹

mkdir /oracle/dump/backup/lxytest

在sam下，

Networking and Communication -> Networking File System -> Share/unshare File system

正式DB:

在oracle中建立directory对象：

CREATE OR REPLACE DIRECTORY

DUMP_LXY AS

'/dumplxy';

在sam下mount hisdb上的/tmp/dumplxy;

Networking and Communication -> Networking File System -> mount remote filesystem

然后在bdf下即可查看：

[MESDB1:LEDMES]/dumplxy> bdf /dumplxy

File-System                                  Mbytes    Used   Avail %Used Mounted on

109.115.100.127:/oracle/dump/backup/lxytest  921600  711894  196600   78% /dumplxy

然后在hisdb上的/oracle/dump/backup/lxytest下编辑参数文件：

#vi lxy_exp_parfile_01.txt

directory=DUMP_LXY

dumpfile=LXY1.dmp,

LXY2.dmp,

LXY3.dmp

logfile=LXY.log

parallel=3

tables =

MESPRD.CONSUMABLEHISTORY:CONSUMABLEHISTORY201004,

MESPRD.CONSUMABLEHISTORY:CONSUMABLEHISTORY201005,

MESPRD.CONSUMABLEHISTORY:CONSUMABLEHISTORY201006

exclude=STATISTICS

开始在正式DB上执行expdp导出操作：

正式DB中oracle数据库使用的操作系统用户叫orames

在此用户下：

cd /dumplxy

expdp system/oracle parfile=lxy_exp_parfile_01.txt

开始导出后，可以用Ctrl+C 离开expdp，提示符变为export>

使用命令status可以看到当前进度

Export> status

Job: SYS_EXPORT_TABLE_01

Operation: EXPORT

Mode: TABLE

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 3

Job Error Count: 0

Dump File: /dumplxy/LXY1.dmp

bytes written: 4,096

Dump File: /dumplxy/LXY2.dmp

bytes written: 4,096

Dump File: /dumplxy/LXY3.dmp

bytes written: 69,632

Worker 1 Status:

State: WORK WAITING

Worker 2 Status:

State: EXECUTING

Object Schema: MESPRD

Object Name: CONSUMABLEHISTORY

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 3

Worker Parallelism: 1

Worker 3 Status:

State: EXECUTING

Object Schema: MESPRD

Object Name: CONSUMABLEHISTORY

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 3

Worker Parallelism: 1

可以看到worker2，worker3在干活，而worker1在歇着。。。

过一会儿再次使用status命令查看进度时，报错

Export> status

UDE-00008: operation generated ORACLE error 31626

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 938

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2816

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3963

ORA-06512: at line 1

估计已经完成了

因为参数文件中定义了log的位置，查看log:

[MESDB1:LEDMES]/dumplxy> vi LXY.log

"LXY.log" 35 lines, 1615 characters

;;;

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 05 July, 2011 10:32:14

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

;;;

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

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

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=lxy_exp_parfile_01.txt

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2.509 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/COMMENT

;;; Export> status

;;; Export> status

;;; Export> status

;;; Export> status

;;; Export> status

;;; Export> status

;;; Export> status

. . exported "MESPRD"."CONSUMABLEHISTORY":"CONSUMABLEHISTORY201005"  765.7 MB 2479721 rows

. . exported "MESPRD"."CONSUMABLEHISTORY":"CONSUMABLEHISTORY201004"  799.1 MB 2604896 rows

;;; Export> status

. . exported "MESPRD"."CONSUMABLEHISTORY":"CONSUMABLEHISTORY201006"  743.9 MB 2424009 rows

;;; Export> status

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/dumplxy/LXY1.dmp

/dumplxy/LXY2.dmp

/dumplxy/LXY3.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 10:35:11

说明已经导出成功了。。。

另外：在导出的过程中，如果在另外的session中查看进度，可以通过使用命令：

expdp system/oracle attach=sys_export_table_01进入到export>环境下

sys_export_table_01其实是当前导出时使用的用户下的一个table

此时可以断开正式db上mount的NFS了

======================================

导入开始：

首先在HistoryDB中建立Directory object

CREATE OR REPLACE DIRECTORY

dump_lxy_imp AS

'/oracle/dump/backup/lxytest';

编辑导入使用的参数文件：

#vi lxy_imp_parfile_01.txt

directory=DUMP_LXY_IMP

dumpfile=LXY1.dmp,

LXY2.dmp,

LXY3.dmp

logfile=LXY_IMP.log

REMAP_SCHEMA = MESPRD:LXY

parallel=3

REMAP_TABLESPACE=

TS_DMESHIS_01:LXY,

TS_IMESHIS_01:LXY

exclude=INDEX

TABLE_EXISTS_ACTION = APPEND

然后在history上使用impdp命令导入

impdp system/oracle parfile=lxy_imp_parfile_01.txt

导入完成后，记得查看log看是否正常导入，我导入时报错：

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role 'SCHPRD' does not exist

Failing sql is:

GRANT SELECT ON "LXY"."CONSUMABLEHISTORY" TO "SCHPRD"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role 'SCHDEV' does not exist

Failing sql is:

GRANT SELECT ON "LXY"."CONSUMABLEHISTORY" TO "SCHDEV"

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

;;; Import> status

Processing object type TABLE_EXPORT/TABLE/COMMENT

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 11:28:55

原因是HistoryDB上没有SCHPRD这个用户，可是这个表在原来的DB上是有SCHPRD这个用户的权限的，没太大关系。