Replicação bi-directional DML e DDL entre 2 bancos Oracle RAC usando OGG 11.2

In the article you will have a look at an example of how to build a classic bi-directional DML and DDL replication between two Oracle RAC databases. The environment consist of a two node Oracle 11.2.0.3 cluster running a source database RACD (11.2.0.3) and a target database RACDB (11.2.0.3) and is describedhere and here. Examples are based on OGG classic extract method and the data type limitations exist as described in the official OGG documentation here. Integrated extract method reduces data type limitations but requires Oracle database 11.2.0.3 and a database specific bundle patch for Integrated Extract 11.2.x (Doc ID 1411356.1).

For Exadata 11.2.0.3 databases, the EXADATA_11.2.0.3_BP3 patch (11.2.0.3.3) should be used. The patch is available from the support site referencing patch 13556853. For non-Exadata 11.2.0.3 database, you need to apply patch 13560925 which is available on the support site referencing the patch number.

Although the source and target systems used in the article are both Oracle 11.2.0.3 the described approach can be used for a near zero down time upgrades and migrations.

Bi-directional replication is interesting because it is the foundation for near zero downtime Oracle upgrades. The present production database is used as a source system. Prepare in advance an upgraded database that will become a new production database. The upgraded database is used as a target system. OGG is used to implement a bi-directional DML and DDL replication between the original production database schemas and the new upgrade database that will become a new production database. As a result of OGG replication you will have an upgraded database replicating all changes from the online production database. After testing and verifying that the new upgraded database replicates data properly a decision is made to switch the application over to the upgraded database. This application switchover is the only downtime involved. If after the switchover a problem arises the old database can be used as a fallback option. After running both databases in bi-directional replication for a while you can decommission the old system. For such setup you can use same OGG version for different Oracle databases on different OS platforms.

The architecture will be summarized as follows:

Source Target
Schema/Table sh.*
hr.*
sh_1.*
hr_1.*
Database RACD RACDB
DB users ddl_ogg
ogg_extract
ogg_replicat
ddl_ogg
ogg_extract
ogg_replicat
Extract groups extshrs
Extract groups pshhr
Replicat groups repshhr
Exttrail ./dirdat/ab
Rmttrail ./dirdat/ac
Extract groups extrev
Extract groups prev
Replicat groups reprev
Exttrail ./dirdat/ad
Rmttrail ./dirdat/ae

Setting a bi-directional replication between sites requires the following major steps

  • Install OGG on both sites and configure managers – ( see the OGG install )
  • Set Change Data Capture (CDC) from RACD – start CDC extract extshhr and pump pshhr and record SCN after extshhr is started
  • Perform an initial dataload (data pump based import over database link using SCN at time right after start extshhr)
  • Start data apply – start the replicat to apply changes captured by CDC using afterscn option
  • Set CDC from RACDB – configure CDC extract extrev and pump prev and start extrev and prev
  • Configure and start replicat reprev
  1. Perform OGG installation as specified here.
  2. Configure and start CDC classic extract extshhr

Create parameter file

extract extshhr

SETENV (ORACLE_SID = “RACD1″)

tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract@racd, password ogg_extract

exttrail ./dirdat/ab

–ddl include all

–ddloptions getreplicates, getapplops,addtrandata, report

table sh.*;

table hr.*;

Add supplemental logging at database level to RACD

SQL> alter database add supplemental log data;

Database altered.

SQL>

Add supplemental logging at schema level

GGSCI (raclinux1.gj.com) 10> dblogin userid ddl_ogg@racd, password ddl_ogg

Successfully logged into database.

GGSCI (raclinux1.gj.com) 11> add schematrandata sh

2012-07-14 15:07:28 INFO OGG-01788 SCHEMATRANDATA has been added on schema sh.

GGSCI (raclinux1.gj.com) 12> add schematrandata hr

2012-07-14 15:07:40 INFO OGG-01788 SCHEMATRANDATA has been added on schema hr.

GGSCI (raclinux1.gj.com) 13>

Add supplemental logging at table level at GGSCI prompt

add trandata sh.SALES

add trandata sh.COSTS

add trandata sh.DR$SUP_TEXT_IDX$K

add trandata sh.DR$SUP_TEXT_IDX$N

add trandata sh.SUPPLEMENTARY_DEMOGRAPHICS

add trandata sh.CAL_MONTH_SALES_MV

