Simple DML operations:
Before we start, we should ensure, that replication is setup correctly and is running. Connect to dbvrep console using script start-console.sh on either source or target machine:
If everything went fine, you should see at the top of the console output that MINE and APPLY processes are running and applying changes from PLOG files:
| No Format |
|---|
MINE ISis running. Currently at plog 3229 and SCN 573985774447 (0106/2208/20152017 2321:0644:5650).
APPLY ISis running. Currently at plog 3229 and SCN 573979774443 (0106/2208/20152017 2321:0644:5550). |
Lets insert some simple row and check in console, how did the replication work.
...
Open any SQL tool, connect as repoe/repoe@TTORCL_SRCrepoe@SOURCE to source database and run following command:
| No Format |
|---|
|
REPOE@TTORCL_SRC>[oracle@source replicate]$ sqlplus repoe/repoe@SOURCE
SQL> insert into REPOE.CARD_DETAILS (card_id, customer_id, card_type, card_number, expiry_date, is_valid, security_code) values (CARD_DETAILS_SEQ.nextval, 1287, 'REPOE_TEST', 9102450385, sysdate, 'Y', 1000 );
1 row created.
REPOE@TTORCL_SRC>
SQL> commit;
Commit complete.
|
Now look at the dbvrep console, if the row was successfully replicated;
...
When we see in the console, that the row was sucessfully replicated to the target database, we should check, if it is correctly replicated. Connect to target database as user repoe/repoe and run following command:
| No Format |
|---|
|
[oracle@source replicate]$ sqlplus repoe/repoe@TARGET
SQL> set lines 200 pages 999
SQL> select * from REPOE.CARD_DETAILS where card_type='REPOE_TEST';
CARD_ID CUSTOMER_ID CARD_TYPE CARD_NUMBER EXPIRY_DATE I SECURITY_CODE
---------- ----------- ------------------------------ ----------- ------------------ - -------------
750005750004 1287 REPOE_TEST 9102450385 2225-JANMAY-1517 Y 1000
|
Simple DDL operation
Let's try a bit more complex command - Create table as select:
| No Format |
|---|
|
[oracle@source replicate]$ sqlplus repoe/repoe@SOURCE
REPOE@SOURCE> create table repoe.tables as select * from user_tables;
Table created.
|
See, what happened in the dbvrep console - run ./start-console.sh from $HOME/replicate directory:
| No Format |
|---|
|
/ Dbvisit Replicate 2.8.04(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 33 and SCN 788527 (06/08/2017 22:08:59).
APPLY is running. Currently at plog 33 and SCN 788507 (06/08/2017 22:08:54).
Progress of replication replicate:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CARD_DETAILS: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:08/06/2017 22:04:48/OK
REPOE.TABLES: 100% Mine:11/11 Unrecov:0/0 Applied:11/11 Conflicts:0/0 Last:08/06/2017 22:08:03/OK
-------------------------------------------------------------------------------------------------------------------------------------------- |
You can see, that the table was created on source and filled with 11 rows. The same table was created on target database under REPOE user and the 11 rows were successfully replicated.
You can check, if the rows were successfully replicated using following query under system user in target database:
| No Format |
|---|
|
SQL> conn system/manager@target
Connected.
SQL> select * from repoe.tables
minus
select * from repoe.tables@source
union all
select * from repoe.tables@source
minus
select * from repoe.tables
/
no rows selected
SQL> |
This query uses database link created before and checks for any rows, that are in source database and are not present in target database and vice versa. If it returns some rows, than something bad happened.
Batch DML operation:
Lets update all rows in REPOE.TABLES table on SOURCE database:
| No Format |
|---|
|
SQL> conn repoe/repoe@SOURCE
Connected.
SQL> update tables set LAST_ANALYZED=sysdate;
11 rows updated.
SQL> commit;
Commit complete. |
Again, lets check the dbvrep console:
| No Format |
|---|
|
| Dbvisit Replicate 2.8.04(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 33 and SCN 792004 (06/08/2017 22:21:17).
APPLY is running. Currently at plog 33 and SCN 791986 (06/08/2017 22:21:13).
Progress of replication replicate:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CARD_DETAILS: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:08/06/2017 22:04:48/OK
REPOE.TABLES: 100% Mine:22/22 Unrecov:0/0 Applied:22/22 Conflicts:0/0 Last:08/06/2017 22:20:53/OK
-------------------------------------------------------------------------------------------------------------------------------------------- |
From this output, you can see, that one command, that was run on source database and changes 11 rows, created 11 single row changes. Replicate is not replicating commands, but only rows changed.
You can again check for any discrepancies during replication by following command:
| No Format |
|---|
|
SQL> conn system/manager@target
Connected.
SQL> select * from repoe.tables
minus
select * from repoe.tables@source
union all
select * from repoe.tables@source
minus
select * from repoe.tables
/
no rows selected
SQL> |
Cleaning up
Lets drop the created table on source database:
| No Format |
|---|
|
SQL> conn repoe/repoe@source
Connected.
SQL> drop table repoe.tables;
Table dropped. |
From the dbvrep console, you can see, that the table disappeared from the output list:
| No Format |
|---|
|
\ Dbvisit Replicate 2.8.04(MAX edition) - Evaluation License expires in 29 days
MINE is running. Currently at plog 33 and SCN 827460 (06/09/2017 00:34:39).
APPLY is running. Currently at plog 33 and SCN 827430 (06/09/2017 00:34:33).
Progress of replication replicate:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CARD_DETAILS: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:08/06/2017 22:04:48/OK
--------------------------------------------------------------------------------------------------------------------------------------------
1 tables listed. |
The table was dropped on source, this command was replicated to target database and finally, it was unprepared from the Replication.