150 likes | 463 Views
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:
E N D
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)