topic 5 keselamatanpd

8/3/2019 TOPIC 5 KeselamatanPD

http://slidepdf.com/reader/full/topic-5-keselamatanpd 1/21

CHAPTER 5

DATABASE BACKUP AND RECOVERY



DATABASE SYSTEMCHAPTER 5: Database Backup And Recovery

76

Objectives

Describe backup and reservation and their function in managing database.

Explain techniques of database reservation.

Describes how to create a database backup.

Describes data security and how data can be controlled




77

6.1. Backup and Recovery

Backup means to do copies of data files. If unexpected things happen, such as data lost or

corrupted, reservation can be done and processing can be continued.

6.1.1 Why We Need Backup

When a transaction is sent to a DBMS for execution, the system will make sure that all

transactions are successfully completed and the effect of the transaction will be recorded into the

database. But during the transaction, there may be physical errors such as from the system,

transaction and internal errors.

That is why reservation needs to be done for error recovery.




78

Backup operation

Restore operation

Database

Media or Disc

Figure 6.1: Illustration describing the backup and recovery process




79

6.1.1.1 Types of Error

a) Computer Error

It means error in the computer system, either the hardware or software during the

execution of transaction. Failure in hardware may cause the lost of content in computer’s

memory.

b) Transaction and System Error

When there is an error, for example deadlock , during system entry, transaction cannot be

executed.

c) Internal error

In certain situation, there are several things that cannot be executed during transaction

and the transaction will stop. For example, data cannot be found during transaction.




80

Concurrency Control

The concurrency will decide whether to stop the transaction or restart if there is a crash

possibility during transaction, and a few more in a deadlock position.

e) Disc Error

Disc error is when the contents of decision are lost, caused by either damage to the data

head or during data transmission operation.

f) Internal Error

A situation when there are things that cannot be executed during transaction and the

transaction will be stopped. For example, data cannot be found during transaction.Physical problem

It refers to normal problems such as shortage of electricity, air conditioning failure, fire,

and sabotage and disk error.




81

Transaction and System Concept

-The execution of program that involves the operation of any access on any database is namedas database transaction, or simply named as transaction.

- If database operation does not change the data in the database but only accept data, it is called

read-only transaction.

Transaction Location and Additional Operation

- Transaction is a working unit whether complete or otherwise.

- For recovery purposes, the system needs to know when a transaction start, end and arrive at

commit point or stop.

- Below is the definition of a few operations.




82

BEGIN_TRANSACTION Transaction begins.

READ or WRITERead and write operation on items intodatabase during transacting

END_TRANSACTION

Read and write and transaction process iscompleted. But, it is necessary to evaluate

the point, whether the point needs to be keptin the database, or if it stops because of certain reasons.

COMMIT_TRANSACTIONTransaction signal has successfully beencompleted without repeating.

ROLLBACK (ABORT)Transaction signal has not successfully beencompleted. Thus, any changes happenedfrom the transaction has to be repeated.




83

There are recovery techniques that needs internal operation, which covers:

- UNDO

Repeat only one operation from all transaction.

− REDO

A few transaction need to be repeated to ensure that all operations meet the acceptance point

and being recorded into the database.




84

6.1.2 Techniques of Recovery

6.1.2.1 System Log

- In recovery of failure, system must always have a log system.

- Log system is also recognised as journal

- Log refers to the modification structure in the database.

- Log needs to keep records detail of transaction and will affect the items in the

database.

- Information held by log is needed during recovery process and during function failure.

- Log is kept inside a disc. So it is not disturbed during failure except for physical

problem and disc error and physical problem.

- Every log record will describe one database and usually has:




85

a) Transaction Name

A unique name will be given to transaction to inform the start off point in data writing process

b) Item Name

Item’s unique name

c) Old Value

Data value during writing data time period

d) New Value

Data value acquired after immediately after the writing process is completed.

There are two techniques to find out the transaction error




86

There are two techniques to find out the transaction error

i. Delaying Correction of Database

Delaying Database Correction Technique means that to hold any changes in database until the

