Scenario given in this post shows where a table is using two tablespaces for storage, while the table remain in one tablespace the lob column related segments of that table are stored in a different tablespace. This type of table configuration is used to store transient data such as application session information. The session id is stored in a 2k tablespace while the session data (java serialized objects) stored in a 32kB tablespace. However for the testing 8k block size tablespaces are used for both table and lob segments. Database version is 11.2.0.3.
Set up the test case by creating the two tablespaces. Here the excbackup tablespace will be excluded from the backup.
create tablespace EXCBACKUP DATAFILE size 10m; create tablespace INCBACKUP DATAFILE size 10m; alter user asanga quota unlimited on excbackup quota unlimited on incbackup;Create the table and populate it with data. The disable storage in row is important as the inserted data is less than 4k without disabling storage in row the lob segment will also get stored in the inbackup tablespace.
create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE STORAGE IN ROW);
begin
for i in 1 .. 100
loop
insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
end loop;
end;
/
select segment_name,tablespace_name,bytes from user_segments;
SEGMENT_NAME TABLESPACE_NAME BYTES
------------------------------ ------------------------------ ----------
Y INCBACKUP 65536
SYS_IL0000105332C00002$$ EXCBACKUP 131072
LOBSEG EXCBACKUP 8388608It could be seen that lob segments are stored in the excbackup tablespace.Configure RMAN such that excbackup tablespace is excluded when a full database backup is done.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE excbackup; using target database control file instead of recovery catalog Tablespace EXCBACKUP will be excluded from future whole database backups new RMAN configuration parameters are successfully stored RMAN> show exclude; RMAN configuration parameters for database with db_unique_name FGACDB are: CONFIGURE EXCLUDE FOR TABLESPACE 'EXCBACKUP';Once the configuration is done run a full database backup including archive logs. The truncated output below shows that datafile belonging to excbackup is not part of the backup
RMAN> backup database plus archivelog delete all input; ... ... archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/archivelog/2014_06_06/o1_mf_1_7_9s3s0jxs_.arc RECID=62 STAMP=849545633 Finished backup at 06-JUN-14 Starting backup at 06-JUN-14 using channel ORA_DISK_1 file 5 is excluded from whole database backup channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00012 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf input datafile file number=00013 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf input datafile file number=00014 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf input datafile file number=00015 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf input datafile file number=00017 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf input datafile file number=00001 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf input datafile file number=00002 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf input datafile file number=00003 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf input datafile file number=00004 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf input datafile file number=00006 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf channel ORA_DISK_1: starting piece 1 at 06-JUN-14 channel ORA_DISK_1: finished piece 1 at 06-JUN-14 piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 06-JUN-14 ... ...Next step is the restore and recovery.
To simulate a situation where all data files are lost, database was shutdown and all data files removed (using OS utility) before the restore. The restore steps are given below.
RMAN> startup nomount; RMAN> restore controlfile from autobackup; RMAN> alter database mount; RMAN> restore database; .... .... file 5 is excluded from whole database backup channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf channel ORA_DISK_1: restoring datafile 00012 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf channel ORA_DISK_1: restoring datafile 00013 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf channel ORA_DISK_1: restoring datafile 00014 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf channel ORA_DISK_1: restoring datafile 00017 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402 .... ....Again the skipping of the file belonging to excbackup tablespace is mentioned during the restore. Since the backupset used here doesn't contain the excbackup it is possible to use the restore database command. However if the restore process is using a backupset that has a particular tablespace and it need to be excluded then use RESTORE DATABASE SKIP TABLESPACE command.
Unlike the restore command the recover database will not work
RMAN> recover database; Starting recover at 06-JUN-14 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 06/06/2014 16:58:43 RMAN-06094: datafile 5 must be restoredRecovery will complain the excluded file must be restored. To overcome this use recover command with skip tablespace. The tablespace name must be given in upper case.
RMAN> recover database skip tablespace 'excbackup'; # failure due to tablespace name being specified in lower case Starting recover at 06-JUN-14 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 06/06/2014 16:59:00 RMAN-06094: datafile 5 must be restored RMAN> recover database skip tablespace 'EXCBACKUP'; Executing: alter database datafile 5 offline starting media recovery archived log for thread 1 with sequence 8 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log archived log for thread 1 with sequence 9 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log thread=1 sequence=8 archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log thread=1 sequence=9 media recovery complete, elapsed time: 00:00:01 Finished recover at 06-JUN-14Finally open the database with reset logs
RMAN> alter database open resetlogs;At this stage the database is open and could be used as long as the segments that were in the excluded tablespace are not needed. All table data that were part of the incbackup tablespace could be access without any error.
SQL> select a from y;
A
----------
1
2
3
4
5Any attempt to access the blob segment which was in the excluded tablespace results in an errorSQL> select b from y; ERROR: ORA-00376: file 5 cannot be read at this time ORA-01110: data file 5: '/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'It is not possible to delete or truncate the table either
SQL> delete from y;
delete from y
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'
SQL> truncate table y;
truncate table y
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'
During this time the table segment information is listed as below.SQL> select segment_name,tablespace_name,bytes from user_segments; SEGMENT_NAME TABLESPACE_NAME BYTES -------------------------- ------------------------------ ---------- Y INCBACKUP 65536 SYS_IL0000105365C00002$$ EXCBACKUP 65536 LOBSEG EXCBACKUPDropping the table doesn't remove the lob segments either (it could be that these don't occupy any space but entry is there under a different name)
SQL> drop table y purge; SQL> select segment_name,tablespace_name,bytes from user_segments; SEGMENT_NAME TABLESPACE_NAME BYTES -------------- ------------------------------ ---------- 5.130 EXCBACKUP 5.138 EXCBACKUP 65536It is also possible to drop the missing data file offline and add another data file to the tablespace and recreate the table.
SQL> alter database datafile '/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf' offline drop;
SQL> alter tablespace excbackup add DATAFILE size 10m;
SQL> create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE STORAGE IN ROW);
begin
for i in 1 .. 100
loop
insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
end loop;
end;
/But still the segment information from the earlier table remains.SQL> select segment_name,tablespace_name,bytes from user_segments; SEGMENT_NAME TABLESPACE_NAME BYTES -------------------------- ------------------------------ ---------- Y INCBACKUP 65536 SYS_IL0000105337C00002$$ EXCBACKUP 65536 LOBSEG EXCBACKUP 983040 5.130 EXCBACKUP 5.138 EXCBACKUPOnly way to get rid of these phantom segment information is to drop the tablespace and recreate it again.
Therefore to have a clean restore and recovery in this type of scenario best course of actions is, once the database is open, drop the table, drop the tablespace and then recreate the tablespace and the table as before.
