Instructor's Notes
(If applicable)
- Appendix A: User-Managed Backup and Recovery
- This lesson covers:
- Failure Scenarios
- Strategies
- Archiving
- Cold Backup
- Hot Backup
- Complete Recovery
- PIT Recovery
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- There are several techniques that can be used to backup an Oracle database. These include:
- User-managed backups. This is done with operating system commands (e.g. cp, tar, copy) and often administrators will write scripts to assist.
- RMAN – Recovery Manager is an Oracle utility that provides server-managed backup and recovery functionality. As of Oracle9i this is a robust tool that I recommend.
- 3rd Party Software – There are several vendors who provide backup and recovery solutions. Legato, Quest, Computer Associates are examples.
- Replication – Use Oracle's replication features (called Data Guard in Oracle9i). In the event of a failure of the primary site, you can failover to one of the secondary sites.
- This lesson will focus on user-managed backups.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Why do databases fail? Though we cannot predict all the scenarios, we can name a few common scenarios:
- Instance Failure – Instance failure is the failure of one or more of the background processes. This can occur due to software errors, OS errors, OS crashes and power failures. Recovery from instance failure is automatic at the next startup.
- Disk failure – Disk devices are a mechanical device and will not last forever (most disk drives come with a MTBF rating (mean time between failure)). Disks that exceed this limit – and even some that do not – can fail. Recovery is needed; in this situation, media recovery will be required. We will study media recovery in this lesson.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Failure Scenarios, continued:
- Application Errors – If an application or user introduces "bad" data, we typically desire to recover to a specific point in time. This is called a point-in-time (PIT) recovery. It is also known as (A.K.A.) partial or incomplete recovery.
- DBA Errors – A database administrator may inadvertently drop an object. This situation typically calls for a recovery of the tablespace or datafile containing the object. Referential integrity issues (I.e. dependencies) may require a database-level PIT recovery operation. Note that in the case of indexes, it is usually more efficient to simply rebuild the index rather than recover it.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- No matter what technique we use to backup the database, we should put the database in ARCHIVELOG mode. This will insure that we can do a complete media recovery if necessary. (We will discuss archiving in more detail later in this lesson.)
- Then choose one or more of the following backup techniques:
- Cold Backup – Database must be shutdown. This is a clean and easy backup technique, but at the cost of prohibiting access to the database.
- Hot Backup – This technique is more complex, but we are able to allow complete access to the database while we perform the backup.
- Export – The EXPORT utility creates a binary file containing the data and the object definitions (I.e. DDL). We can recreate a lost object with the IMPORT utility. EXPORT is often used as a secondary backup technique (I.e. in addition to user-managed or RMAN backups.)
- Notes continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Continued from the previous page:
- Export, continued. Export is referred to as a logical backup because we are backing up logical objects, e.g. tables, as opposed to database files. This is an easy backup method, but has the following disadvantages:
- Though the database must be open, the database should not be open for general access because there could be inconsistencies between objects in the backup data.
- This method also does not support point-in-time (partial, incomplete) recovery. I.e. We cannot apply redo to the imported data.
- RMAN – RMAN is an Oracle utility available with Oracle8 and up. This utility provides some useful features such as true incremental backups. Note that this lesson only covers user-managed backups, not RMAN.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The following items should be included in your backup strategy:
- Tablespaces contain your data, it is obvious that we need to back these up. However, READ ONLY tablespaces might be excluded if the data can be easily recreated.
- The control file contains structural and recovery records. You cannot start, access or recover the database files without it. We need to mirror and back the control file! The CKPT process is responsible for updating the control file every three seconds with checkpoint information so Oracle knows what changes have already been written to datafiles. This information identifies which redo records are needed for recovery.
- Backing up the online redo logs eliminates the need to reset them after full database restore.
- The online and archived logs contain a history of changes to the database. It is critical that they are backed up. Oracle8i and above permits multiplexing the archive logs, I.e. creating multiple copies during the archive process. If you are certain that enough copies of the archives logs exist, backing them up might be deemed unnecessary.
- The text parameter (INIT.ORA) files should be backed up.
- Also consider including your hard-earned administrative scripts.
- Finally, also consider using the EXPORT utility as a secondary backup technique.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- A complete record of all changes made to the database blocks is recorded in the online and archived redo log files. (Note that archived log files are sometimes called offline logs.)
- If any of the database files are lost, then a backed up copy of the database file can be restored and all the changes made to the database file since the backup can be recovered using the archived and online redo log files.
- There are two modes in which the database could be running - ARCHIVELOG and NOARCHIVELOG mode. By default, the database is running in NOARCHIVELOG mode. This means that the redo logs can be overwritten before a copy is taken of their contents. If the database is running in ARCHIVELOG mode then the redo logs will not be overwritten before they are archived.
- The mode of the database should, especially for production systems, be ARCHIVELOG mode. This would ensure that between the online and offline redo log files we have a complete history of all changes made to all database blocks and that a complete media recovery is possible.
- Notes continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Continued from the previous page:
- It is also critical to start the archive background process. This process will automatically archive the online logs. Failure to do so will cause the database to hang when the log write process needs to write to a log that has not been archived (remember, when the database is in ARCHIVELOG mode, Oracle will not write over log file contents until it has been archived.)
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Execute the ARCHIVE LOG LIST command from SQL*Plus or Server Manager to:
- Display the mode of the database
- List the online redo logs
- Show the archive destination
- Determine next log to be archived
- In the example above:
- The oldest redo log file has a sequence number of 111.
- The next redo log file to be archived has a sequence number of 113. (112 has already been archived.)
- The current redo log file has a sequence number of 113.
- The next redo log file to be archived is 113.
- The log sequence numbers are:
- Unique
- Sequentially assigned at log switch time
- Retained with the file during archive
- Used in ascending order to re-apply updates during recovery
Other Text:
(Examples or comments displayed on slide, if any).
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Oracle\oradata\dave\archive
Oldest online log sequence 111
Next log sequence to archive 113
Current log sequence 113
Instructor's Notes
(If applicable)
- A simple and common way of backing up a database is to perform cold backups and to have the database running in ARCHIVELOG mode. This is both a simple and robust strategy.
- If the database files are lost, then we can restore the lost database files from the latest backup and recover the file(s) using the online and archive redo log files to reapply all changes made since the backup.
- Before performing a cold backup, we need to be certain what files to include in the backup. A good way to do this is restrict access to the database (no one can drop or add files) and use the dynamic performance tables to determine what files to backup - they never lie! In this example, first I kick everyone off with a SHUTDOWN IMMEDIATE command and restart with the RESTRICT option.
- The QUIET option used on the STARTUP command shown above simply suppresses the SGA size information.
- Procedure continues on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup quiet restrict
ORACLE instance started.
Database mounted.
Database opened.
Instructor's Notes
(If applicable)
- Next I query the V$DATAFILE dynamic performance view to determine all of the datafiles I should include in my backup.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------
C:\ORACLE\ORADATA\DAVE\SYSTEM01.DBF
C:\ORACLE\ORADATA\DAVE\RMANCATALOG.DBF
C:\ORACLE\ORADATA\DAVE\EXAMPLE01.DBF
C:\ORACLE\ORADATA\DAVE\INDX01.DBF
. . .
Instructor's Notes
(If applicable)
- Dynamic performance views are an excellent source of file names:
- The V$LOGFILE view provides an accurate list of the log files that must be backed up.
- V$CONTROLFILE provides an accurate list of the control files that must be backed up.
- V$ARCHIVED_LOG contains the names of the archived log files. Note that after backing up the archive logs (perhaps more than once, to multiple locations), you may want to delete the archive logs to free up disk space.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> SELECT member FROM v$logfile;
MEMBER
-----------------------------------
C:\ORACLE\ORADATA\DAVE\REDO01.LOG
...
SQL> SELECT name FROM v$controlfile;
NAME
------------------------------------
C:\ORACLE\ORADATA\DAVE\CONTROL01.CTL
...
SQL> select name from v$archived_log;
NAME
---------------------------------------
C:\ORACLE\ORADATA\DAVE\ARCHIVE\1_93.DBF
C:\ORACLE\ORADATA\DAVE\ARCHIVE\1_94.DBF
Instructor's Notes
(If applicable)
- Once you have determined what files should be backed up, shutdown the database and copy all of the files to a secure backup location.
- It is helpful to write a SQL*Plus script to generate the OS copy commands. In the example above, the SELECT statement generates a UNIX "cp" command for each datafile in my database.
- See supplied scripts BACKUP_UNIX.SQL and BACKUP_WINDOWS.SQL for sample SQL*Plus backup scripts. Note that these scripts are NOT production quality scripts; they are samples that are meant to highlight the steps required. The scripts must be modified for your test environment - UNIX or Windows - and your backup locations. The script will create either a .BAT file (Windows) or ".sh" file (Unix). Use the UNIX "sh" command to execute the shell script: "sh ./backup.sh"
Other Text:
(Examples or comments displayed on slide, if any).
-- Find datafiles to back up:
SELECT 'cp ' || name
|| ' $HOME/backup/'
|| SUBSTR(name, INSTR(name, '/', -1) + 1 )
FROM v$datafile;
Instructor's Notes
(If applicable)
- Cold Backup Workshop
- Take a complete cold backup of your database. Use the supplied script BACKUP_UNIX_COLD.SQL or BACKUP_WINDOWS_COLD.SQL for assistance. You will need to modify the script before executing to reflect the directory structure of your server.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Hot backups are taken while the database is open. The database can be queried and updated by users.
- However, if possible, it is recommended to perform your hot backups while during off-peak hours. This is because while the hot backup is being performed, extra redo is generated. Why? Because during hot backup, Oracle writes an entire block to the redo logs if data is changed within the block, not just the bytes changed. This extra redo can flood your redo logs and cause a general performance degradation.
- The database must be in ARCHIVELOG mode to use hot backups.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- To perform a hot backup:
- Begin by putting the tablespace in backup mode with the ALTER TABLESPACE BEGIN BACKUP command.
- Then use an operating system command (e.g. cp or tar) to backup all datafiles associated with the tablespace.
- Then take the tablespace out of backup mode with the ALTER TABLESPACE END BACKUP command.
- Since there are possible changes recorded in the redo log(s) that were not recorded in the tablespace, it is important to also back up the redo log files created while in backup mode. This can be accomplished by making the current log inactive: ALTER SYSTEM SWITCH LOGFILE;
- Then archive all un-archived log files with the ALTER SYSTEM ARCHIVE LOG ALL command.
- This procedure should be repeated for all tablespaces.
- The control file can be backed up while the database is open too. This is done with the ALTER DATABASE command shown above. This does not need to be done for every tablespace, just once.
- Finally, use an operating system command to backup all archived logs (not shown).
Other Text:
(Examples or comments displayed on slide, if any).
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> host
[oracle@Test dev]$ su root
Password:
[root@Test dev]# cp -f /usr2/oracle/oradata/test/users01.dbf /home/
[root@Test dev]# exit
[oracle@Test dev]$ exit
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log all;
System altered.
SQL> alter database backup controlfile to '/home/control.ctl';
Database altered.
Instructor's Notes
(If applicable)
- Hot Backup Workshop
- Take a complete hot backup of your database. Use the supplied script BACKUP_UNIX_HOT.SQL. You will need to modify the script before executing to reflect the directory structure of your server.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- A complete recovery restores the database to the last successful commit point. The procedure for a complete recovery involves these steps:
- Shutdown the database as soon as a problem is detected and you suspect that there may be a missing database file.
- Perform a full backup of all the database files, redo logs and control files which remain. (just in case the procedure needs to be repeated)
- Restore the missing (and only the missing) files from the latest backup using an operating system restore files command.
- Startup the database in MOUNT mode.
- Procedure continues on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Recover procedure, continued from previous page:
- Next, issue the RECOVER command. The RECOVER command has the following options:
- RECOVER DATABASE - Oracle examines all tablespaces and all datafiles to determine what requires recovery. The database must be MOUNTED but not OPEN. Recover the database, by reapplying the archived and online redo log files to re-construct the changes made to the restored database files.
- RECOVER TABLESPACE tablespacename - Recover just the datafiles that make up the tablespace. The database can be open, take the tablespace offline.
- RECOVER DATAFILE datafilename - Just recover the datafile. Database can be open; take the tablespace offline.
- When prompted for the name and location of the archived redo log files, use AUTO which will cause Oracle to use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_NAME INIT.ORA parameters to determine the names and locations of the required redo log files required. If you need to specify a different name and location for the archived redo log files, then just go ahead and specify where they are.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\DAVE\EXAMPLE01.DBF'
SQL> recover datafile 'C:\ORACLE\ORADATA\DAVE\EXAMPLE01.DBF'
Media recovery complete.
SQL> alter database open;
Database altered.
RECOVER is a SQL*Plus command. USE ALTER DATABASE RECOVER if SQL*Plus not available
Instructor's Notes
(If applicable)
- Now check that the database can be started up. Test the database by connecting and executing a query against the restored datafile. Finally, shutdown again and perform a full cold backup of the database files.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Whereas the complete recovery applies all changes made to the last successful COMMIT issued prior to the failure, point-in-time recovery (A.K.A. incomplete or partial recovery) limits the number of changes re-applied. PIT recovery is useful if you want to recover to a point in time in the past (i.e. you do not want to apply all changes to the point of failure) or you are missing one or more archived redo logs. Note that there is no solution for missing one or more redo logs - you cannot continue to roll forward if you are missing a group of changes.
- The DBA can choose from three different options:
- Change-based - Reapply all changes until a specific SCN.
- Time-Based - Reapply all changes until a specific time. Use the date/time format as shown above.
- Cancel-Based - Oracle prompts the DBA before applying each redo log - the DBA can choose to apply the log or stop the process.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> RECOVER DATABASE UNTIL CHANGE 5000;
SQL> RECOVER DATABASE UNTIL '2002-09-01:12:30:00' ;
SQL> RECOVER DATABASE UNTIL CANCEL;
Instructor's Notes
(If applicable)
- The procedure for PIT recovery involves:
- If possible, perform a cold backup of all datafiles and the control file. This is nice to have in case something goes wrong with the recovery, you can restart the process. However, do not backup to the "normal" backup location and overwrite your last backup.
- Use an operating system command to restore all datafiles from a recent backup.
- Enter SQL*Plus, connect AS SYSDBA and start the database in MOUNT mode.
- Issue the RECOVER command. This will apply changes from the archived redo logs until the PIT you specify.
- Open the database with the RESETLOGS option. The RESETLOGS option resets the SCN's in th
