EXP AND IMP

purpors: exp pro data to uat data

######for one owner table

1. step 1 colelct dmp file

ACCEPT v_owner PROMPT 'please input v_owner:'
define v_owner;
pause
ACCEPT v_table PROMPT 'please input v_table:'
define v_table;

REM srdc_rman_backup_output.sql - collect RMAN information with backup.


define SRDCNAME='EXPDP_BACKUP'

SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME

select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;

set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set pagesize 50000;
set echo on;
set feedback on;
select directory_name,directory_path from dba_directories;

select * from dba_registry_history;

select count(*) from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'));

select owner,object_name from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'))
and statuS='INVALID';

select count(*),object_type,owner from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in
('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'))
group by object_type,owner;

select SUM(bytes)/1024/1024 "M" from dba_segments where owner like '%DATA%';

SELECT * FROM v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET';

SELECT '&v_owner' from dual;
SELECT '&v_table' from dual;

select SUM(bytes)/1024/1024 "M" from dba_segments where owner=upper('&v_owner') and segment_name=upper('&v_table');

select count(*) from &v_owner..&v_table;

spool off
set markup html off preformat off

###### for all owner

REM srdc_rman_backup_output.sql - collect RMAN information with backup.


define SRDCNAME='DB_BACKUP'

SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME

select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;

set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set pagesize 50000;
set echo on;
set feedback on;
select '+-------------dump info--------------------------+' from dual;

select directory_name,directory_path from dba_directories;

select * from dba_registry_history;

select count(*) from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'));

select owner,object_name from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'))
and statuS='INVALID';

select count(*),object_type,owner from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in
('SYS','SYSTEM','OPER','BACKUPDB','C##OPER','PDBADMIN'))
group by object_type,owner;

select SUM(bytes)/1024/1024 "M" from dba_segments where owner like '%DATA%';

SELECT * FROM v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET';

select '+-------------tablespace info--------------------------+' from dual;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;

select file_id,file_name,tablespace_name,autoextensible from dba_data_files;

spool off
set markup html off preformat off

########

1.exp and imp will not create user  (all)

2.expdp and impdp will create user (default)

 (impdp 做的时候，如果做之前没有显性删除用户（drop user）,则impdp 不会自动创建user,那么也不需要重置密码 )
3.expdp会导出DDL，并且导出表相关的LOBSEGMENT、LOBINDEX和常规的索引。
      impdp会使用DDL创建表，并且创建表上相关的索引。

for 12c use @ to access pdb

1.from table to table

-> IN PROD TS

exp TS/TEST_Data130@TSprod file=D:projectsTSTS_TS_20160908_update.dmp  log=D:projectsTSeais_all_exp_2016_09_02.log  TABLES=(owner.TS_record_his,owner.TS_record)

->IN UAT  (表导入前存在的 )

imp TS/gatepassuat_data2@TSuat file=D:projectsTSTS_TS_20160908_update.dmp log=D:projectsTSTS_upd_imp_2016_09_05.log  fromuser=TS touser=TS tables=(TS_record_his,TS_record)

 ->IN UAT  (表导入前是不存在的 )

imp TS/gatepassuat_data2@TSuat file=D:projectsTSTS_TS_20160908_update.dmp log=D:projectsTSTS_upd_imp_2016_09_05.log  fromuser=TS touser=TS 

expdp/impdp

-> IN PROD user:TS

expdp system/oracle123@TSprod file=TS_TS_20160908_update.dmp  directory=TMP_DIR log=TS_all_exp_2016_09_02.log  tables=('TS.TS_record_his','TS.TS_record')

->IN UAT  for 12c use @ to access pdb

impdp system/oracle123_@TSuat  directory=DUMP_DIR dumpfile=TS_TS_20160908_update.dmp  log=TS_all_imp_2016_09_02.log table_exists_action=replace remap_schema=TS:TS

--tables=('TS_record_his','TS_record') table_exists_action=replace remap_schema=TS:TS;

--tables=TS.TS_record_his,TS.TS_record remap_schema=TS:TS;

-> in prod SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ------------------------------------------------------------------------ SYS                            TMP_DIR D:dba mp

SYS                            XMLDIR D:appAdministratorproduct11.2.0dbhome_1 dbmsxml

SYS                            ORACLE_OCM_CONFIG_DIR D:appAdministratorproduct11.2.0dbhome_1/ccr/state

