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

I. Goldengate two-way replication configuration

1.1. On the source database source system (original target database) side before making the following configuration

Add a secondary redolog configuration:

SQL > alter database add supplemental log data

SQL > alter system switch logfile

SQL > alter database force logging

1.2. Create an emp_ogg table with scott users in the original Source and the original Target respectively

SQL > create table emp_ogg as select * from emp where 1: 0; / / the original source library creates tables but does not insert data.

Table created.

In case of single replication, the Source end is the EINI_1 process, and the Target side is the RINI_1 process, and vice versa in the case of two-way replication, in order to complete the initialization synchronization of the two tables. The configuration is as follows:

Original Source

GGSCI (gc2) 36 > ADD REPLICAT RINI_1, SPECIALRUN

REPLICAT added.

GGSCI (gc2) 43 > EDIT PARAMS RINI_1

-- GoldenGate Initial Load Delivery

--

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE. / dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*

Original Target

GGSCI (oraclelinux54.cuug.net) 10 > ADD EXTRACT EINI_1, SOURCEISTABLE

EXTRACT added.

GGSCI (oraclelinux54.cuug.net) 11 > INFO EXTRACT *, TASKS

EXTRACT EINI_1 Initialized 2014-08-12 23:05 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI (oraclelinux54.cuug.net) 12 > EDIT PARAMS EINI_1

-- GoldenGate Initial Data Capture

-- for EMP_OGG and DEPT_OGG

--

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST gc2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.EMP_OGG; / / here we only initialize the emp_ og table under scott users.

GGSCI (oraclelinux54.cuug.net) 19 > START EXTRACT EINI_1

Sending START request to MANAGER...

EXTRACT EINI_1 starting

GGSCI (oraclelinux54.cuug.net) 20 > VIEW REPORT EINI_1

…… ...

*

* Run Time Statistics * *

*

Report at 2014-08-12 23:11:04 (activity since 2014-08-12 23:10:59)

Output to RINI_1:

From Table SCOTT.EMP_OGG:

# inserts: 14 / / you can see that both sides have synchronized successfully

# updates: 0

# deletes: 0

# discards: 0

1.3. In the ogg environment, add a log trace:

