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

1. Environment introduction

Project

Source end

Target end

Ip

192.168.12.23

192.168.22.24

Oracle sid

Orcl

Orcl

Goldengate user

Oracle

Oracle

Ogg version

11.2.1.0.1

11.2.1.0.1

Operating system version

Linux

Linux

2. Ogg basic environment configuration 2.1 to create ogg operating system users

Both servers need to create an ogg operating system user (this step is optional).

Useradd-u 1003-g oinstall-G dba ogg1 passwd ogg

2.2 modify OGG operating system user environment variables

PATH=$PATH:$HOME/bin

Export PATH

Umask 022

Export ORACLE_HOSTNAME=segment2

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_SID=orcl

Export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

Export TNS_ADMIN=$ORACLE_HOME/network/admin

ExportPATH=.:$ {PATH}: $HOME/bin:$ORACLE_HOME/bin:$ORACLE_BASE/ogg

ExportPATH=$ {PATH}: / usr/bin:/bin:/usr/bin/X11:/usr/local/bin

ExportLD_LIBRARY_PATH=$ {LD_LIBRARY_PATH}: $ORACLE_HOME/lib

ExportLD_LIBRARY_PATH=$ {LD_LIBRARY_PATH}: $ORACLE_HOME/oracm/lib

ExportLD_LIBRARY_PATH=$ {LD_LIBRARY_PATH}: / lib:/usr/lib:/usr/local/lib

ExportCLASSPATH=$ {CLASSPATH}: $ORACLE_HOME/JRE

ExportCLASSPATH=$ {CLASSPATH}: $ORACLE_HOME/JRE/lib

Export CLASSPATH=$ {CLASSPATH}: $ORACLE_HOME/jlib

ExportCLASSPATH=$ {CLASSPATH}: $ORACLE_HOME/rdbms/jlib

ExportCLASSPATH=$ {CLASSPATH}: $ORACLE_HOME/network/jlib

ExportLIBPATH=$ {CLASSPATH}: $ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

Export ORACLE_OWNER=oracle

Export SPFILE_PATH=$ORACLE_HOME/dbs

Export ORA_NLS10=$ORACLE_HOME/nls/data

ExportOGG_HOME=$ORACLE_BASE/ogg

2.3 create an OGG installation directory

Mkdir / ogg chown-Rogg:oinstall / ogg

2.4 upload OGG installation media

Upload the OGG installation file to the home directory of the OGG operating system user on both servers (default is / home/ogg).

Fbo_ggs_Linux_x64_ora11g_64bit.tar

Unpack fbo_ggs_Linux_x64_ora11g_64bit.tar to the / ogg directory (operations are required on both the source and destination sides)

Tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar-C / ogg

2.5 Login OGG test

$. / ggsci

Extract the post-installation directory

[oracle@sourceogg] $ls-l | grep ^ d

Drwxr-x---2 oracle oinstall 4096 Apr 23 2012 cfg

Drwxr-x---2 oracle oinstall 4096 Apr 23 2012 dirjar

Drwxr-x---2 oracle oinstall 4096 Apr 23 2012 dirprm

Drwxr-x---7 oracle oinstall 4096 Apr 23 2012 UserExitExamples

GGSCI (source) 1 > create subdirs

Creatingsubdirectories under current directory / u01/app/oracle/ogg

Parameterfiles / u01/app/oracle/ogg/dirprm: already exists

Reportfiles / u01/app/oracle/ogg/dirrpt: created

Checkpointfiles / u01/app/oracle/ogg/dirchk: created

Processstatus files / u01/app/oracle/ogg/dirpcs: created

SQLscript files / u01/app/oracle/ogg/dirsql: created

Databasedefinitions files / u01/app/oracle/ogg/dirdef: created

Extractdata files / u01/app/oracle/ogg/dirdat: created

Temporaryfiles / u01/app/oracle/ogg/dirtmp: created

