728x90
반응형
출처
https://adbancedteam.tistory.com/148
1. 문제 발생
- DB 오픈 시 UNDO 데이터 파일 에러 발생
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 314575768 bytes
Database Buffers 100663296 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/app/oracle/oradata/testdb/undotbs01.dbf'
-- UNDO 관리 방법 변경
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT = 'MANUAL' SCOPE=SPFILE;
System altered.
-- DB 재시작
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 314575768 bytes
Database Buffers 100663296 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/app/oracle/oradata/testdb/undotbs01.dbf'
-- 에러 발생한 UNDO 데이터파일 삭제
SQL> alter database datafile '/app/oracle/oradata/testdb/undotbs01.dbf' offline drop;
Database altered.
-- DATABASE OPEN
SQL> alter database open;
Database altered.
-- UNDO 테이블스페이스 삭제 -> 이 단계에서 ORA-01548가 발생할 수 있음. 해결 방안은 아래 참고
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
-- UNDO 테이블스페이스 생성
SQL> create undo tablespace undotbs2 datafile '/app/oracle/oradata/testdb/undotbs02.dbf' size 25M autoextend on;
-- UNDO 테이블스페이스 변경
SQL> alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;
-- UNDO 관리 방법 변경
SQL> alter system set undo_management = 'AUTO' scope=spfile;
-- DB 재시작
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 314575768 bytes
Database Buffers 100663296 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_2081786551$' found, terminate
dropping tablespace
-- 실제 존재하지 않는 세그먼트이기 때문에 drop이 안된다.
SQL> drop rollback segment '_SYSSMU1_20817865551$';
drop rollback segment '_SYSSMU1_20817865551$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name
-- pfile 생성
SQL> create pfile from spfile;
File created.
-- pfile에 해당 롤백 세그먼트를 오프라인 시키는 히든 파라미터를 적음
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS1'
*._offline_rollback_segments=_SYSSMU1_2081786551$
-- DB 재시작
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 314575768 bytes
Database Buffers 100663296 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
-- UNDO 테이블스페이스 삭제
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
-- spfile로 변경
SQL> create spfile from pfile;
-- DB 재시작
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 314575768 bytes
Database Buffers 100663296 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
-- 위의 해결 방안에서 UNDO 테이블스페이스 생성부터 진행
출처: https://adbancedteam.tistory.com/148 [aDBanced Team]
728x90
반응형