OGG部署与初始化

2020-08-29 00:00:00 创建 配置 启动 目标 进程

Ogg初始化-数据泵

Ogg版本11.2

创建用户

授权

创建表空间

管理用户与表空间


SQL> create user oraggs identified by xxxxxx;
SQL> alter user oraggs default tablespace <OGG Tablespace Name> temporary tablespace TEMP quota unlimited on <OGG Tablespace Name>; 

Grant oraggs user permissions 


SQL> grant resource, dba to oraggs;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGGS' ); 


Enable Supplemental logging at the schema level or table level 

----- Source System
GGSCI> DBLOGIN USERID oraggs, PASSWORD xxxx GGSCI> ADD SCHEMATRANDATA APPS
OR
GGSCI> ADD TRANDATA APPS.<TABLENAME> 




启动表级日志

在添加数据库的小补充日志后，Ogg用户增加表的日志记录，这样可以记录主键，update操作不会有问题。

GGSCI (dg1) 3> add trandata scott.*


创建Manager进程


GGSCI (dg2) 1> edit params MGR

-------------------------------------------------------------------

-- GoldenGate Manager

-------------------------------------------------------------------

port 7840


配置源端extract进程

GGSCI (dg1) 5> view params LHREMD2


Extract LHREMD2

-------------------------------------------------------------------

-- Local extract for HR schema

-------------------------------------------------------------------

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID gger, PASSWORD oracle

ExtTrail dirdat/st

Table SCOTT.*;



GGSCI (dg1) 6> ADD EXTRACT LHREMD2,TRANLOG,BEGIN NOW

EXTRACT added.



GGSCI (dg1) 7> ADD EXTTRAIL dirdat/st,EXTRACT LHREMD2,MEGABYTES 100

EXTTRAIL added.



GGSCI (dg1) 8> start ext  LHREMD2


Sending START request to MANAGER ...

EXTRACT LHREMD2 starting



GGSCI (dg1) 9> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           

EXTRACT     RUNNING     LHREMD1     00:00:00      00:00:07    

EXTRACT     RUNNING     LHREMD2     00:01:01      00:00:04    

EXTRACT     RUNNING     PHREMD1     00:00:00      00:00:07    


查询进程 LHREMD2的状态，这里要注意SCN，是进程抓到的早的SCN

GGSCI (dg1) 10> info ext  LHREMD2


EXTRACT    LHREMD2   Last Started 2019-12-13 18:37   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2019-12-13 18:37:11  Seqno 26, RBA 37474816

                     SCN 0.1158391 (1158391)


此时我们登陆数据库，查询此时的SCN，已经向前推进了。

[oracle@dg1 ggs]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 13 18:44:47 2019


Copyright (c) 1982, 2013, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SYS@prod> select current_scn from v$database ;


CURRENT_SCN

-----------

    1158561


SYS@prod> 


配置源端extract pump进程


GGSCI (dg1) 21> view params PHREMD2


Extract PHREMD2

-------------------------------------------------------------------

-- Data Pump extract for HR schema

-------------------------------------------------------------------

PassThru

RmtHost dg2, MgrPort 7840

RmtTrail dirdat/st

Table SCOTT.* ;


GGSCI (dg1) 22> ADD EXTRACT PHREMD2,EXTTRAILSOURCE dirdat/st

EXTRACT added.


GGSCI (dg1) 23> ADD RMTTRAIL dirdat/st,EXTRACT PHREMD2,MEGABYTES 100

RMTTRAIL added.



配置目标段replicat进程


配置Manager进程

GGSCI (dg2) 5> view params mgr


-------------------------------------------------------------------

-- GoldenGate Manager

-------------------------------------------------------------------

port 7840



GGSCI (dg2) 2> view params RHREMD22


Replicat RHREMD22

-------------------------------------------------------------------

-- Replicat for HR Schema

-------------------------------------------------------------------

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID gger, PASSWORD oracle

HandleCollisions                                                                  

AssumeTargetDefs

Map SCOTT.*, Target SCOTT.* ;


添加checkpointtable

GGSCI (dg2) 9> add checkpointtable  scottcheck


Successfully created checkpoint table scottcheck.


GGSCI (dg2) 10> exit

[oracle@dg2 ggs]$ ./ggsci


Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21


Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

这里需要制定进程的checkpointtable



