Shulou(Shulou.com)06/01 Report--

Oracle GoldenGate Learning-- basic Concepts and configurations (3)

System architecture:

OGG basic configuration (unidirectional transmission)

1. Database configuration (Source DB)

Set up Tablespace:11:58:56 SYS@ prod > create tablespace ogg_tbs11:59:12 2 datafile'/ u01 SYS@ prod ogg users: 12:01:17 SYS@ prod > create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;User created. User authorization: 12:01:39 SYS@ prod > grant connect,resource to ogg;Grant succeeded.12:01:55 SYS@ prod > grant create session,alter session to ogg;Grant succeeded.12:02:18 SYS@ prod > grant select any dictionary,select any table to ogg;Grant succeeded.12:02:42 SYS@ prod > grant alter any table to ogg;Grant succeeded.12:02:53 SYS@ prod > grant flashback any table to ogg;Grant succeeded.12:03:03 SYS@ prod > grant execute on dbms_flashback to ogg;Grant succeeded. Enable additional logging functions on Database: 12:19:26 SYS@ prod > select SUPPLEMENTAL_LOG_DATA_MIN from vested database candidate support no 12 SYS@ prod 2053 SYS@ prod > alter database add supplemental log data;Database altered. The database should be in archival mode: 12:21:10 SYS@ prod > archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination / dsk4/arch_prodOldest online log sequence 78Next log sequence to archive 80Current log sequence 8013 22 dsk4/arch_prodOldest online log sequence 78Next log sequence to archive 80Current log sequence 22 SYS@ prod > select force_logging from vault database 22 SYS@ prod > alter database force logging;Database altered.13:24:30 SYS@ prod > select force_logging from v$database FOR---YES establishes Table:12:12:26 SCOTT@ prod > create table emp_ogg as select * from emp;Table created.12:12:40 SCOTT@ prod > alter table emp_ogg add constraint pk_ogg primary key (empno) for transfer testing. Note: Oracle recommends that you establish a primary or unique key for all tables to be replicated

2. Database configuration (Target DB)

Set up Tablespace:12:07:50 SYS@ prod > create tablespace ogg_tbs12:08:09 2 datafile'/ u01 SYS@ prod ogg users: 12:08:47 SYS@ prod > create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;User created. User authorization: 12:09:44 SYS@ prod > grant connect,resource to ogg;Grant succeeded.12:10:06 SYS@ prod > grant create session, alter session to ogg;Grant succeeded.12:10:20 SYS@ prod > grant select any table,select any dictionary to ogg;Grant succeeded.12:10:45 SYS@ prod > grant create table to ogg; create test Table:12:13:47 SCOTT@ prod > create table emp_ogg as select * from emp;Table created.12:14:04 SCOTT@ prod > grant all on emp_ogg to ogg;Grant succeeded.

3. OGG configuration (source)

Configure MGR: [oracle@ogg ogg] $. / ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Add tableGGSCI (ogg) 2 > dblogin userid ogg,password ogg;Successfully logged into database.GGSCI (ogg) 3 > add trandata scott.emp_ogg;ERROR for capture data change: No viable tables matched specification.goldengate add trandata prompt cannot find table ERROR: No viable tables matched specification goldengate is sensitive to symbols, do not use semicolon GGSCI (ogg) 4 > add trandata scott.emp_ogg2014-09-10 14:00:25 WARNING OGG-00869 No unique key is defined for table 'EMP_OGG'. when add trandata 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.EMP_OGG. Note: it is suggested that table:emp_ogg does not establish primary key. After establishing primary key, re-addGGSCI (ogg) 8 > delete trandata scott.emp_oggLogging of supplemental redo log data disabled for table SCOTT.EMP_OGG.GGSCI (ogg) 9 > add trandata scott.emp_oggLogging of supplemental redo data enabled for table SCOTT.EMP_OGG. Check to see if supplemental logGGSCI (ogg) 11 > info trandata scott.emp_oggLogging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.GGSCI (ogg) 13 > edit params mgrport 7809dynamicportlist 7800-8000autorestart extract * is enabled on table. Edit the mgr parameter file and write the above information! GGSCI (ogg) 14 > start mgrManager started.GGSCI (ogg) 16 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING

Configure Extract: add the extract service and start the extract service: GGSCI (ogg) 11 > add extract eini_1,sourceistableEXTRACT added. Check Extract processGGSCI (ogg) 14 > info extract *, tasksEXTRACT EINI_1 Initialized 2014-09-10 14:28 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLE edit extract parameter file GGSCI (ogg) 18 > EDIT PARAMS EINI_1EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg, PASSWORD oggRMTHOST 192.168.249 MGRPort 7809RMTTASK REPLICAT, GROUP RINI_1TABLE scott.EMP_OGG GGSCI (ogg) 15 > start eini_1Sending START request to MANAGER... EXTRACT EINI_1 startingGGSCI (ogg) 16 > info eini_1EXTRACT EINI_1 Initialized 2014-09-10 14:28 Status RUNNINGCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLEGGSCI (ogg) 17 > view report eini_12014-09-10 14:30:34 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 X86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:20:38Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2014-09-10 14:30:34****Operating System Version:LinuxVersion # 1 SMP Wed Sep 1 01:26:34 EDT 2010 Release 2.6.32-71.el6.i686Node: oggMachine: i686 soft limit hard limitAddress Space Size: unlimited unlimitedHeap Size: unlimited unlimitedFile Size: unlimited unlimitedCPU Time: unlimited unlimitedProcess id: 6159Descriptionvirtual * * * Running with the following parameters * * * 2014-09-10 14:30:34 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD * RMTHOST 192.168.249 MGRPORT 7809RMTTASK REPLICAT,GROUP RINI_1TABLE scott.EMP_OGG Using the following key columns for source table SCOTT.EMP_OGG: EMPNO.2014-09-10 14:30:36 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: / u01/ogg/dirtmp.CACHEMGR virtual memory values (may have been adjusted) CACHESIZE: 2GCACHEPAGEOUTSIZE (normal): 4MPROCESS VM AVAIL FROM OS (min): 2.75GCACHESIZEMAX (strict force to disk): 2.47GDatabase Version:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-ProductionPL/SQL Release 11.2.0.1.0-ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0-ProductionNLSRTL Version 11.2.0.1.0-ProductionDatabase Language and Character Set:NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK "NLS_LANGUAGE =" AMERICAN "NLS_TERRITORY =" AMERICA "NLS_CHARACTERSET =" ZHS16GBK "2014-09-10 14:30:36 WARNING OGG-01223 TCP/IP error 111( Connection refusSource Context: SourceModule: [er.idlx] SourceID: [/ net/ap1109nap-vlan900/vol/ifarm_ports/ifarm_views/aime_oggcore_210874/oggcore/OpenSys/src/app/er/idlx.c] SourceFunction : [idlx_tcp_error] SourceLine: [1414] ThreadBacktrace: [10] elements: [/ u01/ogg/libgglog.so (CMessageContext::AddThreadContext () + 0x26) [0x1f9166]]: [/ u01/ogg/libgglog.so (CMessageFactory::CreateMessage (CSourceContext*) Unsigned int,...) + 0x2c5) [0x1f5125]]: [/ u01/ogg/libgglog.so (_ MSG_ERR_TCP_GENERIC (CSourceContext*, char const*) CMessageFactory::MessageDisposition) + 0x36) [0x1d755c]]: [/ u01/ogg/extract () [0x8148d0c]]: [/ u01/ogg/extract (IDLX_tcp_send_data+0xf4) [0x8149ae0]]: [/ u01/ogg/extract (IDLX_send_session_begin_cmd+0xa6) [0x8149e12]] : [/ u01/ogg/extract (process_source_files () + 0x584) [0x819662e]]: [/ u01/ogg/extract (main+0x5fc) [0x819820c]]: [/ lib/libc.so.6 (_ _ libc_start_main+0xe6) [0x8aecc6]]: [/ u01/ogg / extract (_ gxx_personality_v0+0x3ad) [0x81144e1]] 2014-09-10 14:32:35 ERROR OGG-01224 TCP/IP error 111 (Connection refused) Retries exceeded.2014-09-10 14:32:35 ERROR OGG-01668 PROCESS ABENDING.

