Oracle Concepts: Splitting Hairs
January 14, 2009
How many times have you heard the phrase, Same thing, but different? In Oracle, there are many concepts where this applies. Physical files, for example, can be the same, but how they are used is different. RMAN operations can delete, but the type of backup item deleted can be different. Replaying database operations can be the same, but different as to how that is performed. The point of this article is to not really split hairs, but to help refine or define the differences in things that at one level appear to be the same, but are in fact different.
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 wouldnt 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.
Lets execute the statement below. The intent is to create a temporary tablespace and make TEMP2 that users 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, lets 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 youre 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, isnt 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?
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 next clarification concerns the EXPIRED status. How does an object become expired?
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, whats 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.
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.