Oracle OGG Configuration

Http://www.dataguru.cn/thread-171156-1-1.html

Goldengate several important processes introduced:

1. The manager management process opens at both ends, monitors and restarts other processes, allocates data storage and reports errors and events;

2, extract process from the log to fetch and transfer to the target transaction data;

3. The Server collector process accepts data and writes trail files on the target (accept) side;

4, Replicat process read Trail file, and apply to traget database;

5, Trail files when GG own capture information files, is an OS file, stored in./dirdat/, named after X00000, n order ... This file is automatically removed with configurable parameters.

Problem:

1. The database is set to archive mode

2, pay special attention to the extraction process, delivery process, the relationship between the acceptance process

Extract the process definition file path to the drop process local path add extract Pump_so,exttrailsource./dirdat/et/dd

The extraction process defines the remote file path to the Accept process path add Replicat rep7,exttrail./dirdat/dp1/sd

First, environment preparation and installation Goldengate

1. Database Readiness

1) Source Server

IP Address: 192.168.14.150

Database: 10.2.0.5

Sid:orcl

Operating system version: Oracle 5.4

Database: 10.2.0.5

Sid:slave

Operating system version: Oracle 5.4

2. Download ogg software and unzip the installation

1) Source Server

Mkdir-p/u01/app/oracle/ogg

Unzip Fbo_ggs_linux_x64_ora10g_64bit.zip

TAR-XVF fbo_ggs_linux_x64_ora10g_64bit.tar-c/u01/app/oracle/ogg/

Chown-r Oracleinstall/u01/app/oracle/ogg

2) The target server is to be configured

Mkdir-p/u01/app/oracle/ogg

Unzip Fbo_ggs_linux_x64_ora10g_64bit.zip

TAR-XVF fbo_ggs_linux_x64_ora10g_64bit.tar-c/u01/app/oracle/ogg/

Chown-r Oracle:oinstall/u01/app/oracle/ogg

3. Preparing the OGG environment variable

#源服务器, and the target server to configure

$ vi/home/oracle/.bash_profile

Add the following line:

Export ggate= $ORACLE _base/ogg

Effective environment variables

Source/home/oracle/.bash_profile

4. Configure logging mode

# source server, destination server not configured (non-bidirectional)

1) View the file and log modes

Select log_mode,supplemental_log_data_min,force_logging from V$database;

2) Configure as the mode of the document

Shutdown immediate;

startup Mount;

ALTER DATABASE Archivelog;

ALTER DATABASE open;

Alter system set log_archive_dest_1= ' location=/u01/archive ' Scope=both;

3) Configure log mode (turn on mandatory and supplemental log mode)

ALTER DATABASE add supplemental log data;

ALTER DATABASE force logging;

4) View the configuration results:

Sql> select log_mode,supplemental_log_data_min,force_logging from V$database;

Log_mode Suppleme for

------------ -------- ---

ARCHIVELOG Yes Yes

5. Create a Goldengate user account

1) Source Server

Conn/as Sysdba;

Create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/target/tbs_ogg.dbf ' size 10m autoextend on next 10m;

Create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace TEMP Quota Unlimited on tbs_ogg;

Grant Connect,resource to Ogg;

Grant create session,alter session to Ogg;

Grant Select any Dictionary,select any table to Ogg;

Grant alter any table to OGG;

Grant Flashback any table to Ogg;

Grant execute on Dbms_flashback to Ogg;

2) Target server

Conn/as Sysdba;

Create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/orcl/tbs_ogg.dbf ' size 10m autoextend on next 10m;

Create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace TEMP Quota Unlimited on tbs_ogg;

Grant Connect,resource to Ogg;

Grant create session,alter session to Ogg;

Grant Select any Dictionary,select any table to Ogg;

Grant alter any table to OGG;

Grant Flashback any table to Ogg;

Grant execute on Dbms_flashback to Ogg;

Grant insert any table to OGG;

Grant Delete any table to Ogg;

Grant update any table to Ogg;

6. Installing the Goldengate Software

(1) The source server, and the target server are to be configured

./ggsci

Ggsci (test) 1> Create Subdirs

Parameter files/u01/app/oracle/ogg/dirprm:created

Report files/u01/app/oracle/ogg/dirrpt:created

Checkpoint files/u01/app/oracle/ogg/dirchk:created

Process Status files/u01/app/oracle/ogg/dirpcs:created

SQL Script files/u01/app/oracle/ogg/dirsql:created

Database Definitions files/u01/app/oracle/ogg/dirdef:created

Extract Data files/u01/app/oracle/ogg/dirdat:created

Temporary files/u01/app/oracle/ogg/dirtmp:created

Veridata files/u01/app/oracle/ogg/dirver:created

(2) The target server is to be configured

./ggsci

Ggsci (slave) 1> Create Subdirs

