Oracle GoldenGate 11g unidirectional DDL configuration practice

After carefully studying the GoldenGate enterprise-level O & M practices, I focused on my understanding of several OGG processes. Combined with my understanding of the principles, I tried the experiment for nearly two days and configured it four times, finally, the configuration is successful. This article has been completed after many successful configuration practices, and many details need to be improved.

From: http://koumm.blog.51cto.com

Directory
======================================
1. Prepare and install GoldenGate in the environment

1. Database preparation
2. Download the OGG software and decompress it for installation.
3. Prepare OGG Environment Variables
4. Configure the log Mode
5. Create a GoldenGate User Account
6. Install GoldenGate Software
7. Consistent configuration of source and target Databases
 
2. GoldenGate DML synchronization source configuration

1. Source MGR Process
2. Configure the Extract process group
3. Configure the Pump Process Group

3. GoldenGate DML synchronization target Configuration

1. Target MGR Process
2. Add a checklist
3. Configure the target Peplicat Process Group
4. Check the checkpoint status.
5. DML configuration test

Iv. GoldenGate DDL synchronization Configuration

1. The source end supports DDL replication to run scripts.
2. Modify the params file of the source extract Process
3. Modify the params file of the target replicat Process
4. Test

========================================================

Introduction to several important GoldenGate processes:

1. The Manager management process is enabled at both ends, and other processes are monitored and restarted. Data Storage is allocated and errors and events are reported;
2. The Extract process captures and transmits transaction data from the log to the target;
3. The Server Collector process accepts data on the target side and writes the data to the trail file;
4. The Replicat process reads the trail file and applies it to the traget database;
5. When a trail file is used, gg captures the information by himself. It is an OS file stored in./dirdat/, named after X00000, in N order: 1, 2, 3... When this file is used up, configurable parameters are automatically deleted.

1. Prepare and install GoldenGate in the environment

1. Database preparation

1) source server

IP Address: 192.168.14.150
Database: 10.2.0.5 64 bit
SID: orcl
Operating system version: Oracle 5.4 64 bit
Ogg version: fbo_ggs_Linux_x64_ora10g_64bit.tar

Note: The prerequisites are that the database has been prepared for installation.

2) Target Server

IP Address: 192.168.14.151
Database: 10.2.0.5 64 bit
SID: slave
Operating system version: Oracle 5.4 64 bit
Ogg version: fbo_ggs_Linux_x64_ora10g_64bit.tar

Note: The prerequisites are that the database has been prepared for installation.

2. Download the OGG software and decompress it for 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 oracle: oinstall/u01/app/oracle/ogg

2) the target server must 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. Prepare OGG Environment Variables

# Configure the source server and target server
$ Vi/home/oracle/. bash_profile

Add the following line:
Export GGATE = $ ORACLE_BASE/ogg

Effective environment variable
Source/home/oracle/. bash_profile

4. Configure the log Mode

# Source server, which does not need to be configured (non-bidirectional)

1) view the specifications and log Mode
Sqlplus/as sysdba
Select log_mode, supplemental_log_data_min, force_logging from v $ database;

2) configure the rule Mode
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
Alter system set log_archive_dest_1 = 'location =/u01/archive 'scope = both;

3) configure the log mode (enable the forced log mode and supplement the log mode)
Alter database add supplemental log data;
Alter database force logging;

4) view the configuration result:
SQL> select log_mode, supplemental_log_data_min, force_logging from v $ database;

LOG_MODE SUPPLEME
-----------------------
ARCHIVELOG YES

5. Create a GoldenGate User Account

1) source server

Su-oracle
Sqlplus/nolog
Conn/as sysdba;

Create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/orcl/tbs_ogg.dbf' size 10 m autoextend on next 10 m;
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

Su-oracle
Sqlplus/nolog
Conn/as sysdba;

Create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/orcl/tbs_ogg.dbf' size 10 m autoextend on next 10 m;
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. Install GoldenGate Software

(1) Both the source server and the target server must be configured.

