[DB] ORA-01157, ORA-01110 장애 해결 방안

출처

https://adbancedteam.tistory.com/148

[Admin] ORA-01157, ORA-01110 장애 해결 방안

 

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'

 

2. 문제 해결

-- 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.

 

 

 

-- ORA-01548 에러로 테이블스페이스가 drop되지 않을 경우

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]