SQL/PL - Übungen und Lösungen
Contents Index

Fundamentals I - Übungen und Lösungen

PFILE / SPFILE

In dieser Übung werden Sie die Abarbeitungsreihenfolge und -hierarchie der Initialisierungsparameterdateien kennen lernen. Bitte notieren Sie sich Ihre SID:

select name, value from v$parameter where name ='instance_name';

SID = testdb29

Legen Sie in Ihrem Home-Verzeichnis folgende Verzeichnisse an:

mkdir ~/spfile
mkdir ~/pfile

Im folgenden werden Sie ein PFILE aus dem standardmäßigen SPFILE erstellen.

Starten Sie den OEM. Melden Sie sich Standalone an.
Erweitern Sie 'Datenbanken'. Geben Sie in der Anmeldung an die Datenbank als Benutzer sys an. Das Kennwort ist auch sys. Bei 'Anmelden als' geben Sie das Privileg SYSDBA an.
Erweitern Sie 'Instanz' und wählen Sie 'Konfiguration' aus.
Wählen Sie im Menü Objekt - PFILE erstellen.
Unter SPFILE geben Sie den Pfad zum Standard-SPFILE an. Der Dateiname soll SPFILEtestdb29.ora sein. Unter PFILE geben Sie den gleichen Pfad wie beim SPFILE an. Der Dateiname soll inittestdb29.ora sein.
Fahren Sie die Instanz herunter.
Verschieben Sie die Datei SPFILEtestdb29.ora in das Verzeichnis /spfile.
Achtung: Löschen Sie dieses SPFILE nicht!
Versuchen Sie nun Ihre Instanz über das SQL-Worksheet hochzufahren.

Was passiert und warum? Überprüfen Sie auch die Konfigurationsmöglichkeiten der Initialisierungsparameterdatei im OEM.
Die Instanz startet. Es werden die Werte aus dem PFILE gelesen. Es sind daher keine dynamischen Änderungen möglich.

Zusatz: Versuchen Sie ein PFILE aus dem SPFILE mittels SQL-Anweisung zu erstellen. Versuchen Sie ein SPFILE aus dem erstellten PFILE zu erstellen. Testen Sie beide Möglichkeiten (OEM und SQL-Anweisung).

Erstellen Sie ein PFILE manuell und binden das SPFILE darin ein.

Fahren Sie die Instanz herunter.
Erstellen Sie im Verzeichnis /pfile eine Textdatei, die Sie init_SID.ora (z.B. init_testdb29.ora) nennen.
Schreiben Sie in diese Datei nur folgende Zeile:
spfile= /spfile/spfile_SID.ora (z.B. spfile_testdb29.ora)
Fahren Sie die Instanz mit diesem PFILE hoch:
startup pfile= /pfile/init_testdb29.ora

Was passiert und warum?
Die Instanz fährt mit dem eingebundenen PFILE hoch.

Hoch- / Herunterfahren

Legen Sie einen neuen Benutzer test mit dem Kennwort test an.
Geben Sie ihm die Rechte connect und select any table.
Melden Sie sich über das SQL-Worksheet als Benutzer test an.
Melden Sie sich weiterhin über das SQL-Worksheet als Benutzer sys an.
Fahren Sie sys als Ihre Instanz normal herunter (shutdown normal;). Was passiert und warum?
Es ist kein shutdown normal möglich, das der User test noch angemeldet ist.
Wechseln Sie in das Worksheet des Benutzers test.
Melden Sie den Benutzer test ab (disconnect).
Wechseln Sie in das Worksheet des Benutzers sys.
Was passiert und warum?
Die Instanz fährt herunter.

Spielen Sie das Szenario mit den Optionen shutdown immediate und shutdown abort durch. Was passiert und warum?
Bei immediate und abort fährt die Instanz trotz connection vom User test herunter.

Starten Sie eine heruntergefahrene Instanz mittels der verschiedenen Start-Optionen. Wechseln Sie von der niedrigsten Stufe in die jeweils höhere (NOMOUNT, MOUNT, OPEN). Bei welchen Optionen kann sich der Benutzer test wieder einloggen?
Das Einloggen klappt erst bei OPEN.

Nun werden Sie die Datenbank im eingeschränkten Modus hochfahren.

Starten Sie Ihre Instanz mit startup restrict;
Versuchen Sie sich als User test anzumelden. Was passiert und warum?
Der User test kann sich nicht anmelden (Restricted Session).
Ändern Sie Ihre Instanz vom eingeschränkten in den nicht eingeschränkten Modus.
alter system disable restricted session;
Versuchen Sie sich erneut als User test anzumelden. Was passiert und warum?
Der User test kann sich anmelden.
Mit welchem Befehl ändern Sie eine aktuelle Sitzung von normalen Modus in den eingeschränkten Modus?
alter system enable restricted session;

Control Files spiegeln

In dieser Übung Sie die Kontroldateien Ihrer Datenbank spiegeln, um eine höhere Ausfallsicherheit zu gewährleisten. Die Kontrolldateien sollten dabei auf unterschiedliche Datenträger gespeichert werden.

Ermitteln Sie aus der Data Dictionary View V$CONTROLFILE den Speicherort der Kontroldateien. Notieren Sie sich die angegebenen Pfade.
Erstellen Sie drei neue Verzeichnisse. Bennennen Sie diese in /hdd1, /hdd2 und /hdd3. Diese Bezeichnungen sollen unterschiedliche Datenträger darstellen. Wenn es Ihnen möglich ist, sollten Sie diese Verzeichnisse auch als Mount-Points für unterschiedliche Datenträger verwenden.
Melden Sie sich mit dem SQL*Plus-Worksheet als sys an.
Ändern Sie Ihr SPFILE so, dass die neuen Pfadangaben für die Kontrolldateien darin angegeben werden. Bedenken Sie, dass die Pfadangaben für die Kontrolldateien keine dynamischen Werte des SPFILE sind.
```
alter systemset control_files =
 (
   '/hdd1/control01.ctl',
   '/hdd2/control02.ctl',
   '/hdd3/control03.ctl'
 ) 
 SCOPE=SPFILE
;
```
Fahren Sie Ihre Instanz herunter und verschieben (nicht kopieren) Sie die Kontrolldateien mittels Betriebssystem-Befehlen an die entsprechenden Verzeichnisse.
Starten Sie die Instanz wieder (OPEN). Ermitteln Sie aus der Data Dictionary View V$CONTROLFILE wieder den Speicherort der Kontroldateien. Wo befinden sich die drei Kontroldateien jetzt?
/hdd1/control01.ctl,/hdd2/control02.ctl,/hdd3/control03.ctl

Zusatz:
Fahren Sie die Instanz herunter. Benennen Sie die Kontrolldateien um und simulieren so den Ausfall sämtlicher Kontrolldateien. Läßt sich die Instanz starten?
Nein.

Simulieren Sie den Ausfall nur einer Kontrolldatei. Was passiert beim Start?
Es wird ein Lesefehler der Kontrolldatein angezeigt (alert.log). Es wird aber versucht die DB zu mounten. Dies gelingt aber nicht.

Redo Log Dateien

In dieser Übung werden Sie neue Redo-Log-Member zu bereits bestehenden Redo-Log-Gruppen hinzufügen (spiegeln). Das Spiegeln der Member dient zur Erhöhung der Ausfallsicherheit und der Verfügbarkeit. Weiterhin werden Sie Redo-Log-Gruppen hinzufügen. Dies kann die Performance steigern.

Ermitteln Sie die Datein Ihrer Log-Files aus den Data Dictionary Views V$LOG und V$LOGFILE. Wieviele Gruppen und Member besitzt Ihre Datenbank?
Es sind drei Gruppen mit jeweils einem Member.
Erstellen Sie zwei neue Verzeichnisse. Benennen Sie die Ordner /redoSP und /redo4.

Teil 1
Fügen Sie den bestehenden Redo-Log-Gruppen neue Member hinzu und spiegeln Sie diese. Die Member sollten sich auf unterschiedlichen Datenträgern befinden.