OWNER                          DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ------------------------------------------------------------------------ SYS                            DATA_PUMP_DIR D:appAdministrator/admin/TSprod/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR2 D:appAdministratorproduct11.2.0dbhome_1/ccr/state

-> in UAT SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS                            DUMP_DIR D:dbadump

SYS                            XMLDIR D:appAdministratorproduct11.2.0dbhome_1 dbmsxml

SYS                            ORACLE_OCM_CONFIG_DIR D:appAdministratorproduct11.2.0dbhome_1/ccr/state

OWNER                          DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS                            DATA_PUMP_DIR D:appAdministrator/admin/TSuat/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR2 D:appAdministratorproduct11.2.0dbhome_1/ccr/state

2.from schema to schema



-Prepare:
c:apporacledump: datapump


select count(*) from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB'))

select owner,object_name from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB'))
and statuS='INVALID';

-charater
SELECT * FROM v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET';   

set NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

select SUM(bytes)/1024/1024 "M" from dba_segments where owner like '%OTD%';


-rollout

in prod  (if do below in pdb , make sure already create datapump  manule )

in prod
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

set system=db

expdp system/oracle123_ file=TS_TS_201702_update.dmp  directory=datapump log=TS_all_exp_2017_09_28.log  SCHEMAS='%system%usr','%system%patch','%system%query','%system%data' parallel=2





export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export user=otdb
expdp system/oracle123_ file=TS_TS_201702_update.dmp  directory=datapump log=TS_all_exp_2017_09_28.log  SCHEMAS=$user'usr',$user'patch',$user'query',$user'data' parallel=2     



 

in uat

1。notice :

如果data 用户包括sequence ,那么在目标端，导入之前，需要手工先drop sequnece ,在导入。

脚本如下：

 spool drop_sequence.sql

set head off feedback off
--select 'drop SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' from dba_sequences where ----------SEQUENCE_OWNER in ('FAMSDATA','SMASDATA');

select 'drop SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' from dba_sequences where SEQUENCE_OWNER like
('%&DATA%') and SEQUENCE_OWNER not in ('ORDDATA');


spool off

2。 (if do below in pdb , make sure already create datapump  manule )


export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export user=rpms
impdp system/sys_ file=TS_TS_201702_update.dmp directory=datapump SCHEMAS=$user'usr',$user'patch',$user'query',$user'data' table_exists_action=replace parallel=2 log=impdp.log;


 

 

in prod

expdp system/oracle123@testuat file=TS_TS_20160928_update.dmp  directory=DATA_PUMP_DIR log=TS_all_exp_2016_09_28.log  SCHEMAS='etestquery','testrusr','testdata' parallel=2;

in uat

impdp tuser/tuser file=TS_TS_20160928_update.dmp  directory=DATA_PUMP_DIR SCHEMAS='etestquery','testrusr','testdata' table_exists_action=replace parallel=2  log=TS_all_imp_2016_09_28.log;

 for 12c use @ to access pdb

impdp tuser/tuser@pdb file=TS_TS_20160928_update.dmp  directory=DATA_PUMP_DIR SCHEMAS='etestquery','testrusr','testdata' table_exists_action=replace parallel=2  log=TS_all_imp_2016_09_28.log;

 执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象。

 Run the utlrp.sql script as SYS user.
@%ORACLE_HOME%/rdbms/admin/utlrp.sql

3.ps

使用imp进行数据导入时，若表已经存在，要先drop掉表，再进行导入。

而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：

1)  skip：默认操作

2)  replace：先drop表，然后创建表，最后插入数据

3)  append：在原来数据的基础上增加数据

4)  truncate：先truncate，然后再插入数据

notice :

如果data 用户包括sequence ,那么在目标端，导入之前，需要手工先drop sequnece ,在导入。

脚本如下：

 spool drop_sequence.sql

set head off feedback off
select 'drop SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' from dba_sequences where SEQUENCE_OWNER in ('FAMSDATA','SMASDATA');
spool off

6.

只导入用户下对象的元数据
impdp bys/bys schemas=bys  directory=dir_dp file=bys5.dmp logfile=bys6.log content=metadata_only
只导入用户下对象的数据
impdp bys/bys schemas=bys  directory=dir_dp file=bys5.dmp logfile=bys7.log content=data_only
导入用户下所有对象及数据：  --不写content=，默认就是ALL
impdp bys/bys schemas=bys  directory=dir_dp file=bys5.dmp logfile=bys8.log

