Download - Information Systems Development
Information Systems Development
Slovak University of TechnologyFaculty of Material Science and Technology in Trnava
The DBMS Oracle structure
Oraclesoftware
deveopment tools
Oracleapplications
Oracleend user
toolsOracle
network technology
Oracledatabase system
The DBMS Oracle
The DBMS Oracle
ORACLE ARCHITECTURE
I. database architecture II. software architecture III. database processes IV. data dictionary
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, ...)
Database architecture
F4F5F3
F1F2
F6
database files
online redo log files
control files
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
Database architecture
F1
F1F2
F6
F4F5
physical sight to database files
tablespace SYSTEM tablespace A tablespace B
F2
F4
F6
F3
F5
F3
logical sight
Creating a database
deletes all data in the specified DB files, preparing them for new use;loosing of data for an existing database
creating only possible with DBA grant
create database [database][controlfile reuse][logfile filespec [, filespec] ...][maxlogfiles integer][datafile filespec [, filespec] ...][maxdatafiles integer][maxinstances integer][archivelog | noarchivelog][exclusive]
Creating a database
database name of the database
filespec specification of a db-file in the form of‘filename’ [size integer [k|m]] [reuse]
size integer [k|m]] size of the database in K- resp. MByte
reuse specifies the reuse (overwriting) or not of exi-sting files
controlfile reuse existing control-files will be overwritten
maxinstances number of instances, accessing at the same timeto the database (1-255)
exclusive only one instance can access changes of any settings possible with
alter database
Creating a tablespace
create tablespace tablespacedatafile filespec [, filespec] ...[default storage storage][online | offline]
Creating a tablespace
tablespace name of the tablespace
filespec specification of a file containing the tablespace inform of‘filename’ [size integer [k|m]] [reuse]
size integer [k|m]] size of the database in K- resp. MByte
reuse specifies the reuse (overwriting) of existing files
storage default parameter for all objects prepared in thetablespace, e.g. (initial 10k next 50kminextents 1 maxextents 999 pctincrease10)
online | offline makes the tablespace directly accessable
changes are possible with alter tablespace, drop tablespace, ...
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
Structure of a tablespace
D
I
R
T
create table
create index
createrollback segment
created by Oracleif necessary
database files
(temporary)
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
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;
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
database blockinsert area
minimalfilling
expanding recordsfree range for
pctfree
pctused
(slip)
database blockheader
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)
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)
Structure of records
record header data of the columns
column lengt1 or 3 Byte
data of columnvariable (VARCHAR2)fixed (CHAR)
column length1 orr 3 Byte
record header2 Bytes
number of columns1 Byte
cluster key(optional) 1 Byte
linkaddress for records> blocksize (Byte)
Database architecture
ORACLE-database
tablespace file
segment
data
index
rollback
temporary
table
index
extent
db block
Database architecture
Database architecture
views to the data dictionary:
dba_data_files shows all DB files and there state
dba_tablespaces shows all tablespaces and there state
dba_freespaces shows the free memory within the tablespaces
dba_quota shows the tablespace-quotas of all users
user_extents(all_extentsdba_extents)
shows the extents of all tables and indexes of auser (resp. of all tables / indexes accessible bythe user resp. of the whole database
user_tables shows all tables of a user
II. Software architecture
application processes ORACLE DBMS ORACLE database
DB-C
A
C
H
E
DBWR
LGWR
ARCH
SMON
PMON
CHKP
shared and dedicatedORACLE server processes
ORACLE instance with DB cache (SGA)and background processes
Software architecture
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, ...
III. Process structure
ORACLE database server
DB blockbuffer
R
E
D
O
SharedSQL pool
DBWR LGWR PMON SMON ARCH
database files redo log files into archives putted
1 4 2 3 3 4
1 4 2 4 5
4'
5
DB-Cache
redo log files
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
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)
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)
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)
Processing a transaction
process ORACLE DB cache LGWR DBWR
time
insert into<table>
update <table>
delete from<table>
commit
DB-BLOCKBUFFER
REDO
After committhe redo log bufferwill be restoredto the redo log file
writes asynchroniouslymodified DB blockspreceeded andpossible actualtransactions backto the DB files
redo log files DB files
if ok then go on
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
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
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
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
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
IV. Data dictionary
DATA
DICTIONARY
users
user 1
user 2
user 3
user n
DB resources, tables, indexes, ...
contents oriented view of an Oracle database
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, ...
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
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 ...
Data dictionary
select * fromemployeeswhere salary>4500;
1. checking
2. execution
3. result
ok ?
Oracledata dictionary
- exists the table "employees" ?
- exists a column "salary" ?- what means '*' ?- is there an index to "salary" ?- exists statistical information ?- ...
Oracle user data
- does user have the privileg to access ?
Using the data dictionaryfor processing a SQL statement
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
Database Management
Tasks of database management and administration Database integrity Administration of database privileges Backup / Recovery Database tuning
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
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
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
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
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
Database integrity - transactions
definition: A transaction is a sequence of operations, leading the
database in no- interruptable way from one consistent state to (not necessarly different) consistent state.
features of transactions (ACID): Atomicity (no-interruptability, indivisibility): A transaction is
an atomar unit, will be processed as a whole or not. Consistency (preservation of consistency): The correct
processing of a transaction leads the database from one consistent state into a consistent state again.
Isolation (isolated processing): The transaction must be serializable.
Durability (durability of results): After a transaction is successfully finished, the changes may not get lost. That is because this information is needed for recovery of the consistent state in case of an error.
Database integrity
lost update problem (lost changes): If the transaction T2 reads the value of X, before the
transaction T1 has stored the changes, the result of T1 is lost. temporary update problem (temporary changes):
If a transaction fails during the processing and the changes in the database are already stored, another transaction can read the changed date before the the old state was re-stored.
incorrect summary problem (dirty read): Incorrect values can be caused by e.g. adding up data
elements over several records while another transaction updates one of these data elements.
Oracle processing integrity - protection of integrity by lock mechanism
update employeesset salary = salary * 1.04where name like 'A%' locking granulates
DB table DB block record
locking escalation
Oracle processing integrity -typical locking granulates
locking of tables a transaction locks the whole table for access by other
transactions locking db blocks
locking the DB block for other transactions, not affected blocks of the same table are free
locking records only the affected records are locked, all the others are free
for access
Oracle locking mechanisms -locking modes
operation locking mode remarks
record table
select - - reading access needs no locks
insert
update
delete
DDL / DML-Befehle
X
X
X
-
RX
RX
RX
X
All DML-operations lock on record level;other transaction can read the tables
legend:X - record is eXklusively lockedRX - table is in Row EXclusively mode;
RX can keep by some transactions; no locking escalation
Oracle locking mechanisms -principles
Oracle locking is based on transactions directory within each DB block system change / commit number (SCN) Oracle transaction table within the rollback segment header
update employeesset salary = salary * 1.04where pnr > 7000 ordepnr = 40
insert into employeesvalues(2341, 'Maier', 4500.00)
process 1 process 2 process 3
select *from employees
table 'employees'
Oracle locking mechanisms -processing transactions (default, automatically)
when started transaction gets a transaction number (SCN) managed within the transaction table
all records changed by the transaction get the SCN; record accepts as locked if the record-SCN corresponds with the SCN of an active transaction in the transaction table
unlocking by deleting the SCN from the transaction table after ending the transaction (commit or rollback)
Oracle locking mechanisms -processing transactions (default, automatically)
transaction 1
update <tab>set ...where ...
transaction 2
update ...
3982
3999
4003
4007
db block header
db block
2100 3999 3501 1001 3999
X
X
transaction table
Oracle locking mechanisms -explicite locks
additionally explicite locks with SQL possible
lock table <tab> in <mode> (nowait)
possible modes:X exclusive mode table is exclusively locked; other processes can have only
reading access
S shared mode table is in READ-ONLY-mode locked; other processes canalso in S-mode lock and read; no process can change
RX row exclusive mode default locking of Oracle; as many as you like processescan access in RX-mode and at the same time DML-operations realize
RS row shared mode allows other processes RS- und RX-locking; otherprocesses can read the table, but not lock in X-mode; upda-te change RS-locks into RX-locks
SRX shared row exclusive mode other processes can read the table and lock in RS-mode;X- and S-locks are not allowed
Oracle locking mechanisms -toleration of locking of two DB processes
process 1process 2
X S RX RS SRX
X - - - - -
S - + - + -
RX - - + + -
RS - + + + +
SRX - - - + +
Oracle transaction concept
transaction
update kontoset saldo=saldo-2000where knr=63427
update kontoset saldo=saldo+2000where knr=5546
commit
t0
t1
t2
t3
account 6342710,000 $
subtotalaccount 634278,000 $
subtotalaccount 55464,000 $
account 55462,000 $
totalaccount 634278,000 $
totalaccount 55464,000 $
transactionopened
Oracle transaction concept
commit statement results in a transaction transaction is successfully ended all changes carried out are saved by storing the redo-log-block
into the redo-log-file all locked ressources are cleared
rollback statement results transaction is successfully ended all changed data will be re-changed again to the old value with
the help of rollback segment all locked resources are cleared
rollback must not be complete; transaction can set a <savepoint>
Oracleproblem: consistent read
time time
select *from employees
2000
table'employees'
process 1 process 2
update employeesset salary=salary*1.1where depnr=30
3000 (3300)
2000 (2200)
t1
t2
result:2000 ...3300 (instead of 3000)2200 (instead of 2000)
by the process 2changed dataare also in theresult (dirty read)
problems:locking of the whole table for changes during reading causes loss of performance approval of "dirty read" is not acceptable
Oracleproblem: consistent read
time time
select *from employees
2000
table'employees'
process 1 process 2
update employeesset salary=salary*1.1where depnr=30
3000 (3300)
2000 (2200)
t1
t2
result:2000 ...30002000
consistent stateat the time t1;consistent readwithout locks
3000
2000
Rollbacksegment
Read-Consistency-model for reading without locks
access to several tables in analogue way possible(read-only-transaction) as a snapshot on start-time
Tasks of the DB administration
allocation of user identification number registration in DB table inclusively password checking of access privileges at each login; makes together with
the terminal identificator possible to identify the user trying access without privileges
registration of the person responsible of write operations in the systems protocol (user-ID + terminal-ID)
in case of illegal access the database audit following the system protocol determines cases of illegal access during a certain period of time (user-ID, ...)
Access control
access control saves that all information in the database only by authorized user is read or changed resp. in the authorized format and is transported and processed only on authorized ways
Access control: functions
identification means the registration of an user in the system with the
specification of his user-ID authentification
means the checking process by the system to save that the user logging in with a definite password is also in fact this user
authorization is the act of allocation the grants for access to the user. Who
is allocating grants must have the grants themselve with grant option.
Requirements on access protection
graded units of protection definition of several grades of protection and several
granularity of protecting objects must be possible clear interfaces
access to the database has to be possible only via this defined interfaces
decentral authorization access grants to distinguished groups of data should be
allocated and revoked dynamic authorization
allocating and revoking of grants must be possible during running the database system
Requirements on access protection
Different criteria of decision for precise adjustment to objective, legal and other facts the
access grants should depend on several criteria dataflow and inference control
storing of protected data and so making them accessable for other users is not allowed even for authorized users; remaining and using must be controlled
little influence on system performance integral access control in the DMBD
access control must be considered from the beginning of implementation
Concepts of protection
isolation (no common use of data) data and programs are only available for the owner; but in
contradiction to the target of large database systems unlimited access control
as many as you like users use all data commonly and have determined grants to the objects (data, programs, terminals)
principle of smallest as possible privileges users get access grants to smallest possible units (access
keys, values of attributes, ...); but not to storage media, files, record types, ...
Concepts of protection
partial ordering of privileges of using e.g. hierarchical order of user groups and therefore of access
grants (partial) ordering of access operations
e.g. hierarchical order of access operations; who has determined access grants has these also for all subordinated operations (e.g. who is allowed to update is allowed to read too)
order of objects needs of protection classification of information on needs of protection
Allocating grants in SQL
allocating grants in SQL:
all on <table>grant <list of grants> to <list of users>
all but <list of grants> [with grant option]
revoking grants in SQL:
all on <table>revoke <list of grants> from <list of users>
all but <list of grants>
{
{
}
}
Access grants
object privileges define allowed operations on database objects like
tables views PL/SQL programs sequences
system privileges define operations being able to carry out on the database resp. within the database server like
creating a table (‘create table’ privilege) alter a tablespace (‘alter tablespace' privilege) creating a roll back segment (‘create rollback segment’
privilege)
Access grants
users privileges databasetab1
tab2
view1
proc1
insert, select, update
execute
select
update (a,b)
create tablespacealter rollback segment
Object privilegescan be defined by grant statement (Form III)
grant obj_priv [, obj_priv] ... | all [privileges]
on [user.]object to {user | public } [, user] ...
[with grant option]
obj_priv for tables one of the following: ALTER, DELETE, INDEX, INSERT,REFERENCES, SELECT, UPDATE
for views one of the following: DELETE, INSERT, SELECT, UPDATE
for sequences: ALTER, SELECT
update and references privileges can explain constraint for determinedcolumns: grant col_priv [, col_priv] (col , col ...) on [user.]table to {user | public} [, user] ... [with grant option]
all [privileges] specifies all referred object privileges
on [user.]object specifies the refered object
public specifies all users, futural too
with grant option specifies that the receiver of the grant can pass this grant on other users
System privileges
can be specified by grant statement (Form I); DBS privileges necessary
grant db_priv [, db_priv] ...
to user [, user] ... [identified by password [, password] ...]
db_priv DBA: allows to avoid default privileges und to take over DBA functions like createtablespace, create rollback segment; allows select on any tables and views, create forDB objects of other users, drop for DB objects of other users, grant for different DBprivileges, create for public synonyms and DB links, complete DB export and import
CONNECT: allows the user to connect the DB and to process DB objects he ownes thegrants for; can create views, synonyms and DB links
RESOURCE: allows to create DB objects with unlimited resources on all tablespaces
user specifies a new or existing DB user name; existing one gets the grants additionally, a newone will be created with the specified grants
password specifies the password of each specified user
Available system privilegesanalyze anyaudit anyaudit system
create clustercreate any clusteralter any clusterdrop any cluster
alter databasecreate database linkcreate any indexalter any indexdrop any index
grant any privilege
create procedurecreate any procedurealter any procedureexecute anyprocedurecreate profilealter profiledrop profilealter resource cost
create publicdatabase linkdrop public databaselink
create rolealter any rolegrant any role
create rollbacksegmentalter rollback segmentdrop rollback segment
create sessionalter sessionrestricted session
create sequencecreate any sequencealter any sequencedrop any sequenceselect any sequence
create snapshotcreate any snapshotalter any snapshotdrop any snapshot
create synonymcreate any synonymdrop any synonym
alter system
create tablecreate any tablealter any tablebackup any tabledrop any tablelock any tablecomment any tableselect any tableinsert any table
update any table
delete any table
create tablespacealter tablespacemanage tablespacedrop tablespaceunlimited tablespace
force transactionforce any transaction
create triggercreate any triggeralter any triggerdrop any triggercreate userbecome useralter userdrop user
create viewcreate any viewdrop any view
Resource privileges
can be defined by grant statement (Form II)
DBA grant necessary
grant resource [ (quota [k|m] ) ]
on tablespace to { public | user [, user] ...}
resource specifies that the user is allowed to create DB objects in the specifiedtablespace
quota specifies the amount of memory in Kbyte or Mbyte allowed to be consumedby the user; specifying 0 revokes the grant
tablespace name of an existing tablespace
user specifies an existing username
Concept of roles
individual allocation of privileges causes high expenditure of management
remedy: definition of roles / task profiles ('create role' privilege necessary) :
create role rolename; with grant statements roles get privilegs several and as many as you like roles can be allocated to
users
Concept of roles
create role in92;
grant select,inserton tab1
grant create tablespace,create any view,manage tablespaceto in92;
to in92;
grant in92 to in95;
grant in92 to benny;
grouped privilegesindividual privileges
States of roles
categories of roles: available roles: all roles allocated to a user by
grant default roles: after Oracle logon already active
roles; defined by alter user statement, must be allocated to a user by grant
active roles: actually for a user activated roles; can be activated and deactivated during a session with set role ('set role' privileg necessary)
deactivated roles: currently deactivated roles of the user
States of roles
a
b
c
d
e
select *from user_role_privs;
shows all roles of the user
available roles
default roles
bd
alter user bennydefault role b, d;
logon
select *from user_role_privs;
'DEF'-Column= YES (default)NO (no default)
b
dc
select *from session_roles;
activated roles
deactivated roles select granted_rolefrom user_role_privsMINUSselect rolefrom session_role;
b
dc
ae
Operations with roles
create a role create role rolename;
giving privileges to a role grant sys_privs, obj_privs to role;
allocating roles to users / roles grant role1 to user / role2
revoking role from user / role revoke priv / rolle from user / role
activate / deactivate roles set role role1, ...set role all;set role all except role1, ...set role role identified by pw;
define role as default role alter user user default role role, ...
drop a role drop role role;
Backup - principles
Backup – is a way how to save a data.The replicas of data include part of database - control files and data files.
Backup is possible to use for: database, tablespace, tables, materialized views datafiles, control files or archived redo logs. Backup is possible to perform:
• with Export utility • with Recovery Manager
Backup - Export utility • EXP MSADMIN/msvp PARFILE= E:\oracle\exp\MSVP.EXP
MSVP.EXP obsahuje:FILE=E:\oracle\exp\MSVPEXP.DMPLOG=E:\oracle\exp\INFOEXP.TXTOWNER=MSADMINBUFFER=8192
• IMP Scott/Tiger PARFILE=E:\oracle\exp\MSVP.IMP
MSVP.IMP obsahuje:FILE=E:\oracle\exp\MSVPEXP.DMPLOG=E:\oracle\exp\INFOIMP.TXTFROMUSER=MSADMINTOUSER=ScottBUFFER=8192
Restore
Restore – is a process that use backup for renew lost or damaged files.
Using of restore:
command cp – in Unix command restore – in RMAN (Recovery Manager).
Backup and Restore
Recovery - principles
database as a whole must be consistent in the most varied error situations, competition control protects consistency in detail only
target: no approval of data loss kinds of errors:
abnormal end of a user program that carried out changes without reaching commit
abnormal end of the database system (crash of the DB server)
abnormal end of the operating system (crash of the operating system)
loosing database files (disk error)
Recovery - principles
recovery procedures of Oracle:
process recovery (if the user program was crashed) instance recovery (if the DB server or the operating system
was crashed) medium recovery (in case of loss of database files)
Recovery - run of a transaction
update employeesset salary=2000where salary=1000;
commit;
process DB cache
DB buffer rollback segmentbuffer
redo logbuffer
1000
2000
1000 1 RS 1000
1 DW 2000
1 commit
DB files
redo logfiles1000
0
1 3
4
5
2
1 RS 1000
1 DW 2000
1 commit
6
Recovery - run of a transaction
0. read the DB block into the DB cache (if not already available); lock the affected DB record
1. copy the actual value into the rollback segment buffer
2. changing within the DB block (1000 ---> 2000)
3. rollback segment changes will also be protocolled into the redo log buffer (neuer item in RS: 1000)
4. new value of data will be protocolled (DW 2000)
5. end of transaction by commit, item in redo log buffer
6. commit causes writing of the redo log buffer into the redo log file (LGWR process)
7. end of commit message to the user program after successful writing of the redo log file
DB file remains unchanged; but all changes are permanent
Process recovery
example: value ‘salary‘ changed from 1000 to 2000; afterwards crash
of the user program without commit result:
record is changed in DB cache (new value 2000) record is exclusively locked by the crashed program entries in rollback segment and in redo log buffer are
done
Process recovery
solution: PMON process checks (ca. all 20 sec) whether DB
resources of no longer existing process are locked process number in DB cache available, process in
operating system not active PMON initializes rollback of the opened transaction (old
value from the rollback segment will be copied into the DB block, record will be unlocked)
Process recovery
update employeesset salary=2000where salary=1000;
process DB cache
DB buffer rollback segmentbuffer
1000
2000
1000
2000
1000
1000
crash of theuser process
comment
PMON background process:recognizes thatthe user does not exist
rollback:old value from RSwill be restored;unlocking
Instance recovery
update employeesset salary=2000where salary=1000;
commit;
process DB cache
DB-Puffer rollback segmentbuffer
redo logbuffer
1000
2000
1000 1 RS 1000
1 DW 2000
1 commit
DB files
redo logfiles
1000
update departmentset city='Leipzig'where city='Halle'
Halle
Leipzig
Halle 1 RS Halle
1 DW Leipzig
1 RS Halle1 DW Leipzig
Halle
1 RS 10001 DW 20001 commit
situation at the time of system crash:
several transactions are affected
Instance recovery
SMON process:execute a roll forward for each closed transaction from the redo log file (commit
exists), a rollback for each open transaction (commit doesn’t exist)start the recovery from the check point
SMON background process
DB files
1000
2000
1000
Halle Halle
Leipzig
1 RS 1000
1 DW 2000
1 commit
2 RS Halle
2 DW Leipzig
123
45
6
redo log files
Instance recovery transaction 1:
1. RS item will be written from the redo log file to the DB into the RS (1000)
2. changed value will be written into the DB (2000)
3. commit record in the redo log file affects deletion of the RS item
transaction 1 completely recreated transaction 2:
4. RS item will be written from the redo log file to the DB into the RS (Halle)
5. changed value will be written into the DB (Leipzig)
6. commit record in the redo log file is absent and will be recreated with help of the RS item
transaction 2 is cancelled
Medium recovery
requirements: DB have to run in archive mode periodically backups have to be made redo log files have to be archived (ARCH process)
in case of problems the DBA has to bring back the archived files manually
in Oracle several backup / recovery methods (online/ offline) are available
DB tuning - idea
tuning process, to provide optimal performance on a given platform and in a determined application and datastructural profile
DB tuning - tuning levels
datastructure logical structure of DB objectsindex structurecluster (index / hash)sequences
SQL operations SQL optimization (statistical / rule based method)determination of statistics of the DB objects
application kind of configurationintegrity constraints in the DB server / application
(declarative / procedural method)stored PL/SQL programsdiscrete transactions
DB server DB cache optimization (DB block buffer pool / shared poolfor SQL objects / data dictionary)
checkpointing
database object distribution to different disksrollback segments (number / distribution / number of
transactions per RS)redo log file (size / disk)
DB tuning - datastructure level
influences: logical structure of tables appropriate index structures primary key creates automatically an unique index for foreign key there should be non unique indexes
(referencing creates then only an index entry lock) indexes to columns and combinations of columns only if
necessary and selectivity is expected
DB tuning - SQL statement level SQL optimizer:
works with rules corresponding to evaluation of where clauses
based on statistics created with the help of the analyze statement and stored in the data dictionary (Oracle default method), e.g.:
for a table: number of records number of DB blocks of the table number of not used DB blocks averagely free space within the DB blocks number of chained records number of unique values per column
DB tuning - SQL statement level
for an index: minimal / maximal value of each column height of the index tree number of index blocks on sheet level number of unique values within the index minimal / maximal value of the index average number of index blocks per index value average number of DB blocks per index value
additional possibility: information for faster access
DB tuning - SQL statement level
possibilty to trace: set Oracle session by alter session statement sql_trace=true
and collect information in a trace file, e.g. schedule for access of each SQL statement CPU time for execution / parsing of a statement number of physical / logical I/O's number of executions of a statement number of parsing processes
evaluation with utility tkprof
DB-Tuning - application level
server mode (multithreaded server mode: many applications served by few server processes / dedicated server mode: big DB loadings)
use of declarative or procedural integrity constraints stored PL/SQL procedures (in the shared pool on the server;
little net loadings)
DB tuning - DB server level
size and configuration of the DB cache size of the DB block buffer (should be so that for one
physical read operation from disk there are 10 to 20 logical read operations from DB cache)
number of DB block buffers size of the shared pool
SQL statements, PL/SQL programs, DB trigger data dictionary information
DB tuning - database level
redo log files on the fastest disks tablespace SYSTEM only used for data dictionary information temporary segments in an extra tablespace rollback segments in an extra tablespace provides rollback segments always with storage parameters data segments and index segments of the same DB object in
different tablespaces on different disks