Oracle Database 11g Express Edition for Microsoft Windows:
Hinweise zur Deinstallation und Installation sowie erste Schritte

+ andere TechDocs
+ SQL-Beispiele
+ Vererbung
+



Inhalt

  1. Oracle Database XE deinstallieren
  2. Oracle Database XE 11.2 unter 32-bit-Windows per "Silent Installation" installieren
  3. Oracle Database XE 11.2 unter 64-bit-Windows 7 per GUI installieren
  4. Weitere Hinweise zur Installation
  5. Weitere Hinweise
  6. Probleme mit UTF-8 unter Oracle
    Encoding und Feldlängen, Ändern des Oracle-Datenbank-Character-Encodings, Einstellung von NLS_LANG
  7. Beispiel für das Anlegen eines Testusers
  8. Testprogramm für das Speichern von Sonderzeichen in der Datenbank
  9. Beispiel für ein einfaches Testprogramm zur Performance-Messung
  10. AWR-Reports erstellen
  11. Export / Import per Data Pump


Oracle Database XE deinstallieren

Bevor Sie die Oracle Database XE installieren, müssen vorherige Oracle-Datenbankinstallationen entfernt werden.

  1. Falls noch eine Oracle-Datenbank in Betrieb ist: Mit der StopDB.bat stoppen, zum Beispiel für Oracle XE 11.2:

    C:\oraclexe\app\oracle\product\11.2.0\server\bin\StopDB.bat

  2. Standardmäßig werden Programme unter Windows folgendermaßen deinstalliert:

    Start | Systemsteuerung | Programm deinstallieren | Oracle Database 11g Express Edition | Deinstallieren.

  3. Falls das nicht funktioniert und falls es sich um eine Oracle XE 11.2 handelt:

    Downloaden Sie OracleXE112_Win32.zip, entzippen Sie in C:\OracleXE-Install und versuchen Sie:

    cd /D C:\OracleXE-Install\OracleXE112_Win32\DISK1

    setup.exe /s /f1"C:\OracleXE-Install\OracleXE112_Win32\DISK1\response\OracleXE-remove.iss" /f2"C:\OracleXE-Install\OracleXE-remove.log"

    Warten Sie bis C:\OracleXE-Install\OracleXE-remove.log geschrieben wurde.

  4. Überprüfen Sie über
    Start | Systemsteuerung | System und Sicherheit | Verwaltung | Dienste
    (oder alternativ: rechter Mausklick auf Computer bzw. Arbeitsplatz | Verwalten | Dienste und Anwendungen | Dienste),
    ob noch Oracle-DB-Dienste installiert sind, und stoppen und löschen Sie sie gegebenenfalls:

    sc delete OracleServiceXE

    sc delete OracleJobSchedulerXE

    sc delete OracleMTSRecoveryService

    sc delete OracleXEClrAgent

    sc delete OracleXETNSListener

    (Erläuterungen zum sc-Kommando finden Sie in der Windows Command-line Reference.)

  5. Löschen Sie das bisherige Oracle-Datenbankinstallationsverzeichnis, zum Beispiel so:

    rd /S /Q C:\oraclexe

    Falls das nicht möglich ist: Starten Sie den PC neu und versuchen es erneut.

  6. Falls es immer noch Probleme wegen vorheriger Oracle-DB-Installationen gibt, müssen eventuell Registry-Schlüssel gelöscht werden (machen Sie das nur, wenn es unbedingt notwendig ist und Sie sich auskennen):
    Start | Ausführen... | regedit.exe:

    HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE (z.B. KEY_XE)

    HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*



Oracle Database XE 11.2 unter 32-bit-Windows per "Silent Installation" installieren

  1. Die folgende Beschreibung funktioniert mit der Version 11.2 nur unter 32-bit-Windows. Für 64-bit-Windows verfahren Sie bitte wie anschließend beschrieben.

  2. Sehen Sie sich die Oracle-XE-Doku an: http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm

  3. Prüfen Sie, dass keine Reste vorheriger Oracle-DB-Installationen existieren, und entfernen Sie diese (siehe oben).

  4. Erstellen Sie das Verzeichnis C:\OracleXE-Install. Downloaden Sie OracleXE112_Win32.zip und entzippen Sie in C:\OracleXE-Install.

  5. Kopieren Sie die so genannte "Response-Datei" OracleXE-install.iss:

    copy C:\OracleXE-Install\OracleXE112_Win32\DISK1\response\OracleXE-install.iss C:\OracleXE-Install

    Passen Sie in der kopierten C:\OracleXE-Install\OracleXE-install.iss den HTTPPort und das SYSPassword an, zum Beispiel so:

    ****************************************************************************************
    **                                                                                    **
    **  Response file to perform silent install of Oracle Database 11g Express Edition    **
    **                                                                                    **
    **  Values for the following variables are configurable:                              **
    **  szDir - Provide a valid path                                                      **
    **  TNSPort - Provide any valid available port number                                 **
    **  MTSPort - Provide any valid available port number                                 **
    **  HTTPPort - Provide any valid available port number                                **
    **  SYSPassword - Provide a valid password string                                     **
    **                                                                                    **
    ****************************************************************************************
    
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-DlgOrder]
    Dlg0={05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdWelcome-0
    Count=7
    Dlg1={05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdLicense2Rtf-0
    Dlg2={05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdComponentDialog-0
    Dlg3={05A7B662-80A3-4EB9-AE1D-89A62449431C}-AskPort-13013
    Dlg4={05A7B662-80A3-4EB9-AE1D-89A62449431C}-AskSYSPassword-13011
    Dlg5={05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdStartCopy-0
    Dlg6={05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdFinish-0
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdWelcome-0]
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdLicense2Rtf-0]
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdComponentDialog-0]
    szDir=C:\oraclexe\
    Component-type=string
    Component-count=1
    Component-0=DefaultFeature
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-AskPort-13013]
    TNSPort=1521
    MTSPort=2031
    HTTPPort=2020
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-AskSYSPassword-13011]
    SYSPassword=geheim
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdStartCopy-0]
    Result=1
    [{05A7B662-80A3-4EB9-AE1D-89A62449431C}-SdFinish-0]
    Result=1
    bOpt1=0
    bOpt2=0
    

    Der Default-HTTPPort ist 8080. Da diese Portnummer auch von vielen anderen Anwendungen verwendet wird, sollten Sie eine andere wählen.

  6. Erstellen Sie für die "Silent Installation" folgende Batchdatei OracleXE-install.bat (verwenden Sie eine Batchdatei, weil es sonst bei direktem Aufruf schwieriger festzustellen wäre, wann setup.exe fertig ist):

    cd /D C:\OracleXE-Install
    if exist C:\WINDOWS\OracleDatabaseXEServerInstall.log del C:\WINDOWS\OracleDatabaseXEServerInstall.log
    if exist OracleXE-install.log del OracleXE-install.log
    cd /D C:\OracleXE-Install\OracleXE112_Win32\DISK1
    setup.exe /s /f1"C:\OracleXE-Install\OracleXE-install.iss" /f2"C:\OracleXE-Install\OracleXE-install.log"
    cd /D C:\OracleXE-Install
    if exist C:\WINDOWS\OracleDatabaseXEServerInstall.log type C:\WINDOWS\OracleDatabaseXEServerInstall.log
    type OracleXE-install.log
    

    Öffnen Sie ein Kommandozeilenfenster mit Administratorrechten, führen Sie die Batchdatei aus und warten Sie bis C:\OracleXE-Install\OracleXE-install.log geschrieben wurde.

    Dort muss "ResultCode=0" eingetragen sein.

    Eventuell müssen Sie den Virenscanner temporär deaktivieren, weil sonst möglicherweise setup.exe in die Quarantäne verschoben wird.

  7. Falls Sie folgende Fehlermeldung erhalten:

    [ResponseResult]
    ResultCode=-3

    -3 bedeutet: "Required data not found". Kontrollieren Sie genau den Inhalt und die Pfadangabe der Datei OracleXE-install.iss. Der bei /f1 angegebene Pfad muss ein Absolutpfad sein.
    Der Fehlercode -3 kann aber auch völlig andere Ursachen haben, beispielsweise 64-bit- statt 32-bit-Windows.

  8. Fahren Sie fort mit Weitere Hinweise zur Installation.