Stdoutfiles / u01/app/oracle/ogg/dirout: created

File path

Description

/ u01/app/oracle/ogg/dirprm

Store the configuration information of each OGG parameter

/ u01/app/oracle/ogg/dirrpt

Store process report files

/ u01/app/oracle/ogg/dirchk

Store checkpoint files

/ u01/app/oracle/ogg/dirpcs

Store process status files

/ u01/app/oracle/ogg/dirsql

Store SQL script files

/ u01/app/oracle/ogg/dirdef

Store the data definitions generated by the DEFGEN tool

2.6 create OGG tablespaces

ORACLE recommends using a separate tablespace to store OGG data, with a tablespace size of 50m, but it is best to set the automatic extension of the data file.

SQL > create tablespace ogg datafile'/u01/app/oracle/oradata/orcl/ogg01.dbf'size 50m autoextend on

2.7Creating OGG users

SQL > create user ogg identified by ogg default tablespace ogg

2.8authorizing OGG users

OGG users need the following permissions, and some people directly give OGG users DBA permissions to save trouble.

SQL > grant CONNECT, RESOURCE to ogg

SQL > grant SELECT ANY DICTIONARY, SELECT ANY TABLEto ogg

SQL > grant ALTER ANY TABLE to ogg

SQL > grant FLASHBACK ANY TABLE to ogg

SQL > grant EXECUTE on DBMS_FLASHBACK to ogg

SQL > grant insert any table to ogg

SQL > grant update any table to ogg

SQL > grant delete any table to ogg

2.9 additional logs and force log for open source databases

SQL > selectNAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database

SQL > alter database force logging

SQL > alter database add SUPPLEMENTAL log data

SQL > select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MINfrom v$database

2.10 run OGG to support DDL script

If you want OGG to support DDL operation, you need to run several additional scripts. These scripts are brought by OGG instead of ORACLE, and can be found in the installation directory of OGG. If you log in to the database in the installation directory of OGG, you can directly @ add the name of the script, which needs to be run on both the source side and the target side.

As follows:

Sqlplus / as sysdba

GRANT CREATE TABLE,CREATE SEQUENCE TO OGG

After the permission is configured. To re-execute

SQL > @ ddl_setup

SQL > @ role_setup

SQL > grantGGS_GGSUSER_ROLE to ogg

SQL > @ ddl_enable

Principle of 3.OGG configuration 3.1OGG and data Manufacturing

Create test data

Source database

@ demo_ora_create.sql

@ demo_ora_insert.sql

Target data

@ demo_ora_create.sql

3.2Table-level TRANDATA is added at the source end

Adding table-level trandata can be understood as which users need to synchronize which tables with the target library. In fact, table-level is also added.

Supplemental log, but this will not take effect until the minimal supplemental log is opened above.

GGSCI (oggsource) 2 > dbloginuserid ogg,password ogg

GGSCI (dbdream) 3 > add trandata hr.tcustmer

GGSCI (dbdream) 4 > add trandata scott.dept_ogg

After you successfully add a table-level TRANDATA, you can use the INFO command to see which tables have been added with TRANDATA.

INFO TRANDATA scott.*

3.4 configuration of MGR management processes on the target and source side

GGSCI (stream) 2 > EDITPARAMS MGR add the following two lines

PORT 7809

PURGEOLDEXTRACTS / ogg/dirdat,USECHECKPOINTS

Parameter description:

PORT 7809:OGG management process monitoring port.

PURGEOLDEXTRACTS: clear unwanted trail files.

/ ogg/dirdat:trail file storage location.

USECHECKPOINTS: use checkpoint queues.

Start mgr

GGSCI (oggtarget) 4 > info mgr

File storage path $OGG_HOME/dirprm

3.5 configure the initialization data process (this step is not necessary and can be done in other ways)

