Oracle

Server

Jede Instanz für genau eine Datenbank (Instanz = Datenbank), Benutzer sind ein Schema (Schema = User), jeder Benutzer hat einen Default-Tablespace

Oracle Server: DB-Instance + Datenbank

• Instance
  • System Global Area (SGA)
    • Shared Pool
      • Library Cache
      • Data Dictionary Cache
    • Database Buffer Cache
    • Redo Log Buffer
    • Java Pool
    • Large Pool
  • Background Process Structures
    • PMON
    • SMON
    • DBWR
    • LGWR
    • CKPT
    • Optionale Background-Prozesse
      • ARCn
• Datenbank
  • Database Files (Operating System Files)
    • Datafiles
    • Control Files
    • Redo Logfiles
• Other Key Files
  • Parameter Files
  • Password File
  • Archive Redo Logfiles
• User und Server Prozesse
  • Server Process - Program Global Area (PGA)
  • User Process
• Andere Prozesse
  • Advance Queuing
  • Real Application Clusters
  • Shared Server
  • Advanced Replication
  • ...

Oracle installieren

Empfehlungen

Exemplarische Verzeichnis- und Namenskonventionen am Beispiel einer Linuxumgebung

Basisverzeichnis für Installationen

• /opt/oracle/product

Verzeichnis für einzelne Installationen

• /opt/oracle/product/<version>/<installation>/

Basisverzeichnis für Datenbanken

• /opt/oracle/oradata

Verzeichnis für einzelne Datenbanken

• /opt/oracle/oradata/<datenbankname>

Datenbank erstellen/ändern

Verzeichnisse für die Datenbank anlegen

mkdir /opt/oracle/admin/MYDB
mkdir /opt/oracle/oradata/MYDB

Init-Datei anlegen

cp /opt/oracle/product/<version>/<installation>/dbs/init.ora /opt/oracle/product/<version>/<installation>/dbs/initMYDB.ora

vi /opt/oracle/product/<version>/<installation>/dbs/initMYDB.ora

db_name=MYDB
shared_pool_size = 123232153
undo_management=AUTO

Variablen setzen

export ORACLE_BASE=/opt/oracle/app/oracle
export ORACLE_HOME=/opt/oracle/product/<version>/<installation>/
export ORACLE_SID=MYDB

SQL-Plus starten

sqlplus "/ as sysdba"

Datenbank in nomount starten

startup nomount pfile=/opt/oracle/product/<version>/<installation>/dbs/initMYDB.ora;

Datenbank anlegen

create database MYDB
logfile group 1 ('/opt/oracle/oradata/MYDB/redo1.log') size 100M,
group 2 ('/opt/oracle/oradata/MYDB/redo2.log') size 100M,
group 3 ('/opt/oracle/oradata/MYDB/redo3.log') size 100M
character set AL32UTF8
national character set utf8
datafile '/opt/oracle/oradata/MYDB/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '/opt/oracle/oradata/MYDB/sysaux.dbf' size 500M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/opt/oracle/oradata/MYDB/undotbs1.dbf' size 4G
default temporary tablespace temp tempfile '/opt/oracle/oradata/MYDB/temp01.dbf' size 100M;

Datenbank durchstarten

shutdown
startup

DBA-Skripte ausführen

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

Tablespace erstellen/ändern

Größe 10M fest

CREATE TABLESPACE tsd_myuser DATAFILE '/opt/oracle/oradata/MYDB/tsd_myuser.dbf' SIZE 10M;

Größe 10M autoextend max 100M

CREATE TABLESPACE tsd_myuser DATAFILE '/opt/oracle/oradata/MYDB/tsd_myuser.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;

CREATE TABLESPACE tsd_myuser
DATAFILE '/opt/oracle/oradata/MYDB/tsd_myuser.dbf' SIZE 100M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Datafile ändern

