복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
에러 : ORA-19502: write error on file "/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG", block number 2566145 (block size=512)
asm to fs로 clone db 생성 시 파일시스템 공간 부족으로 인해 redo가 제대로 생성되지 못하고 에러가 발생한 문제
현재 redo 경로 확인
|
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
30
31
32
|
SQL>
set lines 200 pages 1000
col member for a60
select l.group#, member, l.status, bytes/1024/1024 mb, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;
GROUP# MEMBER STATUS MB SEQUENCE#
---------- ------------------------------------------------------------ ---------------- ---------- ----------
1 +DATA/ORA19DBFS/ONLINELOG/group_1.287.1223736640 INACTIVE 2048 1136
1 +DATA/ORA19DBFS/ONLINELOG/group_1.312.1223736659 INACTIVE 2048 1136
2 +DATA/ORA19DBFS/ONLINELOG/group_2.259.1220526317 INACTIVE 2048 1137
2 +DATA/ORA19DBFS/ONLINELOG/group_2.313.1223736715 INACTIVE 2048 1137
3 +DATA/ORA19DBFS/ONLINELOG/group_3.260.1220526321 ACTIVE 2048 1138
3 +DATA/ORA19DBFS/ONLINELOG/group_3.314.1223736719 ACTIVE 2048 1138
4 +DATA/ORA19DBFS/ONLINELOG/group_4.261.1220526325 CURRENT 2048 1139
4 +DATA/ORA19DBFS/ONLINELOG/group_4.315.1223736721 CURRENT 2048 1139
5 +DATA/ORA19DBFS/ONLINELOG/group_5.262.1220526327 INACTIVE 2048 1135
5 +DATA/ORA19DBFS/ONLINELOG/group_5.316.1223736723 INACTIVE 2048 1135
6 +DATA/ORA19DBFS/ONLINELOG/group_6.270.1220527063 ACTIVE 2048 1016
6 +DATA/ORA19DBFS/ONLINELOG/group_6.317.1223736725 ACTIVE 2048 1016
7 +DATA/ORA19DBFS/ONLINELOG/group_7.271.1220527067 CURRENT 2048 1017
7 +DATA/ORA19DBFS/ONLINELOG/group_7.318.1223736729 CURRENT 2048 1017
8 +DATA/ORA19DBFS/ONLINELOG/group_8.272.1220527069 INACTIVE 2048 1013
8 +DATA/ORA19DBFS/ONLINELOG/group_8.319.1223736731 INACTIVE 2048 1013
9 +DATA/ORA19DBFS/ONLINELOG/group_9.273.1220527073 INACTIVE 2048 1014
9 +DATA/ORA19DBFS/ONLINELOG/group_9.320.1223736733 INACTIVE 2048 1014
10 +DATA/ORA19DBFS/ONLINELOG/group_10.274.1220527077 INACTIVE 2048 1015
10 +DATA/ORA19DBFS/ONLINELOG/group_10.321.1223736735 INACTIVE 2048 1015
20 rows selected.
|
redo rename 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_1.287.1223736640' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_1.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_2.259.1220526317' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_2.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_3.260.1220526321' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_3.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_4.261.1220526325' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_4.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_5.262.1220526327' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_5.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_6.270.1220527063' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_6.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_7.271.1220527067' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_7.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_8.272.1220527069' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_8.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_9.273.1220527073' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_9.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_10.274.1220527077' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G1_10.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_1.312.1223736659' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_1.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_2.313.1223736715' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_2.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_3.314.1223736719' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_3.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_4.315.1223736721' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_4.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_5.316.1223736723' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_5.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_6.317.1223736725' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_6.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_7.318.1223736729' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_7.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_8.319.1223736731' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_8.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_9.320.1223736733' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG';
alter database rename file '+DATA/ORA19DBFS/ONLINELOG/group_10.321.1223736735' to '/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_10.LOG';
Database altered.
|
redo 경로 재확인
|
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
30
31
32
|
SQL>
set lines 200 pages 1000
col member for a60
select l.group#, member, l.status, bytes/1024/1024 mb, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;
GROUP# MEMBER STATUS MB SEQUENCE#
---------- ------------------------------------------------------------ ---------------- ---------- ----------
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_1.LOG INACTIVE 2048 1136
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_1.LOG INACTIVE 2048 1136
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_2.LOG INACTIVE 2048 1137
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_2.LOG INACTIVE 2048 1137
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_3.LOG ACTIVE 2048 1138
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_3.LOG ACTIVE 2048 1138
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_4.LOG CURRENT 2048 1139
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_4.LOG CURRENT 2048 1139
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_5.LOG INACTIVE 2048 1135
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_5.LOG INACTIVE 2048 1135
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_6.LOG ACTIVE 2048 1016
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_6.LOG ACTIVE 2048 1016
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_7.LOG CURRENT 2048 1017
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_7.LOG CURRENT 2048 1017
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_8.LOG INACTIVE 2048 1013
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_8.LOG INACTIVE 2048 1013
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_9.LOG INACTIVE 2048 1014
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG INACTIVE 2048 1014
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_10.LOG INACTIVE 2048 1015
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_10.LOG INACTIVE 2048 1015
20 rows selected.
|
현재는 redo 경로가 컨틑롤파일 상에서만 변경되었고
실제 OS에는 존재하지 않음
db를 resetlogs로 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19502: write error on file "/app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG", block number 2566145 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 2566145
Additional information: 3584
|
조금 대기후 에러 발생함
에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 7.0G 0 7.0G 0% /dev/shm
tmpfs 3.9G 59M 3.9G 2% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/ol-root 80G 62G 19G 78% /
/dev/sda1 100G 100G 0G 100% /app
/dev/sdb1 1014M 310M 705M 31% /boot
tmpfs 797M 12K 797M 1% /run/user/42
tmpfs 797M 0 797M 0% /run/user/54321
tmpfs 797M 0 797M 0% /run/user/0
|
현재 redo 저장 경로인 /app 파일시스템이 100% full임
redo 재확인
|
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
30
31
32
|
SQL>
set lines 200 pages 1000
col member for a60
select l.group#, member, l.status, bytes/1024/1024 mb, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;
GROUP# MEMBER STATUS MB SEQUENCE#
---------- ------------------------------------------------------------ ---------------- ---------- ----------
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_1.LOG UNUSED 2048 0
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_1.LOG UNUSED 2048 0
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_2.LOG UNUSED 2048 0
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_2.LOG UNUSED 2048 0
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_3.LOG UNUSED 2048 0
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_3.LOG UNUSED 2048 0
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_4.LOG CURRENT 2048 0
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_4.LOG CURRENT 2048 0
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_5.LOG UNUSED 2048 0
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_5.LOG UNUSED 2048 0
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_6.LOG UNUSED 2048 0
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_6.LOG UNUSED 2048 0
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_7.LOG CURRENT 2048 0
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_7.LOG CURRENT 2048 0
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_8.LOG UNUSED 2048 0
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_8.LOG UNUSED 2048 0
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_9.LOG CLEARING 2048 0
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG CLEARING 2048 0
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_10.LOG CLEARING 2048 0
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_10.LOG CLEARING 2048 0
20 rows selected.
|
1~8번까지 redo는 cleaning이 완료되어 unused 상태가 되었지만 9번, 10번 /app 경로의 용량 부족으로 redo는 cleaning이 완료되지 못함
실제 파일도 1~8번 redo만 생성됨
이상태에서 다시 db를 open 상태로 변경하려 하면 에러가 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
--옵션 없이 open 시도시
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--noresetlogs로 open 시도시
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
--resetlogs로 open 시도시
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper], [0x080010003], [], [], [], [], [], [], [], [], [], []
|
해결 방법 : os 공간 확보 후 redo 수동 cleaning 후 open 재수행
os 공간 확보 후 redo 수동 cleaning 후 open 재수행
일단 db 종료
|
1
2
3
4
|
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
|
os 경로 공간 확보
(미사용 파일 삭제 등)
mount 상태로 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 8950920 bytes
Variable Size 1577058304 bytes
Database Buffers 9126805504 bytes
Redo Buffers 24600576 bytes
Database mounted.
|
9, 10번 redo 수동 cleaning 수행
|
1
2
3
4
5
6
7
|
SQL> alter database clear logfile group 9;
Database altered.
SQL> alter database clear logfile group 10;
Database altered.
|
redo 재확인
|
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
30
31
32
|
SQL>
set lines 200 pages 1000
col member for a60
select l.group#, member, l.status, bytes/1024/1024 mb, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;
GROUP# MEMBER STATUS MB SEQUENCE#
---------- ------------------------------------------------------------ ---------------- ---------- ----------
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_1.LOG UNUSED 2048 0
1 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_1.LOG UNUSED 2048 0
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_2.LOG UNUSED 2048 0
2 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_2.LOG UNUSED 2048 0
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_3.LOG UNUSED 2048 0
3 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_3.LOG UNUSED 2048 0
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_4.LOG CURRENT 2048 1139
4 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_4.LOG CURRENT 2048 1139
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_5.LOG UNUSED 2048 0
5 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_5.LOG UNUSED 2048 0
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_6.LOG UNUSED 2048 0
6 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_6.LOG UNUSED 2048 0
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_7.LOG CURRENT 2048 1017
7 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_7.LOG CURRENT 2048 1017
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_8.LOG UNUSED 2048 0
8 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_8.LOG UNUSED 2048 0
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_9.LOG UNUSED 2048 0
9 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_9.LOG UNUSED 2048 0
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G1_10.LOG UNUSED 2048 0
10 /app/oracle/oradata/ORA19DBFS_BK/REDO_G2_10.LOG UNUSED 2048 0
20 rows selected.
|
정상적으로 unused 상태가 됨
db resetlogs로 기동
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
정상 기동됨
원인 : os 파일시스템 공간 부족으로 인해 open resertlogs 명령 수행시 redo cleaning에 문제가 발생함
os 파일시스템 공간 부족으로 인해 open resertlogs 명령 수행시
일부 redo만 cleaning되고 일부 redo는 되지 않아 발생한 문제
참조 :
오라클 12c R2 RAC to RAC ADG redo log 삭제 및 추가 및 리사이즈 하기 ( https://positivemh.tistory.com/832 )