7.for 12c

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/system01.dbf
/centrproddb/data/centrprod/pdbseed/system01.dbf
/centrproddb/data/centrprod/sysaux01.dbf
/centrproddb/data/centrprod/pdbseed/sysaux01.dbf
/centrproddb/data/centrprod/undouser01.dbf
/centrproddb/data/centrprod/users01.dbf
/centrproddb/data/centrprod/penrsprod/system01.dbf
/centrproddb/data/centrprod/penrsprod/sysaux01.dbf
/centrproddb/data/centrprod/penrsprod/penrsprod_users01.dbf
/centrproddb/data/centrprod/pipamprod/system01.dbf
/centrproddb/data/centrprod/pipamprod/sysaux01.dbf

NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/penrsprod/enrs_data_f01.dbf
/centrproddb/data/centrprod/penrsprod/enrs_idx_f01.dbf
/centrproddb/data/centrprod/pipamprod/ipam_data_f01.dbf
/centrproddb/data/centrprod/pipamprod/ipam_idx_f01.dbf
/centrproddb/data/centrprod/pipamprod/test.dbf

16 rows selected.


SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/system01.dbf
/centrproddb/data/centrprod/sysaux01.dbf
/centrproddb/data/centrprod/undouser01.dbf
/centrproddb/data/centrprod/users01.d

--exp 带有query 条件的查询 ,使用parfile ,可以防止query 条件查询 导致的 unix 命令行无法识别的报错。

step 1:

vi /tmp/test.par

userid=dbdata/dbyida_01
file=/dbuatdblog/table_2017.dmp
log=/dbuatdblog/exp2017.log
tables=db_CONFIRMED_GATE_MOVEMENT
query="where to_char(LAST_UPD_DATE,'yyyy-mm-dd') >= '2017-06-01'"

step 2: 

exp parfile=/tmp/test.par

ref

http://blog.csdn.net/kimsoft/article/details/5720814

###new  无需 expdp ,直接走网络impdp

drop directory schema_imp;


connect / as sysdba
create directory schema_imp as '/db/db01/db_impdp';

grant read,write on directory schema_expdp   to public;

impdp_schemas.par:
userid='/ as sysdba'
directory=schema_imp
dumpfile=expdp_schemas_%U.dmp
logfile=impdp_schemas.log
parallel=6
schemas=BBSP,BBSPODS,BCDPETL,DBMONOPR,DBMGR,OVSEE
content=all
network_link=dblink_migup


--- 监控数据库impdp 任务的进度，查看表空间的进度
select * from dba_datapump_jobs;
SYS_IMPORT_SCHEMA_02

####sample 1


1.puserdb03 /usr/user/data/dump
select directory_name,directory_path from dba_directories;
SELECT * FROM v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET';

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


userid='/ as sysdba'
directory=datapump
dumpfile=expdp_useropr_log_%U.dmp
logfile=expdp_useropr_log.log
parallel=4
filesize=10240M
schemas=useropr
content=all

expdp parfile=/usr/user/data/dump


2.root@pnuserdb01

导入数据前关闭约束和触发器,不关闭的话：有可能报错 ，感谢 

 使用数据泵导入数据，导入过程中没有任何错误，导入的记录数完全正常，但应用就是无法使用，取不到数据，找了很久都没找到原因，最后，发现是由于序列的问题引起的，发现导出和导入的值不一样，通过与开发沟通才明白，因为在导入数据时要对一些字段定义做了修改，所以先导入空表，然后再导入数据的，在向表插入数据时会插入序列，而序列是由触发器来产生的，从而导致两边的序列值不一致，引起记录的值有差异，找到原因就好办了，在导入数据前关闭所有的约束和触发器，
SQL>set heading off
SQL>select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;
SQL>alter table table_name disable constraint constraint_name;

SQL>select 'alter trigger '||trigger_name||' disable;' from user_triggers;
SQL>alter trigger trigger_name disable;

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


cat impdp_usermove.par
userid='/ as sysdba'
directory=datapump
dumpfile=expdp_useropr_log_%U.dmp
logfile=impdp_usermove_log.log
parallel=4
REMAP_SCHEMA=useropr:usermove
content=all
table_exists_action=truncate


impdp parfile=/usr/nuser/data/dump

--done
hagrp -freeze sg_nuser_db -sys pnuserdb01

-undone
hagrp -unfreeze sg_nuser_db -sys pnuserdb01

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/feiyun8616/p/5872215.html