OGG新添同步用户步骤

 

参考文档：MOS 1332674.1

 

一、停止抽取、投递、应用进程

注：停止抽取进程后，等待dphp进程把trail文件全部投递后再停止投递进程，等待应用进程全部应用后停止应用进程

$ ggsci --进入OGG命令行接口

stop exthp --停止源库抽取进程，若有长事务无法停止

stop dphp --停止源库投递进程

stop rephp --停止目标库应用进程

 

二、源库新添用户、表级别添加附加日志

注：ogg对符号很敏感，不要添加分号

$ ggsci --进入OGG命令行接口

dblogin userid goldengate password goldengate --ogg用户登录数据库




add trandata agcard.* --添加用户级别附加日志




add trandata a.test --添加表级别附加日志

 

 

三、修改源库抽取、投递进程

edit params exthp --编辑抽取进程参数文件，添加新添用户、表

示例内容如下：

EXTRACT myext01

SETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERID ggs@pcdb, PASSWORD ggs

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 3000

TRANLOGOPTIONS ASMUSER sys@ASM, ASMPASSWORD grid

EXTTRAIL /goldengate/ogg/dirdat/et

DISCARDFILE /goldengate/ogg/dirrpt/myext01.dsc APPEND,MEGABYTES 100

WARNLONGTRANS 2h,CHECKINTERVAL 3m

DBOPTIONS ALLOWUNUSEDCOLUMN

CHECKPOINTSECS 3

EOFDELAY 3

FLUSHSECS 3

DDL INCLUDE ALL

TABLE USERPC.*;

TABLE NODE.*; --新添同步用户

TABLE CARD.TEST; --新添表

 

 

 

edit params dphp --编辑投递进程参数

示例内容如下：

EXTRACT mypump01

RMTHOST slnode3, MGRPORT 7840,TCPBUFSIZE 100000,TCPFLUSHBYTES 300000

EOFDELAY 3

FLUSHSECS 5

PASSTHRU

RMTTRAIL /mygoldengate/ogg/dirdat/te

TABLE ORACHEN.*;

TABLE TEST.*; --新添同步用户

TABLE A.TEST; --新添同步表

 

 

四、源库启动抽取、投递进程

start expdp --启动抽取进程

start dphp --启动投递进程

五、查询源库current_scn号

sqlplus / as sysdba --登录数据库

set numwidth 30 --设置数字显示宽度

select current_scn from v$database; --查询源库当前scn号

 

 

六、源库导出一致性数据

注：导出过程需时刻观察undo表空间使用情况，若导出的一致性数据量过大，需提前准备好足够大的undo空间

sqlplus orachen/orachen --登录导出用户

create directory ectonnodedir as '/rmanbak/expdp/'; --创建dmp目录

create directory agcarddir as '/rmanbak/expdp/'; --创建dmp目录

expdp node/2178 dumpfile=node_2.dmp directory=nodedir buffer=1000000 schemas=node flashback_scn=89485069308 logfile=node_dmp_2.log --导出一致性数据




expdp card/8912 dumpfile=card.dmp directory=carddir buffer=1000000 schemas=card EXCLUDE=TABLE:"IN('T_FLOWLOGS')" flashback_scn=89488310310   logfile=card_dmp.log --导出一致性数据，排除T_FLOWLOGS表

 

表空间查询语句：

--检查当前所有表空间的空间使用情况

set linesize 500 pagesize 500

col tablespace_name format a15

col Sum_space(M) format a20

col Used_space(M) format a20

col Free_space(M) format a20

select a.tablespace_name,

       totalspace || ' M' "Sum_space(M)",

       round((totalspace - nvl(freespace, 0)), 3) || ' M' "Used_space(M)",

       round(((totalspace - nvl(freespace, 0)) / totalspace), 3) * 100 "Used_Rate(%)",

       nvl(freespace, 0) || ' M' "Free_space(M)"

  from (select tablespace_name, sum(bytes) / 1048576 totalspace

          from dba_data_files

         group by tablespace_name) a,

       (select tablespace_name, sum(Bytes) / 1048576 freespace

          from dba_free_space

         group by tablespace_name) b

 where a.tablespace_name = b.tablespace_name(+)

--and ((totalspace - nvl(freespace, 0)) / totalspace) * 100 > 90

--and nvl(freespace,0) < 1000  -- only list TSs < 1GB free

