Ing. Fabio Binotto
[email protected]
Il database Oracle 9i
Appunti applicativi
[1] L'istanza, SGA e PGA
Un istanza di database si definisce come l'insieme dei processi attivi in background, più
la SGA (System Global Area), cioè la memoria che Oracle si riserva allo startup.
I processi di background eseguono l' I/O e tengono sotto controllo il funzionamento del
database e di tutti gli altri processi.
(Per vedere il nome dell'istanza sotto Unix: #echo $ORACLE_SID).
La SGA è composta da:
Shared Pool (parte di memoria usata per interpretare i comandi SQL e PL/SQL)
Library Cache (in particolare carica i comandi, esegue il controllo di
sintassi, dei privilegi etc.)
Data Dictionary Cache (in particolare contiene le porzioni di dizionario dati
che servono per lavorare)
Database Buffer Cache (qui vengono caricati i dati di lavoro veri e propri, prelevati dai
data files nei dischi rigidi; sono le copie dei blocchi di dati copiati dai data files; l'unità
minima di lettura /scrittura dipende dal sistema operativo e generalmente è di 4kB
oppure 8kB, ed inferiore a 64kB; questa unità viene definita dal parametro
DB_BLOCK_SIZE; i dati vengono gestiti mediante un algoritmo LRU, per cui i dati non
acceduti da un certo tempo vengono spostati per fare posto ai nuovi dati. Di fatto è
costituita da tre sotto-caches
indipendenti, definite mediante i parametri:
DB_CACHE_SIZE (dimensione della cache di default), DB_KEEP_CACHE_SIZE (cache
che memorizza i dati che si aspetta vengano riutilizzati), DB_RECYCLE_CACHE_SIZE
(cache che memorizza i dati che si aspetta non vengano usati se non raramente)).
Le informazioni sull'utilizzo sono memorizzate in V$DB_CACHE_ADVICE.
Java Pool (utilizzata per le stored procedure in Java, e per alcune librerie; sconsigliata per
motivi di performance; meglio stored procedure in PL/SQL)
Redo Log Buffer (area di memoria utilizzata per registrare i cambiamenti nei dati, prima
che questi vengano consolidati nel database; lo scopo primario è il recovery dei dati, cioè
ricostruire le transazioni sul database; contiene tutte le informazioni per ricreare i
cambiamenti fatti da: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP; è un buffer
circolare, la cui dimensione è definita dal parametro LOG_BUFFER)
Nota: all'avvio di Oracle il processo SMON esegue dei controlli di coerenza, e nel caso di
problemi può applicare tutta una serie di transazioni o di roll-back fino a portarsi ad una
situazione coerente; SMON interviene se, ad esempio, il database è stato chiuso male, e
quindi il Control File registra delle incoerenze di sincronizzazione.
Large Pool (area di memoria usata per creare una unica area di memoria, condivisa, per
molti utenti, come nel caso di un sito web a cui accedono migliaia di utenti).
Quando un utente si connette ad un server, attiva un processo server il quale esegue le
istruzioni SQL per l'utente. Quando per ogni utente si attiva un processo si usa la modalità
chiamata “dedicated server connection”; altrimenti si parla di “shared server connection”.
Nel caso di “dedicated server connection” la memoria allocata da ogni utente si chiama
PGA (program global area).
Quindi le aree di memoria fondamentalmente usate sono la SGA e la PGA; ogni utente
avrà (“dedicated server connection”) la propria PGA.
Per vedere l'area di memoria SGA dare: SQL> SHOW SGA
A partire dalla versione 9i, la SGA può essere ridimensionata (per quel che riguarda
Databases Buffer Cache, Shared Pool e Large Pool) senza chiudere l'istanza, e quindi a
caldo.
I parametri fondamentali della SGA sono:
SGA_MAX_SIZE (massima dimensione della SGA)
DB_CACHE_SIZE (cache del database, default 48MB su Unix, 52MB su Nt)
LOG_BUFFER (buffer allocati dai redo log, attorno a 500kB)
SHARED_POOL_SIZE (area per PL/SQL e dizionario dati, default 16 o 64MB)
LARGE_POOL_SIZE
(generalmente
a 0, a meno che
non vi
sia
PARRALEL_AUTOMATIC_TUNING=TRUE; Può servire per il Restore mediante RMAN, che
diventa velocissimo: in questo caso andrà alta, per esempio a 24MB; è chiaro che può
essere generalmente lasciata a 0, e messa a 24MB dinamicamente (a caldo) nel momento in
cui serve il restore.)
JAVA_POOL_SIZE (area per stored procedure Java; default 24MB; meglio mettere a 0,
salvo casi particolari, poiché purtroppo alcune stored procedure Oracle sono in Java
(anche se ufficialmente sconsigliate))
Come vedere i parametri (esempio)
SQL>SHOW PARAMETER LARGE (mostra tutti i parametri che contengono la parola
large)
SQL>SHOW PARAMETER JAVA (idem ma per la parola java)
Esempi di modifica dei parametri a caldo:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=96M;
SQL> ALTER SYSTEM SET LARGE_POOL_SIZE=24M;
(tenere presente che la somma delle aree non può eccedere la SGA_MAX_SIZE).
La SGA ed i suio componenti Database Buffer Cache, Shared Pool e Large Pool possono
crescre o decrescere in modo granulare: i granuli sono di 4MB se SGA<128MB, altrimenti i
granuli sono di 16MB.
I parametri fondamentali della PGA sono:
OPEN_CURSORS (default 50)
La memoria viene gestita per default in automatico con Oracle 9i (precedentemente vi
erano
parametri
quali
SORT_AREA_SIZE,
HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE)
Ecco un esempio di parametri dando il comando
SQL> SHOW PARAMETER
SQL> show parameter
NAME
-----------------------------------active_instance_count
aq_tm_processes
archive_lag_target
audit_file_dest
audit_sys_operations
audit_trail
background_core_dump
background_dump_dest
backup_tape_io_slaves
bitmap_merge_area_size
blank_trimming
TYPE
----------integer
integer
integer
string
boolean
string
string
string
boolean
integer
boolean
VALUE
------------------------------
NAME
-----------------------------------buffer_pool_keep
buffer_pool_recycle
circuits
cluster_database
cluster_database_instances
cluster_interconnects
commit_point_strength
compatible
control_file_record_keep_time
control_files
TYPE
----------string
string
integer
boolean
integer
string
integer
string
integer
string
VALUE
------------------------------
0
0
?/rdbms/audit
FALSE
NONE
partial
/oracle/admin/ORA1/bdump
FALSE
1048576
FALSE
0
TRUE
2
1
9.2.0
7
/metafora/oradata/ORA/controlO
RA1.ctl, /engi/oradata/ORA/con
NAME
TYPE
VALUE
------------------------------------ ----------- -----------------------------trolORA2.ctl
core_dump_dest
string
/oracle/admin/ORA1/cdump
cpu_count
integer
3
create_bitmap_area_size
integer
8388608
cursor_sharing
string
EXACT
cursor_space_for_time
boolean
FALSE
db_block_buffers
integer
0
db_block_checking
boolean
FALSE
db_block_checksum
boolean
TRUE
db_block_size
integer
16384
db_cache_advice
string
ON
NAME
-----------------------------------db_cache_size
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_domain
db_file_multiblock_read_count
db_file_name_convert
db_files
TYPE
----------big integer
string
string
string
string
string
string
string
integer
string
integer
VALUE
-----------------------------134217728
NAME
-----------------------------------db_keep_cache_size
dblink_encrypt_login
db_name
db_recycle_cache_size
dbwr_io_slaves
db_writer_processes
db_16k_cache_size
db_2k_cache_size
db_32k_cache_size
db_4k_cache_size
db_8k_cache_size
TYPE
----------big integer
boolean
string
big integer
integer
integer
big integer
big integer
big integer
big integer
big integer
VALUE
-----------------------------0
FALSE
ORA
0
0
1
0
0
0
0
0
NAME
-----------------------------------dg_broker_config_file1
dg_broker_config_file2
dg_broker_start
disk_asynch_io
dispatchers
distributed_lock_timeout
dml_locks
drs_start
enqueue_resources
event
fal_client
TYPE
----------string
string
boolean
boolean
string
integer
integer
boolean
integer
string
string
VALUE
-----------------------------?/dbs/[email protected]
?/dbs/[email protected]
FALSE
TRUE
NAME
-----------------------------------fal_server
fast_start_io_target
fast_start_mttr_target
fast_start_parallel_rollback
file_mapping
filesystemio_options
fixed_date
gc_files_to_locks
global_context_pool_size
global_names
hash_area_size
TYPE
----------string
integer
integer
string
boolean
string
string
string
string
boolean
integer
VALUE
------------------------------
NAME
-----------------------------------hash_join_enabled
hi_shared_memory_address
hs_autoregister
ifile
TYPE
----------boolean
integer
boolean
file
instance_groups
instance_name
instance_number
java_max_sessionspace_size
java_pool_size
java_soft_sessionspace_limit
string
string
integer
integer
big integer
integer
VALUE
-----------------------------TRUE
0
TRUE
/oracle/admin/ORA1/pfile/confi
gORA.ora
16
100
60
2440
FALSE
1024
0
0
LOW
FALSE
setall
FALSE
2097152
ORA
1
0
16777216
0
NAME
-----------------------------------job_queue_processes
large_pool_size
license_max_sessions
license_max_users
license_sessions_warning
local_listener
lock_name_space
lock_sga
log_archive_dest
log_archive_dest_state_1
log_archive_dest_state_10
TYPE
----------integer
big integer
integer
integer
integer
string
string
boolean
string
string
string
VALUE
-----------------------------4
0
0
0
0
NAME
-----------------------------------log_archive_dest_state_2
log_archive_dest_state_3
log_archive_dest_state_4
log_archive_dest_state_5
log_archive_dest_state_6
log_archive_dest_state_7
log_archive_dest_state_8
log_archive_dest_state_9
log_archive_dest_1
log_archive_dest_10
log_archive_dest_2
TYPE
----------string
string
string
string
string
string
string
string
string
string
string
VALUE
-----------------------------enable
enable
enable
enable
enable
enable
enable
enable
NAME
-----------------------------------log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6
log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
log_archive_duplex_dest
log_archive_format
log_archive_max_processes
log_archive_min_succeed_dest
TYPE
----------string
string
string
string
string
string
string
string
string
integer
integer
VALUE
------------------------------
NAME
-----------------------------------log_archive_start
log_archive_trace
log_buffer
log_checkpoint_interval
log_checkpoints_to_alert
log_checkpoint_timeout
log_file_name_convert
logmnr_max_persistent_sessions
log_parallelism
max_commit_propagation_delay
max_dispatchers
TYPE
----------boolean
integer
integer
integer
boolean
integer
string
integer
integer
integer
integer
VALUE
-----------------------------FALSE
0
1048576
0
FALSE
1800
NAME
-----------------------------------max_dump_file_size
max_enabled_roles
max_rollback_segments
max_shared_servers
mts_circuits
mts_dispatchers
mts_listener_address
mts_max_dispatchers
mts_max_servers
mts_multiple_listeners
mts_servers
TYPE
----------string
integer
integer
integer
integer
string
string
integer
integer
boolean
integer
VALUE
-----------------------------UNLIMITED
30
122
20
0
NAME
-----------------------------------mts_service
mts_sessions
nls_calendar
nls_comp
nls_currency
nls_date_format
nls_date_language
nls_dual_currency
nls_iso_currency
nls_language
nls_length_semantics
TYPE
----------string
integer
string
string
string
string
string
string
string
string
string
VALUE
-----------------------------ORA
0
FALSE
enable
enable
%t_%s.dbf
2
1
1
1
99
5
5
20
FALSE
0
AMERICAN
BYTE
NAME
-----------------------------------nls_nchar_conv_excp
nls_numeric_characters
nls_sort
nls_territory
nls_time_format
nls_timestamp_format
nls_timestamp_tz_format
nls_time_tz_format
object_cache_max_size_percent
object_cache_optimal_size
olap_page_pool_size
TYPE
----------string
string
string
string
string
string
string
string
integer
integer
integer
VALUE
-----------------------------FALSE
NAME
-----------------------------------open_cursors
open_links
open_links_per_instance
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
optimizer_mode
oracle_trace_collection_name
oracle_trace_collection_path
TYPE
----------integer
integer
integer
integer
string
integer
integer
integer
string
string
string
VALUE
-----------------------------300
4
4
1
9.2.0
0
100
2000
CHOOSE
NAME
-----------------------------------oracle_trace_collection_size
oracle_trace_enable
oracle_trace_facility_name
oracle_trace_facility_path
os_authent_prefix
os_roles
O7_DICTIONARY_ACCESSIBILITY
parallel_adaptive_multi_user
parallel_automatic_tuning
parallel_execution_message_size
parallel_instance_group
TYPE
----------integer
boolean
string
string
string
boolean
boolean
boolean
boolean
integer
string
VALUE
-----------------------------5242880
FALSE
oracled
?/otrace/admin/fdf
ops$
FALSE
FALSE
FALSE
FALSE
2176
NAME
-----------------------------------parallel_max_servers
parallel_min_percent
parallel_min_servers
parallel_server
parallel_server_instances
parallel_threads_per_cpu
partition_view_enabled
pga_aggregate_target
plsql_compiler_flags
plsql_native_c_compiler
plsql_native_library_dir
TYPE
----------integer
integer
integer
boolean
integer
integer
boolean
big integer
string
string
string
VALUE
-----------------------------5
0
0
TRUE
2
2
FALSE
0
INTERPRETED
NAME
-----------------------------------plsql_native_library_subdir_count
plsql_native_linker
plsql_native_make_file_name
plsql_native_make_utility
plsql_v2_compatibility
pre_page_sga
processes
query_rewrite_enabled
query_rewrite_integrity
rdbms_server_dn
read_only_open_delayed
TYPE
----------integer
string
string
string
boolean
boolean
integer
string
string
string
boolean
VALUE
-----------------------------0
NAME
-----------------------------------recovery_parallelism
remote_archive_enable
remote_dependencies_mode
remote_listener
remote_login_passwordfile
remote_os_authent
remote_os_roles
replication_dependency_tracking
resource_limit
resource_manager_plan
rollback_segments
TYPE
----------integer
string
string
string
string
boolean
boolean
boolean
boolean
string
string
VALUE
-----------------------------0
true
TIMESTAMP
NAME
TYPE
VALUE
AMERICA
10
102400
33554432
?/otrace/admin/cdf
FALSE
FALSE
500
false
enforced
FALSE
NONE
FALSE
FALSE
TRUE
FALSE
-----------------------------------row_locking
serializable
serial_reuse
service_names
session_cached_cursors
session_max_open_files
sessions
sga_max_size
shadow_core_dump
shared_memory_address
shared_pool_reserved_size
----------string
boolean
string
string
integer
integer
integer
big integer
string
integer
big integer
-----------------------------always
FALSE
DISABLE
ORA, ORA1, ORA2
0
10
555
522161952
partial
0
15938355
NAME
-----------------------------------shared_pool_size
shared_servers
shared_server_sessions
sort_area_retained_size
sort_area_size
spfile
sql_trace
sql_version
sql92_security
standby_archive_dest
standby_file_management
TYPE
----------big integer
integer
integer
integer
integer
string
boolean
string
boolean
string
string
VALUE
-----------------------------318767104
0
0
1048576
1048576
NAME
-----------------------------------star_transformation_enabled
statistics_level
tape_asynch_io
thread
timed_os_statistics
timed_statistics
trace_enabled
tracefile_identifier
transaction_auditing
transactions
transactions_per_rollback_segment
TYPE
----------string
string
boolean
integer
integer
boolean
boolean
string
boolean
integer
integer
VALUE
-----------------------------FALSE
TYPICAL
TRUE
1
0
TRUE
TRUE
NAME
-----------------------------------undo_management
undo_retention
undo_suppress_errors
undo_tablespace
use_indirect_data_buffers
user_dump_dest
utl_file_dir
workarea_size_policy
TYPE
----------string
integer
boolean
string
boolean
string
string
string
VALUE
-----------------------------AUTO
900
TRUE
undotbs1
FALSE
/oracle/admin/ORA1/udump
*
MANUAL
FALSE
NATIVE
FALSE
?/dbs/arch
MANUAL
TRUE
610
5
[2]I files del database
I dati vengono memorizzati entro files sui dischi rigidi.
I files sono di tre tipi:
Data files: contengono i dati del database;
Online redo log files: contengono una registrazione di tutti gli ultimi cambiamenti fatti
sul database, per permettere anche il recovery del database in caso di failure;
Control files: contengono tutte le informazioni per mantenere e verificare l'integrità del database.
Gestiscono quindi la sincronizzazione di tutte le informazioni sul database. Senza questi files il
database non può partire.
Vi sono poi altri files:
il file dei parametri (initINSTANCE.ora);
il password files (autentica gli utenti privilegiati alla gestione del database);
gli Archived redo log files: sono copie offline dei redo log files; applicando tutte queste copie e
partendo da un punto di backup, si possono ricostruire tutte le ultime transazioni di un database.
[3]I processi di background
I processi di background mantengono le relazioni tra le strutture di memorizzazione fisica
e quelle di memorizzazione temporanea.
I processi obbligatori (per il funzionamento del database) sono:
DBWn
E' il processo Database Writer; trascrive i blocchi dal database buffer cache ai data files sui
dischi; scrive quando: avviene un checkpoint, i dirty buffer raggiungono un valore di
soglia, non ci sono buffer liberi, vi sono timeout, quando una tablespace viene posta
offline, quando una tablespace viene posta in readonly, quando vi è un drop o truncate di
una tabella, quando comincia un backup (SQL>ALTER TABLESPACE PIPPO BEGIN
BACKUP), quando vi è una richiesta RAC di ping.
PMON
Sorveglia i processi server; nel caso di problemi, fa il rollback delle transazioni, rilascia i
lock, rilascia altre risorse (nella versione 9, ogni 30-60 secondi killa i processi server che
riconosce come anomali o terminati non correttamente).
CKPT
IL processo di Checkpoint scrive ogni 3 secondi e memorizza dati nel control file per
memorizzare in quale posizione dei redolog on line il recovery debba cominciare, nel caso
di crash. Tiene quindi conto di quanto il DBWn abbia già scritto nei data files, e di quando
sia stato scritto dai redolog online ai redolog files.
LGWR
E' il processo di log writer; scrive quando è vera una di queste condizioni: quando c'è un
commit, quando è pieno per un terzo (siccome è piccolino, 500kB per esempio, scrive
frequentemente), quando vi è 1MB di redo buffer, ogni tre secondi, prima che scriva il
DBWn.
Il processo LGWR chiede al DBWn di scrivere.
LGWR conferma le sue operazioni sono dopo che il redo buffer è stato scritto su disco.
SMON
Dopo un crash ripristina la situazione (rollback o rollforward).
ARCn
E' il processo opzionale che automaticamente archivia gli online redo buffer quando è
settato il modo ARCHIVELOG; preserva una registrazione di tutti i cambiamenti fatti sul
database. E' una modalità cruciale per il recupero dei dati dopo la perdita di un disco.
Di solito un database di produzione è configurato con gli ARCHIVELOG attivi.
Regole per i processi di background:
1) i processi DBW e LGWR si fermeranno assieme, allo shutdown;
2) LGWR deve stare sempre avanti a DBWR, nella scrittura su disco;
3) LGWR starà avanti quanto definito da alcuni parametri che tengono conto del tempo
per eseguire le transazioni.
Per memorizzare: il processo LGWR si può paragonare alla lepre, inseguita dal cane San
Bernardo, il processo di DBW. Tipicamente LGWR scatta ogni decimo di secondo; il
DBW scatta ogni 15-20 minuti.
[4] Struttura logica del database
La struttura è la seguente:
tablespace (la più piccola entità amministrabile del database)
Le tablespace possono essere messe fuori linea, eccetto per la system e per la undo attiva.
Di una tablespace si può fare il backup separato.
Una tablespace può essere messa in read/write o in read-only.
segments (tabelle, indici)
extents (insiemi di blocchi allocati dai segmenti)
blocchi (la più piccola entità letta o scritta)
[5] Gli utenti amministratori del database
Gli utenti sys e system sono creati automaticamente durante la creazione del database.
Essi hanno i diritti di DBA.
Sys è il possessore del dizionario dei dati del database.
System è il possessore delle tabelle addizionali e delle viste usate per la gestione del
database.
Per motivi di sicurezza, le password di sys e system vanno cambiate subito dopo la
installazione del database.
Modalità di connessione per sys:
$sqlplus /nolog
sql>connect / as sysdba
[6] I files di inizializzazione (dei parametri)
Il file dei parametri può essere statico o dinamico (modificabile a caldo).
File statico: pfile, file di testo, initSID.ora
File dinamico: spfile, file binario, spfileSID.ora
I file si trovano in $ORACLE_HOME/dbs
Posso creare un file a partire dall'altro:
SQL>CREATE SPFILE FROM PFILE
SQL>CREATE PFILE FROM SPFILE
Cosa userà Oracle?
In ordine:
$ORACLE_HOME/dbs/spfileSID.ora
$ORACLE_HOME/dbs/spfile.ora
$ORACLE_HOME/dbs/initSID.ora
$ORACLE_HOME/dbs/init.ora
Oppure a mano bisogna dare:
SQL> startup pfile='xyz';
(non esiste startup spfile).
Nota bene: le modifiche a caldo vengono registrate nell'spfile solo se il database è partito
con quell'spfile.
Le modifiche possibili sono del tipo:
SQL>ALTER SYSTEM SET SHARED_POOL_SIZE=4M;
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDO2;
SQL>ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=spfile
(so che l'ultimo parametro non può essere modificato a caldo, ma registra la modifica
nell'spfile per la prossima partenza)
Esempio di pfile:
ifile = /oracle/admin/ORA1/pfile/configORA.ora
background_dump_dest = /oracle/admin/ORA1/bdump
user_dump_dest = /oracle/admin/ORA1/udump
core_dump_dest = /oracle/admin/ORA1/cdump
instance_number = 1
instance_name = ORA
thread = 1
undo_tablespace = undotbs1
job_queue_processes = 4
job_queue_interval = 10
open_links = 4
il file configORA.ora a cui si fa riferimento ha il seguente contenuto:
cluster_database
= true
control_files
=
(/metafora/oradata/ORA/controlORA1.ctl,/engi/o
radata/ORA/controlORA2.ctl)
compatible
= 9.2.0
db_block_size
= 16384
db_cache_size
= 120M
db_file_multiblock_read_count
= 16
db_files
= 100
db_name
= ORA
enqueue_resources
= 1024
java_pool_size
= 8M
log_buffer
= 1048576
open_cursors
= 300
optimizer_mode
= CHOOSE
processes
= 500
service_names
= (ORA, ORA1, ORA2)
shared_pool_size
= 300M
sort_area_retained_size
= 1048576
sort_area_size
= 1048576
undo_management
= auto
# rimuovere quando non ci saranno piu' "SET TRANSACTION" stmts in
applicazioni
undo_suppress_errors
= true
utl_file_dir
= *
[7] Partenza e chiusura del database
Gli stati del database sono i seguenti:
OPEN
MOUNT
NOMOUNT
SHUTDOWN
in salita si possono percorrere tutti i gradini; in discesa si può fare solo shutdown.
SQL>STARTUP NOMOUNT (parte solo l'istanza; posso in questo stato creare un
database con CREATE DATABASE...; serve inoltre per il backup con RMAN)
(si passa al livello successivo con ALTER DATABASE PIPPO MOUNT)
SQL>STARTUP MOUNT (vengono aperti e letti i control files; serve per gestire i
problemi di recovery)
(si passa al livello successivo con ALTER DATABASE PIPPO OPEN oppure, per esempio,
ALTER DATABASE PIPPO OPEN READ ONLY)
SQL>STARTUP (si aprono tutti i files, eventualmente si fa un recovery se il db non è
cosnistente).
(oppure SQL>STARTUP RESTRICTED; solo il dba può lavorare; equivalente ad ALTER
SYSTEM ENABLE RESTRICTED SESSION, che si può usare quando il db è già partito,
avendo cura di far terminare le sessioni già terminate).
La chiusura del database si effettua in questi modi:
SQL>SHUTDOWN (normal)
Oracle aspetta che tutti gli utenti finiscano le transazioni; non permette nuove connessioni,
ma si aspetta un tempo lunghissimo.
SQL>SHUTDOWN ABORT
Oracle chiude il db chiudendo immediatamente tutti i processi; non fa eventuali rollback;
non adrebbe mai fatto se non in casi di reale emergenza.
SQL>SHUTDOWN IMMEDIATE
Oracle chiude in subito in modo corretto; termina i processi attivi e fa il rollback delle
transazioni non terminate; salva il db in uno stato consistente. E' la modalità da utilizzare.
(sotto Windows vi sono le chiavi di registro: ORA_ORCL_SHUTDOWN='TRUE' ed
ORA_ORCL_SHUTDOWNTYPE='I', per garantire questa modalità alla chiusura del
servizio)
SQL>SHUTDOWN TRANSACTIONAL
E' analogo allo shutdown (normal); chiude subito le select, ma aspetta per le transazioni
attive.
[8] Strumenti diagnostici
Per vedere dove vengono mandati i messaggi di log, dare il comando:
SQL>SHOW PARAMETER DUMP_DEST
(il parametro da vedere è BACKGROUND_DUMP_DEST).
Il file principale è: alert SID.log.
Tiene la storia di tutto quello che è successo nel database; posso vedere le corruzioni
fisiche, gli errori (cercare “error” oppure “ORA-”); se non vi sono problemi si può
cancellare tranquillamente.
Poi vi sono i files di trace: sono sotto
../admin/udump (user)
../admin/cdump
../admin/bdump (background processes)
L'utility tkprof permette di formattare l'output dei files in modo comprensibile.
E' possibile mettere sotto trace una sessione:
SQL>ALTER SESSION SET SQL_TRACE=TRUE
oppure
SQL>DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
Si può dare il comando anche a livello di istanza, ma è sconsigliato:
SQL_TRACE=TRUE
[9] Il dizionario dati
Il dizionario dati descrive il database ed i suoi oggetti.
Contiene tabelle readonly e viste.
Sono memorizzate nella tablespace System, e possedute da Sys.
Le viste sono di tre tipi:
dba_xxx
quello che c'è in tutti gli schemi
all_xxx
quello a cui un utente può accedere (grazie a dei privilegi)
user_xxx
quello che esiste nello schema dell'utente
Viste interessanti:
DICTIONARY, DICT_COLUMNS
DBA_TABLES, DBA_INDEXES...
DBA_SEGMENTS, DBA_EXTENTS...
DBA_TABLESPACES, DBA_DATA_FILES...
Oracle gestisce se stesso mediante i comandi DDL (data definition language) che sono
essenzialmente: CREATE, ALTER, DROP, TRUNCATE, GRANT/REVOKE, ANALYZE.
Tali comandi non hanno bisogno del commit, sono auto-committanti. Quindi non si torna
indietro mediante rollback!
Il comando analyze merita dei commenti.
Il comando analyze serve per creare delle statistiche sull'utilizzo delle tabelle; in pratica si
vanno a compilare dei campi (che contengono contatori) già esistenti, e che altrimenti
rimarrebbero sempre vuoti.
Se Oracle dispone di questi dati può ottimizzare i suoi piani di esecuzione.
Esercizio;
SQL> SELECT * FROM DBA_TABLES;
(vedo le informazioni su tutte le tabelle)
SQL> SELECT * FROM DBA_TABLES WHERE TABLE_NAME='ANTO';
(vedo i campi vuoti relativamente alla tabella anto)
SQL> ANALYZE TABLE ANTO COMPUTE STATISTICS;
(calcolo le statistiche su anto)
SQL> ANALYZE TABLE ANTO ESTIMATE STATISTICS;
(stimo le statistiche su anto; meglio il comando precedente)
SQL> SELECT * FROM DBA_TABLES WHERE TABLE_NAME='ANTO';
(ora i campi statistici su anto sono compilati)
Le viste di dizionario
alcune viste sono le seguenti (ottenibili mediante i seguenti script):
SQL> SELECT * FROM USER_TABLES;
SQL> SELECT TABLE_NAME FROM USER_TABLES;
SQL> SELECT TABLE_NAME FROM DBA_TABLES; (se ho i privilegi di select any
dictionary)
SQL> SELECT * FROM DBA_TABLES WHERE OWNER='PIPPO';
SQL> SELECT * FROM DBA_SEGMENTS;
SQL> SELECT * FROM DBA_EXTENTS WHERE OWNER='PIPPO' AND
SEGMENT_TYPE='TABLE';
SQL> SELECT * FROM DICTIONARY;
Le V$* sono virtual tables (viste delle tabelle del dizionario), e sono create al runtime
perchè riflettono l'attività corrente del database. Sono continuamente aggiornate
durante l'attività del database. Sono usate per monitorizzare il database al runtime.
Esempi di V$* sono:
V$SGASTAT
V$SESSION
V$CONTROLFILE
V$DATAFILE
V$INSTANCE
V$PARAMETER
V$SGA
V$TABLESPACE
V$VERSION
V$THREAD
V$DATABASE
V$SPPARAMETER
[10] Il control file
Il control file è un piccolo file binario, che definisce lo stato corrente del database. Server a
mantenere l'integrità del database. E' richiesto al mount del database durante lo startup
del database; la perdita del control file richiede il recovery del database.
Contiene:
– il nome del database
– il time stamp della creazione del database
– i nomi delle tablespaces
– nomi e localizzazioni dei data files e dei redo log files
– il numero di sequenza del redo log file corrente
– informazioni sui checkpoint
– inizio e fine dei segmenti di undo
– informazioni sugli archivi di redo log
– informazioni sui backup
Si raccomanda fortemente che i control file siano più di uno e memorizzati su dischi
diversi, per evitare problemi dovuti alla corruzione di uno di essi.
Il control file va salvato subito dopo una modifica al database.
Informazioni sul contro file si trovano in:
v$controlfile
v$parameter
inoltre si può dare il comando
SQL>SHOW PARAMETER CONTROL_FILE
[11] I redo log files
I redo log files hanno le seguenti caratteristiche:
–
–
–
–
registrano tutti i cambiamenti effettuati sui dati
forniscono un meccanismo per il recovery dei dati
possono essere organizzati in gruppi
servono almeno due gruppi
Quando un database sta lavorando, tutte le transazioni vengono scritte in modo
sincrono anche nei buffers di redo log, i quali vengono poi scaricati nei files di redo log;
Questo serve a creare un meccanismo per recuperare le transazioni, in caso del crash del
database o di un media (a meno che vi sia una clausola di NOLOGGING abilitata, ad
esempio caricando i dati con SqlLoader).
Quindi i redo log files sono utilizzati per recuperare dati committati ma che non sono
ancora stati scritti sui data files.
Gli online redo log files sono usati solo per il recovery.
I redo log sono organizzati in gruppi; ogni gruppo contiene files identici (copie ridondanti
per sicurezza); i file identici vanno messi su dischi diversi.
Il processo di LGWR (log writer) scrive le informazioni in contemporanea su tutte le copie
di un gruppo. I gruppi devono essere almeno due.
Gli on line redo log files sono usati in modo ciclico; quando un gruppo è pieno, il LGWR
muove al prossimo gruppo, con una azione chiamata “switch”; in questo caso avviene
anche un checkpoint e l'informazione viene scritta sul control file.
Si può forzare sia uno switch che un checkpoint:
SQL>ALTER SYSTEM SWITCH LOGFILE;
SQL>ALTER SYSTEM CHECKPOINT;
Si possono aggiungere file ai log file:
SQL>ALTER DATABASE PIPPO ADD LOGFILE GROUP 2
('/usr/oracle/esempio/a.rdo') SIZE 2M;
Se un gruppo di redo log non è attivo, si può cancellare:
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Analogamente si può cancellare un file:
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/usr/oracle/esempio/a.rdo';
(se è attivo l'archivelog mode, bisogna che il file sia stato archiviato prima che vi sia la
possibilità di cancellarlo).
Può succedere che un redo log file si corrompa mentre il database stà funzionando; allora
si può reinizializzare senza chiudere il database:
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Naturalmente in questo caso non si può più eseguire il recover del database dall'ultimo
backup.
Nota: guardando l'alert log, se il processo di LGWR deve attendere frequentemente un
gruppo perchè il checkpoint non è avvenuto o il gruppo non è stato archiviato, allora
bisogna aggiungere un gruppo.
Dove trovare informazioni sui membri:
V$LOG, V$LOGFILE
Gli stati corrispondenti sono:
UNUSED: mai usato
CURRENT: attualmente in uso
ACTIVE: attivo, ma non corrente; è necessario per il recovery, nel caso succeda un crash
CLEARING: è nella fase di ricreazione
CLEARING_CURRENT: nella fase di ricreazione, anomala
INACTIVE: non in uso e non necessario per il recovery.
Il database può essere attivo in ARCHIVELOG mode o NOARCHIVELOG; conviene
attivare il modo ARCHIVELOG, così i redo log files vengono attivati in automatico dai
processi di ARC.
Per vedere se è attivo il processo di ARC:
SQL> select archiver from v$instance;
ARCHIVE
------STOPPED
[12] Le tablespace ed i data files
I dati sono memorizzati fisicamente nei datafiles e logicamente nelle tablespace.
La tablespace principale è quella di SYSTEM, creata assieme al database. Contiene il
dizionario dei dati ed il segmento di UNDO di sistema.
Altre tablespace possono essere create dall'amministratore. TEMP ed UNDO sono
proposte di default all'atto della creazione di un database.
SQL>CREATE TABLESPACE userdata DATAFILE '/usr/prova/dati_001.dbf' SIZE 10M;
I parametri fondamentali sono:
DATAFILE: specifica il file di dati;
LOGGING (è il default): significa che i cambiamenti di tabelle, indici e partizioni sono
scritti entro i redo log files);
OFFLINE: tablespace fuori linea;
PERMANENT: la tablespace può essere utilizzata per contenere oggetti permanenti;
TEMPORARY: la talblespace può essere usata solo per oggetti temporanei, come ad
esempio per contere le tabelle temporanee usate da ORDER BY etc.
EXTENT MANAGEMENT LOCAL (default per la 9i): gestione automatica e locale degli
extents; è contrario di DICTIONARY MANAGED TABLESPACE;
Vantaggio: evita operazioni ricorsive di management, si riduce la contesa sulle tabelle di
dizionario dati, si libera automaticamente lo spazio libero adiacente non essendo più
necessaria l'azione di Coalesce, non si generano informazioni di UNDO relativamente
all'aggiornamento delle tabelle di dizionario;
NEXT: prossimo incremento della tab. su disco, se richiesto;
MAXSIZE: massimo spazio su disco consentito per estensione automatica di un datafile;
Dare sempre un massimo, mai mettere UNLIMITED.
Nota: non migrare la System da DICTIONARY A LOCAL: è PERICOLOSO;
UNDO TABLESPACE: usate per memorizzare le vecchie informazioni
TEMPORARY TABLESPACE: usate per memorizzare risultati di ORDER BY, GROUP
BY, DISTINCT, UNION, INTERSECT...
E' IMPORTATE CREARE LA DEFAULT TEMPORARY TABLESPACE, ALTRIMENTI IL
DATABASE USA LA TABLESPACE SYSTEM PER MEMORIZZARE I DATI
TEMPORANEI!!!
La tabella temporanea viene svuotata solo allo shutdown; altrimenti bisogna aggiungere
una tablespace vuota, metterla come tablespace di default, cancellare la vecchia.
Per vedere informazioni sulle tablespace temporanee:
SQL> SELECT * FROM DATABASE_PROPERTIES;
SQL> SHOW PARAMETERS OPEN_CURSORS
open_cursors = 6000 per ogni pga
sort_area_size = 65536 è l'area a disposizione di un utente per gli ordinamenti in memoria
RAM, dopo di che si comincia ad occupare la zona di memoria TEMP;
pga_aggregate_target = 24M utilizzando questo parametro evito swap su disco in quanto
gli ordinamenti vengono eseguiti all'interno di questo spazio; se pga_aggregate_target=0
significa che non è utilizzato questo spazio.
Mettere la tablespace in Read Only:
SQL> ALTER TABLESPACE PIPPO READ ONLY;
Questa istruzione causa un checkpoint;
i dati sono disponibili solo in lettura;
gli oggetti sono cancellabili entro la tablespace (ad esempio posso cancellare un indice e
ricostruirlo).
Mettere la tablespace offline (ed online):
SQL>ALTER TABLESPACE PIPPO OFFLINE [normal | temporary|immediate|for
recovery];
SQL>ALTER TABLESPACE PIPPO ONLINE;
La tablespace System non può essere messa offline;
Non possono essere messe offline le tablespace on attivi segmenti di UNDO;
Non può essere messa offline la default temporary tablespace.
L'operazione di Coalesce
L'operazione di coalesce serve a togliere le flag che suddividono ancora gli extent liberi, in
modo da aumentare lo spazio contiguo libero, altrimenti lo spazio risulta libero, ma se
libero, per es., a frammenti da 4k e mi serve un frammento da 8k non posso utilizzarlo.
L'operazione di coalesce è la seguente:
SQL> ALTER TABLESPACE PIPPO COALESCE;
E' compito del dba lanciare il coalesce, perchè generalmente il processo di SMON non lo
fa.
Se la tablespace è DICTIONARY MANAGED, il coalesce è necessario; se la tablespace è
LOCALLY MANAGED, il coalesce non è necessario, perchè gli extents sono gestiti in
automatico all'interno della tablespace.
Operazioni consigliate periodicamente:
per non avere frammentazione e per ottimizzare gli indici:
export – drop – import.
[13] Le tabelle ed indici
Fanno parte dei Segments che generalmente sono:
Tabelle, indici, table partition, cluster (composti da una o più tabelle), nested tables,
LOBs, undo segments, temporary segments, etc.
LE TABELLE
Esempio di comando per la creazione di una tabella:
CREATE TABLE PIPPO
(
nome varchar2(30),
cognome varchar2(30),
codice number(12)
)
STORAGE
(
INITIAL 200K
NEXT 200K
PCT INCREASE 0
MINEXTENT 1
MAXEXTENT 10)
TABLESPACE ESEMPIO;
Altri parametri:
INITRANS: minimo numero di transazioni concorrenti per il blocco;
MAXTRANS: massimo numero di transazioni concorrenti (default 255) per il blocco;
PCT FREE (default 10%; quanto spazio rimane libero entro un blocco)
(se il parametro è giusto, sarà simile all' AVG_SPACE= spazio medio libero in un blocco, e
vi saranno poche riche concatenate o spostate (CHAIN_CNT); questi parametri li vedo
tramite le statistiche che il dba deve lanciare periodicamente per garantire l'uso di buoni
piani di esecuzione); PCT FREE serve per permettere le modifiche all'interno del blocco;
PCTUSED (default 40%): spazio minimo occupato nel blocco; un blocco vieno messo nella
lista dei blocchi liberi (FREELIST) quando lo spazio occupato scende sotto il 40%;
Nota: il numero giusto da dare a pct increase è 0; infatti, perchè avere tabelle che crescono
esponenzialmente? Pct increase è generalmente responsabile della frammentazione.
Nota: conviene gestire in automatico lo spazio entro i blocchi (pctused, feelist e freelist
groups possono essere gestiti automaticamente). Come fare:
SQL> CREATE TABLESPACE PIPPOTAB DATAFILE 'usr/file1' EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT
AUTO;
(è prerequisito extent management local!).
MOVE: il comando di move serve a muovere fisicamente i dati; li compatta e li ottimizza;
un tempo esisteva solo la sequenza: EXPORT, TRUNCATE, IMPORT, CREATE INDEX,
ABILITA I CONSTRAINTS, ma questo causava disservizio;
il comando è il seguente:
SQL> ALTER TABLE PIPPO MOVE;
(esegue una create table as select ..., cancella la tabella vecchia, rinomina la nuova con lo
stesso nome; gli indici sono a carico nostro => bisogna fare il rebuild degli indici, perchè
gli indici continuerebbero ad esistere, ma le rowid punterebbero alla vecchia tabella
cancellata; sono marcati allora UNUSABLE);
Posso anche spostare una tabella da una tablespace all'altra:
SQL> ALTER TABLE PIPPO MOVE TABLESPACE ESEMPIO;
Come fare con gli indici?
SQL> select * from dba_indexes where table_name='PIPPO';
supponiamo esista un indice di nome PIPPO_INDICE_01
SQL> ALTER INDEX ESEMPIO.PIPPO_INDICE_01 REBUILD;
Allo scopo conviene creare degli scripts così:
SQL> SELECT 'ALTER INDEX '||index_name||' REBUILD;' FROM DBA_INDEXES
WHERE STATUS='UNUSABLE';
TRUNCATE:
cancella i dati nella tabella resettando la watermark; rilascia tutti gli extent non richiesti da
minextent;
è il modo più veloce per svuotare una tabella.
SQL> TRUNCATE TABLE ESEMPIO.PIPPO;
DROP:
cancello la tabella;
SQL> DROP TABLE ESEMPIO.PIPPO CASCADE COSTRAINT;
(cascade costraint è necessaria se esiste una foreign key);
Attenzione: se specifico anche ON DELETE CONSTRAINT mi cancella anche il campo
relatino della foreign key!
OPERAZIONI SULLE COLONNE:
SQL> ALTER TABLE PIPPO DROP COLUMN COMMENTI CASCADE CONSTRAINTS
CHECKPOINT 1000; (pesante)
SQL> ALTER TABLE PIPPO RENAME COLUMN COGNOME TO COGNOME2;
SQL> ALTER TABLE PIPPO MODIFY COLUMN COGNOME NULL;
SQL> ALTER TABLE PIPPO SET UNUSED COLUMN COMMENTS CASCADE
CONSTRAINTS; (rende assolutamente inutilizzabile la colonna);
GLI INDICI
Gli indici possono essere di tipo:
– UNIQUE O NONUNIQUE
– FUNCTION BASED (esempio UPPER(NOME))
– DOMAIN (per ricerche particolari)
Gli indici occupano spazio e sono onerosi (ad esempio, un update in tabella implica
cancellazione ed inserimento entro un indice, scrittura nei Roll_back e nei Redo_log);
Se faccio una ricerca vediamo quando uso l'indice:
...where nome='fabio' usa l'indice
...where nome in ('fabio','laura') può usare l'indice
...where nome <> 'fabio' non usa l'indice
...where nome like 'fab%' usa l'indice
...where nome like '%bio' non usa l'indice
...where nome is null
non usa l'indice
Per quel che riguarda le tipologie di indici utilizzabili, B-tree e Bitmap, vediamo quando
sono utili:
B-tree index: selettività < 5%
Bitmap index: selettività < 20%
i bitmap vanno bene per bassa cardinalità e sono efficienti per il datawarehousing; da non
usare per le transazioni online (mettono il lock su tutto uno stream), perchè sono
tremendamente onerosi;
SQL> CREATE INDEX I1 ON PIPPO(CODICE) TABLESPACE INDX; (B-TREE)
SQL> CREATE BITMAP INDEX I1 ON PIPPO(CODICE) TABLESPACE INDX; (BITMAP)
Operazioni sugli indici:
SQL> ALTER INDEX I1 REBUILD;
(ricostruisce l'indice ed abbandona il vecchio; durante il lavoro mette un lock sull'indice);
SQL> ALTER INDEX I1 REBUILD ONLINE;
(sconsigliato se le modifiche on line sono molte)
SQL> ALTER INDEX I1 COALESCE; (compatta i blocchi, se possibile)
SQL> DROP INDEX I1;
SQL> ALTER INDEX I1 DEALLOCATE UNUSED; (libera i blocchi non usati)
SQL> ALTER INDEX I1 VALIDATE STRUCTURE; (verifica se vi sono blocchi corrotti;
popola la vista INDEX_STATS);
COME VEDERE SE GLI INDICI SONO UTILIZZATI E QUANTO:
SQL> ALTER INDEX I1 MONITORING USAGE; (POI NOMONITORING)
lo lascio attivo per esempio una settimana e poi vedo le statistiche di uso in
V$OBJECT_USAGE
COME FORZARE L'USO DELL'INDICE:
SQL> SELECT /*+ INDEX(PIPPO, I1) */ FROM PIPPO WHERE MATRICOLA>0;
FUNCTION BASED INDEX:
Se ho un indice su COLONNA1 e faccio una ricerca dove vi è:
select ... where to_char(COLONNA1)='XX';
la ricerca non segue l'indice; allora devo fare un function based index su to_char
(COLONNA1).
Analogamente sul to_number di un parametro e così via.
[*]
Appendice: alcune note
1) Gli indici vanno ricostruiti frequentemente, anche ogni sera se serve; l'istruzione da
usare è: rebuild; questa operazione si fa al runtime;
2) Attenzione: l'istruzione “select * from tabella” non usa l'indice, ma fa full scan sulla
tabella;
3) Nella versione 9 di Oracle è stata inserita anche l'istruzione: merge (oltre a insert,
update, delete, select);
4) Quanto si modificano dei dati, i dati vecchi vengono salvati nella tablespace di UNDO (i
vecchi roll back segments); se si fanno grosse modifiche, il commit va dato subito,
altrimenti gli utenti che fanno select sono forzati a leggere negli UNDO i vecchi dati,
con grossa perdita di prestazioni. Poiché inoltre il Lock viene tolto alla successiva select
(per motivi di ottimizzazione), conviene lanciare, dopo un commit su grosse quantità di
dati, una select che legga tutti i record interessati; generalmente se faccio queste
operazioni la sera, il mattino dopo gli utenti trovano tutti i record senza Lock, e non è
necessario nessun ulteriore controllo sui record.
Una select che legge tutti i record non è “select count(*) from tabella” perchè si basa
sull'indice per eseguire il conteggio; “select * from tabella” non va bene perchè provoca
un grosso output; bisogna dare “select count(colonna_nullable) from tabella” perchè la
colonna che permette null sarà letta senza usare indici.
5) Modalità con cui si eseguono le select e si conserva la consistenza temporale dei dati:
faccio select all'istante 50
faccio update di alcuni degli stessi record all'istante 51
faccio il commit all'istante 53
se la select dura molto posso ottenere due risultati:
a) “snapshot too old” se il roll back segment su cui leggo i dati vecchi dell'istante 50 è
stato sovrascritto;
b) vedo i dati all'istante 50 (pur essendo in istanti > 53) se il roll back segment non è
stato sovrascritto;
6) Come vedere le dimensioni di una tabella: select sum(bytes) from tabella;
7) I software di Oracle si scarica da: http://otn.oracle.com
8) Wrapping = nascondere; di soligo si usa per alcune storage procedures per preservare
la proprietà intellettuale.