重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: ogg 11 同步 for linux [打印本页]

作者: mahan 时间: 2025-7-27 09:34
标题: ogg 11 同步 for linux
--一、部署ogg-远端、目标端

1、ogg初始化
--oracle 用户
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export OGG_HOME=/home/oracle/ogg
export  PATH=$ORACLE_HOME/bin:$OGG_HOME:$PATH

2、为ogg创建目录
mkdir /home/oracle/ogg
unzip p22575475_1121032_Linux-x86-64.zip
mv fbo_ggs_Linux_x64_ora11g_64bit.tar /home/oracle/ogg/
cd /home/oracle/ogg
tar -xvf  fbo_ggs_Linux_x64_ora11g_64bit.tar

3、创建子目录--注意存储规划

cd /home/oracle/ogg
./ggsci
GGSCI (test) 1> create subdirs

Creating subdirectories under current directory /home/oracle/ogg

Parameter files             /home/oracle/ogg/dirprm: already exists
Report files                /home/oracle/ogg/dirrpt: created --存放report文件
Checkpoint files             /home/oracle/ogg/dirchk: created --存放checkpoint 文件
Process status files          /home/oracle/ogg/dirpcs: created
SQL script files             /home/oracle/ogg/dirsql: created
Database definitions files    /home/oracle/ogg/dirdef: created
Extract data files          /home/oracle/ogg/dirdat: created --存放trc文件，占用空间最大
Temporary files             /home/oracle/ogg/dirtmp: created
Stdout files                /home/oracle/ogg/dirout: created

二、ogg 准备阶段

1、配置数据库用户--两端都做

create tablespace  tbs_ogg datafile '/home/oracle/oradata/ora11g/tbs_ogg01.dbf' size 100m autoextend on;
create user ogg identified by ogg default tablespace tbs_ogg;
grant dba,connect to ogg;

2、配置数据库日志模式--只在源端

alter database force logging;
alter database  add supplemental log data;
select supplemental_log_data_min from v$database;

3、关闭数据库回收站--源端(10g版本必须关闭，11g+建议)
purge dba_recyclebin;
alter system set recyclebin=off scope=spfile;

4、修改ogg参数--远端和目标端

alter  system set enable_goldengate_replication=true ;

5、配置DDL复制--源端

vi $OGG_HOME/GLOBALS
--新增
GGSCHEMA OGG

--需要在$OGG_HOME目录下执行
--以下脚本会在OGG用户下面创建一些基础表

cd $OGG_HOME
sqlplus / as SYSDBA
@marker_setup
--Enter GoldenGate schema name :ogg
@ddl_setup
@role_setup.sql
grant ggs_ggsuser_role  to  ogg;
@ddl_enable.sql

@?/rdbms/admin/dbmspool.sql
@ddl_pin.sql ogg  --提升DDL性能

@marker_status
@ddl_status ogg

SQL> conn ogg/ogg
Connected.
SQL> select * from tab;

TNAME                         TABTYPE  CLUSTERID
------------------------------ ------- ----------
GGS_DDL_COLUMNS             TABLE
GGS_DDL_HIST                TABLE
GGS_DDL_HIST_ALT             TABLE
GGS_DDL_LOG_GROUPS          TABLE
GGS_DDL_OBJECTS             TABLE
GGS_DDL_PARTITIONS          TABLE
GGS_DDL_PRIMARY_KEYS          TABLE
GGS_DDL_RULES                TABLE
GGS_DDL_RULES_LOG             TABLE
GGS_MARKER                   TABLE
GGS_SETUP                   TABLE
GGS_STICK                   TABLE
GGS_TEMP_COLS                TABLE
GGS_TEMP_UK                   TABLE

14 rows selected.

6、配置 sequence --源端和目标端

--源端运行sequence
@sequence.sql
grant execute on ogg.updateSequence to ogg;
alter table sys.seq$ add supplemental log data (primary key ) columns;

--目标端添加：
@sequence.sql
grant execute  on ogg.replicateSequence to ogg;
grant execute  on dbms_streams_adm_utl_invok to ogg;

7、配置MGR--源端和目标端
--vi 方式
vi /home/oracle/ogg/dirprm/mgr.prm
--或者ggsci 命令行方式
--这里是命令行方式
--ogg 11g版本建议参数写相对路径，绝对路径有问题
edit params mgr

