Oracle Notes

Aus Bitbull Wiki
Wechseln zu: Navigation, Suche

Inhaltsverzeichnis

BASICS

EXTERNE REFERENZEN

LINKS

Oracle Manuals (DOC Server)
http://tahiti.oracle.com
OTN - Oracle Tech Net (Feature/Function Overview)
http://www.oracle.com/database/product_editions.html
Oracle Shop
http://oraclestore.oracle.com -> Lizenzvereinbarungen
Lizenz Beispiel (CPU / Socket)
http://www.oracle.com/corporate/contracts/library/processor-core-factor-table.pdf
Oracle Basis Security Konzept
http://www.oracle.com/technology/pub/articles/project_lockdown/index.html
Oracle 10g basic auditing
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm
Metalink / My Oracle Support
https://metalink.oracle.com
CSI: Customer Support Identifier (Serial ID auf Lizenz Ebene, zB: 10gEE, 5 Sever)
SR: Service Requests
Akadia - installations doku und scripte
http://www.akadia.com/html/publications.html
FORUM + EXAMPLES + SCRIPTS
http://www.psoug.org

Tools

TVD$XTAT -> tool um trace files auszuwerten
http://antognini.ch/category/apmtools/tvdxtat/
Stress Test Tool
Hammer Ora
http://hammerora.sourceforge.net/
gratis gui von oracle
http://www.oracle.com/technology/products/database/sql_developer/index.html
admin view plugin von fourth elephant für sql developper
http://www.fourthelephant.com/sqldeveloper/download/

Scripte

http://www.trivadis.com/uploads/tx_cabagdownloadarea/10g_scripts.zip
http://www.trivadis.com/uploads/tx_cabagdownloadarea/11g_scripts.zip
http://files.hanser.de/hanser/docs/20061204_26124134755-95_Skripte.zip

Bücher

Author: Thomas Kyte
Titel: Expert Oracle Database Architecture: 9i and 10g
ISBN-13: 978-1590595305
Author: Alexander Kick
Titel: Oracle Datenbankadministration mit SQL Scripten
ISBN-13: 978-3446407275

Support / Releases

Premier Support: 5 Jahre (ab Release beginn)

Extended Support: 6. - 8. Jahr (ab Release beginn)

Sustaining Support: 9. + Jahr (ab Release beginn)

Kosten: 22% der Lizenzkosten pro Jahr

http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf

Release: 10.2.0.5.0
         |  | | | |
         |  | | | Plattform Specific Release Number
         |  | | Patch Level
         |  | Application Server Release Number
         |  Database Maintenance Release Number (Update Release)
         Maijor Release Number
Product                         | CPUs | RAM | User | Size
----------------------------------------------------------
Enterprise Edition (EE)         | max  | max | max  | max
Standard Edition (SE)           | 4Soc | max | max  | max
Standard Edition One (SE1)      | 2Soc | max | max  | max
Express Edition (XE)            | 1    | max | max  | 4GB

Cluster Varianten

RAC Cluster (HA + HP)
    ------ LB ------
    ................
 ----  ----  ----  ----
 |S1|  |S2|  |S3|  |S4|
 |  |  |  |  |  |  |  |
 ----  ----  ----  ----
   |    |      |     |
   -------------------
           |
        -------
        | SAN |
        -------
DATA GUARD (HA / Data Protection)
    ---VIP---
   /         \
 ----   |   ----
 |S1|   :   |S2|
 |  |   |   |  |
 ----   :   ----
        |
PRIMARY :  STANDBY
        |
 LOG -----> APPLY


MAA (Maximum Availability Architecture)

Kombination aus "Data Guard" und RAC

Architektur Uebersicht

SGA (System Global Area)

Shared Pool
Library Cache
Data Dictonary Cache
Database Buffer Cache
Redo Log Buffer

Oracle Process Threads

SMON (System Monitor, Roll Forward)

DBWO (Database Writer, DB Cache <> Data File)

PMON (Process Monitor, Roll Back)

CKPT (Check Point, SCN -> Control File, DB File)

LGWR (Log Writer, Redo Log Buffer -> Online Redo Log)

ARCO (Archiver, Online Redo Log -> Offline Redo Log [Archive Log])

Beispiel Zugriff

  • UPDATE "King" -> "Kong"
    • Lock auf Datensatz setzen
    • King nach DB Cache lesen
      • (Block aus UNBDO in DB Cache laden)
      • King nach UNDO schreiben
      • King nach REDO schreiben
    • King im DB Cache nach Kong aendern (Index auch)
      • neuen Wert nach REDO schreiben
      • Commit nach REDO buffer
      • Befehl an LOGWR um Blöcke ins Redo log zu schreiben
  • Log Switch lösst einen Check Point aus
    • SCN (System Change Nummer)
      • Control File
      • Data File (Jedem)
  • Check Point lösst keinen Log Switch aus
    • Check Points können auch auf Basis Zeit, Volumen getriggert werden
SQL> SELECT * FROM V$LOGHIST;

STATIC CONFIGURATION PARAMETER

Allgemein

SQL> SHOW PARAMETER <parameter name>
SQL> SELECT * FROM V$PARAMETER; -- parameter aus dem memory lesen
SQL> SELECT * FROM V$SPPARAMETER; -- spfile online ausgelesen
SQL> SHOW USER; -- wer bin ich denn???


PFILE / SPFILE

pfile muster ausschnitt
*.dbname = DB1
db1a.instance_name = 'DB1A'
db1b.instance_name = 'DB1B'
db1a.db_cache_size = 256M
db1b.db_cache_size = 128M
 |    |              |
 |    |              angepasster wert
 |    angepasste variable
 welche instanz ist betroffen (* = alle)
PFILE - lesbare from von spfile

beim ersten start von oracle wird mit pfile gestartet

SQL> CREATE PFILE='/u00/.../initDB1.ora' FROM SPFILE='/u00/.../spfileDB1.ora';
SPFILE - binär from von pfile

oracle schreibt im laufen nur ins spfile, oder ins memory

SQL> CREATE SPFILE='/u00/.../spfileDB1.ora' FROM PFILE='/u00/.../initDB1.ora';
 
SQL> SELECT * FROM V$PARAMETER;
SQL> ALTER SYSTEM SET xyz=123 SCOPE=MEMORY COMMENT='YYYY.MM.DD:USER: gemacht weil ....';
SQL> ALTER SYSTEM SET xyz=123 SCOPE=BOTH;
SQL> ALTER SYSTEM SET xyz=123 SCOPE=SPFILE;
SQL> ALTER SYSTEM RESET xyz SCOPE=SPFILE SID='*';
SQL> ALTER SESSION SET xyz=123; # verändert aktuelle session
SQL> GRANT select_catalog_role to scot; # dem user scott lesenden zugriff auf die parameter geben

Hinweis: SCOPE=BOTH -> ist default

Setzen von undokumentierten Parametern
SQL> ALTER SYSTEM SET "_xyz"=FALSE ;
Hinweis

ab 11g ist es möglich ein S/Pfile aus dem memory zu dumpen

INIT.ORA

  • parameter file der oracle instanz
  • auf dem server
  • 1x pro instanz
  • konfiguration dispatcher
  • konfiguration shared server
Example
...
DB_NAME = DB1
DB_DOMAIN = DOMAIN.COM
SERVICE_NAMES = DB1.DOMAIN.COM
GLOBAL_NAMES=TRUE
...

MEMORY ARCHITEKTUR

ASSM - Automatic Shared Memory Management

Self Tuning Feature seit 10g
SGA_TARGET > 0
STATISTICS_LEVEL = TYPICAL|ALL #ist per default eingeschaltet
Hinweis
lower limits von dynamischen caches explizit definieren
diese werte werden dann trotz dynamischer verwaltung nicht unterschritten
MetaLink Note: 257643.1 (Automated SGA Memory Tuning)
Oracle 10g Memory Management

Total Used Memory = SGA_TARGET + PGA_AGGREGATE_TARGET

Oracle 11g Memory Management

MEMORY_TARGET = SGA_TARGET + PGA_AGGREGATE_TARGET

SGA - System Global Area

SQL> SELECT * FROM v$sgainfo;
  • Dynamisch -> Default
  • Statisch
    • Large Pool
    • Java Pool
    • Streams Pool
    • Database Buffer Cache #gut angezeigt in dbconsole
    • Shared Pool
      • Library Cache
        • Shared SQL Area
        • PL/SQL Procedures and Packages
      • Dictonary Cache
    • Redo Log Buffer (S)

SGA_MAX_SIZE

  • maximale grösse der sga

SGA_TARGET $

  • summe aller SGA komponenten

Shared Pool

hier drin werden alle PL/SQL befehle abgearbeitet

  • Bestehend aus
    • Library Cache (SQL/PL-SQL Code)
    • Data Dictonary Cache (Row Cache)
  • wenn der shared pool zu kein ist, tritt der fehler ORA-040031 auf
    • shared pool vergrössern
    • large pool definieren
    • mit DBMS_SHARED_POOL grosse Befehle und PL/SQL packages mit KEEP "pinnen"
    • mit SHARED_POOL_RESERVED_SIZE memory reservieren
    • für packages die nicht im shared pool platz finden
views
V$SQLAREA
V$SGASTAT
V$SQLSTATS
Hinweis

wenn ein large pool definiert ist, nimmt zB RMAN diesen und greift nicht in den
shared pool, dies verhindert eine beeinflussung des betriebs bei der RMAN nutzung.

DB BUFFER CACHE

  • verwaltet Daten, Index, UNDO, ... blöcke
  • einige 100 MB bis einige GB gross
  • Multiple Buffer Pools
    • DEFAULT Pool
      • hier werden blöcke normalerweise gecached
    • KEEP Pool
      • alternativer pool um zB häuffig genutzte tabellen zu separieren
    • RECYCLE Pool
      • alternativer pool um zB häuffig genutzte tabellen zu separieren

alle drei pools funktionieren nach dem LRU prinzip
die daten im pool werden bei bedarf überschrieben

SQL> CREATE TABLE plz ( plz NUMBER(4) NOT NULL, ort VARCHAR2 (120) ) STORAGE (BUFFER_POOL KEEP) TABLESPACE data01;
Parameter
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE

cache innerhalb der SGA_MAX_SIZE zu vergrössern ist immer umgehend möglich
beim verkleinern müssen die ressourcen vorher frei sein

v$buffer_bool
buffer cache flushen (achtung)
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

REDO LOG BUFFER

der redo cache ist ein online buffer für die "online redo logs"
diese werden vom LogWriter in die "online redo logs" geschrieben.

  • bei jedem commit
  • alle drei sekunden
  • wenn der cache zu 30% gefüllt ist
LOG_BUFFER = n (bytes)
Hinweis

Default ist GREATEST(500K, 128K x CPU count)

LARGE POOL

  • für backups und recovery mit RMAN
  • für server-session data bei shared-server
  • für I/O slaves
  • die grösse ist dynamisch veränderbar
LARGE_POOL_SIZE = n (K|M|G)
Hinweis

einige 10MB bis (einige) 100MB

Auswertung
SQL> select * from v$sgastat where pool like '%LARGE%';

JAVA POOL

falls in der db mit java gearbeitet wird

SQL> select * from v$sgastat where pool like '%java%';

STREAMS POOL

  • falls streams im einsatz sind
  • enthält gesammelte events
  • ist dynamisch veränderbar
  • falls nicht gesetzt und dennoch benutz, wird memory aus dem shared pool verwendet

STREAM_POOL_SIZE = n byte

PGA - Process Global Area

  • Daten und Kontroll Informationen der Serverprozesse
  • Wird zur Ausführungszeit alloziert
    • Vom OS-Memory für dedicated Server Prozesse
    • Von der SGA für Shared Server Prozesse
  • Kann dynamisch oder statisch verwaltet werden
    • dynamische verwaltung wird empfohlen

summe aller PGAs einer instanz = PGA_AGREGATE_TARGET

PROZESS ARCHITECTURE

Laufende Prozesse und beschreibungen anzeigen:

SQL> select * from v$bgprocess where pserial# = '1';

SMON - System Monitor

  • instance recovery nach crash, shutdown
  • freigabe von temp segmenten

PMON - Process Monitor

  • rollback von abgestürzten transaktionen
  • rollback von abgebrochenen transaktionen
  • freigabe der locks und anderer ressourcen
  • prüft stati von dispatcher und shared server prozessen
  • startet abgestürzte prozesse neu
  • registriert instanz beim listener
Hinweis

bei grossen rollbacks kann der rollback sehr ressourcen intensiv sein