Melden Sie sich mittels SQL*Plus Worksheet als sys an.
Fügen Sie zu jeder Redo-Log-Gruppe einen weiteren Member hinzu. Legen Sie die Member so an, dass die neuen Member im Ordner /redoSP gespeichert werden. Benennen Sie den neuen Redo-Log-Member der ersten Gruppe mit redo01a.log, den der zweiten Gruppe mit redo02a.log und den der dritten Gruppe mit redo03a.log.
```
alter database 
  add logfile member 
    '/redoSP/redo01a.log' to group 1, 
    '/redoSP/redo02a.log' to group 2, 
    '/redoSP/redo03a.log' to group 3
;
select group#,member,status from v$logfile;
```
Prüfen Sie Ihr Ergebnis mit Hilfe der View V$LOGFILE.
Was stellen Sie fest?
Alle sind als INVALID gekennzeichnet.

Teil 2
Nun werden Sie eine neue Redo-Log-Gruppe hinzufügen.

Melden Sie sich mittels SQL*Plus Worksheet an Ihrer Datenbank als sys an.
Fügen Sie die vierte Gruppe hinzu. Diese soll ebenfalls zwei Member mit einer Größe von 50MB besitzen. Speichern Sie den ersten Member im Ordner /redo4 und den zweiten in /redoSP ab. Bennen Sie die Redo-Log-Dateien mit redo04a.log (erster Member) bzw. redo04b.log (zweiter Member).
```
alter database 
  add logfile group 4 
    ('/redo4/redo04a.LOG',
     '/redoSP/redo04b.LOG') 
    size 50M
;
```
Prüfen Sie Ihr Ergebnis mit Hilfe der Views V$LOGFILE und V$LOG.
```
select group#,member,status from v$logfile;
select group#,status from v$log;
```
Was stellen Sie fest?
Alle sind da.

Teil 3
Nun werden Sie die Größe der Redo-Log-Gruppen 1 bis 3 so ändern, dass Ihre Größe nur noch 50MB beträgt.

Melden Sie sich mittels SQL*Plus Worksheet als sys an.
Prüfen Sie den Status Ihrer Redo-Log-Gruppe, die Sie löschen möchten. Beachten Sie, dass aktive Redo-Log-Dateien nicht gelöscht werden können. Switchen Sie, wenn nötig zur nächsten Redo-Log-Gruppe.
```
select group#,status from v$log;
```
Löschen Sie Ihre erste Redo-Log-Gruppe.
```
alter database drop logfile group 1;
```
Legen Sie eine neue Redo-Log-Gruppe 1 mit der Größe von 50MB an.
```
alter database 
  add logfile group 1 
    ('/oracle/oradata/testdb29/redo01a.LOG',
     '/redoSP/redo01b.LOG')
    size 50M;
```
Was passiert und warum?
Das Alert-Log zeigt die Änderung an, die Views V$LOGFILE und V$LOG dagegen nicht.

Fügen Sie Ihrem Erstellungsbefehl das abschließende Wort REUSE hinzu.

alter database 
  add logfile group 1 
  ('/oracle/oradata/testdb29/redo01a.LOG',
   '/redoSP/redo01b.LOG') 
  size 50M reuse;

Nach kurzer Zeit erscheint die Meldung: Datenbank geändert. Prüfen Sie, ob die Redo-Log-Gruppe wirklich in der richtigen Größe an den richtigen Orten erstellt wurde.
```
select group#,status from v$log;
select group#,member,status from v$logfile;
```
Verfahren Sie mit den anderen Gruppen ebenso.

Zusatz
Prüfen Sie, ob es möglich ist Redo-Log-Gruppen mit einer unterschiedlichen Anzahl von Membern anzulegen. Welcher Sinn könnte dahinter stecken?

Index

Im Folgenden werden Sie die Funktionsweise von Indizes und deren Verwendbarkeit kennen lernen. Arbeiten Sie mit dem SQL*Plus Woksheet.

Melden Sie sich an Ihrer Datenbank als sys an.

Erstellten Sie eine Tabelle index_test im lokal verwalteten Tablespace ts_index_test mit einer Datendatei von 500 MB. Die Tabelle soll eine Spalte name besitzen mit dem Datentyp char(2000).

create tablespace ts_index_test 
  datafile '/var/tablespaces/ts_index_test.dbf' 
  size 500M extent management local;
create table index_test (name char(2000)) tablespace ts_index_test;

Geben Sie folgendes Kommando ein:

set timing on;

Was bewirkt dieses Kommando?

set timing on;
select * from index_test;
...
Abgelaufen: 00:00:00.02

