Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to achieve OGG single-to-single deployment, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Environment introduction:

Project source environment target environment

Operating system: Oracle Linux Server release 6.5

Hostname: ogg1,ogg2

IP address: ogg1:192.168.1.11,ogg2:192.168.1.12

Database and version: 11.2.0.4

Database character set: AL32UTF8

SID:ogg1:mtx,ogg2:ymx

OGG user: ogg1:ogg,ogg2:ogg

OGG version: 11.2.1.0.1

Archive mode: archive log

Deployment steps:

1. Create OGG operating system users (optional, Oracle users can also be used)

Useradd-u 2100-g oinstall-G dba ogg

Passwd ogg2008

two。 Modify the environment variable of the operating system user, because the environment does not create the operating system user for OGG, directly use the Oracle user, so the environment variable is the environment variable of the Oracle user

Export PATH

Export TMP=/tmp

Export TMPDIR=$TMP

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=$ORACLE_BASE/product/11.2/db

Export ORACLE_SID=mtx

Export ORACLE_TERM=xterm

Export PATH=/usr/sbin:$PATH

Export PATH=$ORACLE_HOME/bin:$PATH

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64

Export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Export NLS_LANG= "AMERICAN_AMERICA.AL32UTF8"

Note: if you are a newly created operating system user, overload it after configuring the environment variable: source .bash _ profile

3. Two servers create OGG installation paths and change permissions

Mkdir / u01/app/ogg-pv

Chown oracle.oinstall / u01/app/ogg-R

4. Upload the OGG installation file to the OGG path of the two servers, and my path is "/ u01/app/ogg"

The download URL of OGG software can be found on the official website of Oracle, and https://edelivery.oracle.com/osdc/faces/SearchSoftware can find the corresponding version.

Decompress the uploaded file "V34339-01.zip", and you will see that many other files will be generated under the path, including documents, instructions, etc.

There is a tar package "fbo_ggs_Linux_x64_ora11g_64bit.tar", and we need to extract the tar file.

[oracle@ogg1 ogg] $unzip V34339-01.zip (both servers)

Archive: V34339-01.zip

Inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar

Inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc

Inflating: Oracle GoldenGate_11.2.1.0.3_README.txt

Inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf

[oracle@ogg1 ogg] $tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar (both servers)

5. After the decompression is successful, log in to the OGG test:

[oracle@ogg1 ogg] $. / 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.

GGSCI (ogg1) 1 >

Note: if the LD_LIBRARY_PATH variable is set correctly, it will enter the command line correctly as shown above. If the prompt cannot find the so file, you need to check whether the LD_LIBRARY_PATH variable is configured correctly.

6. Create an administrative directory for OGG (do it at both ends)

GGSCI (ogg1) 1 > create subdirs

Creating subdirectories under current directory / u01/app/ogg

Parameter files / u01/app/ogg/dirprm: already exists-- stores the configuration information of OGG parameters

Report files / u01/app/ogg/dirrpt: created-- stores process report files

Checkpoint files / u01/app/ogg/dirchk: created-- stores checkpoint files

Process status files / u01/app/ogg/dirpcs: created-- stores process status files

SQL script files / u01/app/ogg/dirsql: created-- stores sql script files

Database definitions files / u01/app/ogg/dirdef: created-- stores the data definition files generated by the defgen tool

Extract data files / u01/app/ogg/dirdat: created-- stores Trail files, that is, files that capture processes

Temporary files / u01/app/ogg/dirtmp: created-- is stored in this directory by default when the memory required by a transaction exceeds the allocated memory

Stdout files / u01/app/ogg/dirout: created

7. The source side creates OGG tablespaces and OGG users

Oracle recommends using a separate table space to store OGG data. The size of the table space can be defined by yourself, or you can set whether the data file is automatically extended. It is recommended to expand automatically.

SYS@mtx > create tablespace ogg datafile'/ u01qqappActionoradataUnimax MtxUnix ogg01.dbf'

2 size 1024m

3 autoextend on

SYS@mtx > create user ogg identified by ogg2008 default tablespace ogg

8. Give ogg users corresponding permissions, and some people directly give OGG users DBA permissions to save trouble.

SYS@mtx > grant CONNECT, RESOURCE to ogg

SYS@mtx > grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg

SYS@mtx > grant ALTER ANY TABLE to ogg

