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.

Prerequisites

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.

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:

1. Configure the source Oracle database server.
2. Configure the target Oracle database server.
3. Perform the code migration using the export and import process.
4. 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:

1. 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.

2. 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);

3. 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) 

4. 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

5. Validate table partitions:

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

6. Validate constraints:

SELECT c.owner,c.table_name,,c.constraint_name,c.column_name,s.constraint_type,c.position,
s.status
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)

7. Validate indexes:

SELECT
c.table_owner,c.table_name,c.index_owner,c.index_name,c.column_name,
c.column_position,i.status
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)

8. Validate index partitions:

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

9. Validate views:

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

10. Validate synonyms:

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

11. Validate triggers:

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

12. Validate scheduler jobs:

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

13. Validate queues:

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

14. Validate rules:

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

15. Validate Java objects:

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

16. Validate scheduled programs:

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

17. Validate database links:

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

18. 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

19. Validate the invalid list:

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

20. Validate Oracle patches:

select patch_id from dba_registry_sqlpatch

21. Validate Oracle parameters:

SELECT inst_id,name,DISPLAY_VALUE,DEFAULT_VALUE,value
FROM gv$parameter

22. Validate SQL profiles:

SELECT name, status FROM dba_sql_profiles

23. Validate roles:

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

24. Validate roles and privileges:

SELECT grantee,granted_role,admin_option
FROM dba_role_privs

25. Validate system privileges:

SELECT grantee, admin_option ROM dba_sys_privs

26. 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)

27. 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

28. Validate DB profiles:

select profile,resource_name from dba_profiles;

29. Validate object count by object type:

SELECT COUNT(DISTINCT object_name) cnt,
object_type,owner 
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 oracle_to_oracle_object_sanity.sh 
"C:\AZ_WorkSpace\PRJ\git\oracle_to_oralce_codesanity\env.par"
 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 oracle_to_oracle_object_sanity.sh "C:\AZ_WorkSpace\PRJ\git\oracle_to_oralce_codesanity\env.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).

Summary

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.