Su-oracle
Cd/u01/app/oracle/ogg
./Ggsci

GGSCI (test) 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
Export data files/u01/app/oracle/ogg/dirver: created

GGSCI (test) 2> quit

(2) the target server must be configured.

Su-oracle
Cd/u01/app/oracle/ogg
./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
Export data files/u01/app/oracle/ogg/dirver: created

GGSCI (slave) 2> quit

Note 1: ogg_11.1.1.1.2 and ogg_11.2.x.x.1 show that the structure of the created file directory list is different.
Content created for version ogg11.2.

7. Consistent configuration of source and target Databases

There are many ways to configure source and target data consistency, which can be achieved through oracle.
This method uses imp to achieve consistency of the initial data table.

(1) Source end Test Users:

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 );
Commit;

Select count (*) from t1;
----------------------------
COUNT (*)
50315

(2) target end Test Users:
The target server imports the data through exp/imp and only imports 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: You can also use goldengate to initialize data loading for data synchronization.

 
2. GoldenGate DML synchronization source configuration

Configuration ideas:
1) Configure DML synchronization first
2) Configure DDL synchronization.

Cd/u01/app/oracle/ogg
./Ggsci

1. Source MGR Process

1) edit the master Process Group

Edit params mgr

Port 7809
Dynamicportlist 7800-8000
Autorestart extract *, retries 5, waitminutes 2, resetminutes 5

Note:
Port specifies the mgr Process Communication port
Dynamicportlist indicates that the mgr process can specify a port for dynamic communication between the source and destination.
Autorestart extract indicates that the extract process group is automatically restarted, and all processes are restarted every two minutes. Retry five times and reset every five minutes.
After the parameters are configured, restart the mgr process to take effect.

2) Start the main management process
Start mgr

2. Configure the Extract process group

1) edit the configuration file

Edit params eora

Extract eora
Dynamicresolution
Setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
Userid ogg, password ogg
Exttrail/u01/app/oracle/ogg/dirdat/et
Table hr .*;

Note:
Extract eora defines the extract process name
Dynamicresolution
Setenv: Set Environment Variables
Userid: log on to the database
Exttrail specifies the local trail file address
Table defines the synchronized table

2) Add extraction process

Add extract eora, tranlog, begin now

3) Add a local trail File

Add exttrail/u01/app/oracle/ogg/dirdat/et, extract eora

Note:
Create a local trail file. The main extract process is responsible for writing this part of the file, and the pump is responsible for uploading this part of the file to the target server.

4) start the service

Start extract eora

3. Configure the Pump Process Group

1) edit the configuration file

Edit params pump_so

Extract pump_so
Dynamicresolution
Passthru
Rmthost 192.168.14.151, mgrport 7809, compress
Rmttrail/u01/app/oracle/ogg/dirdat/pt
Table hr .*;

2) Add a pump Process

Add extract pump_so, exttrailsource/u01/app/oracle/ogg/dirdat/et

3) Add a remote trail File

Add rmttrail/u01/app/oracle/ogg/dirdat/pt, extract pump_so

Description: Specifies the remote trail file.

4) Start the pump Process

Start extract pump_so

3. GoldenGate DML synchronization target Configuration

Cd/u01/app/oracle/ogg
./Ggsci

1. Target 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 a checklist

Note: After the default checkpoint is specified in the GLOBALS file, the new Replicat groups will automatically use this parameter when it is created. No other commands are required.

1) edit the global configuration file

Edit params./GLOBALS
CHECKPOINTTABLE ogg. checkpoint

2) exit # exit the ggsci terminal.

3) Add a checkpoint table

./Ggsci

GGSCI (slave) 1> dblogin userid ogg, password ogg
Successfully logged into database.

GGSCI (slave) 2> add checkpointtable ogg. checkpoint
Successfully created checkpoint table OGG. CHECKPOINT.

3. Configure the target Peplicat Process Group

1) edit the configuration file
Edit params repl

