In previous blog posts as we see Oracle GoldenGate Veridata does uses MySQL database for repository purpose which helps to Veridata to do all the background operations such as Data Comparision or storing the data of jobs, grouping etc.
In this blog post we will tweak the repository database to explore what information we can also see from the background. Personally this veridata architecture i Liked a lot – Veridata does uses MySQL Database. As part of licensing it is applicable for only veridata and no separate license for MySQL database.
Let’s connect to the MySQL Database as below, I have veridata as username and we need to enter the password of it.
[oracle@gghub ~]$ /ogg/veridata/mysql-commercial-8.0.34-linux-glibc2.17-x86_64-minimal/bin/mysql -u veridata -p -h 127.0.0.1 -P 3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.34-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
We have successfully connected tot he MySQL prompt and we will view what databases are preconfigured in it.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| VDUSER_VERIDATA |
| information_schema |
| performance_schema |
+--------------------+
3 rows in set (0.03 sec)We have three databases and the repository database is VDUSER_VERIDATA and remaining are internal databases.
We will switch to our database VDUSER_VERIDATA, Please note that this is only for select queries but not for write operations
mysql> use VDUSER_VERIDATA
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> From this database we can see what tables were created as part of the veridata configuration.
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_VDUSER_VERIDATA |
+---------------------------+
| COMPARE_COLUMNS |
| COMPARE_DP_DETAILS |
| COMPARE_PAIRS |
| COMPARE_PAIR_DELTAS |
| COMPARE_PAIR_RUNS |
| COMPARE_PAIR_RUN_DETAILS |
| COMPARE_PAIR_RUN_NAMES |
| COMPARE_PAIR_STATS |
| CONNECTIONS |
| CONNECTION_PARAMS |
| CONNECTION_TYPE_MAPPINGS |
| ENSCRIBE_INFO |
| FAVORITES |
| FAVORITES_PARAMS |
| FAVORITES_SHORTCUTS |
| FORMATS |
| GROUPS |
| GROUP_RUNS |
| GROUP_RUN_DETAILS |
| GROUP_RUN_NAMES |
| JOBS |
| JOB_GROUPS |
| JOB_RUNS |
| JOB_RUN_DETAILS |
| JOB_RUN_NAMES |
| PARAM_NAMES |
| PARTITION_INFO |
| PROFILES |
| PROFILE_DETAILS |
| REPAIR_GROUPS |
| REPAIR_JOBS |
| REPAIR_PAIRS |
| REPAIR_ROWS |
| ROLES |
| RUN_KEY |
| SEQUENCE_TABLE |
| TABLE_INFO |
| USERGROUPS |
| USERGROUPS_ROLES |
| USERS |
| USERS_USERGROUPS |
| USER_DETAILS |
| USER_PREFERENCES |
| USER_PREFERENCE_DETAILS |
| VERIDATA_CONTROL |
| flyway_schema_history |
+---------------------------+
46 rows in set (0.01 sec)
Using the above tables, we will view few of them to check what information it is providing.
Viewing the Jobs
mysql> SELECT * FROM JOBS LIMIT 5;
+-------+--------+-------------+--------------+------------+--------+------------------------+------------------------+------------+---------------------+
| name | job_id | src_conn_id | targ_conn_id | profile_id | status | create_date | modify_date | num_groups | description |
+-------+--------+-------------+--------------+------------+--------+------------------------+------------------------+------------+---------------------+
| GGJOB | 1001 | NULL | NULL | 1 | 0 | 2025-12-06 15:32:18.13 | 2025-12-06 15:32:18.00 | 1 | AVUKWEB Comparision |
+-------+--------+-------------+--------------+------------+--------+------------------------+------------------------+------------+---------------------+
1 row in set (0.00 sec)
mysql>The job we created from the WEB UI we can also view from the table JOBS.
Viewing the Connections
mysql> SELECT * FROM CONNECTIONS LIMIT 5;
+--------+---------------+--------+----------+------+-----------------+--------------------+------------------------+------------------------+
| name | connection_id | type | mgr_name | port | description | num_connect_params | create_date | modify_date |
+--------+---------------+--------+----------+------+-----------------+--------------------+------------------------+------------------------+
| OGGDB1 | 1001 | oracle | ggate1 | 8831 | Source Database | 7 | 2025-12-06 13:36:42.68 | 2025-12-06 15:26:40.00 |
| TGDB | 1002 | oracle | ggate1 | 8832 | Target Database | 7 | 2025-12-06 15:15:41.06 | 2025-12-06 15:24:07.00 |
+--------+---------------+--------+----------+------+-----------------+--------------------+------------------------+------------------------+
2 rows in set (0.00 sec)
mysql>It’s interesting now? we can see the source and target database connections with the port information as well.
Veridata Roles
mysql> select * from ROLES;
+----+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| id | name | description | created_at | modified_at |
+----+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| 0 | Administrator | The user with this role can configure, run, and monitor comparison and repair jobs both in Web and command-line interface (CLI) in addition to performing user management functions and server configuration. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 1 | Super User | The user with this role can configure, run, monitor comparison, and repair jobs from the Web user interface. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 2 | Detail Monitoring Operator | The user with this role can view configurations, monitor jobs, and view comparison and out-of-sync reports. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 3 | Monitoring Operator | The user with this role can view configurations, monitor jobs, and view comparison reports. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 4 | Repair Operator | The user with this role can repair the out-of-sync jobs. This is typically used in conjunction with other roles, such as Monitoring Operator or Detail Monitoring Operator or Command Line Operator | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 5 | Command Line Operator | The user with this role can configure, run, monitor comparison jobs from the command-line interface (CLI). Example: Vericom utility. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
| 6 | Job Operator | The user with this role can run comparison jobs. This is typically used in conjunction with other roles such as Monitoring Operator or Detail Monitoring Operator. | 2025-12-06 12:32:19 | 2025-12-06 12:32:19 |
+----+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
7 rows in set (0.00 sec)
mysql>
This shows what roles available and its properties.
Connection Parameters
mysql> select * from CONNECTION_PARAMS;
+---------------+--------------+----------+-------------+------------+---------+---------+
| connection_id | param_number | param_id | param_subid | param_type | int_val | str_val |
+---------------+--------------+----------+-------------+------------+---------+---------+
| 1001 | 0 | 1 | 0 | S | 0 | avukweb |
| 1001 | 0 | 3 | 0 | I | 1000 | NULL |
| 1001 | 0 | 5 | 0 | S | 0 | true |
| 1001 | 0 | 6 | 0 | I | 3600 | NULL |
| 1001 | 0 | 10 | 0 | S | 0 | false |
| 1001 | 0 | 11 | 0 | S | 0 | true |
| 1001 | 0 | 12 | 0 | S | 0 | true |
| 1002 | 0 | 1 | 0 | S | 0 | avukweb |
| 1002 | 0 | 3 | 0 | I | 1000 | NULL |
| 1002 | 0 | 5 | 0 | S | 0 | true |
| 1002 | 0 | 6 | 0 | I | 3600 | NULL |
| 1002 | 0 | 10 | 0 | S | 0 | false |
| 1002 | 0 | 11 | 0 | S | 0 | true |
| 1002 | 0 | 12 | 0 | S | 0 | true |
+---------------+--------------+----------+-------------+------------+---------+---------+
14 rows in set (0.00 sec)
mysql>
This shows all the connection parameters of both source and target as well.
Viewing Compare Pairs
mysql> select group_id,name,src_table_name,targ_table_name from COMPARE_PAIRS;
+----------+-------------------------+----------------+-----------------+
| group_id | name | src_table_name | targ_table_name |
+----------+-------------------------+----------------+-----------------+
| 1001 | ACCOUNTS=ACCOUNTS | ACCOUNTS | ACCOUNTS |
| 1001 | DEPARTMENTS=DEPARTMENTS | DEPARTMENTS | DEPARTMENTS |
| 1001 | EMPLOYEES=EMPLOYEES | EMPLOYEES | EMPLOYEES |
| 1001 | FINANCE=FINANCE | FINANCE | FINANCE |
| 1001 | ORDERS=ORDERS | ORDERS | ORDERS |
+----------+-------------------------+----------------+-----------------+
5 rows in set (0.00 sec)
Repository Version & Schema Status
mysql> SELECT * FROM flyway_schema_history ORDER BY installed_rank;
+----------------+---------+------------------------+------+----------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
+----------------+---------+------------------------+------+----------------------------------+-------------+--------------+---------------------+----------------+---------+
| 1 | 1.0 | create repo 23c tables | SQL | V1.0__create_repo_23c_tables.sql | -1319277763 | veridata | 2025-12-06 12:32:18 | 1801 | 1 |
| 2 | 1.1 | create user | SQL | V1.1__create_user.sql | 1893941771 | veridata | 2025-12-06 12:32:19 | 492 | 1 |
+----------------+---------+------------------------+------+----------------------------------+-------------+--------------+---------------------+----------------+---------+
2 rows in set (0.00 sec)From this we can view the version and also the schema status.
Viewing Historical Runs
mysql> select job_id,COMPARE_PAIR_ID, STATUS, start_date,stop_date from COMPARE_PAIR_RUNS;
+--------+-----------------+--------+------------------------+------------------------+
| job_id | COMPARE_PAIR_ID | STATUS | start_date | stop_date |
+--------+-----------------+--------+------------------------+------------------------+
| 1001 | 1001 | F | 2025-12-06 15:33:05.45 | 2025-12-06 15:33:22.32 |
| 1001 | 1002 | F | 2025-12-06 15:33:05.45 | 2025-12-06 15:33:18.80 |
| 1001 | 1003 | F | 2025-12-06 15:33:05.46 | 2025-12-06 15:33:23.99 |
| 1001 | 1004 | F | 2025-12-06 15:33:05.46 | 2025-12-06 15:33:18.82 |
| 1001 | 1005 | F | 2025-12-06 15:33:18.81 | 2025-12-06 15:33:23.61 |
+--------+-----------------+--------+------------------------+------------------------+
5 rows in set (0.00 sec)
mysql>From this query we can view the Jobs when they were started and ended.
Like these there are various tables where we can explore from the backend.
