Shulou(Shulou.com)06/01 Report--

What is the initialization script of various data pumps in OGG? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. pilot metadata

# # Export metadata (user mode)

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

# # Import metadata (user mode)

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 related objects in the target library

# diable trigger/job/cascade reference is required after the target database is reconstructed and open

The corresponding disable script is as follows:

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

# # #

Second, redirect data

# #

-- regular tables and partition tables are listed

Select owner,segment_name,sum (bytes) / 1024 desc 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 desc

-list (general table + total number of partitions), total number of partitions

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 contains regular tables and partition tables

# #

2.1, user mode to data

# # Export

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

# # Import

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. Table schema import data

# # Export

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

# # Import

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. A user excludes data from specific tables that guide other tables.

# # Export

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')"

# # Import

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

This is the answer to the question about what is the initialization script of various data pumps in OGG. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.