union

select a.tablespace_name,

       space || ' M' "Sum_space(M)",

       used_space || ' M' "Used_space(M)",

       round(nvl(used_space, 0) / space * 100, 2) "Used_Rate(%)",

       nvl(free_space, 0) || ' M' "Free_space(M)"

  from (select tablespace_name,

               round(sum(bytes) / (1024 * 1024), 2) space,

               sum(blocks) blocks

          from dba_temp_files

         group by tablespace_name) a,

       (select tablespace_name,

               round(sum(bytes_used) / (1024 * 1024), 2) used_space,

               round(sum(bytes_free) / (1024 * 1024), 2) free_space

          from v$temp_space_header

         group by tablespace_name) b

 where a.tablespace_name = b.tablespace_name(+);

 

七、目标库创建相应的用户、表空间、权限

create tablespace TBS_NODE datafile '/oradata/ecton/NODE_ts01.dbf' size 10G autoextend off; --创建表空间

create tablespace TBS_CARD datafile '/oradata/ecton/CARD_ts01.dbf' size 4G autoextend off; --创建表空间

alter tablespace TBS_CARD add datafile '/oradata/ecton/CARD_ts2.dbf' size 10G autoextend off; --添加数据文件

create user NODE identified by ec2178 default tablespace TBS_NODE temporary tablespace temp; --创建用户

create user CARD identified by ag8912 default tablespace TBS_CARD temporary tablespace temp; --创建用户

用户赋权：

grant connect,resource to NODE;

grant create any view to NODE;

grant create any SYNONYM to NODE;

grant create any directory to NODE;

grant create database link to NODE;




grant connect,resource to CARD;

grant create any view to CARD;

grant create any SYNONYM to CARD;

grant create any directory to CARD;

grant create database link to CARD;

 

八、将源端一致性数据导入目标库

sqlplus NODE/2178 --登录导入用户

create directory NODEdir as '/oracle/expdp/'; --创建imp目录

impdp NODE/2178 directory=NODEdir dumpfile=NODE.dmp buffer=1000000 logfile=NODE_imp.log --目标库导入一致性数据

 

 

九、目标库禁用新添用户下所有对象的trigger、外键约束、dml相关job

 

SELECT 'alter trigger '||owner||'.'||trigger_name||' disable'||';' from dba_triggers where owner in('NODE','*'); --查询禁用触发器语句

SELECT 'alter trigger '||owner||'.'||trigger_name||' disable'||';' from dba_triggers where owner in('CARD','*');




select trigger_name,status from dba_triggers  where owner in('NODE','*'); --检查禁用后触发器状态

select trigger_name,status from dba_triggers  where owner in('CARD','*');




SELECT 'alter table ' || owner ||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type='R' and owner in ('NODE','*'); --查询禁用外键约束语句

SELECT 'alter table ' || owner ||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type='R' and owner in ('CARD','*');




select table_name,constraint_name,status from dba_constraints where  constraint_type='R' and owner in ('CISMON','*'); --查看禁用后外键约束状态

select table_name,constraint_name,status from dba_constraints where  constraint_type='R' and owner in ('CARD','*');




select job_name,enabled,owner from dba_scheduler_jobs where owner not in ('SYS','SYSTEM'); --查询job并禁用

exec dbms_scheduler.disable('CISMON.TEST'); --禁用job

select * from dba_jobs;

select * from dba_autotask;

 

十、编辑目标库应用进程

edit params rephp --编辑目标库应用进程参数

示例内容如下：

REPLICAT rept01

SETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERID ggs@pcdb,PASSWORD ggs

ASSUMETARGETDEFS

APPLYNOOPUPDATES

REPERROR (DEFAULT, ABEND)

DDL include mapped

DDLERROR DEFAULT DISCARD

DDLOPTIONS REPORT

ALLOWNOOPUPDATES

DISCARDFILE /mygoldengate/ogg/rept01.dsc,append,megabytes 100

MAP USERPC.*, TARGET USERPC.*;

MAP NODE.*,TARGET NODE.*,filter (@getenv("TRANSACTION", "CSN") > 89482350475); --新添同步用户的映射关系,利用filter函数指定该用户下所有数据从一致性scn后开始应用

 

 

十一、启动目标库应用进程并校验

start rephp --启动目标库应用进程