Chapter 3. Tracking the Bits and Bytes

As a DBA it is essential to know how data moves from one database component to the next; that is, the essential architectural infrastructure at the transaction level. Before continuing with this chapter, you should read the Oracle Database Concepts 11g Release 2 (11.2) of the documentation, the book every DBA should start with. There is a large amount of material in the concepts guide you need to be familiar with, and you should refer back to Sections I-IV as you read through this chapter.

The following list is a recommended shorter version the items in the concepts manual that you need to start with. If a topic isn't listed (for example, Table Clusters), it isn't as essential for understanding as the rest of this book. Come back to advanced topics as you have time or develop a need for that technology in the future.

• Part I Relational Data Structures: Tables, Indexes, Views, Data Integrity, and the Data Dictionary

• Part II Data Access: SQL

• Part III Transaction...

Dump block

Dump block gives you the view of the data at the block level, the smallest piece of storage for the data. Working through this chapter allows you to apply concepts of how data moves, block by block, through the physical structures of the database, really solidifying how transaction integrity is maintained. You would only use dump block on a production system with an Oracle Support Engineer, which is usually done during advanced recovery situations. The dump block is often used to determine the level and extent of corruption and what data can be recovered. It can also be used to resolve some tuning and performance issues.

Note

Do not do this in a production database.

Our examination of data blocks starts in Section 12-6 of the Concepts Manual.

Data block format: "Every Oracle data block has a format or internal structure that enables the database to track the data and free space in the block. This format is similar whether the data block contains table, index, or table cluster data...

Oracle's RDBMS Log Miner utility

Log Miner can help when questions such as the following come up: What was changed? Who changed it? And in what order?

When unauthorized people change data, they may assume that the record does not retain all changes if that information isn't viewable at the application level. There is a record of all changes that are logged, but it takes time and trouble to find that information.

The tool most often used is the PL/SQL package DBMS_LOGMNR, but the GUI Interface called Log Miner Viewer has been added to the OEM. There are quite a few examples in the Oracle Database Utilities Guide of how to use this utility for both the browser-based and PL/SQL versions. We will concentrate on when and how to find the data to restore.

You already should have a good understanding of the database structures that include the undo and redo logs: undo is generated when an end user starts changing data and redo is generated after the commit. Each is written to their own set of files...

Flash(back) Recovery Area (FRA)

You will see the FRA referred to as either the Flash Recovery Area (newer form) versus the older version called Flashback Recovery Area. They are the same thing, though Oracle decided to shift the emphasis so that the FRA should be used for all files associated with backup and recovery and not just Flashback logs.

If a mandatory archivelog destination becomes full at the disk level, it can cause the database to hang, as there is no room to write the archived log. FRA as an archivelog or flashback log destination can hang if you fail to accurately predict the amount of space needed to implement it. There are several recommendations from Oracle as what to store in this area, because it is primarily meant for backup and recovery files:

• Current control file

• Online redo logs

• Archived redo logs

• Control file autobackups

• Data file and control file copies

• Backup pieces

• Foreign archived redo log (logical standby database in continuous Log Miner mode. See MAA, Chapter 4)

Space...

Automatic Undo Management (AUM)

If you have installed an 11g database for this exercise, then AUM is the default method of Undo Management. With earlier versions of Oracle, Manual Undo Management was the only method available. DBAs needed to be good at the balancing act of managing rollback segments for different tasks in the database—batch jobs, data loads, smaller online transactions, and so on.

Identifying data in undo segments by flashing back to timestamp

There is a small trick in identifying data that may still exist in the undo segments. This won't work if the undo has already been overwritten, so it is best used when you are alerted to the mistake quickly. I have eliminated the possibility of flashback technology being involved by disabling it with the following commands (flashback is not required for this exercise):

SYS@NEWDB> SHUTDOWN IMMEDIATE;
SYS@NEWDB> STARTUP MOUNT;
SYS@NEWDB> ALTER DATABASE FLASHBACK OFF;
SYS@NEWDB> ALTER DATABASE OPEN;
SYS@NEWDB> show parameter...

When to use Log Miner

Log Miner has both a GUI interface in OEM as well as the database package, DBMS_LOGMNR. When this utility is used by the DBA, its primary focus is to mine data from the online and archived redo logs. Internally Oracle uses the Log Miner technology for several other features, such as Flashback Transaction Backout, Streams, and Logical Standby Databases. This section is not on how to run Log Miner, but looks at the task of identifying the information to restore.

The Log Miner utility comes into play when you need to retrieve an older version of selected pieces of data without completely recovering the entire database. A complete recovery is usually a drastic measure that means downtime for all users and the possibility of lost transactions. Most often Log Miner is used for recovery purposes when the data consists of just a few tables or a single code change. Other purposes for Log Miner will be touched upon in the Tuning chapter.

Make sure supplemental logging is turned...

Summary

In this chapter, we broke down an Oracle database into its smallest pieces—both logically and physically. We examined common threads on how to track transactions by Xid, SCN, and Timestamps. The physical components should be very clear to you as well—undo segments, redo logs, archived logs, and database files. And most important of all: how transactions travel through the physical parts of the database, from when the end user types in a change, to a row in a table identified by a rowid on a particular data block on a physical data file.

Several utilities were covered along with appropriate scenarios and suggested uses:

• Oracle's RDBMS SQL command dump block

• Oracle's RDBMS Log Miner utility

• Flashback Transaction Query and Backout

• Unix strings command

This chapter is a building block on which you keep adding as you continue through the book. The information in the next chapter (Chapter 4, Achieving Maximum Uptime) is based on the Maximum Availability Architecture standard created by Oracle...