Usage
One thing in advance: To execute the procedures from the iz_migration package, you must be connected to the database as iz_migration. The package is written to run in the connected users context (AUTHID CURRENT_USER - this must be done due to the way stored procedures work with grants), and the procedures need other objects from the iz_migration schema they don't find otherwise.
Preparation
First, the iz_migration package won't do all the work for you. It just does a part of the migration process. There are still certain things you need to do:
  • First you may want to clean your originating db. This means especially consolidation by checking for and removing of orphaned data, broken constraints and triggers, and similiar things. To have the database in a consistent state helps avoiding certain problems when migrating.
  • Second you may want to clean your originating database again, this time checking for obsolete/outaged data you don't need in your active environment. These data you may want to export first (to have a backup just in case you need it), and then remove the data from the db. The less data you have in your database, the faster is the migration process when it comes to copy the data. And besides, data access will also be boosted this way.
  • Third you may need to clean your database. Again? Yes. You must make sure that your data really is convertible. This will be done using the Oracle Character Set Scanner (CSS). Please refer to the Oracle documentation (e.g. here) for details on this. Some initial comments you also find in the Too long fields section of this document. Make sure you have no "lossy" fields left where the correct content is important to you. As for the fields marked with "truncation", refer to the Too long fields section of this document.
  • Usually you will need to set up a new (UTF8) database for the migration, since in most cases you cannot simply ALTER DATABASE CHARACTER SET. This UTF8 database is the one you install the iz_migration package into. Prepare all tablespaces, and set up the users you want to migrate here (otherwise the next step fails).
  • When you proceeded all above steps, next you will need to export your structures from the originating database. This is done using Oracle's export utility with exp rows=n indexes=n full=y statistics=none (full is necessary to get all grants along, statistics get obsolete so we don't want to take them along). Then, take this export file and import the structures into your new UTF8 database - of course only for the users you want to migrate. Don't import the Oracle system users (as e.g. SYS or SYSTEM), only your applications users. This is done with Oracle's import utility: imp rows=n indexes=n ignore=y fromuser=(user1,user2,...). Of course, before you can run this import, you have to create the required tablespaces as well as the users in the target (UTF8) database - as said above..
  • Followed up to here? Also already installed the iz_migration package into your UTF8 database? Great! Then we are ready to go...