LGWR - Log Writer

  • schreibt redo blocks in redo logs
    • bei jedem commit
    • alle drei sekunden
    • wenn der redo buffer pool zu 1/3 gefüllt ist
    • wenn der redo buffer 1MB gefüllt ist
    • wenn dbwr blöcke schreiben muss, deren redo einträge noch nicht geschrieben sind
  • schreibt redo einträge der verschiedenen transaktionen
    zusammen in die redo log files (piggy backing)
  • generell wird nie in die datenfiles geschrieben,
    ohne das vorgängig auf die aktuelle redo-gruppe geschrieben wurde

DBWR - Database Writer Process

  • schreibt asynchron aus der SGA in die db files (daten, index, undo, ...)
    • bei jedem checkpoint
    • wenn es zu wenig freie buffer hat
    • gesteuert durch init.ora
FAST_START_MTTR_TARGET
  • unterhält zwei buffer listen
    • LRU List: last recently used buffers
    • dirty list: liste der veränderten blöcke
  • ist in der lage "multi block I/O" auszuführen

CHECKPOINT PROCESS

  • schreibt timestamps in die header der datenfiles und in die control files
  • werden druchgeführt wenn:
    • ein redo log voll ist
    • wenn die anzahl LOG_CHECKPOINT_INTERVAL in ein redo log file geschrieben wurde
    • wenn LOG_CHECKPOINT_TIMEOUT sekunden verstrichen sind
    • ein checkpoint manuel ausgeführt wird:
SQL> ALTER SYSTEM SET CHECKPOINT ;
SQL> ALTER SYSTEM SET CHECKPOINT LOCAL ; #RAC
SQL> ALTER SYSTEM SET CHECKPOINT GLOBAL ; #RAC, alle noden
  • checkpoints können auf wunsch ins alert.log geloggt werden
LOG_CHECKPOINTS_TO_ALERT = TRUE
SQL> SELECT NAME, VALUE FROM v$sysstat WHERE NAME = 'DBWR checkpoints' ;
SQL> SELECT NAME, CHECKPOINT_CHANGE# FROM v$datafile ;

ARCHIVER PROCESS

  • schreibt online redo logs auf eine oder mehrere destinationen
    • aktiv nur im archive mode
    • schreibt nach einem log switch
    • max 10 destinationen

Hinweis:

SQL> show parameter LOG_ARCHIVE
SQL> archive log list
SQL> ATLER SYSTEM SET ARCHIVE_LAG_TARGET = 900 ; -- log switch nach dieser zeit erzwingen

JOB QUEUE PROCESSES

  • user batch prozesse
  • refresh von materialized views
  • replication etc.
  • alles was PL/SQL machen kann

TABLE SPACES + DATA FILES + LOG FILES

DATEN FILES

  • maximale grösse eines daten files ist 32GB
Tablespaces und Data files auflisten
SQL> SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME FROM dba_data_files ORDER BY TABLESPACE_NAME ;
I/O belastung der daten files anzeigen
SQL> SELECT * FROM v$filestat;

TABLE SPACES

  • dictionary managed tablespaces (DMTS)
    • freie/belegte extents werden im datadictionary verwaltet
    • alte methode, sollte nicht mehr verwendet werden
  • locally managed tablespaces (LMTS)
    • freie/belegte extents werden in einer bitmap im fileheader verwaltet
    • einfachere administration
    • fragemtierung weitgehend unproblematisch
    • DDL-operationen sind schneller als DMTS
  • seit 9i wird praktisch nur noch LMTS verwendet

Tablespace Auslastung anzeigen

set linesize 999
set pagesize 999
select
(  select
   decode(extent_management,'LOCAL','*',' ') ||
   decode(segment_space_management,'AUTO','a ','m ')
from
   dba_tablespaces
where
   tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
   nvl(a.tablespace_name,'UNKOWN')) name,
   MB_Allocated MB_Allocated,
   MB_Allocated-nvl(Free_MB,0) used,
   nvl(Free_MB,0) free,
   (  (MB_Allocated-nvl(Free_MB,0))/
       nvl(Max_MB,MB_Allocated))*100 pct_used,
       nvl(Max_MB,MB_Allocated) Max_Size,
       decode( Max_MB, 0, 0, (MB_Allocated/Max_MB)*100) pct_max_used
       from ( select sum(bytes)/1048576 Free_MB,
       max(bytes)/1048576 largest,
       tablespace_name
     from
        sys.dba_free_space
     group by
        tablespace_name ) a,
   ( select
        sum(bytes)/1048576 MB_Allocated,
        sum(maxbytes)/1048576 Max_MB,
        tablespace_name
     from
        sys.dba_data_files
     group by
        tablespace_name
union all
select
   sum(bytes)/1048576 MB_Allocated,
   sum(maxbytes)/1048576 Max_MB,
    tablespace_name
from
   sys.dba_temp_files
group by
   tablespace_name )b
where
a.tablespace_name (+) = b.tablespace_name
order by 1;

Tablespace anlegen (feste extent grösse)

  • jeder extent hat eine grösse von 4MB (default 1MB)
  • jeder extent belegt 1bit im header des datenfiles
SQL> CREATE TABLESPACE app_data
    DATAFILE '/u01/.../app_data01.dbf' SIZE 10G
    AUTOEXTEND ON MAXSIZE 20G
    BLOCKSIZE 16384
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4096K ;

Tablespace anlegen (autoallocate)

  • extents von mindestens 64k werden alloziert
64K, 1024K, 8M, 64M
  • initial extent kann spezifiziert werden
  • AUTOALLOCATE ist default bei DMTS
SQL> CREATE TABLESPACE app_logs
    DATAFILE '/u01/.../app_logs01.dbf' SIZE 1G
    AUTOEXTEND ON MAXSIZE 5G
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;
SQL> CREATE TABLESPACE data_archive
    DATAFILE '/u01/.../data_archive01.dbf' SIZE 5G ;

Tablespace vergrössern/verkleinern

SQL> ALTER DATABASE DATFILE '/u01/.../data_archive01.dbf' RESIZE 2G ;

Tablespace droppen

SQL> DROP TABLESPACE app_logs ;
SQL> DROP TABLESPACE app_logs INCLUDING CONTENTS ;
SQL> DROP TABLESPACE app_logs INCLUDING CONTENTS AND DATAFILES;

Tablespace online/offline nehmen

SQL> ALTER TABLESPACE users OFFLINE NORMAL|TEMPORARY|IMMEDIATE ;
SQL> ALTER TABLESPACE users ONLINE ;