ALTER DATABASE DATAFILE '/opt/oracle/oradata/MYDB/tsd_myuser.dbf' resize 500M;
ALTER DATABASE DATAFILE '/opt/oracle/oradata/MYDB/tsd_myuser.dbf' AUTOEXTEND ON NEXT 500M MAXSIZE 2G;

ALTER DATABASE TEMPFILE '/opt/oracle/oradata/MYDB/temp01.dbf' resize 500M;
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/MYDB/temp01.dbf' AUTOEXTEND ON NEXT 500M MAXSIZE 2G;

Schema/User erstellen/ändern

CREATE USER myuser
  IDENTIFIED BY kennwort
  DEFAULT TABLESPACE tsd_myuser
  TEMPORARY TABLESPACE temp
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
-- Roles
GRANT DBA TO myuser;
GRANT CONNECT TO myuser;
ALTER USER myuser DEFAULT ROLE ALL;
-- System Privilege
GRANT UNLIMITED TABLESPACE TO myuser;

Unlock a password

ALTER USER username ACCOUNT UNLOCK;

Lock a password

ALTER USER username ACCOUNT LOCK;

Change password of an unlocked account

ALTER USER username IDENTIFIED BY password;

Change password of a locked account

ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;

Default-Werte

Import/Export

Export

Ganze Datenbank

exp SYSTEM/<password> FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y

Schema

exp <user>/<password> FIlE=scott.dmp OWNER=scott

Tabelle

Aus verschiedenen Schemata

exp SYSTEM/<password> FILE=expdat.dmp TABLES=(scott.emp,hr.countries)

Aus einem Schema

exp <user>/<password> FILE=scott.dmp TABLES=(emp,dept)

Import

Ganze Datenbank

imp SYSTEM/<password> FULL=y FIlE=dba.dmp

Schema

imp <user>/<password> FIlE=scott.dmp

Tabelle

Import mit dem Benutzer

imp SYSTEM/<password> FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

Import und ändern des Benutzers

imp SYSTEM/<password> FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

Datenbankzustände

http://www.datenbank-start.de/zustaende.htm

• shutdown
  • Die Datenbank Instanz ist gestoppt und die Datenbank geschlossen. D.h. es laufen keine Oracle Prozesse, es ist kein Hauptspeicher allokiert und alle Datenbankdateien sind geschlossen. Ausnahme: Unter Windows NT / 2000 läuft der Service und belegt Resourcen.
• nomount

  • Im Zustand NOMOUNT ist die Datenbank Instanz gestartet. D.h. die Prozesse (Windows: Threads) laufen und der Hauptspeicher (SGA) ist allokiert. Die Initialisierungsdatei ("initora") wurde gelesen, so dass diverse Informationen über die Datenbank bereits bekannt sind (z.B. wo sich die controlfiles) befinden.

    Dieser Zustand wird normalerweise nicht manuell herbeigeführt. Lediglich für die Befehle create database, create controlfile, sowie für das Einschalten des Archivelog-Modus mit dem Befehl archive log start wird er benötigt.

• mount

  • In der Initialisierungsdatei sind die control files mit ihren Pfaden eingetragen. Mehrere Einträge bedeuten, dass die Dateien gespiegelt sind. Sie werden gelesen und abgeglichen. Sollten Differenzen zwischen den Dateien auftauchen erscheint eine Fehlermeldung und die Datenbank kann nicht gestartet werden. Der Administrator muss dass manuell durch kopieren dafür sorgen, dass alle Dateien identisch sind. Er sollte dazu die neueste Datei verwenden!

    In den Control-Dateien sind Informationen über die eigentlichen Datenbankdateien abgelegt. Die Dateien sind aber noch nicht geöffnet, so dass in diesem Zustand einigen administrative Aufgaben durchgeführt werden.

    Typisches Beispiel ist das Umbenennen bzw. verschieben von Dateien:

    alter database rename file '<alter Dateiname inkl. Pfad>' to '<neuer Dateiname inkl. Pfad>';

    Achtung! Es handelt sich nur um die Bekanntmachung eines neuen Namens. Deshalb ist vorher die Datei auf Betriebssystemebene umzubenennen bzw. zu kopieren.