General
As already stated in the disclaimer, the package is not that error-proof (it was not yet thoroughly tested for all possible cases). There are exception handlers for the main issues, and errors are logged - but mostly not to the console (see Logging below). So it is highly recommended that you run every step manually - and before running the next step, consult the logging table about possible errors (you may use a filter for that, as explained in the Logging section - e.g. SELECT * FROM iz_logging WHERE log_level IN ('*','!') to only show the significant information). Don't rely on the "PL/SQL procedure successfully completed." statement - this just means the code didn't break, and possible errors have been caught. Well, I could have added some output to the console in case of errors - but I intentionally decided not to do so: First, with too many errors this may blow up the whole thing due to limitations of the dbms_output package. Second, at least at this stage of a not yet thoroughly tested package, I want to force the "executer" to watch the logging carefully to find out (and report) possible problems. Provided there is enough feedback from users (=testers), and there are possible updates of the package, this may be changed in the future.
Dry Run
Well, I know there are some very sceptic people out there. And right you are, you should not simply run things you don't understand - after all not on administrational level! So at any stage, if you are paranoid or simply want to know what any procedure of the package would change in your database, you can set the package into a "dry run" mode: iz_migration.dry_run(TRUE). Then, instead of executing any DDL or DML, these statements are just written to the log table, the changes are indicated with the log level "S" (for "SQL" - see "Logging" below).
If you then really want the package to do the job, simply turn the "Dry Run" mode off again: iz_migration.dry_run(FALSE), which is the default of the package right after installation.
Logging
As you already may know, using dbms_output for logging has several limitations. One is the restriction to a maximum of less than one megabyte (one million characters, to be exact). The second one is more crucial in our case: All output will not appear before the procedure executed has completed - so you cannot watch the process while it is running. But since we are using a database, we also can write all log to a table. This is what the package does: All log information will be written into the iz_migration.iz_logging table. This makes it easy not only to truncate the log or delete specified entries, but also to filter logging information.
While the log information is stored in the column log_entry, and the processing time can be taken from the log_time column, filtering can also be done by the log_level column. Following list shows the most interesting log levels and what they represent:
LevelDescription
* Start/End of a task (e.g. "Copying table data")
+ Start of a sub-task (e.g. "Copying table data for <owner>")
- Start of a single command (e.g. "Copying <owner>.<table>")
$ Details on fields truncated (only for iz_migration.truncate_long_fields)
S SQL commands which would be executed (this is only available when running in "DryMode", see above)
NULL Minor process statements
Initialize Structures
Before we can start the migration process, we need to initialize the structures. The iz_migration package needs to know the objects to work on. This is not done at installation time, so you can "reconfigure" it at any time using the packages procedures. Following is what you need to do:
ProcedureDescription
initiate_semantics_table (Re-)fill the table holding information about all character type fields (to be converted to CHAR semantic)
initiate_nchars_table Check for all tables having NCHAR columns (to be converted to CHAR)
initiate_nvarchars_table Check for all tables having NVARCHAR columns (to be converted to VARCHAR2)
initiate_nclobs_table Check for all tables having NCLOB columns (to be converted to CLOB)
initiate_constraints_table Save all current constraint states (to re-establish later)
Change Semantics
By default, Oracle uses BYTE semantics. This means, if you specify a column to be VARCHAR2(2000) it means, you can store 2000 byte here. As for US7ASCII, this is exactly the same as 2000 characters, since one character needs exactly one byte here. Same applies to Latin-1 (aka WE8ISO8859P15). However, it does not apply to UTF8 (AL32UTF8). While the simple US7ASCII characters still require exactly one byte here, most of the "special characters" will consume 2 bytes - which means, our VARCHAR2(2000) field can only store up to 2000 characters, if all of them are plain US7ASCII - otherwise the limit will be lower.
So, if you want to store up to 2000 characters in a column without thinking about "special characters or not", you have to define the column accordingly: VARCHAR2(2000 CHAR). In your environment, you should explicitely set NLS_LENGTH_SEMANTICS=CHAR - but this only applies to new objects then. The imported data structures will not be changed: These objects will be created as they have been in the originating database, i.e. their create statements will reflect those settings. This means, they all need to be adjusted.
This job is done by the iz_migration.semantics2char procedure. The objects to be processed are read from the iz_semantics table, which is initiated by the above mentioned iz_migration.initiate_semantics_table procedure.
National Character Fields
When using a non-unicode character set in your originating database, you may have used "national character set fields" to store information hold in other languages: NCHAR, NVARCHAR and NCLOB are the corresponding data types. Now, with UTF8 encoding in all character fields, you don't need the N* fields anymore, and can convert them to their "normal" pendants:
ProcedureDescription
convert_nchar Convert NCHAR columns to CHAR
convert_nvarchar Convert NVARCHAR fields to VARCHAR2
convert_nclob Convert NCLOB fields to CLOB
Which objects are to be checked and converted, is read from the tables you filled with the corresponding initiate_*_table procedure mentioned above.
Too long fields
As explained above, with UTF8 some characters consume more than 1 byte. This means, with the NLS_LENGHT_SEMANTICS set to BYTE, a VARCHAR2(2000) field may hold more than 2000 byte - which is not necessarily a problem. But still, the maximum length vor VARCHAR2 in an Oracle database is 4000 bytes - which may easily be exceeded in a VARCHAR2(4000 CHARS) field. The affected columns will be listed as "truncation" by the Oracle CSS tool (see Preparation above). Which means, these fields need to be truncated, so a maximum of 4000 bytes stay here, not more.
Best is, of course, to identify those fiels with Oracle's CSS tool, and adjust them manually. But you also can do so using the truncate_long_fields function of the iz_migration package. This procedure will first identify the exact records affected, copy the original content to the iz_migration.iz_long_fields table together with the owner, table_name and rowid of the record, so you can pick the original values again if needed later on. Then the content of the original field will be truncated to exactly 4000 bytes.
When using Oracle's CSS tool, don't be confused that it may even list some VARCHAR2(50) columns as to be truncated. This is due to the NLS_LENGTH_SEMANTIC still set to BYTE for the originating database - and the CSS tool is not aware of the fact that we took care to change this to CHAR in the new database - and since this is a parameter in the init.ora file, we can't just overwrite it in the environment (you may however try to ALTER DATABASE SET NLS_LENGTH_SEMANTIC=CHAR in the originating database before running the CSS tool to minimize these "false positives"). This means, you can ignore this for sure for fields with < 1000 characters, and most likely for all fields with < 2000 characters as well (this is what the truncate_long_fields procedure assumes, too). But you for sure can not ignore it for fields with 4000 characters, obviously. To give you a clear rule: Only count those entries from the *.err file, where the "Max Post Conversion Data Size" exceeds 4000. And make sure these columns are all fixed - either manually, or by the truncate_long_fields procedure (in the latter case, consult the iz_logging table to make sure it covered all those columns: SELECT * FROM iz_logging WHERE log_level='$').
Copying data
Now all preparation is done, and we can go to copy the data. This is the time where nothing else should access the originating DB anymore (i.e. "Downtime Start") - and you should make sure that all previous tasks completed successfully. Sure, there shouldn't be any interfering processes on the target (UTF8) database either. To do the copy task in the most effective and fast way, this action is split into several steps (also see the General section on this fact), covered by their dedicated procedures:
ProcedureDescription
disable_triggers Disable all database triggers for the affected schemata
disable_constraints Disable all database constraints for the affected schemata
copy_tables Copy all table data for the affected schemata
copy_sequences Re-Create all sequences for the affected schemata (last values may have changed since the strucure export!)
enable_constraints Re-Enable all constraints which we disabled before
enable_triggers Same for the triggers
validate_constraints Render all enabled constraints VALIDATED for the affected schemata
schema_stats Gather schema stats for the imported data
compile_invalid Optionally compile all functions, procedures, packages and triggers for the imported schemata (recommended)
One step missing in between (before the stats gathering) is to create the indices. For this you need to generate the appropriate script, which holds the CREATE INDEX statements for all the schemata you migrated, but excludes all primary key and unique indexes (since these are automatically created by the corresponding constraints). The generate_create_index.sql script can assist you here (Note: Even if the CREATE INDEX statements here are generated by Oracles own product, the dbms_metadata package, you may need to manually re-format the output. We found many times that Oracle was unable to execute the generated statements directly and just threw bunches of error messages due to "invalid statements" - looks like some line breaks cause this trouble). After the index creation, you can open the new database for the applications as replacement for the originating one (i.e. "Downtime End").
The first two and the last three steps can also safely be done outside the downtime, if you have a "time limit" set for this. While the first two steps are run against a database not yet available to the public (and thus can't do any harm), everything is OK and available to the applications/users after the triggers are enabled: Though the constraints are not yet validated, they are already enabled (so new data will be checked). Without the statistics, the database may be not running at optimal speed - but give the appropriate results. And as for the potentially invalid objects - they would be compiled on the first access in most cases, and thus work fine on the next.

Conclusion
Now you should be through, your migration should have been finished successfully. Still, you should test your application(s) carefully to ensure everything has been done correctly. Remember, this is not the strategy as propagated by Oracle. It was successful in some environments - but this does not necessarily mean it is "100% approved". Again to the disclaimer: Use it at your own risk - I take no responsibility at all!