Rename Datafile

SQL> ALTER TABLESPACE data OFFLINE ;
SQL> ALTER TABLESPACE data 
    RENAME DATAFILE '/u01/.../data.dbf'
    TO '/u01/.../data01.dbf' ;
#> mv '/u01/.../data.dbf' '/u01/.../data01.dbf'
SQL> ALTER TABLESPACE data ONLINE ;

Rename Tablespace

SQL> ALTER TABLESPACE users RENAME TO userdata ;

Löchen eines leeren Datafile

SQL> ALTER TABLESPACE test_ts DROP DATAFILE 8 ;

Tablespace Allocation Typ anzeigen

SQL> DESC dba_tablespaces ;
SQL> SELECT tablespace_name, extent_management, allocation_type FROM dba_tablespaces ;

Temp Tablespace

SQL> ALTER TABLESPACE temp
    ADD TEMPFILE '/u01/.../temp01.dbf' SIZE 1G ;
SQL> SELECT * FROM v$tempfile ;
SQL> SELECT property_value
    FROM database_properties
    WHERE property_name = 'DEFAULT_TEMP_TABLESPACE' ;
  • temp tablespace kann auch als TABLESPACE GROUP mit mehreren temp files definiert werden
    • user können dann an diese gruppe gebunden werden
    • oracle entscheidet dann die optimale auslastung der files
SQL> SELECT * FROM dba_tablespace_groups ;

UNDO TABLE SPACES

  • ermöglicht die lese konsistenz ein einem multi user system
  • ermöglicht undo/rollback uncomitteter transaktionen
  • 2 arten werden unterstützt
    • automatisches undo management (AUM ist default ab 11g)
    • manuelles undo management (rollback segmente)
  • es ist möglich undo tablespace nachträglich zu erweitern
 SQL> CREATE UNDO TABLESPACE undotbs2
      DATAFILE '/u01/.../undotbs2_01.dbf'
      SIZE 256M ;
SQL> show parameter
SQL> SELECT status, count(*) FROM dba_undo_extents
    WHERE tablespace_name = 'UNDOTBS'
    GROUP BY status ;
SQL> ALTER SYSTEM SET undo_management=AUTO|MANUAL
SQL> ALTER SYSTEM SET undo_retention=0 ; # automatisches management aktivieren
SQL> ALTER SYSTEM SET undo_retention=900 (empfehlung: 3600 oder mehr)
SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS
SQL> VIEW v$undostat
SQL> ALTER TABLESPACE undotbs RETENTION GUARANTEE ;
  • unexpired blocks von lese operationen werden nicht von

schreib operationen überschrieben, schreib operation bricht ab: ORA-01555

SQL> ALTER TABLESPACE undotbs RETENTION NOGUARANTEE ;
  • unexpired blocks von leseoptionen werden von


schreib operationen überschrieben, lese operation bricht ab: ORA-01555

SQL> SELECT begin_time, tuned_undoretention, maxquerylen, unxpstealcnt
    FROM v$undostat
    ORDER BY begin_time ;

ONLINE REDO LOGS

  • redo log gruppe kann aus mehreren membern bestehen
  • die redo log gruppen werden fortlaufend numeriert
  • solange mindestens 1 member jeder gruppe verfügbar ist, kann die db betrieben werden
  • alle redo log groups sollten gleich viele members haben
  • redo logs können online verändert werden
show redo logs and groups
SQL>select lgf.group#, lgf.member, lg.status from v$logfile lgf
   inner join v$log lg on lg.group# = lgf.group# order by group#;
redo log switch, logfile archivieren
SQL> alter system switch logfile;
infos anzeigen
SQL> select * from v$logfile;
SQL> select * from v$log;
SQL> select * from v$loghist;
redo log handling
SQL> alter database add logfile member '/opt/u2/.../redo01.log' to group 1;
SQL> alter database drop logfile member '/opt/u2/.../redo02.log';
SQL> alter database drop logfile group 3;
SQL> alter system switch logfile;
$> rm -f /opt/u1/oradata/tcDTA01/redo03.log
datenbank in no/archive log modus nehmen
SQL> startup mount
SQL> alter database archivelog
SQL> alter database noarchivelog
SQL> alter database open
noglog for single comands
SQL> CREATE TABLE company_customers AS SELECT * FROM customers
    WHERE customer_type = 1 NOLOGGING ;
SQL> CREATE INDEX comp_cust_log ON company_customers(loc) NOLOGGING;

OFFLINE REDO LOG HANDLING (ARCHIVE LOGS)

  • wenn der archivelog eingeschaltet ist
  • online redo logs werden beim rotieren als archive logs abgelegt
  • können bei einem backup via rman automatisch gelöscht werden
  • sie dienen dazu die datenbank zu einem bestimmten zeitpunkt zu restoren (rman)

archive log handling

SQL> ALTER SYSTEM SET log_archive_dest_n = "LOCATION=/opt/u2/archlog/APP01" SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest = "/opt/u2/archlog/APP01" SCOPE=BOTH;
SQL> select dest_name, destination from v$archive_dest;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;

die datenbank kann für wartungs/migrations arbeiten im NOARCHIVELOG modus
betrieben werden:

SQL> alter database mount;                    # from nomount to mount
SQL> alter database noarchivelog;             # disable archive logs
SQL> alter database open;                     # open for normal operation
SQL> select log_mode from v$database;         # show current log mode
SQL> show parameter log_archive_start         # change it in init.ora 

CONTROL FILES

  • sind notwendig um eine instanz zustarten
  • inhalt
    • datenbank name
    • namen und pfade aller daten und redo log files
    • aktuelle log sequenz nummer
    • checkpoint informationen
    • on/offline status der einzelnen files
    • startup/shutdown informationen
    • backup informationen falls mit rman gesichert wird
  • grösse der control files ist unterschiedlich
  • wie lange zurück die infos gehalten werden
CONTROL_FILE_RECORD_KEEP_TIME=n ( 0 - 365 Tage, default 7)
  • position der files
 SQL> SELECT * FROM v$controlfile;
  • inhalt des files anzeigen
 SQL> SELECT * FROM v$controlfile_record_section
      WHERE TYPE LIKE '%BACKUP%' ;
  • control files werden beim create database erstellt
  • diese infos werden auch im inti.ora abgelegt
  • zusätzlicher spiegel kann erstellt werden wenn die db offline ist