Oracle Database XE 11.2 unter 64-bit-Windows 7 per GUI installieren

  1. Die oben beschriebene "Silent Installation" funktioniert mit OracleXE112_Win32.zip nicht unter Windows 7 64 bit.
    Und auch die Ausführung von setup.exe als GUI-Anwendung funktioniert nicht fehlerfrei (Stand Dezember 2011), Sie erhalten die Fehlermeldung:
    "The installer is unable to instantiate the file C:\Users\...\AppData\Local\Temp\{...}\KEY_XE.reg. The file does not appear to exist."
    Mit dem im Folgenden beschriebenen Trick funktioniert die Installation. Bitte beachten Sie aber, dass Oracle Database XE 11.2 nicht für 64-bit-Windows zertifiziert ist, und nur für Tests verwendet werden sollte.

  2. Prüfen Sie, dass keine Reste vorheriger Oracle-DB-Installationen existieren, und entfernen Sie diese (siehe oben).

  3. Erstellen Sie das Verzeichnis C:\OracleXE-Install. Downloaden Sie OracleXE112_Win32.zip und entzippen Sie in C:\OracleXE-Install.

  4. Starten Sie die GUI-Anwendung setup.exe mit Admin-Rechten (rechte Maustaste, "Als Administrator ausführen"), aber betätigen Sie keine Schaltfläche:

    cd /D C:\OracleXE-Install\OracleXE112_Win32\DISK1

    setup.exe

  5. Sobald das Installationsprogramm auf eine Eingabe wartet, suchen Sie nach zwei neu entstandenen mit "{" beginnenden Temp-Verzeichnissen. Verzweigen Sie in das Verzeichnis, welches OracleMTSRecoveryService.reg enthält und führen Sie aus:

    cd /D C:\Users\%USERNAME%\AppData\Local\Temp

    dir {*

    cd /D C:\Users\%USERNAME%\AppData\Local\Temp\{...}   ["..." ersetzen!]

    copy OracleMTSRecoveryService.reg KEY_XE.reg

  6. Anschließend fahren Sie normal in der setup.exe-GUI-Anwendung fort.

  7. Fahren Sie fort mit Weitere Hinweise zur Installation.



Weitere Hinweise zur Installation

  1. Folgendermaßen können Sie eine Oracle-XE-Datenbank stoppen und wieder starten (mit Admin-Rechten):

    • C:\oraclexe\app\oracle\product\11.2.0\server\bin\StopDb.bat

    • C:\oraclexe\app\oracle\product\11.2.0\server\bin\StartDB.bat

  2. Testen Sie:

    tnsping XE

    Es muss "OK" erscheinen.

  3. Überprüfen Sie über
    Start | Systemsteuerung | System und Sicherheit | Verwaltung | Dienste
    (oder alternativ: rechter Mausklick auf Computer bzw. Arbeitsplatz | Verwalten | Dienste und Anwendungen | Dienste),
    ob die Oracle-DB-Dienste korrekt installiert sind:

    OracleXE-Dienste

  4. Sehen Sie sich mit dem path-Kommando den eingestellten Such-PATH an:

    path

    Wahrscheinlich hat sich Oracle an den Beginn des Such-PATH gesetzt (z.B. mit c:\oraclexe\app\oracle\product\11.2.0\server\bin). Überlegen Sie sich, ob diese Reihenfolge für Sie korrekt ist.

    Ändern können Sie dies über:
    Windows-Taste + Pause-Taste | Erweiterte Systemeinstellungen | Reiter Erweitert | Umgebungsvariablen...

    Sie können diesen PATH-Eintrag auch entfernen, allerdings müssen Sie dann die DB-Tools wie z.B. sqlplus mit vorangestelltem Pfad aufrufen.

  5. Beachten Sie, dass defaultmäßig die Ports 1521, 2030 und 8080 belegt werden (siehe Oracle-XE Installation Guide). Normalerweise empfiehlt es sich den HTTP-Port 8080 freizuhalten, da sehr viele andere Applikationen ebenfalls diesen Port verwenden. Sie können den Oracle-XE-HTTP-Port entweder während der Installation definieren oder folgendermaßen nachträglich ändern:

    • Erstellen Sie folgendes PL/SQL-Skript OracleXE-HTTP-Port-umstellen.sql (bitte die neu gewählte Portnummer anpassen) (Genaueres finden Sie unter Remote External Jobs und XML DB HTTP Server):

      select dbms_xdb.gethttpport as "HTTP-Port", dbms_xdb.getftpport as "FTP-Port" from dual;
      begin
        dbms_xdb.sethttpport('48080');
        dbms_xdb.setftpport('0');
      end;
      /
      select dbms_xdb.gethttpport as "HTTP-Port", dbms_xdb.getftpport as "FTP-Port" from dual;
      exit
    • Führen Sie das DB-Skript als SYSTEM-User aus. Sie können ein beliebiges SQL-Tool verwenden (wie z.B. SQuirreL). Oder Sie verwenden SQL*Plus (bitte system/geheim@XE durch system/<system-Kennwort>@<sid> ersetzen):

      C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus system/geheim@XE @OracleXE-HTTP-Port-umstellen.sql

    • Falls Sie 48080 gesetzt haben, erreichen Sie die Oracle-XE-Webseite über:

      http://localhost:48080/apex

  6. Falls die DB auf einem anderen PC ist, kann sie mit SQL*Plus so erreicht werden:

    sqlplus <benutzer>/<passwort>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<host>)(Port=1521))(CONNECT_DATA=(SID=<sid>))) @<db-skript>

    oder

    sqlplus <benutzer>/<passwort>@<host>:1521/<sid> @<db-skript>

    Eventuell müssen Sie hierzu die Datei tnsnames.ora anpassen oder erweitern.

  7. Falls Sie die Fehlermeldung "ORA-28001: the password has expired" erhalten bzw. vermeiden wollen, können Sie das Ablaufen des Passworts in Entwicklungs- und Testumgebungen ausschalten (in produktiven Umgebungen sollte stattdessen das Passwort geändert werden):

    • An der Datenbank als system-Benutzer anmelden:

      sqlplus system/<systempassword>@<sid>

    • Ausführen:

      Alter profile DEFAULT limit password_life_time UNLIMITED;

      Alter profile DEFAULT limit password_reuse_max UNLIMITED password_reuse_time UNLIMITED;

      Select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users order by ACCOUNT_STATUS;

      Select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where ACCOUNT_STATUS like '%EXPIRED%' order by USERNAME;

      --> <username>, DEFAULT, EXPIRED...

      Select password from sys.user$ where name = '<username>';

      --> <userpasswordvalue>

      Alter user <username> identified by values '<userpasswordvalue>';

      Select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = '<username>';

      --> <username>, DEFAULT, OPEN

  8. Es folgen einige speziellere Konfigurationen, die nur in bestimmten Fällen sinnvoll sind. Sie werden nur kurz gezeigt. Für Erläuterungen sehen Sie sich bitte die Oracle-Dokumentation an. Bitte ersetzen Sie jeweils geheim durch das passende Passwort.

    • Erhöhen Sie die erlaubte Anzahl an Prozessen (analog können Sie auch SESSIONS und TRANSACTIONS erhöhen):

      sqlplus system/geheim@XE

      ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;
      Select name, value from v$parameter where name in ( 'processes', 'sessions', 'transactions' );
      exit
    • Sie können ein Datenbankverzeichnis erstellen und konfigurieren (Passen sie den Laufwerksbuchstaben an):

      md D:/oraclexe/oradata

      sqlplus sys/geheim@XE as sysdba

      grant execute on dbms_lock to system;
      alter system set db_create_file_dest='D:/oraclexe/oradata';
      exit
    • Falls Sie die Authentifikation durch Windows nicht verwenden wollen, ersetzen Sie in der Datei \oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora

      SQLNET.AUTHENTICATION_SERVICES = (NTS)

      durch

      SQLNET.AUTHENTICATION_SERVICES = (NONE)

  9. Sehen Sie sich die Konfigurationsdatei C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora an.

  10. Wenn Sie als SQL-Client SqlPlus verwenden, muss die NLS_LANG-Variable korrekt gesetzt sein. Siehe hierzu weiter unten: Einstellung von NLS_LANG.



