News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

READ ONLY Tablespace Restore and Recovery

  • Posted by Gavin Soorma
  • On August 12, 2009
  • 0 Comments
  • check readonly, read only tablespace, recovery, skip readonly

Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments.

Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets.

But we need to keep in mind that we need to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups.

However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail.

Let us illustrate the same with an example where we have made the USERS tablespace read only and then simulated a media failure by deleting the three datafiles of the USERS tablespace at the OS level.

We will first attempt a normal restore and recovery and see how that fails. We follow that by using the CHECK READONLY keywords and see how the restore and recovery succeeds.

 restore database;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:10:36
Finished restore at 12-AUG-09
RMAN> recover database;

Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
.....
.......

archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:43
Finished recover at 12-AUG-09

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/12/2009 12:55:30
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/testdb/users03.dbf'

Now, the read only tableaspaces are restored as well, but we notice the recovery stage will skip these tablespaces since no recovery is required as no changes have happened on these tablespaces since the last backup of these tablespaces after they were made read only.

RMAN> restore database check readonly;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/testdb/users03.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
restoring datafile 00006 to /u02/oradata/testdb/users02.dbf
restoring datafile 00009 to /u02/oradata/testdb/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:05:25
Finished restore at 12-AUG-09



RMAN> recover database;
Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
destination for restore of datafile 00002: /u02/oradata/testdb/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/testdb/sysaux01.dbf
destination for restore of datafile 00005: /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0akmb8uu_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0akmb8uu_1_1 tag=TAG20090810T120901
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 36 is already on disk as file /u02/oradata/testdb/arch/arch.36.1.694170424.log
archive log thread 1 sequence 37 is already on disk as file /u02/oradata/testdb/arch/arch.37.1.694170424.log
archive log thread 1 sequence 38 is already on disk as file /u02/oradata/testdb/arch/arch.38.1.694170424.log
archive log thread 1 sequence 39 is already on disk as file /u02/oradata/testdb/arch/arch.39.1.694170424.log
archive log thread 1 sequence 40 is already on disk as file /u02/oradata/testdb/arch/arch.40.1.694170424.log
archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:39
Finished recover at 12-AUG-09

RMAN> alter database open;

database opened
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *