oracle 汉子转unicode,Oracle从non-Unicode到Unicode的转换

1.4. Backup source database by doing full database export (full=y)

1.5. Export full source database without data (ROWS=N constraints=n indexes=n)

1.6. Recreate source database or new database with characterset = AL32UTF8/UTF8

1.7. Make sure to set NLS_LENGTH_SEMANTICS to CHAR in target database initialization file  (bounce DB)

SQL> alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both

1.8. Import export file of step 4 to create all database objects empty

1.9. Run script. nls_length_from_byte_to_char.txt, it will change accela columns containing CHAR, VARCHAR2 from byte to char semantics.

1.10. Import export file of step 3 to import all data (with IGNORE=Y because database objets already exist).

export NLS_LANG=SOURCE_DB_CHARACTERSET

The conversion only happens once during import process.

1.11. Recompile all invalid object if any

1.12. Do MAT test on new DB

1.13. Backup new db

1.14. Performance after Conversion

The performance will not degrade as the AL32UTF8/UTF8 char allocates one byte for ASCII chars.

1.15. DB Size

The DB Size is almost same as old db, as the Unicode char allocates two bytes and non-Unicode only allocates one byte. Please refer to the assessment report for the data

1.16. Data Loss

After tested in Acclea host production db, all rows in the report can be converted

Successfully by exp/imp

1.17. Data Truncation

Only 200 rows (>4000 bytes) has Data Truncation issue in accela production db, they can be handled manually without much time

2. Test Requirement

2.1. Test the migration steps for both Oracle and mssql dbs

2.2. Test the data conversion correctly from the UI and table data especially the data in csscan.err file for oracle version

3. Appendix

3.1. Oracle Application Data Exception Handle

Below agencies has been scanned.

csscan 'sys as sysdba' FULL=Y FROMCHAR= WE8MSWIN1252 TOCHAR= AL32UTF8 LOG=csscan CAPTURE=Y FEEDBACK=1000 ARRAY=1000000 PROCESS=2

There are about 800 truncation exceptions in all below agencies (It needs not much time to fix them manually). There is no lossy data exception in all below agencies.