ORA-01545: rollback segment 'string' specified not available
01545, 00000, "rollback segment '%s' specified not available"
// *Cause: Either:
// 1) An attempt was made to bring a rollback segment online that is
// unavailable during startup; for example, the rollback segment
// is in an offline tablespace.
// 2) An attempt was made to bring a rollback segment online that is
// already online. This is because the rollback segment is
// specified twice in the ROLLBACK_SEGMENTS parameter in the
// initialization parameter file or the rollback segment is already
// online by another instance.
// 3) An attempt was made to drop a rollback segment that is
// currently online.
// 4) An attempt was made to alter a rollback segment that is
// currently online to use unlimited extents.
// 5) An attempt was made to online a rollback segment that is
// corrupted. This is because the rollback is specified in
// _corrupted_rollback_segments parameter in initialization
// parameter file.
// *Action: Either:
// 1) Make the rollback segment available; for example, bring an
// offline tablespace online.
// 2) Remove the name from the ROLLBACK_SEGMENTS parameter if the name
// is a duplicate or if another instance has already acquired the
// rollback segment.
// 3) Bring the rollback segment offline first. This may involve
// waiting for the active transactions to finish, or, if the
// rollback segment needs recovery, discover which errors are
// holding up the rolling back of the transactions and take
// appropriate actions.
// 4) Same as 3).
// 5) Remove the name from the _corrupted_rollback_segments parameter.
발생 OS: SunOS 5.7
Oracle db version: 8.2.7.4
1. 복구 후 DB startup 시 에러 나타남
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> startup
ORACLE instance started.
Total System Global Area 919359164 bytes
Fixed Size 102076 bytes
Variable Size 487653376 bytes
Database Buffers 429490176 bytes
Redo Buffers 2113536 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
얼럿로그: ORA-01545: rollback segment 'RBS1' specified not available
2. init파일에서 rollback_segments 주석처리 후 open
SQL> startup
ORACLE instance started.
Total System Global Area 919359164 bytes
Fixed Size 102076 bytes
Variable Size 487653376 bytes
Database Buffers 429490176 bytes
Redo Buffers 2113536 bytes
Database mounted.
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/rbs01.dbf'
3. v$recover_file 확인해서 파일별 복구 수행후 online
SQL> select * from v$recover_file;
SQL> recover datafile 8;
ORA-00279: change 377347436 generated at 03/06/2013 22:10:36 needed for thread 1
ORA-00289: suggestion : /oradata/archive/arch_1_37864.arc
ORA-00280: change 377347436 for thread 1 is in sequence #37864
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
SQL> alter database datafile 8 online;
Database altered.
4. rbs파일인 (여기선 3번) 3번파일 offline + recover + online 수행
SQL> alter database datafile 3 offline;
Database altered.
SQL> recover datafile 3;
Media recovery complete.
SQL> alter database datafile 3 online;
Database altered.
5. db shutdown 후 init파일 rollback_segments 주석처리 제거하여 startup 후 정상 확인
※해당 상황은 필요 아카이브 로그가 전부 있는 상황이다.
