As a Personal Oracle developer, you need to understand the proper procedure for backing up your Personal Oracle database. Personal Oracle furnishes two tools for this purpose: Backup Manager and Recovery Manager. You can find both programs in the Personal Oracle7 program group.
The only reason for backing up an Oracle database is to protect against data loss. An Oracle database is composed of several files: at least one control file, at least two redo log files, and a default set of four datafiles. A datafile is associated with a tablespace, which I discuss in more detail in Chapter 26, "Managing Space." If even one of these files is lost because of an accidental deletion or a disk drive failure, you won't be able to restart the Oracle database. Therefore, backing up all Oracle database files is essential.
I highly recommend that you use only Backup Manager to safely back up your Personal Oracle database. You can use Microsoft Backup instead, but you incur the risk of an incomplete backup. If you don't back up all the database files, including any files that have been added with Database Expander or SQL*DBA, you won't capture a consistent backup of the database. If you ever needed to restore the database backup, you wouldn't be able to start it. Backup Manager can accurately determine the files that must be backed up for a consistent backup; Microsoft Backup has no knowledge of which files should be backed up--;it's your responsibility to indicate which files should be backed up. If you are planning to use a different backup program to safeguard the Personal Oracle database, you must stop the database before you begin the backup.
Backup Manager in the Windows 95 version of Personal Oracle7 provides the same features that exist in the version for Windows 3.11. It is located in the Personal Oracle7 for Windows 95 program group. Backup Manager can perform a backup of a Personal Oracle database in one of the following modes:
I recommend performing a backup when the Personal Oracle database is running. However, I discuss all three backup options in this chapter.
Backup Manager is one of the only Oracle Database Administration Tools that you can use when the Personal Oracle database isn't running. First, be sure that the Personal Oracle7 database isn't running by using the Stop Database program to shut down Personal Oracle7. Invoke Backup Manager from the Personal Oracle7 for Windows 95 program group. Backup Manager displays the database status (Not running), the backup type (Offline - Full Database), and the default destination directory for the backup (C:\Orawin95\backup), as shown in Figure 25.1. If you have a tape device, you may choose to use it as the backup destination.
If you click Files, Backup Manager displays a window containing the files that it will back up. (See Figure 25.2.) The files are grouped into three categories--;Data Files, Log Files, and Control Files. To see the files in each category, select the appropriate radio button. This window also enables you to add or remove files from the backup. Click OK or Cancel to exit the Database Files window.
When you click Backup, Backup Manager displays a progress bar indicating which file is currently being backed up to the destination directory. When it has completed the backup, Backup Manger displays a message indicating that the database was successfully backed up. (See Figure 25.3.)
If you look at the destination directory, you will see the database files that were backed up. (See Figure 25.4.) Another file you see in the destination directory is vsbackup.ini, which contains a list of the files that were backed up, the backup type, and the date and time of the backup. (See Figure 25.5.)
If you invoke Backup Manager when the Personal Oracle database is running, the Backup Manager window will show the database status as Running in NOARCHIVELOG mode--;meaning that archive logging is not enabled. (See Figure 25.6.) Archive logging is the process by which Oracle makes a copy of a redo log file when it is completely filled with changed data blocks. Notice that the Files button is disabled. Because Personal Oracle7 is running, Backup Manager is able to obtain a correct and complete list of all database files that need to be backed up. Click Backup to initiate the full backup of the database. When the backup is complete, Backup Manager displays a message indicating that the backup was successful.
By default, the Personal Oracle database is created with two redo log files. As you may recall, a redo log file contains data blocks that have changed as a result of committed transactions. Oracle writes the changed blocks to the redo log files in round-robin fashion. It writes changed blocks to the first redo log file until the file is full, at which time it switches to the second redo log file and writes the changed data blocks to it. When the second redo log file is filled, Oracle switches back to the first redo log file. Oracle stores the archived redo log files in C:\Orawin95\rdbms72\archive. (See Figure 25.7.)
What, you might ask, does any of this information have to do with backup and recovery? Quite a bit. Oracle provides a mechanism called archive logging (or Support Recovery for Personal Oracle) that makes a copy of a redo log file when it is completely filled. If you've enabled Support Recovery, you have the potential to recover transactions that have been committed since the last database backup. I use the word potential because you have to take some precautions to recover the committed transactions.
To enable archive logging, edit the file that contains the initialization parameters: C:\Orawin95\Database\initorcl.ora. Specifically, you want to uncomment the line log_archive_start = TRUE and specify the directory in which the archive log files will be stored. (See Figure 25.8.) You can then use SQL*DBA to alter the database to start archive logging by following these steps:
Backup Manager offers the following three options for backing up a running instance:
If you select the Offline - Full Database radio button (see Figure 25.9), Backup Manager shuts down the database, performs a backup of all database files to the specified destination, and restarts the database. Click Backup to begin the full database backup. Backup Manager stores the backup files in C:\Orawin95\backup. If the directory doesn't exist, Backup Manager creates it.
Backup Manager enables you to back up a specific tablespace while the database is running. Highlight the tablespace you want to back up and select the Online - Selected Tablespace radio button. (See Figure 25.10.) Click Backup to start the backup process.
If you want to back up a copy of the database control file, select the Online - Control File only radio button (see Figure 25.11) and then click Backup.
Backup Manager can perform a backup of a Personal Oracle database in one of the following modes:
I discuss each of these modes in more detail in the next few sections.
Backup Manager is one of the only Oracle Database Administration Tools that you can use when the Personal Oracle database isn't running. First, be sure that Oracle isn't running. Double-click the Backup Manager icon. Backup Manager displays a message informing you that it might not have complete information about all of the database files because the Personal Oracle database isn't running. (See Figure 25.12.) The first time that you run Backup Manager while the Oracle database isn't running, Backup Manager will use the default database files for its backup.
Click the OK button on the warning message. Backup Manager displays a screen showing which database files will be backed up. Backup Manager backs up the database either to the \backup directory within ORACLE_HOME (c:\orawin by default) or to a tape device, if installed. (See Figure 25.13.)
If you invoke Backup Manager when the Personal Oracle database is running, Backup Manager displays a dialog box prompting you for the database password. (See Figure 25.14.) Enter oracle or the actual database password and click OK.
Backup Manager displays a screen with three backup options. The message Running in NOARCHIVELOG mode means that archive logging is not enabled. Archive logging is the process by which Oracle makes a copy of a redo log file when it is completely filled with changed data blocks. Click the Backup button to initiate the full backup of the database. (See Figure 25.15.) When the backup is complete, Backup Manager displays a success message. (See Figure 25.16.)
When Backup Manager has performed a full backup of a running Personal Oracle database, it obtains a complete and accurate list of all files that are part of the database. For instance, Figure 25.17 shows the list of database files, including c:\orawin\dbs\wdbfray.ora. This datafile belongs to the FRAYED_WIRES tablespace, which isn't part of the default Personal Oracle database.
By default, the Personal Oracle database is created with two redo log files. As you may recall, a redo log file contains data blocks that have changed as a result of committed transactions. Oracle writes the changed blocks to the redo log files in round-robin fashion. It writes changed blocks to the first redo log file until the file is full, at which time it switches to the second redo log file and writes the changed data blocks to it. When the second redo log file is filled, Oracle switches back to the first redo log file. Oracle stores the archived redo log files in c:\orawin\rdbms71\archive. (See Figure 25.18.)
What, you might ask, does any of this have to do with backup and recovery? Quite a bit. Oracle provides a mechanism called archive logging (or Support Recovery for Personal Oracle) that makes a copy of a redo log file when it has filled. If you've enabled Support Recovery, you have the potential to recover transactions that have been committed since the last database backup. I use the word potential because you have to take some precautions to recover the committed transactions.
To enable Support Recovery, invoke Database Manager and click Configure. The Configure Initialization Parameters window appears. To set up Support Recovery, click the Support Recovery check box. (See Figure 25.19.) The next time you start the Personal Oracle database, Support Recovery will be enabled.
Backup Manager offers the following three options for backing up a running instance:
If you select the Offline - Full Database radio button (see Figure 25.20), Backup Manager shuts down the database, performs a backup of all database files to the specified destination, and restarts the database. Click Backup to begin the full database backup. Backup Manager stores the backup files in c:\orawin\backup. (See Figure 25.21.) If the directory doesn't exist, Backup Manager creates it. ***25DPO20***
Backup Manager enables you to back up a specific tablespace while the database is running. (See Figure 25.22.) Highlight the tablespace you want to back up and select the Online - Selected Tablespace radio button. Click Backup to start the backup process.
If you want to back up a copy of the database control file, select the Online - Control File only radio button and then click Backup. (See Figure 25.23.)
Invoke Recovery Manager by selecting it from the Personal Oracle7 for Windows 95 program group. To simulate the loss of a database file, I'll delete c:\Orawin95\database\sys1orcl.ora. This datafile belongs to the SYSTEM tablespace. When you try to start the database, Oracle displays an error message, indicating that it cannot find the datafile. (See Figure 25.24.)
To recover the lost file, invoke Recovery Manager. Recovery Manager provides four options from which to choose; this example uses Automatic recovery. Be sure that the Automatic recovery radio button is selected and click Recover. (See Figure 25.25.)
Recovery Manager displays a window in which you specify the source of the database backup. If a tape device is available, the option to recover from tape is enabled. By default, Recovery Manager uses C:\Orawin95\backup as the backup source. (See Figure 25.26.) Click OK to begin the recovery process.
Recovery Manager restores the lost files from the backup files. Once all of the lost files have been restored, Recovery Manager automatically starts the database. (See Figure 25.27.)
Invoke Recovery Manager by double-clicking its icon in the Personal Oracle7 program group. Recovery Manager prompts you for the database password; enter it and click OK. (See Figure 25.28.)
To simulate the loss of a database file, I'll delete c:\orawin\dbs\wdbuser.ora. This datafile belongs to the USER_DATA tablespace. When you try to start the database, Database Manager returns an error message indicating that it cannot find the datafile. (See Figure 25.29.)
To recover the lost file, invoke Recovery Manager by double-clicking its icon in the Personal Oracle7 program group. Recovery Manager provides four options from which to choose; this example uses Automatic recovery. Be sure that the Automatic Recovery radio button is selected and click Recover. (See Figure 25.30.)
Recovery Manager displays a window in which you specify the source of the database backup. If a tape device is available, the option to recover from tape is enabled. By default, Recovery Manager uses c:\orawin\backup as the backup source. (See Figure 25.31.) Click OK to begin the recovery process.
Recovery Manager restores the lost file from the backup files. When all the lost files have been restored, Recovery Manager automatically starts the database. (See Figure 25.32.)
In certain circumstances performing a database export is faster than performing a database backup; for example, if the database is large but mostly empty. (Please refer to Chapter 26 for a detailed discussion about Oracle space management.) In that case, Backup Manager spends its time copying large, sparse files, whereas Export copies the database objects and their contents. It's difficult to quantify when performing an export is more efficient than performing a backup. If your database is large but mostly empty, you can experiment with both tools to see which is most appropriate. For the Oracle Workgroup Server or Enterprise Server, you can use Export to move data from a development database to a production database.
This chapter concentrates on the following backup and recovery issues: