OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
에러 : oratop 실행시 ORA-01476: divisor is equal to zero
oratop 실행시 발생하는 ORA-01476: divisor is equal to zero 에러
oratop 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ cd $ORACLE_HOME/suptools/oratop
$ ./oratop / as sysdba
oratop: Release 16.5.1 Production on Sat Jun 21 14:27:11 KST 2025
Copyright (c) 2011, Oracle. All rights reserved.
Connecting
ORA-01476: divisor is equal to zero
Error - database not open.
|
ORA-01476 에러와 함께 db가 open되어 있지않다고 나옴
현재 db는 open 상태임
|
1
2
3
4
5
|
SQL> select status from v$instance;
STATUS
------------
OPEN
|
해결 방법 : db_recovery_file_dest_size를 0이 아닌값으로 설정
db_recovery_file_dest_size를 0이 아닌값으로 설정해주면 해결됨
|
1
2
3
|
SQL> alter system set db_recovery_file_dest_size = 1G scope=both;
System altered.
|
oratop 실행 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ ./oratop / as sysdba
Oracle 19c - 14:43:38 Pri r/w ORA1 up: 1h, 4 sn, 2G sga, 0%fra, 0%db
ID %CPU LOAD AAS ASC ASI ASW ISW IORT MBPS %FRE PGA UCPS SQRT %DBC %DBW
1 14 1 0 0 0 0 2 137u 0 12 871M 0 0 0 0
2 12 0 0 0 0 0 2 308u 0 8 745M 0 228u 0 0
EVENT (C) T/O WAIT TIME AVG %DBT WAIT_CLASS
DB CPU 8t 53
db file sequential read 17k 54s 3m 6 User I/O
row cache lock 93k 93k 40s 1m 4 Concurrency
external table read 4 37s 10s 4 User I/O
control file sequential read 35k 29s 800u 3 System I/O
ID SID SPID USR PROG S OPN SQLID/BLOCKER E/T %CPU PGA ACT EVENT/OB W/T
1 54 462764 SYS pyth D 9s 0 4M INA SQL*Net 9s
2 182 464341 SYS pyth D 5s 0 4M INA SQL*Net 5s
2 175 464340 SYS pyth D 5s 0 4M INA SQL*Net 5s
1 181 463186 SYS pyth D 4s 0 4M INA SQL*Net 4s
|
정상적으로 동작함
원인 : oratop에서 내부적으로 실행되는 SQL에서 FRA 관련 계산식 문제
oratop에서 내부적으로 실행되는 SQL에서 FRA 관련 계산식으로 인한 문제
에러스택 확인을 위해 trace 수행
|
1
2
3
|
SQL> alter system set events '1476 trace name errorstack forever, level 3';
System altered.
|
oratop 재실행
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ ./oratop / as sysdba
oratop: Release 16.5.1 Production on Sat Jun 21 14:27:11 KST 2025
Copyright (c) 2011, Oracle. All rights reserved.
Connecting
ORA-01476: divisor is equal to zero
Error - database not open.
|
trace 종료
|
1
2
3
|
SQL> alter system set events '1476 off';
System altered.
|
diag 경로 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a10
col value for a70
select name, value from v$diag_info
where name = 'Diag Trace';
NAME VALUE
---------- ----------------------------------------------------------------------
Diag Trace /oracle/app/oracle/diag/rdbms/ora11db/ORA11DB1/trace
|
경로 가서 트레이스 파일 확인
|
1
2
3
4
5
6
7
8
|
$ cd /oracle/app/oracle/diag/rdbms/ora11db/ORA11DB1/trace/
$ ls -ltr
...
-rw-r----- 1 oracle dba 293137 Jun 21 14:24 alert_ORA11DB1.log
-rw-r----- 1 oracle dba 940 Jun 21 14:24 ORA11DB1_vkrm_462063.trm
-rw-r----- 1 oracle dba 1663 Jun 21 14:24 ORA11DB1_vkrm_462063.trc
-rw-r----- 1 oracle dba 262928 Jun 21 14:24 ORA11DB1_ora_486727.trm
-rw-r----- 1 oracle dba 6147504 Jun 21 14:24 ORA11DB1_ora_486727.trc <<--
|
트레이스 확인
|
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
$ vi ORA11DB1_ora_486727.trc
Trace file /oracle/app/oracle/diag/rdbms/ora11db/ORA11DB1/trace/ORA11DB1_ora_486727.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
Build label: RDBMS_19.27.0.0.0DBRU_LINUX.X64_250331
ORACLE_HOME: /oracle/app/oracle/product/19c
System name: Linux
Node name: ora19rac1
Release: 5.4.17-2136.342.5.3.el8uek.x86_64
Version: #3 SMP Mon Apr 21 10:12:09 PDT 2025
Machine: x86_64
CLID: P
Instance name: ORA11DB1
Instance number: 1
Database name: ORA11DB
Database unique name: ORA11DB
Database id: 357213915
Database role: PRIMARY
Redo thread mounted by this instance: 1
Oracle process number: 94
Unix process pid: 486727, image: oracle@ora19rac1 (TNS V1-V3)
*** 2025-06-21T14:24:08.485498+09:00
*** SESSION ID:(60.40024) 2025-06-21T14:24:08.485591+09:00
*** CLIENT ID:() 2025-06-21T14:24:08.485601+09:00
*** SERVICE NAME:(SYS$USERS) 2025-06-21T14:24:08.485611+09:00
*** MODULE NAME:(oratop@ora19rac1 (TNS V1-V3)) 2025-06-21T14:24:08.485620+09:00
*** ACTION NAME:() 2025-06-21T14:24:08.485630+09:00
*** CLIENT DRIVER:() 2025-06-21T14:24:08.485637+09:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffc49b9bff0 placed dbkda.c@298
ORA-01476: divisor is equal to zero
----- Current SQL Statement for this session (sql_id=anzxa6f04pr1m) -----
/* oratop s0a*/ SELECT * FROM (SELECT db_unique_name, INITCAP(database_role), LOWER(log_mode),
CASE OPEN_MODE WHEN 'READ WRITE' THEN 'r/w' WHEN 'MOUNTED' THEN 'mnt' WHEN 'READ ONLY' THEN 'r/o'
WHEN 'READ ONLY WITH APPLY' THEN 'r/a' ELSE 'n/a' END from V$DATABASE ), (select SUBSTR(UPPER(VALUE),1,5) typd
from v$system_parameter where NAME='instance_type' ), (select DECODE(VALUE,'BASIC',1,0) stlv from v$system_parameter
where NAME='statistics_level' ), (SELECT SUM(VALUE) prob from GV$DIAG_INFO where NAME='Active Problem Count' ),
(select count(*) dasm from v$asm_diskgroup ), (SELECT SUBSTR(VERSION,1,2), INSTANCE_NAME, DECODE(SUBSTR(STATUS,1,4),'OPEN',1,0),
CASE WHEN SUBSTR(VERSION,1,2) > 11 THEN (SELECT to_number(sys_context('USERENV', 'CON_ID')) from dual) ELSE 0 END ,
CASE WHEN SUBSTR(VERSION,1,2) > 11 THEN (SELECT SUBSTR(sys_context('USERENV', 'CON_NAME'),1,30) from dual) ELSE null END
FROM V$INSTANCE ), (SELECT count(*) FROM GV$INSTANCE where STATUS='STARTED' ), (SELECT ( SELECT SUM(bytes) FROM V$DATAFILE )
+( SELECT SUM(bytes) FROM GV$LOG ) +( SELECT NVL(SUM(bytes),0) FROM V$TEMPFILE ) +( SELECT SUM(block_size*file_size_blks)
FROM V$CONTROLFILE) dbsz FROM dual ), (SELECT SUM(fra) reco FROM (select (SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100 fra
FROM V$RECOVERY_FILE_DEST union select 0 fra from dual) )
----- Parser State -----
Parser state1: len1=4310 len2=4310 pos1=4309 pos2=4309
Parser state2: flg=0x0 xflg=0x4080000 xxflg=0x400
Parser state3: tty=0 tlen=3
Parser string: prx=0x7fa38f730d60 base=0x152411a0 cur=0x15242275
|
anzxa6f04pr1m sql이 보임
이 sql 수동으로 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
/* oratop s0a*/ SELECT * FROM (SELECT db_unique_name, INITCAP(database_role), LOWER(log_mode),
CASE OPEN_MODE WHEN 'READ WRITE' THEN 'r/w' WHEN 'MOUNTED' THEN 'mnt' WHEN 'READ ONLY' THEN 'r/o'
WHEN 'READ ONLY WITH APPLY' THEN 'r/a' ELSE 'n/a' END from V$DATABASE ), (select SUBSTR(UPPER(VALUE),1,5) typd
from v$system_parameter where NAME='instance_type' ), (select DECODE(VALUE,'BASIC',1,0) stlv from v$system_parameter
where NAME='statistics_level' ), (SELECT SUM(VALUE) prob from GV$DIAG_INFO where NAME='Active Problem Count' ),
(select count(*) dasm from v$asm_diskgroup ), (SELECT SUBSTR(VERSION,1,2), INSTANCE_NAME, DECODE(SUBSTR(STATUS,1,4),'OPEN',1,0),
CASE WHEN SUBSTR(VERSION,1,2) > 11 THEN (SELECT to_number(sys_context('USERENV', 'CON_ID')) from dual) ELSE 0 END ,
CASE WHEN SUBSTR(VERSION,1,2) > 11 THEN (SELECT SUBSTR(sys_context('USERENV', 'CON_NAME'),1,30) from dual) ELSE null END
FROM V$INSTANCE ), (SELECT count(*) FROM GV$INSTANCE where STATUS='STARTED' ), (SELECT ( SELECT SUM(bytes) FROM V$DATAFILE )
+( SELECT SUM(bytes) FROM GV$LOG ) +( SELECT NVL(SUM(bytes),0) FROM V$TEMPFILE ) +( SELECT SUM(block_size*file_size_blks)
FROM V$CONTROLFILE) dbsz FROM dual ), (SELECT SUM(fra) reco FROM (select (SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100 fra
FROM V$RECOVERY_FILE_DEST union select 0 fra from dual) );
FROM V$CONTROLFILE) dbsz FROM dual ), (SELECT SUM(fra) reco FROM (select (SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100 fra
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
|
V$RECOVERY_FILE_DEST 부분에서 에러가 발생함
해당 부분만 따로 빼서 수행
|
1
2
3
4
|
SQL> select (SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100 fra FROM V$RECOVERY_FILE_DEST;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
|
동일한 에러가 발생함
각각의 컬럼 값 확인
|
1
2
3
4
5
|
SQL> select space_used, space_reclaimable, space_limit fra from v$recovery_file_dest;
SPACE_USED SPACE_RECLAIMABLE FRA
---------- ----------------- ----------
0 0 0
|
fra를 설정하지 않았기때문에 모두 0임, 0을 0으로 나눌수 없어서 발생한 에러임
db_recovery_file_dest_size를 0이 아닌값으로 설정해주면 해결됨
|
1
2
3
|
SQL> alter system set db_recovery_file_dest_size = 1G scope=both;
System altered.
|
oratop 실행 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ ./oratop / as sysdba
Oracle 19c - 14:43:38 Pri r/w ORA1 up: 1h, 4 sn, 2G sga, 0%fra, 0%db
ID %CPU LOAD AAS ASC ASI ASW ISW IORT MBPS %FRE PGA UCPS SQRT %DBC %DBW
1 14 1 0 0 0 0 2 137u 0 12 871M 0 0 0 0
2 12 0 0 0 0 0 2 308u 0 8 745M 0 228u 0 0
EVENT (C) T/O WAIT TIME AVG %DBT WAIT_CLASS
DB CPU 8t 53
db file sequential read 17k 54s 3m 6 User I/O
row cache lock 93k 93k 40s 1m 4 Concurrency
external table read 4 37s 10s 4 User I/O
control file sequential read 35k 29s 800u 3 System I/O
ID SID SPID USR PROG S OPN SQLID/BLOCKER E/T %CPU PGA ACT EVENT/OB W/T
1 54 462764 SYS pyth D 9s 0 4M INA SQL*Net 9s
2 182 464341 SYS pyth D 5s 0 4M INA SQL*Net 5s
2 175 464340 SYS pyth D 5s 0 4M INA SQL*Net 5s
1 181 463186 SYS pyth D 4s 0 4M INA SQL*Net 4s
|
정상적으로 동작함
참고로 다시 fra 파라미터를 reset, reboot 한뒤에 oratop이 동작하는지 테스트해봤지만 동일한 에러가 발생함
|
1
2
3
4
5
|
SQL> alter system reset db_recovery_file_dest_size scope=spfile;
$ srvctl stop database -d ORA11DB
$ srvctl start database -d ORA11DB
$ ./oratop / as sysdba
ORA-01476: divisor is equal to zero
|
참조 :
ORA-01476: divisor is equal to zero ( https://positivemh.tistory.com/344 )
https://doganay.wordpress.com/2022/07/14/oratop-ora-01476-divisor-is-equal-to-zero/
https://community.oracle.com/mosc/discussion/4519020/oratop-get-ora-01476-error-after-applying-19-15-patch