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:
So let's get's this party on :). Firstly let's define what is what:
[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]$
[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.
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)
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
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
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;
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!!!