FAQ and other troubleshooting questions and tips
Please also see our forums for technical questions and solutions with Dbvisit Replicate: http://www.dbvisit.com/forums/forumdisplay.php?f=37
We are replicating a lot of tables (170) and we are not seeing the prompt in the Dbvisit Replicate console.
Even when the console is listing all the tables being replicated, the command prompt is still available. However, the table listing can be so long that the prompt cannot be seen. By default, 30 tables are listed for each replication, thus for a 2-way replication, it takes up to 70 screen lines.
To change this, edit the *-MINE.ddc file and add line:
set STATUS_BAR_ACTIVE_TABLES 5
Or enter this command at the console prompt. Restart the console for this change to take affect.
Why does the MINE process have to go back to previous logs when it restarts?
The MINE process always has to go back to the start of the oldest active transaction, or last obsolete redo log, starting always on a redo log boundary. That is why on re-start the MINE has to go back to a previous log (PLOG) when it restarts. Note that Dbvisit Replicate will never remove a PLOG that still has an active transaction.
How do we see the progress of the replication?
To asses how the replication is going, start the Dbvisit Replicate console. Then look at the first lines that display datetime, log sequence and SCN of the MINE and MINE processes. These numbers should be constantly updating as the replication is happening in real-time.
The first time you start the replication, it starts at the time as of when the *-apply.sh script was run; on subsequent restarts, it needs to go back to the start of the oldest active transaction.
How is 2-way (active-active) replication different to 1-way replication?
With 1-way replication there is a MINE process on the source database and an APPLY process on the target database.
With 2-way replication there is a MINE and APPLY process on the source database and an APPLY and MINE process on the target database. Each process name must be unique so the second MINE and APPLY processes are usually called MINE1 and APPLY1.
In the console these 4 processes will be displayed at the top of the screen:
MINE IS running. Currently at plog 400 and SCN 5532051 (1/30/2016 12:53:29). APPLY IS running. Currently at plog 400 and SCN 5519040 (1/30/2016 12:21:38) APPLY1 IS running. Currently at plog 370 and SCN 5249942 (1/30/2016 12:53:34). MINE1 IS running. Currently at plog 370 and SCN 5249944 (1/30/2016 12:53:35).
By default when commands are issues in the console it issues commands against process MINE and APPLY. To set the console to issue commands against MINE1 or APPLY1, issue the following command:
dbvrep> choose replication mine1
What are the important commands to run when diagnosing Dbvisit Replicate?
The following commands should be run from the Dbvisit Replicate console:
HEALTHCHECK SHOW TYPE SHOW PEER SHOW INTERFACE SHOW UNIQUE_ID
Check that the hostnames and database names resolve to correct targets on all machines involved.
Please also see Basic Commands and General Check
How can I check on which interfaces and ports Dbvisit Replicate is listening?
Run the following command in the Dbvisit Replicate console:
SHOW INTERFACE
Our source is Oracle on Linux and our target is Microsoft SQL Server. Do we need a Linux Microsoft SQL Server ODBC driver on the source Linux server? Can we use the FETCHER process to avoid the Linux ODBC driver?
The MINE process does not connect to the target database and thus does not need any ODBC drivers for Linux. So you do not need the FETCHER process. MINE is usually running locally with the database, and because that is Windows and it is a local MSSQL connection so that will work.
Dbvisit Replicate Console needs to connect to both MINE and APPLY, so it will need an ODBC connection. The simple solution is to run the console on the target server and you will not need an Linux MSSQL ODBC driver.
How can we manually compare the data between source and target and optionally MINE the differences?
Please see Comparing the data between source and target
Does Dbvisit Replicate work with SELinux?
Currently Dbvisit Replicate does not work with Security-Enhanced Linux. When the dbvrep executable is run, there is an error message:
libnnz11.so: cannot restore segment prot after reloc: Permission denied
To check if SELinux is enabled, run command:
/usr/sbin/getenforce
If the output is "Enforcing" then SELinux is enabled.
To disable SELinux, please see http://www.crypt.gen.nz/selinux/disable_selinux.html
Do target tables need to have the same Primary/Unique/Foreign Keys as the source tables? I’d like to keep the keys to a minimum to speed up INSERTs.
It is not recommend to drop the PK/UK/FK. This is because certain types of conflicts will not be detected and this will cause data divergence. It is also dependent upon the target. If it is going to be used as a substitute for source, then keeping the same structure is advantageous. If the target is used for reporting only then disabling certain constraints is a possibility.
Is primary key (PK) needed on target tables or can the target be configured without Primary Keys?
We recommend having a PK on target tables. Updates and delete SQL statements have where clauses that include the source PK columns. They are usually used to locate the row (index lookup). If there are no indexes and the tables are not small, performance of updates/deletes will suffer on the target.
If the MINE is in sync, what exactly means a table with less than 100%
The counters not at 100% are caused by the IGNOREALL handler - this handler ignores all changes done in a transaction after the conflicting statement; use IGNORE to skip just the one change.
What is the Error: Command affected 0 row(s).
Command affected 0 row(s), are caused by the data being out of sync before the replication starts. It means that a record was updated or deleted on the source database and when the update or delete SQL was applied to the target database, the record to update or delete was not found. Investigate why the data was not there on the target. It is possible to ignore this with the IGNORE conflict handler.
For more information please see Conflicts and Conflict Resolution
Are Materialized views supported?
Materialized views are not supported. You can replicate a materialized view, but the target must be an ordinary table.
MINE is at 100% CPU. Is anything wrong
When the database is very busy with updates, then the MINE process could be at 100% CPU because there is a lot of work to do. However this should only be for short periods of time (depending on the workload).
Is there an easy way to reinitialise just one table, reload all data to it from the source and ignore previous conflicts on it?
To re-instantiate one table, the following steps can be followed:
Stop the mine and MINE processes with command:
dbvrep> shutdown all
- Run "unprepare table"
- Run "prepare table", it will show SCN as of which the table is prepared
- Load data as of that SCN (see the APPLY.sh script that was generated by the setup wizard for an example and syntax on how to use datapump with an SCN)
- Start the mine and MINE again
How can I confirm that replication started at a particular SCN?
You can confirm where the MINE started with:
SQL> select first_scn from dbvrep.dbrsmine_plog_current; FIRST_SCN ---------- 21418661
We do not store the SCN of where MINE started.
SET APPLY.APPLY_LISTEN_INTERFACE 0.0.0.0:7902
Question:
I see the following in the onetime.ddc file:
SET APPLY.APPLY_LISTEN_INTERFACE 0.0.0.0:7902 SET MINE.MINE_LISTEN_INTERFACE 0.0.0.0:7901
As I understand these values are loaded in to the database and as I said the source and target is the same database one the same server – 0.0.0.0 is not a valid IP address, although during the setup wizard I specify the fully qualified domain and server name.
Answer:
0.0.0.0 denotes "any interface", so this setting means the process(es) listen on all network interfaces (loopback, ethernet, ...).
This is the intended and correct value.
How to test if remote port is reachable
Use the nc -z <host> <port> command:
nc -z dbvisit210 7901 Connection to dbvisit210 7901 port [tcp/tnos-sp] succeeded!
Or use telnet:
telnet dbvisit210 7901
If the message says: "Escape character is" then the connection is successful.
How can I configure dbvrep to fetch logs from each RAC instance since they are on private storage?
This configuration is not recommended and is not directly supported by the MINE process. A workaround is to use multiple fetchers (usually one for each server), so they can access the redo/archive logs. However, this is not directly supported by the setup wizard and thus the configuration will need some manual work to set up.
The recommended way is to cross-mount the private storages, e.g. using NFS, so the logs are accessible from the machine where the MINE runs.
If the LOB is stored out-of line (i.e. it is larger than 4000 bytes) will it still be replicated?
Yes.
Does Replicate handle Multibyte charactersets like Japanese Kanji?
Yes, if they are stored on the source database as utf8/utf16 (Unicode).
Protecting the target (or slave) from writing / updating / deleting data
We are using Dbvisit Replicate to replicate our database to a target or slave database. The target database is a fully read write database.
What are the options?
Answer
Here are some suggestions on how having read only target database maybe achieved:
- If noone logs in as table owner(s) and no "any" privileges are used: revoke udate/delete/insert/alter privileges from all users except DBVREP.
- Otherwise: create simple "always fail" triggers with just raise_application_error(-20000, "Table is replicated, do not modfiy it"). Requires 10.2.0.5/11.2.0.2 so that Dbvisit Replicate can disables these triggers for own sessions.
- Oracle EE only: use VPD policies - either where 1=0 or with failing predicate function. Then grant EXEMPT ACCESS POLICY to DBVREP (or even use sys_context to check that it's really dbvrep executable from the machine where MINE runs)
For all three options: It is important to no give anyone DBVREP login credentials.
Adding a new target to an existing Replication
Is it possible to add a new target (or slave) to an existing running replication?
We normally recommend that you run through the setup wizard again to create a new config with 1 master and 2 targets (or slaves) as it is harder to change an existing configuration.
To setup for multiple targets please see: Configure one-to-many replication
If you want to manage each target (or slave) separately (because the targets are so disparate in terms of team responsibility, location, availability etc.), you can also set up a new separate config for the new target.
This does mean that you will have separate 2 MINE processes on the source.
What are the best ways to purge older data?
If I convert the tables to partitioned tables on the target db, will my replication still work?
Partitioning will certainly work. Dbvisit Replicate does not care about the physical storage on the target database, it uses just SQL. So it is possible to use subpartitioning such as interval partitioning.
The changes are inserted as they are fed from MINE, i.e. they can stay inserted but uncommitted until the transaction commits on the source database. When using the CDC option, this means that the application that reads the CDC data must not rely solely on the SCN/time of the change to determine whether a record was processed (that's what the commit SCN is for). And it also means that the purging of old partitions must not be too aggressive, as they can still contain uncommitted rows.
The commit SCN is inserted as NULL and updated only when the commit actually comes in. (This means the transaction id should be indexed if commit SCN column is used and that you need row movement enabled if you use that as partition key.)
Filtering transaction or exclude transactions
I am building a reporting/archival database for an existing apps. I would like to replicate the data, but exclude the transactions that remove or archive historical or expired data from the source. How do I do this?
You can tag your transaction that archive the historical data with the Oracle "SET TRANSACTION NAME". Dbvisit Replicate can then filter out these transaction. The "SET TRANSACTION" must be the first thing in the transaction.
You must use a specific transaction name for Dbvisit Replicate to filter the transaction:
SET TRANSACTION NAME 'DBREPL_DB_%s_XID_%s'
The first %s: name of the target database (as configured in the setup wizard).
The second %s: is not relevant.