Saturday, April 20, 2024
No menu items!
HomeDatabase ManagementSchema and code validator for Oracle to Oracle post-migration

Schema and code validator for Oracle to Oracle post-migration

Most migrations from on-premises Oracle to Amazon Relational Database Service (Amazon RDS) for Oracle use EXPDP/IMPDB or Transportable Tablespaces using RMAN. Depending on the size and complexity, migrations are often split at the schema level and table level.

In this post, we present a solution to help you identify missing schema objects in the target database post-migration and identify any issues. We use a tool to compare source database Oracle schema objects against target database schema objects and provide the missing objects list in HTML format. We also discuss the types of issues you can encounter during migration and how this tool can help you proactively fix migration errors.

Reasons to validate

Oracle is a complex database with lots of flexibility, and due to the breadth of features, it may be necessary to validate your objects. The following are some common reasons why import/export may require further validation:

When we performed the full database export from an Enterprise Edition (EE) 11g database and imported only selected schemas into a Standard Edition (SE) 19C database, we received the errors ORA-39014: One or more workers have prematurely exited and ORA-39029: Worker 1 with process name “DW00” prematurely terminated. This is a limitation of the export and import process. Therefore, we had to perform the selected schema export from the EE 11g database into the SE 19C database.
Export doesn’t capture some types of objects, like ALTER TYPE statements for CREATE TYPES in export dumps, so they will need to be moved over manually:

