Verifying the data transfer on the target system
This recipe will show you how to verify data transfer on a target system.
Getting ready
For this recipe we will use the continuous replication setup for the SCOTT schema done in Setting up a simple GoldenGate replication configuration between two single node databases earlier in this chapter.
How to do it...
Perform the following steps in the source database:
- Check the number of employees whose salary is greater than 3000:
SQL> select * from EMP where sal>3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ----- --- --- -------- --- ---- ------ 7839 KING PRESIDENT 17-NOV-81 5000 10
- Perform some updates in the source database. Here we increase the salary for all employees by 1000:
SQL> update SCOTT.EMP set sal=sal+1000; 14 rows updated. SQL> commit; Commit complete.
- Check the Extract process statistics:
GGSCI (prim1-ol6-112.localdomain) 3> stats EGGTEST1 hourly Sending STATS request to EXTRACT EGGTEST1 ... Start of Statistics at 2012-10-18 10:58:58. Output to /u01/app/ggate/dirdat/st: Extracting from SCOTT.EMP to SCOTT.EMP: *** Hourly statistics since 2012-10-18 10:56:09 *** Total inserts 0.00 Total updates 14.00 Total deletes 0.00 Total discards 0.00 Total operations 14.00 End of Statistics.
- Check the Datapump Process Statistics:
GGSCI (prim1-ol6-112.localdomain) 2> stats pggtest1 hourly Sending STATS request to EXTRACT PGGTEST1 ... Start of Statistics at 2012-10-18 11:03:15. Output to /u01/app/ggate/dirdat/rt: Extracting from SCOTT.EMP to SCOTT.EMP: *** Hourly statistics since 2012-10-18 10:57:13 *** Total inserts 0.00 Total updates 14.00 Total deletes 0.00 Total discards 0.00 Total operations 14.00 End of Statistics.
Perform the following steps in the target database:
- Check the Replicat Process Statistics:
GGSCI (stdby1-ol6-112.localdomain) 2> stats rggtest1 hourly Sending STATS request to REPLICAT RGGTEST1 ... Start of Statistics at 2012-10-18 11:05:05. Replicating from SCOTT.EMP to SCOTT.EMP: *** Hourly statistics since 2012-10-18 10:58:07 *** Total inserts 0.00 Total updates 14.00 Total deletes 0.00 Total discards 0.00 Total operations 14.00 End of Statistics.
- Again, check the number of employees whose salary is greater than 3000:
SQL> select * from emp where sal>3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ----- --- --- -------- --- ---- ------ 7566 JONES MANAGER 7839 02-APR-81 3975 20 7698 BLAKE MANAGER 7839 01-MAY-81 3850 30 7782 CLARK MANAGER 7839 09-JUN-81 3450 10 7788 SCOTT ANALYST 7566 09-DEC-82 4000 20 7839 KING PRESIDENT 17-NOV-81 6000 10 7902 FORD ANALYST 7566 03-DEC-81 4000 20 6 rows selected.
How it works…
In this recipe, we perform an update in the source environment and verify the changes in the target environment. We use the stats command to see the volume statistics of a Replicat process.
The stats command only lists the volume statistics for the tables for which the process found any changes. The stats command has the following syntax:
STATS {EXTRACT | REPLICAT | ER} {<group | wildcard>}[TABLE {<name | wildcard>}] [TOTAL | DAILY | HOURLY | LATEST]
for example:
STATS REPLICAT RGGTEST1 TABLE SCOTT.EMP HOURLY
There's more...
You can use the stats command to list the summary of the volume statistics with the following syntax:
STATS {EXTRACT | REPLICAT | ER} {<group | wildcard>},TOTALSONLY <table>
for example:
STATS REPLICAT RGGTEST1 TOTALSONLY SCOTT.*
Another popular use of the stats command is to check the progress rate of a GoldenGate process:
STATS REPLICAT RGGTEST1 REPORTRATE HR
This command will show the hourly, daily, and total (since startup) processing summary for that replicat as follows:
*** Total statistics since 2012-10-18 11:56:35 *** Total inserts/hour: 0.00 Total updates/hour: 37.66 Total deletes/hour: 0.00 Total discards/hour: 0.00 Total operations/hour: 37.66