ein lesbarer export des control files kann wie folgt erstellt werden:

SQL> alter database backup controlfile to trace as '/tmp/db-info.trc';

TRACE UND ALERT LOGs

  • jede instanz verfügt über ein alert.log file
  • dies ist zentrales logfile
  • jeder oracle hintergrund prozess kann trace files erzeugen
  • file destinationen
    • $ORACLE_BASE/admin/$ORACLE_SID/
      • bdump -> trace file für hintergrund prozesse und alert.log
      • udump -> trace files von userprozessen und manuellem tracing
      • cdump -> core dumps

mail alertlog script

http://www.bitbull.ch/dl/scripts/oracle-check-alertlog.sh


ADR - Advanced Diagnostic Repository (ab 11g)

  • zentralisiertes verzeichnis und repo für trace, dump und log dateien
    • einheitliches daten format
    • produkt und instanz übergreifend
  • neue file typen
    • incident packages
    • SQL test cases
    • data repair records
  • zugriff auf adr files
    • comand line interface (adrci)
    • EM grid control
  • destinations
    • ADR Base
      • init.ora: diagnostic_dest=/u00/app/oracle
    • ADR Home
      • ADR_HOME=$diagnostic_dest/diag/$prod_type/$prod_id/$instance_id
  • comands
SQL> select * from v$diag
adrci> show homes
  • verzeichnis struktur
    • instance_id -> instance id (ADR Home)
      • alert
      • cdump
      • incident
      • trace
      • ...

DATA DICTONARY

  • besteht sogenannten basistabellen und darauf basierten views (ro)
  • der DD gehört dem schema SYS und enthält informationen über alle daten objekte
  • der DD wird automatisch durch die entsprechenden befehle nachgeführt:
    • DDL - data definition language
    • DCL - data control language
    • DML - data manipulation language
  • manuelles nachführen kann zu inkonsistenzen führen
  • es gibt grundsätzlich drei schichten
    • USER_*
      • zB: USER_TABLES
      • sicht auf diejenigen objekte, die ein user besitzt
    • ALL_*
      • zB: ALL_TABLES
      • sicht auf diejenigen objekte, die ein user besitzt und diejenigen objekte auf die er berechtigt wurde
    • DBA_*
      • zB: DBA_TABLES
      • sicht auf alle datenobjekte aller user (incl. SYS)
  • inistialisierung erfolgt beim erstellen der db (sql.bsq)
  • zusätzliche tabellen und views werden beim erstellen des catalog erstellt (catalog.sql)
  • catproc.sql wird ebenfalls benötigt
    • ruft zahlreiche weitere scripts auf
    • installiert PL/SQL und oracle standard packages
  • options spezifische komponenten finden ebenfalls im DD platz

inhalt des DD

    • datenbank objekte (tabellen, partitionen, views, objects, ...)
    • stored objects (trigger, procedures, ...)
    • benutzer, rollen, rechte, quotas, defaults
    • zugriffs berechtigungen, policies, ...
    • informationen zur physischen platz allozierung
    • constraints, ...
    • statistiken, zu
    • audit infos
    • ...

WICHTIGE VIEWS UND TABELLEN

v$transaction -- laufende transaktionen
v$waitstat -- wait statistiken
v$sysstat -- system statistiken
v$sesstat -- session statistiken
v$sqlarea -- session informationen
dba_feature_usage_statistics -- wann wurden welche features verwendet
dba_high_water_mark_statistics -- datenbank grösse und kennzalen

anzeige aller verfügbaren x$, v$, gv$ tabellen

SQL> SELECT * FROM v$fixed_table;
SQL> SELECT * FROM v$fixed_view_definition;

alle tabellen des catalog

SQL> SELECT * FROM cat ;

TIMED_STATISTICS

  • init.ora
TIMED_STATISTICS -- sollte auf TRUE gesetzt werden
  • kann online gesetzt werden
  • dann werden zeitangaben in den v$-fixed-tables angezeigt
SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE ;

INSTALLATION UND DB CONFIGURATION

Linux

Windows

  • mir doch egal

ORACLE - COMAND LINE TOOLS

adrci - automated diagnostic repository comand interpreter (ab 11g)

dbca - database configuration assistant

dbua - database upgrade assistant

exp, imp - export/import utility (end of life)

expdp,impdp - data pump export/import

export db example:

$ mkdir /opt/u4/dpexp
$ sqlplus / as sysdba
SQL> create or replace directory expdp_dir as '/opt/u4/dpexp';
SQL>  grant read,write on directory expdp_dir to app;
$ expdp 'app'/'oo7!' full=n directory=expdp_dir dumpfile=APP_ora001.dmp logfile=exp_APP_ora001.log SCHEMAS=APP [CONTENT=METADATA_ONLY]
$ expdp system/'oo7!' schemas=app25 DIRECTORY=expdp_dir  EXCLUDE=STATISTICS  dumpfile=$ORACLE_SID-$(date +%d%m%y_%H%M%S).dmp \
  logfile=$ORACLE_SID-$(date +%d%m%y_%H%M%S).log flashback_time=\"TO_TIMESTAMP\(\'29-07-2009 12:05:01\', \'DD-MM-YYYY HH24:MI:SS\'\)\"

import db example:

$ mkdir /opt/u4/dpexp
$ sqlplus / as sysdba
SQL> create or replace directory expdp_dir as '/opt/u4/dpexp';
SQL>  grant read,write on directory expdp_dir to app;
$ impdp app/'oo7!' DIRECTORY=expdp_dir DUMPFILE=APP_ora001.dmp SCHEMAS=DTA LOGFILE=imp_APP_ora001.log TABLE_EXISTS_ACTION=REPLACE
$ impdp system/'oo7!' REMAP_SCHEMA=app25:app35 remap_tablespace=app25:app35 dumpfile=expdp_dir:app25.160908_141813.dmp logfile=expdp_dir:app35_dump_import.log

lsnrctl - listener control

nid - tool for setting identifier for a database

orapwd - tool for creating and managing password files

rman - recovery manager (backup/restore)

sqlldr - sql loader used to load files from the filesystem into the database and by any tables

sqlplus - comand line sqlplus user interface

tnsping - ping connections via sql-net

RMAN - Recovery Manager

  • archive logs sind eine voraussetzung für rman

RMAN Comands

connect to rman
$> rman target=sys/pwd@ORA-SID [nocatalog] [catalog=usr/pwd@DBCAT]
$> rman target / nocatalog #catalog infos werden im controlfile abgelegt

alle parameter anzeigen

RMAN> show all

control file soll mitgesichert werden

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

stellt sicher das backup daten für die letzten 7 tage verfügbar bleiben

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

files löschen, die gemäss policy nicht mehr beötigt werden

RMAN> DELETE OBSOLETE

backup destination festlegen

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora/full/sid/%U';

backup ausführen

RMAN> backup database plus archivelog delete input;
  • datenbank backup
  • archive log backup
  • gesicherte archive logs löschen

verfügbare backups anzeigen

RMAN> list backupset of database;

prüfen ob restore möglich ist

RMAN> RESTORE DATABASE VALIDATE;

restore zum letztmöglichen zeitpunkt

RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

archive logs loeschen

RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-14';

RMAN DB CATALOG

  • es ist empfehlenswert in grösseren umgebungen mit einem RMAN-DB-Catalog zu arbeiten
    • eigene DB mit RMAN catalog daten (backup logbuch, control file)
    • ermöglicht "point in time tablespace recovery"
    • ab 10 datenbanken ist ein RMAN-DB-Catalog empfehlenswert
    • zentrales backup reporting der backup daten möglich
    • ein performance problem am catalog kann backups verzögern

DB CLONEN MIT RMAN

  • via EM im webinterface
  • via comand line: Google: rman clone database

FRA - FLASH RECOVERY AREA

  • verzeichnis, filesystem, disk gruppe des ASM
  • speicherort der sich durch das automatische löschen von nicht mehr benötigten files auszeichnet
  • folgende recovery bezogenen filetypen können in der FRA gespeichert und verwaltet werden
    • current control file
    • online logs
    • archived logs
    • flaschback logs
    • control file auto backups
    • control file copies
    • datafile copies
    • backup pieces
  • FRA kann von mehreren datenbanken genutzt werden
  • dynamische initialisierungs parameter
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 50G SCOPE=BOTH ;
SQL> ALTER SYSTEM SET db_recovery_file_dest = "/opt/u4/oradata/flash_recovery_area" SCOPE=BOTH ;
SQL> ALTER SYSTEM SET db_recovery_file_dest = " " SCOPE=BOTH ; #disable FRA
SQL> SELECT * FROM v$flash_recovery_area_usage ;

DATABASE STATUS / OPERATION HANDLING

startup DB

SQL> startup nomount     # start only env (for sga modifikations)
SQL> startup mount       # no client connections allowed (eg restore)
SQL> startup normal      # normal start
SQL> startup force       # shutdown abort, startup normal
SQL> startup read only   # guess
SQL> startup restrict    # same as startup, but no users allowed (eg maintenance)
$> emctl start dbconsole # start EM
$> lsnrctl start         # start listener

shutdown DB

SQL> shutdown normal        # no new sessions, wait for active sessions to close db
SQL> shutdown transactional # no new sessions, wait for active transactions to close db
SQL> shutdown immediate     # drop active transactions
SQL> shutdown abort         # kill it

change DB status

SQL> alter database open                      # start up for normal operation
SQL> alter database nomount                   # only env loaded
SQL> alter database mount                     # from nomount to mount
SQL> alter system enabled restricted session  # restricted mode, hold running sessions
SQL> alter system disable restricted session  # close restricted mode

KILL SESSION

SQL> SELECT user, sid, serial#, logon_time from v$session;
SQL> ALTER SYSTEM KILL SESSION 'sid, serial#' ;

ORACLE NET

  • regelt die auflösung des connect identifier
    • file basierte auflösung (tnsnames.ora)
    • directory naming (zentraler directory server)
    • easy connect naming (auflösung im connect string)
$> sqlplus usr/pwd@DB1
           |   |   |
           |   |   tnsnames.ora
           |   passwort
           username

TNSNAMES.ORA ($TNS_ADMIN)

  • definition des connect string
    • hostname
    • SID
    • protocol
    • port
  • WO
    • client
    • server
  • 1x pro client
Example
ZEUS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xen4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ZEUS)
    )
  )

SQLNET.ORA ($TNS_ADMIN)

  • auf dem client
  • auf dem server
  • WOZU
    • default domain
    • dead client detection
    • tracing
    • logging
    • dedicated name directory
Example
names.default_domain = com
names.initial_retry_timeout = 30
names.max_open_connections = 3
names.message_pool_start_size = 10
names.directory_path = (tnsnames, onames, hostname)
namesctl.trace_directory = /oracle/network/trace

LISTENER.ORA ($TNS_ADMIN)

  • auf dem server
  • WOZU
    • konfiguration listener
    • konfiguration services
  • 1x pro node
Example
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ZEUS)
      (ORACLE_HOME = /opt/u00/app/oracle/product/10.2.0/db1)
      (SID_NAME = ZEUS)
    )
  )


USER AND SECURITY

USER

  • fest zugeordnet
    • eindeutiger name (schema = user + objects)
    • passwort
      • gültigkeits dauer
      • validieren eines passworts
      • lock/unlock account
      • kann auch via OS authentisiert werden
    • default tablespace
    • wenn möglich mit quota einschränken
    • temp table space
  • optional
    • quotas auf tablespace vergeben
    • user mit einem profil versehen
    • objekt und system privilegien erteilen
    • rollen zuweisen
      • keine dba rolle an app, poweruser vergeben
      • end-user nicht als sammel-user erstellen


Example
  • erstellen
SQL> CREATE USER scott IDENTIFIED BY tiger
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users
    QUOTA 1M ON test
    PROFILE end_user;
  • modifizieren
SQL> SELECT username, password FROM dba_users;
SQL> ALTER USER scott IDENTIFIED BY tigerli;
SQL> ALTER USER scott IDENTIFIED BY VALUES 'D2F4G5S2D5D2A2' ;
SQL> ALTER USER scott IDENTIFIED BY tigerli;
SQL> ALTER USER scott QUOTA 0 SYSTEM;
SQL> ALTER USER scott PROFILE pro_query_tool_end_user;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp1;
SQL> ALTER USER scott DEFAULT TABLESPACE test;
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;
SQL> ALTER SYSTEM SET remote_os_authent = FALSE ; -- remote nur mit password
SQL> ALTER SYSTEM SET sec_case_sensitive_logon = TRUE|FALSE ; -- ab 11g, passwort/user handling
SQL> SELECT * FROM dba_users_with_defpwd ; -- ab 11g, wurde das pw nach dem anlegen geändert?
  • löschen:
