7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 1/7

BST 3101: DATABASE SYSTEMS IITopic 4 

Introduction

A key responsibility of the database administrator is to prepare for the possibility of hardware, software, network, process, or system failure. If such a failure aects the

operation of a database system, one must usually recover the database and return

to normal operation as quickly as possible.

Note:

All database reads/writes are within a transaction. Transactions must comply with

the ACI rules. A data recovery subsystem !uarantees Atomicity and urability,

while concurrency control !uarantees Isolation. The application pro!ramme on theother hand ensures Consistency.

atabase "ecovery

ata recovery is the process of restorin! data that has been lost, accidentally

deleted, corrupted or made inaccessible for any reason. The term typically refers to

the restoration of data to a desktop, laptop, server, or e#ternal stora!e system from

a backup. "ecovery may be required due to physical dama!e to the stora!e device

or lo!ical dama!e to the $le system that prevents it from bein! mounted by the

host operatin! system.

atabase ata %oss can be caused by hardware dama!e &at the stora!e device

level such as hard disk'( $le system dama!e &controlled by the operatin! system',

the database $le system stores and or!ani)es system and user $les, and individual

$le dama!e &$le may be needed as data loss can occur within the $le itself if the

internal structure is corrupt'.

"ecovery should protect the database and associated users from unnecessary

problems and avoid or reduce the possibility of havin! to duplicate work manually.

 The recovery system ensures the database contains e#actly those updates

produced by committed transactions.

*

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 2/7

Causes of Database Errors and Failures

+everal problems can compromise the normal operation of database. The followin!

are the most common

User Error 

 There are various possible user errors, includin! accidentally droppin! a table. -ser

error can be minimised by increased trainin! on database and application

principles.

Statement Failure

+tatement failure occurs when there is a lo!ical failure in the handlin! of astatement in the application pro!ram. or e#ample, assume all e#tents of a

database are allocated, and completely $lled with data so that the table is

absolutely full. A valid I+0"T statement cannot insert a row because there is no

space available. Therefore, if issued, the statement fails.

Process Failure

A process failure is a failure in a user, server, or back!round process of a database

instance such as an abnormal disconnect or process termination. 1hen a process

failure occurs, the failed subordinate process cannot continue work, althou!h theother processes of the database instance can continue.

Network Failure

1hen the system uses networks such as local area networks and phone lines to

connect client workstations to database servers, or to connect several database

servers to form a distributed database system, network failures such as aborted

phone connections or network communication software failures can interrupt the

normal operation of a database system.

Database Instance Failure

atabase instance failure occurs when a problem arises that prevents the database

instance from continuin! to work. An instance failure can result from a hardware

problem, such as a power outa!e, or a software problem, such as an operatin!

2

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 3/7

system crash. Instance failure also results when the user issues a +3-T41

A54"T or +TA"T-6 4"C0 statement is issued.