• open
  • Jetzt werden alle Datenbankdateien inkl. Redo-Log-Dateien geöffnet und deren Konsistenz geprüft. Wenn die Datendateien nicht auf dem aktuellen Stand sind, weil z.B. zuletzt mit shutdown abort beendet wurde, so wird versucht mit Hilfe der Online-Redo-Log-Dateien den aktuellen Zustand wieder herzustellen. Sollte dieses nicht gelingen, so ist ein Media Recovery und damit manuelles Eingreifen notwendig.

Datenbank starten

• Normal starten

  • startup

• Initialisierungsdatei mit angeben

  • startup pfile=<init<SID>.ora-Datei inkl. Pfad> [ <Zustand> ]

• startup force

  • startup force [ <Zustand> ]

  • Entspricht shutdown abort und anschließendem startup.
• startup restrict

  • startup restrict [ <Zustand> ]

  • Nur Benutzer mit dem Privileg "Restricted Session" können sich anmelden.
  • Überführen in den normalen Modus:

    alter system disable restricted session;

Datenbank stoppen

• shutdown

  • Wartet bis alle Sessions beendet sind
  • Verhindert in dieser Zeit neue Verbindungen
  • Checkpoint für alle Datendateien
  • Datenbank ist in einem konsistenten Zustand

• shutdown transactional

  • Wartet bis alle aktuellen Transaktionen mit Commit oder Rollback beendet sind
  • Verhindert ab diesem Zeitpunkt neue Transaktionen und Verbindungen
  • Checkpoint für alle Datendateien
  • Datenbank ist in einem konsistenten Zustand

• shutdown immediate

  • Wartet bis alle aktuellen Befehle abgearbeitet sind
  • Verhindert in dieser Zeit neue Befehle und Verbindungen
  • Alle offenen Transaktionen werden zurückgefahren
  • Checkpoint für alle Datendateien
  • Datenbank ist in einem konsistenten Zustand

• shutdown abort

  • Nothalt
  • Datenbank ist in einem inkonsistenten Zustand
  • Automatisches Recovery beim erneuten Starten

Beispiel

shutdown abort
/* Datenbank ist in einem inkonsistenten Zustand */
startup
/* Ein Datenbank Recovery wird automatisch durchgeführt */
shutdown
/* Datenbank ist wieder konsistent */ 

Unsauberen shutdown aufräumen

Alle Datenbanken : shutdown.

1. zur Sicherheit, und 2. soll er selber wegräumen was er wegräumen kann...

Zeit geben zum aufräumen, gucken ob sich was tut in den folgenden Bereichen.

"sgadef<sid>.dbf" in "ORACLE_HOME/dbs" entfernen, wenn vorhanden

rm .../dbs/sgadef<sid>.dbf

Prozesse aufräumen

ps -ef | grep ora_ | grep &ORACLE_SID

Als erstes den "...pmon..."-Prozess, dann etwas warten, die anderen folgen ihrem pmon vermutlich bald ins digitale Nirvana.

kill -9 <Process_ID_Number>

Semaphoren und Speicher von Oracle aufräumen

ipcs
ipcrm -m <Shared_Memory_ID_Number>
ipcrm -s <Semaphore_ID_Number>

LK-Dateien wegräumen, sofern noch vorhanden

rm .../dbs/lk<sid>

Instanz starten.

Ich persönlich versuche nach erfolgreichem Start noch ein mal shutdown und startup, um zu gucken ob sie sauber runter kommt.

Init-Datei

• Wird beim Starten der Instanz gelesen
• Dateiname kann beim starten angegeben werden
• Für alle Parameter existieren Defaultwerte in der Datenbank
• Defaultname und Pfad abhängig vom Betriebssystem (üblich: init<SID>.ora)
• Groß- und Kleinschreibung ggf. nur für Dateinamen relevant
• Kommentare mit #
• Beliebige Reihenfolge
• Anzeige der aktuellen Parameter (im Server Manager, SQL*Plus):

  show parameters

