Zarządzanie strukturą bazy danych Oracle...

Zarządzanie strukturą bazy danych Oracle 11g

Strona: 1 | Administracja bazą Oracle 11g | Studia Podyplomowe SGGW | Laboratorium nr 3 i 4

Zarządzanie strukturą bazy danych Oracle11g

Wstęp Baza danych Oracle przechowuje dane użytkowników oraz własne dane słownikowe w następujących

strukturach logicznych:

przestrzenie tabel

segmenty

extenty

bloki bazodanowe

Przestrzenie tabel fizycznie składają się z plików systemu operacyjnego (systemu plików). Z kolei blok

bazodanowy odpowiada jednemu lub wielokrotności fizycznego bloku w systemie operacyjnym.

Przestrzenie tabel Każda instancja bazy danych posiada conajmniej 4 prekonfigurowane przestrzenie tabel:

SYSTEM

SYSAUX

TEMP

UNDO

Aby sprawdzić jakie przestrzenie tabel występują w bazie danych należy skorzystać z tabeli

DBA_TABLESPACES LUB V$TABLESPACE np:

SELECT TABLESPACE_NAME, BLOCK_SIZE, STATUS FROM DBA_TABLESPACES;

Przestrzenie tabel składają się z jednego lub wielu plików osadzonych w systemie plików widoczne przez

komendy systemu operacyjnego Linux:

