Oracle2oracle installed by OGG 01/16 Update SLTechnology News&Howtos

Oracle2oracle installed by OGG

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Both servers need to create an ogg operating system user (this step is optional or can be installed by an ORACLE user).

Useradd-u 1003-g oinstall-G dba ogg

Passwd ogg

Su-ogg

Vi .bash _ profile

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Export ORACLE_SID=XXL1

Export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib

Export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH

Export GGHOLE=/ogg

Source .bash _ profil takes effect

Both servers need to do the same thing.

Mkdir / ogg

Chown-R ogg:oinstall / ogg

Upload the 11g installation package fbo_ggs_Linux_x64_ora11g_64bit.tar of ogg to / ogg

Decompress with ogg user

Tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

. / ggsci test

[ogg@oracle2 ogg] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (oracle2) 1 >

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.

Select file_name from dba_data_files; looks at the table space storage directory

Create tablespace ogg datafile'/ u01 size autoextend on

Create an OGG user

Create user ogg identified by ogg default tablespace ogg

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

Grant CONNECT, RESOURCE 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 update any table to ogg

Grant delete any table to ogg

OGG users need different permissions for different needs. The above permissions can basically support data replication in most cases. For example, if the above permissions are insufficient, you need to authorize them according to the actual needs.

Open additional logs and force log for the database

SQL > select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database

NAME OPEN_MODE FOR SUPPLEME

DBDREAM READ WRITE NO NO

The above shows that the additional log and force log of the database are not open, use the following command to open it.

SQL > alter database force logging

Database altered.

SQL > alter database add SUPPLEMENTAL log data

Database altered.

SQL > select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database

NAME OPEN_MODE FOR SUPPLEME

DBDREAM READ WRITE YES YES

Make sure that the minimum attachment log is turned on, that the pk,uk,fk additional log is on, and that the all columns additional log is closed.

SQL > select SUPPLEMENTAL_LOG_DATA_MIN alphabetical loggers data all e from v$database

SUPPLEMENTAL_LOG SUPPLE SUPPLE SUPPLE SUPPLE

YES NO NO NO NO

SQL > alter database add supplemental log data (primary key,unique,foreign key) columns

Database altered.

SQL > select SUPPLEMENTAL_LOG_DATA_MIN alphabetical loggers data all e from v$database

A B C D E

YES YES YES YES NO

Turn off the Recycle Bin function (DDL) must be turned off

SQL > show parameter recyclebin

NAME TYPE VALUE

Recyclebin string on

Alter system set recyclebin=off scope=spfile;, you need to restart the database to take effect.

Alter system switch logfile

Check the status of the user who is synchronized and make sure it is open

Select username,account_status from dba_users

Run OGG to support sequence

Run OGG support DDL script if you want OGG to support DDL operation, you need to run several additional scripts. These scripts are provided 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: br/ > 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:

@ marker_setup.sql create a DDL tag table schema name: enter ogg

@ ddl_setup.sql creates necessary components such as trigger for DDL statements in the database

(ggsci must be disconnected during execution, otherwise an error will be reported)

@ role_setup.sql establishes a ggs_ggsuser_role role

@ ddl_enable.sql enable ddl capture trigger

If you do not log in to the database in the OGG installation directory, you need to specify the location of the script, for example, my OGG installation directory is / ogg.

Sqlplus / as sysdba@/ogg/marker_setup.sqlbr/ > @ / ogg/marker_setup.sql

Br/ > @ / ogg/role_setup.sql

Create an administrative directory for OGG

Before formally configuring OGG, you first need to create the management directory of OGG, which needs to be created on both the source side and the destination side. Log in to OGG and simply execute the create subdirs command.

Source side:

[ogg@dbdream ogg] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (dbdream) 1 > create subdirs

Creating subdirectories under current directory / ogg

Parameter files / ogg/dirprm: already exists

Report files / ogg/dirrpt: created

Checkpoint files / ogg/dirchk: created

Process status files / ogg/dirpcs: created

SQL script files / ogg/dirsql: created

Database definitions files / ogg/dirdef: created

Extract data files / ogg/dirdat: created

Temporary files / ogg/dirtmp: created

Stdout files / ogg/dirout: created

Destination side:

[ogg@dbdream ogg] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (dbdream) 1 > create subdirs

