· Etapy tworzenia bazy danych

Laboratorium Baz Danych
Tworzenie bazy danych
Opracowała:
Mgr inż. Aleksandra Werner
2
Laboratorium baz danych - Tworzenie bazy danych
1 Tworzenie/start bazy danych.
1.1
Etapy tworzenia bazy danych

Organizacja danych w przestrzenie tabel

Zaprojektowanie struktury bazy tak, aby zapobiec fragmentacji i brakowi zasobów

Przygotowanie systemu operacyjnego (ustawienie zmiennej ORACLE_SID)

Przygotowanie pliku startowego

Start instancji

Wykonanie komendy CREATE DATABASE

Zapewnienie bezpieczeństwa bazy poprzez stworzenie wielokrotnych plików dziennika
i powielenie pliku kontrolnego

Stworzenie tablic i perspektyw słownika danych
1.2
Etapy startu bazy danych (Rys.1)
STARTUP
OPEN
wystartowała instancja
został otwarty plik kontrolny, pliki danych
i pliki dziennika
MOUNT
STARTUP MOUNT
wystartowała instancja
został otwarty plik kontrolny
NOMOUNT
STARTUP NOMOUNT
wystartowała instancja
SHUTDOWN
Rys.1 Start bazy danych
 Ustawienie zmiennej środowiskowej ORACLE_SID,
 Podłączenie do bazy jako użytkownik INTERNAL (na poziomie Systemu Operacyjnego
następuje weryfikacja),
 Wydanie polecenia startu bazy (STARTUP) z ewentualnymi parametrami.
Laboratorium baz danych - Tworzenie bazy danych
3
Instancja Oracle’a
1.3
Instancja jest to grupa buforów (tzw. Obszar Globalny Systemu – SGA) wykorzystywana do
obsługi bazy oraz szereg procesów drugoplanowych (programów rezydujących w pamięci
operacyjnej komputera pracującego jako serwer Oracle’a), wykonujących wszystkie czynności
związane z obsługą bazy i jej użytkowników. Zwykle dla jednej bazy podniesiona jest jedna
instancja. Możliwe jest jednak otwarcie wielu instancji pracujących na danych jednej bazy przy
użyciu opcji Serwera Równoległego.
1.3.1 Procesy drugoplanowe
Liczba procesów drugoplanowych pracujących w instancji Oracle’a jest zależna od ustawienia
parametrów bazy. Istnieje jednak grupa procesów niezbędnych do pracy bazy:
DBWR (ang. Database Writer) – proces zapisujący dane wprowadzane przez użytkowników
do bazy z buforów SGA do plików dyskowych.
LGWR (ang. Log Writer) – proces zapisujący dane z buforów dziennika powtórzeń do
plików dziennika powtórzeń.
PMON (ang. Process Monitor) – monitor procesów trzymający pieczę nad zasobami bazy
wykorzystywanymi przez użytkowników bazy. Proces ten odzyskuje zasoby zwolnione przez
użytkownika, wycofuje niepotwierdzone transakcje, czyści nienormalnie zakończone połączenia,
wykrywa zakleszczenia.
SMON (ang. System Monitor) – monitor systemu, utrzymujący porządek w SGA. Proces ten
obsługuje plik kontrolny, odzyskuje przestrzeń po segmentach tymczasowych, wykonuje
automatyczne odtwarzanie instancji.
Na rys.2 przedstawiono instancję Oracle’a wraz z wyspecyfikowaniem wybranych
parametrów pliku inicjalizacyjnego (punkt 2.1.1), mających bezpośredni wpływ na wielkość
instancji.
2 Struktura bazy danych Oracle’a
2.1
Struktura fizyczna bazy danych
Na fizyczną strukturę bazy danych Oracle’a składają się następujące pliki:

Plik inicjalizacyjny (i konfiguracyjny),

Pliki dziennika powtórzeń (co najmniej 2),
4
Laboratorium baz danych - Tworzenie bazy danych

Pliki kontrolne,

