DBA Backup and Recovery Methods

The DBA has a number of additional tools for performing backup and recovery, with capabilities for working at a much larger scale than the methods previously discussed. Instead of a couple of tables being dropped by a user, the DBA may need to handle a disk drive failure, resulting in the loss of an entire tablespace.

In addition to using Export and Import to back up database objects, the DBA can perform cold backups or hot backups for an entire tablespace or an entire database. Other tools available to the DBA include Log Miner and RMAN.

Export and Import for DBAs

Earlier in this chapter, you learned about the Export (EXP) and Import (IMP) utilities that a user can use to save and restore database objects. The DBA can use additional features of these utilities for backing up all user objects in the database or to copy a tablespace to another database. The tablespace copy feature, new to Oracle9i, is known as transportable tablespaces. It is a very convenient way to copy all objects in a tablespace to another database, without needing to specify individual objects in the tablespace.

transportable tablespace

A feature of Oracle’s Import and Export utilities that allows a tablespace to be copied to another database. All objects within the tablespace to be copied must be self-contained; in other words, a table in a tablespace to be copied must have its associated indexes in the same tablespace.

At Scott’s widget company, there are two primary databases:

• The OLTP database (OR92), which contains the online widget order system and the HR tables. It has the EMPLOYEES, DEPARTMENTS, and other tables.

• The data warehouse database (WH92), which contains summaries of orders processed on the online system. Analysts use this summarized information to do "what-if" analyses to predict sales for the upcoming fiscal year.

On a weekly basis, Janice, the DBA, needs to copy the transactions from the online database to the data warehouse database. She decides that using transportable tablespaces is the most convenient and efficient way to move this data, as there are hundreds of tables in several different schemas that need to be merged into the data warehouse.

In the online database, Janice reviews the available tablespaces:

connect janice/janice@or92; Connected. select tablespace_name, status, contents from dba_tablespaces; TABLESPACE_NAME                STATUS    CONTENTS ------------------------------ --------- --------- SYSTEM                         ONLINE    PERMANENT UNDOTBS1                       ONLINE    UNDO TEMP                           ONLINE    TEMPORARY CWMLITE                        ONLINE    PERMANENT DRSYS                          ONLINE    PERMANENT EXAMPLE                        ONLINE    PERMANENT INDX                           ONLINE    PERMANENT ODM                            ONLINE    PERMANENT TOOLS                          ONLINE    PERMANENT USERS                          ONLINE    PERMANENT XDB                            ONLINE    PERMANENT TO_DATAMART                    ONLINE    PERMANENT 12 rows selected.

The TO_DATAMART tablespace contains the tables that need to go to the data warehouse database. The first step in copying a tablespace to another database is to make it read-only:

alter tablespace to_datamart read only; Tablespace altered. 

Next, Janice uses Export (EXP) to save the characteristics of the tablespace to a dump file. Note that the contents of the tablespace are not saved to the dump file; only the information about the objects in the tablespace is saved. She will use the datafiles that make up the tablespace to copy the data. In the following EXP command, Janice creates the dump file for the TO_DATAMART tablespace:

E:\TEMP>exp transport_tablespace=y           tablespaces=to_datamart file=exp_mart.dmp Export: Release 9.2.0.1.0 - Production on    Sat Nov 9 18:47:15 2002 Copyright (c) 1982, 2002, Oracle Corporation.     All rights reserved. Username: janice as sysdba Password: Connected to: Oracle9i Enterprise Edition       Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data      Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8MSWIN1252 character set      and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TO_DATAMART ... . exporting cluster definitions . exporting table definitions . . exporting table                    INVENTORIES . . exporting table                    SALES001 . . exporting table                    SALES002 ... . . exporting table                    SALES226 . . exporting table                    CUSTOMERS . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. E:\TEMP> 

In the next step, Janice copies the datafiles that compose the TO_DATAMART tablespace to the directory location where the rest of the data warehouse datafiles reside. Janice uses the data dictionary views V$TABLESPACE and V$DATAFILE to determine the operating system files that compose the TO_DATAMART tablespace:

select d.name "Filenames" from v$tablespace t, v$datafile d where t.ts# = d.ts# and t.name = ‘TO_DATAMART’; Filenames --------------------------------------- D:\ORACLE\ORADATA\OR92\TO_DATAMART.ORA 1 row selected.

Janice uses a standard operating system copy command to make a copy of the tablespace in the new database:

D:\> copy d:\oracle\oradata\or92\to_datamart.ora         d:\oracle\oradata\wh92         1 file(s) copied. D:\>

Back in the online database, Janice changes the source tablespace back to read-write:

connect janice/janice@or92; Connected. alter tablespace to_datamart read write; Tablespace altered.

At this point, the source database is back to its original state, the information about the TO_DATAMART tablespace has been saved to a dump file, and a copy of the TO_DATAMART tablespace datafile is ready to attach to the data warehouse database. Janice will run Import (IMP) to attach the tablespace to the data warehouse database, using many of the same options she used with Export to create the tablespace dump file:

E:\TEMP>imp transport_tablespace=y file=exp_mart.dmp    datafiles=(‘d:\oracle\oradata\wh92\to_datamart.ora’)    tablespaces=to_datamart Import: Release 9.2.0.1.0 - Production      on Sun Nov 10 08:55:26 2002 Copyright (c) 1982, 2002, Oracle Corporation.      All rights reserved. Username: janice as sysdba Password: Connected to: Oracle9i Enterprise Edition      Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data      Mining options JServer Release 9.2.0.1.0 - Production Export file created by EXPORT:V09.02.00      via conventional path About to import transportable tablespace(s) metadata... import done in WE8MSWIN1252 character set      and AL16UTF16 NCHAR character set . importing SYS’s objects into SYS . importing RJB’s objects into RJB . . importing table                  "INVENTORIES" . . importing table                  "SALES001" . . importing table                  "SALES002" ... . . importing table                  "SALES226" . . importing table                  "CUSTOMERS" Import terminated successfully without warnings. E:\TEMP>

A copy of the TO_DATAMART tablespace is now attached to the data warehouse database and ready for use by the marketing analysts:

connect janice/janice@wh92; Connected. select tablespace_name, status, contents      from dba_tablespaces      where tablespace_name = ‘TO_DATAMART’; TABLESPACE_NAME                STATUS    CONTENTS ------------------------------ --------- --------- TO_DATAMART                    READ ONLY PERMANENT 1 row selected.

Before the tablespace can be imported again into the data warehouse database, it must be taken offline and dropped. It is assumed that any objects in the TO_ DATAMART tablespace are copied to other tablespaces shortly after the TO_DATAMART tablespace is imported.

Cold Backups

A database cold backup is most likely the simplest way to make a backup of a database. A cold backup consists of making copies of the datafiles, the control files, and the initialization parameter files while the database is shut down. A cold backup is also known as a closed backup.

cold backup

A database backup performed while the database is shut down. Also known as a closed backup.

closed backup

See cold backup.

Cold backups are easy to do, but they have several disadvantages. The database is unavailable to users during a cold backup, so any database that must be available 24 hours a day is not a good candidate for a cold backup. In addition, a database media failure will result in some loss of data—any transactions that are recorded to the database since the last cold backup are lost.

Hot Backups

A hot backup is similar to a cold backup, except that the backup is performed while the database is open and available to users. A hot backup is also known as an open backup.

hot backup

A database backup performed while the database is open and available to users. Also known as an open backup.

open backup

See hot backup.

Hot backups are performed on one tablespace at a time. They are better than cold backups in that the database is always available to users, even while the backup is in progress.

To perform a hot backup, you must know the names of the datafiles that belong to the tablespace you are backing up. Janice, the DBA, needs to back up the USERS tablespace while the database is open, so she uses the V$TABLESPACE and V$DATAFILE views to find out the datafile names for the USERS tablespace:

