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;
标题名称：ogg配置例子
网站地址：http://myzitong.com/article/jcsidg.html
=============
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;
标题名称：ogg配置例子
网站地址：http://myzitong.com/article/jcsidg.html