상세 컨텐츠

본문 제목

Oracle GoldenGate install and lab

Oracle/OGG

by 야솔아빠 2014. 7. 4. 17:48

본문

반응형

Oracle Golden Gate install and Lab


0. 환경설정

  • OGG workshop virtual box 설치
    아래 virutal box 설명정보

    The following VirtualBox 4.2.6 image consists of Oracle GoldenGate labs running on OEL5, x64. The image memory is configured for 4 GB RAM and guest CPU's are set to 2.


    The image contains:

    Oracle GoldenGate 11.2 (labs available)

    Oracle GoldenGate Monitor

    Oracle GoldenGate Veridata (lab available)

    Oracle GoldenGate Director


    Databaases include:

    Oracle 10gR2

    Oracle 11gR2

    MySQL 5.5

  • source DB start
    cd /home/oracle/pre_lab
    ./crs_start.sh
    ./start10g.sh

  • target DB start
    - 이미 구동 되어 있음
    - ps -ef | grep pmon

    [oracle@ogg-wkshp pre_lab]$ ps -ef | grep pmon
    oracle    4513     1  0 08:46 ?        00:00:05 asm_pmon_+ASM
    oracle    4637     1  0 08:47 ?        00:00:05 ora_pmon_ORCL11
    oracle    6971     1  0 09:59 ?        00:00:03 ora_pmon_ORCL10
    oracle   20315 18978  0 18:39 pts/1    00:00:00 grep pmon

  • 테이트(업무) 유저 구성 - source/target 동일
    - source DB

[oracle@ogg-wkshp ~]$ source 10g.env

