Занимаясь goldengate-репликацией столкнулся с необходимостью выполнить сравнение таблицы, в исходной бд и её таблицы-реплики, в бд-приёмнике.

Для случая когда таблица и таблица-реплика  обе имеют, одинаково устроенные ключи (как оно и д.б., по идее), есть замечательный пакет dbms_comparison. А ещё (и более правильно): oracle veridata;

Однако, что делать, если ключей нет никаких, нет и unique-индекса, с not-null.
А такое — бывает.

Тут нужно велосипедить какое то решение.

Или договариваться с заказчиками репликации чтобы — добавляли ключи, на таблицы и им релевантные таблицы-реплики.

Добавление ключей — не всегда желательно, с т.з. заказчика, например потому что прикладной код может нехорошо отреагировать, на появление в таблице/таблице-реплике нового столбца.

Это можно обойти, сделав новый, ключевой столбец не видимым, gg-такие ключевые столбцы: поддерживает, это интересный способ, но о нём — в другой раз.

В этой статье — про велосипед.

Ещё, подразумеваю, здесь и далее, что таблица и её реплика - устроены одинаково, в смысле набора столбцов.

И ещё подразумеваю что типы данных столбцов - скалярные.

Итак первое что приходит в голову: заспулить строки таблицы/таблицы-реплики в текстовый файл, на стороне исходной бд и бд-приёмника, получить хеши с этих спул-файлов, хеши - сравнить.

BTW: в 21с добавлена ф-ция checksum, отдельный и интересный вопрос - как оно, с большими таблицами/партициями.

Наверное - не очень.
Спулить, как то так:

v_select_list=$( echo -e "STANDARD_HASH( "$v_select_list", 'MD5')" ) 
cat << __EOF__ > "$v_sql_script" 
whenever sqlerror exit failure 
set newp none feedback off pagesize 0 head off linesize 1024 trim on tab on sqlpluscompatibility 12.2 arraysize 5000 appinfo qqq1 
set termout off 
alter session set nls_date_format ='MM/DD/YYYY HH24:MI:SS'; 
alter session set nls_timestamp_format ='MM/DD/YYYY HH24:MI:SS.FF9'; 
alter session set nls_timestamp_tz_format ='MM/DD/YYYY HH24:MI:SS.FF9 TZH:TZM'; alter session set nls_numeric_characters = '. '; 
set termout on 
select `echo -n "$v_select_list" | sed 's/||/||\n/g'` as col1 
from ${v_trgt_owner}.${v_trgt_tabname} 
order by ${v_order_list}; 
__EOF__ 
v_datfile="${v_datadir}/${v_trgt_owner}.${v_trgt_tabname}.dat" 
$ORACLE_HOME/bin/sqlplus -S -L / as sysdba << __EOF__ > "${v_datfile}" 
@${v_sql_script} 
exit 
__EOF__

У этого спулинга есть недостаток, об этом ниже.

Тут, обращаю ваше внимание, что в спул-файл выводится не сама строка, а её хеш.
Это, потом, будет важно, обращу внимание, отдельно, на это, ниже.

Также: надо обрабатывать null-ы, маппить их, однообразно, в какое то определённое значение.

Какой именно хеш вычислять и как - вопрос дискутируемый, ну я взял md5 - побыстрее и дешевле, относительно того что ещё предлагает standart_hash-функция.

В этом смысле интересно как как dbms_comparison-пакет делает обработку таблиц - устраняет null-ы и получает хеш строки, подсмотрел из 10046-трассировки его compare-задачи:

SELECT /*+ REMOTE_MAPPED */ q.wb1, min(q."OBJECT_ID") min_range1,
  max(q."OBJECT_ID") max_range1, count(*) num_rows, sum(q.s_hash)
  sum_range_hash