SYS@mtx > grant FLASHBACK ANY TABLE to ogg

SYS@mtx > grant EXECUTE on DBMS_FLASHBACK to ogg

SYS@mtx > grant insert any table to ogg

SYS@mtx > grant update any table to ogg

SYS@mtx > grant delete any table to ogg

Or

SYS@mtx > grant resource,connect,dba to ogg

9. Set up a test table

The source side establishes the corresponding test table based on the EMP and DEPT tables of scott users, including the data in it.

SCOTT@mtx > create table emp_ogg as select * from emp

Table created.

SCOTT@mtx > create table dept_ogg as select * from dept

Table created.

The target end establishes the corresponding test table based on the EMP and DEPT tables of scott users, and does not contain data.

SCOTT@ymx > create table emp_ogg as select * from emp where 1: 2

Table created.

SCOTT@ymx > create table dept_ogg as select * from dept where 1: 2

Table created.

The source side and the target side establish primary key or unique key constraints on two test tables, respectively.

SCOTT@mtx > alter table EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO)

Table altered.

SCOTT@mtx > alter table DEPT_OGG add constraint PK_DEPTNO_OGG primary key (DEPTNO)

Table altered.

10. Add a minimum supplementary log and a table-level supplementary log on the source side

Open the minimum supplementary log:

SYS@mtx > SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE

SUPPLEME

-

NO

SYS@mtx > alter database add supplemental log data

Database altered.

Open the table-level supplementary log (the minimum supplementary log is required)

GGSCI (ogg1) 1 > dblogin userid ogg,password ogg2008

Successfully logged into database.

GGSCI (ogg1) 2 > add trandata scott.emp_ogg

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

GGSCI (ogg1) 3 > add trandata scott.dept_ogg

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

SYS@mtx > alter table scott.emp_ogg logging

Table altered.

SYS@mtx > alter table scott.dept_ogg logging

Table altered.

11. Configure the MGR management process

Source side and destination side:

GGSCI (ogg1) 1 > edit param mgr

PORT 7809muri-Service listening port, default is 7809

DYNAMICPORTLIST 7840-7939mi-dynamic port, when the designated port is not available, select one from the list as the available port, you can set a maximum of 256

AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3muri-automatic restart parameter settings. The setting here means that all EXTRACT processes are attempted to restart every 3 minutes for a total of 5 times.

