Read only Tablespace Various Scenarios

Contents:- Read-only Tablespace, Recovering a Read-only Tablespace, Read-only Tablespace various Scenarios.

User managed Backup of Read only tablespace.

You do not have to put it in backup mode in order to make a copy of its data files.

You do not have to take the tablespace or data file offline before making a copy of it.

       Because Read-only tablespace are not being written to.

Restoring a Read-only Tablespace

 ü  When Restoring a read-only tablespace, take the tablespace offline.

 ü  Restore the data files belonging to the tablespace.

 ü  Bring the tablespace back online.

Scenarios when a Read-only Tablespace is changed to be read/write

 ü  Make a Backup of Read-only Tablespace.

 ü  Make the Tablespace Read-write.

 ü  Recover the tablespace.

Read-only Tablespace Recovery

Notes:- The method of recovering a read-only tablespace depends on the backup that are available and wheather the tablespace was altered to read/write or read/only

Case 1:- The tablespace being recovered read-only and was read-only when the last backup occurred.

In this case you can simply restore tablespace from backup, there is no need to apply the redo information.

Steps

Sql> create table demo(id number) tablespace inventory;

Sql> insert into demo values(1);

Sql> alter tablespace inventory read only;

Take fresh backup.

Disaster:- inventory tablespace lost

Sql> startup mount;

$> rman target /

Rman>restore tablespace inventory;

Sql> alter database open;

Case 2:- The tablespace being recovered is read-write, but was read-only when the last backup occurred.

In this case you need to restore tablespace from the backup and apply the redo information from the point when the tablespace made read/write.

Steps

Sql> alter tablespace inventory read only;

Take fresh backup.

Sql> alter tablespace inventory read write;

Sql> create table demo(id number) tablespace inventory;

Sql>insert into demo values(&id);

Sql>commit;

Disaster: inventory tablespace lost

Sql> startup mount force

$>rman target /

Rman>restore tablespace inventory;

Rman>recover tablespace inventory;

Sql>alter database open;

Case 3:- The tablespace being recovered read only, but was read/write when the last backup occurred.

Notes:- you should always backup a tablespace after making  it read only to avoid this situation. However if this occur, you must restore tablespace from the backup and recover upto the time that the tablespace was made read-only.

In all three cases, if the current control file is not available, the * denotes which control file backup should be used for recovery. this is necessary because when  a backup control file is used, the recovery process will require you to perform an open resetlogs. this updates the datafile headers and datafile can't be written to if they are read only.

Read-only  tablespace recovery issue

Special consideration must be taken for read-only tablespace when

i) re-creating control file

ii) renaming data file

iii) using a backup control files.

Steps

Sql> create table demo(id number) tablespace inventory;

Insert records into demo table and commit;

Take normal backup.

Sql> insert into demo values(&id);

Sql> commit;

Sql> alter tablespace inventory read only;

Disaster:- all controlfile deleted.

$> rman target / catalog rman_user/rman_user@dbc

Rman> run

{

Startup nomount;

Restore controfile from autobackup;

Sql ‘alter database mount’;

Recover database;

Sql ‘alter database open resetlogs’;

}

Disaster:- inventory tablespace lost;

Rman>restore tablespace inventory;

Rman>recover tablespace inventory;

Sql>alter database open;