br presented to it engineers of nepal electricity authority

8/6/2019 BR Presented to IT Engineers of Nepal Electricity Authority

1/31

Backup & Recovery

Raj KishoreDatabase Consultant


2/31

Backup & Recovery Issues

Protect the database from numerous types of

failures

Increase Mean-Time-Between-Failures (MTBF) Decreases Mean-Time-To-Recover (MTTR)

Eliminate data loss


3/31

Categories of Failures

Statement failures User process failure

User error Network failure

Instance failure

Media failure Disaster


4/31

Causes of Statement Failures

Logic error in an application Attempt to enter invalid data into the table

Attempt an operation with insufficientprivileges

Attempt an INSERT or UPDATE to a table,

causing an extent to be allocated, but withinsufficient free space available in the

tablespace


5/31

Resolutions for Statement Failures

Correct the logical flow of the program Modify and reissue the SQL statement

Provide the necessary database privileges Change the users quota limit by using the

ALTER USER command

Add file space to the tablespace


6/31

Causes of User Process Failures

The user performed an abnormal disconnectin the session

The users session was abnormallyterminated

The users program raised an address

exception, which terminated the session


7/31

Resolution of User Process Failures

The PMON process detects an abnormallyterminated user process

PMON rolls back the transaction andreleases any resources and locks being held

by it


8/31

Possible User Errors

SQL> DROP TABLE employee; SQL> TRUNCATE TABLE employee;

SQL> DELETE FROM employee; SQL> COMMIT;

SQL> UPDATE employee

2> SET salary = salary * 1.5; SQL> COMMIT;


9/31

Resolution of User Errors

Train the database users Recover from a valid backup

Import the table from an export file Use LogMinor to determine the time of

error

Recover with a point-in-time recovery Use FlashBack to view and repair historical

data


10/31

Causes of Instance Failure

A power outage occurs that causes theserver to become unavailable

The server becomes unavailable due tohardware problems

One of the Oracle server background

processes (DBW

n, LGW

R, PMON,SMON, CKPT) experiences a failure


11/31

Recovery from Instance Failure

No special recovery action is needed fromDBA

Start the instance Wait for the database opened notification

Check the alert log to determine the reason

for the failure


12/31

Causes of Media Failures

Head crash on a disk drive Physical problem in reading from or writing

to database files File was accidentally lost


13/31

Resolutions for Media Failures

The recovery strategy depends on whichmethod was chosen and which files are

affected If available, apply archived redo log files to

recover data committed since the last

backup


14/31

NOARCHIVELOG Mode

Redo log files are used in circular fashion

A redo log file can be reused immediately after a

checkpoint has taken place After redo logs are overwritten, media recovery is

only possible to the last full backup

If a tablespace becomes unavailable because of

failure, you can not continue without dropping thetablespace


15/31

ARCHIVELOG Mode

The database is protected from loss of data when

media failure occurs

You can restore a backup copy of the damagedfiles and use archived log files to bring the

datafiles up-to-date

You can not backup the database while it is online

W

hen a tablespace other than SYSTEM goesoffline as a result of media failure, the database

still remains available because tablespaces can be

recovered while the database is open


16/31

Changing the Archiving Mode

INIT.ORAlog_archive_start = true

log_archive_dest_1 = "location={destination}Log_archive_format=arch%s.arc

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT SQL> ALTER DATABASE ARCHIVELOG

SQL> ALTER DATABASE OPEN


17/31

Cold Backup (Offline Backup)

Backup without archiving is used to recover to the

point of the last backup after media failure

Backup with archiving is used to recover to thepoint of failure after a media failure

Data files, Control files, Redo log files, Parameter

file, Password

SQL> SHUTDOW

N IMMEDIATE SQL> HOST cp /backup/

SQL> STARTUP


18/31

Hot Backup (Online Backup)

Maintains high database availability

Can be done at a tablespace or database level

The database is set to ARCHIVELOG mode SQL> ALTER DATABASE BEGIN BACKUP

SQL> HOST cp /backup/

SQL> ALTER DATABASE END BACKUP

continued


19/31


20/31

Export

The export utility can provide a logicalbackup of database objects

Exp scott/tiger tables=emp file=exp1.dmp Exp scott/tiger owner=scott file=exp2.dmp

Exp \sys/{password} as SYSDBA\ full=y

file=exp3.dmp


21/31

Media Recovery Steps

Damaged or missing files are restored from backup

Changes from the archived redo log files and online redo

log files are applied as necessary. Undo blocks are

generated at this time. This is referred to as rolling

forward or cache recovery

The database may now contain committed and

uncommitted changes

The undo blocks are used to roll back any uncommittedchanges. This is known as rolling back or transaction

recovery

The database is now in a recovered state


22/31

Recovery in NOARCHIVELOG

Mode

In NOARCHIVELOG mode, you mustrestore the following database files;

All datafiles Control files

Redo log files

Password file Parameter file


23/31


Mode with Log file Backups

SQL> SHUTDOWN ABORT SQL> HOST cp /backup/* /data/

SQL> connect / as SYSDBA SQL> STARTUP


24/31


Mode without Log File Backups

Shutdown the instance Restore the datafiles and control file from

the most recent whole database backup Perform cancel-based recovery

Open the database with the RESETLOGS

optioncont


25/31

Recovery without Redo Log File

Backups SQL> SHUTDOWN IMMEDIATE

cp /backup/*.dbf /data/

cp /backup/*.ctl /data/ SQL> RECOVER DATABASE UNTIL CANCEL

USING BACKUP CONTROLFILE

SQL> CANCEL

SQL> ALTER DATAB

ASE OPEN RESETLOGS


26/31

Recovery in ARCHIVELOG Mode

Complete Recovery Uses redo data or incremental backups

Updates the database to the most currentpoint in time

Applies all redo changes

Incomplete Recovery Uses backup and redo logs to produce a

non-current version of the database


27/31

Complete Recovery

Make sure that datafiles for restore areoffline

Restore only lost or damaged datafiles Do not restore the control files, redo log

files, password files, or parameter files

Recover the datafile


28/31

RECOVER Command

Recover a mounted database

SQL> RECOVER DATABASE

OrSQL> RECOVER DATAFILE /data/file01.dbf

Recover an open database

SQL> RECOVER TABLESPACE users

OrSQL> RECOVER DATAFILE /data/file01.dbf


29/31

Incomplete Recovery

Shut down and backup the database

Restore all datafiles. Do not restore the control

file, redo logs, password file, or parameter Mount the database

Recover the datafiles to a point before the time of

failure

Open the database with RESETLOGS Perform a closed database backup


30/31

RECOVER in Incomplete Recovery

SQL> RECOVER DATABASE untilcancel

SQL> RECOVER DATABASE until time2005-01-20 10:15:22

RECOVER DATABASE until scn


31/31

Import

imp scott/tiger TABLES=emp,dept rows=yfile=/backup/exp1.dmp

imp system/manager FROMUSER=scottTOUSER=scott file=/backup/exp2.dmp

imp \sys/ AS SYSDBA\

full=y file=/backup/exp3.dmp

br presented to it engineers of nepal electricity authority

Documents