Geben sie folgende Namen ein: Klaus, Silke, Manu, Stefan, Alex.

insert into index_test values ('Klaus');
insert into index_test values ('Silke');
insert into index_test values ('Manu');
insert into index_test values ('Stefan');
insert into index_test values ('Alex');

Fügen Sie weitere Datensätze der Tabelle hinzu, indem Sie folgenden Befehl 12 mal verwenden:

-- 12x
insert into index_test select * from index_test;
--
select count(*) from index_test;

Danach haben Sie 20480 Datensätze in Ihrer Tabelle. Fügen Sie nun einen Datensatz mit Anna als Namen ein.

insert into index_test values ('Anna');
commit;

Sie werden nun eine Abfrage starten, bei der ein Full Table Scan durchgeführt werden muss. Dabei wird die gesamte Tabelle nach den entsprechenden Datensatz durchsucht. Wie lange benötigt die Abfrage zur Ausführung?

select * from index_test where name = 'Anna';
...
Abgelaufen: 00:09:35.00

Erstellen Sie nun einen normalen Index auf die Spalte name Ihrer Tabelle test.

create index id1 on index_test(name);

Wiederholen Sie die in Punkt 7 getätigte Abfrage und notieren Sie sich die zur Ausführung benötigte Zeit. Vergleichen Sie beide Zeiten miteinander und interpretieren Sie das Ergebnis.

select * from index_test where name = 'Anna';
...
Abgelaufen: 00:00:15.08

Löschen Sie den Index, die Tabelle und den Tablespace.

drop index id1;
drop table index_test;
drop tablespace ts_index_test;

Constraints

Im Folgenden werden Sie die Funktionsweise des Deaktivierens und der verschiedenen Aktivierungszustände von Constraints kennen lernen. Arbeiten Sie folgende Übung mit dem SQL*Plus Woksheet durch.

Melden Sie sich als sys an.

Erstellten Sie eine Tabelle constr_test im lokal verwalteten Tablespace ts_constr_test mit einer Datendatei von 50 MB. Die Tabelle soll eine Spalte name besitzen mit dem Datentyp varchar(20).

create tablespace ts_constr_test 
  datafile '/var/tablespaces/ts_constr_test.dbf' 
  size 50M extent 
  management local
;
create table constr_test (name varchar(20)) 
  tablespace ts_constr_test 
;

Geben sie folgende Namen ein: Klaus, Silke, Manu, Stefan, Alex, Ines.

insert into constr_test values ('Klaus');
insert into constr_test values ('Silke');
insert into constr_test values ('Manu');
insert into constr_test values ('Stefan');
insert into constr_test values ('Alex');
insert into constr_test values ('Ines');

Erstellen Sie ein Check-Constraint für die Spalte name. Es soll verhindern werden, dass der Name Anna und Berta eingefügt werden kann.

alter table constr_test 
  add constraint ck_name_constr_test 
  check (name not in ('Anna', 'Berta'))
;

Versuchen Sie nun den Namen Anna in die Tabelle einzufügen.
Was passiert und warum?

insert into constr_test values ('Anna');
FEHLER in Zeile 1:
ORA-02290: Verstoß gegen CHECK-Regel (SYS.CK_NAME_TEST1)

Schalten Sie nun die Constraint-Prüfung aus.

alter table constr_test disable validate constraint ck_name_constr_test;

Versuchen Sie nun wiederum den Namen Anna in Ihre Tabelle einzufügen.
Was passiert und warum?

insert into constr_test values ('Anna');
FEHLER in Zeile 1:
ORA-25128: Kein Einfügen/Aktualisieren/Löschen bei Tabelle mit 
deaktiviertem und validiertem Constraint (SYS.CK_NAME_TEST1)

Schalten Sie die Constraint-Prüfung nun wieder ein. Aktivieren Sie sie so, dass bestehende Datensätze nicht durch das Constraint geprüft werden. Was passiert und warum?

alter table constr_test enable novalidate constraint ck_name_constr_test;
insert into constr_test values ('Anna');
FEHLER in Zeile 1:
ORA-02290: Verstoß gegen CHECK-Regel (SYS.CK_NAME_TEST1)

Können Sie den Namen Berta der Tabelle hinzufügen?