Weitere Hinweise

  1. SYS, SYSTEM, SYSDBA:

    • "sys" ist ein Benutzer ("user") mit der Rolle "DBA" (mit Administratorrechten), der bei der Installation eingerichtet wird und die meisten Rechte hat ("Owner of the Database and Owner of the Data Dictionary"). "sys" kann sich mit "sysdba"-Privilegien mit der Datenbank verbinden ("connect sys/<password> as sysdba").

    • "system" ist ein Benutzer ("user") mit der Rolle "DBA" (mit Administratorrechten), der bei der Installation eingerichtet wird und die zweitmeisten Rechte hat. Administrative Aufgaben sollten bevorzugt als "system"-Benutzer ausgeführt werden.

    • "sysdba" ist weder ein Benutzer noch eine Rolle, sondern ist ein "Privileg" (also eine besondere Berechtigung). Wenn sich der "sys"-Benutzer "as sysdba" anmeldet, verfügt er über weitreichendste Rechte (z.B. Erzeugung der Datenbank, Herunterfahren, Backup und Recovery).

  2. Interessehalber können Sie sich ansehen (einige Kommandos benötigen System-Rechte):

    • Select * from Dba_Users;

    • Select * from User_Tablespaces;

    • Select * from Dba_Data_Files;

    • Select * from v$version;

    • Select * from v$parameter;

    • Select * from v$sysstat; // where name = 'user commits' or name = 'user rollbacks';

    • Select * from v$system_event;

    • Select * from v$sysmetric_history order by End_Time desc, Value desc;

  3. Falls Sie die Größe Ihrer Datenbanktabellen ermitteln wollen:

    • -- MByte pro Tabelle/Index
      Select name as Tabellenname, sum(mb) as MByte
        from (
          Select nvl2(l.segment_name, l.table_name, s.segment_name) name, round(bytes / 1024 / 1024) mb
          from   user_segments s, user_lobs l
          where  s.segment_name = l.segment_name(+) and round(bytes / 1024 / 1024) > 0
          order  by 1, 2 )
        group by name order by MByte desc;

  4. Um hilfreiche Informationen zu anderen angemeldeten Benutzern bzw. Systemen und Programmen zu erhalten, melden Sie sich als system-Benutzer an und führen aus:

      Select count(1), vs.Username, vs.Machine, vs.Program, Status
        from v$session vs where Username is not null
        group by vs.Username, vs.Machine, vs.Program, Status
        order by vs.Username, vs.Machine, vs.Program, Status;



Probleme mit UTF-8 unter Oracle

Encoding und Feldlängen

Mit der Oracle-Datenbank gibt es immer wieder Unsicherheiten zu folgende Fragen:

  1. Werden 'varchar2'-Texte in UTF-8 gespeichert?
  2. Reserviert 'varchar2(3000)' Platz für 3000 Bytes oder 3000 Characters?
  3. Passen in 'varchar2(3000 CHAR)' wirklich 3000 Characters?
  4. Wie ist die 4000-Bytes-Grenze zu verstehen?

Diese Fragen klingen trivial, sind es aber nicht.

Ob 'varchar2'-Texte in UTF-8 gespeichert werden, ist einstellbar. Die aktuelle Einstellung erfahren Sie über den NLS_CHARACTERSET-Parameter mit dem SQL-Kommando:

Select * from nls_database_parameters;

Das Ergebnis könnte zum Beispiel lauten:

ParameterValue
NLS_LANGUAGEGERMAN
NLS_TERRITORYGERMANY
NLS_CHARACTERSETAL32UTF8
NLS_NCHAR_CHARACTERSETAL16UTF16
NLS_LENGTH_SEMANTICSCHAR
NLS_SORTBINARY
NLS_COMPBINARY
NLS_NCHAR_CONV_EXCPFALSE

Ob 'varchar2(3000)' Platz für 3000 Bytes oder 3000 Characters reserviert, ist ebenfalls einstellbar. Die Basisvoreinstellung zeigt obiges 'select * from nls_database_parameters'-Kommando. Ist 'NLS_LENGTH_SEMANTICS' auf 'CHAR' eingestellt, reserviert 'varchar2(3000)' Platz für bis zu 3000 Characters.

Für einzelne Spalten kann dies abweichend von der Basisvoreinstellung vorgegeben werden:
'varchar2(3000 BYTE)' reserviert Platz für 3000 Bytes,
'varchar2(3000 CHAR)' reserviert Platz für bis zu 3000 Characters.

