Transcript Backup and Recovery

Backup and Recovery
Part 2
Online backup
• Online backup requirements:
•Backup all data files (file system copy)
•Backup all redo information produced during online backup
•Backup control file (using database commands)
Online backup
• Online backup procedure:
•Remember current log sequence (v$log view)
•For each tablespace:
•Activate tablespace backup mode
•Copy tablespace data files to backup location
•Deactivate tablespace backup mode
•Force archival of active redo log (for example: force logfile
switch)
•Backup all redo logs produced since backup started
•Backup control file
Tablespace backup mode
• To activate tablespace backup mode:
•ALTER TABLESPACE tablespace_name BEGIN BACKUP
• To deactivate tablespace backup mode:
•ALTER TABLESPACE tablespace_name END BACKUP
• Notes:
•Data files should not be copied unless tablespace is in
backup mode
•Backup mode results in:
•Data header not being updated (it is updated at backup
end)
•Larger redo log records (more redo logs produced)
Archiving redo logs
• The following commands are useful for online
backups:
•ALTER SYSTEM ARCHIVE LOG ALL;
•ALTER SYSTEM ARCHIVE LOG NEXT;
•ALTER SYSTEM ARCHIVE LOG SEQUENCE 104;
•ALTER SYSTEM ARCHIVE LOG CURRENT;
•ALTER SYSTEM ARCHIVE LOG CURRENT
NOSWITCH; (when database is mounted)
• To activate/deactivate automatic archival:
•ALTER SYSTEM ARCHIVE LOG START;
•ALTER SYSTEM ARCHIVE LOG STOP;
Control file backup
• Control file cannot be backed up using
operating system commands
• Control file must be backed up using database
commands:
•ALTER DATABASE BACKUP CONTROLFILE TO
TRACE;
•ALTER DATABASE BACKUP CONTROLFILE TO
‘c:\backup\control.ctl'
Backup controlfile
• During recovery backup controlfile should be
used only when necessary
• Recovery with backup controlfile should be
indicated:
•RECOVER DATABASE USING BACKUP
CONTROLFILE;
•RECOVER DATABASE USING BACKUP
CONTROLFILE UNTIL CANCEL;
• During backup controlfile recovery:
•Database does not know if recovery is complete or not
•ALTER DATABASE OPEN must include RESETLOGS or
NORESETLOGS
Using online backup
• Online backup can be used to:
•Do complete recovery, in that case:
•Restore required data files, do normal recovery
•Do point in time or change based recovery, in that case:
•Restore all data files, do recovery UNTIL TIME or
UNTIL CHANGE
•Do minimal recovery (open the database in the state just
after backup), in that case:
•Restore all data files, do recovery UNTIL CANCEL
•Apply only the logs produced during backup
•OPEN DATABASE with RESETLOGS option
Archive destination
• Archived log files can be multiplexed (up to 10
locations):
•Initialization parameter LOG_ARCHIVE_DEST_n specifies
archive destination for each location
•Archival destination can be:
•Local filesystem path
•Files are simply copied to that destination
•Network service name (remote archival)
•Files are sent via network to remote standby database
•Destination can be MANDATORY or OPTIONAL
Archive destination
• Archival destination examples:
•LOG_ARCHIVE_DEST_1
= 'LOCATION=C:\backup MANDATORY'
•LOG_ARCHIVE_DEST_2
= 'SERVICE=remote1 OPTIONAL'
•LOG_ARCHIVE_DEST_3
= 'LOCATION=C:\backup2'
•LOG_ARCHIVE_DEST_4
= 'SERVICE=remote2 OPTIONAL REOPEN=30
MAX_FAILURE=10'
Archive destination
• LOG_ARCHIVE_MIN_SUCCEED_DEST:
•Specifies minimum number of OPTIONAL destinations that
must succeed
Archived log file name
• LOG_ARCHIVE_FORMAT:
•Specifies file name format, must include:
•%s – log sequence
•%t – thread number (used in clusters)
•%r – resetlogs id
•Example:
•LOG_ARCHIVE_FORMAT=arch%t_%s_%r.arc
Archiving – useful views
• V$DATABASE – specifies archival mode
• V$ARCHIVED_LOG – historical achived log information
from control file
• V$ARCHIVE_DEST – archival destinations, mode and status
of each destination
• V$BACKUP_REDOLOG – information about backups of
redo logs
• V$LOG – information about online log groups
• V$LOG_HISTORY – information about which logs have been
archived and SCN range for each archived log
V$DATABASE
• V$DATABASE – columns related to recovery:
•DBID - Database identifier generated when the database is
created and stored in all file headers
•CURRENT_SCN – current System change number
•RESETLOGS_CHANGE# - System change number (SCN)
at open resetlogs
•RESETLOGS_TIME - Timestamp of open resetlogs
•LOG_MODE – archivelog or noarchivelog
•CHECKPOINT_CHANGE# - Last SCN checkpointed
•OPEN_RESETLOGS - (NOT ALLOWED | ALLOWED |
REQUIRED)