======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:
- Stop all on Primary
- Stop all on Standby
- Start Extract + Data pump on Standby (New Primary)
- Start Replicat on primary (New standby)
- Test the Switchover
- 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:
- Matar - Primary
- 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!!!