PORT 7809
DYNAMICPORTLIST 7840-7910                                                             --端口号列表，ogg通信用
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepdays 5                            --定时清理trace文件，只保留5一天之内的*/
USERID ogg,PASSWORD ogg
--以下2个参数只需要在源端配置
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10                                        --定期清理ogg.DDLHISTORY 表
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10                                     --定期清理ogg.markerHISTORY 表

--定义数据延迟的预警机制
lagreporthours 1                                                                   --每隔1小时健康检查一次
laginfominutes 30                                                                --一旦发现抽取线程和应用线程延迟超过30分钟，写入延迟信息到alert
lagcriticalminutes 45                                                             --如果延迟时间超过45分钟，会将告警日志写入错误日志中

--以上参数如果配置有问题start 会报错，需要查看错误日志：$OGG_HOME/ggserr.log

8、启动 ogg--两个节点

GGSCI (test2) 5> start mgr
Manager started.

GGSCI (test2) 6> info all
Program    Status    Group    Lag at Chkpt  Time Since Chkpt
MANAGER    RUNNING
GGSCI (test2) 7>

三、ogg 配置
整个配置阶段可分为三部分：启动抓取（源端配置抓取进程和投放进程)、初始化数据和数据应用

1、配置extract --源端

./ggsci

--开启用户级的或者表级的附加日志
dblogin userid ogg password  ogg
--表级添加
add trandata scott.*
--或者添加用户级别，二选一
ADD SCHEMATRANDATA scott

--查看是否开启成功：
info trandata  scott.*
--查看用户级
info schematrandata  scott

2、配置extract 参数文件

edit params ext_tf

extract  ext_tf                                                             --抽取线程名字
SETENV (ORACLE_SID="ora11g")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

USERID ogg,PASSWORD ogg
NUMFILES 5000                                                                --指定抓取线程一次抓取的表数量
EXTTRAIL ./dirdat/tf                                                          --指定抽取进程生产的数据前缀，只能2个字符
DISCARDFILE ./dirrpt/ext_tf.dsc , APPEND,MEGABYTES 100                         --抽取进程报错存放到这个路径下,错误日志最大100MB
DISCARDROLLOVER AT 2:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m                                           --超过2h的长事务告警，每30分钟检查一次
REPORTCOUNT EVERY 30 MINUTES, RATE
TRANLOGOPTIONS EXCLUDEUSER ogg                                              --排除 ogg用户的事务
DDL &                                                                      --DDL 捕获
INCLUDE OBJNAME SCOTT.*;
DDLOPTIONS ADDTRANDATA, GETAPPLOPS, GETREPLICATES, REPORT
TABLE SCOTT.*;                                                                --DML 捕获
SEQUENCE SCOTT.*;

3、映射extract 线程

GGSCI >ADD EXTRACT ext_tf, TRANLOG, BEGIN NOW                            --添加抽取进程，从现在开始抽取日志
GGSCI >ADD EXTTRAIL ./dirdat/tf, EXTRACT ext_tf, MEGABYTES 100          --指定抽取进程对应的存放数据
GGSCI >start ext_tf                                                       --启动抽取进程
GGSCI >info all                                                          --查看状态

--查看$OGG_HOME/dirdata 会发现有生成的文件

-rw-r----- 1 oracle oinstall 1039 Jul 17 18:00 tf000000                   --tf是前面设置的前缀
[oracle@orcl:/home/oracle/ogg/dirdat]$ pwd
/home/oracle/ogg/dirdat
[oracle@orcl:/home/oracle/ogg/dirdat]$

4、配置pump线程--源端

--pump 参数文件
GGSCI >edit params pump_rf
EXTRACT pump_rf
RMTHOST 192.168.13.162, MGRPORT 7809, compress
RMTTRAIL ./dirdat/rf
DISCARDFILE ./dirrpt/pump_rf.dsc , APPEND,MEGABYTES 100             --pump进程对应的报错日志存放位置，最大100MB
NUMFILES 5000
PASSTHRU                                                             --传输方式：透传
TABLE SCOTT.*;
SEQUENCE SCOTT.*;