Creating subdirectories under current directory / ogg

Parameter files / ogg/dirprm: already exists

Report files / ogg/dirrpt: created

Checkpoint files / ogg/dirchk: created

Process status files / ogg/dirpcs: created

SQL script files / ogg/dirsql: created

Database definitions files / ogg/dirdef: created

Extract data files / ogg/dirdat: created

Name and use

Dirprm stores the configuration information of OGG parameters.

Dirrpt stores process report files

Dirchk stores checkpoint files

Dirpcs stores process status files

Dirsql stores SQL script files

Dirdef stores the data definition files generated by the DEFGEN tool

Dirdat stores Trail files, that is, log files captured by the Capture process

Dirtmp when a transaction requires more memory than allocated memory, it is stored in this directory by default

OGG replication proc

What we have done before is just preparatory work, and now we can formally configure OGG. Before configuring OGG, take a look at the replication process of OGG. Like other traditional replication software, OGG captures / mines the log information of ORACLE through the source side.

The destination is reshaped according to the log information sent by the source to realize the data synchronization between the source and the destination.

Only log information after commit is captured by the Capture process, and uncommitted transactions are not captured by OGG.

Set up test users and authorize

Create user lqh identified by lqh

Grant dba to lqh

Conn lqh/lqh

Source

Create table test100

