Using OGG, one-way data synchronization between two Oracle databases

Using OGG, one-way data synchronization between two Oracle databases

Configuration: source database 100.100.100.21 Instance name dbsid2

Target database 100.100.100.41 Instance name db1

Tutorial objective: to synchronize the scott user emp table in the source database to the test table in the target database scott

The installation package is the ogg1121020.fbo_ggs_linux_x64_ora10g_64bit.zip file.

Decompress the package, and then decompress tar-xvf fbo_ggs_Linux_x64_ora10g_64bit.tar-C/oracle/ogg/

1. source database (dbsid2) Configuration:

(1) user permission: grant dba to scott

(2) Open the archive Mode

(3) additional logs:
Alter database add supplemental log data;

Alter database force logging;

(4) Add the db1 information of the target database to tnsnames. ora.

2. target database (db1) Configuration:

(1) scott creates an empty table test with the same table structure as emp.

(2) Open the archive Mode

(3) additional logs:

Alter database add supplemental log data;

Alter database force logging;

(4) add the information of the source database (dbsid2) to tnsnames. ora.

3. Install the source database server goldengate

(1) decompress the package to the/oracle/ogg target

(2) Run./ggsci

(3) create a subdirectory: GGSCI (host2) 1> create subdirs

Creating subdirectories under current directory/oracle/ogg

Parameter files/oracle/ogg/dirprm: already exists

Report files/oracle/ogg/dirrpt: created

Checkpoint files/oracle/ogg/dirchk: created

Process status files/oracle/ogg/dirpcs: created

SQL script files/oracle/ogg/dirsql: created

Database definitions files/oracle/ogg/dirdef: created

Extract data files/oracle/ogg/dirdat: created

(4) configure the Port parameters for character Management

GGSCI (host2) 6> edit params mgr

Input: PORT 7809 and save

Check input:

GGSCI (host2) 7> view params mgr

PORT 7809

(5) Start mgr:

GGSCI (host2) 8> start mgr

MGR is already running.

GGSCI (host2) 9> info mgr

Manager is running (IP port host2.7809 ).

4. Install goldengate on the target database server (same as the source database server)

(1) decompress the package to the/oracle/ogg target

(2) Run./ggsci

(3) create a subdirectory: GGSCI (host2) 1> create subdirs

Creating subdirectories under current directory/oracle/ogg

Parameter files/oracle/ogg/dirprm: already exists

Report files/oracle/ogg/dirrpt: created

Checkpoint files/oracle/ogg/dirchk: created

Process status files/oracle/ogg/dirpcs: created

SQL script files/oracle/ogg/dirsql: created

Database definitions files/oracle/ogg/dirdef: created

Extract data files/oracle/ogg/dirdat: created

(4) configure the Port parameters for character Management

GGSCI (host2) 6> edit params mgr

Input: PORT 7809 and save

Check input:

GGSCI (host2) 7> view params mgr

PORT 7809

(4) Start mgr:

GGSCI (host2) 8> start mgr

MGR is already running.

GGSCI (host2) 9> info mgr

Manager is running (IP port host2.7809 ).

5. Configure the source database server (dbsid2) Transfer Process extract

(1) Add a process named source1

GGSCI (host2) 19> add extract source1, sourceistable

EXTRACT added.

(3) modify the source1 Parameter

GGSCI (host2) 20> edit params source1

Add and save

Extract source1

Setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

Userid scott, password tiger

Rmthost 100.100.100.41, mgrport 7809

Rmttask replicat, group target1

Table scott. emp;

Note: 1. the username and password of the table to be copied are scott and tiger.

2. the IP address of the target database (db1) is 100.100.100.41, and the port is 7809.

3. The receiving process name of the target database (db1) is target1.

4. the table to be copied is the scott. emp table.

View modified Parameters

GGSCI (host2) 21> view params source1

6. Configure the target database db1 replicat receiving process

(1) Add a replicat process named source1

GGSCI (host2) 9> add replicat target1, specialrun

REPLICAT added.

(2) Add the modification parameter: GGSCI (host2) 13> edit params target1

Replicat target1

Setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

Assumetargetdefs

Userid scott, password tiger

Discardfile./dirrpt/target1.dsc, purge

Map scott. emp, target scott. test;

Note: 1. The target database account is scott and tiger.

2. Synchronize scott. emp from the source database to the scott. test table.

View the modified

GGSCI (host2) 1> view params target1

7. The source database (dbsid2) starts synchronization.

(1) Start Synchronization

GGSCI (host2) 11> start extract source1

Sending START request to MANAGER...

EXTRACT SOURCE1 starting

(2) monitoring synchronization process

GGSCI (host2) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

(3) view synchronization logs in the source database

GGSCI (host2) 13> view report source1

8. View synchronization information in the target database (db1)

SQL> select * from test;