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

This article introduces the relevant knowledge of "how OGG realizes the synchronization of two Oracle databases". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, let's take a look at the experimental environment.

Environment

The source end is a single instance

Oracle CENTOS 6 + ORACLE 10.2.0.4

IP: 192.168.56.101

The destination side is a single instance

Oracle CENTOS 6 + ORACLE 10.2.0.4

IP: 192.168.56.102

Both hosts have created databases, and the sid is devdb and emrep, respectively.

Configure devdb to emrep data synchronization

Goldengate version 11.2.1.0

1. Configure database information

Open the archive mode in the source-side 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.

Open force logging in the source-side 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

Open supplemental log in the source-side 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

Close the Recycle Bin in the source database

The official statement is that due to a known problem, the Recycle Bin will have an impact on the DDL trigger, so it needs to be closed. Thus, we just need to close the Recycle Bin in the source library.

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 goldengate database users (source and destination)

Note: both the source and destination sides need

[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, 2007, Oracle. All Rights Reserved. Connected to:

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 users used by the extraction process require additional permissions, which are also granted to the database user goldengate (executed in the source-side 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 proper operation of GoldenGate, especially on the target side, give the goldengate user DBA privileges:

SQL > grant dba to goldengate

2.GoldenGate installation environment

Extract the goldengate installation file to the installation directory

It is easy to install GoldenGate software and decompress it.

Log in as 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 side and destination side:

Modify the goldengate user's environment variable profile (ORACLE_SID is modified according to the actual situation)

Cat > > / home/goldengate/.bashrc 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 destination:

[goldengate@rac1 ~] $mkdir / opt/gg/trails

[goldengate@rac1 ~] $ls-l / opt/gg | grep trails

Configure MANAGER

Source side and destination side:

The port range used by the GoldenGate (extract and replicat) processes is configured in DYNAMICPORTLIST

The PORT parameter specifies the port used by MANAGER

The AUTORESTART parameter causes the extract / copy process to restart automatically after a failure

To configure the parameters of MANAGER, the PURGEOLDEXTRACTS parameter specifies that when trail files that have been extracted and copied are found to have been completed according to checkpoint, they will be automatically deleted, but the last 10 will be retained.

PURGEDDLHISTORY and PURGEMARKERHISTORY remove out-of-date data from the DDL history table and the marker table, respectively, to control that they do not become 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 setting

Source side:

GGSCI (rac1) 2 > edit params. / globals

GGSCHEMA goldengate

Destination side:

Create a checkpoint table

Replicat uses this table to maintain the read position in the trail file. This is not a necessary operation. If you do not have this table, it is maintained through a disk file.

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

This is the end of the content of "how to synchronize two Oracle databases with OGG". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.