Pliki z danymi.
2.1.1 Plik inicjalizacyjny:
Plik inicjalizacyjny umożliwia:
- optymalizację wydajności przez dostosowanie struktury pamięci (np. il. buforów danych
w pamięci),
- ustawienie wartości domyślnych dla całej bazy w momencie tworzenia,
- ustawienie limitów bazy (np. max ilośc użytkowników bazy),
- wyspecyfikowanie plików bazy.
Parametry, które powinny być określone:
DB_NAME
- identyfikator bazy danych
CONTROL_FILES
- nazwy plików kontrolnych (min. 2)
INIT_SQL_FILES
- nazwy skryptów SQL-a do wykonania w czasie tworzenia
bazy danych (zawsze sql.bsq; przydałoby się też:
catalog.sql -tworzy perspektywy słownika oraz
catproc.sql - tworzy obiekty niezbędne do opcji
proceduralnej)
LOG_ARCHIVE_DEST
- lokalizacja archiwizowanych plików dziennika powtórzeń
LOG_ARCHIVE_FORMAT
- format nazwy archiwizowanych plików dziennika
powtórzeń
USER_DUMP_DEST
- lokalizacja plików śladowych procesów użytkownika
BACKGROUND_DUMP_DEST
- lokalizacja plików śladowych procesów drugoplanowych
Parametry najczęściej modyfikowane:
AUDIT_TRAIL
- włączenie/wyłączenie obserwacji bazy danych
DB_BLOCK_BUFFERS
- liczba bloków buforów bazy danych w SGA
DB_BLOCK_SIZE
- rozmiar w [B] bloku Oracle’a; rozmiar bloku nie może
być zmieniony po utworzeniu bazy
DML_LOCKS
- max. liczba blokad DML
IFILE
- nazwa dodatkowego pliku z parametrami
LOG_BUFFER
- rozmiar w [B] buforów dziennika w SGA
Laboratorium baz danych - Tworzenie bazy danych
LOG_ARCHIVE_START
5
- włączenie/wyłączenie automatyczne archiwizacji
dziennika w trybie ARCHIVELOG
LOG_CHECKPOINT_INTERVAL
- max. liczba bloków zapisanych do pliku dziennika mdzy
pkt. kontrolnymi
LOG_CHECKPOINT_TIMEOUT
- max odstęp w [sek] mdzy pkt. kontrolnymi
MAX_DUMP_FILE_SIZE
- max. rozmiar w [blokach systemu oper.] pliku śladowego
OPEN_CURSORS
- max liczba kursorów otwartych przez użytkownika
PROCESSES
- max liczba procesów w SO jednocześnie podłączonych
do bazy danych (z drugoplanowymi_
ROLLBACK_SEGMENTS
- nazwy segmentów wycofania, aktywnych w danej
instancji
SHARED_POOL_SIZE
- rozmiar obszaru dzielonego SQL (w blokach Oracle’a)
(nie powinna być zmniejszana poniżej 3500000 [B] bo
zawiesi się wykonanie skryptu catproc.sql)
SQL_TRACE
- włączenie/wyłączenie tworzenia plików śladowych dla
procesów użytkownika
TIMED_STATISTICS
- włączenie/wyłączenie pomiaru czasu operacji dla plików
śladowych i monitora
CHECKPOINT_PROCESSES
- włączenie/wyłączenie procesu CKPT
DB_FILES
- max liczba otwartych plików danych
LOG_FILES
- max liczba otwartych plików dziennika powtórzeń
2.1.2 Plik kontrolny
Plik kontrolny zawiera informacje o fizycznej strukturze bazy danych oraz pewne informacje
kontrolne potrzebne przy odtwarzaniu. W związku z tym, że dane zawarte w pliku kontrolnym są
niezbędne do otwarcia i pracy bazy danych oraz do odtwarzania, bardzo często powiela się plik
kontrolny na innym dysku.
2.1.3 Pliki dziennika powtórzeń
Zawierają informacje o wszystkich zmianach w bazie danych (co zostało zmienione
w wierszu, co zostało zapisane w segmencie wycofania, czy transakcja została potwierdzona, czy
był realizowany punkt kontrolny, czy jest transakcja rozproszona,...) i są wykorzystywane tylko
podczas odtwarzania.
6
Laboratorium baz danych - Tworzenie bazy danych
SNAPSHOT_REFRESH_PROCESSES
liczba procesów SNPn, obsługujących aut.
odświeżanie replikacji zdalnych tablic
(opcja rozproszona)
SAVEPOINTS
max. liczba pkt. wycofania
w transakcji
CKP
ARCH
RECO
SNPn
DBnn
SMON
PMON
procesy drugoplanowe
obszar globalny systemu SGA
DB_BLOCK_BUFFERS liczba bloków w buforach
DB_BLOCK_SIZE - rozmiar bloku
SHARED_POOL_SIZE określenie rozmiaru
LOG_BUFFER- rozmiar
buforów dziennika powtórzeń
bufory dziennika
powtórzeń
bufory bazy danych
obszar dzielony
maksymalny czas między punktami kontrolnymi
LOG_CHECKPOINT_TIMEOUT
proces LOG_CHECKPOINT_INTERVAL
DBWR
procesy
usługowe
(servers)
max liczba bloków
zapisanych na plik
dziennika między
punktami
kontrolnymi
proces LGWR
pliki bazy
danych
CKPT
istnieje, jeśli
CHECKPOINT_PROCESSES = true
pliki dziennika
powtórzeń
procesy
użytkowników
pliki kontrolne
Rys.2 Instancja Oracle’a
plik
konfiguracyjny
CONTROL_FILES - identyfikacja
plików kontrolnych
Laboratorium baz danych - Tworzenie bazy danych
2.2
7
Struktura logiczna bazy danych
Na strukturę logiczną bazy danych składają się następujące elementy: (Rys. 3)

bloki,

segmenty,

obszary,

przestrzenie tabel.
Blok danych jest najmniejszą jednostką wejścia/wyjścia, używaną przez bazę danych.
Pojedynczy blok logiczny odpowiada jednemu lub większej liczbie bloków systemu operacyjnego
(zwykle, rozmiar bloku Oracle’owego jest zależny od rozmiaru bloku systemu operacyjnego).
Obszar (ang. extent) jest zbiorem kolejnych bloków zaalokowanych przez segment.
Pierwszy obszar zwany jest obszarem inicjalnym (ang. initial), natomiast następne –
przyrostowymi (ang. incremental). Oracle uważa bloki z następującymi po sobie numerami
identyfikacyjnymi jako ciągłe, chociaż nie oznacza to, że są one ciągłe na dysku. Obiekt alokuje
nowy obszar tylko wtedy, gdy aktualnie zaalokowane są użyte. Częsta dealokacja obszarów może
doprowadzić do fragmentacji wolnej przestrzeni w plikach danych.
Segment jest zbiorem jednego lub więcej obszarów, w których zawarte są dane określonego
typu logicznej struktury pamięci w przestrzeni tabel. Typy segmentów: danych, indeksu,
tymczasowy, wycofania1, otwarcia. Każdy segment w bazie jest tworzony z co najmniej jednym
obszarem dla danych., jednakże segment wycofania zawsze ma co najmniej dwa obszary.
Segmenty mogą powodować fragmentację wolnej przestrzeni (segment słownika danych nie
powoduje fragmentacji, segmenty danych powodują niewielką fragmentację, segmenty wycofania
powodują średnią fragmentację, segmenty tymczasowe powodują wysoką fragmentację).
Przestrzeń tabel jest logicznym obszarem, w którym baza przechowuje zapisane w niej
dane. Każda przestrzeń składa się z jednego lub więcej plików systemu operacyjnego, które dla
serwera Oracle’a tworzą integralną całość. Użytkownik bazy nie ma wpływu na rozmieszczenie
danych w plikach przestrzeni tablic, natomiast może jawnie wyrazić w poleceniu tworzenia
obiektu (CREATE), w której przestrzeni obiekt ma powstać (Rys.4). Pojedynczy obiekt nie może
Jest to zbiór obszarów, w których znajdują się dane modyfikowane. Służy do zapewnienia możliwości wycofania
transakcji (każda transakcja jest przypisana do jednego segmentu wycofania), spójności czytania i do odtwarzania.
1
8
Laboratorium baz danych - Tworzenie bazy danych
zostać umiejscowiony w kilku przestrzeniach tabel. Obiekty umiejscowione w danej przestrzeni
nigdy nie mogą zaalokować obszarów poza nią.
Przestrzeń tablic nr 2
wiersze danych
80%
obszar wolny
Plik danych
nr 1 obszar wolny
katalog wierszy
katalog wierszy
wierszy Tablica
Plik danych
nr 2
Plik danych nr 3
Tablica
Przestrzeń tablic nr 1
Indeks
Tablica
Indeks
Indeks
Rys.4 Przykładowe rozmieszczenie obiektów w przestrzeniach tabel
W momencie tworzenia bazy danych automatycznie tworzona jest przestrzeń SYSTEM,
zawierająca m.in.:
- słownik danych,
- definicje wyzwalaczy bazy,
- definicje procedur wbudowanych,
- segment wycofania SYSTEM,
ale zalecane jest (w zastosowaniach produkcyjnych) podzielenie bazy Oracle’a na kilka (wiele)
przestrzeni.
Po co używać różnych przestrzeni tabel?
 Żeby oddzielić dane użytkowników od danych słownika
 Żeby oddzielić dane jednej aplikacji od drugiej
 Żeby zachować różne zbiory danych przestrzeni tabel na różnych dyskach
 Żeby oddzielić dane użytkowników od danych segmentu wycofania (zabezpieczenie od
całkowitej utraty danych w przypadku zepsucia się pojedynczego dysku).
Laboratorium baz danych - Tworzenie bazy danych
9
W jednej przestrzeni tabel
może istnieć wiele typów
segmentów
Segment
112K
obszar
28 k
obszar
84 k
zbiór ciągły bloków
bloki bazy
danych
wiele fizycznych bloków
zaalokowanych w pliku
dysk jest udostępniany przestrzeni przez pliki
plik
Rys.3 Powiązanie segmentów i obszarów
2.3
Słownik bazy danych
Słownik jest to szereg tablic dostępnych dla użytkowników tylko do czytania, zawierających
opis stanu bazy danych. Tablice słownika powstają w czasie tworzenia bazy danych (do ich
zakładania służy skrypt SQL.BSD) i są niemodyfikowalne przez cały czas życia bazy.
Istnieją 3 grupy perspektyw, ułatwiających korzystanie ze słownika:
1. USER - perspektywy dostępne dla wszystkich użytkowników bazy; zawierają informacje
o obiektach będących jego własnością
2. ALL
- perspektywy dostępne dla wszystkich użytkowników bazy; zawierają informacje
o obiektach będących jego własnością oraz obiektach, do których użytkownik ten ma
nadane obiektowe prawa bazodanowe
3. DBA
- perspektywy dostępne dla użytkownika o statusie DBA; zawierają informacje
o wszystkich obiektach
Opisy wszystkich perspektyw słownika znajdują się w tablicy DICTIONARY
10
Laboratorium baz danych - Tworzenie bazy danych
2.3.1 Perspektywy z informacją o przestrzeniach tabel:
DBA_/USER_EXTENTS
DBA_/USER_SEGMENTS
DBA_/USER_FREE_SPACE (tu: również możliwość zaobserwowania fragmentacji)
DBA_/USER_TABLESPACES
DBA_USERS
DBA_DATA_FILES
DBA_TS_QUOTAS
V$DATAFILE
2.3.2 Perspektywy z informacją o obiektach schematu
ALL_/USER_TABLES
ALL_/USER_TABCOLUMNS
3 Zarządzanie pamięcią
3.1
Kontrola zajętości przestrzeni na dysku
Kontrola zużycia wolnej przestrzeni dla wstawień, uaktualnień i usunięć wierszy odbywa się
za pośrednictwem właściwych wartości parametrów wykorzystania bloku.
Do tych parametrów należą:
PCTFREE – ustawia procent używanej części bloku (bez nagłówka), zarezerwowanej dla
możliwych uaktualnień wierszy z danego bloku.
PCTUSED – jest progiem określającym, kiedy blok staje się dostępny dla wstawień nowych
wierszy.
INITRANS – określa ilość transakcji, dla których istnieje zarezerwowane miejsce
w nagłówku bloku.
MAXTRANS – określa maksymalną ilość konkurencyjnych transakcji, które mogą być
obsłużone przez blok.
Laboratorium baz danych - Tworzenie bazy danych
3.2
11
Kontrola alokacji obszarów
 Alokacja obszarów jest kontrolowana przez odpowiednie ustawienie następujących
parametrów
- rozmiar w [B] pierwszego zaalokowanego obszaru (domyślnie
INITIAL
5 bloków)
- rozmiar w [B] następnego zaalokowanego obszaru (domyślnie
NEXT
5 bloków)
MAXEXTENTS
- max ilość obszarów, które mogą być zaalokowane przez segment.
Wartość zależy od wlk. bloku (domyślnie 121 dla bloku 2k)
MINEXTENTS
- min ilość obszarów, które są zaalokowane przez segment
wycofania w momencie tworzenia (domyślnie 1)
PCTINCREASE
- procent o jaki każdy następny alokowany obszar jest większy od
oprzedniego (dom. 50 dla segm. danych i ind.; 0 dla segm. wyc.)
OPTIMAL
- określa optymalny rozmiar w [B] segmentu wycofania (dom. null)
FREELIST
- ilość list wolnych bloków dla wstawień
Ustawienie parametrów pamięci dotyczy:
 tabeli
 klastra
 indeksu
 segmentu wycofania

przestrzeni tabel
UWAGA! Ustawienie parametrów pamięci dla przestrzeni tabel jest domyślne dla innych
obiektów w niej stworzonych
12
3.3
Laboratorium baz danych - Tworzenie bazy danych
Zajętość przestrzeni na dysku dla przykładowych wartości: PCTUSED = 40 oraz
PCTFREE = 20
nagłówek
nagłówek
katalog tabel
katalog tabel
uaktualniamy wiersze;
nowe wiersze nie mogą być
wstawiane do bloku
ilość wykorzystywanej
przestrzeni spada poniżej 40%
nagłówek
nagłówek
katalog tabel
katalog tabel
katalog wierszy
katalog wierszy
nowe wiersze są wstawiane
do bloku
obszar
wolny
wiersze danych
39%
obszar
wolny
wiersze wstawiane tylko do 80%;
PCTFREE mówi, że 20% bloku musi
pozostać wolne