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

Oracle DML/DDL synchronous data (OGG_12.2_for_Windows)

Oracle DML/DDL synchronization data (OGG_12.2_for_Windows) 1

The environment prepares and installs GoldenGate 3

1. Basic environment 3

2. Configure log mode 3

3. Create a GoldenGate user account 4

4. Install GoldenGate software 5

5. Simulate the production library and initialize the data 5

II. GoldenGate DML synchronization configuration 6

1. GoldenGate DML synchronization source configuration 6

1. Source MGR process 6

2. Configure Extract process group 7

3. Configure Pump process group 8

4. Initialization of destination side 9

2. GoldenGate DML synchronization destination configuration 9

1. Destination MGR process 10

2. Add checklist 10

3. Add checkpoint Table 11

4. Configure the target Peplicat process group 11

5. You can view the checkpoint status 12

6. Verify synchronization 12

Three: GoldenGate DDL synchronization configuration 12

1. Support DDL replication to run scripts 12

2. Modify the params file of the extract process 14

3. Modify the params file of the target replicat process 15

4. Test 16

5. Verify 16

IV. FAQs 16

The environment prepares and installs GoldenGate

1. Basic environment

1) Source end

IP address: 10.6.252.44

Database: Oracle 11.2.0.4

SID: cjc

Operating system version: Windows 7

Ogg version: Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

Ggs_Windows_x64_shiphome.zip

2) destination side

IP address: 10.11.82.56

Database: Oracle 11.2.0.4

SID: chenjch

Operating system version: Windows 7

Ogg version: Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

Ggs_Windows_x64_shiphome.zip

2. Configure the log mode

# Source server side

(1) configure archiving mode

Sqlplus / as sysdba

Shutdown immediate

Startup mount

Alter database archivelog

Alter database open

(2) configure log mode (open mandatory file and supplementary log mode)

Alter database add supplemental log data

Alter database force logging

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH

Alter system set recyclebin=off scope=spfile

(3) View the configuration results:

SQL > select log_mode,supplemental_log_data_min,force_logging from v$database

LOG_MODE SUPPLEME FOR

-

ARCHIVELOG YES YES

3. Create a GoldenGate user account

Su-oracle

Sqlplus / as sysdba

Create tablespace ogguser datafile'D:\ APP\ ADMINISTRATOR\ ORADATA\ CJC\ ogguser.dbf' size 10m autoextend on next 1m maxsize unlimited

Create tablespace ogguser datafile'D:\ APP\ ADMINISTRATOR\ ORADATA\ CHENJCH\ ogguser.dbf' size 10m autoextend on next 1m maxsize unlimited

Create user ogguser identified by ogguser

Default tablespace ogguser

Temporary tablespace temp

Grant connect,resource,dba to ogguser

4. Install GoldenGate software

Ogg12 version installation is a graphical interface, select the installation version (11)

D:\ ogg\ ggs_Windows_x64_shiphome\ Disk1\ setup.exe

After starting the graphical interface, select

OGG directory: d:\ app\ Administrator\ product\ 11.2.0\ oggcore_1-need to be created in advance

Oracle directory: d:\ app\ Administrator\ product\ 11.2.0\ dbhome_1

Just install it directly.

After installation, all directories will be included under D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1 without the need to execute the create subdirs command

5. Simulate production library and initialize data

Establish test users and initialize data

Create user test identified by test

Grant connect, resource, dba to test

Conn test/test

Create table test (id number, name varchar2 (20)

Create a table that contains CLOB fields and initialize the

Create table t_clob (tid number,c_clob clob)

II. GoldenGate DML synchronization configuration

1. GoldenGate DML synchronization source configuration

1) configure DML synchronization first

Cd D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1

Ggsci.exe

Dblogin userid ogguser password ogguser

1. Source MGR process

1) Edit the main process group

Edit params mgr

PORT 7809

Dynamicportlist 7800-8000

Autorestart extract *, retries 5, waitress 2, 5.