Replicat repl
Userid ogg, password ogg
Assumetargetdefs
Reperror default, discard
Discardfile/u01/app/oracle/ogg/dirrpt/repl. dsc, append, megabytes 50
Dynamicresolution
Map hr. *, target hr .*;

2) Add a replication process
Add replicat repl, exttrail/u01/app/oracle/ogg/dirdat/pt, CHECKPOINTTABLE ogg. checkpoint

3) start the process
Start repl

4. Check the checkpoint status.

Su-oracle
Sqlplus ogg/ogg

SQL> select tname from tab;
TNAME
-------------------------------------
CHECKPOINT TABLE
Checkpoint_lm TABLE

Select * from checkpoint;

Note 1: ogg_11.1.1.1.2 only has one CHECKPOINT table.
NOTE 2: ogg_11.2.x.x.1 has two tables: CHECKPOINT and checkpoint_lm.

5. DML configuration test

1) number of records in the source table

Conn hr/hr
SQL> select count (*) from t1;

COUNT (*)
----------
49935

2) Submit the DML operation
SQL> delete from t1 where rownum <200;
199 rows deleted.

SQL> commit;

SQL> select count (*) from t1;
COUNT (*)
----------
49736

3) view the target end

Conn hr/hr
SQL> select count (*) from t1;
COUNT (*)
----------
49736

Note:

By now, the one-way configuration of goldengate DML has been completed and is carried out through a simple test example.

Iv. GoldenGate DDL synchronization Configuration

1. Supports DDL replication and running scripts

# Server Source Configuration

1) Specify the database mode

$./Ggsci
GGSCI (test) 1> edit param./GLOBALS
GGSCI (test) 2> view param./GLOBALS
Ggschema ogg

GGSCI (test) 3> stop mgr

2) The oralce 10gR2 database must disable the recycle bin.
Su-oracle
Cd/u01/app/oracle/ogg
Sqlplus/as sysdba;
Alter system set recyclebin = off scope = both;

3) install DDL objects

SQL> grant dba to ogg;
SQL> @ marker_setup Description: ogg is specified for all users.
SQL> @ ddl_setup Description: 11.1.1.2 you need to manually enter ogg, INITIALSETUP, yes
SQL> @ role_setup
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ ddl_enable
SQL> @ marker_status. SQL

Note 1: Execute the dbmspool package to create the DBMS_SHARED_POOL package in the database, which will be used later in the ddl_pin package.

SQL> @? /Rdbms/admin/dbmspool. SQL

Package created.
Grant succeeded.
View created.
Package body created.

NOTE 2: run the ddl_pin. SQL command to store the DDLReplication-related objects in the shared pool through the dbms_shared_pool.keep stored procedure to ensure that these objects do not RELOAD and improve performance.

SQL> @ ddl_pin. SQL ogg

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

2. Modify the params file of the extract Process

# Server Source Configuration
# Modify the params file of the extract Process on the source side, add the "ddl include all" parameter, and restart 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 .*;
 
# Note: two lines of tranlogoption and ddl are added.

3) Start the mgr and eora Processes
GGSCI (test) 4> start mgr
GGSCI (test) 5> start extract eora

4) view the eora_t1 process startup status

GGSCI (test) 6> info extract eora

3. Modify the params file of the target replicat Process

# Target server configuration

# Modify the params file of the replicat process on the target side,
Add parameters such as "ddlerror default ignore retryop maxretries 3 retrydelay 5" and 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
Sending STOP request to REPLICAT RORA_T1... Request processed.

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: The target end does not need to configure the DDL include all statement. Otherwise, errors may occur.

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

4) Start the rora_t1 Process
GGSCI (slave) 4> start mgr # automatically starts the repl Process
GGSCI (slave) 5> info replicat repl

4. Test

At the source end, the hr user creates a table to check whether the target end is successfully created.
Create table abc (id integer, name char (10 ));
Insert into abc values (1, 'abc ');

Link: http://koumm.blog.51cto.com/703525/1113773

This article is from the "koumm linux technology blog" blog, please be sure to keep this source http://koumm.blog.51cto.com/703525/1113773