Note: the above tcp/ip error occurs because the destination side is not configured with mgr and cannot communicate!

Configure the extract process on the source side for data synchronization: GGSCI (ogg) 25 > edit params eora_1EXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD ogg EXTTRAIL./dirdat/aaTABLE scott.emp_ogg; add extract primary groupGGSCI (ogg) 27 > add extract eora_1,tranlog,begin nowEXTRACT added. Note the definition of goldengate local trail: 'aa' is prefix for local trail fileGGSCI (ogg) 29 > add exttrail. / dirdat/aa,extract eora_1 Megabytes 5EXTTRAIL added.GGSCI (ogg) 30 > start extract eora_1Sending START request to MANAGER... EXTRACT EORA_1 startingGGSCI (ogg) 31 > info extract eora_1EXTRACT EORA_1 Initialized 2014-09-10 14:47 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:02:06 ago) Log Read Checkpoint Oracle Redo Logs 2014-09-10 14:47:22 Seqno 0 RBA 0 SCN 0.0 (0) configure the pump process on the source side For data synchronization: GGSCI (ogg) 41 > edit params pora_1EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) PASSTHRURMTHOST 192.168.249 Magna MGRPORT 7809RMTTRAIL./dirdat/paTABLE scott.emp_ogg Add pump extract groupGGSCI (ogg) 35 > add extract pora_1,exttrailsource. / dirdat/paEXTRACT added. Add remote trail file to source systemggsci > ADD RMTTRAIL. / dirdat/pa,EXTRACT PORA_1 MEGABYTES 5RMTTRAIL added.GGSCI (ogg) 36 > info extract pora_1EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:22 ago) Log Read Checkpoint File. / dirdat/pa000000 First Record RBA 0 GGSCI (ogg) 42 > start extract pora_1Sending START request to MANAGER. EXTRACT PORA_1 startingGGSCI (ogg) 43 > info extract pora_1EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:03:35 ago) Log Read Checkpoint File. / dirdat/pa000000 First Record RBA 0 and above eora_1 and pora_1 services are in stop state Normal should be running, which should be corrected in the following ways Solution: GGSCI (rh7.cuug.net) 7 > delete extract eora_1,tranlog,begin now2014-09-10 16:16:15 WARNING OGG-01753 Cannot unregister EXTRACT EORA_1 from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT command with LOGRETENTION. Issue DBLOGIN first.Deleted EXTRACT EORA_1.GGSCI (rh7.cuug.net) 8 > dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (rh7.cuug.net) 9 > delete extract eora_1,tranlog,begin nowERROR: EXTRACT EORA_1 does not exist.GGSCI (rh7.cuug.net) 10 > add extract eora_1,tranlog,begin nowEXTRACT added.GGSCI (rh7.cuug.net) 11 > add exttrail. / dirdat/aa, extract eora_1 Megabytes 5EXTTRAIL added.GGSCI (rh7.cuug.net) 12 > start extract eora_1Sending START request to MANAGER... EXTRACT EORA_1 startingGGSCI (rh7.cuug.net) 13 > info extract eora_1EXTRACT EORA_1 Last Started 2014-09-10 16:17 Status RUNNINGCheckpoint Lag 00:00:54 (updated 00:00:07 ago) Log Read Checkpoint Oracle Redo Logs 2014-09-10 16:16:51 Seqno 80 RBA 42201104 SCN 0.0 (0) GGSCI (rh7.cuug.net) 27 > edit params pora_1RMTTRAILEXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) PASSTHRURMTHOST 192.168.249 MGRPORT 7809RMTTRAIL. / dirdat/paTABLE scott.emp_ogg ~ GGSCI (rh7.cuug.net) 28 > start extract pora_1Sending START request to MANAGER... EXTRACT PORA_1 startingGGSCI (rh7.cuug.net) 29 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EORA_1 00:00:00 00:00:04EXTRACT RUNNING PORA_1 00:00:00 00:03:18