Because when creating the test table, the test table on the source side has data, while the test table on the target side has only structure and no data, so it is necessary to initialize the data on the target side. The so-called initialization means that the data on the target side is exactly the same as the data on the source side at this point in time, so the initialization work does not necessarily need to use OGG, but can also use EXP, EXPDP, SQLLOAD and other tools.

This article mainly introduces how to use OGG for data initialization.

Let's configure the capture process EINI_1 on the source side.

GGSCI (dbdream) 11 > ADD EXTRACT EINI_1, SOURCEISTABLE- source plus synchronous process

GGSCI (dbdream) 12 > INFO EXTRACT *, TASKS EXTRACT

GGSCI (dbdream) 13 > EDIT PARAMS EINI_1

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Setenv (ORACLE_SID=dbdream)

Userid ogg, passwordogg

RMTHOST 192.168.12.203, MGRPORT 7839

RMTTASK REPLICAT, GROUP RINI_1- destination name must correspond

TABLE SCOTT.EMP_OGG

TABLE SCOTT.DEPT_OGG

EXTRACT EINI_1: indicates that this is an EXTRACT process, and the name is EINI_1 SETENV:

The environment variable must be set the same as the database character set, otherwise the code may be garbled

USERID: database OGG user PASSWORD: password of database user OGG

RMTHOST: destination address. If resolution has been set in the / etc/hosts file, you can write the hostname MGRPORT:

The port RMTTASK REPLICAT on which the target MGR management process listens:

The group and name of the target REPLICAT application process

TABLE: the name of the table to initialize the data on the source side

B. Configure the target REPLICAT process

-pay special attention to the name configured on the source side

Add synchronization process to the target side of add replicatRINI_1,specialrun-

EDIT PARAMS RINI_1

-- add the following

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE / u01/app/oracle/ogg/dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*

REPLICAT RINI_1: indicates that this is a REPLICAT application process, and its name is RINI_1

SETENV: language variable, same as capture process EINI_1

ASSUMETARGETDEFS: tell OGG that the structure of the table that needs to be synchronized on the destination side and source side is exactly the same, and there is no need for OGG to check the structure of the table, including table name, field name, field type, field length, etc. If the structure of the synchronized table on the destination side and the source side is different, you need to use the SOURCEDEFS parameter.

USERID, PASSWORD: introduction to EINI_1 parameters of the same capture process

DISCARDFILE: location and naming rules for error messages

MAP: the name of the table captured by the source side

TARGET: the name of the synchronized table on the destination side, which may not be in the same SCHEMA

C, initialization data-source side

After configuring the destination application process RINI_1, you can start the source capture process to capture data, while the destination application process RINI_1 does not need to be started manually, that is, the destination RINI_1 process does not need to be managed.

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH

GGSCI (oggsource asogg@dbdream) 17 > START EXTRACT EINI_1

GGSCI (oggsource as ogg@dbdream) 17 > VIEW REPORT EINI_1

You can see that the initialization is correct. After initialization, the initialization process mentioned above (EINI_1, RINI_1) stops automatically, because initialization of data is usually done only once. You can view the status of the process through the INFO command.

INFO EXTRACT EINI_1- source side

INFO REPLICAT RINI_1- destination side

3.6 checkpoints of OGG configured on the source and destination sides

This step is not necessary, but in order to allow OGG network outages, server outages, power outages, etc., to resume transmission at the correct breakpoint, ORACLE recommends configuring the checkpoint queue of OGG.

GGSCI (stream) 2 > EDIT PARAMS. / GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

This tells OGG that the checkpoint is stored in the GGSCHKPT table under the OGG user, but you also need to use the OGG user to log in to the database and create the checkpoint table. At this point, you need to log out of OGG and log in again, otherwise you may encounter the following error.

[ogg@oggsource ogg] $. / ggsci

GGSCI (oggsource) 1 > dblogin userid ogg,password ogg

