Shulou(Shulou.com)06/01 Report--

GoldenGate, as the core strategic product developed by Oracle, is widely used in high availability, disaster recovery,

Data extraction and transformation is playing a more and more important role. There is too much talk about what goldengate can do.

High availability, disaster recovery, Real-Time data synchronization. If you have done BI, you must know the tools of ETL (datastage, infomatica, etc.)

Goldengate can also be done, and there are two key factors. First, goldengate supports heterogeneous databases, almost including

All the mainstream databases, which are they?

Let's have a look here, http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html.

Mainstream DB2, mysql, sql server and sysbase all support it.

Second, it is the core strategic product that oracle develops vigorously.

Goldengate synchronization captures logs on the source side, and extract log information is sent to the destination side, where the replicat log application process is implemented.

Apply transactions to achieve data synchronization. At the same time, the extract extraction process on the source side can also optionally configure extracted trail disk files

The replicat application process on the target side can also selectively configure the trail file of the extracted log to prevent log loss.

I don't say much about things like pre-sales.

This paper accurately introduces the goldengate installation and configuration of oracle by oracle in 64-bit linux environment (no distinction between 10g and 11g).

This paper also configures the data initialization process in batch mode and the real-time data synchronization process in online mode, and installs the configuration according to this article.

It allows you to quickly build a goldengate environment without any problems.

1. Environmental introduction and preparation:

Two centos with oracle 10g software (this installation configuration is also applicable to oracle 11g).

Source: IP:192.168.1.201 ORACLE_SID=ggsource

Destination side: IP:192.168.1.202 ORACLE_SID=ggtarget

1. Configure environment variables:

Source:

ORACLE_SID=ggsource

ORACLE_BASE=/u01

ORACLE_HOME=$ORACLE_BASE/oracle

PATH=$ORACLE_HOME/bin:$ORACLE_BASE/gg:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/gg11:$LD_LIBRARY_PATH

Export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

Target:

ORACLE_SID=ggtarget

ORACLE_BASE=/u01

ORACLE_HOME=$ORACLE_BASE/oracle

PATH=$ORACLE_HOME/bin:$ORACLE_BASE/gg:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/gg11:$LD_LIBRARY_PATH

Export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

Nothing else, search Lujin added goldengate installation directory $ORACLE_BASE/gg and LD_LIBRARY_PATH

(later, when I installed goldengate, my goldengate product was unzipped and installed in the $ORACLE_BASE/gg directory)

LD_LIBRARY_PATH must be configured, otherwise the following error will be reported in create subdirs:

[oracle@mycentos02 ~] $ggsci

Ggsci: error while loading shared libraries: libnnz10.so: cannot open shared object file: No such file or directory

Download and install 2.goldengate (need to be installed on both source and target machines):

Where can I download it? Www.oracle.com- > middleware- > goldengate

How to install it? The installation of goldengate is frighteningly simple, and decompression is installation. Extract the downloaded 64-bit gg for oracle 10g to the / u01/gg directory

/ u01/gg and install directory for my goldengate. After decompressing, cd to the / u01/gg directory, run ggsci, and ggsci > create subdirs under ggsci

Create a directory corresponding to goldengate, even if the installation is complete.

GGSCI (mycentos) 1 > create subdirs

Creating subdirectories under current directory / u01/gg

Parameter files / u01/gg/dirprm: already exists

Report files / u01/gg/dirrpt: created

Checkpoint files / u01/gg/dirchk: created

Process status files / u01/gg/dirpcs: created

SQL script files / u01/gg/dirsql: created

Database definitions files / u01/gg/dirdef: created

Extract data files / u01/gg/dirdat: created

Temporary files / u01/gg/dirtmp: created

Stdout files / u01/gg/dirout: created

GGSCI (mycentos) 2 >

We see that some directories have been established under the installation directory of gg, which are the directories where the relevant information is stored during the run of goldengate. For example,

/ u01/gg/dirrpt is the report directory of report, or goldengate, and / u01/gg/dirprm is the parameter directory, that is, goldengate.

The parameter configuration directory of the process, and so on, the rest will not be discussed one by one.

At this point, the goldengate installation is complete.

3. Add supplemental log support on the source side:

SQL > alter database add supplemental log data

Database altered.

SQL > alter system switch logfile

System altered.

SQL > select supplemental_log_data_min from v$database

SUPPLEME

-

YES

SQL >

4. Create gguser and its corresponding tablespaces on both the source side and the target side

