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

Oracle GoldenGate realizes one-way synchronization between Windows and Windows

This experiment is carried out in the win environment to achieve one-way synchronization between Windows and Windows through GoldenGate to replicate and backup the source database.

Description of the experimental environment

Operating system: Windows 2008 R2

Database: oracle 11.2.0.1.0

GoldenGate:11.2.1.0.3 for Microsoft Windows x64

Download address: http://down.51cto.com/data/2448081

First, check the environment before installation (the source side is the same as the target side):

1. Check the database configuration

Check whether the archive mode, additional logs, and mandatory log parameters are enabled.

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

Archive mode, attach logs, and force log parameters are turned off by default.

Start archive mode, attach logs, force logs. Enabling archiving mode requires restarting the database, while enabling additional logs and forcing logs does not require restarting the database.

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database force logging

SQL > alter database add supplemental log data

SQL > alter database open

Second, install GoldenGate

1 first copy and extract Oracle GoldenGate 11.2.1.0.3 for Microsoft Windows x64 (64-bit) to the D:\ app\ Administrator\ directory (this path can be selected as needed), and change the directory name to ogg after decompression.

2 enter the D:\ app\ Administrator\ ogg\ directory, run ggsci, and enter the ogg management console.

3 enter the command: create subdirs in the ogg console, and let ogg create the directory it needs

4 copy the category.dll and ggsmsg.dll files in the D:\ app\ Administrator\ ogg directory to the SYSTEM32 directory.

5 manually create the discard folder under the ogg directory: md discard

6 to create an GoldenGate exclusive user ogg in Oracle, you must first create an independent tablespace for the ogg user, otherwise the later installation will report an error

D:\ app\ Administrator\ ogg > sqlplus / as sysdba

SQL > create tablespace ogg_tbs datafile'd:\ app\ administrator\ oradata\ hwprod\ ogg_tbs01.dbf' size 1024m

SQL > alter tablespace ogg_tbs add datafile'd:\ app\ administrator\ oradata\ hwprod\ ogg_tbs02.dbf' size 1024m

SQL > alter tablespace ogg_tbs add datafile'd:\ app\ administrator\ oradata\ hwprod\ ogg_tbs03.dbf' size 1024m

SQL > create user ogg identified by oracle default tablespace ogg_tbs quota unlimited on ogg_tbs

SQL > grant dba to ogg; (when it comes to DDL replication, you need to grant dba permission)

SQL > grant execute on utl_file to ogg

7 execute the configuration script for the newly created ogg user. Use sys to log in to sqlplus from the ogg installation directory, and then execute markker_setup.sql,ddl_setup.sql,role_setup.sql,ddl_enable.sql.

(1) marker_setup.sql, which is used to install the GoldenGate marker system required for DDL support.

SQL > @ marker_setup

(2) ddl_setup.sql

SQL > @ ddl_setup

(3) role_setup.sql, which removes and creates roles needed for DDL synchronization, and authorizes DML operations on DDL objects.

SQL > @ role_setup

SQL > grant GGS_GGSUSER_ROLE to ogg

(4) ddl_enable.sql, which enables ddl triggers.

SQL > @ ddl_enable

8 enter the ogg console and test whether the user has created it successfully.

GGSCI (HWORATEST) 1 > dblogin userid ogg

The test is successful and the installation is complete!

3. Configure GoldenGate

Source end

1. Configure Manager management process parameters:

GGSCI (HWORATEST) 1 > edit params mgr

Click "Yes"

Then type the following and save

Port 7809

2. Configure checkpoint table

GGSCI (HWORATEST) 3 > edit params. / GLOBAL

Click "Yes" and enter the following to save

GGSCHEMA ogg

CHECKPOINTTABLE ogg.checkpoint

Add checkpoint

GGSCI (HWORATEST) 7 > add checkpointtable ogg.checkpoint

Please log in before adding an operation

3. Configure the extraction process of the server

GGSCI (HWORATEST) 8 > add extract ext_w1,tranlog, begin now

GGSCI (HWORATEST) 9 > add exttrail d:\ app\ Administrator\ ogg\ dirdat\ wt, extract ext_w1

GGSCI (HWORATEST) 10 > edit params ext_w1

Click "Yes" and enter the following to save

Ddl include all

Extract ext_w1

Userid ogg, password oracle

Exttrail D:\ app\ Administrator\ ogg\ dirdat\ wt

Table prun.*

Note that the above contains DDL replication, mainly on all objects of prun

4. Configure the delivery process of the server

GGSCI (HWORATEST) 11 > add extract pum_w1, exttrailsource D:\ app\ Administrator\ ogg\ dirdat\ wt, begin now

GGSCI (HWORATEST) 12 > add rmttrail D:\ app\ Administrator\ ogg\ dirdat\ rt, extract pum_w1

GGSCI (HWORATEST) 13 > edit params pum_w1

Click "Yes" and enter the following to save

Extract pum_w1

Userid ogg, password oracle

Rmthost 192.168.10.19, mgrport 7809

Rmttrail D:\ app\ Administrator\ ogg\ dirdat\ rt

Table prun.*

Note that the above IP is the target side IP address

At this point, the configuration of Soure is basically complete, and you can view it on info all:

The following configurations are made on the target side:

1. Configure Manager management process parameters:

GGSCI (HWOGG01) 2 > edit params mgr

Click "Yes" to enter the following and save

Port 7809

2. Configure checkpoint table

GGSCI (HWOGG01) 3 > edit params. / GLOBAL

Click "Yes", enter the following and save

GGSCHEMA ogg

CHECKPOINTTABLE ogg.checkpoint

Add checkpoint

GGSCI (HWOGG01) 4 > add checkpointtable ogg.checkpoint

3. Configure the replication process of the target server

GGSCI (HWOGG01) 5 > add replicat rep_w2, exttrail d:\ app\ Administrator\ ogg\ dirdat\ rt, checkpointtable ogg.checkpoint

GGSCI (HWOGG01) 6 > edit params rep_w2

Click "Yes", enter the following and save

Replicat rep_w2

Ddl include all

Ddlerror default ignore retryop maxretries 3 retrydelay 5

Userid ogg, password oracle

Assumetargetdefs

Discardfile d:\ app\ Administrator\ ogg\ dirdat\ rep_w2_discard,append

Map prun.*, target prun.*

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, OVERWRITE))

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE))

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE))

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, IGNORE))

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, IGNORE))

After configuration, check the relevant information

IV. Verification testing

Finally, start the mgr process on both sides and test it (you can type help to find the related commands)

Start the source side first

Start mgr

Start ext_w1

Start pum_w1

Start the target side:

Start mgr

Start rep_w2

Check whether the following table exists on the target side, and if not, create the above table on the source side, and then check if it will be copied

Target side check:

The source side carries on the table building operation

SQL > create table prun.TESTOGG as select * from prun.cts_role

Check the target end again

The test was successful!

Reference: http://blog.itpub.net/29485627/viewspace-1766772/

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.