OGG之 各種資料泵初始化指令碼

一、先導後設資料

## 匯出後設資料 （使用者模式）
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_boss2_1_10_metadata_%U.dmp
exclude=statistics
logfile = expdp_boss2_1_10_metadata.log
parallel=4
schemas=(ucr_act21,
uop_act21,
ucr_act22,
uop_act22,
ucr_act41,
uop_act41,
ucr_act42,
uop_act42,
ucr_catalog,
uop_catalog)
content=metadata_only

##匯入後設資料  （使用者模式）
userid ='system/system_cmcc_shsnc2018'
directory=BAK_DIR
dumpfile=expdp_boss2_2_metadata_%U.dmp
logfile = impdp_boss2_2_pk_index.log
parallel=4
schemas=('UIA_ACT21_STA',
'UIA_ACT22_STA',
'UIA_ACT41_STA',
'UIA_ACT42_STA',
'UIF_ACT21_BI',
'UIF_ACT21_STA',
'UIF_ACT22_BI',
'UIF_ACT22_STA',
'UIF_ACT41_BI',
'UIF_ACT41_STA',
'UIF_ACT42_BI',
'UIF_ACT42_STA',
'UIF_PARAM2',
'UIF_PARAM4')
content=metadata_only
INCLUDE=index,CONSTRAINT
remap_tablespace=(
TBS_ACT_DEF:TBS_ACT_HDACT08
USERS:TBS_ACT_HDACT08
TS_VB_ACCT_01:TBS_ACT_HDACT08
TS_VB_INTF_DAT:TBS_ACT_HDACT08
TS_VB_BASE_DAT:TBS_ACT_HDACT08
TS_VB_INFO:TBS_ACT_HIACT07
TS_VB_INTF_IND:TBS_ACT_HIACT07
)

########################################################

# 目標庫disable相關物件
# 目標資料庫重構並open後，需要diable trigger/job/cascade reference；

對應的disable指令碼如下：
set echo off
set verify off
set feedback off
set pagesize 10000
set heading off
set linesize 200
set trim on

spool disable_ref.txt
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where constraint_type in ('R') and owner = 'XPADRPT' and delete_rule='CASCADE' and status ='ENABLED';
spool off

spool disable_trigger.txt

select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from dba_triggers where owner = 'XPADRPT'  and STATUS='ENABLED';

spool off 

spool disable_job.txt

select 'execute DBMS_IJOB.BROKEN('||job||',TRUE); commit;'
       from dba_jobs
       where schema_user = 'XPADRPT' and BROKEN='N';

spool off 

########################################################

二、再導資料

#############################
-- 會列出普通表與分割槽表

select owner,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where owner='UCR_ACT41' and segment_type in ('TABLE','TABLE PARTITION') group by owner,segment_name order by sum(bytes)/1024/1024/1024 desc;

--列出（普通表+分割槽別總數）、分割槽總數
select owner,object_type,count(1) as"NUM" from dba_objects
where owner in ('TEST') and object_type in ('TABLE','TABLE PARTITION')
group by owner,object_type ;

-- dba_tables 中包含普通表與分割槽表

#############################

2.1、使用者模式到資料

##匯出
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UMT_ACT2_data_20180714_%U.dmp
exclude=statistics
logfile = expdp_UMT_ACT2_data_20180714.log
parallel=4
schemas=UMT_ACT2
content=data_only
compression=DATA_ONLY

##匯入
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UMT_ACT2_data_20180714_%U.dmp
exclude=statistics
logfile =impdp_UMT_ACT2_data_20180714.log
parallel=4
schemas=UMT_ACT2

2.2、表模式導資料

##匯出
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UCR_ACT42_1_2_20180715_%U.dmp
exclude=statistics
logfile = expdp_UCR_ACT42_1_2_20180715.log
parallel=4
tables=UCR_ACT42.TF_B_WRITEOFFLOG_D,UCR_ACT42.TI_OH_SMS_2015
content=data_only
compression=DATA_ONLY

##匯入
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UCR_ACT42_3_7_20180716_%U.dmp
logfile =impdp_UCR_ACT42_3_7_20180716.log
parallel=25
tables=UCR_ACT42.TF_B_SCORE_TRADELOG,                  
UCR_ACT42.TS_B_BILL_BEFORE,                 
UCR_ACT42.TF_BH_INTEGRALBILL_OLD,               
UCR_ACT42.TP_INFOCHECK_RESULT_NEW,              
UCR_ACT42.TF_B_PAYLOG

2.3、 某使用者下排除特定表導其他表的資料

## 匯出
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UCR_ACT21_181_20180718_%U.dmp
logfile = expdp_UCR_ACT21_181_20180718.log
parallel=5
SCHEMAS=ucr_act21
content=data_only
compression=DATA_ONLY
exclude=statistics,TABLE:"IN ('XX','XX')"

## 匯入
userid=system/system_cmcc_shsnc2018 
directory=BAK_DIR
dumpfile=expdp_UCR_ACT21_181_20180718_%U.dmp
logfile = impdp_UCR_ACT21_181_20180718.log
parallel=25
SCHEMAS=ucr_act21