--添加pump进程：
GGSCI >ADD EXTRACT pump_rf, EXTTRAILSOURCE ./dirdat/tf                      --pump 线程名字必须和参数文件名字一致，这里指的是源端文件
GGSCI >ADD RMTTRAIL ./dirdat/rf, EXTRACT pump_rf, MEGABYTES 100             --这里指向的是目的端文件：把目标端的trace文件和 pump 线程做了个映射，文件大小是100MB
GGSCI> start PUMP_RF
GGSCI> info all

----pump 线程传递数据的方式：
--非透传(默认)： pump 要先读取源端和目标端的表定义，然后根据映射或者转换规则进行转换，再进程数据传输
--透传：       源端和目的端的表结构完全一致时，不读取两端的表定义，直接进行数据传递
----支持DDL复制的表必须得用透传的方式进行复制

--查看目标端看是否有文件生成
[oracle@orcl2:/home/oracle/ogg/dirdat]$ ls -ltr
total 0
-rw-r----- 1 oracle oinstall 0 Jul 18 09:10 rf000000
[oracle@orcl2:/home/oracle/ogg/dirdat]$ pwd
/home/oracle/ogg/dirdat
[oracle@orcl2:/home/oracle/ogg/dirdat]$

5、刷新序列号
---如果用户下不存在任何sequence会报错，忽略即可
dblogin userid ogg password  ogg
flush sequence scott.*

6、数据库初始化

目标端的数据初始化常用三种方式： ogg、expdp/impdp、rman

--这里采用数据泵方式：

--1> 获取并记录源生产库当前的 SCN 号
sqlplus / as sysdba
col CURRENT_SCN for 99999999999999
select current_scn as SCN1 from v$database;
--1024284

--2>当前系统是否存在长事务？
col START_SCN for 99999999999999
col SCHEMANAME for a20
col MACHINE for a20
select
p.INST_ID,p.SPID,s.sid,s.SERIAL#,p.username,s.SCHEMANAME,s.sql_id,t.start_scn,t.START_TIME,t.status,MACHINE
from gv$transaction t,gv$process p,gv$session s
where s.taddr=t.addr and p.addr=s.paddr and t.start_scn <= &SCN1;

--kill 掉长事务
SQL> ! kill -9 &SPID --操作系统 kill 掉查到的 SPID 号

--3>数据导出：以 FLASHBACK_SCN 为参数，使用 expdp 导出生产库数据
SQL> select userenv('language') from dual;
--AMERICAN_AMERICA.AL32UTF8
--导出前需确认 undo 表空间足够大，以免 ORA-01555
expdp system/oracle directory=bak dumpfile=ora11g_%U.dmp logfile=expdp_ora11g.log filesize=30G job_name=expdp1 schemas=scott flashback_scn=1024284

--4>数据导入：

--关闭归档可以提高导入速度
impdp system/oracle directory=bak dumpfile=ora11g_%U.dmp logfile=impdp_ora11g.log full=y table_exists_action=truncate

--5>目标库禁用 job、触发器、外键约束

--通用方式
set serveroutput on;
BEGIN
FOR i IN ( SELECT priv_user,job FROM dba_jobs where priv_user in('SCOTT'))
LOOP
dbms_job.broken(i.job,true);
END LOOP;
END;
/

BEGIN
FOR i IN ( SELECT table_owner,trigger_name,status FROM dba_triggers where table_owner in('SCOTT'))
LOOP
EXECUTE IMMEDIATE 'alter trigger ' ||i.table_owner||'.'|| i.trigger_name || ' disable';
END LOOP;
END;
/

BEGIN
FOR i IN ( SELECT owner,table_name,constraint_name,status,validated FROM dba_constraints WHERE owner IN ('SCOTT')
and constraint_type = 'R')
LOOP
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'|| i.table_name || ' disable constraint ' || i.constraint_name;
END LOOP;
END;
/

7、配置replicate--目标端

--创建 checkpoint 表，
./ggsci
GGSCI > DBLOGIN USERID ogg, PASSWORD ogg
GGSCI > ADD CHECKPOINTTABLE OGG.CHECKPOINTTABLE
--在目录$OGG_HOME 下，创建文件 GLOBALS
[oracle@test-db160 ogg]$ vi GLOBALS
CHECKPOINTTABLE OGG.CHECKPOINTTABLE