GGSCI (oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD ogg

GGSCI (oraclelinux54.cuug.net) ADD TRANDATA scott.* / / for the convenience of the experiment, we set all tables under scott to be synchronized.

2013-08-13 03:21:18 GGS WARNING 109 No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2013-08-13 03:21:18 GGS WARNING 301Failed to add supplemental log group on table SCOTT.EMP_OGG due to ORA-01031: insufficient privileges, SQL ALTER TABLE "SCOTT". "EMP_OGG" ADD SUPPLEMENTAL LOG GROUP "GGS_EMP_OGG_74686" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") ALWAYS / * GOLDENGATE_DDL_REPLICATION * /.

Solution:

SQL > alter table emp_ogg add constraint emp_ogg_pk primary key (empno)

SQL > grant alter any table to ogg

Grant succeeded.

1.4. Configure extract

GGSCI (gc2) 71 > EDIT PARAMS EORA_1 / / stop the process before editing

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL. / dirdat/aa

TABLE scott.*

DDL INCLUDE OBJNAME "scott.*"

Key to TRANLOGOPTIONS EXCLUDEUSER ogg / / two-way replication

GGSCI (oraclelinux54.cuug.net) 74 > EDIT PARAMS EORA_1 / / stop the process before editing

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL. / dirdat/aa

TABLE scott.*

DDL INCLUDE OBJNAME "scott.*"

GGSCI (double Node execution) > ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

Note:

ADD EXTRACT EORA_1: add an EXTRACT process, which means running all the time, and no one will grab the log in the redo as soon as it stops.

TRANLOG, BEGIN NOW: start synchronous logging now, or use asynchronous, so you need to configure it separately

EXTRACT added.

GGSCI (two-node execution) > ADD EXTTRAIL. / dirdat/aa, EXTRACT EORA_1, MEGABYTES 5 / / add trace files to EORA_1 with a size of 5m

EXTTRAIL added.

GGSCI (dual-node execution) > START EXTRACT EORA_1

1.5. Configure the pump process

GGSCI (gc2) 72 > EDIT PARAMS PORA_1 / / stop the process before editing

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST oraclelinux54.cuug.net, MGRPORT 7809

RMTTRAIL / u01/app/ogg/dirdat/pa

TABLE scott.*

GGSCI (oraclelinux54.cuug.net) 75 > EDIT PARAMS PORA_1 / / stop the process before editing

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc2, MGRPORT 7809

RMTTRAIL. / dirdat/pa

TABLE scott.*

GGSCI (dual-node execution) > ADD EXTRACT PORA_1, EXTTRAILSOURCE. / dirdat/aa / / tell PORA_1 which path to transmit the tracking information under

EXTRACT added.

GGSCI (dual-node execution) > ADD RMTTRAIL. / dirdat/pa, EXTRACT PORA_1, MEGABYTES 5 / / indicates the directory file in which the captured information is transferred remotely

RMTTRAIL added.

GGSCI (dual-node execution) > START EXTRACT PORA_1

Configure replicate

GGSCI (gc2) 74 > EDIT PARAM RORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE. / dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.*, TARGET scott.*

GGSCI (oraclelinux54.cuug.net) 87 > EDIT PARAM RORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE. / dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.*, TARGET scott.*

GGSCI (dual-node execution) 87 > ADD REPLICAT RORA_1, EXTTRAIL. / dirdat/pa / / indicates where to extract the transmitted information

GGSCI (dual-node execution) 87 > START REPLICAT RORA_1

SQL > conn / as sysdba

Connected.

SQL > grant insert on scott.emp_ogg to ogg

Grant succeeded.

SQL > conn / as sysdba

Connected.

SQL > grant delete on scott.emp_ogg to ogg

Grant succeeded.

SQL > grant update on scott.emp_ogg to ogg

Grant succeeded.

2. Configure checkpoint

GGSCI (oraclelinux54.cuug.net) EDIT PARAMS. / GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

GGSCI (oraclelinux54.cuug.net) exit

GGSCI (oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (oraclelinux54.cuug.net) ADD CHECKPOINTTABLE / / if a single copy has been done, there is no need to add it here

You should see that the processes of both nodes are in Running state.

GGSCI (gc2) 73 > info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:02

EXTRACT RUNNING PORA_1 00:00:00 00:00:04

REPLICAT RUNNING RORA_1 00:00:00 00:00:01

GGSCI (oraclelinux54.cuug.net) 88 > info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:09

EXTRACT RUNNING PORA_1 00:00:00 00:00:02

REPLICAT RUNNING RORA_1 00:00:00 00:00:05

Third, test the two-way transmission results

3.1.source to target

Source system

SQL > INSERT INTO emp_ogg VALUES (8000 recordings HLTHLTHLING pencils CLRKLERKPY 7902 LINGOLING 12murDECULING 80s, 800pIY 100LING 20)

1 row created.

SQL > commit

Commit complete.

SQL > select * from emp_ogg

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

8000 HL CLERK 7902 12-DEC-80 800 100 20

Target system

SQL > select * from emp_ogg

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

8000 HL CLERK 7902 1980-12-12: 00:00:00 800 100 20

3.2target to source

Target system

SQL > select * from tcustmer

CUST NAME CITY ST

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL > insert into tcustmer values ('HL','zai','cuug','en')

1 row created.

SQL > commit

Commit complete.

Source system

SQL > select * from tcustmer

CUST NAME CITY ST

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

Helei zai cuug en

4. Support DDL replication configuration

4.1 execute the DDL synchronization script command on both nodes:

First enter the goldengate software installation directory, log in to oracle as SYSDBA and execute the following script. During the execution of the script, all the users you need to enter are ogg, and the installation mode is INITIALSETUP. If there are errors in the data dictionary or some internal packages, you need to run catalog.sql and catproc.sql scripts.

SQL > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string off must be off

A recyclebin error will be reported when the second script @ ddl_setup is executed here, and you can only start the library with pfile after modifying the recyclebin=off in Pfile, execute two clean scripts, and run the script again.

SQL > @ marker_setup

SQL > @ ddl_setup

SQL > @ role_setup

SQL > grant GGS_GGSUSER_ROLE to ogg

SQL > @ ddl_enable

If a script is executed incorrectly and needs to be re-executed, the scripts to clean up should be executed first: ddl_remove.sql and marker_remove.sql

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.