(id number primary key

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Create table test200

(id number primary key

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Create table test300

(id number

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Begin

For i in 1..1000

Loop

If i mod 2 = 0

Then

Commit

End if

End loop

Commit

End

Goal:

Create table test100

(id number primary key

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Create table test200

(id number primary key

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Create table test300

(id number

Name1 varchar2 (20)

Name2 varchar2 (20)

Name3 varchar2 (20))

Add table-level TRANDATA to the source side

Adding table-level trandata can be understood as which users' which tables need to be synchronized to the target library. In fact, it is also adding table-level supplemental log, but this will not take effect until the minimal supplemental log is opened above.

GGSCI (dbdream) 2 > DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

Table-level TRANDATA is added to the source side. We will first use test for some tables.

GGSCI (oracle1) 3 > add trandata lqh.test100

Logging of supplemental redo data enabled for table LQH.TEST100.

GGSCI (oracle1) 4 > add trandata lqh.test200

Logging of supplemental redo data enabled for table LQH.TEST200.

GGSCI (oracle1) 5 > add trandata lqh.test300

2018-04-17 06:13:52 WARNING OGG-00869 No unique key is defined for table 'TEST300'. 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 LQH.TEST300.

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

GGSCI (oracle1) 6 > info trandata lqh.*

Logging of supplemental redo log data is enabled for table LQH.TEST100.

Columns supplementally logged for table LQH.TEST100: ID.

Logging of supplemental redo log data is enabled for table LQH.TEST200.

Columns supplementally logged for table LQH.TEST200: ID.

Logging of supplemental redo log data is enabled for table LQH.TEST300.

Columns supplementally logged for table LQH.TEST300: ID, NAME1, NAME2, NAME3.

Check to see if the lqh.TEST100 table has trandata enabled.

GGSCI (oracle1) 8 > info trandata lqh.TEST100

Logging of supplemental redo log data is enabled for table LQH.TEST100.

Columns supplementally logged for table LQH.TEST100: ID.

Add table-level additional logs on the source side

Set heading off

Set pagesize 120

Dblogin userid goldengate,password * *

Add trandata "user". "Table name"

Info trandata users. *

Configure the MGR management process

Source side:

GGSCI (dbdream) 5 > EDIT PARAMS MGR

Add the following two lines

PORT 7809

AUTOSTART ER

PURGEOLDEXTRACTS. / ogg/dirdat/, USECHECKPOINTS

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

Destination side:

GGSCI (stream) 2 > EDIT PARAMS MGR

Add the following two lines

PORT 7809

AUTOSTART ER

PURGEOLDEXTRACTS. / ogg/dirdat/, USECHECKPOINTS

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

Parameter description and reference:

PORT 7809:-OGG management process monitors the communication port. The production side and the disaster recovery side need to be consistent.

AUTOSTART ER *-automatically start the process

PURGEOLDEXTRACTS:-clear unwanted trail files.

/ ogg/dirdat:-trail file storage location.

USECHECKPOINTS:-use checkpoint queues.

LAGREPORTHOURS 1-check the transmission delay every other hour

LAGINFOMINUTES 30-if the transmission delay exceeds 30 minutes, the error log will be written

LAGCRITICALMINUTES 45-if the transmission delay exceeds 45 minutes, the warning log will be written, both error

PURGEOLDEXTRACTS. / dirdat/, USECHECKPOINTS, MINKEEPDAYS 3

-clear expired tail files and keep them for 3 days

STARTUPVALIDATIONDELAY 5-check delay of 5 seconds at startup

DYNAMICPORTLIST 7840-7914

-used to communicate with goldengate replication process, collector process, ggsci command.

AUTORESTART EXTRACT, RETRIES 5, WAITMINUTES 7

-when the extraction process is interrupted, try to restart automatically, try to start every 7 minutes, try 5 times, basically nothing

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10,FREQUENCYMINUTES 30

-- this parameter is used to maintain the GGS_DDL_ hist table. By default, it clears records that have not been changed in 1 hour. Here, keep records that have not been changed in 7-10 days for GGS_DDL_HIST records.

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

-- the number of records used to maintain GGS_MARKER. The default is that records for more than 1 hour will be deleted without change. The parameter here means that if the record has not changed within 7-10 days, the record will be deleted and checked and executed every 30 minutes.

After you have configured the MGR management process, you can start the MGR management process (both the source side and the target side need to be started).

GGSCI (oracle2) 6 > start mgr

Manager started.

You can check the status of the process through the INFO command (preferably at both ends of each startup).

GGSCI (oracle2) 7 > info mgr

Manager is running (IP port oracle2.7809).

GGSCI (oracle1) 4 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Configure the initialization data process

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 describes how to use OGG for data initialization. Let's configure the capture process lqh on the source side.

GGSCI (oracle1) 8 > add extract lqh, SOURCEISTABLE

EXTRACT added.

View the status of the EINI_1 process.

GGSCI (oracle1) 9 > INFO EXTRACT *, TASKS

EXTRACT lqh Initialized 2018-04-11 01:01 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

Since only the capture process lqh has been added and has not yet been configured and started, the current state is STOPPED.

View the database character set as follows

Select * from nls_database_parameters where parameter='NLS_CHARACTERSET'

Select userenv ('language') from dual

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

Edit the capture process lqh on the source side.

GGSCI (oracle1) 10 > EDIT PARAMS lqh

-add the following

EXTRACT lqh

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

USERID ogg PASSWORD ogg

RMTHOST oracle2 MGRPORT 7809

RMTTASK REPLICAT GROUP xixi

TABLE LQH.TEST100

TABLE LQH.TEST200

Parameter description:

EXTRACT lqh: indicates that this is an EXTRACT process with the name lqh

SETENV: environment variable, which must be set to the same as the database character set, otherwise the code may be garbled

USERID: database OGG user

PASSWORD: the password of the database user OGG

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

MGRPORT: the port on which the target MGR management process listens

RMTTASK REPLICAT: the group and name of the target REPLICAT application process

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

After editing the capture process lqh, you also need to configure the REPLICAT application process on the destination side with the same name as the RMTTASK REPLICAT parameter in the capture process lqh on the source side, that is, you also need to configure RMTTASK REPLICAT lqh on the destination side.

Configure the target side REPLICAT process

Destination side:

GGSCI (oracle1) 8 > add replicat xixi,specialrun

EXTRACT added.

View the status of the lqh process.

GGSCI (oracle2) 7 > info replicat *, task

REPLICAT XIXI Initialized 2018-04-17 06:43 Status STOPPED

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

Log Read Checkpoint Not Available

Task SPECIALRUN

GGSCI (oracle2) 9 > EDIT PARAMS xixi

-add the following

REPLICAT xixi

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

ASSUMETARGETDEFS

USERID ogg PASSWORD ogg

DISCARDFILE. / dirrpt/xixi.dsc,PURGE

MAP lqh. TARGET lqh.

The. / dirrpt/lqh.dsc here must be written this way, or an OGG-01091 error will be reported.

The directory previously written is. / ogg/dirrpt/xixi.ds, and the error is as follows

ERROR OGG-01091 Unable to open file ". / ogg/dirrpt/xixi.dsc" (error 2, No such file or directory).

Parameter description:

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

SETENV: language variable, same as capture process xixi

ASSUMETARGETDEFS: tell OGG that the structure of the table 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, and so on. If the structure of the synchronized table on the destination side and the source side is different, you need to use the SOURCEDEFS parameter. For more information, please see the official OGG documentation.

USERID: database OGG user

PASSWORD: the password of the database user OGG

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 target side, which may not be in the same SCHEMA.

Initialization data

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

GGSCI (oracle1) 11 > START EXTRACT lqh

Sending START request to MANAGER...

EXTRACT LQH starting

GGSCI (oracle1) 17 > view report lqh

2018-04-17 06:45:18 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

EXTRACT lqh

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

USERID ogg PASSWORD *

RMTHOST oracle2 MGRPORT 7809

RMTTASK REPLICAT GROUP xixi

TABLE LQH.TEST100

Using the following key columns for source table LQH.TEST100: ID.

After starting the source-side capture process lqh, under normal circumstances (if there is no problem with the configuration), the data from the source side has been transferred to the destination side. You can check the working status of the source-side capture process lqh through the VIEW command.

If you cannot see the data and do not report an error, you can restart the extraction process again.

If there is data, the synchronization is successful, and the service will stop automatically after the synchronization is completed (the following status is normal)

GGSCI (oracle1) 33 > info extract lqh

EXTRACT LQH Last Started 2018-04-12 23:49 Status ABENDED

Checkpoint Lag Not Available

Log Read Checkpoint Table LQH.TEST100

2018-04-12 23:49:26 Record 1

Task SOURCEISTABLE

If ERROR appears in the last part of the log above, you need to check the ggserr.log log in the directory where OGG is installed, which is equivalent to the database alarm log.

All of the above data is transferred to the target side, but the dml statement cannot be transferred, so we need to configure the capture and transfer process of the data source database.

GGSCI (oracle1) 21 > edit params caplqh

-add the following

-- capture change from oracle1 to oracle2

EXTRACT caplqh

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

USERID ogg, PASSWORD ogg

EXTTRAIL. / dirdat/qh

TABLE lqh.test100

TABLE lqh.test200

Among them, EXTTRAIL refers to the location of the data stored in the extraction trace. Qh is the first letter prefix of the file (this prefix can only be

It's two letters).

Generate the password password for ogguser:

GGSCI (oracle2) 6 > ENCRYPT PASSWORD ogg BLOWFISH ENCRYPTKEY DEFAULT

Using default key...

Encrypted password: AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB

Algorithm used: BLOWFISH

Change the password after configuration and remember to restart the process

Caplqh captures process parameters

EXTRACT

SETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK") sets the character set

USERID goldengate, PASSWORD AACAAAAAAAAAAGAIF, ENCRYPTKEY default username and password

REPORTCOUNT EVERY 30 MINUTES, RATE-report processed statistics every 30 minutes

DISCARDFILE. / dirrpt/exta.dsc, APPEND, MEGABYTES 256-there is an error writing to the secondary file during processing

DISCARDROLLOVER AT 3:00-set the time to switch queues at 3 o'clock every day

WARNLONGTRANS 2h, CHECKINTERVAL 3m

-check major transactions every 3 minutes and report those that are not finished for more than 2 hours

EXTTRAIL. / dirdata/ya, MEGABYTES 256-specify which queue to write to

DYNAMICRESOLUTION-resolve table names dynamically. When there are many tables, there is no need to retrieve the table structure from the database.

DBOPTIONS ALLOWUNUSEDCOLUMN

-this parameter needs to be configured when there is a unused column in the production database table. It is used to prevent the process abend when the extraction process extracts data because the table contains a unused column. Even with this parameter, the extraction process logs a warning to the log file when it extracts the unused column.

FETCHOPTIONSNOUSESNAPSHOT

-the default value is usesnapshot, which means the data is read by database flashback. Nousesnapshot means to read the relevant data directly from the original table.

TRANLOGOPTIONS CONVERTUCS2CLOBS

-CLOB field, a special parameter required when parsing database logs. This parameter has been invalidated since the v11 version

Tranlogoptionsasmusersys@asm, asmpasswordAACAAAAAAAAAAGAIF, ENCRYPTKEY default

TRANLOGOPTIONS altarchivelogdest primary instance rac1 / arch2 altarchivelogdest instance rac2 / arch3

-rac is used to solve the problem of inconsistent archive paths. Multiple archive paths can be configured on an instance to ensure that goldengate can read the archives of each node at the same time. Generally, NFS is used to mount the archives of other nodes of rac.

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 THREADOPTIONS IOLATENCY 1000

-all nodes in the RAC cluster must synchronize the system clock. GoldenGate makes key decisions by comparing the time of the local system with the point in time when the transaction was committed. You can use NTP to vary the system time. The COMPATIBLE parameter settings must also be the same on all nodes. This parameter is no longer used after the Oracle11.2 version.

TRANLOGOPTIONS rawdevice offset 0-use of bare equipment

TRANOPTIONS EXCLUDEUSER goldengate-excluding goldengate users

-- DDL parameter area, all copied objects and shielded objects are reflected on the source side.

DDL &

INCLUDE MAPPED OBJTYPE 'TABLE' &

INCLUDE MAPPED OBJTYPE 'INDEX' &

INCLUDE MAPPED OBJTYPE 'SEQUENCE' &

INCLUDE MAPPED OBJTYPE 'VIEW' &

INCLUDE MAPPED OBJTYPE 'PROCEDURE' &

INCLUDE MAPPED OBJTYPE 'FUNCTION' &

INCLUDE MAPPED OBJTYPE 'PACKAGE' &-Red parameter is the newly added replication content of application-level disaster recovery

EXCLUDE OPTYPE COMMENT

DDLOPTIONS addtrandata REPORT

-- match table/index/sequence/view/procedure/function/package objects for DDL replication. The parameter here is matching replication by object type.

TABLE SCHEMA.;-list of objects to be copied

Sequence schema.;-the sequence object to be copied

GGSCI (oracle1) 22 > add extract caplqh,tranlog,begin now

EXTRACT added.

Add extract ext_app, tranlog, begin now, threads 2

An add extract threads clause is not required for a single instance; for a RAC library with more than two instances, the threads clause indicates the actual number of threads

GGSCI (oracle1) 23 > add exttrail. / dirdat/qh,extract caplqh,megabytes 5

EXTTRAIL added.

It means to add the extract trace file captured by lqh to the. / dirdat directory, with a maximum of 5m.

Add EXTTRAIL. / dirdat/r1, extract ext_app,MEGABYTES 100

GGSCI (oracle1) 10 > start extract caplqh

Sending START request to MANAGER...

EXTRACT CAPLQH starting

GGSCI (oracle1) 18 > info extract caplqh

EXTRACT CAPLQH Last Started 2018-04-17 06:51 Status RUNNING

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

Log Read Checkpoint Oracle Redo Logs

2018-04-17 06:50:59 Seqno 75, RBA 41052160

SCN 0.913524 (913524)

GGSCI (oracle1) 12 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING CAPLQH 00:00:00 00:00:00

Configure the pump process to deliver (transfer trace files)

GGSCI (oracle1) 18 > edit params pumplqh

The contents are as follows:

-- pump file from oracle1 to oracle2

EXTRACT pumplqh

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

PASSTHRU

RMTHOST oracle2 MGRPORT 7809

RMTTRAIL. / dirdat/md

TABLE lqh.test100

TABLE lqh.test200

GGSCI (oracle1) 41 > add extract pumplqh,exttrailsource. / dirdat/qh

EXTRACT added.

The above is consistent with caplqh, and qh naming must be consistent with extraction.

Datapump process parameters

EXTRACT

RMTHOST IP address, MGRPORT 7809, COMPRESS

-the ip address of the destination side, and 7809 is the management port of the destination side. It is required to configure compress compression parameters to save network bandwidth, but it will increase CPU overhead.

PASSTHRU

-the transport process interacts directly with the extraction process, but no longer with the database, reducing the use of database resources

NUMFILES 5000-how many tables can be processed at most

RMTTRAIL. / dirdata/ya-which queue to deliver must be consistent with the extraction

DYNAMICRESOLUTION-dynamic resolution of table names

TABLE schema.;-list of objects to be delivered

Sequence schema.;-the sequence object to be delivered

If you add it incorrectly, the following command deletes it

GGSCI (oracle1) 5 > DELETE extract pumplqh

We send the trace file at the beginning of qh to the target library. Then set the receiving path of the other party, and the file is opened as pu

Boss, maximum 5m.

GGSCI (oracle1) 14 > info extract pumplqh

EXTRACT PUMPLQH Initialized 2018-04-13 01:56 Status STOPPED

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

Log Read Checkpoint File. / dirdat/qh000000

First Record RBA 0

GGSCI (oracle1) 43 > add rmttrail. / dirdat/md,extract pumplqh,megabytes 5

RMTTRAIL added.

The above is consistent with pumplqh.

GGSCI (oracle1) 18 > delete extract pumplqh2

Deleted EXTRACT PUMPLQH1.

If you add it incorrectly, delete it like this.

GGSCI (oracle1) 44 > start extract pumplqh

Sending START request to MANAGER...

EXTRACT PUMPLQH starting

Configure the recovery process of the target library

GGSCI (oracle2) 17 > edit params. / GLOBALS

The GLOBAS must be capitalized. The contents of the file are:

Checkpointtable ogg.ggschkpt

GGSCI (oracle2) 18 > view params. / GLOBALS

Checkpointtable ogg.ggschkpt

That is, to add the checkpoint table (to resume the upload of the breakpoint). After the configuration is completed, you must exit the reconnection and let the parameters take effect.

GGSCI (oracle2) 19 > quit

[ogg@oracle2 ogg] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (oracle2) 1 > dblogin userid ogg password ogg

Successfully logged into database.

GGSCI (oracle2) 2 > add checkpointtable

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

Successfully created checkpoint table ogg.ggschkpt.

[oracle@oracle2 ~] $sqlplus ogg/ogg

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 17 07:14:13 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > select * from tab

TNAME TABTYPE CLUSTERID

GGSCHKPT TABLE

GGSCHKPT_LOX TABLE

GGSCI (oracle2) 3 > add replicat mrpxixi exttrail. / dirdat/md

REPLICAT added.

This is the same as oracle1's pumplqh.

GGSCI (oracle2) 4 > edit params mrpxixi

REPLICAT mrpxixi

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

ASSUMETARGETDEFS

HANDLECOLLISIONS

USERID ogg PASSWORD ogg

DISCARDFILE. / dirrpt/mrpxixi.dsc,PURGE

MAP lqh.test100, TARGET lqh.test100

MAP lqh.test200, TARGET lqh.test200

If the HANDLECOLLISIONS parameter is hit by update, the insert,delete will not operate if it is missed.

If an insert exists and cannot be repeated, it becomes a modification of the data, and if it exists, a new one is inserted. If it wasn't for this,

The parameter will report an error when it encounters the above situation. ASSUMETARGETDEFS assumes that the structure of the data source table is the same as the target table

Create a replicat process

Dblogin userid goldengate,password goldengate

Add checkpointtable goldengate.chktable

Add replicat reppa,exttrail. / dirdat/r1,checkpointtable goldengate.chktable

Configure target segment replication process parameters

REPLICAT

USERIDgoldengate, PASSWORD AACAAAAAAAAAAGAIFAAAUDVHCFUGFIYF, ENCRYPTKEY default

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"

-Open the delay constraint feature in the database to avoid insertion-related constraint errors caused by split in the replication process.

REPORT AT 01:59-generate a report file regularly at 01:59 every day

REPORTCOUNT EVERY 25 MINUTES, RATE

-every 30 minutes of this parameter, goldengate generates a record to the log file (information on the number of records after the extraction process was started and the calculation of the processing rate)

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

-goldengate principle for handling errors, that is, automatically abend an error that cannot be handled, and manual intervention is required to start the process.

ALLOWNOOPUPDATES-when there are excluded columns in the source table or columns that do not exist in the target table, the goldengate default error is reported when the column is updated. After applying this parameter, you can have goldengate generate a warning message instead of reporting an error

ASSUMETARGETDEFS

DISCARDFILE. / dirrpt/ya.dsc, APPEND, MEGABYTES 256m-the path of this parameter must be a relative path. It is recommended that the value of this parameter be set to 1024m

DISCARDROLLOVER AT 02:00-indicates that discard files rotate regularly at 02:00 every day

Assumetargetdefs-indicates that the table structure of the source database is consistent with that of the target database

ALLOWNOOPUPDATES

-- the operation types that need to be masked in the DDL parameter region are masked in this area.

DDL include mapped-DDL all operations

Ddloptions report

DDLERROR IGNORE-DDL error filtering

MAP schema., TARGET schema.

Modify the queue file and rba number started by the process

Alter extseqno, extrba 0

Start, aftercsn scn number

Start

GGSCI (oracle2) 3 > start replicat mrpxixi

Sending START request to MANAGER...

REPLICAT MRPXIXI starting

GGSCI (oracle2) 4 > info replicat mrpxixi

REPLICAT MRPXIXI Last Started 2018-04-13 02:03 Status RUNNING

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

Log Read Checkpoint File. / dirdat/md000000

First Record RBA 0

There is nothing wrong with the Insert,delete,update test.

Ddl doesn't support it yet, so let's do ddl.

Turn off the Recycle Bin function (DDL) must be turned off

SQL > show parameter recyclebin

NAME TYPE VALUE

Recyclebin string on

Alter system set recyclebin=off scope=spfile;, you need to restart the database to take effect.

SQL > @ marker_setup

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

Marker setup table script complete, running verification script...

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

Setting schema name to OGG

MARKER TABLE

MARKER SEQUENCE

Script complete.

SQL > @ ddl_setup user ogg finally appears as follows

STATUS OF DDL REPLICATION

SUCCESSFUL installation of DDL Replication software components

If you report an error, you need to execute @ marker_remove.sql and @ ddl_remove.sql scripts.

SQL > @ role_setup

SQL > grant GGS_GGSUSER_ROLE to ogg

Grant succeeded.

SQL > @ ddl_enable

Trigger altered.

SQL > @ marker_status

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

Ogg

Setting schema name to OGG

MARKER TABLEOK

MARKER SEQUENCE

SQL > @? / rdbms/admin/dbmspool this package is designed to prevent the following foot from making a mistake.

Package created.

Grant succeeded.

SQL > @ ddl_pin.sql ogg

PL/SQL procedure successfully completed.

GGSCI (oracle1) 18 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING CAPLQH 00:00:00 00:00:08

EXTRACT RUNNING PUMPLQH 00:00:00 39:34:33

GGSCI (oracle1) 19 > stop EXTRACT CAPLQH

Sending STOP request to EXTRACT CAPLQH...

Request processed.

GGSCI (oracle1) 20 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED CAPLQH 00:00:00 00:00:04

EXTRACT RUNNING PUMPLQH 00:00:00 00:00:08

GGSCI (oracle1) 21 > edit params caplqh

The content is as follows. Blue is the newly added DDL content.

-- capture change from oracle1 to oracle2

Dynamicresolution

EXTRACT caplqh

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL. / dirdat/qh

DDL INCLUDE ALL

Ddloptions addtrandata, report

TABLE lqh.test200

GGSCI (oracle1) 23 > start EXTRACT CAPLQH

Sending START request to MANAGER...

EXTRACT CAPLQH starting

GGSCI (oracle1) 26 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING CAPLQH 00:00:00 00:00:10

EXTRACT RUNNING PUMPLQH 00:00:00 00:00:00

Modify the replicat file on the destination side

GGSCI (oracle2) 10 > edit params mrpxixi

The content is as follows, and the blue is the added part.

REPLICAT mrpxixi

SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)

ASSUMETARGETDEFS

HANDLECOLLISIONS

USERID ogg PASSWORD ogg

DISCARDFILE. / dirrpt/mrpxixi.dsc,PURGE

Dynamicresolution

Ddloptions report

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP lqh.test200, TARGET lqh.test200

GGSCI (oracle2) 13 > stop REPLICAT MRPXIXI

REPLICAT MRPXIXI is already stopped.

If you can't turn off direct kill, such as kill mrpxixi,

GGSCI (oracle2) 14 > start REPLICAT MRPXIXI

Sending START request to MANAGER...

REPLICAT MRPXIXI starting

GGSCI (oracle2) 18 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING MRPXIXI 00:06:25 00:00:13

GGSCI (oracle2) 22 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING MRPXIXI 00:06:25 00:06:11

The test ddl is also coming, no problem.

Ddl synchronization should be cautious. The ddl and dml mechanisms in ogg are separate, and ddl synchronization is not stable.

Execute ddl_remove.sql to remove ddl

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.