Database Replication with Oracle Goldengate 11G İnstallation Step By Step On Oracle Linux

Oracle’s GoldenGate is a software solution that enables DML, DDL, DCL and TCL replication of existing data between databases. If you want to explain this sentence briefly, thanks to Oracle GoldenGate, we can transfer and operate the records of the transactions performed on the source database on the target databases we want, in real time or delayed. This transfer may include all operations performed in the source database or specifically as a transfer of only one seqeunce, table or schema.

One of the important features of Oracle GoldenGate is its hybrid structure. The source database can be Oracle or the destination database can be MS-SQL Server or vice versa. Oracle’s GoldenGate hybrid structure can support replication on a cross-platform or database. Examples include; Oracle, MS-SQL, Postgre SQL, MySQL and IBM DB2.

Click here for my article on replication with Oracle streams.

Oracle GoldenGate is installed separately on the source and destination servers, and the configurations of the servers are different. The source server runs the services we call Extract and Pumper, while the target server runs the services we call Collector and Replicat. In addition, Manager services are common on both servers. Let’s briefly explain these terms.

2. Exract: A service that runs on the resource. It enables DML, DDL, DCL and TCL operations performed on the database to be sent to the target database. Responsible for sending and receiving objects through database.
        

Oracle Golden Gate is a software solution that allows the synchronized (= replication) of DML, DDL, DCL and TCL operations of existing data between databases. The software is not database-dependent. Each database type can be replicated between each other. Golden Gate consists of 4 different processing threads. These parts and tasks are as follows.

1. Manager: Golden Gate is the management panel. It must be run on both servers. It is necessary to run Exract and Replicat services and is managed by the manager.

a. Pumper: It performs the processing of the commands from the Extract service and sending them to the other party. This process part runs under the extract service. Uses port 7809 as the port. The data is sent as a trail.

3. Replicat: Works on the target database. It is responsible for processing the commands executed on the source database with the pumper.
        a. Collector: Processes the trail files of the data sent by the Pumper and converts them into a format that the Replicat service understands.

Oracle Golden Gate Intended Use
    1. Continuity of business continuity and preparation of highavailability environments
    2. Transferring or upgrading data in the database
    3. To create a data warehouse (Data Warehousing)
    4. Data integration and verification

Oracle Golden Gate Installation;

1. Oracle Golden Gate software should be downloaded from the link below.
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
2. The following directories are created on databases (source and destination).
mkdir up /u01/app/oracle/product/11.2.0/goldengate

mkdir up /u01/app/oracle/product/11.2.0/goldengate/discard

3. We unzip the downloaded file to any directory of our operating system.
unzip fbo_ggs_linux_x64_shiphome.zip

4. The following parameters must be added to the bash_profile file of the Oracle user.
vi /home/oracle/.bash_profile
GG_HOME = /u01/app/oracle/product/11.2.0/ goldengate; export GG_HOME
PATH = $ ORACLE_HOME / bin: $ GG_HO to: $ ORACLE_HOME / opatch: $ PATH: $ BASE_PATH; export PATH
LD_LIBRARY_PATH = $ ORACLE_HOME / lib: / lib: / usr / lib: $ ORACLE_HOME / lib: $ GG_HO me; export LD_LIBRARY_PATH

5. We start the installation of the extracted file by running ‘runInstaller dosy.

6. After the process, we go to the directory where Golden Gate installation takes place with the following command and run our program from here.
cd $ gg_home
the ./ggsc

7. After opening the Golden Gate promp, it is necessary to explode the subdirectories. To do this, run the following command.
GGSCI (asm.localdomain) 1> CREATE SUBDIRS

Creatingsubdirectoriesundercurrentdirectory /u01/app/oracle/product/11.2.0/goldengate/goldengate

Parameterfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirprm: created
Report files /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirrpt: created
Checkpoint files /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirchk: created
Processstatusfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirpcs: created
SQL scriptfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirsql: created
Database definitionsfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirdef: created
Extract datafiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirdat: created
Temporaryfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirtmp: created
Stdoutfiles /u01/app/oracle/product/11.2.0/goldengate/goldengate/dirout: created

8. The following sql script should then be run with DBA privileges for both databases.
create tablespacegoldengatedatafile ‘gg1.dbf’ size 256m autoextend on next 10m maxsizeunlimited;

commit;
createusergoldengateidentifiedbygoldengatedefaulttablespacegoldengat A;
grantconnect, resourcetogoldengat A;
grantselectanydictionary, selectanytabletogoldengate;
grantcreatetabletogoldengat A;
grantflashbackanytabletogoldengat A;
grantexecute on dbms_flashbacktogoldengate;
grantexecute on utl_filetogoldengate;
grantalteranytabletogoldengat A;
commit;
alterdatabaseaddsupplementallogdata (all) columns;
commit;

9. The database should be placed in ‘Archivelog’ mode.
selectlog_modefromv $ database; – indicates the log mode of the database.
shutdownimmediat A;
startupmount;
alterdatabasearchievelog;
alterdatabaseop that;