--配置进程： rep_rf
GGSCI >edit params rep_rf
REPLICAT rep_rf
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg, PASSWORD ogg
DDL &
INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DISCARDFILE dirrpt/rep_rf.dsc , APPEND                         --存放进程对应的错误日志
DISCARDROLLOVER AT 2:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
NUMFILES 5000
GROUPTRANSOPS 2000
MAXTRANSOPS 3000
ASSUMETARGETDEFS                                              --两端数据结构一致使用此参数，类似extract进程里面的透传方式
MAP SCOTT.*,TARGET SCOTT.*;                                     --源端表和目标端表对应关系
SEQUENCE SCOTT.*,TARGET SCOTT.*;

--添加进程：
GGSCI >ADD REPLICAT rep_rf, EXTTRAIL ./dirdat/rf                --REPLICAT 进程的队列一定要和源端 pump进程里面的远程队列一致
GGSCI >start replicat rep_rf aftercsn 前期获取的 SCN

8、同步测试

--1>DDL create 测试：
--源端：
create table scott.test_tmp(id number,name varchar(20));
--目标端查看：
select * from scott.test_tmp;

----分区表

create table scott.test_tmp2(
id number(12),
name varchar2(30),
age varchar2(10))
partition by range(age)
(partition p1 values less than (20),
partition p2 values less than (40),
partition p3 values less than (60),
partition p_other values less than (maxvalue))
enable row movement;

select * from scott.test_tmp2;

--2>DML  测试：

----INSERT测试
----源端
insert into scott.test_tmp values(1,'test1');
insert into scott.test_tmp values(2,'test2');
commit;
select * from scott.test_tmp;
--目标端
select * from scott.test_tmp;

--分区表
insert into scott.test_tmp2 values(01,'test01',18);
insert into scott.test_tmp2 values(02,'test02',28);
insert into scott.test_tmp2 values(03,'test03',58);
insert into scott.test_tmp2 values(04,'test04',68);
commit;

select * from scott.test_tmp2;
select * from scott.test_tmp2 partition(p1);
select * from scott.test_tmp2 partition(p2);
select * from scott.test_tmp2 partition(p3);
select * from scott.test_tmp2 partition(p_other);

----update测试
-----源端--普通表
update  scott.test_tmp set name='test1111' where id =1;
update  scott.test_tmp set name='test2222' where id =2;
commit;
select * from scott.test_tmp;

-----目标端
select * from scott.test_tmp;

-----源端---分区表
update scott.test_tmp2 set name='test100' where id=01;
update  scott.test_tmp2 set age=19 where id=04;
commit;
----目标端查看
select * from scott.test_tmp2;
select * from scott.test_tmp2 partition(p1);
select * from scott.test_tmp2 partition(p2);
select * from scott.test_tmp2 partition(p3);
select * from scott.test_tmp2 partition(p_other);

----delete测试
---源端
delete from scott.test_tmp;
commit;
select * from scott.test_tmp;

--目标
select * from scott.test_tmp;

----分区表
delete scott.test_tmp2 where id=01;
commit;
select * from scott.test_tmp2;
select * from scott.test_tmp2 partition(p1);
select * from scott.test_tmp2 partition(p2);
select * from scott.test_tmp2 partition(p3);
select * from scott.test_tmp2 partition(p_other);
----DDL drop 测试：
--源端
drop table scott.test_tmp;
select * from scott.test_tmp;

--目标端
select * from scott.test_tmp;

=》分区表
drop table  scott.test_tmp2;
select * from scott.test_tmp2;

----目标端如果遇到问题就会报错暂停同步：

GGSCI (orcl2) 1> info all

Program    Status    Group    Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING
REPLICAT ABENDED    REP_RF    00:00:06    00:03:30    --abended ：异常终止

---查明原因后，可以跳过继续恢复：
GGSCI (orcl2) 3> start  REP_RF skiptransaction

Sending START request to MANAGER ...
REPLICAT REP_RF starting

GGSCI (orcl2) 4> info all

Program    Status    Group    Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING
REPLICAT RUNNING    REP_RF    00:06:38    00:00:02

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)