FROM
 (SELECT /*+ FULL(s) REMOTE_MAPPED */  width_bucket(s."OBJECT_ID", :scan_min1,
   :scan_max_inc1, :num_buckets) wb1, s."OBJECT_ID",
  ora_hash(NVL(to_char(s."OBJECT_ID"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."OWNER"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."OBJECT_NAME"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."SUBOBJECT_NAME"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."DATA_OBJECT_ID"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."OBJECT_TYPE"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."CREATED"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."LAST_DDL_TIME"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."TIMESTAMP"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."STATUS"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."TEMPORARY"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."GENERATED"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."SECONDARY"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."NAMESPACE"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."EDITION_NAME"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."SHARING"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."EDITIONABLE"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."ORACLE_MAINTAINED"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."APPLICATION"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."DEFAULT_COLLATION"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."DUPLICATED"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL((s."SHARDED"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."CREATED_APPID"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."CREATED_VSNID"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."MODIFIED_APPID"), 'ORA$STREAMS$NV'), 4294967295,
  ora_hash(NVL(to_char(s."MODIFIED_VSNID"), 'ORA$STREAMS$NV'), 4294967295, 0))
  )))))))))))))))))))))))) s_hash FROM
  "TESTUSER"."TESTTAB_2"@TO_TESTDB.WORLD s WHERE (s."OBJECT_ID">=
  :scan_min1 AND s."OBJECT_ID"<=:scan_max1) ) q GROUP BY q.wb1 ORDER BY q.wb1

У себя, в v_select_list-переменную, до её хеширования, я готовлю сконкатенированный список столбцов таблицы, nullable-стобцы оборачиваю в nvl, для замены null-ов, а определённое константное значение;
Сортировка делается либо по столбц(у|ам) первичного ключа, отсортированным по возрастанию по значению dba_cons_columns.position;
Либо, если ключей нет - по отсортированному списку столбцов таблицы.
Главное что - сортировка строк выборки: делается одинаковая, для таблицы и её реплики.

Далее: на вот такие спул-файлы, которые получаться от спулинга строк таблицы и её таблицы-реплики, потом вычислить md5-хеш, хеши — сравнить с друг другом, вот, казалось бы и всё.
Тут, правда, немедленно выясниться что сортировка — зло: на достаточно больших таблицах: всё будет задерживаться очень надолго, и занимать ресурсы субд: цпу, pga, темп-ы.
А без сортировки тут - нельзя, md5-хеш будет давать разные значения, даже если спул файлы отличаются только порядком строк.
Значит нужно какое-то такое вычисление дайджеста, со спул-файлов, которое не чувствительно к порядку следования строк в файле.
Тогда, если строки, в обоих файлах, действительно — одинаковые, по своему кол-ву и по данным в ним, дайжест — должен получится одинаковым.
Если строки — разные, по кол-ву и/или по данным в полях строк — дайджест должен получится разным, для спул-файлов.

Оказывается что есть такая техника вычисления дайджеста, описана в этой статье — нужно, побитово, xor-ить строки в спул-файле, друг с другом.
Единственное что, для работы в реальных условиях организации, то, чем, в статье, предлагают выполнять xor-инг: UTL_RAW.BIT_XOR — это будет очень долго.

В общем, лучше по этому пути не ходить, я так это понял и весь процесс создания-обработки спулл-файлов, вынес вне базы, в шелл-скрипт и накостылил такую питон-вставку (да простят меня try-питонисты), для шелл-скрипта, для вычисления xor-ов:

function func1(){
local v_file="$1"
"$PYTHON" << __EOF__
def change_to_be_hex(s):
    return int(s,base=16)

def xor_two_str(str1,str2):
    a = change_to_be_hex(str1)     
    b = change_to_be_hex(str2)     
    return hex(a ^ b).rstrip("L").lstrip("0x")

f="$v_file"
infile=open(f,'r')
x=1 
b="" 
for i in infile:
    if x==1:
        b=i
    if x>=2:
        b=xor_two_str(i, b)
    if b=="":
        b="0"
    x=x+1
infile.close()
if x==2:
    b=change_to_be_hex(b)
print(b) 
exit()
__EOF__
}
export -f func1

В файле, имя которого аргументом отдаётся этой ф-ции: в каждой строке md5-хеш, это спул файл, полученный с помощью heredoc-обёртки над склпюс-ом, выше.

