NLS_LENGTH_SEMANTICS, Unicode and Everything
Werbung
Oracle Globalization Support, NLS_LENGTH_SEMANTICS, Unicode and Everything Else Markus Flechtner Senior Consultant 17. Dezember 2013 Oracle goes Unicode. Mehr und mehr Datenbanken müssen von den früher üblichen 8Bit-Zeichensätzen mitteleuropäischer Prägung auf Unicode-Zeichensätze migriert werden, um den aktuellen Anforderungen gewappnet zu sein. Der Artikel zeigt Grundlagen zu Zeichensätzen, Unicode und Zeichensatzkonvertierung und stellt die Oracle-Tools für derartige Zeichensatzkonvertierungen vor. 1. Zeichensätze – ganz allgemein Was ist ein Zeichensatz? Ein Zeichensatz ist letztendlich nichts anderes als eine Übersetzungstabelle. Wie sollen Bits und Bytes im Computer in für den Menschen lesbare Zeichen übersetzt werden? Und umgekehrt. Einige der bekannteren Zeichensätze sind die Zeichensätze der ISO8859-Familie, die Unicode-Zeichensätze oder der „Klassiker“ US7ASCII. In der Version 12.1 unterstützt Oracle 247 verschiedene Zeichensätze. Das Spektrum reicht dabei von „Traditional Chinese“ über „Hebrew“ bis hin zu „Icelandic“. Dabei können die unterstützten Zeichensätze in verschiedene Gruppen aufgeteilt werden. Man kann sie in „EBCDIC“-Zeichensätze und „ASCII“-Zeichensätze gruppieren, je nachdem, auf welcher Zeichenkodierung sie aufsetzen. EBCDIC kommt aus der Großrechnerwelt und verliert insgesamt eher an Bedeutung. Weiterhin gibt es Unterscheidungen nach den verschiedenen Betriebssystemen. So gibt es Windows-Zeichensätze, von denen WE8MSWIN1252 sicher der bekannteste ist, Mac-Zeichensätze oder Zeichensätze für die früher häufig verwendete VMSPlattform, z.B. WE8DEC. Eine weitere Unterteilungsvariante ist die von Oracle vorgenommene Aufteilung in -­‐ -­‐ -­‐ Single-Byte Zeichensätze (SB) Multi-Byte Zeichensätze (MB) Multi-Byte Zeichensätze mit fester Länge (FIXED) -­‐ -­‐ -­‐ Zeichensätze die eine echter Obermenge zum ASCII-Zeichensatz sind (im Gegensatz zur EBCDIC-Familie) (ASCII) Zeichensätze, die das EURO-Symbol unterstützen (EURO) Mit Hilfe des „Oracle Locale Builder“ erstellte eigene Zeichensätze („User Defined Charactersets“, UDC) Die in Westeuropa am häufigsten verwendeten Zeichensätze entstammen der ISO-Norm 8859. Früher war es der ISO8859-1-Zeichensatz, aber seit der Euro-Umstellung wird praktisch nur noch der ISO8859-15 verwendet. Dieser enthält im Gegensatz zum ISO8859-1 u.a. auch das Euro-Zeichen. Abbildung 1: Unterschiede zwischen ISO8859-1 und ISO8859-15 (Quelle: http://en.wikipedia.org/wiki/ISO8859-15) In der Oracle-Notation heißt dieser Zeichensatz „WE8ISO8859P15“, übersetzt: „WestEuropäisch“ - „8 Bit“ - gemäß ISO8859-15. Die ISO8859 wird nicht weiterentwickelt. Stattdessen wird an der ISO10646 gearbeitet, die praktisch dem Unicode-Zeichensatz entspricht. Welcher Zeichensatz in einer Oracle-Datenbank verwendet wird, ist in den Data-Dictionary Views DATABASE_PROPERTIES oder NLS_DATABASE_PROPERTIES zu finden: SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ’%CHARACTERSET%’; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ----------------------------------NLS_CHARACTERSET WE8MSWIN1252 NLS_NCHAR_CHARACTERSET AL16UTF16 2. Unicode „Unicode“ ist ein universeller Zeichensatz, genauer: eine universelle Zeichenkodierung, die das Ziel hat, die Zeichen aller Sprachen abbilden zu können. Das Unicode-Konsortium wurde 1991 gegründet und hat seitdem den Unicode-Standard entwickelt. Derzeit ist die Version 6.2 aktuell, die mehr als 110.000 Zeichen umfasst. Oracle 12.1 unterstützt die Unicode-Version 6.1. Der einzige Unterschied zur Version 6.2 ist das fehlende Währungssymbol für die türkische Lira. Unicode stellt für jedes Zeichen eine Nummer, einen sog. „Code-Punkt“ (engl. „Code-Point“) bereit. Für einige Zeichen gibt es auch mehrere Code-Punkte, damit die Konvertierung zu anderen Zeichensätzen einfacher wird. Die ersten 128 Zeichen im Unicode stimmen mit dem ASCII-Zeichensatz überein (ASCII 0-127). Früher war der Unicode-Standard zwei Byte lang, so dass nur 65.536 Zeichen dargestellt werden konnten. Inzwischen wurde der Standard auf vier Byte erweitert, so dass theoretisch mehr als eine Million Zeichen dargestellt werden können. [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 2 / 21 Das UTF, das „Unicode-Transformation-Format“, ist eine Methode, um die Code-Punkte des Unicode-Standards in Bytes abbilden zu können: -­‐ -­‐ -­‐ UTF-32 – dabei werden für jedes Zeichen vier Byte verwendet UTF-16 – je nach Zeichen werden zwei oder vier Byte verwendet UTF-8 – hier werden die Zeichen mit variabler Länge von ein bis vier Byte codiert. Die Zeichen des ASCII-Zeichensatzes (Code-Punkte 0 – 127) benötigen dabei ein Byte. Alle anderen Zeichen, z.B. auch Umlaute, benötigen zwei oder mehr Byte. Das folgende Bild zeigt die verschiedenen Varianten, wie Zeichen im UTF-8 gespeichert werden: Abbildung 2: Byte-Bedarf für verschiedene Zeichen im UTF-8, aus Oracle 11.2 Globalization Support Guide, (c) Oracle Corporation Bei Oracle-Datenbanken sind folgende Unicode-Zeichensätze möglich: -­‐ -­‐ -­‐ -­‐ AL16UTF6 AL32UTF8 UTF8 UTFE UTFE ist dabei eine EBCDIC-Variante des Unicode-Standards. Die beiden UTF8Zeichensätze sind eine strikte Obermenge des ASCII-Zeichensatzes. Dies ist bei den in unserem Kulturkreis überwiegend verwendeten Zeichen hilfreich, denn die häufig verwendeten ASCII-Zeichen benötigen dabei nur ein Byte. Der Zeichensatz AL16UTF16 ist der einzige Zeichensatz aus der obigen Gruppe, der für die Zeichen eine feste Länge vorsieht. 3. Welcher Datenbank-Zeichensatz ist der beste? Die Frage ist etwas irreführend, denn es gibt zwei Zeichensätze in der Datenbank: -­‐ -­‐ Database Character Set National Character Set [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 3 / 21 Letzterer muss ein Unicode-Zeichensatz sein, genauer: AL16UTF16 oder UTF8, und bestimmt den Zeichensatz für die Datentypen NCHAR, NVARCHAR2 und NCLOB. Dieser zweite Zeichensatz in der Datenbank wurde eingeführt, als Oracle Unicode als Database Character Set noch nicht unterstützt hat. Oracle hat damals über diesen Zeichensatz die Möglichkeit geschaffen, Unicode in der Datenbank zu speichern. Inzwischen kann auch Unicode als Database Character Set verwendet werden und der National Character Set hat an Bedeutung verloren. Welchen Zeichensatz ist als „Database Character Set“ am besten geeignet? Dieser Fragestellung sollten wir vor Erstellen einer Datenbank die nötige Aufmerksamkeit schenken. Eine nachträgliche Änderung des Zeichensatzes ist zwar möglich; kann aber sehr aufwändig sein und ist auch mit einigen Einschränkungen verbunden. Kriterien für die Auswahl des Datenbank-Zeichensatzes sind: 1. Der Datenbank-Zeichensatz muss eine Obermenge der Zeichensätze aller Clients sein. Ansonsten kommt es zu Datenverlust, denn die Datenbank kann dann Zeichen, die der Client überträgt, nicht richtig speichern. 2. Der ehemals häufig verwendete Zeichensatz WE8ISO8859P1 sollte aufgrund des fehlenden Euro-Zeichens nicht mehr verwendet werden. WE8ISO8859P15 oder WE8MSWIN1252 sind die besseren Varianten. 3. Wer überwiegend mit Windows-Clients arbeitet, sollte WE8MSWIN1252 wählen. WE8MSWIN1252 enthält einige Zeichen mehr als WE8ISO8859P15, die sonst nicht in der Datenbank abgelegt werden können. Außerdem ist bei dieser Konfiguration keine Konvertierung zwischen Windows-Client und Datenbank erforderlich. Dadurch wird die Kommunikation zwischen Client und Server etwas schneller. 4. Internet- und Java-Anwendungen arbeiten mit Unicode, daher sollte der zugehörige Datenbank-Zeichensatz auch ein Unicode-Zeichensatz sein. 5. Wer Unicode als Datenbank-Zeichensatz wählt, sollte bedenken, dass die UnicodeZeichensätze Multi-Byte-Zeichensätze sind. Die Datenbank benötigt somit mehr Platz und es gibt einen gewissen Performance-Overhead. Die Oracle-Tools csscan und DMU geben eine Abschätzung über den zusätzlich erforderlichen Plattenplatz ab. 6. Last but not least, sollte der Blick in die Zukunft schweifen: Ist etwa eine Konsolidierung mehrerer internationaler Datenbanken in eine Datenbank geplant oder plant das Unternehmen eine internationale Expansion? In so einem Fall kann ein UnicodeZeichensatz die bessere Wahl sein. Oracle empfiehlt, für neue Datenbanken einen Unicode-Zeichensatz zu verwenden. Für Speicherung von CHAR und VARCHAR2-Spalten gibt es technische Obergrenzen innerhalb von Oracle. In Oracle 11.2 und Oracle 12.1 kann eine VARCHAR2-Spalte standardmäßig maximal 4.000 Byte enthalten, eine CHAR-Spalte 2.000 Byte. Das bedeutet, dass im Zeichensatz AL32UTF8 (mit variabler Länge) ein Feld zwar 4.000 „a“ enthalten kann, aber nur 3.997 „a“ und 1 Euro-Zeichen („€“), denn das Euro-Zeichen belegt in diesem Zeichensatz drei Byte. Da im AL32UTF8 die Zeichen bis zu vier Byte benötigen, kann dies im Extremfall dazu führen, dass in einem Feld nur 1.000 Zeichen abgelegt werden können. Es ist sicher unwahrscheinlich, dass im westeuropäischen Kulturkreis ein derartiger Fall eintritt. Aber wir müssen diesen Aspekt beachten und bei langen Texten in der Datenbank ggf. auf CLOBs wechseln. Oracle 12.1 können wir auch so konfigurieren, dass VARCHAR2-Spalten bis 32.767 Zeichen lang sein können. Weitere Informationen zu dieser Einstellung und den dazu notwendigen Schritten sind z.B. in der Oracle-Dokumentation „Oracle Database Reference 12c Release 1“ unter dem Parameter „MAX_STRING_SIZE“ zu finden. [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 4 / 21 4. Oracle-Parameter 4.1 NLS_LANG Der Parameter NLS_LANG ist der wichtigste Parameter für die Sprach-Einstellungen auf einem Oracle-Client. Der Parameter wird als Registry-Eintrag (Windows, unter HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE) oder als Umgebungsvariable abgelegt: -- Format <Language>_<Territory>.<ClientCharacterSet> NLS_LANG = GERMAN_GERMANY.AL32UTF8 Über die Angabe von Sprache, Land und Zeichensatz werden alle wichtigen Einstellungen abgeleitet: -­‐ -­‐ -­‐ -­‐ -­‐ Kalender (Gregorianisch, Thai Buddha, Arabic Hijrah etc.) Datumsfunktionen: Datumsformat, Tag 1 einer Woche, Woche 1 eines Jahres Numerische Funktionen: Dezimal- und Tausendertrennzeichen Sortierung Währungen SQL> !echo $NLS_LANG German_Germany.WE8ISO8859P15 SQL> SELECT * FROM NLS_SESSION_PARAMETERS; PARAMETER VALUE ------------------------------ ---------------------------------NLS_LANGUAGE GERMAN NLS_TERRITORY GERMANY NLS_CURRENCY € NLS_ISO_CURRENCY GERMANY NLS_NUMERIC_CHARACTERS ,. NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD.MM.RR NLS_DATE_LANGUAGE GERMAN NLS_SORT GERMAN NLS_TIME_FORMAT HH24:MI:SSXFF NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR NLS_DUAL_CURRENCY € NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE Beim Thema „Zeichensatz“ ist es wichtig, dass der Zeichensatz-Teil des Parameters NLS_LANG auf den korrekten Zeichensatz des Clients gesetzt wird. D.h. z.B. bei WindowsClients üblicherweise auf WE8MSWIN1252. Bei Unix-Clients werden die locale-Settings ausgewertet: [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 5 / 21 [oracle@rac2node1 ~]$ locale LANG=de_DE.UTF-8 LC_CTYPE="de_DE.UTF-8" LC_NUMERIC="de_DE.UTF-8" LC_TIME="de_DE.UTF-8" LC_COLLATE="de_DE.UTF-8" LC_MONETARY="de_DE.UTF-8" LC_MESSAGES="de_DE.UTF-8" LC_PAPER="de_DE.UTF-8" LC_NAME="de_DE.UTF-8" LC_ADDRESS="de_DE.UTF-8" LC_TELEPHONE="de_DE.UTF-8" LC_MEASUREMENT="de_DE.UTF-8" LC_IDENTIFICATION="de_DE.UTF-8" LC_ALL= Achtung: Ist NLS_LANG falsch gesetzt, kann es auf dem Weg vom und zum DatenbankServer zu Konvertierungsverlusten kommen. Client-Eingaben werden nicht richtig in der Datenbank abgelegt und Datenbank-Ausgaben werden auf dem Client falsch dargestellt. 4.2 NLS_LENGTH_SEMANTICS Der Parameter NLS_LENGTH_SEMANTICS gibt an, in welcher Einheit die Längenangaben bei CHAR und VARCHAR2-Spalten standardmäßig gemacht werden. Dabei stehen CHAR und BYTE (Default) zur Auswahl. Bis Oracle 11.1 hat Oracle auch erlaubt, dass dieser Parameter in der Init.ora resp. im spfile auf „CHAR“ gesetzt wird. Seit Oracle 11.2 rät Oracle davon ab: „Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.“ (Zitat aus der Oracle-Dokumentation „Oracle 11.2Reference“). Es sollte auch nur ein Workaround sein, den Parameter auf Session-Ebene zu setzen, um dann die Tabellen mit der korrekten Längenangabe anzulegen: SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR; Session altered. Es ist die beste Lösung, die Längenangaben in der Spaltendefinition mit aufzuführen: SQL> CREATE TABLE MUSTER_TABELLE ( SPALTE_1 VARCHAR2(10 CHAR), SPALTE_2 VARCHAR2(255 CHAR) .. ); Die „Length Semantics“ einer Spalte kann auch via „ALTER TABLE“ geändert werden: SQL> ALTER TABLE MUSTER_TABELLE MODIFY (SPALTE_3 VARCHAR2(20 CHAR)); Table altered. Tipp: steht eine Zeichensatzänderung an, dann können wir diese Änderung schon in der Ausgangsdatenbank durchführen. Dort ändert sich nichts, aber wenn wir die Tabellen via [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 6 / 21 export/import oder DataPump transferieren, dann bekommen sie in der Zieldatenbank direkt die korrekte Längensemantik. Bleibt die NLS_LENGTH_SEMANTICS für eine Spalte auf dem Default BYTE, kann es zu einem bösen Erwachen kommen, wenn Daten eingefügt werden sollen: SQL> CREATE TABLE unicode_demo ( byte_string VARCHAR2(10 BYTE), char_string VARCHAR2(10 CHAR) ) Table created. SQL> DESCRIBE unicode_demo Name Null Typ ----------- ---- ----------------BYTE_STRING VARCHAR2(10 BYTE) CHAR_STRING VARCHAR2(10) SQL> INSERT INTO unicode_demo (byte_string) VALUES ('äöüÄÖÜ'); ORA-12899: Value too large for column [..] 5. Tool CSSCAN/CSALTER (11.2) Wenn wir den Zeichensatz einer Datenbank ändern wollen, dann ging das bis zur Version 11.1 mit dem Befehl „ALTER DATABASE CHARACTER SET“: SQL> ALTER DATABASE CHARACTER SET AL32UTF8; Achtung: Eine derartige Änderung des Zeichensatzes ohne vorherige Prüfung der Datenbank-Inhalte kann zu Datenverlusten führen und wird seit Oracle 11.2 nicht mehr unterstützt. Es ist besser, die Tools csscan/csalter oder den Nachfolger „Database Migration Assistant for Unicode“ (DMU) verwenden. Wenn wir mit csscan/csalter arbeiten, dann erfolgt die eigentliche Änderung des Datenbankzeichensatzes mit dem Skript „csalter“. „csscan“ ist ein „Character Set Scanner“ der prüft, ob die Änderung des Zeichensatzes ohne Probleme möglich ist. Csscan benötigt dabei ein Datenbankschema CSMIG, das mit dem Skript $ORACLE_HOME/rdbms/admin/csminst.sql angelegt wird. Nachdem der User angelegt wurde, muss er noch entsperrt werden und das Password muss noch geändert werden. Als nächstes kommt das Tool csscan ins Spiel: [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 7 / 21 Oracle> csscan help=yes Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. You can let Scanner prompt you for parameters by entering the CSSCAN command followed by your username/password: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" Or, you can control how Scanner runs by entering the CSSCAN command followed by various parameters. To specify parameters, you use keywords: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3 Keyword Default Prompt Description ---------- ------- ------ ------------------------------------------------USERID yes username/password FULL N yes scan entire database USER yes owner of tables to be scanned TABLE yes list of tables to scan COLUMN yes list of columns to scan EXCLUDE list of tables to exclude from scan TOCHAR yes new database character set name FROMCHAR current database character set name TONCHAR new national character set name FROMNCHAR current national character set name ARRAY 1024000 yes size of array fetch buffer PROCESS 1 yes number of concurrent scan process MAXBLOCKS split table if block size exceed MAXBLOCKS CAPTURE N capture convertible data SUPPRESS maximum number of exceptions logged for each table FEEDBACK report progress every N rows BOUNDARIES list of column size boundaries for summary report LASTRPT N generate report of the last database scan LOG scan base file name of report files PARFILE parameter file name PRESERVE N preserve existing scan results LCSD N no enable language and character set detection LCSDDATA LOSSY no define the scope of the detection HELP N show help screen (this screen) QUERY N select clause to scan subset of tables or columns ---------- ------- ------ ------------------------------------------------Scanner terminated successfully. Die Parameterliste zeigt, dass csscan nicht nur eine komplette Datenbank überprüfen kann. Auch eine Eingrenzung der Scan-Läufe auf einzelne User, Tabellen oder sogar Spalten und Datensätze ist möglich. Über den Parameter PROCESS kann ein Scan-Lauf parallelisiert werden. Tipp: Da solche Scan-Läufe sehr aufwändig sein können, ist es empfehlenswert, sie außerhalb der Spitzenlastzeiten durchzuführen. Welchen Parallelisierungsgrad dabei der beste ist, hängt von der Leistungsfähigkeit des Systems ab und kann nicht pauschal beantwortet werden. Um zu überprüfen, ob in der Datenbank Inhalte gespeichert sind, die im aktuellen Datenbankzeichensatz nicht korrekt gespeichert werden konnten, gibt es eine einfache [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 8 / 21 Möglichkeit: Sie führen einen csscan-Durchlauf aus, bei dem FROMCHAR und TOCHAR gleich dem aktuellen Datenbankzeichensatz sind. Oracle> csscan userid='"/ as sysdba"' full=y fromchar=WE8ISO8859P15 tochar=WE8ISO8859P15 ARRAY=1024000 LOG=/tmp/csscan_source_db PROCESS=8; Die Zusammenfassung von csscan sieht wie folgt aus: [Data Dictionary Conversion Summary] Datatype Changeless Convertible Truncation Lossy ------------------ ---------------------------- ----------- ----------VARCHAR2 1.778.335 0 0 0 CHAR 94 0 0 0 LONG 128.56 0 0 0 VARRAY 19.812 0 0 0 ------------------ ---------------------------- ----------- ----------Total 1.948.87 0 0 0 Total in percentage 100,000% 0,000% 0,000% 0,000% The data dictionary can be safely migrated using the CSALTER script [Application Data Conversion Summary] Datatype Changeless Convertible Truncation Lossy ------------------ ---------------- ----------- ----------- ----------VARCHAR2 682.846.037 0 0 872.321 CHAR 134.119.259 0 0 0 LONG 36.528 0 0 0 CLOB 0 0 0 0 VARRAY 0 0 0 0 ------------------ ---------------- ----------- ----------- ----------Total 817.001.824 0 0 872.321 Total in percentage 99,893% 0,000% 0,000% 0,107% Insgesamt teilt csscan die untersuchten Datensätze bzw. –spalten in vier Gruppen ein: Typ CHANGELESS CONVERTIBLE TRUNCATION LOSSY Bedeutung Die Spalte enthält nur ASCII-Zeichen zwischen 0-127. Bei einer Zeichensatzänderung müssen diese Spalten nicht geändert werden. Die Spalte enthält Zeichen, die bei der Zeichensatzänderung konvertiert werden müssen. Wenn wir als „TOCHAR“ einen Unicode-Zeichensatz angegeben haben, dann werden hier die Spalten aufgeführt, die bei der Umstellung des Zeichensatzes so wachsen, dass die derzeitige maximale Länge überschritten wird und der Inhalt abgeschnitten wird. Die Spalte enthält Zeichen, die nicht in den neuen Zeichensatz konvertiert werden können. Es droht Datenverlust. Vor einer Konvertierung müssen die Fehler bereinigt werden. Wenden wir uns zuerst den „Lossy“-Daten zu. Dabei hilft eine von csscan erzeugte Datei, die die problematischen Datensätze aufgeschlüsselt nach Tabellen und Spalten enthält: [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 9 / 21 .. User : DEMO Table : DEMO_TABELLE Column: BESCHREIBUNG Type : VARCHAR2(80) Number of Exceptions : 13 Max Post Conversion Data Size: 81 ROWID Exception Type Data(first 30 bytes) ------------------ ------------------ ----------------------------AAAPYvAANAAADOfAAr lossy conversion GEBŽUDE AAAPYvAANAAADOPAAs lossy conversion FIRMENANGEH(tm)RIGE AAAPYvAANAAADOvAAP lossy conversion EINFšHRUNG WINDOWS7 .. Eine der wichtigsten und meist aufwändigsten Aufgaben im Rahmen eines ZeichensatzMigrationsprojektes ist es, die in solchen Fällen erforderliche Datenbereinigung durchzuführen. Tipp: Bei der Datenbereinigung hilft ein kleines Skript, das die View CSMV$ERRORS im Schema CSMIG nutzt. Diese View enthält die Schema-, Tabellen- und Spaltennamen der betroffenen Datensätze sowie die zugehörigen Rowids. SQL> set serveroutput on SQL> BEGIN FOR c_rec IN (SELECT owner_name,table_name,column_name,data_rowid FROM csmig.csmv$errors WHERE error_type='DATA_LOSS') LOOP sql_statement := 'SELECT '||c_rec.column_name||' from '||c_rec.owner_name||'.'||c_rec.table_name||' where rowid='||chr(39)||c_rec.data_rowid||chr(39); execute immediate sql_statement into old_value; dbms_output.put_line ('UPDATE '||c_rec.owner_name||'.'||c_rec.table_name|| ' SET '||c_rec.column_name||'='||chr(39)||old_value||chr(39)|| ' where ROWID='||chr(39)||c_rec.data_rowid||chr(39)||';'); END LOOP; END; / Das obige Skript erzeugt eine Ausgabe der Form .. UPDATE DEMO.DEMO_TABELLE SET BESCHREIBUNG=’GEBŽUDE’ WHERE ROWID=’ AAAPYvAANAAADOfAAr’; .. D.h., es generiert UPDATE-Befehle, die dazu genutzt werden können, die fehlerhaften Daten zu korrigieren. Dazu wird die Ausgabe des Skriptes in eine Datei geschrieben und anschließend in einem Editor bearbeitet. Danach wird das modifizierte Skript ausgeführt, damit die Datenbank-Inhalte korrigiert werden. Der vorsichtige DBA macht natürlich vor solchen größeren Änderungen einen Backup der Datenbank! Anschließend erfolgt ein neuer csscan-Lauf. Dies ist ein iteratives Verfahren, das wir so oft wiederholen sollten, bis alle Datenfehler korrigiert sind. Tipp: Die Ursache für solche „Lossy“-Daten ist in den meisten Fällen eine fehlerhafte NLS_LANG-Einstellung auf den Datenbank-Clients. Neben der Korrektur der vorhandenen [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 10 / 21 Daten müssen wir daher auch Ursachenforschung betreiben und die fehlerhaften Systemeinstellungen der Clients korrigieren. Die obige Ausgabe weist auch noch auf ein anderes mögliches Problem hin, das in der csscan-Zusammenfassung als „TRUNCATION“ bezeichnet wird. Wie bereits erwähnt, benötigen Zeichen in der Unicode-Codierung oftmals mehr als ein Byte. Die maximale Länge, die eine Spalte nach einer Konvertierung in Unicode belegt, wird als „Max Post Conversion Data Size“ angegeben. Dieser Wert ist in Byte. .. Column: BESCHREIBUNG Type : VARCHAR2(80) Number of Exceptions : 13 Max Post Conversion Data Size: 81 .. Der Wert ist unproblematisch wenn er kleiner als 4.000 (bei VARCHAR2-Spalten) bzw. 2.000 (CHAR) ist. Dann reicht die Umstellung der NLS_LENGTH_SEMANTICS der Spalte auf „CHAR“. Ist der Wert größer als 4.000 bzw. 2.000, dann müssen wir entweder zu CLOBs wechseln, in Oracle 12.1 die maximale Länge der VARCHAR2-Spalten auf 32.767 erhöhen oder die Daten sinnvoll kürzen. Letzteres ist – sofern möglich - der pragmatischere und meist schnellere Weg, denn dann sind keine Änderungen an der Applikation erforderlich. Wenn alle durch csscan ermittelten Probleme gelöst sind und ein abschließender csscan-Lauf dies auch bestätigt, dann kommt der nächste Schritt, die Änderung des Datenbankzeichensatzes. Dafür bietet Oracle das Skript csalter.plb an. Aber Achtung, csalter macht nur einen Teil der Arbeit: csalter ändert nur die Zeichensatz-Metadaten in der Datenbank und passt die CLOB-Daten der Oracle-Default-Schemata an. Die Applikationsdaten bleiben unverändert! D.h., die Verwendung von csalter ist daher nur dann sinnvoll, wenn csscan ergeben hat, dass es nur „changeless“-Applikationsdaten in der Datenbank gibt! Ansonsten müssen wir die Applikationsdaten selbst anpassen, d.h. z.B. mittels „Create-TableAs-Select“,„ALTER TABLE .. MOVE“ oder export/import korrigieren. Wenn wir den Zeichensatz mit csalter ändern wollen, ist der Ablauf wie folgt: [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 11 / 21 SQL> startup restrict ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2234960 bytes Variable Size 360711600 bytes Database Buffers 675282944 bytes Redo Buffers 5656576 bytes Database mounted. Database opened. SQL> @?/rdbms/admin/csalter.plb 0 rows created. Function created. Function created. Procedure created. This script will update the content of the Oracle Data Dictionary. Please ensure you have a full backup before initiating this procedure. Would you like to proceed (Y/N)? Y old 6: if (UPPER('&conf') <> 'Y') then new 6: if (UPPER('Y') <> 'Y') then Checking data validility... begin converting system objects [..] PL/SQL procedure successfully completed. Alter the database character set... CSALTER operation completed, please restart database PL/SQL procedure successfully completed. 0 rows deleted. Function dropped. Function dropped. Procedure dropped. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2234960 bytes Variable Size 360711600 bytes Database Buffers 675282944 bytes Redo Buffers 5656576 bytes Database mounted. Database opened. SQL> Anschließend müssen, wie bereits erwähnt, noch die Applikationstabellen angepasst werden. Da dies meist die Hauptarbeit bei der Zeichensatzmigration ist, ist der Weg mittels csscan/csalter oftmals nicht praktikabel. Stattdessen empfiehlt es sich, eine neue Datenbank mit Unicode-Zeichensatz aufzubauen (vgl. Abschnitt 7). [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 12 / 21 Achtung: In Oracle 12.1 werden csscan und csalter nicht mehr unterstützt. Nachfolger ist der Database Migration Assistant for Unicode (DMU), der mit 12.1 mit ausgeliefert wird. Im Gegensatz zu csscan/csalter unterstützt DMU allerdings nur den Wechsel zu Unicode, nicht aber zu anderen Zeichensätzen. Bei einem denkbaren Wechsel von WE8ISO8859P1 zu WE8ISO8859P15 können wir DMU also nicht einsetzen. 6. Database Migration Assistant for Unicode (11.2 und 12.1) Database Migration Assistant for Unicode (DMU) ist der Nachfolger des Toolsets csscan/csalter. DMU liegt derzeit in der Version 1.2 vor und muss für die Verwendung mit Oracle 11.2 über das Oracle Technet oder über My Oracle Support (Patch 16345720) bezogen werden. Bei Oracle 12.1 wird DMU 1.2 mit ausgeliefert. DMU ist eine GUI-basierte Lösung, die den kompletten Workflow einer Zeichensatzmigration unterstützt. Der Ablauf selbst unterscheidet sich nicht wesentlich von dem Workflow bei der Verwendung von csscan und csalter. Dabei stellt DMU diesen Workflow sehr schön in der GUI dar: Abbildung 3: Überblick DMU Workflow [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 13 / 21 6.1 Voraussetzungen 6.1.1 Server-Software Bei Oracle 12.1 sind alle software-seitigen Voraussetzungen für den Einsatz von DMU „out-ofthe-box“ erfüllt. Für Oracle 11.2 gilt: auf dem Datenbank-Server muss der Patch 9825461 installiert sein. Dieser Patch ist in 11.2.0.3 und allen höheren Versionen enthalten. Ist jedoch noch 11.2.0.2 installiert, dann ergibt sich dadurch eine Einschränkung für die Verwendung von DMU. Für den Patch wird z.B. mindestens 11.2.0.2.5 (=PSU Januar 2012) vorausgesetzt und der Patch ist nicht für alle Plattformen verfügbar. Schlimmstenfalls bleibt also nur die „FallbackVariante“ csscan/csalter. Einzelheiten zu den serverseitigen Anforderungen sind in der MOS-Note 1272374.1.1, der zentralen MOS-Note zu DMU, zu finden. 6.1.2 Datenbank In der Datenbank, die mit DMU analysiert und bearbeitet werden soll, muss das Package DBMS_DUMA installiert sein: SQL> show user USER is "SYS" SQL> @?/rdbms/admin/prvtdumi.plb Library created. Package created. No errors. Package body created. No errors. 6.1.3 Client Auf Client-Seite muss ein J2SE SDK 6 installiert sein. J2SE SDK 7 funktioniert nicht. Für den DMU-Client ist keine Installation erforderlich. Es reicht, die ZIP-Datei auszupacken. DMU wird dann über dmu.sh (Unix/Linux) bzw. dmu64.exe (64-Bit-Windows) oder dmu32.exe (32-Bit-Windows oder 64-Bit-Windows mit 32-Bit-JDK) gestartet. 6.2 Repository-Installation Analog zum Datenbank-User CSMIG beim csscan benötigt auch DMU einige Tabellen in der Datenbank. Wenn sich DMU bei einer Datenbank anmeldet, dann prüft DMU, ob das Repository installiert ist. Ist dies nicht der Fall, so muss es installiert werden, bevor die Arbeit beginnen kann. Dabei führt uns der „Repository Configuration Wizard“ durch den Installationsprozess: [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 14 / 21 Abbildung 4 : DMU-Repository Configuration Wizard 6.3 Scannen der Datenbank Genauso wie bei csscan muss auch beim DMU vor dem Zeichensatzwechsel geprüft werden, ob alle Datenbankinhalte konvertiert werden können. Auch dieser Schritt wird von einem Wizard unterstützt: Abbildung 5: DMU Scan-Wizard Der Scan-Fortschritt kann im DMU verfolgt werden: Abbildung 6: Scan-Prozess im DMU [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 15 / 21 Tipp: DMU scannt auch die Tabellen im Recyclebin. Daher sollte dieser vorher datenbankweit mittels „PURGE DBA_RECYCLEBIN;“ gelöscht werden. Meldet der DMU nach Abschluss des Scans, dass es „Migration Issues“ gibt, müssen diese gelöst werden. Abbildung 7: DMU migration issues Auch bei der Bearbeitung der Probleme unterstützt DMU den Datenbankadministrator. 6.4 Bereinigung der Daten Nach dem Scan stellt DMU die Ergebnisse und die aufgetretenen Probleme übersichtlich dar: Abbildung 8: Übersicht über die Scan-Ergebnisse Ein Drill-Down auf eine problematische Tabelle zeigt das Ergebnis auf Tabellen-Ebene: Abbildung 9: Scan-Ergebnisse auf Tabellen-Ebene Hier zeigt sich die von csscan bekannte Aufteilung der Ergebnisse: DMU need no change csscan CHANGELESS need conversion CONVERTIBLE Bedeutung Die Spalte enthält nur ASCII-Zeichen zwischen 0-127. Bei einer Zeichensatzänderung müssen diese Spalten nicht geändert werden. Die Spalte enthält Zeichen, die bei der Zeichensatz- [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 16 / 21 invalid representation LOSSY over column limit TRUNCATION over type limit TRUNCATION änderung konvertiert werden müssen. Die Spalte enthält Zeichen, die nicht in den neuen Zeichensatz konvertiert werden können. Es droht Datenverlust. Vor einer Konvertierung müssen die Fehler bereinigt werden. Die Byte-Länge im Ziel-Zeichensatz ist größer als die aktuelle Länge. Ein Wechsel der NLS_LENGTH_SEMANTICS für die Spalte ist erforderlich. Nach der Konvertierung wäre die Spalte länger als das Oracle-seitige Limit von 2000 Byte (CHAR) bzw. 4000 Byte (VARCHAR2). Die Daten müssen gekürzt werden oder ein Wechsel zu CLOBS ist fällig. Die Datenfehler können direkt innerhalb von DMU korrigiert werden. Ab diesem Punkt geht DMU damit über den Funktionsumfang von csscan hinaus, denn die Datenbereinigung wurde dort nicht unterstützt. Ein weiterer Schritt in den Migrationsvorbereitungen ist die Umstellung der Spalten von „BYTE“ auf „CHAR“-Längenangaben. Auch wenn dieser Wechsel im Ausgangszeichensatz keine Bedeutung hat ist er für die spätere Zeichensatzänderung sehr wichtig. Auch diese Änderung kann vollständig mit dem DMU durchgeführt werden. Nach Abschluss der Bereinigungsaktivitäten muss die Datenbank mit DMU erneut geprüft werden. Abbildung 10: Abschluss der Vorbereitungen im DMU Erst wenn die Prüfung durch DMU ergeben hat, dass alle Probleme gelöst sind, erlaubt DMU den nächsten Schritt, die eigentliche Zeichensatzänderung. 6.5 Wechsel des Zeichensatzes mit DMU Der Ablauf der Zeichensatzänderung ist im DMU konfigurierbar und parametrisierbar. So kann z.B. angegeben werden, mit welchem Parallelitätsgrad die Daten modifiziert werden sollen oder wie die einzelnen Applikationstabellen konvertiert werden sollen. Dabei stehen die Varianten „UPDATE“ oder „Create-Table-as-Select“ (CTAS) zur Auswahl. Wenn ein Großteil der Datensätze konvertiert werden muss, ist CTAS meist die schnellere Variante. Tipp: Um zu verhindern, dass Anwendersessions die Datenbank-Konvertierung stören, sollte die Datenbank heruntergefahren werden und dann im RESTRICTED SESSION-Modus neu gestartet werden. DMU setzt die Datenbank vor Beginn der Konvertierung zwar auch von sich aus in den RESTRICTED SESSION-Modus, aber laufende Datenbanksitzungen werden durch diese Änderung bekannterweise nicht gestoppt. Lt. DMU-Dokumentation ist der Ablauf der Konvertierung wie folgt: 1. Die Datenbank wird in den Restricted-Mode gesetzt 2. Datenbank-Jobs werden temporär deaktiviert 3. Indizes werden gedroppt [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 17 / 21 4. 5. 6. 7. Trigger und Constraints werden deaktiviert Applikationsdaten und Data-Dictionary-Daten werden konvertiert CLOBs im Data-Dictionary werden konvertiert Der Datenbank-Zeichensatz wird geändert („ALTER DATABASE .. CHARACTER SET ..“) 8. Trigger und Constraints werden wieder aktiviert und die Indizes werden neu angelegt. 9. Instanz-Parameter werden auf ihre Originalwerte zurückgesetzt. Vor der Zeichensatzkonvertierung machen wir natürlich einen Backup der Datenbank, damit wir für den Fall der Fälle eine Fallback-Lösung haben. Abbildung 11: Start der Zeichensatzkonvertierung Abbildung 12: Konvertierungsfortschritt im DMU Sollte es zu Fehlern kommen, erlaubt DMU auch ein begrenztes Error-Handling. So können z.B. Teilschritte, bei denen es zu Problemen gekommen ist, übersprungen werden und die Probleme können später gelöst werden. [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 18 / 21 6.6 Zusammenfassung DMU ist ein sehr mächtiges und für eine Zeichensatzkonvertierung. Die Bedeutung, die Oracle diesem Tool bemisst, zeigt sich in der ausführlichen Dokumentation und der Vielzahl der MOSNotes, mit denen der Anwender beim Einsatz des DMU-Tools unterstützt wird. Der Ansatz, den Zeichensatz einer vorhandenen Datenbank zu ändern, inklusive der Applikationsdaten, ist allerdings nicht in jedem Fall sinnvoll, denn -­‐ -­‐ im Fehlerfall steht meist keine schnelle Fallback-Option zur Verfügung die notwendigen Anpassungen der Datenbank-Inhalte mittels UPDATEBefehlen oder CTAS sind bei größeren Datenmengen sehr zeitaufwändig und sorgen für ein hohes Aufkommen an Archivelog-Informationen Daher bietet es sich an, einen Zeichensatzwechsel mit einem Datenbankwechsel zu verbinden. 7. Zeichensatz- und Datenbank-Wechsel Aufgrund der oben erwähnten Nachteile bei der Zeichensatzkonvertierung in einer vorhandenen Datenbank ist es in vielen Fällen eine Überlegung wert, eine neue Datenbank mit Unicode als Datenbank-Zeichensatz aufzubauen und die vorhandenen Daten in diese neue Datenbank zu transferieren. In diesem Fall ergibt sich folgender Grob-Ablauf: 1. 2. 3. 4. 5. 6. Prüfen der vorhandenen Datenbank mittels csscan oder DMU Lösen der ermittelten Probleme („lossy data“ oder „truncation data“) Aufbau der neuen Datenbank mit Unicode als Zeichensatz Anlegen der Applikationsstrukturen (Tablespaces, User) Anlegen der Applikationstabellen mit NLS_LENGTH_SEMANTICS „CHAR“ Transferieren der Daten aus der vorhandenen Datenbank mittels export/import, DataPump, CTAS oder Streams Dieses Vorgehen hat folgende Vorteile: 1. Die Ausgangsdatenbank bleibt unverändert und kann sehr schnell als FallbackLösung herangezogen werden. 2. Die Datenbank wird reorganisiert. 3. Der Zeichensatzwechsel kann z.B. mit einem Upgrade auf eine neue Version oder einem Serverwechsel verbunden werden. 4. Enthält die Datenbank „Read-Only-Tabellen“ oder historische Daten, die nicht geändert werden, können diese vor dem eigentlichen Datenbank-Wechsel transferiert werden. Dadurch wird die Auszeit beim späteren Datenbankwechsel verkürzt. 5. Das Befüllen der Daten kann im NOARCHIVELOG-Modus erfolgen. Dadurch ist es schneller. Nachteil dieses Vorgehens ist natürlich der doppelte Bedarf an Plattenplatz während der Migration. [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 19 / 21 8th NLS_LENGTH_SEMANTICS, SQL & PL/SQL Bei einem Wechsel zu Unicode müssen nicht nur die Datenbank-Inhalte betrachtet werden. Auch im Applikationscode können Änderungen erforderlich sein. 8.1 PL/SQL-Code Da PL/SQL-Prozeduren und –Funktionen bei der Deklaration von CHAR- oder VARCHAR2Variablen auch Längenangaben enthalten, müssen sie auch um die Angabe „CHAR“ ergänzt werden. 8.2 SQL-Funktionen Bei SQL-Funktionen, die sich in irgendeiner Form auf die Länge von Zeichenketten beziehen, muss angegeben werden, wie denn die zugehörige Längeneinheit ist: Einheit wird aus dem Zeichensatz des Eingabestrings übernommen LENGTH SUBSTR INSTR Längenangabe in Byte LENGTHB SUBSTRB INSTRB Längenangabe in UnicodeZeichen LENGTHC SUBSTRC INSTRC (siehe auch: Oracle SQL Reference 11g Release 2) 9. Zusammenfassung In Zeiten der Globalisierung geht der Trend beim Datenbankzeichensatz eindeutig Richtung Unicode. Mit csscan/csalter und dem umfangreicheren Nachfolgetool DMU bietet Oracle gute Werkzeuge für eine Zeichensatzumstellung. Wie bei den meisten Projekten gilt auch bei Konvertierungsprojekten das Pareto-Prinzip: Der Großteil besteht aus Vorbereitung, sorgfältiger Datenbereinigung und ausführlichen Tests und der kleinere Teil aus der eigentlichen Zeichensatzkonvertierung. Viel Erfolg beim Einsatz von Trivadis-Know-how wünscht Ihnen Markus Flechtner Trivadis GmbH Werdener Straße 4 D-40227 Düsseldorf Internet: www.trivadis.com Tel: Fax: Mail: +49 211 586 66 - 470 +49 211 586 66 - 471 [email protected] [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 20 / 21 Literatur und Links Links ! ! ! ! ! Oracle Documentation (http://docs.oracle.com/) ! Oracle® Database Globalization Support Guide 11.2 ! Oracle® Database Globalization Support Guide 12.1 Unicode allgemein ! http://www.unicode.org/ Database Migration Assistant for Unicode ! http://www.oracle.com/technetwork/products/globalization/dmu/overview/ index.html ! Near-Zero Downtime Migration Model with DMU & Oracle Streams - http://www.oracle.com/technetwork/products/globalization/dmu/le arnmore/nzd-migration-524223.html Oracle Whitepaper „Character Set Migration – Best Practices“ (http://www.oracle.com/technetwork/products/globalization/twp-character-setmigration-best-pr-128766.pdf) NLS_LANG-FAQ http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html MOS-Notes ! ! ! ! ! ! ! ! ! ! ! ! 144808.1 - Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) 158577.1 - NLS_LANG Explained (How does Client-Server Character Conversion Work?) 264157.1 - The correct NLS_LANG setting in Unix Environments 179133.1 - The correct NLS_LANG in a Windows Environment 276914.1 - The National Character set ( NLS_NCHAR_Character set ) in Oracle 9i, 10g and 11g 225912.1 - Changing the Database Character Set ( NLS_Character set ) 69518.1 - Storing and Checking Character Codepoints in an UTF8/AL32UTF8 (Unicode) database 788156.1 - AL32UTF8 / UTF8 (Unicode) Database Character set Implications 745809.1 - Installing and configuring Csscan in 10g and 11g (Database Character set Scanner) 444701.1 - Csscan output explained 1272374.1 - The Database Migration Assistant for Unicode (DMU) Tool 1522722.1 - Known Issues With The DMU Tool version 1.2 [email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 Datum 17.12.2013 . Seite 21 / 21