Media (Disk Failure

An error can arise when tryin! to write or read a $le that is required to operate a

database. This occurrence is called media failure because there is a physical

problem readin! or writin! to $les on the stora!e medium. A common e#ample of 

media failure is a disk head crash, which causes the loss of all $les on a disk drive.

+tructures -sed for atabase "ecovery

Database Backus

A database backup consists of backups of the physical $les &all data$les and a

control $le' that constitute the database. To be!in media recovery after a media

failure, a database uses $le backups to restore dama!ed data$les or control $les.

"eplacin! a current, possibly dama!ed, copy of a data$le, tablespace, or database

with a backup copy is called restoring that portion of the database.

 There are several options in performin! database backups, includin!

&i' "ecovery 7ana!er&ii' 4peratin! system utilities&iii' 0#port utility&iv' 0nterprise 5ackup -tility

T!e "edo #o$

 The redo lo! records all chan!es made in the database. The redo lo! of a database

consists of at least two redo lo! $les that are separate from the data$les &which

actually store a database8s data'. A database8s redo lo! can consist of two parts, the

online redo lo! and the archived redo lo!.

9

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 4/7

"ollback Se$%ents

"ollback se!ments of a database store the old values of data chan!ed by on!oin!

transactions for uncommitted transactions.

Amon! other thin!s, the information in a rollback se!ment is used durin! database

recovery to undo any uncommitted chan!es applied from the redo lo! to the

data$les. Therefore, if database recovery is necessary, then the data is in a

consistent state after the rollback se!ments are used to remove all uncommitted

data from the data$les.

"eco&er' Catalo$

 The "ecovery 7ana!er maintains a repository called the recovery catalog, which

contains information about backup $les and archived lo! $les. "ecovery 7ana!er

uses the recovery catalo! to automate both restore operations and media recovery.

 The recovery catalo! contains

&i' Information about backups of data$les and archive lo!s&ii' Information about data$le copies&iii' Information about archived redo lo!s and copies of them&iv' Information about the physical schema of the tar!et database

&v' amed sequences of commands called stored scripts

"eco&er' Mana$er

 The "ecovery 7ana!er is a utility that manages the processes of creatin! backups

of all database $les &data$les, control $les, and archived redo lo! $les', and

restorin! or recoverin! $les from backups. It propa!ates information about backup

data$le sets, archived redo lo!s, backup control $les, and data$le copies into the

recovery catalo! for lon!:term retention.

1hen doin! a restore, the "ecovery 7ana!er e#tracts the appropriate information

from the recovery catalo! and passes it to the database server. The server performs

various inte!rity checks on the input $les speci$ed for a restore.

;

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 5/7

Control iles

 The control $le of a database is a small binary $le necessary for the database to

start and operate successfully. A control $le is updated continuously by 57+durin! database use, so it must be available for writin! whenever the database is

open. If for some reason the control $le is not accessible, the database will not

function properly. Amon! other thin!s, a control $le contains information such as

&i' The database name&ii' The timestamp of database creation&iii' The names and locations of associated data$les and online redo lo! $les&iv' Tablespace information&v' ata$le o<ine ran!es&vi' The lo! history

&vii' Archived lo! information&viii' 5ackup set and backup piece information&i#' 5ackup data$le and redo lo! information&#' ata$le copy information&#i' The current lo! sequence number&#ii' Checkpoint information

DATABASE SEC("ITY 

atabase security is the ran!e of methods used to protect information stored within

a database. The term refers to the processes and procedures undertaken to prevent

unauthorised access to the database. 3ackin! attempts are the most common

ha)ard to database information, but there are many other dan!ers.

 The most common database security vulnerabilities areDeplo!ment Failures

 The most common cause of database vulnerabilities is a lack of due care at themoment they are deployed. Althou!h any !iven database is tested for functionalityand to make sure it is doin! what the databases is desi!ned to do, very few checksare made to check the database is not doin! thin!s it should not be doin!.

"roken Databases The +=% +lammer worm of 2>>9 was able to infect more than ?> percent of vulnerable computers within *> minutes of deployment, takin! down thousands of databases in minutes. This worm took advanta!e of a bu! that was discovered in7icrosoft8s +=% +erver database software the previous year, but few systemadministrators installed a $#, leavin! computers vulnerable.Data #eaks

@

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 6/7

atabases may be considered a back end part of the oBce and secure fromInternet:based threats &and so data doesn8t have to be encrypted', but this is notthe case. atabases also contain a networkin! interface, and so hackers are able tocapture this type of traBc to e#ploit it. To avoid such a pitfall, administrators shoulduse encrypted communication platforms.

Stolen Database "ackups4r!anisation insiders are also likely to steal archives includin! database backups whether for money, pro$t or reven!e. This is a common problem for the modernenterprise, and businesses should consider encryptin! archives to miti!ate theinsider:risk.

T$e %buse o& Database FeaturesIt has been established that every database e#ploit has been based on the misuseof a standard database feature. or e#ample, a hacker can !ain access throu!hle!itimate credentials before forcin! the service to run arbitrary code.

#ack o& Se're'ation

 The separation of administrator and user powers, as well as the se!re!ation of duties, can make it more diBcult for fraud or theft undertaken by internal sta. Inaddition, limitin! the power of user accounts may !ive a hacker a harder time intakin! complete control of a database.

Securit! #oop$olesCybercriminals may opt to $nd a weakness within the infrastructure that can beused as levera!e for more serious attacks until they reach the back:end databasesystem.

S# In)ectionsA popular method for hackers to take, +=% inDections remain a critical problem in the

protection of enterprise databases. Applications are attacked by inDections, and thedatabase administrator is left to clean up the mess caused by unclean variables andmalicious code which is inserted into strin!s, later passed to an instance of +=%server for parsin! and e#ecution. The best ways to protect a!ainst these threats areto protect web:facin! databases with $rewalls and to test input variables for +=%inDection durin! development.

Sub*Standard +e! Mana'ement Eey mana!ement systems are meant to keep keys safe, but research has foundencryption keys stored on company disk drives. atabase administrators sometimesfalsely believe these keys have to be left on the disk because of database failures,but this isn8t true and placin! such keys in an unprotected state can leavesystems vulnerable to attack.

Database Inconsistencies+ystem administrators and database developers need to develop a consistentpractice in lookin! after their databases, stayin! aware of threats and makin! surethat vulnerabilities are taken care of. This isn8t an easy task, but documentation andautomation to track and make chan!es can ensure that the information contained inenterprise networks is kept secure.

F

7/21/2019 bit-3107-4

http://slidepdf.com/reader/full/bit-3107-4 7/7

Control Measures

Installin! a database $rewall, a protective barrier that keeps all unknown

connections at bay, is the most basic form of database security. irewalls are

installed on most computers and are made so hackers have a diBcult time

connectin! to a victimGs computer. irewalls work by $lterin! throu!h connections in

the network and only allowin! trusted computers or users to access the database.

Encr'tion is another database security measure in which the data are encrypted

or made ille!ible for anyone who accesses the database. 1hen encryption is used,

an al!orithm scrambles the characters into nonsense, so it cannot be read. This

means that, unless the hacker has speci$c knowled!e of the encryption key, the

information one needs to chan!e the encrypted data from ille!ible characters back

to a le!ible format, there is no way he or she can read the database.

Auditin$ is when a supervisor, or database mana!er, scans the database to ensure

nothin! has chan!ed. This type of database security can either be performed

physically, by someone readin! over the database, or by usin! a pro!ram for lar!er

databases to see if the codin! is the same.

)erfor%in$ a database backu  is a database security measure that protects

a!ainst many dierent threats. 1hen the database is backed up, this means the

data are stored in another area or medium. If the database loses any or all

information, it can be promptly restarted with minimal loss usin! the backup. 5y

doin! a database backup, administrators are able to !uard a!ainst physical dama!e

to the computer such as from a $re, database corruption or the database shuttin!

down from bein! overloaded.

H