add trandata sh.DR$SUP_TEXT_IDX$R

add trandata sh.PROMOTIONS

add trandata sh.DR$SUP_TEXT_IDX$I

add trandata sh.TIMES

add trandata sh.PRODUCTS

add trandata sh.COUNTRIES

add trandata sh.DIMENSION_EXCEPTIONS

add trandata sh.FWEEK_PSCAT_SALES_MV

add trandata sh.CUSTOMERS

add trandata sh.CHANNELS

add trandata hr.REGIONS

add trandata hr.DEPARTMENTS

add trandata hr.COUNTRIES

add trandata hr.JOBS

add trandata hr.LOCATIONS

add trandata hr.EMPLOYEES

add trandata hr.JOB_HISTORY

Add an extract and exttrail and get SCN right after extract is started

GGSCI (raclinux1.gj.com) 11> add extract extshhr, tranlog, threads 2, begin now;

EXTRACT added.

GGSCI (raclinux1.gj.com) 12>

GGSCI (raclinux1.gj.com) 12> add exttrail ./dirdat/ab , extract extshhr megabytes 500

EXTTRAIL added.

GGSCI (raclinux1.gj.com) 13>

GGSCI (raclinux1.gj.com) 18> start extract extshhr

Sending START request to MANAGER …

EXTRACT EXTSHHR starting

GGSCI (raclinux1.gj.com) 19> info extract extshhr, detail

EXTRACT EXTSHHR Last Started 2012-07-14 15:29 Status RUNNING

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

Log Read Checkpoint Oracle Redo Logs

2012-07-14 15:30:08 Thread 1, Seqno 191, RBA 1850368

SCN 0.3513686 (3513686)

Log Read Checkpoint Oracle Redo Logs

2012-07-14 15:26:01 Thread 2, Seqno 0, RBA 0

SCN 0.0 (0)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

./dirdat/ab 0 1037 500

Extract Source Begin End

Not Available 2012-07-14 15:26 2012-07-14 15:30

Not Available * Initialized * 2012-07-14 15:26

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/EXTSHHR.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/extshhr.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/EXTSHHR.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/EXTSHHR.pce

Stdout file /u02/stage_ogg112_ora11/dirout/EXTSHHR.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 20>

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN

———–

3513834

SQL>

  1. Perform an initial data load

Create a database link from RACDB to RACD

SQL> create database link racd connect to system identified by sys1 using ‘RACD’;

Database link created.

SQL> select * from dual@racd;

D


X

SQL> select * from dual;

D


X

SQL>

Import SH and HR schemas from RACD into RACDB using impdp over a link and specifying the SCN from the previous step

[oracle@raclinux1 ~]$ impdp system/sys1@racdb schemas=sh,hr remap_schema=sh:sh_1,hr:hr_1 network_link=’RACD’ flashback_scn=3513834