ls /u01/app/oracle/oradata/orcl/*.dbf

Standardowym rozszerzeniem plików danych jest .dbf, ale można nadać każde inne rozszerzenie w

zależności od przyjętego standardu nazewniczego.

Aby dowiedzieć się, jakie pliku należą do jakich przestrzeni tabel należy z poziomu SQLPLUS wykonać

następujące zapytanie na tabeli DBA_DATA_FILES lub V$DATAFILES:

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;



opis kolumn w tych tabelach i widokach można uzyskać poleceniem DESC:

DESC DBA_TABLESPACES

DESC DBA_DATA_FILES

DESC V$DATAFILE

Tworzenie nowej przestrzeni tabel Różne obiekty wymagają różnych parametrów przestrzeni tabel stąd potrzeba tworzenia odrębnych

przestrzeni pod tabele, indeksy czy zmaterializowane widoki.

Aby utworzyć przestrzeń tabel należy zalogować się jako użytkownik SYS przez SQLPLUS:

sqlplus sys as sysdba

Następnie wykonać polecenie:

CREATE TABLESPACE MY_USERS DATAFILE

‘/u01/app/oracle/oradata/orcl/my_users01.dbf’ SIZE 20M AUTOEXTEND ON;

Powyższe polecenie tworzy przestrzeń tabel MY_USERS z podanym plikiem o wielkości początkowej

20MB z możliwością rozszerzania.

Do istniejącej przestrzeni tabel można również dodać kolejny plik poleceniem:

ALTER TABLESPACE MY_USERS ADD DATAFILE

‘/u01/app/oracle/oradata/orcl/my_users.dbf02’ SIZE 10M AUTOEXTEND ON;

Od tej chwili przestrzeń tabel MY_USERS zawiera dwa pliki fizyczne.

Zarządzanie przestrzeniami tabel Przestrzenie tabel mogą być wprowadzane przez administratora lub ze względu na błąd w kilku

statusach:

ONLINE – normalny tryb pracy

OFFLINE – przestrzeń tabel wyłączona z pracy

READ-WRITE – do zapisu i odczytu danych

READ ONLY – tylko do odczytu.

Zmiana statusu odbywa się za pomocą polecenia:

ALTER TABLESPACE MY_USERS READ ONLY;



ALTER TABLESPACE MY_USERS READ WRITE

Przechodzenie w tryb OFFLINE może odbywać się na kilka sposobów:

NORMAL– wszystkie niezapisane dane w pamięci są zapisywane do plików przestrzeni tabel. Jest

wykonywany Checkpoint. Ponowne wprowadzenie w tryb Online nie wymaga odtwarzania.

TEMPORARY – wykonuje się wtedy gdy niektóre pliki przestrzeni tabel są uszkodzone lub

niedostępne. Ponowne wprowadzenie w tryb ONLINE wymaga odtwrzania dla uszkodzonych

plików.

IMMEDIATE – natychmiastowe wprowadzenie przestrzeni w tryb OFFLINE bez wykonania

Checkpoint-u. Ta opcja jest możliwa jeśli baza działa w trybie ARCHIVELOG.

ALTER TABLESPACE MY_USERS OFFLINE NORMAL;

ALTER TABLESPACE MY_USERS OFFLINE TEMPORARY;

Ponowne wprowadzenie przestrzeni tabel w tryb ONLINE wymaga wydania polecenia: ALTER TABLESPACE MY_USERS ONLINE;

Utrata pliku przestrzeni tabel W przypadku kiedy w wyniku awarii utracimy plik przestrzeni tabel i nie jest to plik należący do

przestrzeni SYSTEM lub SYSAUX to należy wprowadzić w stan OFFLINE nie całą przestrzeń tabel, ale

tylko ten jeden plik. Załóżmy że startujemy bazę danych i któryś z plików .dbf nie istnieje lub jest

uszkodzony.

STARTUP;

DATABASE MOUNTED.

ORA-01157: CANNOT IDENTIFY/LOCK DATA FILE 5 - SEE DBWR TRACE FILE

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/users02.dbf'

Baza uruchomi się tylko w trybie MOUNT (plik kontrolny zostanie odczytany). Należy brakujący plik

wprowadzić w tryb OFFLINE następującym poleceniem:

ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/orcl/users02.dbf'

OFFLINE DROP;

Klauzulę OFFLINE DROP stosujemy kiedy baza jest w trybie NOARCHIVELOG czyli pliki redo log nie są

kopiowane do archive log. Jeśli baza działa w trybie ARCHIVELOG to wystarczy klauzula OFFLINE.



Po odtworzniu utraconego pliku (np. przez dogranie go z kopii) można ponownie przywrócić go w tryb

ONLINE:

ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/orcl/users02.dbf'

ONLINE;

Powyższa procedura zadziała tylko wtedy kiedy brakujący plik został poprawnie zamknięty przy

zamykaniu bazy danych i został utracony przed jej ponownym startem. W innym przypadku jeśli baza

działa w trybie NOARCHIVELOG Oracle zaleca usunięcie całej przestrzeni tabel bo może nie być możliwe

odtworzenie jej do właściwego momentu w czasie.

Szczegółowe informację o logicznej alokacji obiektów bazy danych Tabele przechowywane sa w przestrzeniach tabel i jej plikach. Dla obiektów takie jak tabele czy indeksy

baza tworzy Segmenty, a w nich Extent-y, które składają się z ciągłego obszaru Bloków bazodanowych.

Informacja o Segmentach znajduje się w widoku DBA_SEGMENTS. Szczegóły alokacjo Extent-ów można

znaleźć w widoku DBA_EXTENTS np:

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS

FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’TEST_TABLE’;

SELECT EXTENT_ID, BLOCKS, FILE_ID

FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE';

Następujące kolumny oznaczają

SEGMENT_NAME - odnosi się do nazwy obiektu czyli nazwy tabeli czy indeksu

BYTES - określa rozmiar w bajtach danego segmentu lub extentu.

BLOCKS – ilość bloków bazy danych w segmencie lub extencie

FILE_ID – identyfikator pliku przestrzeni tabel. Aby uzyskać ścieżkę do pliku przestrzeni tabel

to trzeba to wyciągnąć z widoku DBA_DATA_FILES.

Przestrzenie tabel o różnych rozmiarach bloku Każda przestrzeń tabel może mieć inny rozmiar bloku dlatego dla każdej przestrzeni tabel można to

sprawdzić zapytaniem:

SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;



Blok to najmniejsza porcja informacji odczytywana i zapisywana w operacjach I/O (wejscia/wyjścia z

urządzeń dyskowych). W przypadku dużych obiektów np. tabelach o długich kolumnach czy dużych

indeksów warto jest, aby były one przechowywane w przestrzeniach tabel o większym rozmiarze bloku

bo dużo szybciej baza jest w stanie odczytać te samą ilość danych (używając mniejszej ilości operacji

I/O). Stąd potrzeba tworzenia różnych przestrzeni tabel z różnym rozmiarem bloku. Aby było to możliwe

należy dodatkowo zarezerwować odpowiedni obszar w SGA (DB Buffer Cache) o takim samym rozmiarze

bloku. Służą do tego parametry:

DB_2K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_16K_CACHE_SIZE

DB_32K_CACHE_SIZE

Domyślmy rozmiar bloku jest określany parametrem DB_BLOCK_SIZE i najczęściej jest równy 4kB lub

8kB. Wartości parametrów sprawdzamy:

SHOW PARAMETER DB_BLOCK_SIZE

SHOW PARAMETER CACHE_SIZE

Chcąc stworzyć przestrzeń tabel o rozmiarze bloku 16k należy ustawić odpowiedni parameter

poleceniem ALTER SYSTEM:

ALTER SYSTEM SET DB_16K_CACHE_SIZE=8M;

Następnie stworzyć przestrzeń tabel o rozmiarze bloku 16kB:

CREATE TABLESPACE MY_USERS_16K DATAFILE

'/u01/app/oracle/oradata/orcl/my_users_16k.dbf' SIZE 1M BLOCKSIZE 16K;

Po utworzeniu tabel czy indeksów w tej przestrzeni tabel można z widoków DBA_SEGMENTS i

DBA_EXTENTS sprawdzić właściwy rozmiar bloku tworzonych segmentów.

Zarządzanie strukturą logiczną i fizyczną bazy w Enterprise Manager Za pomocą graficznego narzędzia Enterprise Manager można również zarządzać przestrzenią bazy

danych. Uruchamianie EM było ćwiczone na poprzednich zajęciach. W ramach przypomnienia:

Uruchomienie LISTENER-a – wymagane do poprawnego działania EM

lsnrctl start



Uruchomienie EM:

emctl start dbconsole

Po uruchomieniu EM należy otworzyć przeglądarkę i zalogować się jako SYS/oracle connect as SYSDBA.

Następnie wybrać zakładkę Server i link Tablespace:

Ćwiczenia

Ćwiczenie 1 – Sprawdzanie parametrów istniejących przestrzeni tabel

1. Korzystając z SQLPLUS:

a. Uruchomić instancję bazy

b. Sprawdzić jakie posiada aktualnie przestrzenie tabel

c. Dla każdej przestrzeni tabel zidentyfikować pliki danych

d. Sprawdzić domyślny rozmiar bloku w bazie danych

e. Sprawdzić następujące parametry przestrzeni tabel SYSTEM i SYSAUX:

i. rozmiar bloku

ii. łączną zaalokowaną liczbę extentów

iii. łączny zajmowany rozmiar w [kB]

2. Korzystając z Enterprise Manager-a

a. Sprawdzić te same dane co w punkach 1 a-d.



Ćwiczenie 2 – Zarządzanie przestrzeniami tabel

Wszystkie operacje w tym ćwiczeniu wykonać w SQLPLUS

1. Utworzyć nową przestrzeń tabel ‘MY_TS1’ z plikiem danych o rozmiarze 2 MB.

2. Dodać do przestrzeni ‘MY_TS1’ drugi plik o rozmiarze 4 MB

3. Utworzyć dowolną tabele o nazwie ‘MY_TABLE’ w przestrzeni ‘MY_TS1’

4. Wprowadzić 2 wiersze danych

5. Wprowadzić przestrzeń tabel ‘MY_TS1’ w tryb READ ONLY

6. Spróbować zaaktualizować wcześniej wprowadzone wiersze

7. Wprowadzić przestrzeń tabel ‘MY_TS1’ w tryb OFFLINE

8. Wyświetlić wszystkie wiersze z tabeli ‘MY_TABLE’;

9. Przywrócić przestrzeń ‘MY_TS1’ w tryb ONLINE

Ćwiczenie 3 – Niestandardowe przestrzenie tabel

Wszystkie operacje w tym ćwiczeniu wykonać w SQLPLUS

1. Utworzyć przestrzeń tabel ‘MY_TS_16K’ o rozmiarze bloku 16 [kB] i rozmiarze pliku 4MB

2. Utworzyć dowolną tabele o nazwie ‘MY_TABLE_16K’ w tej przestrzeni tabel

3. Dodać kilka wierszy do tej tabeli

4. Sprawdzić ile extentów i bloków zajmuje ta tabela.

5. Usunąć przestrzeń tabel ‘MY_TS_16K’

Ćwiczenie 4 – Odzyskiwanie po awarii

1. Zamknąć instancję bazy w sposób czysty (np immediate)

2. Zlokalizować w systemie plików plik danych do przestrzeni tabel ‘MY_TS1’

3. Zmienić nazwę pliku danych tej przestrzeni tabel symulując jego utratę (patrz komendy Linuxa

w opisie laboratorium nr 1 do zmany nazwy plików)

4. Uruchomić bazę danych w trybie OPEN

5. Jeśli baza nie będzie chciała się otworzyć to utracony plik danych wprowadzić w tryb OFFLINE.

6. Przełączyć bazę w tryb OPEN

7. Przywrócić poprawną nazwę plikowi danych komendą Linuxa

8. Przełączyć plik danych w tryb ONLINE

9. Sprawdzić czy można odczytać wiersze tabeli ‘MY_TABLE’



Odpowiedzi do zadań

Ćwiczenie 1 1. sqplus sys as sysdba

a. STARTUP;

b. SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

c. SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

d. SHOW PARAMETER DB_BLOCK_SIZE

e. SYSTEM i SYSAUX

i. SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES WHERE

TABLESPACE_NAME=’SYSTEM’ OR TABLESPACE_NAME=’SYSAUX’

ii. SELECT SUM(BYTES)/1024, SUM(EXTENTS), TABLESPACE_NAME FROM

DBA_SEGMENTS WHERE TABLESPACE_NAME=’SYSTEM’ OR

TABLESPACE_NAME=’SYSAUX’ GROUP BY TABLESPACE_NAME

iii. j.w

2. Korzystając z EM wg. instrukcji w tym skrypcie.

Ćwiczenie 2 1. CREATE TABLESPACE MY_TS1 DATAFILE ‘/u01/app/oracle/oradata/orcl/my_ts1.dbf’ SIZE 2M

AUTOEXTEND ON;

2. ALTER TABLESPACE MY_TS1 ADD DATAFILE ‘/u01/app/oracle/oradata/orcl/my_ts2.dbf’ SIZE 4M;

3. CREATE TABLE MY_TABLE (ID NUMBER, NAME VARCHAR2(20)) TABLESPACE MY_TS1;

4. INSERT INTO MY_TABLE VALUES (1, ‘AAAAAA’); COMMIT;

5. ALTER TABLESPACE MY_TS1 READ ONLY;

6. UPDATE MY_TABLE SET NAME=’BBBBB’;

7. ALTER TABLESPACE MY_TS1 OFFLINE NORMAL;

8. SELECT * FROM MY_TABLE;

9. ALTER TABLESPACE MY_TS1 ONLINE;

Ćwiczenie 3

1. Aby utworzyć przestrzeń tabel o rozmiarze bloku 16kB należy wykonać kilka operacji:

a. ALTER SYSTEM SET DB_16K_CACHE_SIZE=8M;

b. CREATE TABLESPACE MY_TS_16K DATAFILE

‘/u01/app/oracle/oradata/orcl/my_ts_16k.dbf’ SIZE 4M BLOCKSIZE 16K;

2. CREATE MY_ TABLE_16K (ID NUMBER, NAME VARCHAR2(20)) TABLESPACE MY_TS_16K;

3. INSERT INTO MY_TABLE_16K VALUES (1, ‘DDDDDD’); COMMIT;

4. SELECT SEGMENT_NAME, BLOCKS, EXTENTS, BYTES FROM DBA_SEGMENTS WHERE

SEGMENT_NAME='MY_TABLE_16K';

5. DROP TABLESPACE MY_TS_16K INCLUDING CONTENTS;

Zarządzanie strukturą bazy danych Oracle...

Documents

Transcript of Zarządzanie strukturą bazy danych Oracle...