Information Systems Development

1. Slovak University of Technology Faculty of Material Science and Technology in Trnava Information Systems Development

2. The DBMS Oraclestructure

3. The DBMS Oracle

4. The DBMS Oracle

5. ORACLE ARCHITECTURE • I. database architecture • II. software architecture • III. database processes • IV. data dictionary

6. I. Database architecture • physical structure: • as many as you like DB files distributed to as many as you like disks; containing all database objects like data structures, procedural objects, access structures, data • at least two redo log files; protocolling and storing all changes of data; serve for recovery of the database after system crash • at least two control-files; containing the basic structures and basic information about an Oracle database (moment of creation, names of all DB files, names of all redo log files und the sequence number of this, moment of the last checkpoint and corresponding redo log files, ...)

7. Database architecture

8. Database architecture • logical structure of the database files: • DB files are assigned to tablespaces • for each database at least one tablespace exists (SYSTEM), created at the same time with database; further can be created • all database objects will be assigned to a tablespace • creation possible only with DBA grant

9. Database architecture

10. Creating a database

11. Creating a database

12. Creating a tablespace

13. Creating a tablespace

14. Structure of a tablespace • types of objects resp. kinds of segments in a tablespace: • tables (data segments) • indexes (index segments) • rollback segments • temporary segments • each table, index, rollback segment is assigned to exactly one segment in the concerning tablespace; temporary segments will be created by Oracle • assignment to a tablespace depends on • tablespace of the objects owner • owner options • options in the create statement of the object

15. Structure of a tablespace

16. Structure of segments • segment is created by extents (= memory ranges of determinable size) • differentiation between initial extents and next extents • next extents can have an increasing factor additionally (pctincrease) • extent contains Oracle datablocks of mostly 2 KByte • specification of storing parameters in create / alter e.g.. for • tablespace • table • index • rollback segment

17. Structure of segments • example: storage ( initial 20 MB, next 10 MB, minextents 3, maxextents 80, pctincrease 15, freelist 3) • supervision of tablespaces and tables by view on data dictionary: select * from user_tablespaces; select * from user_tables;

18. Structure of database blocks • smallest units of a database are blocks; are stored in the extents of segments • configuration of datablocks by pctfree, pctused, initrans, maxtrans

19. Structure of database blocks • database blockheader contains management information (row directory, transaction directory) • data range is divided in pctfree und pctused; important for varchar2-data (pctfree) and balanced filling of all blocks if possible (pctused)

20. Structure of records • smallest logical units of the database; containing record header and record body • record header: min. 3, max. 5 Bytes • record body: data as specified by create table statement incl. column length, but not absolute in the defined order (LONG und LONG ROW always at the end)

21. Structure of records

22. Database architecture

23. Database architecture

24. Database architecture

25. II. Software architecture

26. Software architecture

27. Software architecture • database system consists of • ORACLE instance and • several server processes being part of them • ORACLE instance consists of • database cache (Shared Global Area SGA) and • several background processes • ORACLE server processes (usually several activ at the same time) take it up to do e.g. • parsing of SQL statements, • executing of SQL statements, • reading of DB blocks from DB files into the DB cache, • ...

28. III. Process structure

29. Oracle processes: database write process (DBWR) • writes DB blocks changed in the DB cache back to the DB files • is started when • all DB blocks in the cache are covered and space in the memory is needed (blocks not used longest time are first restored) • number of modified blocks in cache is too large • not activated by an external event for a too long time (DBWR timeout ca. all 3 seconds) • a checkpoint is reached (all changes will be restored, fileheader of DB files, control files and redo log files will be actualized; database is in a defined state) • when activated synchronization with LGWR process necessary

30. Redo log writer process (LGWR) • restoring of changes in Oracle not synchroniously with commit, but "any time" asynchroniously • DB changes are additionally protocolled in the redo log buffer (old + new value) • LGWR writes redo log buffer to the redo log file when • redo log buffer is filled up to 80 percent • an user process signalizes an end of transaction • the DBWR process signalizes a writing process (LGWR writes before)

31. Redo log writer process (LGWR) • at the end of a transaction (caused by commit) after the writing process the user process will receive the commit end message, ensuring that • all finished transactions are made permanent in the redo log file (safety) • all modified DB blocks will be kept as long as possible in the DB cache in the memory (performance) • all transactions get very quickly the commit end acknowledgement (commit) when finished, because max. one redo log buffer has to be written to the disk (performance)