Import: Release 11.2.0.3.0 – Production on Sat Jul 14 16:36:48 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/********@racdb schemas=sh,hr remap_schema=sh:sh_1,hr:hr_1 network_link=RACD flashback_scn=3513834

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 274.3 MB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:”HR_1″ already exists

ORA-31684: Object type USER:”SH_1″ already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported “SH_1″.”CUSTOMERS” 55500 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_1998″ 4411 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_1999″ 5884 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_2000″ 3772 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_2001″ 7328 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_1998″ 2397 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_1999″ 4179 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_2000″ 3715 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_2001″ 5882 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_1998″ 4129 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_1999″ 4336 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_2000″ 4798 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_2001″ 7545 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_1998″ 4577 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_1999″ 5060 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_2000″ 5088 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_2001″ 9011 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_1998″ 43687 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_1999″ 64186 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_2000″ 62197 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_2001″ 60608 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_1998″ 35758 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_1999″ 54233 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_2000″ 55515 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_2001″ 63292 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_1998″ 50515 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_1999″ 67138 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_2000″ 58950 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_2001″ 65769 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_1998″ 48874 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_1999″ 62388 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_2000″ 55984 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_2001″ 69749 rows

. . imported “SH_1″.”SUPPLEMENTARY_DEMOGRAPHICS” 4500 rows

. . imported “SH_1″.”FWEEK_PSCAT_SALES_MV” 11266 rows

. . imported “SH_1″.”PROMOTIONS” 503 rows

. . imported “SH_1″.”TIMES” 1826 rows

. . imported “HR_1″.”COUNTRIES” 25 rows

. . imported “HR_1″.”DEPARTMENTS” 27 rows

. . imported “HR_1″.”EMPLOYEES” 107 rows

. . imported “HR_1″.”JOBS” 19 rows

. . imported “HR_1″.”JOB_HISTORY” 10 rows

. . imported “HR_1″.”LOCATIONS” 23 rows

. . imported “HR_1″.”REGIONS” 4 rows

. . imported “SH_1″.”CAL_MONTH_SALES_MV” 48 rows

. . imported “SH_1″.”CHANNELS” 5 rows

. . imported “SH_1″.”COUNTRIES” 23 rows

. . imported “SH_1″.”DIMENSION_EXCEPTIONS” 0 rows

. . imported “SH_1″.”PRODUCTS” 72 rows

. . imported “SH_1″.”COSTS”:”COSTS_1995″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_1996″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_H1_1997″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_H2_1997″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_2002″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q1_2003″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_2002″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q2_2003″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_2002″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q3_2003″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_2002″ 0 rows

. . imported “SH_1″.”COSTS”:”COSTS_Q4_2003″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_1995″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_1996″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_H1_1997″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_H2_1997″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_2002″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q1_2003″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_2002″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q2_2003″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_2002″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q3_2003″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_2002″ 0 rows

. . imported “SH_1″.”SALES”:”SALES_Q4_2003″ 0 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/DIMENSION

Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ completed with 2 error(s) at 16:46:11

[oracle@raclinux1 ~]$

Configure and start extract pump pshhr

GGSCI (raclinux1.gj.com) 22> view params pshhr

extract pshhr

passthru

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/ac

table sh.*;

table hr.*;

GGSCI (raclinux1.gj.com) 23>

GGSCI (raclinux1.gj.com) 23> add extract pshhr, exttrailsource ./dirdat/ab

EXTRACT added.

GGSCI (raclinux1.gj.com) 24> add rmttrail ./dirdat/ac, extract pshhr megabytes 500

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 25>

GGSCI (raclinux1.gj.com) 25> start extract pshhr

Sending START request to MANAGER …

EXTRACT PSHHR starting

GGSCI (raclinux1.gj.com) 26> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTCDC 00:00:00 00:00:06

EXTRACT RUNNING EXTSALE 00:00:00 00:00:03

EXTRACT RUNNING EXTSHHR 00:00:00 00:00:01

EXTRACT RUNNING PSHHR 00:00:00 00:00:04

EXTRACT RUNNING PUMPSALE 00:00:00 00:00:01

REPLICAT RUNNING REPCDC1 00:00:00 00:00:01

REPLICAT RUNNING REPCDC2 00:00:00 00:00:04

REPLICAT RUNNING REPCDC3 00:00:00 00:00:04

REPLICAT RUNNING REPCUST 00:00:00 00:00:04

REPLICAT RUNNING REPSALE 00:00:00 00:00:04

GGSCI (raclinux1.gj.com) 27>

GGSCI (raclinux1.gj.com) 27> info extract pshhr, detail

EXTRACT PSHHR Last Started 2012-07-14 16:35 Status RUNNING

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

Log Read Checkpoint File ./dirdat/ab000000

First Record RBA 1037

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

./dirdat/ac 0 0 500

Extract Source Begin End

./dirdat/ab000000 * Initialized * First Record

./dirdat/ab000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/PSHHR.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/pshhr.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/PSHHR.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/PSHHR.pce

Stdout file /u02/stage_ogg112_ora11/dirout/PSHHR.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 28>

Configure replicat repshhr and create a checkpoint table if do not exists

GGSCI (raclinux1.gj.com) 30> view params repshhr

replicat repshhr

SETENV (ORACLE_SID = “RACDB1″)

userid ogg_replicat@racdb, password ogg_replicat

–handlecollisions

assumetargetdefs

discardfile ./dirrpt/repshhr.dsc, append

map sh.*, target sh_1.*;

map hr.*, target hr_1.*;

GGSCI (raclinux1.gj.com) 31>

GGSCI (raclinux1.gj.com) 31> add replicat repshhr, exttrail ./dirdat/ac

REPLICAT added.

GGSCI (raclinux1.gj.com) 32>

Add checkpoint table ( iy is already added)

GGSCI (raclinux1.gj.com) 39> dblogin userid ogg_replicat@racdb

Password:

Successfully logged into database.

GGSCI (raclinux1.gj.com) 40>

GGSCI (raclinux1.gj.com) 41> add CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (ddl_ogg.oggchkpt)…

ERROR: OCI Error ORA-00955: name is already used by an existing object (status = 955). Creating checkpoint table ddl_ogg.oggchkpt, SQL <CREATE TABLE ddl_ogg.oggchkpt ( group_name VARCHAR2(8) NOT NULL, group_key NUMBER(19) NOT NULL, seqno NUMBER(10), rba NUMBER(19) NOT NULL, audit_ts VARCHAR2(29), create_ts DATE NOT NULL, last_update_ts DATE NOT NULL, current_dir VARCHAR2(255) NOT NULL, log_csn VARCHAR2(129), log_xid VARCHAR2(129), log_cmplt_csn VARCHAR2(129), log_cmplt_xids VARCHAR2(2000), version NUMBER(3), PRIMARY KEY (group_name, group_key))>.

GGSCI (raclinux1.gj.com) 42>

  1. Start data apply – start the replicat to apply changes captured by CDC using afterscn option

Start the replicat using the SCN captured

GGSCI (raclinux1.gj.com) 35> START REPLICAT repshhr, AFTERCSN 3513834

Sending START request to MANAGER …

REPLICAT REPSHHR starting

GGSCI (raclinux1.gj.com) 36>

Test and verify that unidirectional DML replication works

Make sure that all tables participating in the replication are not created with nologging option in order to avoid the following error. 2012-07-14 18:09:47 ERROR OGG-01960 Failed to validate table SH.CUSTOMERS. The table is created with the NOLOGGING option, which is not supported. Extract may not be able to capture data from it.

Add trace table on the source site and target site to avoid looping in the bi-directional replication.

On Source

GGSCI (raclinux1.gj.com) 25> dblogin userid ogg_extract, password ogg_extract

Successfully logged into database.

GGSCI (raclinux1.gj.com) 26> add tracetable

No trace table specified, using default (GGS_TRACE)…

Successfully created trace table GGS_TRACE.

GGSCI (raclinux1.gj.com) 27> info tracetable

No trace table specified, using default (GGS_TRACE)…

Trace table GGS_TRACE created 2012-07-15 09:21:38.

GGSCI (raclinux1.gj.com) 28>

On target

GGSCI (raclinux1.gj.com) 40> dblogin userid ogg_replicat, password ogg_replicat

Successfully logged into database.

GGSCI (raclinux1.gj.com) 41> add tracetable

No trace table specified, using default (GGS_TRACE)…

Successfully created trace table GGS_TRACE.

GGSCI (raclinux1.gj.com) 42> info tracetable

No trace table specified, using default (GGS_TRACE)…

Trace table GGS_TRACE created 2012-07-15 10:09:05.

GGSCI (raclinux1.gj.com) 43>

Add a checkpoint table to RACD ( for the case when RACD is a target)

GGSCI (raclinux1.gj.com) 118> add CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (ddl_ogg.oggchkpt)…

Successfully created checkpoint table ddl_ogg.oggchkpt.

GGSCI (raclinux1.gj.com) 119>

Add DDL support by modifying the parameter file and restarting the extract. Pay attention
to the parameter DDLOPTIONS GETREPLICATES, GETAPPLOPS, addtrandata
, report

GGSCI (raclinux1.gj.com) 19> view param extshhr

extract extshhr

SETENV (ORACLE_SID = “RACD1″)

tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract@racd, password ogg_extract

exttrail ./dirdat/ab

ddl include all

ddloptions getreplicates, getapplops,addtrandata
, report

table sh.*;

table hr.*;

Modify replicat repshhr to address bi-directional replication.

GGSCI (raclinux1.gj.com) 25> view params repshhr

replicat repshhr

SETENV (ORACLE_SID = “RACDB1″)

userid ogg_replicat@racdb, password ogg_replicat

–handlecollisions

assumetargetdefs

ddl include all

ddloptions report, updatemetadata

discardfile ./dirrpt/repshhr.dsc, append, MAPDERIVED

map sh.*, target sh_1.*;

map hr.*, target hr_1.*;

GGSCI (raclinux1.gj.com) 26>

Test and verify that DDL replication works

SQL> connect sh/sh

Connected.

SQL> drop table cust;

Table dropped.

SQL>

GGSCI (raclinux1.gj.com) 53> stats extract extshhr

Sending STATS request to EXTRACT EXTSHHR …

Start of Statistics at 2012-07-15 09:47:06.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 1.00

Mapped operations 1.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Output to ./dirdat/ab:

Extracting from DDL_OGG.GGS_MARKER to DDL_OGG.GGS_MARKER:

*** Total statistics since 2012-07-15 09:45:06 ***

No database operations have been performed.

*** Daily statistics since 2012-07-15 09:45:06 ***

No database operations have been performed.

*** Hourly statistics since 2012-07-15 09:45:06 ***

No database operations have been performed.

*** Latest statistics since 2012-07-15 09:45:06 ***

No database operations have been performed.

End of Statistics.

GGSCI (raclinux1.gj.com) 54> view report extshhr

2012-07-15 09:45:06 INFO OGG-01487 DDL found, operation [drop table cust (size 16)], start SCN [3742555],

commit SCN [3742716] instance [RACD1 (1)], DDL seqno [14001], marker seqno [14501].

2012-07-15 09:45:06 INFO OGG-00487 DDL operation included [include all], optype [DROP], objtype [TABLE], ob

jowner [SH], objname [CUST].

2012-07-15 09:45:06 INFO OGG-00497 Writing DDL operation to extract trail file.

GGSCI (raclinux1.gj.com) 56> stats extract pshhr

Sending STATS request to EXTRACT PSHHR …

No active extraction maps

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 1.00

.

GGSCI (raclinux1.gj.com) 57>

GGSCI (raclinux1.gj.com) 26> stats replicat repshhr

Sending STATS request to REPLICAT REPSHHR …

No active replication maps

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 1.00

Mapped operations 1.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Errors 0.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 0.00

.

GGSCI (raclinux1.gj.com) 27> view report repshhr

2012-07-15 09:45:08 INFO OGG-00482 DDL found, operation [drop table cust (size 16)].

2012-07-15 09:45:08 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [drop table sh_1.”CUST”

(size 23)].

2012-07-15 09:45:08 INFO OGG-00487 DDL operation included [include all], optype [DROP], objtype [TABLE], objown

er [sh_1], objname [CUST].

2012-07-15 09:45:08 INFO OGG-01407 Setting current schema for DDL operation to [SYS].

2012-07-15 09:45:08 INFO OGG-00484 Executing DDL operation.

2012-07-15 09:45:11 INFO OGG-00483 DDL operation successful.

2012-07-15 09:45:11 INFO OGG-01408 Restoring current schema for DDL operation to [ogg_replicat].

2012-07-15 09:49:20 INFO OGG-01021 Command received from GGSCI: STATS.

GGSCI (raclinux1.gj.com) 28>

  1. Set CDC from RACDB – configure CDC extract extrev and pump prev and start extrev and prev

Prepare the destination RACDB as a source.

Add database supplemental logging to RACDB

SQL> alter database add supplemental log data;

Database altered.

SQL>

Add schema and table supplemental logging after logging in to RACDB from ggsci

add schematrandata sh_1

add schematrandata hr_1

add trandata sh_1.SALES

add trandata sh_1.COSTS

add trandata sh_1.DR$SUP_TEXT_IDX$K

add trandata sh_1.DR$SUP_TEXT_IDX$N

add trandata sh_1.SALES_TRANSACTIONS_EXT

add trandata sh_1.SUPPLEMENTARY_DEMOGRAPHICS

add trandata sh_1.CAL_MONTH_SALES_MV

add trandata sh_1.DR$SUP_TEXT_IDX$R

add trandata sh_1.PROMOTIONS

add trandata sh_1.DR$SUP_TEXT_IDX$I

add trandata sh_1.TIMES

add trandata sh_1.PRODUCTS

add trandata sh_1.COUNTRIES

add trandata sh_1.DIMENSION_EXCEPTIONS

add trandata sh_1.FWEEK_PSCAT_SALES_MV

add trandata sh_1.CUSTOMERS

add trandata sh_1.CHANNELS

add trandata sh_1.cust

add trandata hr_1.REGIONS

add trandata hr_1.DEPARTMENTS

add trandata hr_1.COUNTRIES

add trandata hr_1.JOBS

add trandata hr_1.LOCATIONS

add trandata hr_1.EMPLOYEES

add trandata hr_1.JOB_HISTORY

Add and start extract extrev

GGSCI (raclinux1.gj.com) 35> view params extrev

extract extrev

SETENV (ORACLE_SID = “RACDB1″)

tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract@racdb, password ogg_extract

exttrail ./dirdat/ad

ddl include all

ddloptions getreplicates, getapplops,addtrandata, report

table sh_1.*;

table hr_1.*;

GGSCI (raclinux1.gj.com) 36>

GGSCI (raclinux1.gj.com) 36> add extract extrev, tranlog, threads 2, begin now

EXTRACT added.

GGSCI (raclinux1.gj.com) 37>

GGSCI (raclinux1.gj.com) 37> add exttrail ./dirdat/ad, extract extrev megabytes 500

EXTTRAIL added.

GGSCI (raclinux1.gj.com) 38>

GGSCI (raclinux1.gj.com) 38> start extract extrev

Sending START request to MANAGER …

EXTRACT EXTREV starting

GGSCI (raclinux1.gj.com) 39> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTCDC 00:00:00 00:00:05

EXTRACT RUNNING EXTREV 00:01:45 00:00:04

EXTRACT RUNNING EXTSALE 00:00:00 00:00:04

EXTRACT RUNNING EXTSHHR 00:00:00 00:00:11

EXTRACT RUNNING PSHHR 00:00:00 00:00:00

EXTRACT RUNNING PUMPSALE 00:00:00 00:00:06

REPLICAT RUNNING REPCDC1 00:00:00 00:00:01

REPLICAT RUNNING REPCDC2 00:00:00 00:00:07

REPLICAT RUNNING REPCDC3 00:00:00 00:00:05

REPLICAT RUNNING REPCUST 00:00:00 00:00:07

REPLICAT RUNNING REPSALE 00:00:00 00:00:04

REPLICAT RUNNING REPSHHR 00:00:00 00:00:07

Add and start pump extract prev

GGSCI (raclinux1.gj.com) 40>

GGSCI (raclinux1.gj.com) 43> view params prev

extract prev

passthru

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/ae

table sh_1.*;

table hr_1.*;

GGSCI (raclinux1.gj.com) 44>

GGSCI (raclinux1.gj.com) 44> add extract prev, exttrailsource ./dirdat/ad

EXTRACT added.

GGSCI (raclinux1.gj.com) 45> add rmttrail ./dirdat/ae, extract prev megabytes 500

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 46>

GGSCI (raclinux1.gj.com) 47> start extract prev

Sending START request to MANAGER …

EXTRACT PREV starting

GGSCI (raclinux1.gj.com) 48> info extract prev, detail

EXTRACT PREV Last Started 2012-07-15 11:31 Status RUNNING

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

Log Read Checkpoint File ./dirdat/ad000000

First Record RBA 1038

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

./dirdat/ae 0 0 500

Extract Source Begin End

./dirdat/ad000000 * Initialized * First Record

./dirdat/ad000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/PREV.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/prev.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/PREV.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/PREV.pce

Stdout file /u02/stage_ogg112_ora11/dirout/PREV.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 49>

  1. Configure and start replicat reprev

Add and start replicat reprev

GGSCI (raclinux1.gj.com) 1> view param reprev

replicat reprev

SETENV (ORACLE_SID = “RACD1″)

userid ogg_replicat@racd, password ogg_replicat

–handlecollisions

assumetargetdefs

ddlerror default ignore

–ddlerror default ignore

ddl include all

ddloptions report, updatemetadata, MAPDERIVED

discardfile ./dirrpt/reprev.dsc, append

map sh_1.*, target sh.*;

map hr_1.*, target hr.*;

GGSCI (raclinux1.gj.com) 2>

GGSCI (raclinux1.gj.com) 4> add replicat reprev, exttrail ./dirdat/ae

GGSCI (raclinux1.gj.com) 5> start replicat reprev

Sending START request to MANAGER …

REPLICAT REPREV starting

GGSCI (raclinux1.gj.com) 6>

Test and verify that bi-directional DDL and DML replications works

Test plan is a seriese of DMS and DDL statements issued on RACD and verified on RACDB and issued or RACDB and verified on RACD.

On RACD issue an update on cust

insert into cust values(500000,’John ‘,’Smith ‘,’M’, ’1967′,’Single’,’2345 Crossing Way’,’07470′,’Wayne’,500000,’New Jersy’,500000,52770,’555-555-5555′,’200000-220000′,5000,’john.smith@gmail.com’,’50000′,500000,500000,sysdate-365*5, sysdate,’A’);

Check the stats and target table on sh_1.cust.

GGSCI (raclinux1.gj.com) 79> stats extract extshhr

Sending STATS request to EXTRACT EXTSHHR …

Start of Statistics at 2012-07-16 14:03:12.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 64.00

Mapped operations 60.00

Unmapped operations 2.00

Other operations 2.00

Excluded operations 0.00

Output to ./dirdat/ab:

Extracting from SH.CUST to SH.CUST:

*** Total statistics since 2012-07-16 13:56:53 ***

Total inserts 388502.00

Total updates 55503.00

Total deletes 333001.00

Total discards 0.00

Total operations 777006.00

*** Daily statistics since 2012-07-16 13:56:53 ***

Total inserts 388502.00

Total updates 55503.00

Total deletes 333001.00

Total discards 0.00

Total operations 777006.00

*** Hourly statistics since 2012-07-16 14:00:00 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2012-07-16 14:01:31 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

Extracting from DDL_OGG.GGS_MARKER to DDL_OGG.GGS_MARKER:

*** Total statistics since 2012-07-16 13:56:53 ***

No database operations have been performed.

*** Daily statistics since 2012-07-16 13:56:53 ***

No database operations have been performed.

*** Hourly statistics since 2012-07-16 14:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-07-16 14:01:31 ***

No database operations have been performed.

End of Statistics.

GGSCI (raclinux1.gj.com) 80>

GGSCI (raclinux1.gj.com) 80> stats extract pshhr

Sending STATS request to EXTRACT PSHHR …

Start of Statistics at 2012-07-16 14:03:38.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 4.00

Output to ./dirdat/ac:

Extracting from SH.CUST to SH.CUST:

*** Total statistics since 2012-07-16 14:03:05 ***

Total inserts 1.00

Total updates 2.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2012-07-16 14:03:05 ***

Total inserts 1.00

Total updates 2.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2012-07-16 14:03:05 ***

Total inserts 1.00

Total updates 2.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Latest statistics since 2012-07-16 14:03:05 ***

Total inserts 1.00

Total updates 2.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

End of Statistics.

GGSCI (raclinux1.gj.com) 81>

GGSCI (raclinux1.gj.com) 81> stats replicat repshhr

Sending STATS request to REPLICAT REPSHHR …

Start of Statistics at 2012-07-16 14:04:46.

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 4.00

Mapped operations 4.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 8.00

Errors 0.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 0.00

Replicating from SH.CUST to SH_1.CUST:

*** Total statistics since 2012-07-16 14:03:08 ***

Total inserts 1.00

Total updates 6.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

*** Daily statistics since 2012-07-16 14:03:08 ***

Total inserts 1.00

Total updates 6.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

*** Hourly statistics since 2012-07-16 14:03:08 ***

Total inserts 1.00

Total updates 6.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

*** Latest statistics since 2012-07-16 14:03:08 ***

Total inserts 1.00

Total updates 6.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

End of Statistics.

GGSCI (raclinux1.gj.com) 82>

On RACD

create table sh.cust1 as select * from cust where 1=2;

On RACDB verify that cust1 is replicated. On RACDB add a constraint.

SQL> alter table cust1 add constraint cust1_pk primary key (cust_id);

Table altered.

SQL>

On RACD insert values into cust1.

SQL> insert into cust1 values(500000,’John ‘,’Smith ‘,’M’, ’1967′,’Single’,’2345 Crossing Way’,’07470′,’Wayne’,500000,’New Jersy’,500000,52770,’555-555-5555′,’200000-220000′,5000,’john.smith@gmail.com’,’50000′,500000,500000,sysdate-365*5, sysdate,’A’);

1 row created.

SQL>

SQL>

SQL> commit;

Commit complete.

SQL>

On RACDB confirm the values and on RACDB execute the following DDL.

create table cust2 as select * from cust where 1=2;

alter table cust2 add constraint cust2_pk primary key (cust_id);

On RACD verify that the table cust2 exists.

Summary:

In the article you had a look at an example for a bidirectional DML and DDL replication based on a classic extract using OGG 11.2. The OGG configuration setup was tested and verified. The article explained that a similar setup can be used for a near zero downtime Oracle database upgrade or migration. If an Oracle database is used as source and target that SCN based exp(dp)/imp(dp) provides a faster database instantiation and you looked at way to use it for initial data load.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE e marcado , , , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s