You may desire or need to change platforms for your Oracle database. Many times, that migration is from one brand of UNIX or Windows to the Linux Platform. Our database version is 220.127.116.11 – so technically, we have 7 options available.
- Export/Import to include the use of datapump facilities
- Transportable tablespaces
- RMAN convert database functions
- Streams replication
- Create Table As Select (CTAS)
- Dataguard heterogeneous primary and physical standbys
- Oracle Golden Gate
The replication type options are not practical for our case. Also, since we are moving across platforms from Solaris 10 to Linux 6 (64 Bit), which have different endian types, we are narrowed down to two choices: Transportable Tablespaces or Export/Import (expdp/impdp). Also, for our database, we are on the US7ASCII character set. It is highly desirable to have a Unicode character set. AL32UTF8 is the current, most widely accepted character set. UTF8 is widely used and is still supported is but is now deprecated. Executing transportable tablespaces as a migration method requires the source and target databases to be on the same language and character set, so this method won’t allow us to upgrade the character set.
Oracle has a new free tool to assist with character set conversions – Oracle Database Migration Assistant for Unicode (DMU). CSSCAN, CSCONV, and CSALTER are no longer supported on releases which support DMU. The DMU is a graphical tool that’s fairly easy to use.
Click on the database you want to scan, and the tool will guide you through all the steps.
You can accept the defaults.
The tool will display the scanning progress on all the tables.
It generates a spreadsheet report for review of all issues in the current database. This tool can also be used to perform a character set conversion in place. We will be using expdp / impdp to perform our character set conversion since we are also migrating platforms. US7ASCII is a subset of the Unicode AL32UTF8 character set, so usually there are no issues with the conversion. The exception being sometimes there are issues from data expansion caused by the conversion, but the ProblemDataReport spreadsheet will detail down to every column and row id any data issues.
Data cleanup may be done in advance in order to prevent partial data loss. Once the data has been cleaned up, there should be no issues with the data after performing an export / import.
By using export / import we gain several benefits:
- Database character set conversion to AL32UTF8
- Complete database “reorg”
- All tables are compacted, resetting the high water mark and recovering file space
- All indexes are rebuilt
- A compacted database is faster because data is not as scattered
- Less disk resources are used for production, clones, and backups
The only downside of using the export / import method is it takes longer cook time than using transportable tablespaces.