Validating database objects after migration using AWS RDS,SCT and AWS DMS
AWS provides several tools and services that provide a pre-migration checklist and migration assessments.
You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another.
AWS Database Migration Service (AWS DMS) makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores.
The following query uses the ntile function to identify the column with the exact number of rows in each partition. It also provides the max value, which can be used as a range for your table mapping to load it in parallel. You can use this even if your column is an UUID.
select /*+ parallel */ min(ID),max(ID), count(*), nt
from ( select ID, ntile(16) over (order by ID) nt
from VIQASH.DUMMY_TABLE) group by nt order by nt;
Validating objects
Validate database objects after migrating from Oracle to Amazon Aurora PostgreSQL
Step 1 - Validate packages
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 1 - Oracle source database:
SELECT object_name AS package_name FROM all_objects WHERE object_type = 'PACKAGE' AND owner = upper('your_schema') ORDER BY upper(object_name);
QUERY 2 - Amazon Aurora PostgreSQL:
SELECT upper(schema_name) AS package_name FROM information_schema.schemata
WHERE schema_name not in('pg_catalog','information_schema', lower('your_schema'))
ORDER BY upper(schema_name);
Step 2 - Validate tables
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 3 - Oracle:
SELECT count(1) AS tables_cnt FROM all_tables WHERE owner = upper('your_schema');
QUERY 4 - Amazon Aurora PostgreSQL:
SELECT count(1) AS tables_cnt FROM pg_tables WHERE schemaname = lower('your_schema');
Step 3 - Validate views:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 5 - Oracle:
SELECT count(1) AS views_cnt FROM all_views WHERE owner = upper('your_schema');
QUERY 6 - Amazon Aurora PostgreSQL:
SELECT count(1) AS views_cnt FROM pg_views WHERE schemaname = lower('your_schema');
Step 4 - Validate sequences:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 7 - Oracle:
SELECT count(1) AS sequence_cnt FROM all_sequences WHERE sequence_owner = upper('your_schema');
QUERY 8 - Amazon Aurora PostgreSQL:
SELECT count(1) AS sequence_cnt FROM information_schema.sequences WHERE sequence_schema = lower('your_schema');
Step 5 - Validate triggers:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 9 - Oracle:
SELECT owner AS schema_name, trigger_name, table_name, triggering_event, trigger_type
FROM ALL_TRIGGERS WHERE owner = upper('your_schema') ORDER BY trigger_name;
QUERY 10 - Amazon Aurora PostgreSQL:
SELECT
upper(trigger_schema) AS schema_name,
upper(trigger_name) AS trigger_name,
upper(event_object_table) AS table_name,
string_agg(upper(event_manipulation), ' OR ' ORDER BY CASE WHEN event_manipulation = 'INSERT' THEN 1 WHEN event_manipulation = 'UPDATE' THEN 2 ELSE 3 END) AS triggering_event,
upper(action_timing) || ' ' || CASE WHEN action_orientation = 'ROW' THEN 'EACH ROW' ELSE action_orientation END AS trigger_type
FROM information_schema.triggers
WHERE trigger_schema = lower('your_schema')
GROUP BY trigger_schema, trigger_name, event_object_table, action_timing, action_orientation
ORDER BY upper(trigger_name);
Step 6 - Validate primary keys:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 11 - Oracle:
SELECT owner AS schema_name,
table_name, constraint_name AS object_name, 'PRIMARY KEY' AS object_type
FROM all_constraints WHERE owner = upper('your_schema') AND constraint_type = 'P';
QUERY 12 - Amazon Aurora PostgreSQL:
SELECT upper(n.nspname) AS schema_name,
trim(upper(split_part(conrelid::regclass::varchar, '.', 2)), '"') AS table_name,
upper(conname::varchar) AS object_name,
'PRIMARY KEY' AS object_type
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype in ('p')
AND n.nspname = lower('your_schema')
Step 7 - Validate indexes:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 13 - Oracle:
WITH cols AS (
SELECT idx.owner AS schema_name, idx.table_name, idx.index_name, cols.column_name, cols.column_position, idx.uniqueness, decode(cols.descend, 'ASC', '', ' '||cols.descend) descend
FROM ALL_INDEXES idx, ALL_IND_COLUMNS cols
WHERE idx.owner = cols.index_owner AND idx.table_name = cols.table_name AND idx.index_name = cols.index_name
AND idx.owner = upper('your_schema')
),
expr AS (
SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
, extractValue(xs.object_value, '/ROW/INDEX_NAME') AS index_name
, extractValue(xs.object_value, '/ROW/COLUMN_EXPRESSION') AS column_expression
, extractValue(xs.object_value, '/ROW/COLUMN_POSITION') AS column_position
FROM (
SELECT XMLTYPE(
DBMS_XMLGEN.GETXML( 'SELECT table_name, index_name, column_expression, column_position FROM ALL_IND_EXPRESSIONS WHERE index_owner = upper(''your_schema'') '
||' union all SELECT null, null, null, null FROM dual '
)
) AS xml FROM DUAL
) x
, TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
SELECT
cols.schema_name,
cols.table_name,
cols.index_name AS object_name,
'INDEX' AS object_type,
replace('CREATE'|| decode(cols.uniqueness, 'UNIQUE', ' '||cols.uniqueness) || ' INDEX ' || cols.index_name || ' ON your_schema.' || cols.table_name || ' USING BTREE (' ||
listagg(CASE WHEN cols.column_name LIKE 'SYS_N%' THEN expr.column_expression || cols.descend ELSE cols.column_name || cols.descend END, ', ') within group(order by cols.column_position) || ')', '"', '') AS condition_column
FROM cols
LEFT OUTER JOIN expr ON cols.table_name = expr.table_name
AND cols.index_name = expr.index_name
AND cols.column_position = expr.column_position
GROUP BY cols.schema_name, cols.table_name, cols.index_name, cols.uniqueness;
QUERY 14 - Amazon Aurora PostgreSQL:
SELECT upper(schemaname) AS schema_name,
upper(tablename) AS table_name,
upper(indexname) AS object_name,
'INDEX' AS object_type,
upper(replace(indexdef, '"', '')) AS condition_column
FROM pg_indexes
WHERE schemaname = lower('your_schema');
Step 8 - Validate check contraints:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 15 - Oracle:
WITH ref AS (
SELECT extractValue(xs.object_value, '/ROW/OWNER') AS schema_name
, extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
, extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME') AS object_name
, extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS condition_column
, extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name
FROM (
SELECT XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT cons.owner, cons.table_name, cons.constraint_name, cons.search_condition, cols.column_name
FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols
WHERE cons.owner = cols.owner AND cons.table_name = cols.table_name AND cons.constraint_name = cols.constraint_name
AND cons.owner = upper(''your_schema'') AND cons.constraint_type = ''C'' '
)
) AS xml FROM DUAL
) x
, TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
SELECT schema_name||'.'||table_name AS table_name,
object_name,
constraint_type,
trim(upper(replace(check_condition, '"', ''))) AS check_condition
FROM (
SELECT
schema_name,
table_name,
object_name,
'CHECK' AS constraint_type,
condition_column AS check_condition
FROM ref
UNION
SELECT
owner AS schema_name,
table_name,
'SYS_C0000'||column_id AS object_name,
'CHECK' AS constraint_type,
'"'||column_name||'" IS NOT NULL' AS check_condition
FROM all_tab_columns tcols where owner = upper('your_schema') and nullable = 'N'
AND NOT EXISTS ( SELECT 1 FROM ref WHERE ref.table_name = tcols.table_name
AND ref.schema_name = tcols.owner
AND ref.column_name = tcols.column_name
AND ref.condition_column = '"'||tcols.column_name||'" IS NOT NULL')
/* ALL_TAB_COLUMNS contains Tables and Views. Add below to exclude Views NOT NULL constraints */
AND NOT EXISTS ( SELECT 1 FROM ALL_VIEWS vw WHERE vw.view_name = tcols.table_name
AND vw.owner = tcols.owner
)
);
QUERY 16 - Amazon Aurora PostgreSQL:
SELECT
table_name,
object_name,
constraint_type,
upper(trim(replace(replace(replace(replace(check_condition, '"', ''), '(', ''), ')', ''), 'CHECK', ''))) check_condition
FROM (
SELECT
upper(n.nspname)||'.'||upper(c.relname) AS table_name,
'SYS_C0000'||attnum AS object_name,
'CHECK' AS constraint_type,
a.attname||' IS NOT NULL' AS check_condition
FROM pg_attribute a, pg_class c, pg_namespace n
WHERE a.attrelid = c.oid and c.relnamespace = n.oid AND n.nspname = lower('your_schema')
AND attnotnull AND attstattarget <> 0
UNION
SELECT
upper(conrelid::regclass::varchar) AS table_name,
upper(conname::varchar) AS object_name,
'CHECK' AS constraint_type,
pg_get_constraintdef(c.oid) AS check_condition
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype in ('c')
AND conrelid::regclass::varchar <> '-'
AND n.nspname = lower('your_schema')
) a;
Step 9 - Validate foreign keys:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 17 - Oracle:
SELECT
c.child_tab_owner AS schema_name,
c.table_name,
c.constraint_name AS object_name,
'FOREIGN KEY' AS object_type,
'FOREIGN KEY ('|| cc.fk_column || ') REFERENCES ' || p.parent_tab_owner || '.' || p.table_name || '('|| pc.ref_column ||') NOT VALID' AS condition_column
FROM ( SELECT owner child_tab_owner, table_name, constraint_name, r_constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type = 'R') c,
( SELECT owner parent_tab_owner, table_name, constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type IN('P', 'U') ) p,
( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) fk_column
FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) cc,
( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) ref_column
FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) pc
WHERE c.r_constraint_name = p.constraint_name
AND c.table_name = cc.table_name AND c.constraint_name = cc.constraint_name AND c.child_tab_owner = cc.owner
AND p.table_name = pc.table_name AND p.constraint_name = pc.constraint_name AND p.parent_tab_owner = pc.owner;
QUERY 18 - Amazon Aurora PostgreSQL:
SELECT upper(n.nspname::text) AS schema_name,
trim(upper(split_part(conrelid::regclass::varchar, '.', 2)), '"') AS table_name, upper(conname::varchar) AS object_name,
'FOREIGN KEY' AS object_type,
CASE contype WHEN 'f' THEN upper(pg_get_constraintdef(c.oid)) END AS condition_column
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype in ('f')
AND n.nspname::text = lower('your_schema');