html网站开发实例/外贸网站推广怎么做
remap_schemas可以更改导入用户,比如想导入scott用户下的数据,但是导入到liu用户下,这个时候就需要用到数据泵中remap_schemas参数
select OWNER,SEGMENT_TYPE,sum(BYTES/1024/1024/1024) gb from dba_segments where OWNER in ('YNSCMS','YNSCMSAPP_NEW') group by owner,SEGMENT_TYPE;
OWNER SEGMENT_TYPE GB
------------------------------ ------------------ ----------
YNSCMS TABLE 47.6245728
YNSCMS INDEX PARTITION .000366211
YNSCMSAPP_NEW INDEX .243469238
YNSCMS LOB PARTITION .000366211
YNSCMS LOBINDEX .004150391
YNSCMS TABLE PARTITION 19.699585
YNSCMS INDEX 42.5075684
YNSCMSAPP_NEW LOBSEGMENT 1.74298096
YNSCMSAPP_NEW TABLE 1.14886475
YNSCMSAPP_NEW LOBINDEX .057189941
YNSCMS LOBSEGMENT .202453613
11 rows selected.
lob段不大,总体数据量100g左右,io正常的情况下应该是很快的
2.查看目标库dblink
col host for a50
col db_link for a20
select * from dba_db_links;
OWNER DB_LINK USERNAME HOST
------------------------------ -------------------- ------------------------------ --------------------------------------------------
PUBLIC OLD SYSTEM old
有个dblink是old,直接tnsping下是否可用
xxxx:/home/oracle@db> tnsping old
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 11-JUN-2018 19:53:57
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.111.111)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) ( sid = sid2)))
OK (70 msec)
确认ip正确就可以使用这个tns了
set pagesize 200
select owner,tablespace_name from dba_segments where owner in ('YNSCMS','YNSCMSAPP_NEW') group by owner,tablespace_name;
3.查看表空间
--源库用户使用的表空间
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
YNSCMSAPP_NEW SCMS
YNSCMS YNSCMS5
YNSCMS SCMS
YNSCMS YNSCMS7
YNSCMS YNSCMS8
YNSCMS USERS
YNSCMS EPMSDEV
YNSCMS YNSCMS1
YNSCMS YNSCMS2
YNSCMS YNSCMS6
YNSCMS YNSCMS3
YNSCMS YNSCMS4
--目标库用户使用的表空间
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
YNSCMSAPP_NEW SCMS
YNSCMS YNSCMS5
YNSCMS SCMS
YNSCMS YNSCMS7
YNSCMS YNSCMS8
YNSCMS USERS
YNSCMS EPMSDEV
YNSCMS YNSCMS1
YNSCMS YNSCMS2
YNSCMS YNSCMS6
YNSCMS YNSCMS3
YNSCMS YNSCMS4
表空间没有变化,可用不需要新建表空间或者使用remap_tablespace参数更改表空间
--查看表空间是否足够
表空间不足够需要新建表空间
4.查看目标库的cpu使用情况
ALL 0.5 0.1 0.0 99.4 0.03 0.8
sn59503vioc1:/home/oracle@db> prtconf|grep Processor
Processor Type: PowerPC_POWER5
xxxxxxx
Processor Version: PV_5_3
Number Of Processors: 8
Processor Clock Speed: xxx MHz
Model Implementation: Multiple Processor, PCI bus
+ proc0 Processor
+ proc2 Processor
+ proc4 Processor
+ proc6 Processor
+ proc8 Processor
+ proc10 Processor
+ proc12 Processor
+ proc14 Processor
8个cpu,可以全部用上,impdp中parallel参数设置为8
5.导入用户
--不加schemas会导入当前登录用户
下面是以实例的方式展示如何使用remap_schemas参数 以impdp+dblink的方式导入数据
两个用户需要做更改
源库用户 | 目标库用户 |
ynscms | ynscms3 |
ynscmsapp_new | ynscmsapp_new3 |
1.查看源库数据大小和类型
select OWNER,SEGMENT_TYPE,sum(BYTES/1024/1024/1024) gb from dba_segments where OWNER in ('YNSCMS','YNSCMSAPP_NEW') group by owner,SEGMENT_TYPE;
OWNER SEGMENT_TYPE GB
------------------------------ ------------------ ----------
YNSCMS TABLE 47.6245728
YNSCMS INDEX PARTITION .000366211
YNSCMSAPP_NEW INDEX .243469238
YNSCMS LOB PARTITION .000366211
YNSCMS LOBINDEX .004150391
YNSCMS TABLE PARTITION 19.699585
YNSCMS INDEX 42.5075684
YNSCMSAPP_NEW LOBSEGMENT 1.74298096
YNSCMSAPP_NEW TABLE 1.14886475
YNSCMSAPP_NEW LOBINDEX .057189941
YNSCMS LOBSEGMENT .202453613
11 rows selected.
lob段不大,总体数据量100g左右,io正常的情况下应该是很快的
2.查看目标库dblink
col host for a50
col db_link for a20
select * from dba_db_links;
OWNER DB_LINK USERNAME HOST
------------------------------ -------------------- ------------------------------ --------------------------------------------------
PUBLIC OLD SYSTEM old
有个dblink是old,直接tnsping下是否可用
xxxx:/home/oracle@db> tnsping old
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 11-JUN-2018 19:53:57
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.111.111)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) ( sid = sid2)))
OK (70 msec)
确认ip正确就可以使用这个tns了
set pagesize 200
select owner,tablespace_name from dba_segments where owner in ('YNSCMS','YNSCMSAPP_NEW') group by owner,tablespace_name;
3.查看表空间
--源库用户使用的表空间
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
YNSCMSAPP_NEW SCMS
YNSCMS YNSCMS5
YNSCMS SCMS
YNSCMS YNSCMS7
YNSCMS YNSCMS8
YNSCMS USERS
YNSCMS EPMSDEV
YNSCMS YNSCMS1
YNSCMS YNSCMS2
YNSCMS YNSCMS6
YNSCMS YNSCMS3
YNSCMS YNSCMS4
--目标库用户使用的表空间
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
YNSCMSAPP_NEW SCMS
YNSCMS YNSCMS5
YNSCMS SCMS
YNSCMS YNSCMS7
YNSCMS YNSCMS8
YNSCMS USERS
YNSCMS EPMSDEV
YNSCMS YNSCMS1
YNSCMS YNSCMS2
YNSCMS YNSCMS6
YNSCMS YNSCMS3
YNSCMS YNSCMS4
表空间没有变化,可用不需要新建表空间或者使用remap_tablespace参数更改表空间
--查看表空间是否足够
4.查看目标库的cpu使用情况
--查看cpu负载
topas:
CPU User% Kern% Wait% Idle% Physc EntcALL 0.5 0.1 0.0 99.4 0.03 0.8
cpu很空闲
sn59503vioc1:/home/oracle@db> prtconf|grep Processor
Processor Type: PowerPC_POWER5
xxxxxxx
Processor Version: PV_5_3
Number Of Processors: 8
Processor Clock Speed: xxx MHz
Model Implementation: Multiple Processor, PCI bus
+ proc0 Processor
+ proc2 Processor
+ proc4 Processor
+ proc6 Processor
+ proc8 Processor
+ proc10 Processor
+ proc12 Processor
+ proc14 Processor
8个cpu,可以全部用上,impdp中parallel参数设置为8
5.导入用户
--不加schemas会导入当前登录用户
nohup impdp system/gGkA-RY\!5YS7 parallel=8 cluster=N schemas=ynscms,ynscmsapp_new NETWORK_LINK=old directory=DATA_PUMP_DIR remap_schema=ynscms:ynscms3,ynscmsapp_new:ynscmsapp_new3 logfile=schemas_aa.log TABLE_EXISTS_ACTION=REPLACE estimate=statistics &
使用nohup到后台运行。
输出:
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "YNSCMSAPP_NEW3"."xxx" 12 rows
...