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

Objective: to simulate simple one-way DDL replication on two virtual machines.

Note: because "OGG stand-alone installation and configuration, and experiment one-way DML replication operation" has been configured one-way dml operation, so the configuration content on the basis of the previous article.

Environment explanation: in "OGG stand-alone installation and configuration, and experiment with one-way DML replication operation", hostname:slient,db_name:test is used as the source library, while hostname:one,db_name:onemore as the target library, you only need to configure a reverse operation this time, that is, slient is the source side and onemo is the target side.

The steps of the experiment:

1. The source closes the Recycle Bin

Note: in oracle11g, the System Modifiable of the recyclebin parameter is DEFERRED, which means that if you want to modify the system level, you need to add the deferred parameter, which has no effect on the currently connected sesion, but the newly connected session will be affected. (you can query the view selectname,isses_modifiable,issys_modifiable from v$parameter wherename='recyclebin';)

SQL > set lines 200

SQL > col name for A30

SQL > select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin'

NAME ISSES ISSYS_MOD

Recyclebin TRUE DEFERRED

SQL > alter system set recyclebin=off DEFERRED

System altered.

SQL > select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin'

NAME ISSES ISSYS_MOD

Recyclebin TRUE DEFERRED

two。 Source-side configuration. / GLOBALS (modify global configuration file to add ggschema parameter)

GGSCI (slient as ogg@test) 14 > edit params. / GLOBALS

Ggschema ogg

~

". / GLOBALS" [New] 1L, 13C written

GGSCI (slient as ogg@test) 15 >

GGSCI (slient as ogg@test) 16 > view param. / GLOBALS

Ggschema ogg

GGSCI (slient as ogg@test) 17 >

3. The source library runs related sql scripts

[oracle@slient ogg_home] $pwd

/ opt/ogg/ogg_home

[oracle@slient ogg_home] $

[oracle@slient ogg_home] $ls mark*

Marker_remove.sql marker_setup.sql marker_status.sql

[oracle@slient ogg_home] $

[oracle@slient ogg_home] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 30 21:52:30 2017

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

Connected to:

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

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

-- execute marker_setup.sql script:

SQL > @ / opt/ogg/ogg_home/marker_setup.sql

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

-

OK

MARKER SEQUENCE

-

OK

Script complete.

SQL >

-- execute ddl_setup.sql script

SQL >! ls ddl_setup*

Ddl_setup.sql

SQL > @ / opt/ogg/ogg_home/ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

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

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

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

Working, please wait...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables...

Check complete.

Using OGG as an Oracle GoldenGate schema name.

Working, please wait...

DDL replication setup script complete, running verification script...

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

Setting schema name to OGG

CLEAR_TRACE STATUS:

Line/pos Error

--

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

--

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

--

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

--

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL IGNORE TABLE

-

OK

DDL IGNORE LOG TABLE

-

OK

DDLAUX PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL HISTORY TABLE

-

OK

DDL HISTORY TABLE (1)

-

OK

DDL DUMP TABLES

-

OK

DDL DUMP COLUMNS

-

OK

DDL DUMP LOG GROUPS

-

OK

DDL DUMP PARTITIONS

-

OK

DDL DUMP PRIMARY KEYS

-

OK

DDL SEQUENCE

-

OK

GGS_TEMP_COLS

-

OK

GGS_TEMP_UK

-

OK

DDL TRIGGER CODE STATUS:

Line/pos Error

--

No errors No errors

DDL TRIGGER INSTALL STATUS

-

OK

DDL TRIGGER RUNNING STATUS

ENABLED

STAYMETADATA IN TRIGGER

OFF

DDL TRIGGER SQL TRACING

0

DDL TRIGGER TRACE LEVEL

NONE

LOCATION OF DDL TRACE FILE

-

/ u01/app/oracle/diag/rdbms/test/test/trace/ggs_ddl_trace.log

Analyzing installation status...

VERSION OF DDL REPLICATION

-

OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

STATUS OF DDL REPLICATION

-

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL >

-- execute role_setup.sql script

SQL >! pwd

/ opt/ogg/ogg_home

SQL > @ role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

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

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

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ogg

SP2-0606: Cannot create SPOOL file "role_setup_spool.txt"

SP2-0606: Cannot create STORE file "role_setup_set.txt"

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

Where is the user assigned to the GoldenGate processes.

SQL >

-perform authorization according to the above prompts:

SQL > GRANT GGS_GGSUSER_ROLE TO ogg

Grant succeeded.

SQL >

-- execute ddl_enable.sql script:

SQL > @ ddl_enable.sql

Trigger altered.

SQL >

-- execute dbmspool.sql

SQL > @? / rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

-- execute ddl_pin.sql

SQL > @ ddl_pin.sql

Enter value for 1: ogg

PL/SQL procedure successfully completed.