create or replace type “typ_name” as object
(sno number
,name varchar2(10)

ALTER TYPE “typ_name” ADD ATTRIBUTE emp_location varchar2(38) CASCADE;

Schema-level export and import of objects to the target database will minimize downtime. However, there will be lot of errors during expdp/impdp. Going through and identifying errors is a tedious task. Therefore, this sanity report helps you find any missing objects.
When there is no downtime for the source database, you would use either AWS Database Migration Service (AWS) DMS or Oracle GoldenGate. If any of DDL statement is not supported by the tools, our solution can help you find such missing objects.
At times, we disable triggers and foreign key constraints at the target database during the data migration process and enable them post-migration. This tool can capture any missed objects to enable.
If the source database code has wrapped (using DBMS_DDL.WRAP) code, importing the data into the target fails if you’re missing Oracle patch 20594149. Ensure patch 20594149 is applied first.
If the target database is Amazon RDS and has any missing patches (for example, patch Patch 8795792), the import may cause index creation failures.
If we neglect to set a static configuration parameter similar to source databases, the database needs to be restarted after the cutover.
There will be invalid objects if you migrate the database using expdp/impdp at the schema and table level. By invalid, we usually mean different types of objects such as synonyms, functions, packages, procedures, and views that have a reference to non-existing objects or to objects that were changed in some way or built in a different order.
The database link from the source must be reviewed. If the source database is on premises, the database link will connect to the older database after migration and therefore these need to be manually updated.
A SQL profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal run plan for a SQL statement. The SQL profile contains corrections for poor optimizer estimates discovered during automatic SQL tuning. During migration, if you’re not migrating the SQL profiles, it can lead to performance issues.

Solution overview

The Oracle-to-Oracle object sanity post-migration tool works for the following use cases starting from Oracle 12C and above:

On-premises Oracle to Oracle on Amazon Elastic Compute Cloud (Amazon EC2) and vice versa
On-premises Oracle to Amazon RDS for Oracle and vice versa
Oracle on Amazon EC2 to Amazon RDS for Oracle and vice versa

Use this script for application schemas rather than for SYS or SYSTEM schema comparisons.

The following diagram depicts the solution architecture and AWS services used in this post.


To follow along with this post, you need the following prerequisites:

An EC2 instance or bastion server that connects to the source database and target database
Access to the source and target database metadata with either SELECT_CATALOG_ROLE or DBA privileges
SQL *Plus
SQL *Loader
Clone the GitHub repo and provide the chmod 755 permissions for the files
Ensure you have connectivity to source database and target database from your workspace if run mode chosen is sql_ldr
Ensure you have connectivity to only the target database from your workspace if the run mode chosen is db_link

Validating objects

The following table summarizes the Oracle objects and validation fields.

Oracle Objects
Validation Fields

Schema list
Schema name, profile, status

Object types
Database object types, attributes, attribute position, data type, data type length

Sequence start position, last number, min value, max value, status, is_cycle, cache_size

Tables, column names, column position, column data type, data length

Table partitions
Table name, table owner, partition name

Constraint name, attribute, attribute type, position, status

Index name, index column name, column position, index schema, status

Index partitions
Index name, partition name, status

View name, number of lines of code

Synonym name, table name, database link

Trigger name, table owner, table name, trigger type, status

Scheduler jobs
Scheduler job names, state

Queue name, queue table, queue type, enqueue enabled, dequeue enabled

Rule name, rule owner, rule status

Object name, object owner

Scheduled programs
Program name, owner, status

Database links
Database link name, user, host details

Code line count
Verifying the number of lines count for every code object

Invalid list
Object name, object type, owner

Oracle patches
Patch ID, status

Oracle parameters
Parameter name, inst_id, default value, values

SQL profiles
Profile name, status

Role name

Roles and privileges
Grantee, granted, admin options

System privileges
Grantee, privilege, admin options

Table statistics
Table name, number of rows

LOB objects
Owner, table name, column name

DB profiles
Profile name, resource name

Objects count
Object type, count, owner combination

Perform schema validation

Database migration is a multistage process, which usually includes assessment, schema migration, data migration, testing, and many other steps spanning across the stages. The size and type of Oracle database migration you want to do greatly determines the tools you should use. For Oracle database, we can use native tools like export and import, and AWS tools like AWS DMS.

The following task list shows when you should conduct schema validation during your migration process:

Configure the source Oracle database server.
Configure the target Oracle database server.
Perform the code migration using the export and import process.
Perform schema and code validation.

To validate the schema conversion, compare the objects found in the source Oracle database and target Oracle database using your preferred query editor.

The standard validation method compares object counts in the source database against the counts in the target database. You can perform count verification for any schema object, but count validation alone doesn’t always satisfy end-users. In the previous section discussing reasons to validate, we discussed why we need to go beyond count verification. Users often look for object definition-level verification. This is why you must write your custom queries to retrieve the data definition language (DDL) from the database and compare them.

In this section, we walk you through the steps to perform schema validation:

After migrating the source Oracle database to the target Oracle database, verifying the schema list at the source and target is important for zero loss of objects. Use the following Oracle query to compare the schema list details:

select username,profile, password_versions , account_status from dba_users where username IN (‘schema1′,’schema2’,..)

Use your Oracle user name list in place of schema_name in the following example queries. If you exclude any schemas from migration in the source database or introduce one in the target database, then exclude those schemas by adding a WHERE clause filter in the preceding and subsequent queries.

As part of schema validation, you should verify the list of object types and its attribute-related information in the source and target database in order to ensure zero loss of data. Use the following Oracle query to compare the object type details:

SELECT owner,type_name,attr_name,attr_type_name,replace(‘(‘|| nvl(length, nvl(precision, 0)) || ‘)’, ‘(0)’, ”) attr_lenght,attr_no FROM dba_type_attrs WHERE owner in (schema_name);

Validate sequences with the following code:

SELECT c.sequence_owner,c.sequence_name,to_char(c.min_value),to_char(c.max_value),to_char(c.increment_by),to_char(c.cycle_flag),to_char(c.cache_size),to_char(c.last_number)
FROM dba_sequences c
WHERE sequence_owner in (schema_name)

Validate tables:

SELECT c.owner, c.table_name, c.column_name ,
c.data_type|| ‘(‘|| c.char_length||CASE WHEN c.char_used = ‘C’ THEN ‘ CHAR’ WHEN c.char_used = ‘B’ THEN ‘ BYTE’ ELSE NULL END|| ‘)’ data_type, c.column_id col_position
FROM dba_tab_cols c, dba_tables t
WHERE c.column_id IS NOT NULL
AND c.owner in (schema_name)
AND t.owner = c.owner
AND c.hidden_column = ‘NO’
AND t.table_name = c.table_name

Validate table partitions:

select table_owner , table_name, partition_name, from dba_tab_partitions where table_owner in (‘schema_name’);

Validate constraints:

SELECT c.owner,c.table_name,,c.constraint_name,c.column_name,s.constraint_type,c.position,
FROM dba_cons_columns c,dba_constraints s
WHERE s.table_name = c.table_name
AND c.owner = s.owner
AND c.constraint_name = s.constraint_name
AND c.constraint_name NOT LIKE ‘BIN%’
AND c.constraint_name NOT LIKE ‘SYS_%’
AND c.table_name NOT LIKE ‘BIN%’
AND c.table_name NOT LIKE ‘SYS%’
AND c.owner IN (schema_name)

Validate indexes:

FROM dba_indexes i, dba_ind_columns c
WHERE i.index_name = c.index_name
AND i.owner = c.index_owner
AND i.table_owner = c.table_owner
AND i.table_name = c.table_name
AND i.table_name NOT LIKE ‘SYS%’
AND i.table_name NOT LIKE ‘BIN%’
AND i.index_name NOT LIKE ‘SYS%’
AND i.index_name NOT LIKE ‘BIN%’
AND i.owner in (schema_name)

Validate index partitions:

select index_owner,index_name,partition_name,status
from DBA_IND_PARTITIONS where index_owner in (schema_name)

Validate views:

select owner,view_name,text_length from dba_views where owner in (schema_name)

Validate synonyms:

select owner,synonym_name,table_owner,table_name,db_link from dba_synonyms where owner in (‘schema_name’)

Validate triggers:

select owner,trigger_name,table_owner, table_name,triggering_event,trigger_type,status from dba_triggers where owner in (schema_name)

Validate scheduler jobs:

select owner,job_name,state from dba_scheduler_jobs where owner in (schema_name)

Validate queues:

select owner,name,queue_table,queue_type,ENQUEUE_ENABLED,DEQUEUE_ENABLED
from DBA_QUEUES where owner in (schema_name)

Validate rules:

select owner,object_name,object_type,status from dba_objects where owner in (schema_name) and object_type LIKE ‘RULE%’

Validate Java objects:

select owner,object_name,object_type,status from dba_objects where owner in (schema_name) and object_type LIKE ‘JAVA%’

Validate scheduled programs:

select owner,program_name, program_type, enabled
from DBA_SCHEDULER_PROGRAMS where owner in (‘schema_name’)

Validate database links:

SELECT owner, db_link, username, chr(34)||replace(trim(host),chr(10),”)||chr(34) host_details
FROM dba_db_links

Validate lines of code in code objects:

select owner, name, type, count(line) from dba_source where owner in (schema_name) group by owner,name,type

Validate the invalid list:

select owner, object_name, subobject_name,object_type,status FROM dba_objects WHERE status=’INVALID’
AND owner in (schema_name)

Validate Oracle patches:

select patch_id from dba_registry_sqlpatch

Validate Oracle parameters:

FROM gv$parameter

Validate SQL profiles:

SELECT name, status FROM dba_sql_profiles

Validate roles:

SELECT role,password_required, AUTHENTICATION_TYPE, common, oracle_maintained FROM dba_roles

Validate roles and privileges:

SELECT grantee,granted_role,admin_option
FROM dba_role_privs

Validate system privileges:

SELECT grantee, admin_option ROM dba_sys_privs

Validate table statistics:

select s.owner, s.table_name,
decode(nvl(s.num_rows,0),0,0, s.num_rows) num_rows
from dba_tables s
where s.owner in (schema_name)

Validate large objects (LOBs):

select l.owner, l.table_name, l.column_name, sum(sg.bytes) bytes
from dba_lobs l, dba_segments sg
where l.owner=sg.owner and l.segment_name=sg.segment_name
and l.owner in (schema_name)
group by l.owner,l.table_name,l.column_name

Validate DB profiles:

select profile,resource_name from dba_profiles;

Validate object count by object type:

SELECT COUNT(DISTINCT object_name) cnt,
FROM dba_objects WHERE object_name NOT LIKE ‘/%’
AND object_name NOT LIKE ‘BIN%’
AND object_name NOT LIKE ‘SYS%’
AND owner in (schema_name)
GROUP BY owner, object_type

Use the post-migration sanity check tool

The post-migration sanity check tool runs the preceding queries in the source and target database and provides the differences in HTML format. You can run the tool in two different modes:

sql_ldr (Oracle SQL Loader) – The following script is secure and doesn’t need a password to be stored; it will request for a password:

$ sh
Enter the mode of execution. Example: db_link or sql_ldr

Mode of execution:
Execution mode entered is: sql_ldr
Enter Source DB Password:
Enter Target DB Password:

db_link (Oracle DB Link) – Similarly, the following script will request for a password:

$ sh “C:AZ_WorkSpacePRJgitoracle_to_oralce_codesanityenv.par”
Enter the mode of execution. Example: db_link or sql_ldr

Mode of execution:
Execution mode entered is: db_link
Enter Target DB Password:

For a detailed step-to-step guide to run the tool, refer to the README file.

The following screenshots show a sample of the HTML sanity report.

All the object types discussed in this post are been covered in the HTML report. Refer the GitHub repo for the complete sanity report sample.

The HTML report includes two result sets:

Source Vs Target – This provides details of objects that exist in the source but not the target
Target Vs Source – This provides details of objects that exist in the target but not the source

Clean up

Because this solution runs on an Amazon EC2 server or bastion host, cleanup of AWS services is required. The script also has a feature to upload the report to Amazon Simple Storage Service (Amazon S3).


This post introduced a post-migration sanity check tool and demonstrated how it can help as a schema and code validator for Oracle-to-Oracle migration projects.

We strongly recommend going through this solution and implementing it wherever necessary. If you have any questions or comments about this post, please share your thoughts in the comments section.

About the Authors

Lokesh Gurram is a Lead Consultant with AWS ProServe, GCC India. He assists and enables customers to migrate servers and databases to AWS. He loves working backward and automating manual processes to enhance the user experience with least Business As Usual. His primary focus is homogeneous and heterogeneous migrations of on-premises databases to Amazon RDS along with complete migration automation solutions.

Suhas Basavaraj is a Lead Consultant AWS ProServe, GCC India. He is working as Database Migration Lead, helping and enabling customers in homogeneous and heterogeneous migrations from on premises to Amazon RDS. He is passionate about automation and building solutions to accelerate database migration.

Read MoreAWS Database Blog



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments