Lesson 9

You can define multiple locations for "Archive Log Files" using the 'export' command in Oracle. To do this, you can use the `LOG_ARCHIVE_DEST_n`initialization parameters, where`n` is an integer from 1 to 31. For example, the following command will archive log files to two locations:

export LOG_ARCHIVE_DEST_1='location1'
export LOG_ARCHIVE_DEST_2='location2'

You can also use the `LOG_ARCHIVE_DUPLEX_DEST` initialization parameter to specify a secondary archive destination. For example, the following command will archive log files to the primary destination `location1` and the secondary destination `location2`:

export LOG_ARCHIVE_DEST_1='location1'
export LOG_ARCHIVE_DUPLEX_DEST='location2'

When you use multiple archive destinations, Oracle will try to archive log files to the primary destination first. If the primary destination is unavailable, Oracle will try to archive log files to the secondary destination. If both the primary and secondary destinations are unavailable, Oracle will not archive the log files.
Example: Here is an example of how to export log files to multiple locations using the `export` command:

export LOG_ARCHIVE_DEST_1='location1'
export LOG_ARCHIVE_DEST_2='location2'
export expdp hr/hr full=y dumpfile=oradata1:dumpfile1.dmp,oradata2:dumpfile2.dmp filesize=5GB logfile=oralog.log

This command will export the entire database to two dump files, `dumpfile1.dmp` and `dumpfile2.dmp`, which will be located in the directories `oradata1` and `oradata2`, respectively.

By now, you have learned about the other improvements within Oracle, with regard to backup and recovery.
Now that you have completed this module, you should be able to:

1. Define multiple locations for archive log files
2. Use DBMS_REPAIR package to detect and repair corrupt blocks
3. Use LogMiner to query the archive log files
4. Use the LIST and REPORT commands within RMAN
5. Query the data dictionary for backup views
6. Describe the improvements in export and import

Oracle Database provides different methods for detecting and correcting data block corruption. One method of correction is to drop and re-create an object after the corruption is detected. However, this is not always possible or desirable. If data block corruption is limited to a subset of rows, then another option is to rebuild the table by selecting all data except for the corrupt rows. Another way to manage data block corruption is to use the DBMS_REPAIR package. You can use DBMS_REPAIR to detect and repair corrupt blocks in tables and indexes. You can continue to use objects while you attempt to rebuild or repair them.
Note: Any corruption that involves the loss of data requires analysis and understanding of how that data fits into the overall database system. Depending on the nature of the repair, you might lose data, and logical inconsistencies can be introduced. You must determine whether the repair approach provided by this package is the appropriate tool for each specific corruption problem.

This section describes the procedures contained in the DBMS_REPAIR package and notes some limitations and restrictions on their use.

DBMS_REPAIR procedures have the following limitations:

1. Tables with LOB datatypes, nested tables, and varrays are supported, but the out of line columns are ignored.
2. Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.
3. Index-organized tables and LOB indexes are not supported.
4. The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
5. The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long.

The following terms were introduced in this module:

1. Optimizer: Optimization is a process of choosing the most efficient way to execute a SQL statement. A part of Oracle called the optimizer calculates the most efficient way to execute a SQL statement. The optimizer evaluates several factors to select among alternative access paths.
2. Handle: A handle is a connection to the database server, which completes a transaction for the user.
3. Deprecated: When a certain feature or functionality is no longer advisable for use or will no longer be supported on the Oracledatabase server, that feature is called deprecated.
4. Metadata: The system tables and views of Oracle are metadata of the database.

The next module explores using Enterprise Manager for backup and recovery.

Click the Quiz link below to review your knowledge of the material in this module.
Data Dictionary Recovery Views - Quiz