Enter value for 1: ogg

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL >

4. The source library modifies the params file of the extract process, adds the "ddl include all" parameter, and restarts the extract process

GGSCI (slient as ogg@test) 19 > edit param exta

EXTRACT exta

Setenv (ORACLE_SID=test)

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Userid ogg,password ogg

Ddl include all

Exttrail / opt/ogg/ogg_home/dirdat/r1

Dynamicresolution

TABLE scott.*

~

"dirprm/exta.prm" 8L, 203C written

GGSCI (slient as ogg@test) 20 >

-restart the extract process:

GGSCI (slient as ogg@test) 20 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEA 00:00:00 00:00:05

EXTRACT RUNNING EXTA 00:00:00 00:00:10

REPLICAT STOPPED REP_REV 00:00:00 01:16:00

GGSCI (slient as ogg@test) 21 >

GGSCI (slient as ogg@test) 21 >

GGSCI (slient as ogg@test) 21 > stop exta

Sending STOP request to EXTRACT EXTA...

Request processed.

GGSCI (slient as ogg@test) 22 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEA 00:00:00 00:00:07

EXTRACT STOPPED EXTA 00:00:00 00:00:20

REPLICAT STOPPED REP_REV 00:00:00 01:16:32

GGSCI (slient as ogg@test) 23 > start EXTA

Sending START request to MANAGER...

EXTRACT EXTA starting

GGSCI (slient as ogg@test) 24 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEA 00:00:00 00:00:07

EXTRACT RUNNING EXTA 00:00:00 00:00:03

REPLICAT STOPPED REP_REV 00:00:00 01:16:42

GGSCI (slient as ogg@test) 25 > info extract EXTA

EXTRACT EXTA Last Started 2017-10-30 22:30 Status RUNNING

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

Process ID 21542

Log Read Checkpoint Oracle Redo Logs

2017-10-30 22:30:38 Thread 1, Seqno 336, RBA 7193088

SCN 0.21418576 (21418576)

GGSCI (slient as ogg@test) 26 >

5. The target library modifies the params file of the replicat process, adds "ddl include all" and "ddlerrordefault ignore retryop maxretries 3 retrydelay 5" parameters, and restarts the replicat process

GGSCI (one as ogg@onemo) 67 > edit param rep_demo

Replicat rep_demo

Setenv (oracle_sid=onemo)

Setenv (nls_lang = "american_america.zhs16gbk")

Userid ogg,password ogg

Ddl include all

Ddlerror default ignore retryop maxretries 3 retrydelay 5

-- report at 01:59

-- reportrollover at 02:00

Reperror default,abend

Discardfile / u01/app/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10

Assumetargetdefs

-- allownoopupdates

Dynamicresolution

-- insertallrecords

Map scott.*,target scott.*

~

~

"dirprm/rep_demo.prm" 15L, 431C written

GGSCI (one as ogg@onemo) 68 >

GGSCI (one as ogg@onemo) 68 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED DPE_REV 00:00:00 01:24:21

EXTRACT STOPPED EXT_REV 00:00:00 01:24:27

REPLICAT STOPPED REP_DEMO 00:00:00 00:04:09

-restart the replicat process

GGSCI (one as ogg@onemo) 69 > start REP_DEMO

Sending START request to MANAGER...

REPLICAT REP_DEMO starting

GGSCI (one as ogg@onemo) 70 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED DPE_REV 00:00:00 01:24:28

EXTRACT STOPPED EXT_REV 00:00:00 01:24:34

REPLICAT RUNNING REP_DEMO 00:00:00 00:00:01

GGSCI (one as ogg@onemo) 71 >

GGSCI (one as ogg@onemo) 71 > info REPLICAT REP_DEMO

REPLICAT REP_DEMO Last Started 2017-10-27 08:45 Status RUNNING

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

Process ID 11591

Log Read Checkpoint File / u01/app/oracle/ogg/dirdat/ra000000000

2017-10-30 21 1114 03.153747 RBA 5075

GGSCI (one as ogg@onemo) 72 >

6. Test the data on the source side and the target side

-- check the source and target databases first:

Source database:

SQL > conn scott/tiger

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TB_PT TABLE

TREE_VIEW VIEW

6 rows selected.

Target Library:

SQL > conn scott/tiger

Connected.

SQL >

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL >

-- start testing:

Source database:

SQL > create table T5 (an int)

Table created.

SQL > insert into T5 values

1 row created.

SQL > commit

Commit complete.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T5 TABLE

TB_PT TABLE

TEST TABLE

TREE_VIEW VIEW

8 rows selected.

SQL >

Check the target library:

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T5 TABLE

TEST TABLE

6 rows selected.

SQL > select * from T5

A

-

one hundred and eleven

SQL >

Test data synchronization is successful, realizing one-way DDL replication!

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.