OGG简单配置初始化

大家好，欢迎来到IT知识分享网。

1、解压安装包（源端和目标端）

[oradba@test1:/data01/ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 

2、创建ogg表空间和用户并授权（源端和目标端）

SQL> create tablespace tbs_gguser datafile'/data01/orcl1/gguser.dbf' size 50m autoextend on;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace tbs_gguser temporary tablespace temp quota unlimited on tbs_gguser;

User created.

授权（源端）
SQL> grant connect,resource to ogg;
SQL> grant create session,alter session to ogg;
SQL> grant select any dictionary,select any table to ogg;
SQL> grant alter any table to ogg;
SQL> grant flashback any table to ogg;
SQL> grant execute on DBMS_FLASHBACK to ogg;


授权（目标端）
SQL> grant connect,resource to ogg;
SQL> grant create session,alter session to ogg;
SQL> grant select any dictionary,select any table to ogg;
SQL> grant create table to ogg;

3、创建测试用户和表（源端和目标端）

SQL> create user scott identified by tiger account unlock;     

User created.

SQL> grant connect,resource to scott;

Grant succeeded.

create table emp_ogg
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPNO NUMBER(4)
);

CREATE TABLE dept_ogg(
DEPTNO NUMBER(4),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

插入数据(源库)

INSERT INTO emp_ogg VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
INSERT INTO emp_ogg VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp_ogg VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp_ogg VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp_ogg VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp_ogg VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp_ogg VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp_ogg VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp_ogg VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp_ogg VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp_ogg VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp_ogg VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

INSERT INTO dept_ogg VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept_ogg VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept_ogg VALUES (30,'SALES','CHICAGO');
INSERT INTO dept_ogg VALUES (40,'OPERATIONS','BOSTON');

4、授权表（源端和目标端）

conn scott/tiger;
grant insert,update,delete on scott.emp_ogg to ogg;
grant insert,update,delete on scott.dept_ogg to ogg;

5、开启最小附加日志（源端和目标端）

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database; SUPPLEME --------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.


SQL> select force_logging from v$database; FOR ---
NO

SQL> alter database force logging;

Database altered.

6、配置ogg

创建目录（源端和目标端）

[oradba@test1:/data01/ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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


GGSCI (test1) 1>  create subdirs

Creating subdirectories under current directory /data01/ogg

Parameter files                /data01/ogg/dirprm: already exists
Report files                   /data01/ogg/dirrpt: created
Checkpoint files               /data01/ogg/dirchk: created
Process status files           /data01/ogg/dirpcs: created
SQL script files               /data01/ogg/dirsql: created
Database definitions files     /data01/ogg/dirdef: created
Extract data files             /data01/ogg/dirdat: created
Temporary files                /data01/ogg/dirtmp: created
Stdout files                   /data01/ogg/dirout: created


使用ogg用户连接数据库（源端和目标端）

GGSCI (test1) 1> dblogin userid ogg,password ogg
Successfully logged into database.

添加要同步的表（源端）
GGSCI (test1) 2> add trandata scott.emp_ogg

2018-05-19 16:22:04  WARNING OGG-00706  Failed to add supplemental log group on table SCOTT.EMP_OGG due to ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL ALTER TABLE "SCOTT"."EMP_OGG" ADD SUPPLEMENTAL LOG GROUP "GGS_70051" ("EMPNO") ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.

GGSCI (test1) 3> add trandata scott.emp_ogg

Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.

GGSCI (test1) 4> add trandata scott.dept_ogg

2018-05-19 16:37:10  WARNING OGG-00869  No unique key is defined for table 'DEPT_OGG'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.

//报错是因为这个表没有主健，所以ogg会把所有的列当做主键来看，不过不会在表上加主键

可以查看同步哪些表
GGSCI (test1) 5> info trandata scott.*

Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG.

Columns supplementally logged for table SCOTT.DEPT_OGG: DEPTNO, DNAME, LOC.

Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.

Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.

7、配置ogg管理进程manager

（源端）
GGSCI (test1) 7> edit params mgr

port 7809
PURGEOLDEXTRACTS /dirdat,USECHECKPOINTS

GGSCI (test1) 18> start mgr

Manager started.

GGSCI (test1) 19> info mgr

Manager is running (IP port test1.7809).


（目标端）
GGSCI (test2) 3> edit params mgr

port 7800
PURGEOLDEXTRACTS /data01/ogg/dirdat,USECHECKPOINTS

GGSCI (test2) 4> start mgr

Manager started.

GGSCI (test2) 5> info mgr

Manager is running (IP port test2.7800).

8、创建抽取进程

（源端）
GGSCI (test1) 20> add extract eini_1,sourceistable
EXTRACT added.


GGSCI (test1) 23> INFO EXTRACT *,TASKS

EXTRACT    EINI_1    Initialized   2018-05-19 17:31   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


GGSCI (test1) 25> EDIT PARAMS EINI_1

EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID ogg, PASSWORD ogg
RMTHOST 192.168.137.128, MGRPORT 7800
RMTTASK REPLICAT, GROUP RINI_1
TABLE scott.EMP_OGG;
TABLE scott.DEPT_OGG;

（目标端）
GGSCI (test2) 8> edit params rini_1

REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE
MAP scott.*, TARGET scott.*;

GGSCI (test2) 14> add replicat RINI_1,specialrun

9、开始同步（源端）

GGSCI (test1) 78> start eini_1

Sending START request to MANAGER ...
EXTRACT EINI_1 starting

查看日志

GGSCI (test1) 82> VIEW REPORT EINI_1


2018-05-20 17:36:00  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
 Oracle GoldenGate Capture for Oracle
 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16

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


 Starting at 2018-05-20 17:36:00
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Oct 19 11:24:13 EDT 2016, Release 3.10.0-514.el7.x86_64
Node: test1
Machine: x86_64
 soft limit hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 33286

Description: 

***********************************************************************
** Running with the following parameters **
***********************************************************************

2018-05-20 17:36:00  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID ogg, PASSWORD ***
RMTHOST 192.168.137.128, MGRPORT 7800
RMTTASK REPLICAT, GROUP RINI_1
TABLE scott.EMP_OGG;
Using the following key columns for source table SCOTT.EMP_OGG: EMPNO.

TABLE scott.DEPT_OGG;

2018-05-20 17:36:24  WARNING OGG-00869  No unique key is defined for table 'DEPT_OGG'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may b
e used to define the key.
Using the following key columns for source table SCOTT.DEPT_OGG: DEPTNO, DNAME, LOC.


2018-05-20 17:36:24  INFO    OGG-01815  Virtual Memory Facilities for: COM
 anon alloc: mmap(MAP_ANON) anon free: munmap
 file alloc: mmap(MAP_SHARED) file free: munmap
 target directories:
 /data01/ogg/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                               64G
CACHEPAGEOUTSIZE (normal):                8M
PROCESS VM AVAIL FROM OS (min):         128G
CACHESIZEMAX (strict force to disk):     96G

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252" 
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "WE8MSWIN1252" 

Processing table SCOTT.EMP_OGG

Processing table SCOTT.DEPT_OGG

***********************************************************************
* ** Run Time Statistics **                         *
***********************************************************************


Report at 2018-05-20 17:37:00 (activity since 2018-05-20 17:36:20)

Output to RINI_1:

From Table SCOTT.EMP_OGG:
 # inserts: 12
 # updates: 0
 # deletes: 0
 # discards: 0
From Table SCOTT.DEPT_OGG:
 # inserts: 4
 # updates: 0
 # deletes: 0
 # discards: 0


REDO Log Statistics
  Bytes parsed                    0
  Bytes output                 2684

10、目标端查看数据已同步过来

SQL> select * from DEPT_OGG;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from EMP_OGG;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      DEPNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      DEPNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

12 rows selected.