Занимаясь 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-утилита, но она будет требовать отсортированности строк сравниваемых файлов.
Если спул-файлы большие - ну, сортировка будет долгой и ресурсоёмкой.
О ограничениях такого подхода.
Понятно что выполнять сравнение таблицы и её таблицы реплики нужно либо при остановленной репликации, либо при отсутствии транзакций, на таблицу(и её реплику)
Ну т.е., практически, поскольку перекрывать транзакции от приклада, в проде, к реплицируемой таблице(ам) - вряд ли, в общем случае, разрешат, то:
репликацию таблицы в её реплику, на период времени в течении которого выполняется процедура сравнения надо будет останавливать.
После остановки репликации надо засекать — таблица-реплика, в терминах, например scn-значений бд-источника: до какого scn-а догнана, репликатом.
На стороне бд-источника, в спул-файл выгонять данные, от релевантной таблицы, надо с 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, именно для вот таких целей - сравнение, устранение расхождений в данных между таблицей и её репликой.
Слышал, то бывалых людей, что всё там здорово-спортивно, нативно умеет и учитывает партиционированность таблицы и таблицы-реплики.
Только что требует наличия и одинакового устройства ключа, на таблицу и её таблицу-реплику.
Спасибо за ваше время, внимание.
