DBA took one of rollback segment offline, corressponding to data file 14: '/d101/oracle/ORVIT6PA/rbsbig01_ORVIT6PA.dbf' , and one of the ongoing transaction failed with following error.
Oct 1 15:02:48: SQLState: 60000; Message: ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/d101/oracle/ORVIT6PA/rbsbig01_ORVIT6PA.dbf'
Oracle claims that if rollback segments has any active transactions at the time when the command to take it offline is issued, then it would wait till all transactions are finished, and then only take the segment offline. Obviously, looking at the error that hasn't happened, why would it be so ? Is it possible that the segment didn't have any active transaction but had data needed for read consistency ? Considering that's true, then the retry of transaction should succeed.
Application is coded to retry the transaction, in that case, it should have used different rollback segment which was online that time. But the retry also failed with the exact same error, which doesn't make any sense to me. Had it been a data file segment which holds committed data, then it's understood, not for rollback segment ?? Any ideas ? Three retries for the whole transaction failed, and application shut down finally. DBA had to take that segment online again for application to continue.
Click Here to Expand Forum to Full Width