10. When Golden Gate application is run again, it is necessary to login to the database as below.
GGSCI (asm.localdomain) 1> DBLOGIN USERID goldengate, PASSWORD goldengate;

11. If the table-based replication is to be performed, the following command should be entered here.
ADD TRANDATA CEA.

12. For Oracle 10g databases, the trash must be closed.
SQL> ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

13. Following Golden Gate installation, the following scripts should be run in order. This must be in the GG_HOME directory. Schema name is also asked when running scripts. The corresponding schema goldengate schema.
cd $ gg_home
SQL> @ marker_setup.sql;
SQL> @ ddl_setup.sql;
SQL> @ role_setup.sql;
SQL> grant GGS_GGSUSER_ROLE togoldengate;
SQL> @ ddl_enable.sql;

14 – It is necessary to create or identify users to be replicated in both databases. (In the document, the source user is specified for the source database and the target user is specified for the target database.)

Starting the Golden Gate Replication Process

If all the above adjustments are completed correctly, the following processes should be started and maintained.
For replication procedures, the following steps should be followed in order.

Source Database:

1. Go to the Golden Gate home directory and then open promp. To do this, the following commands must be executed.
cd $ gg_home
the ./ggsc

2. A parameter must be entered to configure the Manager thread. Here it is enough to enter the port information.
— **** This command opens the file to be entered with the vi editor.
GGSCI (asm.localdomain) 1> editparamsmgr

— **** Port information should be entered on the opened editor as follows.
PORT 7809

— **** Save and exit the editor.

3. The Manager process thread starts.
GGSCI (asm.localdomain) 1> start manager

 4. We check that the Manager has started.
GGSCI (asm.localdomain) 2> infoall

5. We need to adjust the Extract thread. We should only do this on the source database.

GGSCI (asm.localdomain) 4> add extract ext1, tranlog, beginnow GGSCI (asm.localdomain) 5> ADD EXTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/ex, EXTRACT EXT1 GGSCI (asm.localdomain) 6> ADD RMTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt, EXTRACT EXT1

6. As a result of the process, we created the extract process thread. Then it is necessary to enter the parameter settings of this part.
— *** The editor must be opened to enter the parameter settings.
GGSCI (asm.localdomain) 7> editparams ext1

7. The following parameters must be entered in the editor screen. SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db”) SETENV (ORACLE_SID = “can”) EXTRACT ext1 USERID goldengate, PASSWORD goldengate EXTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/ex RMTHOST asm2, MGRPORT 7809 ddlincludemappedobjname source. *; RMTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt TABLE source. *;

8. We check the status of Manager and extract process parts.
GGSCI (asm.localdomain) 8> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:08:51

9. The authorization parameter related to Golden Gate in our database should also be edited and its users should be authorized. To do this, the following procedure should be performed.
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE = BOTH;

Target Database:
We have to perform the above 1, 2, 3, 4 steps again. Then we have to follow the instructions here.
5. Here we have to make a checkpoint table in the database and make the adjustments required by this table with the following commands.

Open the parameter file.
GGSCI (asm2.localdomain) 1> editparams ./GLOBAL

We enter the following values.
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.checkpoint

7. We have database login process to our Golden Gate service.
GGSCI (asm2.localdomain) 2> dbloginuseridgoldengate
Password:
Successfullyloggedintodatabas to.

8. We create the following table in our database. We have to do this through the Golden Gate interface.
GGSCI (asm2.localdomain) 3> addcheckpointtablegoldengate.checkpoint
Successfullycreated checkpoint tablegoldengate.checkpoint.

9. We create the Replicat process.
GGSCI (asm2.localdomain) 4> add replicat rep1, exttrail /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt,checkpointtablegoldengate.checkpoint

10. Configure the resulting replicat service.
GGSCI (asm2.localdomain) 5> editparams rep1

In the VI Editor screen that opens, we enter the following parameters. SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
SETENV (ORACLE_SID = "can")
REPLICAT rep1
ASSUMETARGETDEFS useridgoldengate, passwordgoldengate discardfile /u01/app/oracle/product/11.2.0/goldengate/discard/rep1.txt, append, megabytes 10
DDL map source. *, target target. *;

Starting Services

In order to start the services, the extract process must be started first. For this
GGSCI (asm.localdomain) 1> start extract ext1
Sending START requesttoMANAGER …

EXTRACT EXT1 starting

GGSCI (asm.localdomain) 2> infoall

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:01

After this process, we must ensure that replicat processes are started on our target server.<br>
GGSCI (asm2.localdomain) 1> start replicat rep1

Sending START requesttoMANAGER …
REPLICAT REP1 starting

GGSCI (asm2.localdomain) 2> infoall

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06

Replication process will be started as a result of the operations performed. We must use the reporting service of Golden Gate to provide the controls. To do this, the following command will suffice.

On the source server
GGSCI (asm.localdomain) 7> viewreport ext1
On the target server
GGSCI (asm2.localdomain) 7> viewreport rep1