insert into constr_test values ('Berta');
FEHLER in Zeile 1:
ORA-02290: Verstoß gegen CHECK-Regel (SYS.CK_NAME_TEST1)

Ändern Sie den Zustand des Constraints so ab, dass bestehende Datensätze auf Übereinstimmung geprüft werden. Was passiert und warum?

alter table constr_test enable validate constraint ck_name_constr_test;
Tabelle wurde geändert.

Benutzerverwaltung

Sie werden drei Benutzer namens anna, berta und carla erstellen.

Erstellen Sie den User anna. Das Passwort ist auch anna.

create user anna identified by anna;
grant connect, create session to anna;

Melden Sie sich unter SQLPLUS als anna@testdb29 an. Was passiert?
```
sqlplus anna@testdb29
Kennwort eingeben: anna
SQL>
```

Versuchen Sie eine Tabelle zu erstellen.

SQL> create table annatest(s1 int);
FEHLER in Zeile 1:
ORA-01031: Unzureichende Berechtigungen

Melden Sie sich als User anna wieder ab.
```
SQL> exit
```

Geben Sie dem User anna unbegrenzten Speicherplatz auf dem Tablespace users.

alter user anna quota unlimited on users;
alter user anna default tablespace users 
      quota unlimited on users;

Versuchen Sie wieder eine Tabelle zu erstellen. Was passiert und warum?

sqlplus anna@testdb29
Kennwort eingeben: anna
SQL> create table annatest(s1 int);

Fügen Sie zwei Datensätze in die Tabelle ein.

SQL> insert into annatest values(1);
SQL> insert into annatest values(2);
SQL> select * from annatest;
        S1
----------
         1
         2

Schließen Sie die Transaktion ab.
```
SQL> commit;
```

Erstellen Sie einen User berta und als berta eine Tabelle bertatest. Versuchen Sie als User berta die Tabelle annatest abzufragen.

create user berta identified by berta;
grant connect, create session to berta;
alter user berta quota unlimited on users;
alter user berta default tablespace users quota unlimited on users;

Was passiert und warum?

sqlplus berta@testdb29
Kennwort eingeben:
SQL> create table bertatest(s1 int);
SQL> select * from anna.annatest;
select * from anna.annatest
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden

Versuchen Sie eine Lösung zu finden.

sqlplus anna@testdb29
SQL> grant select on annatest to berta;
Benutzerzugriff (Grant) wurde erteilt.

sqlplus berta@testdb29
SQL> select * from anna.annatest;
        S1
----------
         1
         2

Überprüfen Sie, ob es möglich ist im Schema eines anderesn Users eine Tabelle zu erstellen.
```
sqlplus berta@testdb29
SQL> create table anna.bertatest(s1 int);
FEHLER in Zeile 1:
ORA-01031: Unzureichende Berechtigungen
```
Kann nur durch SYS dem User anna zugewiesen werden (grant ... with admin option). sys darf natürlich Rechte für Objekte vergeben. anna kann nur für ihre Objekte die Berechtigungen weitergeben.
```
sqlplus anna@testdb29
SQL> grant select, insert, update, delete on anna.annatest to berta;
Benutzerzugriff (Grant) wurde erteilt.
```

Namensauflösung

Teil 1 - Local Naming