PURGEOLDEXTRACTS. / dirdat/*, USECHECKPOINTS, minkeepdays 7muri-regularly clean up trail files. This setting means deleting trail files for more than 7 days, and the location of trail files can also be seen from this parameter.

LAGREPORTHOURS 1Mel-defines the delay warning mechanism, which is set to check the delay of extract every 1 hour.

LAGINFOMINUTES 30Mel-defines the delay warning mechanism. New delay information will be recorded in the error log after more than 30 minutes.

LAGCRITICALMINUTES 45Mel-defines the delay warning mechanism. If the delay is set to exceed 45 minutes, it will be written to the error log as a warning.

Start the MGR management process

GGSCI (ogg1) 2 > start mgr

twelve。 Configure the extraction process

Add a capture process:

GGSCI (ogg1) 4 > add extract ext1,tranlog,begin now

EXTRACT added.

Specify the path where the trail file generated by the capture process is located

GGSCI (ogg1) 7 > add exttrail / u01/app/ogg/dirdat/ea, extract ext1, megabytes 100

EXTTRAIL added.

Configure the parameter file for the process

GGSCI (ogg1) 6 > edit param ext1

Extrail ext1

SETENV (ORACLE_SID= "mtx")

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg2008 configuration

EXTTRAIL / u01/app/ogg/dirdat/ea

NUMFILES 3000

GETTRUNCATES

DYNAMICRESOLUTION

Table scott.dept_ogg

Table scott.emp_ogg

Start the capture process

GGSCI (ogg1) 31 > start ext1

Sending START request to MANAGER...

EXTRACT EXT1 starting

13. Configure the transport process

Add a transport process:

GGSCI (ogg1) 16 > add extract pum1,exttrailsource / u01/app/ogg/dirdat/ea

EXTRACT added.

Specify the trail file path of the transfer process

GGSCI (ogg1) 18 > add rmttrail / u01 pum1,megabytes extract pm extract

RMTTRAIL added.

Configure the parameter file for the process

Extract pum1

SETENV (ORACLE_SID= "mtx")

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

RMTHOST 192.168.1.12,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000

CACHEMGR CACHEBUFFERSIZE 64KB, CACHESIZE 1024MB, CACHEDIRECTORY / u01/app/ogg/dirtmp

Rmttrail / u01/app/ogg/dirdat/pm

PASSTHRU

DYNAMICRESOLUTION

TABLE scott.*

Start the transfer process

GGSCI (ogg1) 32 > start pum1

Sending START request to MANAGER...

EXTRACT PUM1 starting

14. Do the initialization operation

Query the system change number of the current database

SYS@mtx > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), dbms_flashback.get_system_change_number | |''from dual

15158302130192

SYSDATE DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER |

13-JUL-17 286349

According to the SCN number to export the data of the two tables, using the data pump, the directory object needs to be created by itself.

Expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG flashback_scn=286349

Expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG flashback_scn=286349

Transfer the exported data file to the target side by SCP or FTP for import operation

Impdp ogg/ogg2008 directory=pump dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG table_exists_action=append

Impdp ogg/ogg2008 directory=pump dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG table_exists_action=append

-15. The destination side configures the checkpoint of OGG

This step is not necessary, but in order to allow OGG network outages, server outages, power outages, etc., to correctly resume transmission at breakpoints, Oracle recommends configuring OGG checkpoint queues.

-- GGSCI (ogg2) 75 > edit param. / GLOBALS

-- checkpointtable ogg.checkpoint_tab

-- GGSCI (ogg2) 7 > add checkpointtable ogg.checkpoint_tab

-- Successfully created checkpoint table ogg.checkpoint_tab.

16. The target side configures the replycat process

GGSCI (ogg2) 8 > add replicat rep1 exttrail / u01/app/ogg/dirdat/pm, checkpointtable ogg.checkpoint_tab

REPLICAT added.

GGSCI (ogg2) 12 > edit param rep1

Replicat rep1

SETENV (ORACLE_SID= "ymx")

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg2008

Sqlexec "Alter session set commit_write=nowait"

DISCARDFILE / u01/app/ogg/dirrpt/rep1err.dsc,append,megabytes 100

ASSUMETARGETDEFS

ALLOWNOOPUPDATES

DYNAMICRESOLUTION

NUMFILES 3000

EOFDELAYCSECS 30

GETTRUNCATES

BATCHSQL BATCHESPERQUEUE 200,OPSPERBATCH 2000

MAP SCOTT.DEPT_OGG, TARGET SCOTT.DEPT_OGG*

MAP SCOTT.EMP_OGG, TARGET SCOTT.EMP_OGG*

Start the replycat process

GGSCI (ogg2) 18 > start replicat rep1,aftercsn 286349

17. Test effect

Source side:

SCOTT@mtx > select * from dept_ogg

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SCOTT@mtx > insert into dept_ogg values (50 hundred percent)

1 row created.

SCOTT@mtx > commit

Commit complete.

The result captured by the source side:

GGSCI (ogg1) 71 > stats ext1

Sending STATS request to EXTRACT EXT1...

Start of Statistics at 2017-07-13 14:39:28.

Output to / u01/app/ogg/dirdat/ea:

Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

* Total statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Daily statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Hourly statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Latest statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (ogg1) 72 > stats pum1

Sending STATS request to EXTRACT PUM1...

Start of Statistics at 2017-07-13 14:39:33.

Output to / u01/app/ogg/dirdat/pm:

Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

* Total statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Daily statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Hourly statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Latest statistics since 2017-07-13 14:37:03 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

Destination side:

GGSCI (ogg2) 38 > stats rep1

Sending STATS request to REPLICAT REP1...

Start of Statistics at 2017-07-13 18:11:18.

Replicating from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

* Total statistics since 2017-07-13 18:11:11 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Daily statistics since 2017-07-13 18:11:11 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Hourly statistics since 2017-07-13 18:11:11 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

* Latest statistics since 2017-07-13 18:11:11 * *

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

The result of the target side applying the data to the database:

SCOTT@ymx > select * from dept_ogg

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 TEC BEIJING

Thank you for reading this article carefully. I hope the article "how to achieve OGG single-to-single deployment" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.