SQL > create user gguser identified by oracle default tablespace users quota unlimited on users

User created.

SQL > grant dba to gguser

Grant succeeded.

SQL >

5. Create a test table under the gguser user of the source-side and target-side databases, and insert the initial data on the source side

Source side:

SQL > create table ggtable (id number,name varchar2 (50))

Table created.

SQL >

SQL > insert into ggtable values (1judicial hunter 01')

1 row created.

SQL > insert into ggtable values (2pure hunter 02')

1 row created.

SQL > insert into ggtable values (3 famous hunter 03')

1 row created.

SQL > commit

Commit complete.

SQL >

Target side:

SQL > create table ggtable (id number,name varchar2 (50))

Table created.

SQL >

6. Configure the mgr process on the source and target sides and start:

Manager is the management process of goldengate, which must be started when goldengate is running, so both source and target should be configured.

GGSCI (mycentos) 2 > edit params mgr

GGSCI (mycentos) 3 > start mgr

Manager started.

GGSCI (mycentos) 4 > info mgr

Manager is running (IP port mycentos.7809).

GGSCI (mycentos) 5 >

7. Configure the tables to be synchronized by goldengate:

GGSCI (mycentos) 1 > dblogin userid gguser,password oracle

Successfully logged into database.

GGSCI (mycentos) 2 > add trandata gguser.ggtable

2014-07-20 15:37:47 WARNING OGG-00869 No unique key is defined for table 'GGTABLE'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table GGUSER.GGTABLE.

GGSCI (mycentos) 3 > info trandata gguser.*

Logging of supplemental redo log data is enabled for table GGUSER.GGTABLE.

Columns supplementally logged for table GGUSER.GGTABLE: ID, NAME.

GGSCI (mycentos) 4 >

Here is a table without unique key warning, we ignore, you can also create a unique key for the table.

8. Complete the initial load:

Goldengate has two operation modes: batch and online. Before the online mode can synchronize the source data, you need to

Initialize the data on the target side, of course, if your source and before goldengate starts running synchronization

The data on the target side is consistent, and this step can be omitted. In addition, even if you want to initialize the data on both sides before goldengate runs,

You can also use any other means of data replication and synchronization, such as exp/expdp, and so on. Here is the initial that introduces goldengate

Load process, and I am using direct load mode here. I will not list other patterns in detail. I am interested in looking up the relevant goldengate.

Documentation:

Source side:

Configure the extract process and configure the process parameters:

GGSCI (mycentos) 2 > add extract einikk,sourceistable

EXTRACT added.

GGSCI (mycentos) 3 >

GGSCI (mycentos) 3 > edit params einikk

Parameter file:

EXTRACT EINIKK

USERID gguser, PASSWORD "oracle"

RMTHOST 192.168.1.202, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINIKK

TABLE gguser.ggtable

Where einikk is the extracted group name, and sourceistable represents a task of initial load.

Destination side:

Configure the replicat process rinikk and edit the parameters of the process:

Add replicat rinikk,specialrun

Edit params rinikk

Parameter file:

REPLICAT RINIKK

ASSUMETARGETDEFS

USERID gguser, PASSWORD oracle

DISCARDFILE. / dirrpt/RINIKK.dsc, PURGE

MAP gguser.*, TARGET gguser.*

Start initial load:

Start the extraction process einikk, and the process of initial load:

GGSCI (mycentos) 5 > start extract einikk

Sending START request to MANAGER...

EXTRACT EINIKK starting

When we look at the einikk report by view report einikk, we can see that the initialization of the source data has been completed.

That is, the first three pieces of data are inserted on the target side:

Processing table GGUSER.GGTABLE

*

* Run Time Statistics * *

*

Report at 2014-07-20 17:01:13 (activity since 2014-07-20 17:01:03)

Output to RINIKK:

From Table GGUSER.GGTABLE:

# inserts: 3

# updates: 0

# deletes: 0

# discards: 0

At this point, the initial load process is complete.

9. Configure data synchronization for online mode:

This process is to configure the extract process on the source side and the replicat process on the target side.

Configure the extraction process eorakk on the source side, and configure the process parameters:

GGSCI (mycentos) 7 > add extract eorakk,tranlog,begin now,threads 1

EXTRACT added.

GGSCI (mycentos) 8 > info extract *

EXTRACT EORAKK Initialized 2014-07-20 17:09 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint Oracle Redo Logs

2014-07-20 17:09:47 Thread 1, Seqno 0, RBA 0

SCN 0.0 (0)

GGSCI (mycentos) 9 >

GGSCI (mycentos) 9 > edit params eorakk

EXTRACT EORAKK

USERID system, PASSWORD oracle

RMTHOST 192.168.1.202, MGRPORT 7809

RMTTRAIL. / dirdat/kk

TABLE gguser.ggtable

Add the trail file on the target side on the source side:

Add rmttrail. / dirdat/kk,extract eorakk

GGSCI (mycentos) 10 > add rmttrail. / dirdat/kk,extract eorakk

RMTTRAIL added.

GGSCI (mycentos) 11 > info rmttrail *

Extract Trail:. / dirdat/kk

Extract: EORAKK

Seqno: 0

RBA: 0

File Size: 100M

GGSCI (mycentos) 12 >

Then, start the extraction process:

GGSCI (mycentos) 12 > start extract eorakk

Sending START request to MANAGER...

EXTRACT EORAKK starting

GGSCI (mycentos) 13 > info extract eorakk

EXTRACT EORAKK Last Started 2014-07-20 17:18 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:09 ago)

Log Read Checkpoint Oracle Redo Logs

2014-07-20 17:18:49 Thread 1, Seqno 4, RBA 2489344

SCN 0.578967 (578967)

GGSCI (mycentos) 14 >

Configure the replicat process on the destination side and start:

First, configure the checkpoint table:

Edit params. / GLOBALS

GGSCI (mycentos02) 1 > edit params. / GLoBALS

CHECKPOINTTABLE system.ggchkptable

Create a ggchkptable:

GGSCI (mycentos02) 1 > dblogin userid system,password oracle

Add checkpointtable

In this way, we have set up checkpointtable:system.ggchkptable, and we can log in to the system user on the target side.

See the table:

[oracle@mycentos02 ~] $sqlplus system/oracle

SQL*Plus: Release 10.2.0.1.0-Production on Sun Jul 20 05:27:58 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

SQL > desc ggchkptable

Name Null? Type

-

GROUP_NAME NOT NULL VARCHAR2 (8)

GROUP_KEY NOT NULL NUMBER (19)

SEQNO NUMBER (10)

RBA NOT NULL NUMBER (19)

AUDIT_TS VARCHAR2 (29)

CREATE_TS NOT NULL DATE

LAST_UPDATE_TS NOT NULL DATE

CURRENT_DIR NOT NULL VARCHAR2 (255)

LOG_CSN VARCHAR2 (129)

LOG_XID VARCHAR2 (129)

LOG_CMPLT_CSN VARCHAR2 (129)

LOG_CMPLT_XIDS VARCHAR2 (2000)

VERSION NUMBER (3)

SQL >

Configure the replicat process rorakk and start:

GGSCI (mycentos02) 1 > add replicat rorakk,exttrail. / dirdat/kk

REPLICAT added.

GGSCI (mycentos02) 2 >

Parameters:

Edit params rorakk

REPLICAT RINIKK

ASSUMETARGETDEFS

USERID gguser, PASSWORD oracle

DISCARDFILE. / dirrpt/RINIKK.dsc, PURGE

MAP gguser.*, TARGET gguser.*

Start:

Start replicat rorakk

In this way, we completed the process of goldengate data synchronization on both the source side and the target side, and then we completed the

When inserting data into the gguser.ggtable table on the source side, you can see that data synchronization can be achieved in the database on the target side.

How fast is synchronization? oracle says sub-second.

Supplementary note:

The command of goldengate can be viewed by typing the help process under ggsci. The process has stop, running and ABENDED status.

When it is running normally, it is the running state. During the configuration process, I gave it to the parameter file of the rorakk process.

USERID gguser, PASSWORD oracle just started to write the wrong password of gguser, and, after start, the process is in ABENDED, of course, it cannot synchronize the data.

By looking at the report of the process

View report rorakk:

2014-07-20 05:43:43 ERROR OGG-00664 OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denie

d)。

The problem was discovered.

In addition, in the mapping table here, MAP gguser.*, TARGET gguser.*; I also forgot to write TARGET, and I can also check the cause of the error in the report of the process:

2014-07-20 06:02:43 ERROR OGG-00212 Invalid option for MAP: gguser.ggtable.

After correcting the above two errors, I start rorakk again, and the data is synchronized immediately.

In short, when you encounter errors, you should learn to view relevant logs and reports. All commands can be viewed by help.

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.