OGG synchronizes two Oracle databases

OGG synchronizes two Oracle databases

Here is a simple example to illustrate how goldengate synchronizes two Oracle databases.
The content is as follows:
1. Configure the database information.
2. Install golden gate.
3. Configure golden gate.
4. Test Synchronization

First, let's look at the experiment environment.

Environment

The source is a single instance.
Oracle Enterprise 5 + ORACLE 10.2.0.4
IP: 10.4.128.100

The target end is a single instance.
Oracle Enterprise 5 + ORACLE 10.2.0.4
IP: 10.4.128.101

Databases have been created for both hosts. The sid is devdb and emrep respectively.

Configure Data Synchronization from devdb to emrep

Goldengate version 11.2.1.0

1. Configure database information

 

Open archive mode in source database

 

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination/u01/archive1
Oldest online log sequence 180
Next log sequence to archive 181
Current log sequence 181

If you are in non-archive mode, change to archive mode:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

 

Enable force logging in the source database

 

SQL> select force_logging from v $ database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v $ database;
FOR
---
YES

Enable supplemental log in the source database

SQL> select supplemental_log_data_min from v $ database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.
Switch logs to make the changes take effect
SQL> alter system switch logfile;
System altered.

SQL> select supplemental_log_data_min from v $ database;

SUPPLEME
--------
YES

 

Disable the recycle bin in the source database

The official note is that, due to a known issue, the recycle bin will have an impact on the DDL trigger, so you need to disable it. Therefore, we only need to disable the recycle bin in the source database.

SQL> show parameter recyclebin

NAME TYPE VALUE
-----------------------------------------------------------------------------
Recyclebin string on

SQL> alter system set recyclebin = off;

System altered.

SQL> show parameter recyclebin

NAME TYPE VALUE
-------------------------------------------------------------------
Recyclebin string OFF

 

Create a goldengate database user (source and target)

 

Note: Both the source and target must be

[Oracle @ rac1 ~] $ Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.4.0-Production on Fri Jan 9 11:56:28 2015
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.


Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace goldengate;

Tablespace created.

SQL> create user goldengate identified by goldengate default tablespace goldengate;

User created.

SQL> grant connect, resource to goldengate;

Grant succeeded.

SQL> grant execute on utl_file to goldengate;

Grant succeeded.

SQL>


The database user used by the extraction process requires additional permissions. We also grant these permissions to the Database User goldengate (executed in the source database)

SQL> exec dbms_streams_auth.grant_admin_privilege ('goldengate ');

PL/SQL procedure successfully completed.

SQL> grant insert on system. logmnr_restart_ckpt $ to goldengate;

Grant succeeded.

SQL> grant update on sys. streams $ _ capture_process to goldengate;

Grant succeeded.

SQL> grant become user to goldengate;

Grant succeeded.

SQL>

To ensure the normal operation of GoldenGate, especially on the target end, grant the DBA permission to the goldengate User:
SQL> grant dba to goldengate;

 

2. GoldenGate installation environment

 

 

Decompress the goldengate installation file to the installation directory.

 

Installing the GoldenGate software is easy. Just unzip it.
Log On As A goldengate user
[Goldengate @ rac1 goldengateMedia] $ mkdir/opt/gg/goldengate,
[Goldengate @ rac1 goldengateMedia] $ cp ggs_Linux_ora10g_.tar/opt/gg/goldengate
[Goldengate @ rac1 goldengateMedia] $ cd/opt/gg/goldengate
[Goldengate @ rac1 goldengate] $ tar-xvf ggs_Linux_ora10g.tar

 

 

Configure Environment Variables

 

Source and target:
Modify the environment variable configuration file of goldengate (ORACLE_SID is modified as needed)
Cat>/home/goldengate/. bashrc <EOF
ORACLE_HOME =/opt/app/oracle/product/10.2.0/db_1
Export ORACLE_HOME
ORACLE_SID = devdb
Export ORACLE_SID
GG_HOME =/opt/gg/goldengate
Export GG_HOME
PATH =\$ ORACLE_HOME/bin: $ GG_HOME: \ $ PATH
Export PATH
LD_LIBRARY_PATH =\$ ORACLE_HOME/lib: \ $ GG_HOME: \ $ LD_LIBRARY_PATH
Export LD_LIBRARY_PATH
EOF
Apply the modified environment variable, enter the GoldenGate installation directory, and run ldd ggsci to find the required library files. If the shared library file cannot be found, for example, libnnz10.so => not found, check the LD_LIBRARY_PATH environment variable settings.
[Goldengate @ ggdb goldengate] $ source ~ /. Bashrc
[Goldengate @ ggdb goldengate] $ cd $ GG_HOME
[Goldengate @ ggdb goldengate] $ ldd ggsci
We can think that the installation of ogg is actually a decompression. Very simple.

3. Configure goldengate

Create a goldengate working directory

Source and target:

[Goldengate @ rac1 goldengate] $ cd $ GG_HOME
[Goldengate @ rac1 goldengate] $./ggsci

GGSCI (rac1) 1> create subdirs

Creating subdirectories under current directory/opt/gg/goldengate

Parameter files/opt/gg/goldengate/dirprm: created
Report files/opt/gg/goldengate/dirrpt: created
Checkpoint files/opt/gg/goldengate/dirchk: created
......

GGSCI (gg1) 2> exit

Create a directory for storing trail files

Source and target:

[Goldengate @ rac1 ~] $ Mkdir/opt/gg/trails
[Goldengate @ rac1 ~] $ Ls-l/opt/gg | grep trails

Configuration MANAGER

 

Source and target:

The port range used by the GoldenGate (extract and replicat) process is configured in DYNAMICPORTLIST.
The PORT parameter specifies the PORT used by the MANAGER.
The AUTORESTART parameter automatically restarts when the extraction/replication process fails.
Configure the MANAGER parameter. The PURGEOLDEXTRACTS parameter specifies that the trail files that have been extracted and copied are automatically deleted based on the checkpoint, but the last 10 files are retained.
PURGEDDLHISTORY and PURGEMARKERHISTORY Delete the expired data in the DDL History Table and the marker table respectively to prevent them from becoming too large.

GGSCI (gg1) 1> edit params mgr

PORT 5898
PURGEOLDEXTRACTS/opt/gg/trails/w1 *, USECHECKPOINTS, MINKEEPFILES 10
Autorestart er *, RETRIES 3, WAITMINUTES 5
Purgeddlhistory minkeepdays 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
Purgemarkerhistory minkeepdays 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

Global parameter settings

Source end:

GGSCI (rac1) 2> edit params./globals
GGSCHEMA goldengate

Target end:

Create a checkpoint table
Replicat maintains the read position in the trail file through this table. This is not a required operation. If this table is not available, a disk file is used for maintenance.

GGSCI (ggdb) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (ggdb) 3> add checkpointtable goldengate. chkpoint

Successfully created checkpoint table GOLDENGATE. CHKPOINT.

GGSCI (ggdb) 4> edit params./globals

GGSCHEMA goldengate
CHECKPOINTTABLE goldengate. chkpoint

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

GoldenGate unidirectional table DML Synchronization

Oracle GoldenGate series: restoration principles of Extract processes

Oracle GoldenGate installation Configuration

OGG-01004 OGG-1296 error for Oracle goldengate

Oracle GoldenGate Quick Start Tutorial: Basic Concepts and configurations

Build a GoldenGate bidirectional replication environment from Oracle to Oracle

For more details, please continue to read the highlights on the next page:

1 2 Next Page