32. Redo log writer process (LGWR) • at a checkpoint the LGWR has additional tasks as follow: • marking all modified DB blocks in the DB cache und so prepare them for restoring by the DBWR • actualize all fileheaders relating to checkpoint • write the actual redo log buffer to redo log file • signalize the checkpoint to the DBWR process; DBWR then writes all marked DB blocks to the DB files • for highly stressed DB systems checkpointing is optional a separate process (CHKP)

33. Processing a transaction

34. Process monitor (PMON) • checking in periodically • user processes have to be stopped by the systemmanager • programs not regularly finished due to crash and that have locked database ressources • unlocking and rollback the changes of this transaction

35. System monitor (SMON) • checking for opened transactions resp. not restored changes when database is started • reason is e.g. not regularly finished DB working if • closing the database by the DB administrator with shutdown immediate or abort • crash of the DBMS or of the operating system • hardware error, power fall out • starts the recovery automatically; all completed transactions will written with help of the redo log files to the DB files, all uncompleted transaction will be rollbacked

36. Archieving process (ARCH) • optional background process • when DB is started in ARCHIVE LOG mode with the redo log files it can be reconstructed • ARCH copies redo log files to another medium, actualizes the control files

37. Database cache • DB block buffer • DB blocks requested by the application program will be read into the DB cache, there processed and available for the user process • blocks in the DB cache can be used by several processes (good tuning means 9 logical reads related to one physical read from disk) • DB blocks are subject of last recently used algorithm (LRU); blocks not used for the longest time will be first restored if possible

38. Database cache • shared pool contains • parsed SQL statements, compiled functions, procedures, data dictionary information and DB trigger (shared SQL region for public use) • private SQL region for session oriented private information • are also subject of LRU-algorithm • instance information • registration of locked resources, order for processing a queue, active transactions with information about state, ... • db cache parameters are defined in init.ora

39. IV. Data dictionary contents oriented view of an Oracle database

40. Data dictionary • Oracle database consists of • data dictionary, storing all data responsible for regular function of the database = database for the management of database objects • as many as you like users with different privileges • database objects like tables, views, indexes, procedures, DB triggers, ...

41. Data dictionary • data dictionary • consists of system tables, accessible by SQL • internal level = real tables; no accesss by normal users • external level = views to system tables • data dictionary tables • when creating a database the users SYS, SYSTEM and PUBLIC will be created automatically • SYS is owner of all data dictionary tables (internal level) and of all data dictionary views (external level) = highest privileged user • SYSTEM is owner of DB tables for Oracle tools • PUBLIC is a user, representing all users of an Oracle system; all users have privileges of PUBLIC

42. Data dictionary • data dictionary content • users and their privileges • tables and their column names and datatypes • statistics about tables and indexes • index information • access privileges to tables • profile information and allocation to the users • free space management • ...

43. Data dictionary Using the data dictionary for processing a SQL statement

44. Data dictionary views • each view exists in 3 groups, accessible with the assigned prefix in SQL: • USER consists all DB objects, where the user is owner z.B.: select * from user_tables • ALL consists all DB objects, where the access by the user is possible z.B.: select * from all_tables • DBA consists all database objects, where DBA privileges are necessary = overall view to the database z.B.: select * from dba_table

45. Database Management • Tasks of database management and administration • Database integrity • Administration of database privileges • Backup / Recovery • Database tuning

46. User groups and their tasks • DBMS-designers / -implementors: • Database designers: • development of DBMS • problem analysis • establishing the schema • Applications programmers: • realization of the end-user demanded applications

47. User groups and their tasks • Database administrators: • maintenance of data / of the system • user support • minimization of expense for database management • End-users: • predefined queries (parameterized user) • ad hoc queries (occasional user

48. Tasks in the management phase • system maintenance and monitoring • keep documentation of the DBS • keep statistics (access behavior, memory requirements, ...) • monitoring of security protocols • management of authorizations / creating new users • system changes • actualization of system documentation • coordination and planning of the hardware development

49. Tasks in the management phase • system changes – cont. • coordination of software maintenance • extensions of the conceptual schema • changes of the internal schema (optimizations) • database administration • allocation of user-id's • granting / revoking of access privileges

50. Oracle database administration • initialize the database • start and stop the database • structuring the database • definition of tablespaces • file allocation for tablespaces • distribution of database files to disks • definition of rollback segments (size, count, place, ...) • development and implementation of strategies for backup / recovery of the database • development and implementation of security concepts • monitoring of the databse activities • tuning the database