И да: видел на стэковерфлоу дискуссии про то как более спортивно вычислять xor-ы вообще и для строк в частности.

Мне надо было закодить прототип и вот этого питон-кода - вполне хватило.
При этом, каким бы ни был спортивным код который будет делать xor-инг строк, всегда может найтись достаточно большая таблица, с которой получится достаточно большой спул файл.

Т.е. бороться, за меньший elapsed-time xor-инга, считаю что, нужно привлечением распараллеливания, в этой части обработки данных...

Например — если спул-файл получается большой: split-ить его на куски: файлы с небольшим кол-вом строк.

И обрабатывать эти, маленькие файлы, xor-ить в них строки, друг с другом.

Сохранять xor-значения куда то, в файл, или бд (sqlite — вполне себе для этого подходит).А потом полученные xor-значения — xor-ить друг с другом. По свойствам xor-операции получится, в итоге, ровно то значение которое получилось бы при честном xor-инге строк исходного большого спул-файла, только — намного быстрее.

split --lines=${v_chunk_size} --suffix-length=5 -d "${v_datfile}" "${v_datadir}/${v_prefix}"
v_cmd="find ${v_datadir} -type f -name '${v_prefix}*'"
eval "$v_cmd" | xargs -d "\n" -n 1 -P ${v_xdop} -I {} bash -c func1\ \{\} > "$v_tempfile"
v_digest=$( func1 "$v_tempfile" )
v_cmd="find ${v_datadir} -type f -name '${v_prefix}*' -delete"
eval "$v_cmd"
cat /dev/null > "$v_tempfile"

Т.о., на сторонах репликации, спулим таблицу и её реплику, в спул-файл, получаем для спул-файлов xor-дайджесты.

Делаем такое для того набора таблиц/реплик, в данной схеме репликации, которые хотим проверить на расхождение данных.

Выписываем куда то соотношение таблиц и их xor-дайджестов.

Потом эту информацию сделать доступной на второй стороне репликации и там сравнить с, аналогично, полученной информацией по, что там будет - таблицы, или реплики таблиц, учитывая — какая таблица-реплика к какой таблице в исходной бд относится.

Если пара: таблица/таблица-реплика — действительно имеют одинаковое кол-во строк и одинаковые данные в строках - xor-дайджесты: совпадут.

Или не совпадут, если что то (кол-во строк и/или данные в строках) - разные.

Если, вместе с информацией о сопоставлении xor-дайджестов именам таблиц/реплик, сделать доступными, на вторую сторону репликации, ещё и спул-файлы то тогда, для тех пар таблица/реплика, у которых xor-дайджесты оказались разными, появляется возможность, глядя на кол-во общих и уникальных хешей, в их спул-файлах, оценить - а на сколько строк расходятся таблица и её реплика.

Тут, правда, со спортивностью такой оценки всё не так радужно.

Есть например comm-утилита, но она будет требовать отсортированности строк сравниваемых файлов.

Если спул-файлы большие - ну, сортировка будет долгой и ресурсоёмкой.

О ограничениях такого подхода.

Понятно что выполнять сравнение таблицы и её таблицы реплики нужно либо при остановленной репликации, либо при отсутствии транзакций, на таблицу(и её реплику)
Ну т.е., практически, поскольку перекрывать транзакции от приклада, в проде, к реплицируемой таблице(ам) - вряд ли, в общем случае, разрешат, то:

1. репликацию таблицы в её реплику, на период времени в течении которого выполняется процедура сравнения надо будет останавливать.

2. После остановки репликации надо засекать — таблица-реплика, в терминах, например scn-значений бд-источника: до какого scn-а догнана, репликатом.

3. На стороне бд-источника, в спул-файл выгонять данные, от релевантной таблицы, надо с as of scn-клаузой, в которую подставлять найденный, на стороне реплики, scn

Тут, думаю, всё понятно, почему именно так.

И тут же кроется ограничение на технику - доступное для выполнения сравнения время: лимитируется допустимым временем простоя репликации.

