Oracle Concepts: Splitting Hairs

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 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:oracleproduct10.2.0oradataORCLtempxx.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.

Type versus management

Tablespace management

Type of file

Locally managed

Dictionary managed

Datafiles

Impossible

Possible

Tempfiles

Possible

Impossible

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.

»


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.
Previous articleFirebird Roadmap 2009
Next articleIntro to MySQL Proxy

Latest Articles