Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Chapter 3. Files - Part 5

Chapter 3. Files - Part 5

by Tom Kyte

From Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions, Berkeley, Apress, September 2005.

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5

Change Tracking File

The change tracking file is a new, optional file for use with Oracle 10g Enterprise Edition. The sole purpose of this file is to track what blocks have modified since the last incremental backup. In this fashion, the Recovery Manager (RMAN) tool can back up only the database blocks that have actually been modified without having to read the entire database.

In releases prior to Oracle 10g, an incremental backup would have had to read the entire set of database files to find blocks that had been modified since the last incremental backup. So, if you had a 1TB database to which you simply added 500MB of new data (e.g., a data warehouse load), the incremental backup would have read 1TB of data to find that 500MB of new information to backup. So, the incremental backup would have stored significantly less data in the backup, and it would have still read the entire database.

In Oracle 10g Enterprise Edition, that is no longer the case. As Oracle is running, and as blocks are modified, Oracle will optionally maintain a file that tells RMAN what blocks have been changed. The process of creating this change tracking file is rather simple and is accomplished via the ALTER DATABASE command:

ops$tkyte@ORA10GR1> alter database enable block change tracking
2 using file
3 '/home/ora10gr1/product/10.1.0/oradata/ora10gr1/ORA10GR1/changed_blocks.bct';
Database altered.

Caution: I’ll say this from time to time throughout the book: please bear in mind that commands that set parameters, change the database, and make fundamental changes should not be done lightly, and definitely should be tested prior to performing them on your “real” system. The preceding command will, in fact, cause the database to do more work. It will consume resources.

To turn off and remove the block change tracking file, you would use the ALTER DATABASE command once again:

ops$tkyte@ORA10GR1> alter database disable block change tracking;
Database altered.

ops$tkyte@ORA10GR1> !ls -l /home/ora10gr1/.../changed_blocks.bct
ls: /home/ora10gr1/.../changed_blocks.bct: No such file or directory

Note that that command will in fact erase the block change tracking file. It does not just disable the feature — it removes the file as well. You can enable this new block change tracking feature in either ARCHIVELOG or NOARCHIVELOG mode. But remember, a database in NOARCHIVELOG mode, where the redo log generated daily is not retained, cannot recover all changes in the event of a media (disk/device) failure! A NOARCHIVELOG mode database will lose data some day. We will cover these two database modes in more detail in Chapter 9.

Flashback Log Files

Flashback log files (or simply flashback logs) were introduced in Oracle 10g in support of the FLASHBACK DATABASE command, a new feature of the Enterprise Edition of the database. Flashback logs contain “before images” of modified database blocks that can be used to return the database to the way it was at some prior point in time.

Flashback Database

The FLASHBACK DATABASE command was introduced to speed up the otherwise slow process of a point in time database recovery. It can be used in place of a full database restore and a rolling forward using archive logs, and it is primarily designed to speed up the recovery from an “accident.” For example, let’s take a look at what a DBA might do to recover from an “accidentally” dropped schema, in which the right schema was dropped, just in the wrong database (it was meant to be dropped in the test environment). The DBA recognizes immediately the mistake he has made and immediately shuts down the database. Now what?

Prior to the flashback database capability, what would probably happen is this:

      1. The DBA would shut down the database.
      2. The DBA would restore the last full backup of database from tape (typically). This is generally a long process.
      3. The DBA would restore all archive redo logs generated since the backup that were not available on the system.
      4. The DBA would roll the database forward and stop rolling forward at a point in time just before the erroneous DROP USER command.
      5. The database would be opened with the RESETLOGS option.

This was a nontrivial process with many steps and would generally consume a large piece of time (time where no one would be accessing the database, of course). The causes of a point in time recovery like this are many: an upgrade script gone awry, an upgrade gone bad, an inadvertent command issued by someone with the privilege to issue it (a mistake, probably the most frequent cause), or some process introducing data integrity issues into a large database (again, an accident; maybe it was run twice instead of just once, or maybe it had a bug). Whatever the reason, the net effect was a large period of downtime.

The steps to recover in Oracle 10g Enterprise Edition, assuming you configured the flashback database capability, would be as follows:

      1. The DBA shuts down the database.
      2. The DBA startup-mounts the database and issues the flashback database command, using either an SCN, the Oracle internal clock, or a timestamp (wall clock time), which would be accurate to within a couple of seconds.
      3. The DBA opens the database with resetlogs.

To use this feature, the database must be in ARCHIVELOG mode and must have been set up to enable the FLASHBACK DATABASE command. What I’m trying to say is that you need to have set up this capability prior to having a need to use it. It is not something you can enable after the damage is done; you must make a conscious decision to use it.

Flash Recovery Area

The Flash Recovery Area is a new concept in Oracle 10g. For the first time in many years (over 25 years), the basic concept behind database backups has changed in Oracle. In the past, the design of backup and recovery in the database was built around the concept of a sequential medium, such as a tape device. That is, random access devices (disk drives) were always considered too expensive to waste for mere backups. You used relatively inexpensive tape devices with large storage capacities.

Today, however, you can buy terabytes of disk storage at a very low cost. In fact, by 2007, HP intends to ship desktop computers with terabyte disk drives. I remember my first hard drive on my personal computer: a whopping 40MB. I actually had to partition it into two logical disks because the OS I was using (MS-DOS at the time) could not recognize a disk larger than 32MB. Things have certainly changed in the last 20 years.

The Flash Recovery Area in Oracle 10g is a new location where Oracle will manage many of the files related to database backup and recovery. In this area (an area being a set-aside area of disk for this purpose; a directory, for example), you could find

      • Copies of data files on disk
      • Incremental backups of your database
      • Redo logs (archived redo logs)
      • Control files and backups of control files
      • Flashback logs

This new area is used to allow Oracle to manage these files, for the server to have knowledge of what is on disk and what is not on disk (and perhaps on tape elsewhere). Using this information, the database can perform operations like a disk-to-disk restore of a damaged data file or the flashing back (a “rewind” operation) of the database to undo an operation that should not have taken place. For example, you could use the flashback database command to put the database back the way it was five minutes ago (without doing a full restore of the database and a point in time recovery). That would allow you to “undrop” that accidentally dropped user account.

The Flash Recovery Area is more of a “logical” concept. It is a holding area for the file types discussed in this chapter. Its use is optional — you do not need to use it — but if you want to use some advanced features such as the Flashback Database, you must use this area to store the information.

DMP Files (EXP/IMP Files)

Export and Import are venerable Oracle data extraction and load tools that have been around for many versions. Export’s job is to create a platform-independent DMP file that contains all of the required metadata (in the form of CREATE and ALTER statements), and optionally the data itself to re-create tables, schemas, or even entire databases. Import’s sole job in life is to read these DMP files, and execute the DDL statements and load any data it finds.

DMP files are designed to be backward-compatible, meaning that newer releases can read older releases’ DMP files and process them successfully. I have heard of people exporting a version 5 database and successfully importing it into Oracle 10g (just as a test!). So Import can read older version DMP files and process the data therein. The converse, however, is most definitely not true: the Import process that comes with Oracle9i Release 1 cannot — will not — successfully read a DMP file created by Oracle9i Release 2 or Oracle 10g Release 1. For example, I exported a simple table from both Oracle 10g Release 1 and Oracle9i Release 2. Upon trying to use these DMP files in Oracle9i Release 1, I soon discovered Oracle9i Release 1 import will not even attempt to process the Oracle 10g Release 1 DMP file:

[tkyte@localhost tkyte]$ imp userid=/ full=y file=10g.dmp
Import: Release - Production on Sun Jan 2 21:08:56 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

When processing the Oracle9i Release 2 file, things are not that much better:

[tkyte@localhost tkyte]$ imp userid=/ full=y file=9ir2.dmp
Import: Release - Production on Sun Jan 2 21:08:42 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
IMP-00017: following statement failed with ORACLE error 922:
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
Import terminated successfully with warnings.

While 9i Release 1 tried to read the file, it could not process the DDL contained therein. In Oracle9i Release 2 a new feature, table compression, was added. Hence Export in that version started adding NOCOMPRESS or COMPRESS as a keyword to each and every CREATE TABLE statement. The DDL from Oracle9i Release 2 does not work in Oracle9i Release 1.

If, however, I use the Oracle9i Release 1 Export tool against either Oracle9i Release 2 or Oracle 10g Release 1, I will get a valid DMP file that can be successfully imported into Oracle9i Release 1. So, the rule with DMP files is that they must be created by a version of Export that is less than or equal to the version of Import that will be used against them. To import data in Oracle9i Release 1, you must use Oracle9i Release 1’s Export (or you could use a version 8i Export process as well; the DMP file must be created by a release of Export less than or equal to Oracle9i Release 1).

These DMP files are platform independent, so you can safely take an Export from any platform, transfer it to another, and import it (as long as the versions of Oracle permit). One caveat, however, with Windows and FTPing of files is that Windows will consider a DMP file a “text” file by default and will tend to convert linefeeds (the end-of-line marker on UNIX) into carriage return/linefeed pairs, thus totally corrupting the DMP file. When FTPing a DMP file inWindows, make sure you’re doing a binary transfer, and if the import won’t work, check the source and target file sizes to make sure they’re the same. I can’t recall how many times this issue has brought things to a screeching halt while the file has to be retransferred.

DMP files are binary files, meaning you won’t be editing them to change them. You can extract a large amount of information from them — CREATE DDL, and more — but you won’t be editing them in a text editor (or any sort of editor, actually). In the first edition of Expert One-on-One Oracle (which you as owner of the second edition have full access to in electronic form), I spent a great deal of time discussing the Import and Export utilities and working with DMP files. As these tools are falling out of favor, in place of the infinitely more flexible Data Pump utilities, I’ll defer a full discussion of how to manipulate them, extract data from them, and use them in general to the online first edition.

Data Pump Files

Data Pump is a file format used by at least two tools in Oracle 10g. External tables can load and unload data in the Data Pump format, and the new import/export tools IMPDP and EXPDP use this file format much in the way IMP and EXP used the DMP file format.

Note: The Data Pump format is exclusive to Oracle 10g Release 1 and above — it did not exist in any Oracle9i release, nor can it be used with that release.

Pretty much all of the same caveats that applied to DMP files mentioned previously will apply over time to Data Pump files as well. They are cross-platform (portable) binary files that contain metadata (not stored in CREATE/ALTER statements, but rather in XML) and possibly data. That they use XML as a metadata representation structure is actually relevant to you and I as end users of the tools. IMPDP and EXPDP have some sophisticated filtering and translation capabilities never before seen in the IMP/EXP tools of old. This is in part due to the use of XML and the fact that a CREATE TABLE statement is not stored as a CREATE TABLE, but rather as a marked-up document. This permits easy implementation of a request like “Please replace all references to tablespace FOO with tablespace BAR.” When the metadata was stored in the DMP file as CREATE/ALTER statements, the Import utility would have had to basically parse each SQL statement before executing it in order to accomplish this feat (something it does not do). IMPDP, however, just has to apply a simple XML transformation to accomplish the same — FOO, when it refers to a TABLESPACE, would be surrounded by <TABLESPACE>FOO</TABLESPACE> tags (or some other representation).

The fact that XML is used has allowed the EXPDP and IMPDP tools to literally leapfrog the old EXP and IMP tools with regard to their capabilities. In Chapter 15, we’ll take a closer look at these tools in general. Before we get there, however, let’s see how we can use this Data Pump format to quickly extract some data from database A and move it to database B. We’ll be using an “external table in reverse” here.

External tables, originally introduced in Oracle9i Release 1, gave us the ability to read flat files — plain old text files — as if they were database tables. We had the full power of SQL to process them. They were read-only and designed to get data from outside Oracle in. External tables in Oracle 10g Release 1 and above can go the other way: they can be used to get data out of the database in the Data Pump format to facilitate moving the data to another machine, another platform. To start this exercise, we’ll need a DIRECTORY object, telling Oracle the location to unload to:

ops$tkyte@ORA10G> create or replace directory tmp as '/tmp'
2 /
Directory created.

Next, we’ll unload the data from the ALL_OBJECTS view. It could be from any arbitrary query, involving any set of tables or SQL constructs we want:

ops$tkyte@ORA10G> create table all_objects_unload
2 organization external
3 ( type oracle_datapump
4 default directory TMP
5 location( 'allobjects.dat' )
6 )
7 as
8 select * from all_objects
9 /
Table created.

And that literally is all there is to it: we have a file in /tmp named allobjects.dat that contains the contents of the query select * from all_objects. We can peek at this information:

ops$tkyte@ORA10G> !head /tmp/allobjects.dat
<?xml version="1.0"?>

That is just the head, or top, of the file; binary data is represented by the ....... (don’t be surprised if your terminal “beeps” at you when you look at this data). Now, using a binary FTP (same caveat as for a DMP file!), I moved this allobject.dat file to a Windows XP server and created a directory object to map to it:

tkyte@ORA10G> create or replace directory TMP as 'c:\temp\'
2 /
Directory created.

Then I created a table that points to it:

tkyte@ORA10G> create table t
15 )
16 organization external
17 ( type oracle_datapump
18 default directory TMP
19 location( 'allobjects.dat' )
20 )
21 /
Table created.

And now I’m able to query the data unloaded from the other database immediately:

tkyte@ORA10G> select count(*) from t;


That is the power of the Data Pump file format: immediate transfer of data from system to system over “sneaker net” if need be. Think about that the next time you’d like to take a subset of data home to work with over the weekend while testing.

One thing that wasn’t obvious here was that the character sets were different between these two databases. If you notice in the preceding head output, the character set of my Linux database WE8ISO8859P1 was encoded into the file. My Windows server has this:

tkyte@ORA10G> select *
2 from nls_database_parameters
3 where parameter = 'NLS_CHARACTERSET';

------------------------------ -----------------

Oracle has the ability now to recognize the differing character sets due to the Data Pump file format and deal with them. Character-set conversion can be performed on the fly as needed to make the data “correct” in each database’s representation.

Again, we’ll come back to the Data Pump file format in Chapter 15, but this section should give you an overall feel for what it is about and what might be contained in the file.

Flat Files

Flat files have been around since the dawn of electronic data processing. We see them literally every day. The alert log described previously is a flat file.

I found these definitions for “flat file” on the Web and feel they pretty much wrap it up:

An electronic record that is stripped of all specific application (program) formats. This allows the data elements to be migrated into other applications for manipulation. This mode of stripping electronic data prevents data loss due to hardware and proprietary software obsolescence.1

A computer file where all the information is run together in a signal character string.2

A flat file is simply a file whereby each “line” is a “record,” and each line has some text delimited, typically by a comma or pipe (vertical bar). Flat files are easily read by Oracle using either the legacy data-loading tool SQLLDR or external tables — in fact, I will cover this in detail in Chapter 15 (external tables are also covered in Chapter 10). Flat files, however, are not something produced so easily by Oracle — for whatever reason, there is no simple commandline tool to export information in a flat file. Tools such as HTMLDB and Enterprise Manager facilitate this process, but there are no official command-line tools that are easily usable in scripts and such to perform this operation.

That is one reason I decided to mention flat files in this chapter: to propose a set of tools that is capable of producing simple flat files. Over the years, I have developed three methods to accomplish this task, each appropriate in its own right. The first uses PL/SQL and UTL_FILE with dynamic SQL to accomplish the job. With small volumes of data (hundreds or thousands of rows), this tool is sufficiently flexible and fast enough to get the job done. However, it must create its files on the database server machine, which is sometimes not the location we’d like for them. To that end, I have a SQL*Plus utility that creates flat files on the machine that is running SQL*Plus. Since SQL*Plus can connect to an Oracle server anywhere on the network, this gives us the ability to unload to a flat file any data from any database on the network. Lastly, when the need for total speed is there, nothing but C will do (if you ask me). To that end, I also have a Pro*C command-line unloading tool to generate flat files. All of these tools are freely available at, and any new tools developed for unloading to flat files will appear there as well.


In this chapter, we explored the important types of files used by the Oracle database, from lowly parameter files (without which you won’t even be able to get started) to the all important redo log and data files. We examined the storage structures of Oracle from tablespaces to segments, and then extents, and finally down to database blocks, the smallest unit of storage. We reviewed how checkpointing works in the database, and we even started to look ahead at what some of the physical processes or threads of Oracle do.

1 See

2 See


Thomas Kyte is the Vice President of the Core Technologies Group at Oracle Corporation and has been with them since version 7.0.9 was released in 1993. Kyte, however, has been working with Oracle since version 5.1.5c. At Oracle Corp., Kyte works with the Oracle database, and more specifically, he helps clients who are using the Oracle database and works directly with them specifying and building their systems or rebuilding and tuning them. Prior to working at Oracle, Kyte was a systems integrator who built large scale, heterogeneous databases and applications for military and government clients. Tom Kyte is the same “Ask Tom” whose column appears in Oracle Magazine, where he answers questions about the Oracle database and tools that developers and DBAs struggle with every day.

Contributors : Tom Kyte
Last modified 2006-01-20 01:00 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone