Efficient Oracle Migration, Amazing Tricks!

 Author: Carl Frentzen

Page: 1 2

Oracle migration with minimal downtime during upgrades is the dream of every database administrator. Inability to use Data Guard due to its licensing costs and slow performance after upgrade is amongst the common issues we face while migrating Oracle databases. Today we will discuss some amazing tips and tricks that will help you perform efficient migration of Oracle databases.

Direct Path:

The conventional export path executes SQL SELECT statements whereas direct path exports (DIRECT=Y) allows the export utility to skip the SQL evaluation buffer. SQL evaluation buffer is where the WHERE clause processing, column formatting etc would normally take place. Direct path offers considerable performance gains as data is read from disk into the buffer cache returning rows directly to the export client.

You can improve the performance of the export almost 50% assuming a full export and using direct=y if you are not using any predicates in the export. The recordlength parameter can also be used to optimize performance while using the direct path. Remember QUERY= parameter can not be used to select a subset of rows in direct path mode.

Large Buffers:

Always use a larger buffer size such as 10MB at least. If you are using large buffers then there will be fewer physical writes and hence greater performance. Import can do more work before disk access is performed by using a larger buffer setting.  You can use buffer size = rows in array * max row size to identify the proper buffer size.

Novalidate:

Prior to export you should alter all your constraints to novalidate. This should be done if your application can be brought down and you can take a consistent export. This prevents Oracle to validate constraints on massive tables and hence helps in reinstating the constraints instantly.

Subsets:

You can execute export process only against the data that needs to be exported by applying subset on the data using the QUERY option. The benefit of using Subsets is that if your table has some rows that are never updated then your old data will be exported only once and only newer data subsets will be exported from that point onwards. Remember SQL is necessary to create the subset and therefore you can not specify subsets with direct path exports since

WORKAREA_SIZE_POLICY:

WORKAREA_SIZE_POLICY helps you control the sizing for work areas. I recommend setting WORKAREA_SIZE_POLICY=MANUAL. If you set WORKAREA_SIZE_POLICY to manual then it will force connections to allocate memory according to the values for the SORT_AREA_SIZE parameter.

SORT_AREA_SIZE:

Oracle SORT_AREA_SIZE parameter reduces expensive disk sorts by controlling the RAM size for dedicated tasks to sort SQL result sets. Setting SORT_AREA_SIZE=6GB prevents the need to spill to disk by building indexes with more space in memory for all the sorts.

Single, Large Rollback Segment :

You can improve import performance by creating a single large rollback segment and taking all others offline.

Postpone Index Creation:

You can eliminate the maintenance overhead by setting index=n to postpone index creation until the import is completed. Once the data is loaded then you can use the indexfile parameter for rebuilding all the indexes.

Massive Online Redo Logs:

The massive online redo logs should be at 2GB each, 2 members each and 6 groups. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Quick Guide to determine Oracle RAM Size!!
   SQL - Best Practices to improve Performance
   Database Tuning Guide for Third Party Applications
   Tips for handling Tricky Roles in Oracle Stored Procedures!