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:
MINE is running. Currently at plog 29 and SCN 774447 (06/08/2017 21:44:50). APPLY is running. Currently at plog 29 and SCN 774443 (06/08/2017 21:44:50).
Lets insert some simple row and check in console, how did the replication work.
We are going to insert a row to the table REPOE.CARD_DETAILS. We will create a unique value of CARD_TYPE column in order to be able to check, whether the row was replicated or not.
Check on target database that the row does not exist on the target:
select count(*) from REPOE.CARD_DETAILS where card_type='REPOE_TEST';
COUNT(*)
----------
0
Now insert a row into REPOE.CARD_DETAILS table.
Open any SQL tool, connect as repoe/repoe@SOURCE to source database and run following command:
[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. SQL> commit; Commit complete.
Now look at the dbvrep console, if the row was successfully replicated;
Progress of replication repoe:MINE->APPLY: total/this execution ------------------------------------------------------------------------------------------------------------------ REPOE.CARD_DETAILS: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:22/01/2015 23:23:54/OK ------------------------------------------------------------------------------------------------------------------
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:
[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
---------- ----------- ------------------------------ ----------- ------------------ - -------------
750004 1287 REPOE_TEST 9102450385 25-MAY-17 Y 1000
Simple DDL operation
Let's try a bit more complex command - Create table as select:
[oracle@source replicate]$ sqlplus repoe/repoe@TARGET 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:
/ 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:
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:
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:
| 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:
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:
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:
\ 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.
Add Comment