5 backup recovery w
TRANSCRIPT
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