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:
|
Level | Description |
* |
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:
|
Procedure | Description |
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:
|
Procedure | Description |
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:
|
Procedure | Description |
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!
|