select d.name "Filenames" from v$tablespace t, v$datafile d where t.ts# = d.ts# and t.name = ‘USERS’; Filenames ----------------------------------- D:\ORACLE\ORADATA\OR92\USERS01.DBF 1 row selected.

Before Janice initiates the backup, she marks the tablespace as being in a backup state:

alter tablespace users begin backup; Tablespace altered.

Now any transactions occurring against the tablespace while the backup is in progress will be correctly applied to the objects in the tablespace when the backup is complete.

In the next step, Janice performs a copy operation at the operating system command prompt, similar to the copy she performed when transporting a tablespace:

D:\> copy d:\oracle\oradata\or92\users01.dbf d:\backup         1 file(s) copied. D:\>

To finish the hot backup, Janice takes the tablespace out of backup mode:

alter tablespace users end backup; Tablespace altered.

During the time the tablespace was in backup mode, all objects in the tablespace were still available to users.

Log Miner

Oracle Log Miner is another tool the DBA can use to view past activity in the database. The Log Miner tool can help the DBA find changed records in redo log files by using a set of PL/SQL procedures and functions. Log Miner extracts all DDL and DML activity from the redo log files for viewing by a DBA via the dynamic performance view V$LOGMNR_CONTENTS. In addition to extracting the DDL and DML statements used to change the database, the V$LOGMNR_CONTENTS view also contains the DML or DDL statements needed to reverse the change made to the database. This is a good tool for not only pinpointing when changes were made to a table, but also for automatically generating the SQL statements needed to reverse those changes.

Log Miner works differently from Oracle’s flashback query feature. The flashback query feature allows a user to see the contents of a table at a specified time in the past; Log Miner can search a time period for all DDL against the table. A flashback query uses the undo information stored in the undo tablespace; Log Miner uses redo logs. Both of these tools can be useful for tracking down how and when changes to database objects took place.

Log Miner may be configured and used either from a SQL command line or via a GUI-based interface within Oracle Enterprise Manager (OEM) by selecting Tools > Database Applications > Logminer Viewer, as shown here.

This Log Miner session initiated through OEM shows a sequence of DML statements executed by GARY against the ORDERS table. The SQL Redo column shows the DML statement used to change the ORDERS table, and the SQL Undo column shows how to reverse the change made by the DML statement in the SQL Redo column. Double-clicking a row in the report brings up a second window that shows the complete text of both the SQL Undo and SQL Redo columns, as shown on the next page.

Recovery Manager

The Recovery Manager (RMAN) tool is an extensive and comprehensive set of tools that can streamline the backup and recovery of a database. It can be accessed via either a command line or a GUI interface through OEM by selecting Tools > Database Tools > Backup Management > Backup. Using RMAN can reduce errors by automating many of the tasks that a DBA would otherwise need to perform manually, such as checking a backup set for completeness or logging the results of a backup operation.

Recovery Manager (RMAN)

A comprehensive set of backup and recovery tools that can streamline the backup and recovery of a database.

RMAN can perform the following tasks:

Back up all database objects. RMAN can back up every individual type of database or filesystem object, or the entire database. It can back up tablespaces, datafiles, control files, and log files.

Log all backup operations. RMAN automatically logs the status of the backup as it occurs and when it completes.

Catalog backup information. Information about what database objects were backed up on what days is kept in an Oracle database.

Perform incremental backups. Only the changes to database objects are backed up in an RMAN incremental backup. This saves time and space. A full backup can occur weekly, with incremental backups performed during the week.

Create a duplicate of a database. A copy of an entire database can be made for testing a new release of a software application or testing an upgrade to a new release of the Oracle database software.

Test the recovery process. RMAN can review the contents of backups to validate that the database can be restored successfully in case of a catastrophic failure of the database.

The GUI version of RMAN includes a wizard, as shown below. This interface can help the DBA choose which objects are included in a backup, choose a backup strategy, and automate the backup process through OEM.