• Beispieldatei mit den wichtigsten Parametern wird mitgeliefert
• Beschreibung der Parameter im Oracle 8i / Oracle 9i - Reference Manual
• Leistungsoptimierung der Datenbank erfolgt mit diesen Parametern

Parameter

Arten der Init-Datei

• pfile
  • Parameter-File, Textdatei
  • Erzeugen aus spfile:

    create pfile from spfile;

  • Erzeugen aus spfile:

    create pfile=/path/to/backup.ora from spfile;

• spfile
  • Server-Parameter-File, Binärdatei
  • Erzeugen aus pfile:

    create spfile from pfile=/path/to/backup.ora

  • Änderungen mit Alter-System:

    alter system set db_recovery_file_dest_size=10g;

Reihenfolge der Dateisuche

1. spfileSID.ora
2. spfile.ora
3. initSID.ora
4. init.ora

Ohne Init-Datei schlägt der Start fehl.

Standard-Verzeichnis

ORACLE_HOME/dbs

ORACLE_HOME/database

Standard-Name

init<SID>.ora

Ausgabe der Parameter

desc v$parameter

show parameter control_files;

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

Verweise zwischen den Dateien

Ein pfile mit dem folgenden Inhalt anlegen:

SPFILE=/path/to/spfiletestdb.ora

Überwachung

Logfiles

Überwachen der Alert Log File und Trace Files

Zur Fehlerüberwachung und Diagnostik dienen mehrere Arten von Log/Trace-Files. Diese ASCII-Dateien können mit den üblichen Unix-Befehlen (tail, grep, ...) betrachtet und ausgewertet werden.

alertSID.log

Pfad wird in BACKGROUND_DUMP_DEST festgelegt. Dies ist die eigentliche Log-Datei für die Instanz. Diese sollte regelmäßig untersucht und auch mal gelegentlich geleert werden.

find . -type f -name alert*.log

Background Trace Files

Pfad wird in BACKGROUND_DUMP_DEST festgelegt. Die Benennung ist: sid_prozessname_pid.trc. Wobei der Prozessname sich aus dem Background-Prozess ergibt (LGWR, DBWR, ...). Die PID ist die PID vom Betriebssystem. Diese Dateien werden nur bei Fehlern angelegt. Diese sollte regelmäßig untersucht und dann gelöscht werden.

find . -type f -name *.trc

User Trace Files

Pfad wird in USER_DUMP_DEST festgelegt. Die Bennenung ist: sid_ora_PID.trc. Diese Dateien werden nur bei Fehlern angelegt. Diese sollte regelmäßig untersucht und dann gelöscht werden.

find . -type f -name *.trc

(De-)Aktivieren des User-Tracing

alter session set SQL_TRACE = TRUE;

SQL_TRACE ist ein statischer Parameter.

Achtung: Es entstehen sehr viele Daten, da z.B. jedes SQL-Statement geloggt wird. Die Default-Einstellung ist TRUE und sollte daher auf FALSE gesetzt werden.

Life-Überwachung Logfiles

Mit einfachsten Mitteln auf der Console kann man zum Beispiel das tun:

