5 backup recovery w

Backup and RecoveryBackup and Recovery

Backup TypesBackup TypesLogical

Physical

Hot

Cold

Without

With

IssuesIssues

– Protect the database from numerous types of failures

– Increase Mean-Time-Between-Failures (MTBF)

– Decrease Mean-Time-To-Recover (MTTR)

– Minimize data loss

– Protect the database from numerous types of failures

– Increase Mean-Time-Between-Failures (MTBF)

– Decrease Mean-Time-To-Recover (MTTR)

– Minimize data loss

Categories of FailuresCategories of Failures

– Statement failure– Statement failure

– Logic error in an application– Attempt to enter bad data into the table– Attempt an operation with insufficient

privileges– Attempt an INSERT or UPDATE to a table,

causing an extent to be allocated, but with insufficient free space left in the tablespace

– Logic error in an application– Attempt to enter bad data into the table– Attempt an operation with insufficient

privileges– Attempt an INSERT or UPDATE to a table,

causing an extent to be allocated, but with insufficient free space left in the tablespace

No Recovery Needed

Categories of FailuresCategories of Failures

– Statement failure

– User process failure

– Statement failure

– User process failure

– The user performed an abnormal disconnect in the session.

– The user’s session was abnormally terminated.

– The user’s program raised an address exception terminating the session.

– The user performed an abnormal disconnect in the session.

– The user’s session was abnormally terminated.

– The user’s program raised an address exception terminating the session.

No Recovery NeededPMON

Categories of FailuresCategories of Failures

– Statement failure

– User process failure

– User error

– Statement failure

– User process failure

– User error

– Accidental DROP TABLE;

– Accidental TRUNCATE TABLE;

– Accidental DELETE / UPDATE;

– Accidental DROP TABLE;

– Accidental TRUNCATE TABLE;

– Accidental DELETE / UPDATE;

Recovery Needed

IMP Utility

Categories of FailuresCategories of Failures

– Statement failure

– User process failure

– User error

– Instance failure

– Statement failure

– User process failure

– User error

– Instance failure

– HardWare Problem on CPU / RAM– HardWare Problem on CPU / RAM

No Recovery Needed

PMON SMON

Categories of FailuresCategories of Failures

– Statement failure

– User process failure

– User error

– Instance failure

– Media failure

– Statement failure

– User process failure

– User error

– Instance failure

– Media failure

– HardWare Problem on I/O Device– HardWare Problem on I/O Device

Recovery NeededPhysical

Requirements for StrategyRequirements for Strategy

– Business requirements• Evolutionary process

– Technical requirements• Hardware, software, man power and time

• Database configurations

• Transaction volume

• Frequency of backups

– Operational requirements• 7-day / 24-hour operations

• Testing and validating backups

– Business requirements• Evolutionary process

– Technical requirements• Hardware, software, man power and time

• Database configurations

• Transaction volume

• Frequency of backups

– Operational requirements• 7-day / 24-hour operations

• Testing and validating backups

Physical Backup MethodsPhysical Backup Methods

Physical backupPhysical backup

Archive Archive modemode

No archive No archive modemode

ARCHIVELOGARCHIVELOG

Archived log Archived log filefile

052ARC0

LOG_ARCHIVE_DESTLOG_ARCHIVE_DEST

/disk1/archive/

Online redo log filesOnline redo log files

052

052

GroupGroup 22

053

Group 1Group 1

053

LOG_ARCHIVE_FORMATLOG_ARCHIVE_FORMAT

arch%s.arc

Enabling ARCHIVELOG Mode

Enabling ARCHIVELOG Mode

Init.ora Control file

Shutdown normal or immediate1

Startup mount2

Alter database ARCHIVELOG 3

Alter database open 4

Shutdown normal or immediate

5

Full database backup6

Physical Closed Database BackupPhysical Closed Database Backup

PasswordPasswordfilefile

Online or Online or offline offline

storagestorage

Control Control filesfiles

Parameter Parameter filesfiles

Data filesData files Redo log Redo log files files

SHUTDOWN IMMEDIATE;

STARTUP OPEN;

HOST cp <files> /backup/

Advantages of Physical ClosedDatabase Backups

Advantages of Physical ClosedDatabase Backups

– Conceptually simple– Easy to perform– Require little operator interaction

– Conceptually simple– Easy to perform– Require little operator interaction

Physical Opened Database Backup (1)

Physical Opened Database Backup (1)

Archived redoArchived redolog fileslog files

Parameter Parameter filesfiles

Pwd filePwd file

Online Online redoredo

log fileslog filesControl Control

filesfiles

Online or Online or offline offline

storagestorage

HOST cp <files> /backup/

Data filesData files

Physical Opened Database Backup (2)

Physical Opened Database Backup (2)

Online or Online or offline offline

storagestorage

HOST cp <files> /backup/

SQL> alter tablespace <name> begin backup;SQL> alter tablespace <name> begin backup;

SQL> alter tablespace <name> end backup;SQL> alter tablespace <name> end backup;

All tablespace data All tablespace data filesfiles

Individual data Individual data filefile

Advantages of Physical OpenedDatabase Backups

Advantages of Physical OpenedDatabase Backups

