Re: Backup and Recovery

jantonio_at_worldgate.com (JACK) wrote:

>Looking for thoughts on inventive methods to do a point in time recovery of a single tablespace.
>Have a situation where I have static historical data maintained in read-only format. Want to do nightly hot backups
>of the read/write portion of the database, but no backups on the read-only portion. Have taken a permanent cold
>backup of those tablespaces that are read only.
 

>The scenario occurs where the drives holding the read-only data get blown away, so I want to restore the read-only
>tablespaces to their original backup date. I don't want to have to apply logs to this tablespace period. I just want it
>brought back online as of the date it was backed up.
 

>Currently, as of my last go round with this, point intime recovery can only occur a database level, it cannot occur at
>a tablespace or datafile level. Any thoughts??

You don't mention your database version, the following applies to Oracle7.1 and above:

alter tablespace READ_ONLY_TABLESPACE_NAME read only;

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.

<quote>
CHAPTER 3. READ-ONLY TABLESPACES (Server documentation addendum for 7.1)

This chapter describes read-only tablespaces, including:

	·	the use of read-only tablespaces
	·	making tablespaces read-only
	·	making read-only tablespaces writeable
	·	making backups of read-only tablespaces
	·	recovery and read-only tablespaces

The ALTER TABLESPACE command has two new options: READ ONLY and READ WRITE. The files of a read-only tablespace are never updated by Oracle, and are therefore allowed to reside on read-only media, such as CD-ROMs or WORM drives. The primary purpose of read-only tablespaces is to eliminate the need to perform

backup and recovery of large, static portions of a database.

Note: Because a tablespace can only be brought online to the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data

publishing requirements.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable
again.

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.

It is possible to drop items, such as tables and indexes, from a read-only tablespace, just as they can be dropped from an offline tablespace. However, objects cannot be created or altered in a read-only tablespace.

</quote>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Mon Dec 11 1995 - 00:00:00 CET