目录
一、KSSY--测试 192.168.12.14 导入
1、创建表空间、用户、用户授权、对象授权
CREATE tablespace MESPRD_TBSP logging DATAFILE'/data/u01/app/oracle/oradata/KSSYORCLPRD/datafile/MESPRD_01.dbf' size 10240m autoextend on next 1000m; create user mesprd identified by "************" default tablespace MESPRD_TBSP temporary tablespace TEMP profile DEFAULT; grant connect,dba,resource to mesprd; grant unlimited tablespace to MESPRD with admin option;
2、创建目录
create directory backup_mes as '/data/backup'; GRANT READ,WRITE ON DIRECTORY backup_mes to MESPRD; GRANT READ,WRITE ON DIRECTORY backup_mes to system; create directory backup_mes as '/u01/expbackup'; GRANT READ,WRITE ON DIRECTORY backup_mes to mesprd; GRANT READ,WRITE ON DIRECTORY backup_mes to system;
3、导入，导出命令
PRD导出： expdp system/****@127.0.0.1/kssyorclprd DIRECTORY=backup_mes DUMPFILE=KSSYMES20191009_full.dmpdp logfile=KSSYMES20191009_full.log full=y PRD导出： expdp MESPRD/****#****@127.0.0.1/kssyorclprd DIRECTORY=backup_mes DUMPFILE=KSSYMES20191009_MESPRD.dmpdp logfile=KSSYMES20191009_MESPRD.log expdp MESPRD/****#****@127.0.0.1/kssyorclprd DIRECTORY=backup_mes DUMPFILE=KSSYMES20200104_MESPRD.dmpdp logfile=KSSYMES20200104_MESPRD.log exclude=table:\"in\(\'H_HOSPS\',\'MODULE\'\)\" expdp MESPRD/****#****@127.0.0.1/kssyorclprd DIRECTORY=backup_mes DUMPFILE=KSSYMES20200104_MESPRD.dmpdp logfile=KSSYMES20200104_MESPRD.log exclude=table:\"in\(\'FND_CUSTOMER\'\)\"
UAT导出：
expdp system/**** DIRECTORY=backup_mes DUMPFILE=KSSYMES20191012_full.dmpdp logfile=KSSYMES20191012_full.log full=y 导入： impdp system/****@172.18.1.190/kssyorclqas dumpfile=KSSYMES20200319.dmpdp logfile=KSSYMES20200319-imp.log DIRECTORY=backup_mes SCHEMAs=MESPRD
4、删除导出用户、表空间
startup restrict——受限模式，维护时避免别人打扰，连接等 drop user MESPRD cascade; drop tablespace MESPRD_TBSP including contents and datafiles;
5、创建表空间、用户、用户授权、对象授权
CREATE tablespace MESPRD_TBSP logging DATAFILE'/u01/app/oracle/oradata/KSSYORCLQAS/datafile/MESPRD_01.dbf' size 10240m autoextend on next 1000m ; create user mesprd identified by "****" default tablespace MESPRD_TBSP temporary tablespace TEMP profile DEFAULT; grant connect,dba,resource to mesprd; grant unlimited tablespace to mesprd with admin option;
6、给表空间增加数据文件
alter tablespace MESPRD_TBSP add DATAFILE'/u01/app/oracle/oradata/KSSYORCLQAS/datafile/MESPRD_02.dbf' size 10240m autoextend on next 1000m ;
二、昆山塑业（正式机-10.110.48.2导入测试机--10.110.48.11）-2020-05-16
1、导出10.110.48.2数据到10.110.48.11
2、删除10.110.48.11用户mesprd
drop user MESPRD cascade;
3、测试机，创建mesprd用户
create user mesprd identified by "****" default tablespace MESPRD_TBSP temporary tablespace TEMP profile DEFAULT; grant connect,dba,resource to mesprd; grant unlimited tablespace to MESPRD with admin option;
4、数据导入到测试机
impdp system/****@10.110.48.11/kssyorclprd dumpfile=KSSYMESPRD-10.110.48.2-20200516.dmpdp logfile=KSSYMESPRD-10.110.48.2-20200516-impdp.log DIRECTORY=backup_mes SCHEMAs=MESPRD
三、昆山塑业（对测试机--10.110.48.11导入一张表）-2020-05-20
1、要求：把正式机mesprd的表sys_param导入到测试机test的sys_param表中
2、创建用户：
create user test identified by "****" default tablespace MESPRD_TBSP temporary tablespace TEMP profile DEFAULT;
grant connect,dba,resource to test;
grant unlimited tablespace to test with admin option;
3、导入表数据：
impdp test/****@10.110.48.11/kssyorclprd dumpfile=KSSYMESPRD-10.110.48.2-20200516.dmpdp.bak logfile=KSSYMESPRD-10.110.48.2-20200516-impdp_test.log DIRECTORY=backup_mes tables=mesprd.sys_param REMAP_SCHEMA=mesprd:test remap_table=mesprd.sys_param:sys_param
4、注意：
（1）同一用户（schema)导入某个表-如：源端mesprd到目标端mesprd中
a、不能加remap_schema；
b、remap_table冒号前面的表名一定要带schema信息，冒号后面的表名一定不能带schema信息
impdp mesprd/****@10.110.48.11/kssyorclprd dumpfile=KSSYMESQAS-10.110.48.11-20200516.dmpdp
logfile=KSSYMESQAS-10.110.48.11-20200516_impdp_test.log
DIRECTORY=backup_mes
tables=mesprd.sys_param
remap_table=mesprd.sys_param:sys_param
(2)不同用户（shcema）导入某个表--如：mesprd到test2中
a、 必须加remap_schema，---remap_schema=mesprd:test2
b、remap_table冒号前面的表名一定要带schema信息，冒号后面的表名一定不能带schema信息 ---rema
impdp test2/****@10.110.48.11/kssyorclprd dumpfile=KSSYMESQAS-10.110.48.11-20200516.dmpdp
logfile=KSSYMESQAS-10.110.48.11-20200516_impdp_test.log
DIRECTORY=backup_mes
tables=mesprd.sys_param
REMAP_SCHEMA=mesprd:test2
remap_table=mesprd.sys_param:sys_param
文章评论