execution of transaction ended and meet the commit point.

During transaction, changes will only be recorded inside log and internal work area.

When transaction meets the commit point, changes will be written in the log and recorded into

database.

If transaction fails before meeting the commit point there is no need for any operation because

changes are not recorded.

- May be it is better to redo the effect of the completed operation from the log

because the effect or the changes is not recorded in the database.

To summarize, delaying correction protocol are as below:

Transaction will not affect the database until it meets the commit point.

Transaction meets the commit point until updated operation is recorded into log and written into disc.

The technique is also known as NO-UNDO/REDO algorithm.

Immediate Correction Of Database

Immediate Correction Of Database means the database will immediately be changed duringtransaction without waiting the transaction to reach the commit point.

In this technique, any changes will be recorded into the log inside a disc before it is recorded

into the database.




87

- If transaction fails, but record has been done in the database before it reaches commit point,

the effect of changes in the database has to be redone.

- Thus, operation effect in the database and transaction has to be redone.

- Both UNDO and REDO process need to be done during recovery

- The process is also known as UNDO/REDO algorithm.




88

6.1.2.2 Checkpoints

Why Checkpoint is Needed

When the system fails to operate, log has to function to identify, which transactions

have to be repeated.

There are two approaches:

i. Searching process named as time–consuming

ii. All repeated transactions will be written into database

What is Checkpoint?

- Checkpoint is synchronizing point between database and log file.

All buffers that have been written will be placed into secondary storage.

- This will cause limited amount of search parallel with the outgoing process and

log file.

- DBMS will wait until operator informs all instruction has been written into

database and log has been successfully completed.

-

At this point, database and log is parallel.- Checkpoint record will be written into the log

- Database can be recovered through checkpoint. Transaction for image data

will only start when demand for checkpoint occur




89

Checkpoint Operation?

Among operations that can be done on checkpoint are:

- Writing to all log records to main memory and then it will be brought to secondary storage.

- Writing all corrected blocks to database buffers.

- Writing checkpoint records into log file. Those records contain introduction to all active

transaction when checkpoint occurs.




90

SQL Support

a) COMMIT instruction

Beginning for a transaction and commit

ROLLBACK instructionCan cause the transaction executed being canceled

c) GRANT instruction

Function as confer authority

Syntax: GRANT <privilege list> ON <object> TO <customer list>

Example:

GRANT UPDATE ON STUDENT TO ALICE




91

Database Backup

When the system is malfunctioning, the latest backup will be accessed from tape and stored into

disc and lastly the system will be restarted.

All database and log will be stored in scale in storage media such as magnetic tape.

6.1.3.1 Data File Backup

Data File is a schedule that holds data in the database

User who renews the data in schedule or adding new object will cause changes in datafile and updated need to be done.

So, the duty of administrator needs to do backup frequently so that the latest data have

their backup.

Transaction Log Backup

A backup needed to be created for the transaction log to avoid loss of latest transaction

and latest backup.

Other Backup Files

If possible all database files need to have backup as a precaution step.

Files need to be controlled so that the physical structure can be maintained.




92

6.2 Data Security

Can be done by means of :

i. Granting access to database user

ii. Extending and restricting privileges

iii. Role in security management

6.2.1 Granting Access to Database User

- Granting means giving authority to an individual or user.

- Database manager will set authority to specific user for specific parts in the database.

- Syntax:

GRANT < privilege list> ON <name connection> TO <user list>

[With option GRANT];

Among the privilege instruction




93

UsageNeed in using specific domain

Need in using specific domainSelect

Need to enter dataInsert

Need to update dataUpdate

Need to eliminate dataDelete

Need to refer name in specific schedule for constraint.References

Figure 6.2: Privilege instructions




95

Extending And Restricting Privileges

- Each different user will have different entry described as privileges /authority

- Different user has different entry for the same object

- Permission privilege to user include access certain data file record and mode

such as read and update mode.

6.2.3 Role in Security Management

- Every data is labelled with certain classification at different levels.

- Only valid user can access object data only.

topic 5 keselamatanpd

Documents