oracle_golden_gate_switchover

Overview

In order to perform a switchover, we have to stop every process on the primary, then stop everything on the standby. Once this is done, we have to start extract and data pump on the standby(new primary) and replicat on primary(new standby) So in nutshell our procedure is:

  1. Stop all on Primary
  2. Stop all on Standby
  3. Start Extract + Data pump on Standby (New Primary)
  4. Start Replicat on primary (New standby)
  5. Test the Switchover
  6. Cleanup old Extract on Primary and Replicat on Standby

Process

So let's get's this party on :). Firstly let's define what is what:

  1. Matar - Primary
  2. Naos - Standby

Stop all on Primary

[oracle@matar dirprm]$ /u01/app/oracle/product/12.1.2/ebanking_gg/ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (matar.izb.local) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EBKDMP1     00:00:00      00:00:09
EXTRACT     RUNNING     EBKEXT1     00:00:00      00:00:01


GGSCI (matar.izb.local) 2> lag extract EBKEXT1

2018-07-18 17:57:25  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EBKEXT1 ...
Last record lag 1 seconds.
At EOF, no more records to process


GGSCI (matar.izb.local) 3> stop extract EBKEXT1

Sending STOP request to EXTRACT EBKEXT1 ...
Request processed.


GGSCI (matar.izb.local) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EBKDMP1     00:00:00      00:00:06
EXTRACT     STOPPED     EBKEXT1     00:00:00      00:00:02


GGSCI (matar.izb.local) 5> lag extract  EBKDMP1

2018-07-18 17:58:04  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EBKDMP1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (matar.izb.local) 6> stop extract EBKDMP1

Sending STOP request to EXTRACT EBKDMP1 ...
Request processed.


GGSCI (matar.izb.local) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EBKDMP1     00:00:00      00:00:01
EXTRACT     STOPPED     EBKEXT1     00:00:00      00:00:42


GGSCI (matar.izb.local) 8> [oracle@matar dirprm]$

Stop all on Standby

[oracle@Naos dirprm]$ /u01/app/oracle/product/12.1.2/ebanking_gg/ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (Naos.izb.local) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     EBKREP1     00:00:00      00:00:07
REPLICAT    RUNNING     REP2        00:00:00      00:00:07


GGSCI (Naos.izb.local) 2> lag extract EBKREP1

Sending GETLAG request to EXTRACT EBKREP1 ...

2018-07-18 17:57:14  ERROR   OGG-15145  EXTRACT EBKREP1 does not exist.


GGSCI (Naos.izb.local) 3> status replicat EBKREP1
REPLICAT EBKREP1: RUNNING


GGSCI (Naos.izb.local) 4> status replicat EBKREP1
REPLICAT EBKREP1: RUNNING


GGSCI (Naos.izb.local) 5> lag replicat EBKREP1

2018-07-18 18:00:32  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT EBKREP1 ...
Last record lag 5 seconds.
At EOF, no more records to process


GGSCI (Naos.izb.local) 6> stop replicat EBKREP1

Sending STOP request to REPLICAT EBKREP1 ...
Request processed.


GGSCI (Naos.izb.local) 7> lag replicat rep2

2018-07-18 18:00:58  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REP2 ...
Last record lag 5 seconds.
At EOF, no more records to process


GGSCI (Naos.izb.local) 8> stop replicat rep2

Sending STOP request to REPLICAT REP2 ...
Request processed.

Start Extract + Data pump on Standby (New Primary)

GGSCI (Naos.izb.local) 4> add extract EBKEXT1,TRANLOG,BEGIN NOW
EXTRACT added.

GGSCI (Naos.izb.local) 1> add EXTTRAIL ./dirdat/lt, EXTRACT EBKEXT1
EXTTRAIL added.

GGSCI (Naos.izb.local) 2> ADD EXTRACT EBKDMP1, EXTTRAILSOURCE ./dirdat/lt
EXTRACT added.

GGSCI (Naos.izb.local) 3> ADD RMTTRAIL ./dirdat/rt, EXTRACT EBKDMP1
RMTTRAIL added.

GGSCI (Naos.izb.local) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EBKDMP1     00:00:00      00:00:02
EXTRACT     STOPPED     EBKPEXT1    00:00:00      00:02:26
REPLICAT    STOPPED     EBKREP1     00:00:00      00:44:57   <- Old replicats (Will delete them later)
REPLICAT    STOPPED     REP2        00:00:00      00:44:34   <- Old replicats (Will delete them later)

Start Replicat on primary (New standby)

GGSCI (matar.izb.local) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EBKDMP1     00:00:00      01:14:18   <- Again old Extracts, will remove them later :)
EXTRACT     STOPPED     EBKEXT1     00:00:00      01:14:59   <- Again old Extracts, will remove them later :)


GGSCI (matar.izb.local) 6> ADD REPLICAT EBKREP1, EXTTRAIL ./dirdat/rt, checkpointtable ggate.CKPTAB
REPLICAT added.


GGSCI (matar.izb.local) 8> ADD REPLICAT REP2 , EXTTRAIL ./dirdat/rt, checkpointtable ggate.CKPTAB
REPLICAT added.


GGSCI (matar.izb.local) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EBKDMP1     00:00:00      01:17:29
EXTRACT     STOPPED     EBKEXT1     00:00:00      01:18:10
REPLICAT    STOPPED     EBKREP1     00:00:00      00:00:24
REPLICAT    STOPPED     REP2        00:00:00      00:00:01


GGSCI (matar.izb.local) 10>


GGSCI (matar.izb.local) 11> start replicat EBKREP1

Sending START request to MANAGER ...
REPLICAT EBKREP1 starting

GGSCI (matar.izb.local) 12> start replicat rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting


GGSCI (matar.izb.local) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EBKDMP1     00:00:00      01:18:22
EXTRACT     STOPPED     EBKEXT1     00:00:00      01:19:02
REPLICAT    RUNNING     EBKREP1     00:00:00      00:00:05
REPLICAT    RUNNING     REP2        00:00:00      00:00:01

Test the Replication

We can insert a row into empty table on the standby(new primary) 

SQL> select * From TESTTABLE;

        ID
----------
         1

SQL> insert into TESTTABLE values(2);

1 row created.

SQL> commit;

Check it on the primary(New standby) 

SQL>  select * from IZOLAPROP.TESTTABLE;

        ID
----------
         1
         2

Count the rows of all tables within one schema

set pagesize 450
set lines 400
col table_name for a40
SELECT
table_name,
',',
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from &&owner .'||table_name)),'/ROWSET/ROW/C')) count
FROM dba_tables where owner = '&owner'
order by count asc;

Sequences

Since golden gate doesn't deal with sequences, (we can configure, but it will be nuts :) ) We can simply re-create the sequences as follows: 

select 'drop sequence '||sequence_owner||'.'||sequence_name||'; \n
create sequence '||sequence_owner||'.'||sequence_name||' increment by 1 start with '||(last_number)||' MAXVALUE 999999999999999999 MINVALUE 1;'
from dba_sequences where sequence_owner in ('OWNER_1','OWNER_2');

!!!Happy End!!!

  • oracle_golden_gate_switchover.txt
  • Last modified: 2023/11/27 13:17
  • by andonovj