By Franck Pachot
.
I had a quick question today about Dbvisit replicate as a solution to feed an Operational Data Store. The need is to run some end-of-month jobs on data from a specific state. The best way is to stop the APPLY at that fixed state for the duration of the job. Then no need for flashback query and no risk of ORA-1555. And what if we know this state only afterwards? Easy if the target is in Enterprise Edition, running in FLASHBACK ON. Then how to continue the APPLY? It’s easy with Dbvisit replicate. Here is an example.
One solution would be to create a restore point before doing the flashback in order to be able to get forth to the state before the flashback and re-start the APPLY where it was stopped. But if we do that, we need to open the database READ ONLY for running the job on it.
The other solution is to be able to re-start the apply at the point where the database has been flashed back. Do you think we have to get the right SCN, reset some configuration with it, etc? No. There’s something very simple and practical with Dbvisit replicate:
- The MINE configuration is stored in the source database
- The APPLY configuration is stored in the destination database
When you install Dbvisit for the first time, you may wonder why there is not only one repository. But this architecture makes it easy as the metadata is stored with the related data.
In my case, if I flashback the target database, the APPLY repository is flashed back as well, so the APPLY continues exactly at the right point
But just in case let’s test it.
I use my old #repattack VMs, which explains why the dbvrep version is old, and why the time is on New Zealand time zone. Here is a running replication:
/ Dbvisit Replicate 2.7.06.4485(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 392 and SCN 6648759 (11/04/2016 09:14:08).
APPLY IS running. Currently at plog 392 and SCN 6648742 (11/04/2016 09:14:06).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CUSTOMERS: 100% Mine:210/210 Unrecov:0/0 Applied:210/210 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.ADDRESSES: 100% Mine:210/210 Unrecov:0/0 Applied:210/210 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.CARD_DETAILS: 100% Mine:195/195 Unrecov:0/0 Applied:195/195 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.ORDER_ITEMS: 99% Mine:1553/1553 Unrecov:0/0 Applied:1550/1550 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.ORDERS: 99% Mine:1245/1245 Unrecov:0/0 Applied:1243/1243 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.INVENTORIES: 99% Mine:1523/1523 Unrecov:0/0 Applied:1521/1521 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
REPOE.LOGON: 99% Mine:1493/1493 Unrecov:0/0 Applied:1491/1491 Conflicts:0/0 Last:04/11/2016 09:09:13/OK
--------------------------------------------------------------------------------------------------------------------------------------------
I want to flashback to a quarter before, at 09:00, and because I’m in Oracle XE I’ll restore rather than flashback:
[oracle@target ~]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 4 09:10:40 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2736105154)
RMAN> startup force mount;
Oracle instance started
database mounted
...RMAN> restore database until time "timestamp'2016-11-04 09:00:00'";
Starting restore at 04-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISKRMAN> recover database until time "timestamp'2016-11-04 09:00:00'";
Starting recover at 04-NOV-16
using channel ORA_DISK_1
starting media recovery
...
media recovery complete, elapsed time: 00:02:17
Finished recover at 04-NOV-16
RMAN> sql "alter database open resetlogs";
sql statement: alter database open resetlogsSo the APPLY stopped because the destination was down, but the MINE continues:
- Dbvisit Replicate 2.7.06.4485(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 392 and SCN 6654476 (11/04/2016 09:27:12).
Could not connect to APPLY process. Process not started or connection refused.
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CUSTOMERS: ---% Mine:300/300 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.ADDRESSES: ---% Mine:300/300 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.CARD_DETAILS: ---% Mine:277/277 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.ORDER_ITEMS: ---% Mine:2178/2178 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.ORDERS: ---% Mine:1735/1735 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.INVENTORIES: ---% Mine:2129/2129 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
REPOE.LOGON: ---% Mine:2109/2109 Unrecov:0/0 Applied:0/0 Conflicts:0/0 Last:--/--
--------------------------------------------------------------------------------------------------------------------------------------------My database has been flashed back to its state at 09:00 and I can do what I want. Then I just re-start the APPLY:
[oracle@target dbvrep_XE]$ sh dbvrep_XE-run-target.sh
Initializing......done
DDC loaded from database (352 variables).
Dbvisit Replicate version 2.7.06.4485
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /u01/app/oracle/dbvrep_XE/dbvrep_XE-APPLY.ddc loaded.
Starting process APPLY...startedand nothing to do manually, it restarts from where it needs, applying the changes from 09:00
/ Dbvisit Replicate 2.7.06.4485(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 392 and SCN 6655130 (11/04/2016 09:28:33).
APPLY IS running. Currently at plog 392 and SCN 6645037 (11/04/2016 09:05:53).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CUSTOMERS: 46% Mine:312/312 Unrecov:0/0 Applied:145/12 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.ADDRESSES: 46% Mine:312/312 Unrecov:0/0 Applied:145/12 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.CARD_DETAILS: 46% Mine:289/289 Unrecov:0/0 Applied:135/11 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.ORDER_ITEMS: 49% Mine:2247/2247 Unrecov:0/0 Applied:1105/80 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.ORDERS: 49% Mine:1793/1793 Unrecov:0/0 Applied:890/60 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.INVENTORIES: 49% Mine:2199/2199 Unrecov:0/0 Applied:1083/77 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
REPOE.LOGON: 48% Mine:2183/2183 Unrecov:0/0 Applied:1055/75 Conflicts:0/0 Last:04/11/2016 09:22:39/OK
--------------------------------------------------------------------------------------------------------------------------------------------This is quick as it has already been mined. The APPLY quickly resolved the gap:
- Dbvisit Replicate 2.7.06.4485(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 392 and SCN 6655589 (11/04/2016 09:29:36).
APPLY IS running. Currently at plog 392 and SCN 6655567 (11/04/2016 09:29:34).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CUSTOMERS: 99% Mine:319/319 Unrecov:0/0 Applied:317/184 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.ADDRESSES: 99% Mine:319/319 Unrecov:0/0 Applied:317/184 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.CARD_DETAILS: 99% Mine:296/296 Unrecov:0/0 Applied:294/170 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.ORDER_ITEMS: 99% Mine:2291/2291 Unrecov:0/0 Applied:2289/1264 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.ORDERS: 100% Mine:1828/1828 Unrecov:0/0 Applied:1828/998 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.INVENTORIES: 100% Mine:2238/2238 Unrecov:0/0 Applied:2238/1232 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
REPOE.LOGON: 99% Mine:2235/2235 Unrecov:0/0 Applied:2232/1252 Conflicts:0/0 Last:04/11/2016 09:23:32/OK
--------------------------------------------------------------------------------------------------------------------------------------------Things are simple when they are well designed. The APPLY has to synchronize with the destination, so they have put the APPLY repository into the destination. No problem if the replication went further than the point in time you need to freeze. Any solution is possible as long ans the whole database is consistent: Flashback database, PITR, snapshots, etc.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)