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. 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 apply 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 (07/31/2012 12:53:29).
APPLY IS running. Currently at plog 400 and SCN 5519040 (07/31/2012 12:21:38)
APPLY1 IS running. Currently at plog 370 and SCN 5249942 (07/31/2012 12:53:34).
MINE1 IS running. Currently at plog 370 and SCN 5249944 (07/31/2012 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 process 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.
How can I check on which interfaces 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. Apply 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 do we change the Dbvisit Replicate schema user password (default: dbvrep)?
To change the password please follow these steps:
1. Run "dbvrep encrypt new_password_text" to get encrypted form of the new password (this is a very long hex number)
2. Edit the ddc files and change the line memory_set DDC_PASSWORD.
3. Start "start-console.sh --no-checkdb" and enter following commands, replacing newpd with the encrypted password (or put all the commands into a file and use @file).
set MINE.MINE_PASSWORD newpwd
set APPLY.APPLY_PASSWORD newpwd
set MINE.DDC_PASSWORD newpwd
set APPLY.DDC_PASSWORD newpwd
If there are more processes, then do this for all processes.
How can we manually compare the data between source and target and optionally apply the differences?
Please see http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm for the use of DBMS_COMPARISON to compare both datasets and generate SQL for the differences.
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.
Do the source tables require supplemental logging?
Yes the source tables require supplemental logging. This is automatically done during the setup. The following SQL is run by the setup scripts:
SQL>alter database add supplemental log data;
SQL> alter table '||owner||'.'||table_name||' add supplemental log data (primary key) columns;
If DDL is enabled, then the following is also run:
SQL>alter database add supplemental log data (primary key) columns;
If the apply 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.
Are Materialized views supported?
Materialized views are not supported. You can replicate a materialized view, but the target must be an ordinary table.
How do we resend a plog?
A plog can be resend from the mine process to the apply process by using an internal engine command. Please see API and internal commands for more information.
The process is:
- Stop the Mine process
- In the console type (in this case the plogs to resend starts at 957):
dbvrep> ENGINE MINE RESET TO PLOG 957
- Restart Mine process
- Restart Apply process if this was stopped
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 apply 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 apply 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 apply 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 denote "any interface", so this setting means the process(es) listen on all network interfaces (loopback, ethernet, ...).
This is intended and correct value.
How to test if remote port is reachable
Use nz -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.