[oracle@ogg-wkshp ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 3 22:19:08 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create user app identified by app;

User created.

SQL> grant connect, resource to app;

Grant succeeded.

SQL> exit


- target DB


[oracle@ogg-wkshp ~]$ source 11gR2.env

[oracle@ogg-wkshp ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 3 22:23:42 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user app identified by app;

User created.

SQL> grant connect, resource to app;

Grant succeeded.

SQL> exit


1. OGG 설치

  • 계정 및 clear

[oracle@ogg-wkshp ~]$ source 10g.env
[oracle@ogg-wkshp ~]$ cd Lab1

[oracle@ogg-wkshp Lab1]$ ./Workshop_Reset.sh 

[oracle@ogg-wkshp Lab1]$ ./prep_ogg.sh 1


Workshop_Reset.sh

- 설치된 OGG 삭제 및 OGGUSER DB user 삭제
  • OGG 를 위한 DB 유저 구성 및 권한 설정

아래 명령 수행하여 OGGUSER 계정 생성 및 권한 부여


[oracle@ogg-wkshp ~]$ sqlplus / as sysdba


CREATE TABLESPACE "GOLDENGATE" DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

CREATE USER OGGUSER
        IDENTIFIED BY Oracle1
        DEFAULT TABLESPACE GOLDENGATE
        TEMPORARY TABLESPACE TEMP
        ACCOUNT UNLOCK;
        -- 2 Roles for OGGUSER
GRANT CONNECT TO OGGUSER;
GRANT DBA TO OGGUSER;
GRANT UNLIMITED TABLESPACE TO OGGUSER;
GRANT SELECT ANY DICTIONARY TO OGGUSER;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

  • OGG install

- 아래 명령을 수행하여 환경변수 확인

[oracle@ogg-wkshp Lab1]$ env | grep ORACLE
ORACLE_SID=ORCL10
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10g
[oracle@ogg-wkshp Lab1]$
[oracle@ogg-wkshp Lab1]$ env | grep LD_LIBR
LD_LIBRARY_PATH=/u01/app/oracle/product/10g/lib:/u01/software/jdk1.6.0_37/jre/lib/amd64/server/libjvm.so
[oracle@ogg-wkshp Lab1]$
[oracle@ogg-wkshp Lab1]$
[oracle@ogg-wkshp Lab1]$ env | grep JAVA
JAVA_HOME=/u01/software/jdk1.6.0_37


- OGG install


[oracle@ogg-wkshp Lab1]$ cd stage/
[oracle@ogg-wkshp Lab1]$ cp fbo_ggs_Linux_x64_ora10g_64bit.tar $GG/.
[oracle@ogg-wkshp Lab1]$ cd $GG/.

[oracle@ogg-wkshp 10gogg]$ tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar 

[oracle@ogg-wkshp 10gogg]$ ldd mgr

* ldd mgr : check to make sure the OGG libaraies are linked with the Oracle Libraries.


[oracle@ogg-wkshp 10gogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb  5 2013 08:12:25

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



GGSCI (ogg-wkshp.us.oracle.com) 3> create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/10gogg

Parameter files                /u01/app/oracle/product/10gogg/dirprm: already exists
Report files                   /u01/app/oracle/product/10gogg/dirrpt: created
Checkpoint files               /u01/app/oracle/product/10gogg/dirchk: created
Process status files           /u01/app/oracle/product/10gogg/dirpcs: created
SQL script files               /u01/app/oracle/product/10gogg/dirsql: created
Database definitions files     /u01/app/oracle/product/10gogg/dirdef: created
Extract data files             /u01/app/oracle/product/10gogg/dirdat: created
Temporary files                /u01/app/oracle/product/10gogg/dirtmp: created
Stdout files                   /u01/app/oracle/product/10gogg/dirout: created

GGSCI (ogg-wkshp.us.oracle.com) 3> exit


* create subdirs 명령으로 하위 디렉토리까지 생성하면 설치 완료


2.Lab


GLOBAL parameter 설정 (source, target 모두)

[oracle@ogg-wkshp 10gogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb  5 2013 08:12:25

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



GGSCI (ogg-wkshp.us.oracle.com) 1> view param ./GLOBALS
ERROR: PARAM file ./GLOBALS does not exist.


GGSCI (ogg-wkshp.us.oracle.com) 2> edit params ./GLOBALS



GGSCI (ogg-wkshp.us.oracle.com) 3> view param ./GLOBALS

GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.GGSCHKPT
SYSLOG NONE


* 터미널에서 source, target system 설정방법

- source

[oracle@ogg-wkshp ~]$ source 10g.env


-target

[oracle@ogg-wkshp ~]$ source 11gR2.env



mgr parameter 설정

[oracle@ogg-wkshp 10gogg]$ cd dirprm
[oracle@ogg-wkshp dirprm]$ ls -altr
total 16
-rwxr-x---  1 oracle oinstall   53 Feb  5  2013 jagent.prm
drwxr-x---  2 oracle oinstall 4096 Feb  5  2013 .
drwxr-xr-x 14 oracle oinstall 4096 Jul  4 01:00 ..


source db에서 mgr parameter 를 변경한다.

port 9010


[oracle@ogg-wkshp 10gogg]$ ./ggsci


GGSCI (ogg-wkshp.us.oracle.com) 1> edit param mgr



GGSCI (ogg-wkshp.us.oracle.com) 2> view param mgr

PORT 9010

GGSCI (ogg-wkshp.us.oracle.com) 3> exit


[oracle@ogg-wkshp 10gogg]$ cd dirprm
[oracle@ogg-wkshp dirprm]$ ls -altr
total 20
-rwxr-x---  1 oracle oinstall   53 Feb  5  2013 jagent.prm
drwxr-xr-x 14 oracle oinstall 4096 Jul  4 01:00 ..
-rw-rw-rw-  1 oracle oinstall   11 Jul  4 01:02 mgr.prm
drwxr-x---  2 oracle oinstall 4096 Jul  4 01:02 .



target db에서 mgr parameter 를 변경한다.

port 9020


[oracle@ogg-wkshp 11gR2ogg]$ ./ggsci


GGSCI (ogg-wkshp.us.oracle.com) 1> edit param mgr



GGSCI (ogg-wkshp.us.oracle.com) 2> view param mgr

PORT 9020




Manager 기동 (source, target)

GGSCI (ogg-wkshp.us.oracle.com) 16> start mgr

Manager started.


GGSCI (ogg-wkshp.us.oracle.com) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          



시나리오

2.1 단방향 테이블 복제


시나리오 설명 : source DB의 simple_table, simple_table2 테이블의 데이터를 target DB 로 복제


  • 테이블 생성 (source/target)


[oracle@ogg-wkshp dirsql]$ sqlplus app/app

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 7 01:15:36 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DROP TABLE simple_table;
DROP TABLE simple_table
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE simple_table (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (20) NOT NULL,
  FIELD3   VARCHAR2 (20) NOT NULL,
  NUM1     NUMBER        NOT NULL,
  DATE1    DATE,
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3)
  USING INDEX
  );

Table created.


SQL> DROP TABLE simple_table2;


SQL> CREATE TABLE simple_table2 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  DATE1    DATE,
  PRIMARY KEY ( NUM1, FIELD1)
  USING INDEX
  );


SQL> exit



  • source 시스템 OGG 설정

[oracle@ogg-wkshp 10gogg]$ ggsci

GGSCI (ogg-wkshp.us.oracle.com) 2> dblogin userid ogguser, password Oracle1
Successfully logged into database.


GGSCI (ogg-wkshp.us.oracle.com) 3> info trandata app.*

Logging of supplemental redo log data is disabled for table APP.SIMPLE_TABLE.

Logging of supplemental redo log data is disabled for table APP.SIMPLE_TABLE2.

GGSCI (ogg-wkshp.us.oracle.com) 4> add trandata app.*

Logging of supplemental redo data enabled for table APP.SIMPLE_TABLE.

Logging of supplemental redo data enabled for table APP.SIMPLE_TABLE2.


add trandata app.*

The ADD TRANDATA command adds supplemental logging to the tables. Supplemental
logging ensures that all of the relevant information about update and delete operations are
recorded in the Oracle redo logs so that Oracle GoldenGate can replicate the transactions
for those tables correctly.


  • target 시스템 OGG 설정

CheckPoint table 생성


[oracle@ogg-wkshp 11gR2ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

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



GGSCI (ogg-wkshp.us.oracle.com) 1> dblogin userid ogguser, password Oracle1
Successfully logged into database.

GGSCI (ogg-wkshp.us.oracle.com) 2> add checkpointtable

No checkpoint table specified, using GLOBALS specification (OGGUSER.GGSCHKPT)...

Successfully created checkpoint table OGGUSER.GGSCHKPT.


  • Extract 설정 및 기동 (source)

extract 는 Online Redo/Active Log 를 읽어서 변경데이터를 추출하는 역활을 한다

아래 ext01.prm 이 없으면 아래 에러가 발생

GGSCI (ogg-wkshp.us.oracle.com) 1> start ext01
ERROR: Parameter file /u01/app/oracle/product/10gogg/dirprm/ext01.prm does not exist.

[oracle@ogg-wkshp 10gogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb  5 2013 08:12:25

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



GGSCI (ogg-wkshp.us.oracle.com) 1> edit param ext01


--# Extract Name
extract ext01
--# DB Information
UserID ogguser, Password Oracle1

--#Management Discard File
DiscardFile ./dirout/ext01.dec, append, megabytes 50
DisCardRollover at 00:01

--# Report File (Not using at Product env)
ReportCount Every 1 Records, Rate
ReportRollover at 00:01

--# Warn for long running txns
WARNLONGTRANS 1H, CHECKINTERVAL 10m

--# Trail (Ext)
ExtTrail ./dirdat/ex

--# Table List
TABLE APP.*;


Extract process 기동

[oracle@ogg-wkshp 10gogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb  5 2013 08:12:25

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



GGSCI (ogg-wkshp.us.oracle.com) 1> dblogin userid ogguser, password Oracle1
Successfully logged into database.

GGSCI (ogg-wkshp.us.oracle.com) 2> add extract ext01, tranlog, begin now
EXTRACT added.


GGSCI (ogg-wkshp.us.oracle.com) 3> add exttrail ./dirdat/ex, extract ext01, megabytes 50
EXTTRAIL added.


GGSCI (ogg-wkshp.us.oracle.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     STOPPED     EXT01       00:00:00      00:00:40   



GGSCI (ogg-wkshp.us.oracle.com) 9> start ext01

Sending START request to MANAGER ...
EXTRACT EXT01 starting


GGSCI (ogg-wkshp.us.oracle.com) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT01       00:00:00      00:15:48    


GGSCI (ogg-wkshp.us.oracle.com) 11> exit
[oracle@ogg-wkshp 10gogg]$ cd dirdat
[oracle@ogg-wkshp dirdat]$ ls -altr
total 16
drwxr-xr-x 17 oracle oinstall 4096 Jul  7 02:36 ..
-rw-rw-rw-  1 oracle oinstall 1059 Jul  7 02:36 ex000000
drwxrwxr-x  2 oracle oinstall 4096 Jul  7 02:36 .

*참고 : source DB가 RAC 2-Node 일 경우, "add extract ext01, tranlog, thread 2 begin now" 를 사용

*로그보는 방법 : GGSCI (ogg-wkshp.us.oracle.com) 10> view report ext01


  • Pump (Extract) 구성 및 기동

Pump Process : 변경데이터를 전송하는 역활

[oracle@ogg-wkshp 10gogg]$ ./ggsci

GGSCI (ogg-wkshp.us.oracle.com) 2> edit param pmp01

--# Pump Name
extract pmp01

--# Mode
PassThru

--# Target Information
RmtHost 127.0.0.1, MGRPORT 9020

--# Trail
RmtTrail ./dirdat/ex

--# Report File
ReportCount Every 1 Records, Rate
ReportRollover at 00:01

--# Table List
Table app.*;



GGSCI (ogg-wkshp.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT01       00:00:00      00:00:02   


GGSCI (ogg-wkshp.us.oracle.com) 5> add extract pmp01, exttrailsource ./dirdat/ex
EXTRACT added.


GGSCI (ogg-wkshp.us.oracle.com) 6> add exttrail ./dirdat/ex, extract pmp01, megabytes 50
EXTTRAIL added.


GGSCI (ogg-wkshp.us.oracle.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT01       00:00:00      00:00:04   
EXTRACT     STOPPED     PMP01       00:00:00      00:02:04   


GGSCI (ogg-wkshp.us.oracle.com) 8> start pmp01

Sending START request to MANAGER ...
EXTRACT PMP01 starting


GGSCI (ogg-wkshp.us.oracle.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT01       00:00:00      00:00:06   
EXTRACT     RUNNING     PMP01       00:00:00      00:03:00



GGSCI (ogg-wkshp.us.oracle.com) 10> exit
[oracle@ogg-wkshp 10gogg]$ ps -ef | grep extract
oracle   12803 18067  0 19:19 ?        00:00:00 /u01/app/oracle/product/10gogg/extract PARAMFILE /u01/app/oracle/product/10gogg/dirprm/pmp01.prm REPORTFILE /u01/app/oracle/product/10gogg/dirrpt/PMP01.rpt PROCESSID PMP01 USESUBDIRS
oracle   15074     1  0 01:08 ?        00:03:10 /u01/app/oracle/product/10gogg/extract PARAMFILE /u01/app/oracle/product/10gogg/dirprm/peconf.prm REPORTFILE /u01/app/oracle/product/10gogg/dirrpt/PECONF.rpt PROCESSID PECONF USESUBDIRS
oracle   20660 25357  0 19:20 pts/2    00:00:00 grep extract
oracle   31909 18067  0 17:28 ?        00:00:14 /u01/app/oracle/product/10gogg/extract PARAMFILE /u01/app/oracle/product/10gogg/dirprm/ext01.prm REPORTFILE /u01/app/oracle/product/10gogg/dirrpt/EXT01.rpt PROCESSID EXT01 USESUBDIRS


  • Replicat 설정 및 기동

Replicat : 변경데이터를 Target DB 에 반영하는 process


[oracle@ogg-wkshp 11gR2ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

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



GGSCI (ogg-wkshp.us.oracle.com) 1> edit param rep01



GGSCI (ogg-wkshp.us.oracle.com) 2> view param rep01

--# Replicat Name
Replicat rep01

--# DB Information
USERID ogguser, Password ogguser

--# Management Discard file
DiscardFile ./dirout/rep01.dec, append, megabytes 50
DisCardRollover at 00:01

--# Report File(not using at Prd)
ReportCount Every 1 Records, Rate
ReportRollover at 00:01

--# Auto Mapping Column
AssumeTargetDefs

--# Table List
MAP app.simple_table, Target app.simple_table;
MAP app.simple_table2, Taret app.simple_table2;



GGSCI (ogg-wkshp.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          


GGSCI (ogg-wkshp.us.oracle.com) 4> add replicat rep01, exttrail ./dirdat/ex
REPLICAT added.


GGSCI (ogg-wkshp.us.oracle.com) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP01       00:00:00      00:00:03   


GGSCI (ogg-wkshp.us.oracle.com) 6> start rep01

Sending START request to MANAGER ...
REPLICAT REP01 starting


GGSCI (ogg-wkshp.us.oracle.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP01       00:00:00      00:00:16   


GGSCI (ogg-wkshp.us.oracle.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP01       00:00:00      00:00:20   


GGSCI (ogg-wkshp.us.oracle.com) 9> view report rep01

....

2014-07-07 19:29:54  ERROR   OGG-00664  OCI Error beginning session (status = 1017-ORA-010
17: invalid username/password; logon denied).


에러내용을 보고 rep01 parameter 를 수정한다

GGSCI (ogg-wkshp.us.oracle.com) 1> edit param rep01

--# DB Information
USERID ogguser, Password Oracle1


rep01 재기동

GGSCI (ogg-wkshp.us.oracle.com) 3> start rep01

Sending START request to MANAGER ...
REPLICAT REP01 starting


GGSCI (ogg-wkshp.us.oracle.com) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP01       00:00:00      00:00:05  


  • 테스트

gentrans.sql 을 이용하여 source DB 의 simple_table, simple_table2 에 각각 100건의 데이터를 insert 한다.


[oracle@ogg-wkshp dirsql]$ pwd
/u01/app/oracle/product/10gogg/dirsql


[oracle@ogg-wkshp dirsql]$ vi gentrans.sql


<gentrans.sql>

/*
        Simple data entry procedure
*/

SET VERIFY OFF;
SET DEFINE '&';

DECLARE
        v_MainLoop      NUMBER(6);

BEGIN
  FOR v_MainLoop IN 1..100 LOOP
   INSERT INTO simple_table VALUES('thisisa','temporary','tablevalue',simple_seq.NEXTVAL,SYSDATE);
   INSERT INTO simple_table2 VALUES('s_table2',simple_seq.NEXTVAL,SYSDATE);

   COMMIT;
  END LOOP;
END;
/
EXIT;


sequence 를 생성 한다.

[oracle@ogg-wkshp dirsql]$ sqlplus app/app

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 7 21:58:51 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create sequence simple_seq;

Sequence created.


[oracle@ogg-wkshp dirsql]$ sqlplus app/app @gentrans.sql

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 7 22:00:31 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


PL/SQL procedure successfully completed.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ogg-wkshp dirsql]$ sqlplus app/app

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 7 22:01:13 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from simple_table;

  COUNT(*)
----------
       100

SQL> select count(*) from simpletable2;
select count(*) from simpletable2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from simple_table2;

  COUNT(*)
----------
       100

SQL> exit


target DB를 조회해 보니 두개의 table이 0건이다.

로그를 확인해보니 source 측은 문제가 없었고, target rep01 에 에러가 있다

로그 확인 후 에러를 수정

GGSCI (ogg-wkshp.us.oracle.com) 5> view report rep01

GGSCI (ogg-wkshp.us.oracle.com) 6> edit param rep01

--> 오타를 수정한다. "Taret --> Target"
GGSCI (ogg-wkshp.us.oracle.com) 37> start rep01

Sending START request to MANAGER ...
REPLICAT REP01 starting


GGSCI (ogg-wkshp.us.oracle.com) 38> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP01       00:00:00      00:00:06   


GGSCI (ogg-wkshp.us.oracle.com) 39> view report rep01


rep01 의 로그를 보면 정상수행으로 보인다.

아래 명령으로 target table의 갯수를 확인한다.


[oracle@ogg-wkshp 11gR2ogg]$ sqlplus app/app

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 7 22:13:57 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select count(*) from simple_table;

  COUNT(*)
----------
       100

SQL> select count(*) from simple_table2;

  COUNT(*)
----------
       100


* reference by 조창윤 SC


반응형
저작자표시

'Oracle > OGG' 카테고리의 다른 글

Data miration using OGG  (0) 2014.07.21
OGG(Oracle Golden Gate) DDL Replication  (0) 2014.07.11

관련글 더보기

댓글 영역

티스토리툴바