PURGEOLDEXTRACTS. / dirdat/*,usecheckpoints, minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

Description:

Port specifies the mgr process communication port

Dynamicportlist indicates that the mgr process can specify ports for dynamic communication between source and destination, and ogg12 has been abandoned.

Autorestart extract* means to automatically restart the extract process group, try to restart all processes every 2 minutes, retry 5 times, and clear zero every 5 minutes.

Keep the local queue for 7 days after the minkeepdays 7 queue delivery ends

LAGREPORTHOURS 1 / * check delay report every hour

LAGINFOMINUTES 30 / * checks for delays every 30 minutes. If the delay threshold is exceeded, an error log will be written.

LAGCRITICALMINUTES 45 / * delay threshold 45 minutes

2) start the main management process

Start mgr

2. Configure the Extract process group

1) Edit configuration file

Edit param ext1

Extract ext1

Setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Userid ogguser,password ogguser

Exttrail D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ yd

Table test.*

Description:

Extract ext1 defines extract to enter. First name

Dynamicresolution

Setenv sets environment variables

Userid log in to the database

Exttrail specifies the local trail file address

Table defines synchronized tables

The character set of the AMERICAN_AMERICA.AL32UTF8 experimental environment, which can be found by using SELECT userenv ('language') from dual;

Conversion of large TRANLOGOPTIONS CONVERTUCS2CLOBS clob Fields

GETTRUNCATES get Truncate operation

Tranlogoptions rawdeviceoffset 0 / * bare device file offset (AIX system-specific parameter), not used in this lab

2) add extraction process

Add extract ext1,tranlog,begin now

3) add a local trail file

Add exttrail D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ yd,extract ext1

Description:

Create a local trail file, the main extract process is responsible for writing this part of the file, and pump is responsible for transferring this part of the file to the target server.

4) start the service

Start extract ext1

3. Configure the Pump process group

1) Edit configuration file

Edit param extpump

Extract extpump

Dynamicresolution

Passthru

Rmthost 10.11.82.56,mgrport 7809,compress

Rmttrail D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ mb

Table test.*

2) add pump process

Add extract extpump,exttrailsource D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ yd

3) add remote trail files

Add rmttrail / ogg/app/oracle/dirdat/mb,extract extpump

Description: specify a remote trail file

4) start the pump process

Start extract extpump

At this point: the source configuration is complete. Check whether the relevant process is started normally.

Command:

Info all to view the status of all processes

View report ext1 can view the error reports of failed processes

4. Initialization of the destination side

Establish test users and initialize data

Create user test identified by test

Grant connect, resource, dba to test

Conn test/test

Create table test (id number, name varchar2 (20)

Create a table that contains CLOB fields and initialize the

Create table t_clob (tid number,c_clob clob)

2. GoldenGate DML synchronization destination configuration

Install ogg software, the installation directory is the same as the original server, and the installation process is ignored

Configure on the database, create users, authorize

Create user ogguser identified by ogguser

Default tablespace ogguser

Temporary tablespace temp

Grant connect,resource,dba to ogguser

1. Target-side MGR process

Cd D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1

Ggsci.exe

Dblogin userid ogguser password ogguser

1) Edit configuration file

Edit param mgr

Port 7809

Dynamicportlist 7800-8000

Autostart er *

Autorestart extract *, waitminutes 2, resetminutes 5

Lagreporthours 1

Laginfominutes 3

Lagcriticalminutes 5

Purgeoldextracts D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ rt*, usecheckpoints, minkeepdays 3

2) start

Start mgr

2. Add a checklist

Note: when we specify the default checkpoint in the GLOBALS file, the new Replicat groups will automatically use this parameter when it is created, and no other instructions are required.

1) Edit the global configuration file

GGSCI (dg2) 4 > edit param. / GLOBALS

CHECKPOINTTABLE ogguser.checkpoint

2) exit # you need to exit the ggsci terminal here

3. Add checkpoint table

. / ggsci

GGSCI (dg2) 1 > dblogin userid ogguser password ogguser

GGSCI (dg2 as ogguser@mbdb) 2 > add checkpointtable ogguser.checkpoint

4. Configure the target Peplicat process group

1) Edit configuration file

Edit params repl

Replicat repl

Userid ogguser,password ogguser

Reperror default,discard

Discardfile D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirrpt\ repl.dsc,append,megabytes 4096-too small megabytes may cause repl to fail to start

Map test.*, target test.*

2) add replication process

Add replicat repl,exttrail D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirdat\ mb, CHECKPOINTTABLE ogguser.checkpoint

3) start the process

Start repl-aftercsn

5. You can view the checkpoint status

Su-oracle

Sqlplus ogguser/ogguser

SQL > select tname from tab

TNAME

-

CHECKPOINT TABLE

CHECKPOINT_LOX TABLE

Select * from checkpoint

Note 1: ogg_11.1.1.1.2 has only one table for CHECKPOINT.

Note 2: the version after ogg_11.2.x.x.1 has two tables of CHECKPOINT,CHECKPOINT_LOX.

Check to see if the relevant process starts normally

Info all

6. Verify synchronization

The source side performs insert,update,delete operations on all tables under the test user to ensure that the target side can normally synchronize the data generated by all DML operations.

Three: GoldenGate DDL synchronization configuration

1. Support DDL replication to run scripts

# Server source configuration

1) specify the database schema

D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ ggsci.exe

GGSCI (dg1) 1 > edit param. / GLOBALS

Ggschema ogguser

GGSCI (dg1) 3 > stop mgr

2) install DDL object

Go to the installation directory of ogg and this lab is D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1

Sqlplus / as sysdba

SQL > grant dba to ogguser

SQL > @ marker_setup description: all specify user ogguser

SQL > @ ddl_setup description: 11.1.1.2 requires manual input of ogguser,INITIALSETUP,yes

SQL > @ role_setup

SQL > grant GGS_GGSUSER_ROLE to ogguser

SQL > @ ddl_enable

SQL > @ marker_status.sql

Note 1: executing the dbmspool package creates the DBMS_SHARED_POOL package in the database, after which the ddl_pin package needs to be used

SQL > @? / rdbms/admin/dbmspool.sql

Note 2: execute ddl_pin.sql to keep DDLReplication-related objects in a shared pool through dbms_shared_pool.keep stored procedures to ensure that these objects are not RELOAD and improve performance.

SQL > @ ddl_pin.sql ogguser

2. Modify the params file of the extract process

# Server source configuration

# the source side modifies the params file of the extract process, adds the "ddl include all" parameter, and restarts the extract process

1) stop the ext1 process

GGSCI (test) 2 > stop extract ext1

2) Edit configuration file

GGSCI (test) 3 > edit params ext1

Extract ext1

Dynamicresolution

Setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Userid ogguser,password ogguser

Exttrail / ogg/app/oracle/dirdat/yd

Report at 1:00

Reportrollover at 1:00

Ddl include all

Ddloptions addtrandata,report

GETTRUNCATES

TRANLOGOPTIONS CONVERTUCS2CLOBS

Table test.*

# description: add two lines tranlogoption and ddl

3) start the mgr,eora process

GGSCI (test) 4 > start mgr

GGSCI (test) 5 > start extract ext1

4) check the startup of the process

GGSCI (test) 6 > info extract ext1

3. Modify the params file of the target replicat process

# Target server configuration

# modify the params file of the replicat process on target

Add parameters such as "ddlerror default ignore retryop maxretries 3 retrydelay 5" to restart the replicat process, as follows

1) stop the mgr process

GGSCI (slave) 1 > stop mgr

2) stop the repl process

GGSCI (slave) 1 > stop replicat repl

3) Edit repl configuration file

GGSCI (slave) 1 > edit params repl

GGSCI (slave) 2 > view params repl

Replicat repl

Userid ogguser,password ogguser

Reperror default,discard

Discardfile D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirrpt\ repl.dsc,append,megabytes 4096

Ddloptions report

Ddlerror default ignore retryop maxretries 3 retrydelay 5

Ddlerror default discard

Ddlerror default ignore retryop

Map test.*, target test.*

4) start the rora_t1 process

GGSCI (slave) 4 > start mgr # automatically starts the repl process

GGSCI (slave) 5 > info replicat repl

4. Test

Create a table on the source side of the test user to see if the destination side has been created successfully.

Create table T1 (id number)

5. Verification

Check whether the T1 table and data are synchronized on the target side.

IV. Frequently asked questions

Question 1:

Insert and delete can be synchronized, update cannot be synchronized

Solution:

Ggsci > dblogin userid gg, password gg

Ggsci > add trandata test.*

Question 2:

The repl process failed to start

View the log

D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ ggserr.log

D:\ app\ Administrator\ product\ 11.2.0\ oggcore_1\ dirrpt\ repl.dsc

The megabytes value is too small, which may cause the rep1 process ABENDED

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.