Öffnen Sie die Datei sqlnet.ora.
Wie ist die Reihenfolge der Namensauflösung?
TNSNAMES, ONAMES, HOSTNAME
Nennen Sie die Datei tnsnames.ora in tnsnames.old um. Versuchen Sie sich an dern Datenbank testdb29 anzumelden. Was passiert und warum?
ORA-12154 TNS: Der Servicename konnte nicht aufgelöst werden.
Nennen Sie die Datei tnsnames.old wieder in tnsnames.ora um. Was passiert und warum?
Der Connect wird durchgeführt.
Öffnen Sie die Datei sqlnet.ora. Ändern Sie dort die folgende Zeile:
names.directory_path = (ONAMES, HOSTNAME)
Speichern Sie die Datei sqlnet.ora.
Versuchen Sie sich an der Datenbank testdb29 anzumelden. Was passiert?
ORA-12154 TNS: Der Servicename konnte nicht aufgelöst werden.
Öffnen Sie wieder die Datei sqlnet.ora. Ändern Sie dort die folgende Zeile:
names.directory_path = (TNSNAMES, ONAMES, HOSTNAME)
Speichern Sie die Datei sqlnet.ora.
Versuchen Sie sich wieder an der Datenbank testdb29 anzumelden. Was passiert?
Der Connect wird durchgeführt.
Öffnen Sie die Datei tnsnames.ora. Ändern Sie dort den Port für die Datenbank testdb29 auf 1539. Versuchen Sie sich wieder an der Datenbank testdb29 anzumelden. Was passiert?
ORA-12541 TNS: Kein Listener.
Öffnen Sie die Datei listener.ora. Ändern Sie auch dort den Port für die Datenbank testdb29 auf 1539. Starten Sie den Listener neu. Was passiert?
Der Connect wird durchgeführt.
Ändern Sie die Port-Einstellungen in listener.ora und tnsnames.ora wieder zurück auf 1521
Erweitern Sie die Datei tnsnames.ora, so dass Sie sich auf die Datenbank Ihres Nachbarn verbinden können.

Teil 2 - Host Naming

Öffnen Sie die Datei sqlnet.ora. Ändern Sie dort die folgende Zeile:
names.directory_path = (HOSTNAME)
Speichern Sie die Datei sqlnet.ora.
Versuchen Sie sich an der Datenbank testdb29 anzumelden. Was passiert?
ORA-12154 TNS: Der Servicename konnte nicht aufgelöst werden.

Öffnen Sie die Datei listener.ora. Ändern Sie die Datei folgendermassen:

...
(SID_DESC=
   (GLOBAL_DBNAME=pinguin)
   (ORACLE_HOME=/opt/oracle/product/9ir2)
   (SID_NAME=testdb29)
)
...

Starten Sie den Listener neu.

Verbinden Sie sich mit sqlplus mit der Datenbank testdb29.
```
sqlplus /nolog
connect sys/sys@pinguin
```
Was passiert?
Der Connect wird durchgeführt.
Warten Sie bis Ihr Nachbar soweit ist und versuchen Sie sich mit der Datenbank auf dessen Computer zu verbinden.
Stellen Sie die ursprüngliche Datei listener.ora wieder her.
Öffnen Sie die Datei sqlnet.ora. Brinden Sie dort die folgende Zeile wieder auf den alten Zustand:
names.directory_path = (TNSNAMES, ONAMES, HOSTNAME)
Speichern Sie die Datei sqlnet.ora.

Cold Backup im NOARCHIVELOG-Modus

In dieser Übung wollen wir folgendes Szenario durchspielen. Jeden Tag gegen 18:00 Uhr wir die Datenbank testdb29 mit Hilde eines Cold-Backups gesichert. Am Dienstag gegen 16:00 Uhr bemerken Sie, dass die Datei users01.dbf beschädigt ist. Sie müssen die Funktionsfähigkeit der Datenbank schnellstmöglich wieder herstellen.

Montag 18:00 Uhr - Cold Backup
Führen Sie ein Cold-Backup durch. Die Datenbank testdb29 soll im Modus NOARCHIVE-Log sein.

shutdown immediate;
startup mount;
alter database noarchivelog;
archive log stop;

Fahren Sie nun die Instanz herunter un kopieren Sie alle notwendigen Dateien in das Verzeichnis /backup. Fahren Sie anschließend die Instanz wieder hoch.