– Maintains high database availability– Can be done at a tablespace or data file level– Supports nonstop business operations

– Maintains high database availability– Can be done at a tablespace or data file level– Supports nonstop business operations

Data filesData files

Redo log Redo log files files

Control Control filefile

1

1

Archived log fileArchived log file

ARC01

Redo log Redo log files files

Control Control filefile

2

2

Archived log fileArchived log file

1ARC01

2

Data filesData files

Redo log Redo log files files

Control Control filefile

3

3

Archived log fileArchived log file

1ARC03

22

Data filesData files

Redo log Redo log files files

Control Control filefile

4

4

Archived log fileArchived log file

1ARC03

42

3

Data filesData files

Redo log Redo log files files

Control Control filefile

5

5

Archived log fileArchived log file

1ARC05

42

3 4

Data filesData files

Data filesData files

5

Control Control filefile

5

Redo log Redo log files files

Control Control filefile

6

6

Archived log fileArchived log file

1ARC05

62

3 4

Data filesData files

Data filesData files

5

Control Control filefile

5

5

Redo log Redo log files files

Control Control filefile

9

9

Archived log fileArchived log file

1ARC09

82

3 4

Data filesData files

Data filesData files

5

Control Control filefile

5

5 6

7 8

Shutdown1

Startup Mount2

O.S. Restore From Hot or Cold Physical BackUp DataFile (s) 3

Redo log Redo log files files

Control Control filefile

9

9

Data filesData files

9

9

85

Recover DataFile (s) Automatic 4

Applyed Archived Logs #5 #6 #74a

Applyed On Line Logs #8 #94b

Alter DataBase Open5

9

Shutdown Immediate6

New Cold Physical BackUp7

Startup Open 8

S.C.N. System Change NumberS.C.N. System Change Number

Control fileData files

HeaderRedoRecord

Incremental Time Stamp1

Unique Identified “Committed Version D.B.”2

Log file

Log file #

Low SCN #

Higth SCN #

Oracle Export and Import Utilities

Oracle Export and Import Utilities

Logical Backup MethodsLogical Backup Methods

• An interactive dialog

• The export page of the Data Manager within Enterprise Manager

• The command line interface, by specifying parameters

• An interactive dialog

• The export page of the Data Manager within Enterprise Manager

• The command line interface, by specifying parameters

These utilities enable you to do the following:

– Archive historical data

– Save table definitions (with or without data) to protect from user error failure

– Move data between machines and databases or versions of the Oracle server

– Transport tablespaces between databases

These utilities enable you to do the following:

– Archive historical data

– Save table definitions (with or without data) to protect from user error failure

– Move data between machines and databases or versions of the Oracle server

– Transport tablespaces between databases

Data Base

Exp File *.dmp

ImpData Base

Formato Binario Proprietario

Oracle ServerOracle ServerExport ConceptsExport Concepts

Generate SQLcommands

Dump file

Two-Taskcommon

(TTC)

Buffercache

SQLcommand

processing

TTCbuffer

Buffercache

manager

Evaluatingbuffer

Analyze blocks

Read blocks

Oracle9i Server

Tables3Owner2Full1

IncType Complete1a

IncType Cumulative1b

IncType Incremental1c

KEYWORD DESCRIPTION (DEFAULT)-----------------------------------------------------------------USERID username/password BUFFER size of data buffer

FILE output files (EXPDAT.DMP)LOG log file of screen output PARFILE parameter filenameFILESIZE maximum size of each dump file

COMPRESS import into one extent (Y)CONSISTENT cross-table consistency (N)

GRANTS export grants (Y) INDEXES export indexes (Y) ROWS export data rows (Y) CONSTRAINTS export constraints (Y)TRIGGERS export triggers (Y)

FULL export entire DataBase (N)OWNER list of owner usernamesTABLES list of table names

INCTYPE incremental export typeDIRECT direct path (N)STATISTICS analyze objects (ESTIMATE)

Command-Line Method

> exp scott/tiger file=scott.dmp owner=scott grants=Y rows=Y compress=Y

Export Messages

Export: Release 9.2.0.4.0 - Production on Fri Oct 30 09:35:33 1998(c) Copyright 2003 Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionPL/SQL Release 9.2.0.4.0 - ProductionExport done in WE8DEC character set and WE8DEC NCHAR character set. exporting pre-schema procedural objects and actions. exporting foreign function library names for user SCOTT. exporting object type definitions for user SCOTTAbout to export SCOTT’s objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export SCOTT’s tables via Conventional Path .... . exporting table BONUS 0 rows exported. . exporting table DEPT 4 rows exported. . exporting table EMP 14 rows exported

IncType Complete

IncType Incremental -----> Exp IncType Generic

IncType Cumulative -----> Exp IncType Cumulative or Complete

Oracle Standby Implementation

• Fail over Solution• Disaster Recovery Solution (if remote)• Ease of implementation• Minimum impact on Production System• Read Only Standby Database

Overview of Managed Oracle Standby DB

Primary control file

Primary DB

Primary Instance

Redo log

Arc log

ARCH

Standby DB

DBWR

Standby Instance

Recovery proc

Standby control file

RFS

Arc log

T.N.S.

Recovery Mode1ReadOnly Mode2

Activate3