7

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.

3
  • Great question! One can certainly take a tablespace offline, see docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces005.htm But I suspect the easiest way to automate this would be to drop the tablespaces before shutting down the Oracle instance. Then there's the issue of what would happen on a machine crash. I'm more of a "logical DBA" so I'll leave it to a "physical DBA" to answer this question. May 16, 2013 at 7:42
  • Can you give a use-case for this? May 20, 2013 at 11:23
  • @DavidAldridge: any Oracle deployment on the Amazon cloud is a use-case. May 22, 2013 at 12:37

3 Answers 3

2

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.

2
  • Thanks. So do you think that just creating a temporary tablespace on top tempfiles in the ephemeral storage is enough? After a reboot it will just have all empty tables or will it loose the schema as well? May 22, 2013 at 13:10
  • @AndreaRatto Temporary tablespace does not contain any table but only transient (temporary) object like GTT (Global Temporary Table) and data have to be sorted in a DML process for example. There is no SCHEMA in temporary table or other standard objects.
    – Ste
    May 22, 2013 at 13:15
2

As Ste says in his answer, temp tablespaces should be fine for this, so global temporary tables and other transient storage would be good.

If you wanted to use permanent tablespaces then you could DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS before system shutdown if the storage was going to be lost, and recreate on startup. You'd probably want to have something on startup to handle loss of the data files if the RDBMS had not removed them itself.

2
  • The startup after a failure is the tricky part no doubts... May 22, 2013 at 13:30
  • Yes, quite so -- of course db startup ought to be a fairly rare event, conventionally. May 22, 2013 at 15:46
1

In addition to what the others have said, you could keep some data in flat files and them copy the files to the ephemeral storage on startup. You could then access it using external tables. I don't know if this would be faster or better, but it is another possible use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.