shutdown immediate;
host;
cp /oracle/oradata/testdb29/* /backup
exit
startup;

Dienstag 08:00 bis 15:59 - Arbeitslast
Simulieren Sie die Arbeitslast. Sie werden hierfür eine Tabelle anlegen, dort fünf Datensätze einfügen und drei Log-Switches ausführen.

create table unterricht (fachnummer number, fachname varchar2(20));
insert into unterricht values(1,'Deutsch');
insert into unterricht values(2,'Deutsch');
insert into unterricht values(3,'Englisch');
insert into unterricht values(4,'Mathe');
insert into unterricht values(5,'Zeichnen');
commit;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Dienstag 16:00 - Verlust der Datei users01.dbf
Simulieren Sie den Verlust der Datendatei users01.dbf.

shutdown immediate;
host;
rm /oracle/oradata/testdb29/users01.dbf
exit
startup;
...
Datenbank mit Mount angeschlossen.
ORA-01157: Datendatei 3 kann nicht identifiziert/gesperrt werden
           Siehe DBWR-Trace-Datei.
ORA-01110: Datendatei 3: '/oracle/oradata/testdb29/users01.dbf'

Wiederherstellung der Datenbank testdb29 (1. Versuch)
Als erstes versuchen Sie die Datei users01.dbf wiederherzustellen.

shutdown immediate;
host;
cp /backup/users01.dbf /oracle/oradata/testdb29
exit
startup;
...
Datenbank mit Mount angeschlossen.
ORA-01113: Für Datei '3' ist eine Datenträger-Recovery notwendig
ORA-01110: Datendatei 3: '/oracle/oradata/testdb29/users01.dbf'

Versuchen Sie nun, die seit der letzten Sicherung durchgeführten Transaktionen aus den Online-Redo-Log-Dateien wiederherzustellen. Es kann sein, dass bei Ihnen die Datei users01.dbf nicht die Dateindatei 3 ist.

recover datafile 3;

Warum schlägt dieses Vorhaben fehl?
Es müssen alle Datendateien wiederhergestellt werden.

Wiederherstellung der Datenbank testdb29 (2. Versuch)
Versuchen Sie alle Datendateien wiederherzustellen.

shutdown immediate;
host;
cp /backup/* /oracle/oradata/testdb29
exit
startup;
ORACLE-Instanz hochgefahren.
...
ORA-00214: Steuerdatei '/hd2/control02.ctl' Version 684 
 nicht konsistent mit Datei
 '/oracle/oradata/testdb29/control01.ctl Version 657

Natürlich müssen noch die Multiplex-Control-Dateien an den entsprechenden Stellen und unter den entsprechenden Namen wiederhergestellt werden. Da die Control-Dateien immer identisch sind, kann die gesicherte Control-Datei aus dem Verzeichnis /backup an die anderen Speicherorte ohne weiteres kopiert werden.

shutdown immediate;
host;
cp /backup/control01.ctl /hd2/control02.ctl
cp /backup/control01.ctl /hd3/control03.ctl
exit
startup;
ORACLE-Instanz hochgefahren.
...
Datenbank geöffnet.

Nun testen Sie, ob die Tabelle unterricht noch vorhanden ist.

select * from unterricht;
              *
FEHLER in Zeile 1: 
ORA-00942: Tabelle oder View nicht vorhanden.

Cold Backup im ARCHIVELOG-Modus

In dieser Übung wollen wir das Szenario der vorherigen Übung durchspielen. Die Datenbank wird aber in den Archive-Log-Modus versetzt.

select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
1 Zeile wurde ausgewählt.

shutdown immediate;

sqlplus /nolog
connect sys/sys as sysdba

startup mount;
alter database archivelog;

Manuell:

alter system archive log start;
alter system set log_archive_start=true scope=spfile;
alter database open;

Montag 18:00 Uhr - Cold Backup
Im folgenden werden Sie ein Cold-Backup durchführen. Fahren Sie nun die Instanz herunter und kopieren Sie alle notwendigen Dateien in das Verzeichnis /backup. Fahren Sie anschließend die Instanz wieder hoch.

shutdown immediate;
host;
cp /oracle/oradata/testdb29/* /backup
exit
startup;

Dienstag 08:00 bis 15:59 - Arbeitslast
Im folgenden werden Sie die Arbeitslast vom Dienstag simulieren. Legen Sie hierfür eine Tabelle an, fügen dort fünf Datensätze ein und führen drei Log-Switches aus.

create table unterricht (fachnummer number, fachname varchar2(20));
insert into unterricht values(1,'Deutsch');
insert into unterricht values(2,'Deutsch');
insert into unterricht values(3,'Englisch');
insert into unterricht values(4,'Mathe');
insert into unterricht values(5,'Zeichnen');
commit;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Dienstag 16:00 - Verlust der Datei users01.dbf
Simulieren Sie den Verlust der Datendatei users01.dbf.

shutdown immediate;
host;
rm /oracle/oradata/testdb29/users01.dbf
exit
startup;
...
Datenbank mit Mount angeschlossen.
ORA-01157: Datendatei 3 kann nicht identifiziert/gesperrt werden
           Siehe DBWR-Trace-Datei.
ORA-01110: Datendatei 3: '/oracle/oradata/testdb29/users01.dbf'

Wiederherstellung der Datenbank testdb29
Versuchen Sie die Datei users01.dbf wiederherzustellen.

shutdown immediate;
host;
cp /backup/users01.dbf /oracle/oradata/testdb29
exit
startup;
...
Datenbank mit Mount angeschlossen.
ORA-01113: Für Datei '3' ist eine Datenträger-Recovery notwendig
ORA-01110: Datendatei 3: '/oracle/oradata/testdb29/users01.dbf'

Als nächstes versuchen Sie nun, die seit der letzten Sicherung durchgeführten Transaktionen aus den Online-Redo-Log-Dateien wiederherzustellen. Es kann sein, dass bei Ihnen die Datei users01.dbf nicht die Dateindatei 3 ist.

recover datafile 3;

Warum sfunktioniert dieses Vorhaben nunmehr?

Control File skripten / wiederherstellen

In dieser Übung werden wir untersuchen, wie sich die Datenbank verhält, wenn alle Control-Files fehlen. Voraussetzung hierbei ist, dass alle Data Files noch vorhanden sind. Glücklicherweise haben wir vorher ein Skript erstellt, welches die Control Files neu erstellen kann. Ansonsten wäre alles etwas aufwendiger.

Skripten Sie das Control File.

alter database backup controlfile to trace;

Schauen Sie in den Ordner /opt/oracle/admin/testdb29/udump nach einer Datei, die eben erstellt wurde.
Öffnen Sie diese Datei in einem Editor. Sie sehen die CREATE CONTROLFILE-Anweisung, die Oracle automatisch erstellt hat.
Fahren Sie die Instanz herunter und löschen Sie alle Control Files.
Starten Sie die Instanz wieder. Was passiert und warum?
Kopieren Sie nun die komplette CREATE CONTROLFILE-Anweisung aus dem Trace-File in das SQL-Plus-Fenster und führen Sie diese Anweisung aus.
Versuchen Sie nun die Datanbank zu öffnen.
```
alter database open;
```
Was passiert?
Alles ist wieder gut.

Vollsicherung mit RMAN

Sie werden mit RMAN eine inkonsistente Vollsicherung der Datenbank testdb29 durchführen. Stellen Sie sicher, dass das Verzeichnis /backup existiert. Starten Sie RMAN.

RMAN target sys/sys@testdb29 nocatalog

Geben Sie folgende Anweisung ein:

run {
   allocate channel s1 type disk format '/backup/b_%u_%s_%p';
   backup database;
}

Erstellen Sie die Tabelle abteilung und tragen dort zwei Abteilungen ein. Führen Sie drei Log-Switches durch. Die Änderungen befinden sich demnach nicht mehr in den Online-Redo-Log-Dateien.

create table abteilung (abtnr int, abtname varchar2(30));
insert into abteilung values (1,'Forschung');
insert into abteilung values (2,'Entwicklung');
commit;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Simulieren Sie den Verlust der Datendatei users01.dbf.

shutdown immediate;
host;
rm /oracle/oradata/testdb29/users01.dbf
exit
startup;
...
Datenbank mit Mount angeschlossen.
ORA-01157: Datendatei 3 kann nicht identifiziert/gesperrt werden
           Siehe DBWR-Trace-Datei.
ORA-01110: Datendatei 3: '/oracle/oradata/testdb29/users01.dbf'

Im welchen Zustand befindet sich die Instanz?
MOUNT

Stellen Sie die Datei users01.dbf mit RMAN wieder her.

RMAN target sys/sys@testdb29 nocatalog

Geben Sie folgende Anweisung ein:

run {
  allocate channel s1 type disk format '/backup/b_%u_%s_%p';
  restore datafile '/oracle/oradata/testdb29/users01.dbf';
  recover datafile '/oracle/oradata/testdb29/users01.dbf';
}

Wechseln Sie zu SQLPLUS und öffnen Sie die Datenbank.

alter database open;

Tuning - Übungen und Lösungen

Appendix

SQL/PL - Übungen und Lösungen
Contents Index

Stefan Hietel dama.go GmbH, Robert Warnke http://rowa.giso.de