Creating subdirectories under current Directory/u01/app/oracle/ogg

Parameter files/u01/app/oracle/ogg/dirprm:created

Report files/u01/app/oracle/ogg/dirrpt:created

Checkpoint files/u01/app/oracle/ogg/dirchk:created

Process Status files/u01/app/oracle/ogg/dirpcs:created

SQL Script files/u01/app/oracle/ogg/dirsql:created

Database Definitions files/u01/app/oracle/ogg/dirdef:created

Extract Data files/u01/app/oracle/ogg/dirdat:created

Temporary files/u01/app/oracle/ogg/dirtmp:created

Veridata files/u01/app/oracle/ogg/dirver:created

7. Configuration source, target database consistent

There are many ways to configure source and target data consistency, which can be done in Oracle. This method uses the Imp method to realize the initial data table consistency.

(1) Source-side test User:

Create user hr identified by HR;

Grant Connect,resource,select_catalog_role to HR;

Conn hr/hr;

CREATE TABLE T1 as select * from Dba_objects;

ALTER TABLE t1 ADD constraint Prikey_t1 primary key (OBJECT_ID);

(2) Target end test User:

The destination server imports the past through exp/imp, importing only the table structure

Create user hr identified by HR;

Grant Connect,resource,select_catalog_role to HR;

Conn hr/hr;

Imp hr/hr file=hr.dmp rows=n

Note: Data synchronization can also be achieved by goldengate initialization of data loading

Second, GoldenGate DML synchronization source-side configuration

Configuration ideas:

1) Configure DML synchronization first

2) Configure DDL synchronization again

./ggsci

1. Source Mgr Process and start

1) Edit the main process group

PORT 7809

Dynamicportlist 7800-7900

AutoRestart EXTRACT *,retries 3, waitminutes 5, Resetminutes 60

