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;
SELECT /*+ parallel(32) */ nt, Max( ID(this is index id of pk)), Count(*)
FROM (SELECT ID, Ntile(6) over( ORDER BY ID) nt FROM schem.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');
Comparing data between Oracle and PostgreSQL post-migration involves several steps to ensure that the data has been accurately and completely transferred from one database system to the other. Here’s a structured approach to achieve this
Oracle
----- -------- SCHEM All object Size-------------------------
select segment_type,sum(bytes)/1024/1024/1024 from dba_segments where owner='DLK' group by segment_type;
----------------Tables Column Count--------
select owner,table_name,count(*) as column_count from dba_tab_columns where owner='DLK' group by owner,table_name order by owner,table_name asc;
------------------All Object counts------------------
select owner,object_type,count(*) from dba_objects where owner='DLK' group by owner,object_type ;
--------------------Tables Name-----------------------
select table_name from dba_tables where owner='TEST' and temporary!='Y' order by 1 asc;
------------------Index Name ----------------------------------
select index_name from dba_indexes where owner='TEST' order by 1 asc;
----Trigger Name ----------
select trigger_name from dba_triggers where owner='TEST';
-----------------------Trigger Status --------------------
select OWNER, TRIGGER_NAME,STATUS from dba_triggers where owner='TEST' order by 1;
-------------Tables Rows Count-------------------------
select owner,table_name,num_rows from dba_tables where owner='TEST' order by table_name asc;
----------------------Constraint --------------------------
select ctr.constraint_type, COUNT (1)
from sys.all_constraints ctr
join sys.all_cons_columns col
on ctr.owner = col.owner
and ctr.constraint_name = col.constraint_name
and ctr.table_name = col.table_name
--where ctr.constraint_type = 'C'
WHERE ctr.owner in ('TEST')
GROUP BY ctr.constraint_type
order by ctr.owner, ctr.table_name, ctr.constraint_name;
SELECT constraint_type ,COUNT(1) from dba_constraints
where owner='TEST' --and constraint_type like ('R')
and constraint_name not like 'SYS_%'
GROUP BY constraint_type
select owner,constraint_type,count(*) from dba_constraints
where owner='TEST' and constraint_name not like 'BIN%' and constraint_name not like '%aws%' and table_name not like '%aws%' group by owner,constraint_type order by 1 asc;
-------------------------------Start---------------------------
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
set serveroutput on
set heading off
set lines 500 pages 300 long 999999999
set echo off
set heading off
set feedback off
set linesize 1024
set pagesize 50000
set verify off
set trimspool on
select dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name, owner) from dba_constraints
where owner='TEST' and constraint_type like ('R')
and constraint_name not like 'SYS_%';
--------------------End--------------------------------
----------------------Truncate table ---------
SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM DBA_TABLES WHERE owner='TEST';
----------------------Lob Column Size ----------------------
select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;
This is the query to get the max size of the LOB in the entire column:
select max(dbms_lob.getlength (<lob column name>)/1024) from schema_name.table_name;
---------------Validate lob data ---------------------
SELECT max((Length(lob column name))/(1024.00)) AS "Size_in_KB" from <table_name>;
PostgreSQL
---------------------All Object counts------------------
SELECT n.nspname as schema_name ,CASE c.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
when 't' then 'TRIGGER'
END as object_type
,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s')
and n.nspname ='TEST'
GROUP BY n.nspname,
CASE c.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
when 't' then 'TRIGGER'
END
ORDER BY n.nspname,
CASE c.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
when 't' then 'TRIGGER'
END;
--------------- Table name rows count ---------------
SELECT schemaname as table_schema, relname as table_name, n_live_tup as row_count FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
---------------Tables Column Count-------------------
select table_schema,table_name,count(*) as column_count
from information_schema.columns where table_schema='TEST'
group by table_schema,table_name
order by column_count desc;
-----------------View count---------------------------
SELECT count(1) AS views_cnt FROM pg_views WHERE schemaname = lower('TEST');
---------------Trigger Name ---------------------
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('TEST') GROUP BY trigger_schema, trigger_name, event_object_table, action_timing, action_orientation ORDER BY upper(trigger_name);
SELECT schemaname, count(1) AS indexes_cnt FROM pg_indexes WHERE schemaname = lower('TEST')
group by schemaname;
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('TEST');
---------------Validate lob data ---------------------
SELECT max((Length(lob column name))/(1024.00)) AS "Size_in_KB" from <table_name>;