Основные временные затраты возникают на хешировании строк при спулинге, xor-инге, и, возможно сравнении спул-файлов, если захочется количественную оценку на .кол-во разных строк в таблице и/или таблице реплике.

С временными затратами на спулинг, полагаю, можно бороться например распараллеливанием спулинга и/или притягивать какой то, в вычислительном смысле, значительно более простой хеш, ну например вот тот же вариант хеширования строки который в dbms_сomparison-пакет.

С распараллеливанием, тут, что имею в виду.

Выполнять запрос, который в heredoc-обёртке, в примере выше, в параллельном режиме - формально можно и оно будет работать.

Но технически всё упрётся в передачу данных от базы клиенту, запрос в базе будет обслуживаться много быстрее, в несколько PX-серверов, чем склплюс будет фетчить к себе выборку.

Тут, видимо, так надо поступать: если надо получить md5-хеши от достаточно большой таблицы (или партиции таблицы) то получить, в субд, параллельным CTAS-ом, с этой, большой таблицы, её копию, в виде хеш-секционированной таблицы с достаточно большим кол-вом партиций.

Благодаря хеш-секционированию размеры партиций, в таблице-копии, должны будут получится примерно одинаковыми.

Кроме того, поскольку CTAS можно ещё и as of scn сделать - всё равно делать.
Ну и потом обрабатывать, в параллель, партиции этой таблицы копии: спулить с них md5-хеши строк в спул-файл - отдельный и свой, для каждой конкретной партиции.
А когда будут обработаны все партиции, от таблицы-копии, таблицу-копию: удалить.
Про xor-обработку больших файлов с md5-хешам, уже говорил выше, по моему - только параллелить.

Со спортивным выщемлением либо кол-ва общих, для таблицы/реплики, хешей, из их спул-файлов, либо кол-ва уникальных хешей - тут не знаю как быть.

Сортировка спул-файлов тут - выглядит неизбежной и дальше либо comm, либо join утилиты ОС-и.

Либо грузить эти спул файлы в базу какую то и делать какой нибудь hash join и получать кол-во общих хешей, если и загрузка и HJ-таблиц субд, в совокупности, будут дешевле сортировки строк файлов и обработки их comm-утилитой.

Остановку репликата и получение scn-а, на котором оно остановилось, я делаю так:
На стороне бд-источника заведена спец-таблица и прописано в конфиг экстрактора:

table ggate.controltable
, tokens( commit_scn=@GETENV('TRANSACTION', 'CSN') )
, eventactions( stop )
;

На стороне бд-приёмника, в конфиге репликта:

map ggate.controltable, target ggate.controltable
, colmap(usedefaults, stop_scn=@TOKEN('commit_scn'))
, eventactions( stop )
;

В моём случае: я довёл решение до работающего баш скрипта, выполнял его несколько раз на проде.

Для моего случая, самые большие таблицы: 100-150Гб, средний размер таблицы около 10Гб, таблиц в схеме репликации несколько десятков, ближе к сотне - оно вполне бодро обрабатывает такое, даже не целый рабочий день.

Была возможность сверять результаты проверки с результатами от методы сравнения данных в таблицах/репликах от разработчиков (она у них есть, но дорогая в выполнении и "на ручном приводе") - совпадало.

Но, после этого, таки получилось договорится, с заказчиками репликации, про создание ключей на таблицы/реплики, которые были поставлены в репликацию бесключевыми и необходимость в каком то кастомном велосипеде - отпала, теперь пользуюсь dbms_comparison-пакетом. Благо что, повезло и почти все таблицы - со скалярными и поддерживаемыми, этим пакетом, типами данных в столбцах.

Есть, в природе, такой комбайн продукт: oracle veridata, именно для вот таких целей - сравнение, устранение расхождений в данных между таблицей и её репликой.
Слышал, то бывалых людей, что всё там здорово-спортивно, нативно умеет и учитывает партиционированность таблицы и таблицы-реплики.

Только что требует наличия и одинакового устройства ключа, на таблицу и её таблицу-реплику.

Спасибо за ваше время, внимание.