Data files versus temporary files

Physically, with respect to the file system, datafiles and tempfiles are the same, but how they are used by Oracle is quite different. I wouldn’t say it is a common mistake (although I have seen it in more than one place), but it is one to easily fall into, and that is when creating the temporary tablespace is to omit a key word in the CREATE TABLESPACE statement. An alternate means is to omit a key word in the storage clause. Where is this pitfall avoided? When using the dbca (Database Configuration Assistant) or installing the seed database via Oracle Universal Installer. When it is likely to be encountered? When creating a database via command line or a script.

Let’s execute the statement below. The intent is to create a temporary tablespace and make TEMP2 that user’s temporary tablespace.

SQL> CREATE TABLESPACE temp2 datafile
  2  '/u064/oradata/train/tempxx.dbf' 
  3       SIZE 20M REUSE
  4       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Tablespace created.

SQL> CREATE USER APPS
  2    IDENTIFIED BY APPS
  3    DEFAULT TABLESPACE USERS
  4    TEMPORARY TABLESPACE TEMP2
  5    PROFILE DEFAULT
  6    ACCOUNT UNLOCK;

User created.

On another database, let’s try the same operation.

SQL> CREATE TABLESPACE temp2 datafile
  2  'D:\oracle\product\10.2.0\oradata\ORCL\tempxx.dbf' 
  3       SIZE 20M REUSE
  4       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Tablespace created.

SQL> CREATE USER APPS
  2    IDENTIFIED BY APPS
  3    DEFAULT TABLESPACE USERS
  4    TEMPORARY TABLESPACE TEMP2
  5    PROFILE DEFAULT
  6    ACCOUNT UNLOCK;
CREATE USER APPS
*
ERROR at line 1:
ORA-10615: Invalid tablespace type for temporary tablespace

Why did this work in the first case and not the second? The obvious difference is the operating system (UNIX versus Windows), but everything else, script-wise, is the same. What is not obvious are the database versions and the space management of the SYSTEM tablespace. In the first case, the operation was run on an 8.1.7 database using DMT, and the second on 10.2.0.3, using LMT. The significant part of this can be refined to 8i versus 10g. With respect to the idea of a temporary tablespace, we see the same thing, but different. The reason is due to changes in how Oracle uses/assigns/manages temporary tablespaces.

The evolution has also gone from CREATE TABLESPACE ... TEMPORARY to CREATE TEMPORARY TABLESPACE. MetaLink note 160426.1 contains a detailed discussion of more differences between the use of datafiles and tempfiles.

Aside from being relatively throwaway and recreate when necessary, a significant feature of tempfiles has to do with RMAN. What takes place in tempfiles? Sorts, group by, and objects of a temporary nature, that is, things that are session-specific. Nothing permanent is stored. If nothing of permanence is stored, then what need would there to be to backup tempfiles? If you’re thinking no reason at all, then you are in alignment with how RMAN views tempfiles. RMAN does not backup tempfiles. It does store information about them, but not the actual files themselves. If you are backing up tempfiles otherwise, it is a waste of time and resources.

Delete obsolete versus delete expired

Speaking of RMAN, the DELETE command obviously deletes what you tell it to, but what is the difference between DELETE OBSOLETE and DELETE EXPIRED? If a backup item (piece or set) is expired, isn’t it also obsolete? Or, if obsolete, is it also expired? One way to look at the difference is this: not needed versus not found. Something obsolete may or may not be found. Something expired may or may not be needed. What does DELETE do?

DELETE removes the physical files from the backup media, deletes the record of the backup from the recovery catalog (if RMAN is connected to a recovery catalog), and updates the records of these backups in the control file to status DELETED.

In the obsolete case, what helps to clarify the difference between OBSOLETE and EXPIRED is the retention policy. If a backup item is no longer needed for recovery – because it is older than the retention policy – then it is obsolete. What does DELETE OBSOLETE do?

The RMAN DELETE command supports an OBSOLETE option, which deletes backups that are obsolete, that is, no longer needed to satisfy specified recoverability requirements. You can delete files obsolete according to the configured default retention policy, or another retention policy that you specify as an option to the DELETE OBSOLETE command. As with other forms of the DELETE command, the files deleted are removed from backup media, deleted from the recovery catalog, and marked as DELETED in the control file.

The next clarification concerns the EXPIRED status. How does an object become expired?

When the CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired backups from the RMAN repository. If the expired files still exist, then the DELETE EXPIRED command terminates with an error.

As you can, something will be deleted when using the DELETE command. It is either a physical item, or metadata. Same thing, but different.

UNDO versus Flashback

The difference between undo and redo is much clearer as those functions have been around for quite a while. REDO allows you to replay an action, much like a recording on a tape, whereas UNDO can be thought of as playing the tape in reverse to undo changes. So if UNDO lets you go back in time, and flashback lets you go back in time, what’s the difference between them?

The simple definition behind undo is that “Oracle maintains information to nullify changes made to the database.” Using undo, Oracle can “rollback an active transaction, recover a terminated transaction, provide read consistency, and recover from logical corruptions.”

Flashback features “provide ways to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.” Using flashback, Oracle can “perform queries that return past data, perform queries that return metadata that shows a detailed history of changes to the database, and recover tables or rows to a previous point in time.”

Both rely on undo data. When considering the undo management system (as in AUM), we clearly know that data for undo is stored in the UNDO tablespace. For flashback operations, is it the same undo data stored in the UNDO tablespace, or is it undo data stored in logs in the Flash Recovery Area? Or, is it a combination of both? Does it depend on which type of flashback operation is being considered?

With flashback, the flavors are (10g unless noted otherwise) Flashback Database, Flashback Table, Flashback Drop, Flashback Query (available in 9i), Flashback Version Query and Flashback Transaction Query, and Flashback Data Archive (in 11g). Flashback logs “are used to perform flashback database operations.” Flashback database differs from Flashback Table or Query in that flashback logs are specific to the database option, whereas the other two flashback features are dependent upon our “normal” undo data, so there is a difference in where the undo data comes from. Flashback logs are found only in the flash recovery area, while the UNDO datafiles can be anywhere. Once again, same thing, but different in terms of how the rewind effect is implemented.

In Closing

As you can see, there are some subtle (or not) differences between similar operations, features, and attributes. Another operation where things are mostly the same, but potential differences can arise, is in export/import. Suppose you want to work with one schema, migrate it from one database to another. In the source, you could perform a full export and at the target, import the schema. Or, at the source, export the schema, and then at the target import full. Are those equivalent operations? Something to try on your own and see if you can detect any differences.

Back to DBAsupport.com