SQL> DROP USER "scott";
SQL> DROP USER "scott" cascade;
DD Views for users
USER_USERS
DBA_USER
USER_TS_QUOTAS
DBA_TS_QUOTAS

PROFILES

  • - werden direkt auf benutzerebene zugeordnet
  • - passwörter verwalten
    • - minimale passwort länge
    • - erzwingen von strings, zahlen, spezialzeichen
    • - verbieten von gebräuchlichen passworten
    • - passwort history
    • - account lockout, wenn nicht im geforderten zeitraum geändert
    • - warnung wenn grace time zum ändern erreicht
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
SQL> CREATE PROFILE prf_pwd_check LIMIT
     FAILED_LOGIN_ATTEMPS 5
     PASSWORD_LIFE_TIME 30
     PASSWORD_VERIFICATION_FUNCTION fkt_pwd_ckeck
     PASSWORD_LOCK_TIME UNLIMITED
     PASSWORD_GRACE_TIME 7;
SQL> ALTER USER scott ACCOUNT LOCK;
SQL> ALTER USER scott ACCOUNT UNLOCK;
SQL> ALTER USER sctoot PROFILE prf_pwd_che
SQL>
  • ressourcen verwalten
    • init.ora: RESSOURCE_LIMIT = TRUE (default = FALSE)
      • dynamischer parameter
SQL> ALTER SYSTEM SET RESSOURCE_LIMIT=TRUE ;
Example
SQL> CREATE PROFILE pro_query_tool_end_user LIMIT
    SESSION_PER_USER 2
    CONNECT_TIME 500 /* minutes */
    IDLE_TIME 30
    LOGICAL_READS_PER_SESSION 100000 /* blocks */
    PRIVATE_SGA 1000000 ; /* bytes */
SQL> ALTER PROFILE ...
SQL> SELECT distinct, profile, ressource_type from dba_profiles;

SYSTEM UND OBJEKT PRIVILEGIEN

SYSTEM PRIVILEGIEN

  • was darf in der datenbank gemacht werden
  • es gibt über 160 system privilegien
  • es ist sinvoll privilegien an rollen zu vergeben
  • ein user braucht mindestens CREATE SESSION rechte
Example
SQL> GRANT CREATE TABLE, CREATE ANY TABLE, SELECT ANY TABLE, CREATE TRIGGER, 
    ALTER ANY TRIGGER, BECOME USER, CREATE SESSION TO scott;
SQL> GRANT CREATE SESSION TO rol_query_tool_end_user;
SQL> GRANT ALL PRIVILEGES TO jones;
SQL> REVOKE CREATE SESSION FROM scott;
SQL> REVOKE BECOME USER FROM jones;

OBJEKT PRIVILEGIEN

  • objekte
    • tabellen
    • views
    • types
    • sequences
    • stored objects
    • directories
    • libraries
    • materialized views
  • privilegien
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • ALTER
    • REFERENCES
    • INDEX
    • ALL
    • QUERY REWRITE
    • ON COMMIT REFRESH
    • DEBUG
    • FLASHBACK
  • funktionen laufen per default mit den rechten des besitzers ab (AUTHID DEFINER)
  • funktionen können explizit mit den rechten des ausführenden gestartet werden (AUTHID CURRENT_USER)
Example
SQL> GRANT SELECT, INSERT, UPDATE(ENAME,JOB) ON emp TO adams;
SQL> GRANT ALL ON dept TO PUBLIC;
SQL> REVOKE ALL ON emp TO addams;
SQL> GRANT EXECUTE on proc1 TO huber;
synonyme
SQL> CREATE SYNONYM angestellte FOR scott.emp;
SQL> CREATE PUBLIC SYNONYM dual for sys.dual;

ROLLEN

  • rollen können beliebig verschachtelt werden
  • CREATE PROCEDURE, CREATE VIEW funktioniert nicht auf berechtigungen die via rollen vergeben wurden
  • rollen konzept
    • application privileges (app_shop, app_finance)
      • application roles (accts_getstat, accts_addcutomer)
        • user roles (usr_app_storno, user_callcenter)
          • users (tom, joe)
rollen definieren
SQL> CREATE ROLE rol_pis_abfrage ;
SQL> CREATE ROLE rol_dba IDENTIFIED BY manager;
SQL> CREATE ROLE rol_mis_end_user IDENTIFIED EXTERNALY;
rollen berechtigen
SQL> GRANT SELECT ON emp TO rol_pis_abfrage;
SQL> GRANT CREATE SESSION TO rol_sys_end_user;
SQL> GRANT rol_sys_end_user TO rol_pis_abfrage;
SQL> GRANT BECOME USER TO rol_dba WITH ADMIN OPTION ;
rollen an user berechtigen
SQL> GRANT rol_pis_abfrage TO huber;
SQL> GRANT rol_dba TO jones;
SQL> GRANT rol_alle_pub_tabellen TO PUBLIC;
rollen entziehen
SQL> REVOKE rol_dba FROM jones;
SQL> REVOKE rol_sys_end_user FROM rol_pis_abfrage;
default rollen zuordnen
SQL> ALTER USER huber DEFAULT ROLE rol_dba;
rolle setzen (nicht default rollen)
SQL> SET ROLE rol_progr, rol_dba;
SQL> SET ROLE ALL;
SQL> SET ROLE NONE;
SQL> SET ROLE ALL EXCEPT rol_dba;

DIVERSES

AUDITING

  • loggt verändernde sql comands
  • ab 11g standardmässig aktiv
SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
  • none or false - Auditing is disabled.
  • db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
  • db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
  • xml- Auditing is enabled, with all audit records stored as XML format OS files.
  • xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
  • os- Auditing is enabled, with all audit records directed to the operating system's audit trail.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE ;
SQL> SELECT view_name
    FROM   dba_views
    WHERE  view_name LIKE 'DBA%AUDIT%'
    ORDER BY view_name;
SQL> AUDIT ALL BY audit_test BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

REORGANISATION VON DATEN

GRUENDE

  • heruntersetzen der high water mark (HWM) nach löschaktionen
  • eliminieren von block chaining
  • eliminieren von row chaining
  • ändern der block parameter PCTFREE und INITRANS (dbms_space.sql)
  • verschieben von segmenten in einen anderen tablespace
  • daten nach bestimmten kriterien sortiert ablegen

