Hi,
This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again.
After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change.
Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup.
1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices?
2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem.
3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take?
This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again.
After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change.
Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup.
1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices?
2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem.
3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take?
4. If the
database were shutdown during the rollback I assume the rollback would
recommence when Oracle came back up. Would it start where it left off or
start from scratch. It was my impression that it is marking the header
blocks as it goes, but I would like to check.
Thanks,
Russ Brooks
Thanks,
Russ Brooks
