Build a GoldenGate bidirectional replication environment from Oracle to Oracle

Build a GoldenGate bidirectional replication environment from Oracle to Oracle

Objective: To build a Goldengate two-way replication environment (DDL + DML supported) from Oracle to Oracle ).

Environment:

OS: Red Hat Enterprise Linux Server release 5.5 (Tikanga)

DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

Gg1 and gg2 are mutually source and target, and the OS and the security DB are the same.

 

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 one-way replication test environment for Oracle to Oracle Golden Gate

 

1. Install GG

Perform the following operations on both gg1 and gg2:

(1) Add environment variables and add the following content to the/home/oracle/. bash_profile file:

Export PATH =/u01/ggate: $ PATH

ExportLD_LIBRARY_PATH =/u01/ggate: $ LD_LIBRARY_PATH

Export ggtest =/u01/ggate

Here, GG and Oracle use the same user, so you can add the GG variable. Load the environment variable you just set:

Source/home/oracle/. bash_profile

(2) Use the ggsci tool to create necessary Directories

Gg1:/u01/ggate> ggsci

-- Call ggsci Tool

Oracle GoldenGate Command Interpreter forOracle

Version 11.1.1.1OGGCORE _ 11.1.1 _ PLATFORMS_110421.2040

Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14

 

Copyright (C) 1995,201 1, Oracle and/or itsaffiliates. All rights reserved.

 

GGSCI (gg1) 1> create subdirs

-- Use ggsci to create a directory

Creating subdirectories under currentdirectory/u01/ggate

 

Parameter files/u01/ggate/dirprm: created

Report files/u01/ggate/dirrpt: created

Checkpoint files/u01/ggate/dirchk: created

Process status files/u01/ggate/dirpcs: created

SQL script files/u01/ggate/dirsql: created

Database definitions files/u01/ggate/dirdef: created

Extract data files/u01/ggate/dirdat: created

Temporary files/u01/ggate/dirtmp: created

Encrypted data files/u01/ggate/dirver: created

Encrypted data Lock files/u01/ggate/dirver/lock: created

Upload data Out-Of-Sync files/u01/ggate/dirver/oos: created

Export data Out-Of-Sync XML files/u01/ggate/dirver/oosxml: created

Invalid Data Parameter files/u01/ggate/dirver/params: created

Invalid Data Report files/u01/ggate/dirver/report: created

Upload data Status files/u01/ggate/dirver/status: created

Export data Trace files/u01/ggate/dirver/trace: created

Stdout files/u01/ggate/dirout: created

 

GGSCI (gg1) 2>

 

The above is the installation of GG, which is executed in both source and target databases.

2. Configure source and target respectively

(1) GoldenGate captures the source database redo logs for analysis and applies the obtained data to the target database for data synchronization. Therefore, the source database must be in archive mode, and additional logs and forced logs must be enabled.

Archive mode, additional logs, and forced logs

-- View

SQL> select log_mode, supplemental_log_data_min, force_logging from v $ database;

LOG_MODE SUPPLEME

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

ARCHIVELOG NO

 

-- Modify

(1) archivelog

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

(2) force logging

SQL> alterdatabase force logging;

(3) supplemental log data

SQL> alterdatabase add supplemental log data;

(2) Disable Recycle Bin

If DDL support is enabled, you must disable recycle bin. The official website is explained as follows:

If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session restarts es implicitrecycle bin DDL operations that cause the trigger to fail.

Oracle 11g:

SQL> alter system set recyclebin = offscope = spfile;

System altered.

If the database is 10 Gb, You need to disable recyclebin and restart it; or manually purge recyclebin.

(3) create a user to store DDL information and grant permissions

SQL> create user ggtest identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to ggtest;

Grant succeeded.

SQL> grant execute on utl_file to ggtest;

Grant succeeded.

Exit all sessions using Oracle, and then run the following script for users with SYSDBA permissions:

Gg1:/u01/ggate> echo $ ggate

/U01/ggate

-- Enter the GG directory and call the script:

Gg1:/home/oracle> cd $ ggate

Gg1:/u01/ggate> sqlplus/as sysdba;

SQL * Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

-- Script 1:

SQL> @ marker_setup. SQL;

Marker setup script

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Enter the username we created earlier:

Enter GoldenGate schema name: ggtest

Marker setup table script complete, runningverification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to ggtest

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

Script complete.

-- Script 2:

SQL> @ ddl_setup. SQL;

GoldenGate DDL Replication setup script

Verifying that current user has privilegesto install DDL Replication...

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.

-- Note that the recycle bin must be disabled in 10 Gb. In Versions later than 11 GB, you do not need to disable it.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Prompt the user who entered GG:

Enter GoldenGate schema name: ggtest

You will be prompted for the mode ofinstallation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

-- Here Let's select the installation mode: install and reinstall select INITIALSETUP

Enter mode of installation: INITIALSETUP

Working, please wait...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables...

Check complete.

Using ggtest as a GoldenGate schema name, INITIALSETUP as a mode of installation.

 

Working, please wait...

 

DDL replication setup script complete, running verification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to ggtest

 

DDLORA_GETTABLESPACESIZE STATUS:

......

 

STATUS OF DDL REPLICATION

Bytes -------------------------------------------------------------------------------------------------------

SUCCESSFUL installation of DDL Replicationsoftware components

 

Script complete.

-- Script 3:

SQL> @ role_setup. SQL;

GGS Role setup script

This script will drop and recreate the roleGGS_GGSUSER_ROLE

To use a different role name, quit thisscript and then edit the params. SQL script to change the gg_role parameter tothe preferred name. (Do not run the script .)

 

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Enter the GG user name as follows:

Enter GoldenGate schema name: ggtest

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

 

Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:

-- We are prompted to grant permissions to relevant users:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

Where <loggedUser> is the userassigned to the GoldenGate processes.

-- Script 4: grant permissions

SQL> grant GGS_GGSUSER_ROLE to ggtest;

Grant succeeded.

-- Script 5:

SQL> @ ddl_enable. SQL;

Trigger altered.

Note that all the tables created by the script use the default names. You can also modify the default names of these tables.

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

1 2 Next Page