while true; do sleep 5; clear; [z.B. diverse find's o.ä.]; done

Offene Cursor ermitteln

Cursor

SELECT * FROM v$open_cursor;

Anzahl Cursor

SELECT count(*) FROM v$open_cursor;

Cursor je Session

SELECT SID, count(*) FROM v$open_cursor
GROUP BY SID;

Cursor je User

SELECT USER_NAME, count(*) FROM v$open_cursor
GROUP BY USER_NAME;

SQL-Plus

export ORACLE_SID=datenbankname

sqlplus "/ as sysdba"

PL/SQL

• Liste aller Tabellen

  SELECT * FROM cat;

• Liste aller User

  SELECT * FROM dba_users

• Liste aller angemeldeten User/Sessions

  SELECT * FROM v$session

• Ausgabe in Datei umleiten

  SPOOL liste.txt

• Ausgabe in HTML

  set markup HTML on
  spool index.html
  select * from tab;
  spool off
  set markup HTML off

• User-Rechte

  SELECT * FROM user_sys_privs;

• DBA-Rechte

  SELECT * FROM dba_sys_privs;

• Rechte der Session

  SELECT * FROM session_privs;

• Liste aller Rollen

  SELECT * FROM dba_roles;

• Was tun die Benutzer grade?

  SELECT EXECUTIONS, USERS_EXECUTING, username, sql_text 
  FROM v$session se, v$sql sq
  WHERE se.sql_address = sq.address;
  

• Kopieren ganzer Datenbanken

  COPY FROM scott/tiger@db1 TO scott/tiger@db2 INSERT mytable USING select * from mytable;

• Alle eigenen Sessions beenden, ausser der aufrufenden

  EXECUTE system.kill my sessions;

Skripte

Kommentare

/* So kommentiert man
  mehrzeilig */
--So kommentiert man einzeilig

Struktur

SET serveroutput ON;
DECLARE -- Deklarationsblock
  variablenname datentyp;
  variablenname datentyp := wert;
  variablenname tabellenname.spaltenname%TYPE;
  CURSOR cursorname IS SELECT spalte FROM tabelle;
  exception_name EXCEPTION;
  PRAGMA exception_init(exception_name, -exception_number);
BEGIN -- Ausführungsteil
  SELECT spalte INTO variable FROM tabelle WHERE ...;
  LOOP
    ...
    EXIT WHEN BEDINGUNG;
  END LOOP;
  WHILE Bedingung LOOP
    ...
  END LOOP;
  FOR i IN REVERSE 1 .. 5 LOOP
    DBMS_OUTPUT.put_line(TO_CHAR(i));
  END LOOP;
  IF ... THEN
    ...;
  ELSIF ... THEN
    ...;
  ELSE
    ...;
  END IF;
  raise <exception_name>;
EXCEPTION -- Ausnahmeverarbeitung
  WHEN exception_name THEN -- Execptionhandlung für die Exception exception_name
    ...
  WHEN OTHERS THEN -- Execptionhandlung für alle restlichen, nicht erwarteten Exceptions
    ...
END;

Benutzereingaben

Einen Wert bei jedem Zugriff Abfragen

SELECT spalte FROM tabelle WHERE spalte LIKE '&suchwert';

Einen Wert einmalig Abfragen, wird bei jedem Zugriff wieder verwendet

SELECT spalte FROM tabelle WHERE spalte LIKE '&&suchwert';

Create

CREATE TABLE menschen (
	mensch_id number(10) not null, 
	mensch_name varchar2(50) not null, 
	land_id number(10), 
	CONSTRAINT pk_menschen PRIMARY KEY (mensch_id), 
	CONSTRAINT fk_länder FOREIGN KEY (land_id) REFERENCES länder(land_id) 
);

Erzeugen aus anderer Tabelle

CREATE TABLE tabelle2
AS (SELECT * FROM tabelle2);

CREATE TABLE tabelle2
AS (SELECT * FROM tabelle2 WHERE tabelle2.id > 5000);

CREATE TABLE tabelle2
AS (SELECT spalte1, spalte2 FROM tabelle2 WHERE tabelle2.id > 5000);

Kopieren einer Tabelle ohne Zeilen zu übernehmen durch getrickst negative Bedingung

CREATE TABLE tabelle2
AS (SELECT * FROM tabelle1 WHERE 1=2);

Alter

ALTER TABLE tabelle1
RENAME TO tabelle2;

ALTER TABLE tabelle1
ADD spalte1 spaltendefinition;

ALTER TABLE tabelle1
MODIFY spalte1 spaltendefinition_neu;

ALTER TABLE tabelle1
RENAME COLUMN spalte1 TO spalte2;

ALTER TABLE tabelle1
DROP COLUMN spalte1;

Drop

DROP TABLE tabelle;

Insert

Insert into tabelle
  (spalte1, spalte2, spalte3, spalte4)
Values
  ('wert1', 'wert2', 'wert3', 'wert4');

Update

UPDATE table 
SET columnA = ''

UPDATE table 
SET columnA = columnB

Update über mehrere Tabellen (Eigentlich hab ich auch keine Ahnung mehr was ich da mal gemacht hab ...)

Temporäre Tabelle erstellen, Update durchführen, temporäre Tabelle wegwerfen

-- Daten zusammenführen
CREATE TABLE temp_tabelle AS 
    SELECT 
    qt1.id AS "qt1_id", qt2.spalte1, qt2.spalte2
    FROM quell_tabelle2 qt2, zuo_tabelle zuo, quell_tabelle1 qt1
    WHERE qt2.id = zuo.id AND zuo.id = qt1.id;
-- Update durchführen
UPDATE quell_tabelle1 qt1 SET spalte2 = 
    (SELECT DISTINCT 
        spalte2
        FROM temp_tabelle
        WHERE qt1.id = temp_tabelle.qt1_id
    ) WHERE EXISTS (SELECT 
        spalte2
        FROM temp_tabelle
        WHERE qt1.id = temp_tabelle.qt1_id
    );
UPDATE quell_tabelle1 qt1 SET spalte1 = 
    (SELECT DISTINCT 
        spalte1
        FROM temp_tabelle
        WHERE qt1.id = temp_tabelle.qt1_id
    ) WHERE EXISTS (SELECT 
        spalte1
        FROM temp_tabelle
        WHERE qt1.id = temp_tabelle.qt1_id
    );
-- Testabfrage
SELECT 
    qt1.id AS "qt1_id", qt1.spalte1, qt1.spalte2,
    qt2.id, qt2.spalte1, qt2.spalte2
FROM quell_tabelle2 qt2, zuo_tabelle zuo, quell_tabelle1 qt1
WHERE qt2.id = zuo.id AND zuo.id = qt1.id;
-- Aufräumen
DROP TABLE temp_tabelle;
-- Abschließen
commit;

Delete

DELETE FROM tabelle
WHERE name='wegdamit';

Lösche alles aus tabelle_a wenn es schon in tabelle_b ist

DELETE FROM tabelle_a
WHERE EXISTS ( 
	select tabelle_b.name
	from tabelle_b
	where tabelle_b.objekt_id = tabelle_a.objekt_id
	and tabelle_b.objekt_name = 'doppelagent' 
);

Lösche alles aus tabelle_a wenn feld1 und feld2 nicht feldx und feldy entspricht

DELETE FROM tabelle_a
WHERE NOT EXISTS ( 
	select *
	from tabelle_b
	where tabelle_a.field1 = tabelle_b.fieldx
	and tabelle_a.field2 = tabelle_b.fieldz 
);

Konvertieren

UPDATE tabelle 
SET spalte1 = to_number(SUBSTR(to_char(spalte2), 1, 2) || '000000')

EXISTS

Kann als Bedingung für Subquerys verwendet werden, wenn das Subquery mindestens eine Zeile liefert. Ist allerdings sehr ineffizient, da das Subquery für jede Zeile der befragten Tabelle ausgeführt wird.

SELECT spalte
FROM tabelle
WHERE [NOT] EXISTS ( subquery );

Zwei Beispiele, ein mal alle Kunden die mindestens eine Bestellung haben, ein mal alle Kunden die keine Bestellung haben.

SELECT * FROM kunden
WHERE EXISTS (select * from bestellungen where kunden.kunde_id = bestellungen.kunde_id);
SELECT * FROM kunden
WHERE NOT EXISTS (select * from bestellungen where kunden.kunde_id = bestellungen.kunde_id);