GGSCI (dg2) 2> ADD REPLICAT RHREMD22, EXTTRAIL dirdat/st, checkpointtable gger.scottcheck

REPLICAT added.


启动源端的pump进程


GGSCI (dg1) 6> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           

EXTRACT     RUNNING     LHREMD1     00:00:00      00:00:07    

EXTRACT     RUNNING     LHREMD2     00:00:00      00:00:08    

EXTRACT     RUNNING     PHREMD1     00:00:00      00:00:04    

EXTRACT     STOPPED     PHREMD2     00:00:00      04:27:42    



GGSCI (dg1) 7> start ext  PHREMD2


Sending START request to MANAGER ...

EXTRACT PHREMD2 starting



GGSCI (dg1) 8> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           

EXTRACT     RUNNING     LHREMD1     00:00:00      00:00:02    

EXTRACT     RUNNING     LHREMD2     00:00:00      00:00:03    

EXTRACT     RUNNING     PHREMD1     00:00:00      00:00:00    

EXTRACT     RUNNING     PHREMD2     00:00:00      04:27:58  

在目标端验证trail文件被传送过来，注意时间点

[oracle@dg2 ggs]$ ls -lrt dirdat

total 20

-rw-rw-rw- 1 oracle oinstall 1234 Dec  9 22:06 l2000000

-rw-rw-rw- 1 oracle oinstall 4786 Dec 10 11:31 l2000001

-rw-rw-rw- 1 oracle oinstall 1213 Dec 13 13:55 l2000002

-rw-rw-rw- 1 oracle oinstall 1236 Dec 13 13:55 l2000003

-rw-rw-rw- 1 oracle oinstall    0 Dec 13 23:11 st000000



此时先不启动目标端的REPLICAT进程，因为还没有数据初始化，下面导出导入数据  《《《《这个步骤也可以在源端启动extract进程后完成。



»  Create a database directory:
SYS@prod> create directory dir1 as '/home/oracle';


Directory created.

»  Get the current SCN on the source database :
   SYS@prod> select current_scn from v$database ;


CURRENT_SCN

-----------

    1167015

»  Run the Data Pump Export using the flashback SCN you obtained in the previous step.

[oracle@dg1 ~]$ expdp system/oracle directory=dir1 schemas=scott parallel=4 dumpfile=hrsource_%u.dmp flashback_scn=1167015


[oracle@dg1 ~]$ expdp system/oracle directory=dir1 schemas=scott parallel=4 dumpfile=hrsource_%u.dmp flashback_scn=1167015



拷贝到目标端

[oracle@dg1 ~]$ scp *.dmp dg2:$PWD

oracle@dg2's password: 

hrsource_01.dmp                                                                                           48KB  48.0KB/s   00:00    

hrsource_02.dmp                                                                                           32KB  32.0KB/s   00:00    

hrsource_03.dmp                                                                                           196KB 196.0KB/s   00:00




» Start an import using impdp to the target database when the export step is complete. 


SYS@prod> create directory dir1 as '/home/oracle';


Directory created.


  impdp system/oracle DIRECTORY=dir1 DUMPFILE=hrsource_%u.dmp  schemas=scott

[oracle@dg2 ~]$ impdp system/oracle DIRECTORY=dir1 DUMPFILE=hrsource_%u.dmp  schemas=scott



我们在目标端做个测试，删除表中的记录，修改一条记录

SCOTT@prod> update emp set sal=1111 where empno=7788;


1 row updated.


SCOTT@prod> commit;


Commit complete.


SCOTT@prod> delete from emp where empno=7369;


1 row deleted.


SCOTT@prod> commit;


Commit complete.


此时源端已经捕获到变化了


GGSCI (dg1) 9> stats PHREMD2


Sending STATS request to EXTRACT PHREMD2 ...


Start of Statistics at 2019-12-13 23:24:59.


Output to dirdat/st:


Extracting from SCOTT.EMP to SCOTT.EMP:


*** Latest statistics since 2019-12-13 23:24:20 ***

        Total inserts                                      0.00

        Total updates                                      1.00

        Total deletes                                      1.00

        Total discards                                     0.00

        Total operations                                   2.00


End of Statistics.

这个变化会发送到了目标端



» Start Replicat after import is complete:


GGSCI> START REPLICAT RHREMD22, AFTERCSN 1176710
























相关文章