Oracle Notes
Contents
- 1 BASICS
- 2 STATIC CONFIGURATION PARAMETER
- 3 MEMORY ARCHITEKTUR
- 4 PROZESS ARCHITECTURE
- 5 TABLE SPACES + DATA FILES + LOG FILES
- 5.1 DATEN FILES
- 5.2 TABLE SPACES
- 5.2.1 Tablespace Auslastung anzeigen
- 5.2.2 Tablespace anlegen (feste extent grösse)
- 5.2.3 Tablespace anlegen (autoallocate)
- 5.2.4 Tablespace vergrössern/verkleinern
- 5.2.5 Tablespace droppen
- 5.2.6 Tablespace online/offline nehmen
- 5.2.7 Rename Datafile
- 5.2.8 Rename Tablespace
- 5.2.9 Löchen eines leeren Datafile
- 5.2.10 Tablespace Allocation Typ anzeigen
- 5.3 Temp Tablespace
- 5.4 UNDO TABLE SPACES
- 5.5 ONLINE REDO LOGS
- 5.6 OFFLINE REDO LOG HANDLING (ARCHIVE LOGS)
- 5.7 CONTROL FILES
- 5.8 TRACE UND ALERT LOGs
- 5.9 ADR - Advanced Diagnostic Repository (ab 11g)
- 6 DATA DICTONARY
- 7 INSTALLATION UND DB CONFIGURATION
- 8 RMAN - Recovery Manager
- 9 DATABASE STATUS / OPERATION HANDLING
- 10 ORACLE NET
- 11 USER AND SECURITY
- 12 DIVERSES
- 13 REORGANISATION VON DATEN
- 14 INDEX
- 15 OPTIMIZER NOTES
- 16 TRACE
1 BASICS
1.1 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
1.2 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
1.3 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
1.4 Architektur Uebersicht
1.4.1 SGA (System Global Area)
- Shared Pool
- Library Cache
- Data Dictonary Cache
- Database Buffer Cache
- Redo Log Buffer
1.4.2 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])
1.4.3 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)
- SCN (System Change Nummer)
- Check Point lösst keinen Log Switch aus
- Check Points können auch auf Basis Zeit, Volumen getriggert werden
SQL> SELECT * FROM V$LOGHIST;
2 STATIC CONFIGURATION PARAMETER
2.1 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???
2.2 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
2.3 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 ...
3 MEMORY ARCHITEKTUR
- 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
3.2 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
- Library Cache
- Redo Log Buffer (S)
SGA_MAX_SIZE
- maximale grösse der sga
SGA_TARGET $
- summe aller SGA komponenten
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.
3.2.2 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
- DEFAULT Pool
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;
3.2.3 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)
3.2.4 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%';
3.2.5 JAVA POOL
falls in der db mit java gearbeitet wird
SQL> select * from v$sgastat where pool like '%java%';
3.2.6 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
3.3 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
4 PROZESS ARCHITECTURE
Laufende Prozesse und beschreibungen anzeigen:
SQL> select * from v$bgprocess where pserial# = '1';
4.1 SMON - System Monitor
- instance recovery nach crash, shutdown
- freigabe von temp segmenten
4.2 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
4.3 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
4.4 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
4.5 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 ;
4.6 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
4.7 JOB QUEUE PROCESSES
- user batch prozesse
- refresh von materialized views
- replication etc.
- alles was PL/SQL machen kann
5 TABLE SPACES + DATA FILES + LOG FILES
5.1 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;
5.2 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
5.2.1 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;
5.2.2 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 ;
5.2.3 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 ;
5.2.4 Tablespace vergrössern/verkleinern
SQL> ALTER DATABASE DATFILE '/u01/.../data_archive01.dbf' RESIZE 2G ;
5.2.5 Tablespace droppen
SQL> DROP TABLESPACE app_logs ; SQL> DROP TABLESPACE app_logs INCLUDING CONTENTS ; SQL> DROP TABLESPACE app_logs INCLUDING CONTENTS AND DATAFILES;
5.2.6 Tablespace online/offline nehmen
SQL> ALTER TABLESPACE users OFFLINE NORMAL|TEMPORARY|IMMEDIATE ; SQL> ALTER TABLESPACE users ONLINE ;
5.2.7 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 ;
5.2.8 Rename Tablespace
SQL> ALTER TABLESPACE users RENAME TO userdata ;
5.2.9 Löchen eines leeren Datafile
SQL> ALTER TABLESPACE test_ts DROP DATAFILE 8 ;
5.2.10 Tablespace Allocation Typ anzeigen
SQL> DESC dba_tablespaces ; SQL> SELECT tablespace_name, extent_management, allocation_type FROM dba_tablespaces ;
5.3 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 ;
5.4 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 ;
5.5 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;
5.6 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)
5.6.1 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
5.7 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';
5.8 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
- $ORACLE_BASE/admin/$ORACLE_SID/
5.8.1 mail alertlog script
http://www.bitbull.ch/dl/scripts/oracle-check-alertlog.sh
5.9 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
- ADR Base
- comands
SQL> select * from v$diag adrci> show homes
- verzeichnis struktur
- instance_id -> instance id (ADR Home)
- alert
- cdump
- incident
- trace
- ...
- instance_id -> instance id (ADR Home)
6 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)
- USER_*
- 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
6.1 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
- ...
6.2 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
6.2.1 anzeige aller verfügbaren x$, v$, gv$ tabellen
SQL> SELECT * FROM v$fixed_table; SQL> SELECT * FROM v$fixed_view_definition;
6.2.2 alle tabellen des catalog
SQL> SELECT * FROM cat ;
6.3 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 ;
7 INSTALLATION UND DB CONFIGURATION
7.1 Linux
- http://bitbull.ch/dl/scripts/oracle-10g-install.sh
- beachte: db erst nach dem patchen erstellen (mit dbca)
dann muss die db nicht ubgraded werden
- beachte: db erst nach dem patchen erstellen (mit dbca)
7.2 Windows
- mir doch egal
7.3 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)
7.3.1 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
8 RMAN - Recovery Manager
- archive logs sind eine voraussetzung für rman
8.1 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';
8.1.1 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
8.1.2 DB CLONEN MIT RMAN
- via EM im webinterface
- via comand line: Google: rman clone database
8.2 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 ;
9 DATABASE STATUS / OPERATION HANDLING
9.1 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
9.2 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
9.3 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
9.4 KILL SESSION
SQL> SELECT user, sid, serial#, logon_time from v$session; SQL> ALTER SYSTEM KILL SESSION 'sid, serial#' ;
10 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
10.1 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) ) )
10.2 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
10.3 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) ) )
11 USER AND SECURITY
11.1 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
11.2 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
- init.ora: RESSOURCE_LIMIT = TRUE (default = FALSE)
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;
11.3 SYSTEM UND OBJEKT PRIVILEGIEN
11.3.1 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;
11.3.2 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;
11.4 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)
- user roles (usr_app_storno, user_callcenter)
- application roles (accts_getstat, accts_addcutomer)
- application privileges (app_shop, app_finance)
- 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;
12 DIVERSES
12.1 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;
13 REORGANISATION VON DATEN
13.1 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
13.2 METHODEN
- Siehe auch Oracle Whitepaper
- Oracle Database 10g Release 2 Online Data Reorganisation & Redefinition
13.2.1 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
13.2.2 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 ;
13.2.3 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
13.2.4 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;
13.2.5 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;
14 INDEX
14.1 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
15 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;
15.1 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 ;
16 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.
16.1 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; /
16.2 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
16.3 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 + ^)