Bei UTF-8-Kodierung belegen die einzelnen Zeichen mindestens ein Byte und maximal vier Bytes (weiteres hierzu siehe http://de.wikipedia.org/wiki/Utf-8). Wenn der Oracle-'CHARACTERSET' auf UTF-8 eingestellt ist und 'NLS_LENGTH_SEMANTICS' auf 'BYTE' eingestellt ist, kann es also passieren, dass 'varchar2(3000)' nur 750 Zeichen speichern kann, falls der Text viele exotische Zeichen (mit 4-Byte-Kodes) enthält!

Häufig wird angenommen, dass man auf der sicheren Seite ist, wenn 'NLS_LENGTH_SEMANTICS' auf 'CHAR' eingestellt ist oder wenn die Spalte explizit per 'varchar2(3000 CHAR)' definiert ist. Leider kann trotzdem nicht davon ausgegangen werden, so zuverlässig 3000 Zeichen speichern zu können.

Zusätzlich zu der Obergrenze, die bei der Tabellendefinition bei 'varchar2(...)' definiert wird, gibt es bei Oracle eine zweite davon unabhängige Obergrenze: Es können maximal 4000 Bytes gespeichert werden. Diese Grenze gilt auch dann, wenn 'NLS_LENGTH_SEMANTICS' auf 'CHAR' eingestellt ist. Im schlimmsten Fall, wenn der Text viele exotische Zeichen (mit 4-Byte-Kodes) enthält, können trotz der 'varchar2(3000 CHAR)'-Definition nur 1000 Zeichen gespeichert werden!

Führen Sie folgende SQL-Kommandos aus (zum Beispiel per SQuirreL), um das Gesagte nachzuvollziehen (achten Sie darauf, dass Oracle auf UTF-8 eingestellt ist) (getestet mit 'Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit' und dem JDBC-Typ-4-Treiber 'oracle_db.jar' Version 9.2.0.1.0):

select * from nls_database_parameters;

create table MeinUtf8Test ( vrchr10Byte varchar2(10 BYTE), vrchr10Char varchar2(10 CHAR), vrchr3000Char varchar2(3000 CHAR) );

insert into MeinUtf8Test ( vrchr10Byte, vrchr10Char ) values ( '1234567890', 'äöüߧ€áàâ€' );

insert into MeinUtf8Test ( vrchr10Byte, vrchr10Char ) values ( '€€€', '€€€€€€€€€€' );

select * from MeinUtf8Test;

Bis hierher funktioniert alles.

Die folgenden SQL-Kommandos führen zu Exceptions:

insert into MeinUtf8Test ( vrchr10Byte ) values ( '€€€€' );

--> 'Error: java.sql.SQLException: ORA-01401: Eingefügter Wert zu groß für Spalte, SQL State: 23000, Error Code: 1401'

Diese Exception ist nicht überraschend: Der vier Zeichen lange Textstring '€€€€' ist UTF-8-kodiert 12 Bytes lang und kann deshalb nicht in 'varchar2(10 BYTE)' gespeichert werden.

Aber die folgende Exception sorgt meistens für Überraschung:

insert into MeinUtf8Test ( vrchr3000Char ) values ( '0001_0100__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0101_0200__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0201_0300__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0301_0400__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0401_0500__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0501_0600__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0601_0700__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0701_0800__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0801_0900__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 0901_1000__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1001_1100__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1101_1200__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1201_1300__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1301_1400__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1401_1500__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1501_1600__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1601_1700__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1701_1800__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1801_1900__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ 1901_2000__€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€ ' );

--> 'Error: java.sql.SQLException: ORA-01704: Zeichenfolge zu lang, SQL State: 42000, Error Code: 1704'

Der Versuch, nur 2000 Zeichen in eine als 'varchar2(3000 CHAR)' definierte Spalte zu speichern, schlägt fehl (wegen der 4000-Byte-Obergrenze)!

Weiteres hierzu finden Sie unter: http://unix.business.utah.edu/doc/applications/oracle/doc_cd/server.901/a88827/ch850.htm und http://unix.business.utah.edu/doc/applications/oracle/doc_cd/server.901/a90125/sql_elements2.htm#45443.

Ändern des Oracle-Datenbank-Character-Encodings

Oracle-Datenbanken werden bei der Installation auf das gewünschte grundsätzliche Character-Encoding eingestellt. Ein späterer Wechsel ist schwierig und problematisch und bedingt häufig einen Export und Import aller Daten. Falls Sie trotzdem im Nachhinein das Encoding wechseln müssen, können Sie Folgendes versuchen (was nicht immer gelingt) (machen Sie vorher einen Backup und passen Sie die Einträge an):

  1. Erstellen Sie folgende zwei SqlPlus-Skripte:

    OracleDB-umstellen-UTF8.sql

    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    ALTER SYSTEM SET AQ_TM_PROCESSES=0;
    ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
    ALTER DATABASE CHARACTER SET AL32UTF8;
    QUIT;
    

    Check-NLS.sql

    Set Pagesize 100;
    Column Parameter format a30;
    Column Value     format a30;
    Select Parameter, Value from nls_database_parameters;
    Select Parameter, Value from nls_database_parameters where Parameter = 'NLS_CHARACTERSET';
    Quit;
    
  2. Führen Sie folgende Kommandos aus:

    sqlplus sys@XE as sysdba @OracleDB-umstellen-UTF8.sql

    StopDB.bat

    StartDB.bat

    sqlplus system@XE @Check-NLS.sql

Einstellung von NLS_LANG

Wenn Sie als SQL-Client SqlPlus verwenden, muss die NLS_LANG-Variable korrekt gesetzt sein. Wenn keine NLS_LANG-Umgebungsvariable gesetzt ist, gilt unter Windows der in der Registry voreingestellte Wert, meistens:

German_Germany.WE8MSWIN1252

Wenn Sie in UTF8 gespeicherte SQL-Skripte verarbeiten wollen, muss auf der Clientseite UTF8 eingestellt werden, zum Beispiel so:

set NLS_LANG=German_Germany.AL32UTF8

Falls Sie im Windows-Kommandozeilenfenster mit CP850 erstellte SQL-Kommandos mit SqlPlus verarbeiten, kann folgende Einstellung notwendig sein:

set NLS_LANG=German_Germany.WE8PC850

Die korrekte clientseitige NLS_LANG-Einstellung richtet sich also nicht nach dem in der DB eingestelltem Encoding, sondern nach dem clientseitig verwendeten Encoding.



Beispiel für das Anlegen eines Testusers

  1. Für erste Versuche sollte ein eigener Benutzer angelegt werden.

    Folgendes Skript erstellen: Testuser-anlegen.sql

    CREATE ROLE TestRole;
    GRANT  CREATE session, CREATE table, CREATE view, CREATE procedure, CREATE synonym TO TestRole;
    CREATE USER TestUser IDENTIFIED BY TestUser
           DEFAULT TABLESPACE users
           TEMPORARY TABLESPACE temp
           QUOTA UNLIMITED ON users;
    GRANT  TestRole TO TestUser;
    exit;
    
  2. Skript ausführen (geheim durch das system-Passwort ersetzen):

    C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus system/geheim@XE @Testuser-anlegen.sql



Testprogramm für das Speichern von Sonderzeichen in der Datenbank

Das folgende Programm speichert Sonderzeichen in der Datenbank, liest sie wieder aus und vergleicht den gelesenen String mit dem vorher gespeicherten. Sie können es entweder mit den voreingestellten Sonderzeichen äöüߧ€½²√∑ verwenden oder andere Sonderzeichen als Kommandozeilenparameter übergeben.

Fügen Sie den zur Datenbank passenden JDBC-Treiber beim Aufruf zum Classpath hinzu, zum Beispiel so:

javac SqlEncodingTest.java 
java -cp .;ojdbc7.jar SqlEncodingTest "" "" meinUsr meinPwd [für lokale Oracle-XE-DB]
java -cp .;ojdbc7.jar SqlEncodingTest "" "" meinUsr meinPwd "äöüߧ€½²√∑..." [falls andere Sonderzeichen]
java -cp .;<JDBC-Treiber> SqlEncodingTest "<driver>" "<url>" "<usr>" "<pwd>" [falls andere DB]

Falls es Unterschiede gibt, können Sie diese am einfachsten analysieren über die resultierenden Unicode-Hexwerte in:

type x.properties


import java.io.FileOutputStream;
import java.sql.*;
import java.util.Properties;

/**
 * HTML: <html>&#x00E4;&#x00F6;&#x00FC;&#x00DF;&#x00A7;&#x20AC;&#x00BD;&#x00B2;&#x221A;&#x2211;&#x0080;</html><br>
 * Aufruf fuer lokale Oracle-XE-DB:
 *    javac SqlEncodingTest.java
 *    java -cp .;ojdbc7.jar SqlEncodingTest "" "" "<usr>" "<pwd>"
 *    java -cp .;ojdbc7.jar SqlEncodingTest "" "" "<usr>" "<pwd>" "äöüߧ€..."
 *    type x.properties
 * Aufruf fuer andere DB:
 *    java -cp .;<JDBC-Treiberdatei> SqlEncodingTest "<driver>" "<url>" "<usr>" "<pwd>"
 *    type x.properties
 */
public class SqlEncodingTest
{
   public static void main( String[] args ) throws Exception
   {
      String dbDrv = getArg( 0, args, "oracle.jdbc.OracleDriver" );
      String dbUrl = getArg( 1, args, "jdbc:oracle:thin:@localhost:1521:XE" );
      String dbUsr = getArg( 2, args, "" );
      String dbPwd = getArg( 3, args, "" );
      String s1    = getArg( 4, args, "\u00E4\u00F6\u00FC\u00DF\u00A7\u20AC\u00BD\u00B2\u221A\u2211\u0080" );

      String s2 = storeAndReadDb( dbDrv, dbUrl, dbUsr, dbPwd, s1 );
      storeProperties( new String[] { "s1", s1, "s2", s2 }, "x.properties" );

      System.out.println( "Original-Sonderzeichen: " + s1 );
      System.out.println( "Sonderzeichen aus DB:   " + s2 );
      System.out.println( "Ergebnis ist " + (( s1.equals( s2 ) ) ? "korrekt." : "fehlerhaft.") );
   }

   static String getArg( int i, String[] args, String defaultStr )
   {
      return ( args != null && args.length > i && args[i] != null && args[i].length() > 0 ) ? args[i] : defaultStr;
   }

   static String storeAndReadDb( String dbDrv, String dbUrl, String dbUsr, String dbPwd, String s1 ) throws Exception
   {
      String sqlCreate = "Create Table MeinUtf8Test ( Text varchar2(20 char) )";
      String sqlInsert = "Insert into MeinUtf8Test Values ( ? )";
      String sqlRead   = "Select Text from MeinUtf8Test";
      String sqlDrop   = "Drop Table MeinUtf8Test";
      Connection        cn = null;
      PreparedStatement ps = null;
      Statement         st = null;
      ResultSet         rs = null;
      try {
        String s2 = null;
        Class.forName( dbDrv );
        cn = DriverManager.getConnection( dbUrl, dbUsr, dbPwd );
        st = cn.createStatement();
        st.executeUpdate( sqlCreate );
        ps = cn.prepareStatement( sqlInsert );
        ps.setString( 1, s1 );
        ps.executeUpdate();
        rs = st.executeQuery( sqlRead );
        if( rs.next() ) s2 = rs.getString( 1 );
        st.executeUpdate( sqlDrop );
        return s2;
      } finally {
        try { if( null != rs ) rs.close(); } catch( Exception ex ) {/*ok*/}
        try { if( null != st ) st.close(); } catch( Exception ex ) {/*ok*/}
        try { if( null != ps ) ps.close(); } catch( Exception ex ) {/*ok*/}
        try { if( null != cn ) cn.close(); } catch( Exception ex ) {/*ok*/}
      }
   }

   static void storeProperties( String[] keysAndValues, String filename ) throws Exception
   {
      Properties prop = new Properties();
      for( int i = 0; i < keysAndValues.length; i+=2 ) {
         String v = keysAndValues[i+1];
         prop.put( keysAndValues[i], ( v != null ) ? v : "" );
      }
      prop.store( new FileOutputStream( filename ), null );
   }
}


Beispiel für ein einfaches Testprogramm zur Performance-Messung

  1. Obigen "Testuser" anlegen.

  2. Das Verzeichnis DbVieleCommitsTest anlegen und dort hinein den Oracle-JDBC-Treiber ojdbc7.jar kopieren.

  3. Im Verzeichnis DbVieleCommitsTest erzeugen: DbVieleCommits.java

    import java.sql.*;
    import java.text.SimpleDateFormat;
    
    public class DbVieleCommits
    {
       public static void main( String[] args ) throws ClassNotFoundException, SQLException
       {
          int    anzahl = 30000000;
          String dbTbl  = "MassenTest";
          String dbDrv  = "oracle.jdbc.OracleDriver";
          String dbUrl  = "jdbc:oracle:thin:@localhost:1521:XE";
          String dbUsr  = "TestUser";
          String dbPwd  = "TestUser";
          dropAndCreateDbTable( dbTbl, dbDrv, dbUrl, dbUsr, dbPwd );
          fillDbTable( anzahl, dbTbl, dbDrv, dbUrl, dbUsr, dbPwd );
       }
    
       static void dropAndCreateDbTable( String dbTbl, String dbDrv, String dbUrl, String dbUsr, String dbPwd )
             throws ClassNotFoundException, SQLException
       {
          Connection cn = null;
          Statement  st = null;
          try {
             Class.forName( dbDrv );
             cn = DriverManager.getConnection( dbUrl, dbUsr, dbPwd );
             st = cn.createStatement();
             try {
                st.execute( "DROP TABLE " + dbTbl );
             } catch( SQLSyntaxErrorException ex ) {
                if( !ex.getMessage().contains( "ORA-00942" ) && !ex.getMessage().contains( "Unknown table 'massentest'" ) ) {
                   throw ex;
                }
             }
             st.close();
             st = cn.createStatement();
             st.execute( "CREATE TABLE " + dbTbl + " ( id INT PRIMARY KEY, lastUpdate TIMESTAMP, meinText VARCHAR(100) )" );
          } finally {
             try { if( st != null ) { st.close(); } } catch( Exception ex ) {/* nothing to do */}
             try { if( cn != null ) { cn.close(); } } catch( Exception ex ) {/* nothing to do */}
          }
       }
    
       static void fillDbTable( int anzahl, String dbTbl, String dbDrv, String dbUrl, String dbUsr, String dbPwd )
             throws ClassNotFoundException, SQLException
       {
          SimpleDateFormat  df = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
          Connection        cn = null;
          PreparedStatement st = null;
          try {
             Class.forName( dbDrv );
             cn = DriverManager.getConnection( dbUrl, dbUsr, dbPwd );
             st = cn.prepareStatement( "INSERT INTO " + dbTbl + " VALUES ( ?, ?, ? )" );
             long anzahlZuletzt = 0;
             long time = System.currentTimeMillis();
             long nanoTimeZuletzt = System.nanoTime();
             for( int i = 0; i < anzahl; i++ ) {
                if( System.currentTimeMillis() >= time ) {
                   System.out.println( df.format( new java.util.Date() ) + ": " + i + " Commits, " + ((i - anzahlZuletzt + 5) / 10) + " Commits/Sekunde." );
                   time += 10000;
                   anzahlZuletzt = i;
                }
                long nanoTime = System.nanoTime();
                st.setLong(      1, i );
                st.setTimestamp( 2, new Timestamp( (new java.util.Date()).getTime() ) );
                st.setString(    3, i + ", " + nanoTime + ", " + ((nanoTime - nanoTimeZuletzt + 500) / 1000) + " Microsekunden" );
                st.execute();
                nanoTimeZuletzt = nanoTime;
             }
          } finally {
             try { if( st != null ) { st.close(); } } catch( Exception ex ) {/* nothing to do */}
             try { if( cn != null ) { cn.close(); } } catch( Exception ex ) {/* nothing to do */}
          }
       }
    }
    
  4. Test starten:

    cd DbVieleCommitsTest

    javac DbVieleCommits.java

    java -cp .;ojdbc7.jar DbVieleCommits

    Übliche Werte sind ca. 4000 Transaktionen pro Sekunde.



AWR-Reports erstellen

  1. Bei Problemen (z.B. Performanceproblemen langlaufender Applikationen) ist es häufig hilfreich, zu einem vorgebbaren Zeitraum die SQL-Kommandos zu analysieren. Falls Ihre Oracle-Lizenz das zulässt, sind hierfür die Reports des AWR (Automatic Workload Repository) hilfreich (siehe auch Automatic Performance Statistics und C:\oraclexe\app\oracle\product\11.2.0\server\RDBMS\ADMIN\awrrpt.sql).

  2. Falls zum Beispiel obiger Test genügend lange läuft oder wenn manuell Snapshots erzeugt werden, können AWR-Reports erzeugt werden. Dazu während des Testlaufs mit dem "EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot"-Kommando mehrere Snapshots erstellen, zum Beispiel alle 10 Minuten, und am Schluss mit dem awrrpt-Kommando einen AWR-Report erstellen (bitte sys/geheim@XE durch <sysdba-Benutzername>/<sysdba-Kennwort>@<sid> ersetzen):

    C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus sys/geheim@XE as sysdba

    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

    @?/rdbms/admin/awrrpt

    html, 1, Start-Snap-ID, End-Snap-ID

  3. Beispielhafter Auszug aus AWR-Report

    Load Profile:

    Per Second
    Executes: 4,145.91
    Transactions: 3,994.74

    Top 5 Timed Events:

    EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
    CPU time  419  65.6 
    log file sync 3,145,319 340 0 53.1Commit
    log file parallel write 3,154,928 264 0 41.2System I/O
    log file switch completion 66 57 869 9.0Configuration
    log file switch (checkpoint incomplete) 108 40 373 6.3Configuration


Export / Import per Data Pump

Doku zur Oracle Data Pump

Oracle Data Pump in 11g Release 2 (11.2),
Oracle Data Pump in 12c Release 1 (12.1),
Oracle Data Pump in 12c Release 2 (12.2).

Zum Unterschied zwischen dem "Original Export/Import" mit exp/imp versus "Data Pump Export/Import" mit expdp/impdp siehe:
Original Export and Import und How Data Pump Export Parameters Map to Those of the Original Export Utility.

Export

Die folgende Beschreibung geht von Linux als Betriebssystem und von folgenden Annahmen zur Installation der Export-Oracle-DB aus (die für den konkreten Anwendungsfall korrigiert werden müssen):
DB-Rechner: dbhost, DB-Installationsverzeichnis: /opt/oracle/db/, DB-SID: dbsid, OS-Benutzername für DB: dblinuxusr, DB-system-Passwort: dbpwd, Verzeichnis für Dumps: /opt/oracle/db/dpdump/

Mit folgenden Schritten erfolgt der Export:

  1. Verbindungsaufbau per ssh und Wechsel zum User "dblinuxusr":

    ssh root@dbhost

    su dblinuxusr

  2. Überprüfen der in der DB vorhandenen "Directory Objects" (registrierte Verzeichnisse):

    cd /opt/oracle/db/bin

    ./sqlplus system/dbpwd@dbsid

    Select * from ALL_DIRECTORIES;

    Select * from ALL_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

    exit

  3. Falls der gewünschte Eintrag fehlt, kann er beispielsweise so angelegt werden:

    Create or replace Directory DATA_PUMP_DIR as '/opt/oracle/db/dpdump/';

    Grant Read, Write on Directory DATA_PUMP_DIR to system;

  4. Angenommen, dass "DATA_PUMP_DIR" ist konfiguriert zu "/opt/oracle/db/dpdump/". Überprüfen, ob dieses Verzeichnis existert und korrekte Schreibrechte hat. Falls nicht, muss es angelegt werden. Der Linux-Account der Datenbank (dblinuxusr) benötigt Schreibrechte in diesem Verzeichnis:

    ls -al /opt/oracle/db/dpdump/

  5. Vergabe der Berechtigung zur Umschaltung in den 'restricted mode':

    cd /opt/oracle/db/bin

    ./sqlplus system/dbpwd@dbsid

    GRANT RESTRICTED SESSION TO system;

    exit

  6. Falls ein Monitoring-System installiert ist, sollte es deaktiviert werden. Im Falle von Nagios und falls nicht nur einzelne Checks deaktiviert werden sollen, sondern Nagios komplett gestoppt werden soll:

    ssh root@nagios-host

    service nagios stop

  7. Die Datenbank für neue Verbindungen sperren durch Aktivieren des 'restricted mode' (siehe auch: Datenbank nur für den DBA - RESTRICTED):

    cd /opt/oracle/db/bin

    ./sqlplus system/dbpwd@dbsid

    alter system enable restricted session;

  8. Mit sqlplus eine Skriptvorlage erstellen, für ein Skript mit dem bestehende Verbindungen abgebrochen werden können:

    spool disconnect.sql

    select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username in ('MEIN_SCHEMA');

    spool off

    exit

  9. In dem dabei entstandenen Skript 'disconnect.sql' alle störenden Zeilen löschen (z.B. Überschriften, Header- und Footer-Zeilen), sowie am Ende ein "Exit"-Kommando hinzufügen.

  10. Da die DB im "restricted session"-Modus läuft, ist eine normale Remote-Anmeldung über den DB-Listener nicht mehr möglich. Um sich trotzdem anmelden zu können, muss "UR = A" angegeben werden ("USERS with RESTRICTED session privilege are ALLOWED"). Dabei muss beachtet werden, dass "UR = A" nur funktioniert, wenn bei der Connection nicht die SID per "SID = ..." übergeben wird, sondern der Servicename per "SERVICE_NAME = ..." (falls es keinen expliziten Servicenamen gibt kann bei "SERVICE_NAME = ..." die SID eingetragen werden).
    Hierzu kann entweder die tnsnames.ora erweitert werden, oder alternativ kann auch eine entsprechende Connection per Kommandozeile angegeben werden.
    Ausführung des Abbruch-Skriptes "disconnect.sql" und Löschen des Skripts:

    cd /opt/oracle/db/bin

    export _ORA_DB_CONN_1="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbsid)(UR=A)))"

    export _ORA_DB_CONN_2=\"\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=tcp\)\(HOST=dbhost\)\(PORT=1521\)\)\(CONNECT_DATA=\(SERVICE_NAME=dbsid\)\(UR=A\)\)\)\"

    env | grep -i ora

    ./sqlplus system/dbpwd@"$_ORA_DB_CONN_1" @disconnect.sql

    rm ./disconnect.sql

  11. Hilfstexte zum expdp-Kommando ansehen (siehe auch: Data Pump Export):

    cd /opt/oracle/db/bin

    ./expdp HELP = y

  12. Schätzen der resultierenden Dateigröße (ohne Metadaten) (MeinSchemaAbc durch das gewünschte Schema ersetzen):

    ./expdp system/dbpwd@$_ORA_DB_CONN_2 ESTIMATE_ONLY=YES SCHEMAS=MeinSchemaAbc DIRECTORY=DATA_PUMP_DIR LOGFILE=MeinSchemaAbc.log

    Ergebnis ansehen:

    cat /opt/oracle/db/dpdump/MeinSchemaAbc.log

  13. Start eines einzelnen Exports (MeinSchemaAbc durch das gewünschte Schema ersetzen) (statt einzelner Schemata könnte mit FULL=y auch alles exportiert werden):

    cd /opt/oracle/db/bin

    ./expdp system/dbpwd@$_ORA_DB_CONN_2 SCHEMAS=MeinSchemaAbc DIRECTORY=DATA_PUMP_DIR DUMPFILE=MeinSchemaAbc.dmp LOGFILE=MeinSchemaAbc.log

  14. Start vieler Exporte per Skript (falls das Skript run-ora-export-schemata.sh erstellt wurde wie weiter unten beschrieben wird) (im Skript eventuell die Parameter anpassen) (das DB-Passwort per Kommandozeilenparameter übergeben):

    cd /opt/oracle/db/bin

    ./run-ora-export-schemata.sh dbpwd

  15. Falls der Export über Nacht lief und jetzt ein neues Terminalfenster geöffnet wird: Eventuell muss die Umgebungsvariable _ORA_DB_CONN_1 neu gesetzt werden, wie oben gezeigt. Überprüfung per:

    env | grep -i ora

  16. Aufheben des 'restricted mode':

    cd /opt/oracle/db/bin

    ./sqlplus system/dbpwd@"$_ORA_DB_CONN_1"

    alter system disable restricted session;

    exit

  17. Falls das Aufheben des 'restricted mode' nicht funktionierte, als SYSDBA versuchen:

    cd /opt/oracle/db/bin

    ./sqlplus sys/dbpwd@"$_ORA_DB_CONN_1" AS SYSDBA

    alter system disable restricted session;

    exit

  18. Löschen der Shell-History mit den darin enthaltenen Passwörtern:

    history -c

  19. Reaktivierung der Nagios-Checks:

    ssh root@nagios-host

    service nagios start

  20. Im "DATA_PUMP_DIR" befinden sich die Ergebnisse (Log- und Dump-Datei):

    ls -al /opt/oracle/db/dpdump/

Import

Im Folgenden werden Dumps per Data Pump importiert in die Import-Datenbank.

  1. Überprüfen der in der Import-DB vorhandenen "Directory Objects" (registrierte Verzeichnisse):

    Select * from ALL_DIRECTORIES;

    Select * from ALL_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

    Falls der gewünschte Eintrag fehlt, muss er angelegt werden, siehe oben.

    Das als DATA_PUMP_DIR konfigurierte Verzeichnis muss mit korrekten Schreibrechten existieren:

    ls -al ...

  2. In dieses Verzeichnis muss der oben erstellte Export-Dump kopiert werden.

  3. Start des Imports ohne Schemaumbenennung (MeinSchemaAbc durch das gewünschte Schema ersetzen) (mit FULL=YES kann die komplette Datenbank importiert werden):

    ./impdp system@dbhost2 SCHEMAS=MeinSchemaAbc DIRECTORY=DATA_PUMP_DIR DUMPFILE=MeinSchemaAbc.dmp LOGFILE=MeinSchemaAbc.log

  4. Alternativ Start des Imports mit Schemaumbenennung (die zwei Schemanamen ersetzen) (siehe auch: REMAP_SCHEMA):

    ./impdp system@dbhost2 REMAP_SCHEMA=MeinSchemaAbc:MeinNeuesSchemaAbc DIRECTORY=DATA_PUMP_DIR DUMPFILE=MeinSchemaAbc.dmp LOGFILE=MeinNeuesSchemaAbc.log

  5. Alternativ Imports mit Schemaumbenennung per Skript: siehe unten.

Erstellen eines Skripts zum Export vieler Schemata

Auf dem dbhost-Rechner im /opt/oracle/db/bin-Verzeichnis das Shell-Skript run-ora-export-schemata.sh erstellen (dabei die Parameter anpassen, und, falls das DB-Passwort nicht per Kommandozeilenparameter übergeben werden soll, bei '_DB_PWD' eintragen) (zur Ausführung des Skripts siehe oben):

#!/bin/bash

echo
echo Skript zum Export vieler DB-Schemata per Oracle Data Pump im 'restricted DB-Mode'
echo Doku siehe: http://www.torsten-horn.de/techdocs/oraclexe-db.htm
echo

_OS_USR=dblinuxusr
_DB_USR=system
_DB_PWD=$1
_DB_PWD=${_DB_PWD:=dbpwd}
_DB_SID=dbsid
_DB_HOST=dbhost
_ORA_DB_CONN_2=\"\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=tcp\)\(HOST=$_DB_HOST\)\(PORT=1521\)\)\(CONNECT_DATA=\(SERVICE_NAME=$_DB_SID\)\(UR=A\)\)\)\"
_DP_DIR=/opt/oracle/db/dpdump/
_SCHEMATA="MEIN_SCHEMA_1 MEIN_SCHEMA_2 MEIN_SCHEMA_3 MEIN_SCHEMA_N"

echo "date:                 "`date`
echo "whoami:               "`whoami`
echo "pwd:                  "`pwd`
echo "Ergebnis-Verzeichnis: "$_DP_DIR
if [[ $_OS_USR != `whoami` ]] ; then echo -e "\nFehler: Benutzer muss $_OS_USR sein.\n" ; exit 1 ; fi
if [ ! -f ./expdp           ] ; then echo -e "\nFehler: Startverzeichnis ist falsch, ./expdp fehlt.\n" ; exit 1 ; fi
if [ ! -d "$_DP_DIR"        ] ; then echo -e "\nFehler: Das Ergebnis-Verzeichnis $_DP_DIR fehlt.\n" ; exit 1 ; fi
if [ ! -w "$_DP_DIR"        ] ; then echo -e "\nFehler: Zum Ergebnis-Verzeichnis $_DP_DIR fehlen Schreibrechte.\n" ; exit 1 ; fi
if [ "$(ls -A $_DP_DIR)"    ] ; then echo -e "\nWarnung: Das Ergebnis-Verzeichnis $_DP_DIR ist nicht leer.\n" ; fi

echo
echo "Inhalt vom Ergebnis-Verzeichnis $_DP_DIR:"
ls -al $_DP_DIR
echo

for schema in $_SCHEMATA
do
   echo -------- Exportiere das Schema $schema ...
   if ! ./expdp $_DB_USR/$_DB_PWD@$_ORA_DB_CONN_2 SCHEMAS=$schema DIRECTORY=DATA_PUMP_DIR DUMPFILE=$schema.dmp LOGFILE=$schema.log
   then echo -e "\nFehler beim Oracle-Data-Pump-Kommando expdp.\n" ; exit $? ; fi
done

echo
echo "Inhalt vom Ergebnis-Verzeichnis $_DP_DIR:"
ls -al $_DP_DIR
echo

Erstellen eines Skripts zum Import vieler Schemata, teilweise mit Schemanamenumbenennung/Remapping

Vor der Ausführung die Parameter anpassen, und bei der Ausführung das DB-Passwort per Kommandozeilenparameter übergeben:

#!/bin/bash

echo
echo Skript zum Import vieler DB-Schemata per Oracle Data Pump, teilweise mit Schemanamenumbenennung/Remapping
echo Doku siehe: http://www.torsten-horn.de/techdocs/oraclexe-db.htm
echo

_OS_USR=dblinuxusr
_DB_USR=system
_DB_PWD=$1
_DB_SID=dbsid
_DB_HOST=dbhost
_ORA_DB_CONN_2=\"\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=tcp\)\(HOST=$_DB_HOST\)\(PORT=1521\)\)\(CONNECT_DATA=\(SERVICE_NAME=$_DB_SID\)\)\)\"
_ORA_DB_IMPDP=./impdp
_DP_DIR=/opt/oracle/db/dpdump/

# Schemata ohne Schemanamenumbenennung/Remapping:
_SCHEMATA_OK="MEIN_SCHEMA_1 MEIN_SCHEMA_2 MEIN_SCHEMA_3 MEIN_SCHEMA_N"
# Schemata mit Schemanamenumbenennung/Remapping:
_SCHEMATA_ALT=( MeinSchemaAbc      MeinSchemaXyz )
_SCHEMATA_NEU=( MeinNeuesSchemaAbc MeinNeuesSchemaXyz )

echo "date:             "`date`
echo "whoami:           "`whoami`
echo "Startverzeichnis: "`pwd`
echo "Quellverzeichnis: "$_DP_DIR
echo "impdp-Kommando:   "$_ORA_DB_IMPDP
if [[ $_OS_USR != `whoami` ]] ; then echo -e "\nFehler: Benutzer muss $_OS_USR sein.\n" ; exit 1 ; fi
if [ ! -n "$_DB_PWD"        ] ; then echo -e "\nFehler: Das DB-Passwort muss per Kommandozeilenparameter übergeben werden.\n" ; exit 1 ; fi
if [ ! -f "$_ORA_DB_IMPDP"  ] ; then echo -e "\nFehler: Der Pfad zum impdp-Kommando ist falsch.\n" ; exit 1 ; fi
if [ ! -d "$_DP_DIR"        ] ; then echo -e "\nFehler: Das Quellverzeichnis $_DP_DIR fehlt.\n" ; exit 1 ; fi
if [ ! -w "$_DP_DIR"        ] ; then echo -e "\nFehler: Zum Quellverzeichnis $_DP_DIR fehlen Schreibrechte.\n" ; exit 1 ; fi
if [ ! "$(ls -A $_DP_DIR)"  ] ; then echo -e "\nFehler: Das Quellverzeichnis $_DP_DIR ist leer.\n" ; exit 1 ; fi

n1=${#_SCHEMATA_ALT[*]}
n2=${#_SCHEMATA_NEU[*]}
if [ $n1 != $n2 ] ; then echo -e "\nFehler: Die beiden Schemanamen-Arrays müssen gleich groß sein.\n" ; exit 1 ; fi

echo
echo "Inhalt vom Quellverzeichnis $_DP_DIR:"
ls -al $_DP_DIR
echo

# Importiere Schemata mit Schemanamenumbenennung/Remapping:
i=0 ; until [ $i == $n1 ] ; do
   echo -------- Importiere das Schema ${_SCHEMATA_ALT[i]} zum Schema ${_SCHEMATA_NEU[i]} ...
   if ! $_ORA_DB_IMPDP $_DB_USR/$_DB_PWD@$_ORA_DB_CONN_2 REMAP_SCHEMA=${_SCHEMATA_ALT[i]}:${_SCHEMATA_NEU[i]} DIRECTORY=DATA_PUMP_DIR DUMPFILE=${_SCHEMATA_ALT[i]}.dmp LOGFILE=${_SCHEMATA_NEU[i]}.log
   then echo -e "\nFehler beim Oracle-Data-Pump-Kommando impdp.\n" ; exit $? ; fi
   i=`expr $i + 1`;
done

echo

# Importiere Schemata ohne Schemanamenumbenennung/Remapping:
for schema in $_SCHEMATA_OK
do
   echo -------- Importiere das Schema $schema ...
   if ! $_ORA_DB_IMPDP $_DB_USR/$_DB_PWD@$_ORA_DB_CONN_2 SCHEMAS=$schema DIRECTORY=DATA_PUMP_DIR DUMPFILE=$schema.dmp LOGFILE=$schema.log
   then echo -e "\nFehler beim Oracle-Data-Pump-Kommando impdp.\n" ; exit $? ; fi
done

echo
echo "Inhalt vom Quellverzeichnis $_DP_DIR (Dumps und Logs):"
ls -al $_DP_DIR
echo




Weitere Themen: andere TechDocs | SQL-Beispiele | Vererbung | SQL mit Java | SQL
© 2008-2017 Torsten Horn, Aachen