@ at this point, the source end, Extract, Pump service configuration is basically complete!

4. OGG configuration (Target)

Configure MGRGGSCI (rh7.cuug.net) 4 > edit params mgrport 7809dynamicportlist 7800-8000autorestart extract *, waitminutes 2 5~GGSCI (rh7.cuug.net) 5 > start mgrManager started.GGSCI (rh7.cuug.net) 6 > info mgrManager is running (IP port rh7.cuug.net.7809). Configure Replicate service [oracle@rh7 ogg] $. / ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (rh7.cuug.net) 1 > add replicat rini_1,specialrunREPLICAT added.GGSCI (rh7.cuug.net) 2 > info replicat *, tasksREPLICAT RINI_1 Initialized 2014-09-10 14:33 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:13 ago) Log Read Checkpoint Not AvailableTask SPECIALRUNGGSCI (rh7.cuug.net) 3 > edit params rini_1REPLICAT RINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) ASSUMETARGETDEFSUSERID ogg,PASSWOR oggDISCARDFILE. / dirrpt/RINIaa.dsc,PURGEMAP scott.emp_ogg TARGET scott.emp_ogg Note: if you start eini_1 process on the source side, the destination rini_1 process will be automatically started to add replicate checkpoint tableGGSCI (rh7.cuug.net) 1 > edit params. / GLOBALSCHECKPOINTTABLE ogg.oggchkpt~GGSCI (rh7.cuug.net) 2 > exit must exit ggsci Checkpoint table will take effect [oracle@rh7 ogg] $. / ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (rh7.cuug.net) 1 > dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (rh7.cuug.net) 2 > add checkpointtableNo checkpointtable specified, using GLOBALS specification (ogg.oggchkpt)... Successfully created checkpointtable ogg.oggchkpt. Add replicate groupGGSCI (rh7.cuug.net) 4 > add replicat rora_1,exttrail. / dirdat/paREPLICAT added. Edit replicate parameterfileGGSCI (rh7.cuug.net) 5 > edit param rora_1REPLICAT RORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE. / dirrpt/RORA_aa.DSC,PURGEMAP scott.oem_ogg,TARGET scott.emp_ogg Launch replicate processGGSCI (rh7.cuug.net) 7 > start replicat rora_1Sending START request to MANAGER... REPLICAT RORA_1 startingGGSCI (rh7.cuug.net) 10 > info replicat rora_1REPLICAT RORA_1 Last Started 2014-09-10 15:53 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:05 ago) Log Read Checkpoint File. / dirdat/pa000000 First Record RBA 0 GGSCI (rh7.cuug.net) 9 > Info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING RORA_1 00:00:00 00:00:005 、 Synchronous test

Source:16:27:33 SCOTT@ prod > select * from emp_ogg EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--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 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.16:27:41 SCOTT@ prod > update emp_ogg set deptno=40 where empno=7788 1 row updated.16:28:06 SCOTT@ prod > commit;Commit complete.target:16:26:31 SCOTT@ prod > truncate table emp_ogg;Table truncated.16:26:44 SCOTT@ prod > select * from emp_ogg No rows selected16:28:23 SCOTT@ prod > / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7369 SMITH CLERK 7902 17-DEC-80 7902 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 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 7788 SCOTT ANALYST 7566 19-APR-87 3000 40 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03 Mudec- 81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.@ The data synchronization on the same source side of the destination side is successful, and the one-way transmission of OGG is basically configured.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.