OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : ORA-01552 + dba_rollback_segs NEEDS RECOVERY 해결
emp 테이블에 데이터를 insert 하다가 오류를 만남.
1 2 3 4 5 6 | SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20); * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS2' |
(문서 ID 1919142.1)
ORA-1552: cannot use system rollback segment for non-system tablespace '%s'
에러 원인: non-시스템 테이블스페이스를 포함하는 업무작업에 시스템 롤백
세그먼트를 사용하려고 시도한것 입니다. 만약 해당 시스템이 클론
데이터베이스일 경우 시스템 테이블스페이스 밖에 임의의 데어터의
수정을 시도했을때 일어납니다. 오직 시스템 롤백 세그먼트만이 클론
데이터베이스에서 온라인이 가능합니다.
액션: 하나 혹은 여러개의 전용/공용 세그먼트를 만들어준후 데이터 베이스를
재시작합니다. 프라이베이트 롤백 세그먼트를 얻기 위하여 아마 INIT.ORA 파라메터
rollback_segments의 수정이 필요할것입니다. 만약 TSPITR 를 사용한 클론
데이터베이스일 경우 해당 동작은 허용되지 않습니다.
undo tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU1_1968273193$ UNDOTBS1 NEEDS RECOVERY _SYSSMU2_448866274$ UNDOTBS1 OFFLINE _SYSSMU3_3368905956$ UNDOTBS1 OFFLINE _SYSSMU4_674679975$ UNDOTBS1 OFFLINE _SYSSMU5_2911911356$ UNDOTBS1 OFFLINE _SYSSMU6_352842569$ UNDOTBS1 OFFLINE _SYSSMU7_4088294245$ UNDOTBS1 OFFLINE _SYSSMU8_2474966820$ UNDOTBS1 OFFLINE _SYSSMU9_702768780$ UNDOTBS1 OFFLINE _SYSSMU10_366725892$ UNDOTBS1 OFFLINE 11 rows selected. |
_SYSSMU1_1968273193$ 가 status가 NEED RECOVERY 상태이고 나머지 segment들이 offline 상태인것을 확인
online 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> alter tablespace undotbs1 online; Tablespace altered. SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU1_1968273193$ UNDOTBS1 NEEDS RECOVERY _SYSSMU2_448866274$ UNDOTBS1 OFFLINE _SYSSMU3_3368905956$ UNDOTBS1 OFFLINE _SYSSMU4_674679975$ UNDOTBS1 OFFLINE _SYSSMU5_2911911356$ UNDOTBS1 OFFLINE _SYSSMU6_352842569$ UNDOTBS1 OFFLINE _SYSSMU7_4088294245$ UNDOTBS1 OFFLINE _SYSSMU8_2474966820$ UNDOTBS1 OFFLINE _SYSSMU9_702768780$ UNDOTBS1 OFFLINE _SYSSMU10_366725892$ UNDOTBS1 OFFLINE 21 rows selected. |
변경되지 않음
UNDOTBS2 생성
1 2 3 4 | SQL> create undo tablespace UNDOTBS2 datafile '/oracle/app/oracle/oradata/orcl/undotbs02.dbf' size 200m autoextend on; Tablespace created. |
UNDOTBS2 를 기본 undo tablespace로 지정
1 2 3 | SQL> alter system set undo_tablespace=UNDOTBS2 SCOPE=BOTH; System altered. |
새로 만든 undo tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU1_1968273193$ UNDOTBS1 NEEDS RECOVERY _SYSSMU2_448866274$ UNDOTBS1 OFFLINE _SYSSMU3_3368905956$ UNDOTBS1 OFFLINE _SYSSMU4_674679975$ UNDOTBS1 OFFLINE _SYSSMU5_2911911356$ UNDOTBS1 OFFLINE _SYSSMU6_352842569$ UNDOTBS1 OFFLINE _SYSSMU7_4088294245$ UNDOTBS1 OFFLINE _SYSSMU8_2474966820$ UNDOTBS1 OFFLINE _SYSSMU9_702768780$ UNDOTBS1 OFFLINE _SYSSMU10_366725892$ UNDOTBS1 OFFLINE _SYSSMU11_2537689203$ UNDOTBS2 OFFLINE _SYSSMU12_807680646$ UNDOTBS2 OFFLINE _SYSSMU13_2004296324$ UNDOTBS2 OFFLINE _SYSSMU14_1117439626$ UNDOTBS2 OFFLINE _SYSSMU15_3621255694$ UNDOTBS2 OFFLINE _SYSSMU16_4000800200$ UNDOTBS2 OFFLINE _SYSSMU17_2636616834$ UNDOTBS2 OFFLINE _SYSSMU18_1690515686$ UNDOTBS2 OFFLINE _SYSSMU19_1066767748$ UNDOTBS2 OFFLINE _SYSSMU20_2369856428$ UNDOTBS2 OFFLINE |
online 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SQL> alter tablespace undotbs2 online; Tablespace altered. SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU1_1968273193$ UNDOTBS1 NEEDS RECOVERY _SYSSMU2_448866274$ UNDOTBS1 OFFLINE _SYSSMU3_3368905956$ UNDOTBS1 OFFLINE _SYSSMU4_674679975$ UNDOTBS1 OFFLINE _SYSSMU5_2911911356$ UNDOTBS1 OFFLINE _SYSSMU6_352842569$ UNDOTBS1 OFFLINE _SYSSMU7_4088294245$ UNDOTBS1 OFFLINE _SYSSMU8_2474966820$ UNDOTBS1 OFFLINE _SYSSMU9_702768780$ UNDOTBS1 OFFLINE _SYSSMU10_366725892$ UNDOTBS1 OFFLINE _SYSSMU11_2537689203$ UNDOTBS2 OFFLINE _SYSSMU12_807680646$ UNDOTBS2 OFFLINE _SYSSMU13_2004296324$ UNDOTBS2 OFFLINE _SYSSMU14_1117439626$ UNDOTBS2 OFFLINE _SYSSMU15_3621255694$ UNDOTBS2 OFFLINE _SYSSMU16_4000800200$ UNDOTBS2 OFFLINE _SYSSMU17_2636616834$ UNDOTBS2 OFFLINE _SYSSMU18_1690515686$ UNDOTBS2 OFFLINE _SYSSMU19_1066767748$ UNDOTBS2 OFFLINE _SYSSMU20_2369856428$ UNDOTBS2 OFFLINE |
변경되지 않음
해결 방법 : 히든 파라미터 이용 후 작업 진행
1. spfile 로 pfile 생성
1 2 3 | SQL> create pfile from spfile; File created. |
2. pfile 열어서 수정
1 2 3 4 5 6 | $ cd $ORACLE_HOME/dbs $ vi initorcl.ora 제일 밑에 아래 문장 입력 *._corrupted_rollback_segments='_SYSSMU1_1968273193$' <- 위에 NEEDS RECOVERY 로 표시된 것들만 작성여러개면 ,로 구분 *._offline_rollback_segments='_SYSSMU1_1968273193$' <- 위에 NEEDS RECOVERY 로 표시된 것들만 작성여러개면 ,로 구분 #*.undo_management=AUTO <- undo_management 파라미터 AUTO로 되어있는걸 주석처리 |
3. pfile로 spfile 생성
1 2 3 | SQL> create spfile from pfile; File created. |
4. restrict mode로 startup
1 2 3 4 5 6 7 8 9 10 | SQL> startup restrict; ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2257880 bytes Variable Size 616565800 bytes Database Buffers 218103808 bytes Redo Buffers 2355200 bytes Database mounted. Database opened. |
5. 위에 NEEDS RECOVERY 로 표시된 것들만 삭제
1 2 3 4 5 6 7 | SQL> drop rollback segment "_SYSSMU1_1968273193$"; <- 여러개면 drop rollback segment 문장 여러번 입력 drop rollback segment "_SYSSMU1_1968273193$" * ERROR at line 1: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [128], [38508], [], [], [], [], [], [], [], [] |
잘 삭제 될 수도 있고
위와 같이 에러가 발생 할 수 도 있는데 실제 확인해보면 삭제되어 있음
6. undo tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU2_448866274$ UNDOTBS1 OFFLINE _SYSSMU3_3368905956$ UNDOTBS1 OFFLINE _SYSSMU4_674679975$ UNDOTBS1 OFFLINE _SYSSMU5_2911911356$ UNDOTBS1 OFFLINE _SYSSMU6_352842569$ UNDOTBS1 OFFLINE _SYSSMU7_4088294245$ UNDOTBS1 OFFLINE _SYSSMU8_2474966820$ UNDOTBS1 OFFLINE _SYSSMU9_702768780$ UNDOTBS1 OFFLINE _SYSSMU10_366725892$ UNDOTBS1 OFFLINE _SYSSMU11_2537689203$ UNDOTBS2 OFFLINE _SYSSMU12_807680646$ UNDOTBS2 OFFLINE _SYSSMU13_2004296324$ UNDOTBS2 OFFLINE _SYSSMU14_1117439626$ UNDOTBS2 OFFLINE _SYSSMU15_3621255694$ UNDOTBS2 OFFLINE _SYSSMU16_4000800200$ UNDOTBS2 OFFLINE _SYSSMU17_2636616834$ UNDOTBS2 OFFLINE _SYSSMU18_1690515686$ UNDOTBS2 OFFLINE _SYSSMU19_1066767748$ UNDOTBS2 OFFLINE _SYSSMU20_2369856428$ UNDOTBS2 OFFLINE |
NEEDS RECOVERY로 되어있던 segment 삭제된것을 확인
7. 1번 undo tablespace(undotbs1) 삭제
1 2 3 | SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. |
8. DB 종료
1 2 3 4 | SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. |
9. pfile에 작성했던 내용 원복(히든파라미터 삭제 및 undo_menagement=AUTO 주석 해제)
1 2 3 4 5 | $ cd $ORACLE_HOME/dbs $ vi initorcl.ora *._corrupted_rollback_segments='_SYSSMU1_1968273193$' <- 삭제 *._offline_rollback_segments='_SYSSMU1_1968273193$' <- 삭제 *.undo_management=AUTO <- 주석 해제 |
10. pfile로 spfile 생성
1 2 3 | SQL> create spfile from pfile; File created. |
11. DB 정상 기동 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SQL> startup Total System Global Area 839282688 bytes Fixed Size 2257880 bytes Variable Size 616565800 bytes Database Buffers 218103808 bytes Redo Buffers 2355200 bytes Database mounted. Database opened. SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU2_3591705079$ UNDOTBS2 ONLINE _SYSSMU3_1804564213$ UNDOTBS2 ONLINE _SYSSMU4_1390701974$ UNDOTBS2 ONLINE _SYSSMU5_2619049222$ UNDOTBS2 ONLINE _SYSSMU6_4126997615$ UNDOTBS2 ONLINE _SYSSMU7_895185006$ UNDOTBS2 ONLINE _SYSSMU8_944358489$ UNDOTBS2 ONLINE _SYSSMU9_582459079$ UNDOTBS2 ONLINE _SYSSMU10_1186970945$ UNDOTBS2 ONLINE _SYSSMU21_1970430449$ UNDOTBS2 ONLINE 11 rows selected. |
정상적으로 ONLINE 상태인것을 확인
제일 처음으로 돌아가서 test 계정에 emp 테이블에 데이터 insert 시도
1 2 3 4 5 6 7 | SQL> conn test/test SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20); 1 row created. |
정상적으로 insert 가 되는것을 확인가능함
원인 : 특정 undo segment 문제
특정 undo segment 문제
참조 : Doc. 1919142.1
http://alivekim.tistory.com/2
'ORACLE > Trouble Shooting' 카테고리의 다른 글
| crsctl stat res -t 명령어로 조회 시 ora.diskmon OFFLINE (0) | 2018.03.09 |
|---|---|
| CRS-4013: This command is not supported in a single-node configuration. (10) | 2018.03.09 |
| ORA-12919: Can not drop the default permanent tablespace 해결 방법 (0) | 2018.03.07 |
| 오라클 11g R2 설치 시 INVALID BLOCK TYPE 에러 (0) | 2018.02.28 |
| ORA-39070: Unable to open the log file. (0) | 2018.02.28 |