Purgeoldextracts./dirdat/*/*, usecheckpoints, Minkeepfiles 100

Lagreportminutes 1

Laginfominutes 1

Lagcriticalminutes 1

Description

PORT Specifies the MGR process communication port

Dynamicportlist indicates that the MGR process can specify ports for source and destination dynamic communication

AutoRestart extract represents an automatic restart of the extract process group, which attempts to restart all processes every 2 minutes, retries 5 times, and zeroing every 5 minutes.

After configuring the parameters, restart the MGR process to take effect

2) Start the master management process

Start Mgr

2. Configuring the Extract Process Group

1) Edit the configuration file

Edit params Eora

Extract Eora

Dynamicresolution

SETENV (oracle_home= "/u01/app/oracle/product/11.2.0/db_1")

SETENV (oracle_sid= "source")

SETENV (nls_lang= "American_america. ZHS16GBK ")

UsERID ogg Password Ogg

Exttrail./DIRDAT/ET/DD------------This path is closely related to the pump process

DDL include all

Ddloptions Addtrandata, Report

Table hr.*;

2) Add extraction process

Add extract Eora, Tranlog,begin now

3) Add a local trail file

ADD Exttrail./DIRDAT/ET/DD (this path is closely related to the pump process) EXTRACT Eora, megabytes 5

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

4) Start the service

Start Extract Eora

3. Configuring the Pump Process Group

1) Edit the configuration file

Edit params Pump_so

EXTRACT Pump_so

PASSTHRU

Dynamicresolution

Rmthost 192.168.31.14, Mgrport 7809

Rmttrail./dirdat/dp1/sd

Discardfile./dirrpt/dp2.dsc,append,megabytes 5

Discardrollover at 6:00

Reportrollover at 6:00

Reportcount every 1 hours,rate

Table hr.*;

2) Add the pump process and indicate the local file

Add extract Pump_so,exttrailsource./DIRDAT/ET/DD (pay special attention to the extraction process file queue location)

3) Add a remote trail file

Add Rmttrail./DIRDAT/DP1/SD (pay special attention to parameter file Rmttrail location), EXTRACT Pump_so, megabytes 5

Description: Specify a remote Trail file

4) Start the pump process

Start Extract Pump_so

Third, GoldenGate DML synchronization target-side configuration

1. Target-side MGR Process

1) Edit the configuration file

Edit params Mgr

Port 7809

Dynamicportlist 7800-8000

Autostart ER *

AutoRestart extract *, Waitminutes 2, resetminutes 5

Lagreporthours 1

Laginfominutes 3

Lagcriticalminutes 5

purgeoldextracts/u01/app/oracle/ogg/dirdat/rt*, Usecheckpoints, Minkeepdays 3

2) Start

Start Mgr

2. Add Checklist

Note: When we specify the default checkpoint in the Globals file, the new Replicat groups automatically uses this parameter when it is created, no additional instructions are required

1) Edit the global configuration file

Edit params./globals

Successfully logged into database.

Ggsci (slave) 2> add checkpointtable ogg.checkpoint

Successfully created Checkpoint table OGG. CHECKPOINT.

Configuring target-side Peplicat process groups

1) Edit the configuration file

Edit params Repl

Replicat REP1

SETENV (oracle_home = "/u01/app/oracle/product/11.2.0/db_1")

SETENV (Oracle_sid = "target")

SETENV (Nls_lang = "American_america. ZHS16GBK ")

USERID ogg, PASSWORD ogg

Handlecollisions

Assumetargetdefs

Discardfile./DIRRPT/REP1.DSC, append, megabytes 5

--sourcedefs./dirdef/def.def

DDL INCLUDE MAPPED, OBJTYPE ' TABLE ' &

INCLUDE MAPPED OBJTYPE ' INDEX '

MAP HR. T1, TARGET Hr. T1;

2) Add replication process

Add Replicat rep7,exttrail./DIRDAT/DP1/SD (the path is a remote path defined by the pump process)

3) Start the process

Start REPL

4. You can view the checkpoint status

Su-oracle

Sqlplus Ogg/ogg

Sql> select tname from tab;

Tname

-------------------------------------

CHECKPOINT TABLE

Checkpoint_lox TABLE

SELECT * FROM Checkpoint;

Note 1:ogg_11.1.1.1.2 only checkpoint a single table.

Note 2:ogg_11.2.x.x.1 has checkpoint,checkpoint_lox two sheets.

5. DML Configuration Test

1) Number of records in the Source view table

Conn HR/HR

Sql> Select COUNT (*) from T1;

2) commit after DML operation

sql> Delete from T1 where rownum < 200;

Sql> Select COUNT (*) from T1;

3) Target side view

Conn HR/HR

Sql> Select COUNT (*) from T1;

Description

To this goldengate DML one-way configuration has been configured with a simple test example.

3) Installing DDL objects

Sql> Grant DBA to Ogg;

Sql> @marker_setup Description: All designated users OGG

sql> @ddl_setup Description: 11.1.1.2 requires manual input Ogg,initialsetup,yes

Sql> @role_setup

Sql> Grant Ggs_ggsuser_role to Ogg;

Sql> @ddl_enable

sql> @marker_status. sql

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

Note 1: Executing the Dbmspool package will create the Dbms_shared_pool package in the database, after which the Ddl_pin package needs to be used

Note 2: Execution Ddl_pin.sql ddlreplication related objects keep in a shared pool through dbms_shared_pool.keep stored procedures to ensure that these objects do not reload and improve performance.

sql> @ddl_pin. SQL Ogg

2. Modify the params file for the extract process

# Server Source-side configuration

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

1) Stop the EORA_T1 process

Ggsci (test) 2> Stop extract Eora

2) Edit the configuration file

Ggsci (test) 3> edit params Eora

Extract Eora

Dynamicresolution

Setenv (Nls_lang=american_america. ZHS16GBK)

UserID Ogg,password ogg

Exttrail/u01/app/oracle/ogg/dirdat/et

DDL include all

Ddloptions Addtrandata, Report

Table hr.*;

# Description: Added two lines of tranlogoption and DDL

3) Start the Mgr,eora process

Ggsci (test) 4> start Mgr

Ggsci (test) 5> start extract Eora

4) View the EORA_T1 process startup situation

Ggsci (test) 6> Info extract Eora

3. Modify the params file of the target-side replicat process

# Target Server Configuration

# The target side modifies the params file of the Replicat process,

Add "ddlerror default Ignore RETRYOP maxretries 3 RetryDelay 5" and other parameters to restart the Replicat process

1) Stop the MGR process

Ggsci (slave) 1> stop Mgr

2) Stop the REPL process

Ggsci (slave) 1> stop Replicat REPL

3) Edit the REPL configuration file

Ggsci (slave) 1> edit params Repl

Ggsci (slave) 2> view params repl

Replicat REPL

UserID ogg, Password ogg

Assumetargetdefs

Reperror default, Discard

DISCARDFILE/U01/APP/ORACLE/OGG/DIRRPT/REPL.DSC, append, megabytes 50

Dynamicresolution

Ddloptions Report

Ddlerror default Ignore RETRYOP maxretries 3 RetryDelay 5

Ddlerror Default Discard

Ddlerror Default Ignore Retryop

Map hr.*, Target hr.*;

Note: It is not necessary to configure the DDL include all statement on the target side, otherwise it will be an error.

# #Error text [Error code [1031], ora-01031:insufficient privileges

4) Start the RORA_T1 process

Ggsci (slave) 5> info replicat repl

4. Testing

Create a table on the source-side HR user to see if the target is successfully created.

CREATE TABLE ABC (ID integer, name char (10));

INSERT INTO ABC values (1, ' abc ');

Oracle OGG Configuration