GGSCI (oggsource as ogg@dbdream) 2 > ADD CHECKPOINTTABLE

[ogg@dbdream ogg] $sqlplus ogg/ogg

No checkpoint table specified. Using GLOBALS specification (ogg.ggschkpt)...

Successfully created checkpoint table ogg.ggschkpt.

At this point, log in to the database with OGG, and you can see the checkpoint table created by OGG.

SQL > select * from tab

3.7 Source-side configuration capture process

$edit param ext1

EXTRACT ext1

SETENV (ORACLE_HOME= "/ oracle/app/product/11.2/db")

SETENV (ORACLE_SID= "orcl")

SETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK")

USERID ogg password ogg

EXTTRAIL. / dirdat/ext1/sd

Gettruncates

DDL INCLUDE MAPPED, OBJTYPE 'TABLE' &

INCLUDE MAPPED OBJTYPE 'INDEX'

Table scott.*

Add extract ext1 tranlog, begin now

ADD EXTTRAIL. / dirdat/ext1/sd EXTRACT ext1, MEGABYTES 100-Note is the relative path

Start ext1

ADD EXTRACT EORA_2, TRANLOG, BEGIN NOW

ADD EXTTRAIL / u01/app/oracle/ogg/dirdat/zz,EXTRACT EORA_2, MEGABYTES 5

3.7 configure the PUMP process on the source side

Add the PUMP process PORA_1 to OGG and specify the local TRAIL file.

Edit pram dp1

EXTRACT dp1

PASSTHRU

DYNAMICRESOLUTION

RMTHOST 192.168.12.24, MGRPORT 7809

RMTTRAIL. / dirdat/ext2/sd

DISCARDFILE./dirrpt/dp2.dsc,APPEND,MEGABYTES 100

DISCARDROLLOVER AT 6:00

REPORTROLLOVER AT 6:00

REPORTCOUNT EVERY 1 HOURS,RATE

TABLE scott.*

ADD EXTRACT dp1, EXTTRAILSOURCE./dirdat/ext1/sd

INFO EXTRACT dp2

ADD RMTTRAIL. / dirdat/ext1/sd, EXTRACT dp1, MEGABYTES100- pay attention to the relative path

ADD EXTRACT PORA_1, EXTTRAILSOURCE / u01/app/oracle/ogg/dirdat/pa

Specify the name for the PUMP process PORA_1 to save the local TRAIL file to the destination TRAIL file after it is transferred to the destination side.

ADD RMTTRAIL / u01/app/oracle/ogg/dirdat/pa, EXTRACTPORA_1, MEGABYTES 5

START EXTRACT dp1

GGSCI (oggsource as ogg@dbdream) 36 > INFO EXTRACT dp1

Pay attention to the existence of paths and the contents related to environmental variables.

3.8 the target side configures the synchronization process RORA_1

Edit param rep1

REPLICAT rep1

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

SETENV (ORACLE_SID = "orcl")

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

USERID ogg, PASSWORD ogg

-- HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE. / dirrpt/rep2.dsc, append,megabytes 10

-SOURCEDEFS./dirdef/def.def

DDL INCLUDE MAPPED, OBJTYPE 'TABLE' &

INCLUDE MAPPED OBJTYPE 'INDEX'

MAP SCOTT.EMP_OGG, TARGET SCOTT.EMP_OGG

MAP SCOTT.DEPT_OGG, TARGET SCOTT.DEPT_OGG

ADD REPLICAT rep1, EXTTRAIL. / dirdat/ext1/sd

START REPLICAT rep1

Pay attention to parameters

ERROR OGG-00423 Could not find definitionfor source.person

In the Replicat param Please tryASSUMETARGETDEFS, if source and target have same table structure..

If both the structures are different, generate the source definition and use SOURCEDEFS in Replicate Param.

Handcollisions is annotated, which means that when delete and update data is missing on the target side, the target side cannot handle it automatically.

Validate data

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.