crontab
0,15,30,45 * * * * /software/oracle/oracle/scripts/memory_usage/gg_mem_usage_wrapper.sh
crontab
0,15,30,45 * * * * /software/oracle/oracle/scripts/memory_usage/gg_mem_usage_wrapper.sh
=================
General
=================
There are large trace files with same error:
-rw-r----- 1 oracle dba 658132992 Jan 8 18:57 ./diag/rdbms/igt/igt/trace/igt_arc1_30193.trc
-rw-r----- 1 oracle dba 649527296 Jan 8 18:57 ./diag/rdbms/igt/igt/trace/igt_arc0_30185.trc
-rw-r----- 1 oracle dba 537001984 Jan 11 00:46 ./diag/rdbms/igt/igt/trace/igt_arc0_30446.trc
=================
Error in trace files
=================
============================================================
/software/oracle/diag/rdbms/igt/igt/trace/igt_arc1_30193.trc
============================================================
Trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_arc1_30193.trc
krsv_proc_add: Request to add process to V$MANAGED_STANDBY [krss.c:2851]
*** 2021-01-08T17:30:31.928588+05:45
Corrupt redo block 691858 detected: BAD BLOCK HEADER
header size = 16, block size = 512
Seq: 0x00000000 Block: 0x00000000 Time: 134217728 Beg: 0x0 Cks: 0x0
Dump of memory from 0x00007FBE4B0FE400 to 0x00007FBE4B0FE600
7FBE4B0FE400 00000000 00000000 08000000 00000000 [................]
Corrupt redo block 691858 detected: BAD BLOCK HEADER
header size = 16, block size = 512
Seq: 0x00000000 Block: 0x00000000 Time: 134217728 Beg: 0x0 Cks: 0x0
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
2021-01-08T17:30:32.068886+05:45
Incident 128315 created, dump file: /software/oracle/diag/rdbms/igt/igt/incident/incdir_128315/igt_arc1_30193_i128315.trc
ORA-00353: log corruption near block 691858 change 38994289 time 01/08/2021 17:00:25
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
<error barrier> at 0x7fff9511adc8 placed krse.c@7386
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 691858 change 38994289 time 01/08/2021 17:00:25
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
*** 2021-01-08 17:30:33.201232 [krsh.c:6348]
Error 354 while archiving
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
<error barrier> at 0x7fff9511f820 placed krse.c@1876
ORA-16038: log 3 sequence# 4695 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
*** 2021-01-08T17:30:37.150427+05:45
Corrupt redo block 691858 detected: BAD BLOCK HEADER
header size = 16, block size = 512
Seq: 0x00000000 Block: 0x00000000 Time: 134217728 Beg: 0x0 Cks: 0x0
SET LINESIZE 160
COL STATUS FOR A20
COL REDOLOG_FILE_NAME FOR A60
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- ------------ -------------------- ---------------------------------------- ----------
1 1 4782 YES ACTIVE /oracle_db/db1/db_igt/ora_redo_01_a.rdo 1024
2 1 4783 NO CURRENT /oracle_db/db1/db_igt/ora_redo_02_a.rdo 1024
3 1 4695 NO INACTIVE /oracle_db/db1/db_igt/ora_redo_03_a.rdo 1024
All errors are from redo 03 group
oracle@my_server:/software/oracle/diag/rdbms/igt/igt/trace>% grep ORA-00312 *.trc | sort -u
igt_arc0_30446.trc:ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
igt_arc1_30466.trc:ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
igt_arc2_30476.trc:ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
igt_arc3_30487.trc:ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
================
Solution
================
SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
SQL>SET LINESIZE 160
COL STATUS FOR A20
COL REDOLOG_FILE_NAME FOR A60
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM V$LOG a
JOIN V$LOGFILE b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- ------------ -------------------- ------------------------------------------------------------ ----------
1 1 4782 YES INACTIVE /oracle_db/db1/db_igt/ora_redo_01_a.rdo 1024
2 1 4783 NO CURRENT /oracle_db/db1/db_igt/ora_redo_02_a.rdo 1024
3 1 0 YES UNUSED /oracle_db/db1/db_igt/ora_redo_03_a.rdo 1024
Group Log status meaning:
CURRENT - Current redo log. This implies that the redo log is active.
ACTIVE - Log is active but is not the current log. It is needed for crash recovery.
INACTIVE - Log is no longer needed for instance recovery.
UNUSED - Online redo log has never been written to.
This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log, or after CLEAR UNARCHIVED LOGFILE command
CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
In alert.log:
Clearing online log 3 of thread 1 sequence number 4695
2021-01-11T01:17:54.055476+05:45
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_arc3_30487.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 691858 change 38994289 time 01/08/2021 17:00:25
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
2021-01-11T01:17:54.055551+05:45
ARC3 (PID:30487): Error 354 while archiving
2021-01-11T01:17:54.055628+05:45
Closing local archive destination LOG_ARCHIVE_DEST_1 '/oracle_db/db2/db_igt/arch/arch0001_4695_1040477025.arc', error=354 (igt)
ARC3 (PID:30487): Committing creation of archive log '/oracle_db/db2/db_igt/arch/arch0001_4695_1040477025.arc', error=354
ARC3 (PID:30487): LNO:3 mismatch, expected sequence 4695 found T-1.S-0
2021-01-11T01:17:54.078378+05:45
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_arc3_30487.trc:
ORA-16038: log 3 sequence# 4695 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: '/oracle_db/db1/db_igt/ora_redo_03_a.rdo'
ARC3 (PID:30487): Archival error occurred on a closed thread, archiver continuing
2021-01-11T01:17:54.078511+05:45
ORACLE Instance igt, archival error, archiver continuing
2021-01-11T01:17:57.357756+05:45
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
2021-01-11T01:22:42.413279+05:45
ALTER SYSTEM ARCHIVE LOG
2021-01-11T01:22:42.483945+05:45
Thread 1 advanced to log sequence 4784 (LGWR switch)
Current log# 3 seq# 4784 mem# 0: /oracle_db/db1/db_igt/ora_redo_03_a.rdo
2021-01-11T01:22:44.306443+05:45
NET (PID:7901): Archived Log entry 4417 added for T-1.S-4783 ID 0x4c386ae1 LAD:1
2021-01-11T01:22:48.774541+05:45
Control autobackup written to DISK device
SQL> /
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- ------------ -------------------- ------------------------------------------------------------ ----------
1 1 4782 YES INACTIVE /oracle_db/db1/db_igt/ora_redo_01_a.rdo 1024
2 1 4783 YES ACTIVE /oracle_db/db1/db_igt/ora_redo_02_a.rdo 1024
3 1 4784 NO CURRENT /oracle_db/db1/db_igt/ora_redo_03_a.rdo 1024
Now, the redo log group 3 could be archived.
Issue fixed.