ogg配置例子

add114.t_name_sequence   
=============
MAP add114.bst_workpaper, TARGET add114.bst_workpaper;
======================       


expdp userid=system/oracle 




expdp userid=system/oracle add114_ogg.par
directory=expdp_dir
tables=(add114.t_name_sequence ) 
filesize=10G
dumpfile=add114_ogg_%U.dmp
logfile=add114_ogg.log
content=all
flashback_scn=12739732806371
version=10.2.0.3.0


col current_scn for 999999999999999999
select current_scn from v$database;
expdp userid=system/oracle add114_ogg_bak.par
directory=dmpdir
tables=(add114.t_name_sequence ) 
filesize=10G
dumpfile=add114_ogg_bak_%U.dmp
logfile=add114_ogg_bak.log
content=all








同步表統計：
--查外鍵表NC56.CSCI有無對應的主鍵表  
 SELECT a.owner,
        A.TABLE_NAME      primary_table_name,
        A.CONSTRAINT_NAME primary_table_key_name,
        b.owner,
        B.TABLE_NAME      foreign_table_name,
        B.CONSTRAINT_NAME foreign_table_foreign_key_name,
        B.STATUS          foreign_table_foreign_key_stat
   FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
  WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
    and B.CONSTRAINT_TYPE = 'R'
    AND b.TABLE_NAME = 'EMP'
    and a.owner = 'SCOTT'
    and b.owner = 'SCOTT'
  ORDER BY 1, 2, 3, 4;
--查主鍵表NC56.CBH有無對應的外鍵表  
SELECT a.owner,
       A.TABLE_NAME      primary_table_name,
       A.CONSTRAINT_NAME primary_table_key_name,
       b.owner,
       B.TABLE_NAME      foreign_table,
       B.CONSTRAINT_NAME foreign_table_foreign_key_name,
       B.STATUS          foreign_table_foreign_key_stat
  FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
 WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
   and B.CONSTRAINT_TYPE = 'R'
   AND a.TABLE_NAME = 'EMP'
   and a.owner = 'SCOTT'
   and b.owner = 'SCOTT'
 ORDER BY 1, 2, 3, 4;




==========================
====
select 'alter table '|| t.table_name||' disable constraint '||t.constraint_name||';'   
from user_constraints t where t.constraint_type = 'R'  
order by t.table_name   




啟用外鍵
[sql] view plain copy
select 'alter table '|| t.table_name ||' enable constraint '||t.constraint_name||';'  
from user_constraints t where t.constraint_type = 'R'  
order by t.table_name 
==






grant connect,resource to add114;
grant create view to add114;
grant CREATE DATABASE LINK to ADD114;
grant CREATE SYNONYM to ADD114;
grant DEBUG CONNECT SESSION to ADD114;








dblogin userid oggadmin,password oggadmin#123


add trandata add114.t_name_sequence    








table add114.t_name_sequence;        
 
map add114.t_name_sequence,        target add114.t_name_sequence;       
   












nohup impdp userid=system/oracle114 directory=dmpdir dumpfile=add114_ogg_01.dmp logfile=add114_ogg_impdp.log content=data_only &


alter trigger add114.TR_AIUD_T_CUSTOMER disable;






start replicat repbk,aftercsn 12739888465710








============================
create user oggadmin identified by oggadmin#123 default tablespace users ;






oggadmin.bst_range_pub_tel_test
source端配置extract進程和DATA PUMP進程


add extract extbk,tranlog,begin now
add exttrail /oradata1/ogg/dirdat/jfbak/tr, extract extbk, megabytes 100
edit params extbk
extract extbk
USERID oggadmin, PASSWORD oggadmin#123,
EXTTRAIL /oradata1/ogg/dirdat/jfbak/tr
TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE 4194304, BUFSIZE 4194304
FETCHOPTIONS FETCHPKUPDATECOLS
NOCOMPRESSDELETES
DYNAMICRESOLUTION
table oggadmin.t_customer_tel_log_test;




=================
add extract ppbk,exttrailsource /oradata1/ogg/dirdat/jfbak/tr
add rmttrail /oradata1/ogg/dirdat/jfbak/td,extract ppbk,megabytes 100
edit param ppbk
extract ppbk
USERID oggadmin, PASSWORD oggadmin#123,
rmthost 10.6.241.76,mgrport 7809, compress
rmttrail /oradata1/ogg/dirdat/jfbak/td
Dynamicresolution
numfiles 8000
table oggadmin.t_customer_tel_log_test;
==================
目標端
edit param ./GLOBALS
GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpointtable
==
add checkpointtable oggadmin.checkpointtable
==
add replicat repbk,exttrail /oradata1/ogg/dirdat/jfbak/td,checkpointtable oggadmin.checkpointtable
edit param repbk
replicat repbk
USERID oggadmin, PASSWORD oggadmin#123,
ASSUMETARGETDEFS 
REPERROR DEFAULT,DISCARD
DISCARDFILE /oradata1/ogg/dirrpt/repbk.dsc,append,megabytes 100
ASSUMETARGETDEFS
DYNAMICRESOLUTION
MAP oggadmin.t_customer_tel_log_test, TARGET oggadmin.t_customer_tel_log_test;




==============
 dblogin userid oggadmin,password oggadmin#123
 
 
===
===刪除物化日志==drop materialized view log on  create materialized view log on T1 with rowid
drop materialized view log on BST_SYS_ROLE;            
      


create materialized view log on BST_SYS_ROLE with rowid;        




alter table T_BALANCE_WARNING disable constraint FK_T_BALANCE_WARNING_ORDER_ID;