METHODEN

Siehe auch Oracle Whitepaper
Oracle Database 10g Release 2 Online Data Reorganisation & Redefinition

MOVE TABLE

Indizes werden UNUSABLE und müssen mit REBUILD neu aufgebaut werden

SQL> ALTER TABLE dept
    MOVE TABLESPACE users
    STORAGE (INITIAL 256K NEXT 256K) 
    PCTFREE 0 ; 
SQL> ALTER INDEX pk_dept REBUILD

ONLINE SEGMENT SHRINK

  • das segment muss in einem Automatic Segment Space Management (ASSM) liegen (default ab 10g)
  • init.ora -> COMPATIBLE >= 10.0
  • row movement auf der tabelle muss aktiviert werden
  • nur ganz kurzer lock am ende der operation
  • gibt platz vor und nach der high watermark frei
  • benötigt keinen zusätzlichen platz während der reorganisation
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT; -- komprimierung
SQL> ALTER TABLE scott.emp SHRINK SPACE; -- high water mark zurücksetzen
Examples

enable row movement (voraussetzung)

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

shrink space inklusive HWM anpassung

SQL> ALTER TABLE scott.emp SHRINK SPACE;

shrink space ohne HWM (gibt keinen lock)

SQL> ALTER TABLE scott.emp SHRINK SPACE;

shrink space inklusive index

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

shrink von LOB segmenten (ab 10gR2)

SQL> ALTER TABLE scott.emp lob_tab MODIFY LOB (picture) SHRINK SPACE ;

REBUILD INDEX

für fragmentierte indexes

benötigt doppelten platz im tablespace
SQL> ALTER INDEX emp_deptno REBUILD 
    PCTFREE 5 
    TABLESPACE pis_i;
benötigt keinen platz im tablespace
SQL> DROP INDEX ...
SQL> CREATE INDEX ...
alle indexe im schema neu aufbauen
SQL > set heading off
SQL > spool s.sql
SELECT ' ALTER INDEX ' || INDEX_NAME || ' REBUILD ; '
FROM USER_INDEXES ;
SQL > spool off
SQL >@s.sql

CREATE TABLE AS SELECT

  • momentan sortierte ablegung der daten
  • kann applikatorisch grossen gewinn bringen
SQL> CREATE TABLE emp_neu
    AS SELECT * FROM emp ORDER BY deptno;

DROP COLUMN

  • löschen einer column aus einer tabelle
  • kann viel I/O verursachen
varianten
  • column direkt löschen
  • column auf unused setzen
    • unused columns nachträglich löschen
Examples
SQL> ALTER TABLE emp DROP COLUMN job;
SQL> ALTER TABLE emp SET UNUSED COLUMN mgr;
SQL> ALTER TABLE emp DROP UNUSED COLUMNS;
SQL> ALTER TABLE DROP COLUMN deptno CASCADE CONSTRAINTS;
SQL> ALTER TABLE big_emp DROP COLUMN comm 
    CHECKPOINT 10000;


INDEX

SORTED B*TREE INDEX

  • besteht aus zwei teilen
    • baum strukturierterindex zu den sequenzblocks
    • sequenz satz ist eine sortierte liste aller schlüsselwerte
Examples
SQL> CREATE INDEX sh.products_prod_cat_ix ON
    ON sh.products(prod_category ASC)
    STORAGE (INITIAL 128K MINEXTENTS 1)
    PCTFREE 10
    INITRANS 2
    TABLESPACE example;
SQL> ALTER INDEX emp_ind
    INITRANS 5;
SQL> DROP INDEX emp_ind;
SQL> ALTER INDEX sys_c00754 RENAME TO pk_dept_ind;
SQL> ALTER INDEX name_i MONITORING USAGE;
SQL> SELECT * FROM V$OBJECT_USAGE;
SQL> ANALYZE INDEX name_i VALIDATE STRUCTURE; -- alt
SQL> @lsindsta.sql -- index analysen durchführen

OPTIMIZER NOTES

  • init.ora
    • OPTIMIZER_MODE
    • OPTIMIZER_FEATURES_ENABLED
    • DB_FILE_MULTIBLOCK_READ_COUNT
    • OPTIMIZER_DYNAMIC_SAMPLING
    • PGA_AGREGATE_TARGET
prüfen wann die statistiken für ein objekt gerechnet wurden
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS';
SQL> SELECT TABLESPACE_NAME, TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES ORDER BY LAST_ANALYZED;
statistik rechnen lassen
SQL> begin
    dbms_stats.gather_table_stats(
    ownname=> 'DTA',
    tabname=> 'EVENTS' ,
    estimate_percent=> 10,
    cascade=> TRUE,
    degree=> null,
    no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
    granularity=> 'AUTO',
    method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY');
    end;


HINTS ON QUERYS

erlauben den durch den optimizer generierten execution plan
zu beeinflussen hints können einfluss nehmen auf

  • optimizer mode
  • zugriffs pfade
  • join orders und join operationen
Example
SQL> SELECT /*+ INDEX(i_employees)*/ first_name, last_name
    FROM employees
    WHERE department_id = 10 ;

TRACE

um einen tieferen einblick in eine applikation zu bekommen
können sessions getraced werden, diese files geben aufschluss
über das verhalten und performance probleme.

TRACE ERSTELLEN

Beispiel
SQL> create or replace trigger logon_trigger
     after logon on database
     begin
        if (user = 'SYNEDRA' ) then
           execute immediate 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=''UNLIMITED''';
           execute immediate 'ALTER SESSION SET STATISTICS_LEVEL=''ALL''';
           execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SYNEDRA''';
           execute immediate 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
        end if;
     end;
 /

TRACE AUSWERTEN

das generierte trace file kann nun wie folgt ausgewertet werden
$> tkprof /opt/.../udump/db_2220.trc  /opt/.../udump/db_2220.txt explain=hr/hr sys=no waits=yes sort=(prsela,exeela,fchela)

oder

  • das tool tvd$xtat von antognini

sqlplus history and syntax highlighting

install wrapper

yum install rlwrap

configure the env

 vi .bashrc
 ---
 export RLWRAP_EDITOR="vim +%L -c 'syntax on' -c 'set filetype=sql'"
 alias sysdba='rlwrap -m -s5000 sqlplus / as sysdba'
 --

now you have history and comand search function (ctrl + R)
and great multiline sql comand editor with highlighting (ctrl + ^)