Data Warehouse Requirements

Oracle supports several features that can assist the recovery of a data warehouse. If the business requirements dictate that the data is for read-access only, Oracle’s read-only tablespaces can be utilized. Oracle’s read-only tablespaces eliminate the need for Oracle’s read consistency and archive recovery features as the data is not allowed to change. The need to backup this tablespace, or to back it up more than once, is eliminated. These read-only tablespaces can reside on CD-ROM technology.

If the data warehouse is large, Oracle8’s partitioning features may be of value for backup and recovery scenarios (as well as the obvious performance implications). Partitions can be individually backed up while online and available to applications. Partitions can be loaded independently of other partitions. If recovery is needed, recovery happens at the partition level, while the other partitions are still available to the application. This option alone can greatly enhance data availability to applications.

Scheduled Maintenance

Application-unavailable issues such as scheduled maintenance need to be considered as well. Periodic maintenance for backups and reorganizations is essential for a well- running database environment. Some applications have a batch mode, where all changes to the data is applied at one time, usually in the off hours. These applications lend themselves well for backup right after all of the updates. Data reorganization typically occurs on weekends, and can be a time-consuming process in Oracle’s complex relationships with other objects (constraints, referential integrity, and so on).

The size of the data objects has a direct relationship to the time of backup, recovery, and database-maintenance tasks. The larger the database objects and the more critical the data availability requirements are, the more Oracle partitioning should be considered. Partitioning breaks larger objects into smaller, more manageable pieces.

Overview of Oracle Backup and Recovery Processes

There are many files that make up the Oracle8 database environment (see Figure 23.1). This section will give a brief description of each, their relative importance, and methods of backing up each.

Figure 23.1.  Oracle database processes and related files.

The most important file is the control file. This file contains locations of all the other Oracle files, important recovery information, important startup and shutdown information, and, if Recovery Manager is implemented, the recovery files. Basically, if this file is lost or unrecoverable, the Oracle database cannot be started. Oracle allows for many control files to be maintained on disk as desired. These files are listed, with their operating-system full-path specifications, in the INIT.ORA parameter file. A control file can always be created with the following SQL statement alter database backup controlfile to /tmp/<SID>.ctl. The location and names of the currently maintained control files can be viewed with the following SQL statement select name from v$controlfile.

---

NOTE:  
When performing hot backups (backups with the database online and available), it is important to use this command to create a backup copy of the control file, not the control file that is currently on disk. The only time the real control files should be backed up is during a cold backup.

---

The database files are assigned to the Oracle tablespaces and contain the Oracle data dictionary and all application data. These are the main files to be backed up. Oracle supports two methods to back up these files, cold backups and hot backups. Both of these methods are discussed in detail later, but it suffices to say here that during a cold backup, it is important to copy all of these database files to a removable media, and during a hot backup it is important to copy all of the database files associated with the tablespace being backed up to a removable media. Database files can easily be viewed with the following SQL statement select name from v$datafile.

The redo log files are used to record all changes made to the database. These files are used during instance recovery to roll forward any committed changes to the data files and roll back any uncommitted changes to the data files. There are at least two of these redo logs online and available for the Oracle environment to record changes to data and schemas. If ARCHIVELOG mode is activated (complete discussion later in this chapter), the contents of these logs are saved to operating system files called Archive Logs before being made available for reuse by the Oracle environment.

---

NOTE:  
It is important to save these redo log files during a cold backup but absolutely not during a hot backup. Restoring these files at any other time than with all files associated with a cold backup could and will corrupt the database environment.

---

The redo log files are very important to the operation of the Oracle environment, and for this reason, Oracle provides a method to mirror these files, as many times as needed, to other disk drives. A complete list of assigned redo log files can be viewed with the SQL statement select member from v$logfile.

There are a few other files I like to back up, especially during a cold backup. They are the SQL*Net configuration files (TNSNAMES.ORA, LISTENER.ORA, and SQLNET.ORA). Another rather important file is the INIT.ORA file, the database startup parameter file.