Tablespace on ephemeral storage

etloracle

For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral".

This question is about taking advantage of such type of storage in installations of the Oracle database. Which breaks down into:

  1. What is a way to keep a tablespace's datafiles on ephemeral storage
    and have Oracle create those on boot (and possibly run some scripts to create/populate a few tables) and be ok when they are lost.
  2. What would be the implications for backups (there should be no backup of the ephemeral data).

  3. Any other possible consideration for tables and other object on top of it

    • possible optimizations: e.g. disable logging.
    • what is lost with a reboot (data or data+metadata)

The TEMP tablespace is a perfect candidate for this optimization and in fact for MS SQLServer's equivalent there are on the web howtos for doing just this.

Let's consider a data warehouse, as the reference use case, with a recurring job that imports many gigabytes of data (from CSVs or datapump) into a staging schema "STG" and a subsequent ETL process that saves the results on the production schema.

This workload would benefit a lot from having very fast read and write access on the staging schema, easily tolerating the volatility of its data.

Best Answer

Oracle does not record checkpoint information in tempfile. So, for the tempfile:

  1. Oracle is able to start even if tempfile is missing. You will get a message in the alter log and you should recreate tempfile.

  2. No backup is taken of tempfile so it's safe to loose it

  3. There is no need to trick with any storage parameter for tempfile if resides on ephemeral storage.

About physical data structure are not tempfile, it is not safe to put them in the ephemeral storage. Oracle keep track of changes in the datafile even if you have disabled logging. Disabling logging will have only effect in the recoverability of the database, redo and overall performance. Does not permit you to put datafile on ephemeral storage. Opening the DB with a missing database (database does not take care of how important were data on it) will raise.

ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'

And you have to manually deal with the issue. Hence the use of ephemeral storage with normal datafile have to be avoided.

Related Question