Resolving ORA-19502, ORA-16038 and ORA-27072 Errors in Oracle Database

We recently encountered errors below and there are several common causes.

 ORA-19502: write error on file "/oraarch/TESTDB/1_432678_12436018.dbf", block number 182272 (block size=512)
 ORA-16038: log 2 sequence# 432678 cannot be archived
 ORA-19502: write error on file "", block number (block size=)
 ORA-00312: online log 2 thread 1: '/redo2/TESTDB/TESTDB_1B.rdo'
 ORA-27072: File I/O error

The “ORA-27072: File I/O error” , can occur due to below are common reasons

• Disk issue – This error can also occur if the disk or storage is inaccessible. It might be due to hardware related issues
• File corruption- The file system where database resides might have corrupted.
• Permission issue – If the database user does not have enough permissions, you will get this error.
• Mount failures – when Filesystem not mounted properly

The “ORA-16038” error mainly occurs when archive log file cannot be archived. In this case if the database cannot be able to reuse redo log files, logs cannot switch, the database may hung.

The “ORA-19502” error mainly caused by insufficient disk space or file system full.

In our case, the issue was caused by a full archive log filesystem. 

When archive log file system got full, the redo log archiving failed triggering ORA-16038 and ORA-19502 errors. This eventually resulted ORA-27072 due to failed write attempts

Recommended steps

1. Check the archive log and db_recovery_file_dest destinations

SHOW PARAMETER log_archive_dest;
SHOW PARAMETER db_recovery_file_dest;

If using FRA:

SHOW PARAMETER db_recovery_file_dest_size;

 

2. User should use “df-h” to check the diskspace

User should Pay special attention to:

• Archive destination mount point
• FRA mount point

3. If the file system is full Increase size by extending lun or increasing FRA size.

4. Make sure user run the backup and delete old archive logs

rman target /
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-x';

Here X means number of days

5. In case FRA is full then user should increase the db_recovery_file_dest_size using below example

Check FRA usage using below query

SELECT name, space_limit/1024/1024 MB_LIMIT,
               space_used/1024/1024 MB_USED,
               space_reclaimable/1024/1024 MB_RECLAIMABLE

FROM   v$recovery_file_dest;  

ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

6. Always check for alert.log to review errors and find the root cause.

Look for:

• ARCn errors
• Log switch failures
• Repeated I/O messages

To Avoid this issue in future user can take below measures:

• Monitor FRA usage regularly
• Set up alerting when disk usage exceeds 80%
• Configure proper RMAN retention policy
• Automate archive log deletion after backup
• Separate archive logs from other mount points
• Monitor log switch frequency

In our environment, the archive log filesystem became completely full.

This caused:
ORA-19502 (write failure)
ORA-16038 (cannot archive log)
ORA-27072 (I/O error)

Once disk space was cleared, archiving resumed automatically and the database returned to normal operation.

Thanks & Regards,

https://oracleracexpert.com

Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/oracle/TDB/controlfile/TDB01.ctl
/oracle/TDB/controlfile/TDB02.ctl
/oracle/TDB/controlfile/TDB03.ctl

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps

•  Alter the control_files parameter

ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;

• Shutdown the database and copy or move the files to new location

SQL> SHUTDOWN IMMEDIATE
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl

• Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/dbf1/TDB/controlfile/TDB01.ctl
/dbf2/TDB/controlfile/TDB02.ctl'
/dbf3/TDB/controlfile/TDB03.ctl'

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

Follow the below steps to move or rename a Redo log file

• Shutdown the Db and rename the file at operating system

SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo

• Start the database in mount mode and ALTER DATABASE RENAME FILE

SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;

• Open the database and verify

SQL> Alter database open;
SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.

• You can check the redo status from the V$log view

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE

• Drop and recreate the redo log group with new location

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;

• Switch the log file to change the current redo and recreate the other redo groups

SQL> Alter system switch log file;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards.

http://oracleracexpert.com, Oracle ACE

ORA-01163: SIZE clause indicates (blocks), but should match header

I have generated a control file using “backup control file to trace” but while recreating I have encountered below error

*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 32768 (blocks), but should match header 262144
ORA-01517: log member: '/oracle/TEST/redo1/TEST_redo1.rdo'

I see that the failure is due to log member size. But the current redo log file size on the disk and in the script is correct. I ran below query to find the redo log BLOCKSIZE and found that the size is 4096.

select * from v$log;

I tried couple of options to recreate the control file by changing the redo log size in KB/MB/Bytes..etc but nothing worked. After investigation I found that drop and recreating the redo log is only the option.

Other possible scenarios/errors
1. If you receive the same ORA-01163 error for DATA FILE then your size calculation might be incorrect.

    Use below formula to get the size.
    Expected size = Expected no of blocks * db_block_size / 1024

2. ORA-01378: The logical block size (4096) of file /oracle/TEST/redo1/TEST_redo1.rdo'is not
    compatible with the disk sector size (media sector size is 512 and host sector size is 512)

If you receive above error you ned to set below parameter
SQL> alter system set "_disk_sector_size_override"=TRUE scope=both;

Reference for more info.
ORA-00344: WHEN RESIZING YOUR REDO LOGS (Doc ID 1018307.102)

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Terminating the instance due to error 16038 and unable to startup

I have encountered storage crash on one of the Database system. After storage is back online, not able to bring up the database. The database comes to mount stage and terminating the instance.

We are receiving below error.

ORA-03113: end-of-file on communication channel
Process ID: 5038
Session ID: 202 Serial number: 3

When I look into the alert.log I found below error messages

ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Tue Mar 10 10:24:10 2015
Incomplete read from log member '/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'. Trying next member.
ARCH: All Archive destinations made inactive due to error 333
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/ORAC/archiveORAC_ARCH_347548987.dbf' (error 333) (ORAC)
Committing creation of archivelog '/oracle/ORAC/archive/1_3541_854889367.dbf' (error 333)
Errors in file /oracle/diag/rdbms/orac/ORAC/trace/ORAC_ora_23498.trc:
ORA-16038: log 1 sequence# 3541 cannot be archived
ORA-00333: redo log read error block count
ORA-00312: online log 1 thread 1: '/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'
USER (ospid: 23498): terminating the instance due to error 16038
Tue Mar 10 10:24:12 2015
System state dump requested by (instance=1, osid=23498), summary=[abnormal instance termination].
System State dumped to trace file /oracle/diag/rdbms/orac/ORAC/trace/ORAC_diag_23470.trc
Dumping diagnostic data in directory=[cdmp_20150310102412], requested by (instance=1, osid=23498), summary=[abnomal instance termination].
Instance terminated by USER, pid = 23498

By looking the error, I can see that one of the online redo is corrupted. Archive process cannot able to archive to disk. In order to bring up the database you need to clear the corrupted redo log.

From ALERT.LOG file, I am able to identify that group 1 redo log 1 corrupted. By using below command I have cleared the unarchived log file

SQL> alter database clear unarchived logfile ‘/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'

If you find all multiplexed redo members are corrupted then you need to run below command to clear the GROUP.

SQL> alter database clear unarchived logfile group 1;

Now I am able to bring up the database. But note that you cannot able to bring the database back beyond corrupted point using old backups. Now you must take one FULL database backup.

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com
http://www.sqlserver-expert.com

ORA-16038: log 1 sequence# 3541 cannot be archived

I have encountered storage crash on one of the Database system. After storage is back online, not able to bring up the database. The database comes to mount stage, but going down.

We are receiving below error.

ORA-03113: end-of-file on communication channel
Process ID: 5038
Session ID: 202 Serial number: 3

When I look into the alert.log I found below error messages

ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Tue Mar 10 10:24:10 2015
Incomplete read from log member '/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'. Trying next member.
ARCH: All Archive destinations made inactive due to error 333
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/ORAC/archiveORAC_ARCH_347548987.dbf' (error 333) (ORAC)
Committing creation of archivelog '/oracle/ORAC/archive/1_3541_854889367.dbf' (error 333)
Errors in file /oracle/diag/rdbms/orac/ORAC/trace/ORAC_ora_23498.trc:
ORA-16038: log 1 sequence# 3541 cannot be archived
ORA-00333: redo log read error block count
ORA-00312: online log 1 thread 1: '/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'
USER (ospid: 23498): terminating the instance due to error 16038
Tue Mar 10 10:24:12 2015
System state dump requested by (instance=1, osid=23498), summary=[abnormal instance termination].
System State dumped to trace file /oracle/diag/rdbms/orac/ORAC/trace/ORAC_diag_23470.trc
Dumping diagnostic data in directory=[cdmp_20150310102412], requested by (instance=1, osid=23498), summary=[abnomal instance termination].
Instance terminated by USER, pid = 23498

By looking the error, I can see that one of the online redo is corrupted. Archive process cannot able to archive to disk. In order to bring up the database you need to clear the corrupted redo log.

From ALERT.LOG file, I am able to identify that group 1 redo log 1 corrupted. By using below command I have cleared the unarchived log file

SQL> alter database clear unarchived logfile ‘/oracle/ORAC/datafile/ORAC/onlinelog/ORAC_Redo1_.log'

If you find all multiplexed redo members are corrupted then you need to run below command to clear the GROUP.

SQL> alter database clear unarchived logfile group 1;

Now I am able to bring up the database. But note that you cannot able to bring the database back beyond corrupted point using old backups. Now you must take one FULL database backup.

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com
http://www.sqlserver-expert.com

Optimal redo logfile size in Oracle

If you have a small redo log file then you will see frequent log switches, in case of large redo logfile you might be at risk of losing data during instance crash. The optimal redo logfile size should not have more than 5 switches per hour.

You can use below query to find the number of log switches per hour

col day format a15;
col hour format a4;
col total format 999;
select to_char(first_time,'yyyy-mm-dd') day, to_char(first_time,'hh24') hour, count(*) total
from v$log_history
group by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24') asc;

To resize the redo logfile size follow the steps in below link

http://www.oracleracexpert.com/2013/07/thread-1-cannot-allocate-new-log.html

In Oracle 10g, the Redo Logfile Size Advisor introduced and using this you can determine the optimal redo log size based upon FAST_START_MTTR_TARGET parameter. You must set a non-zero value to enable redo log file size advisor.

FAST_START_MTTR_TARGET – this parameter enables you to specific number of seconds the database takes to perform crash recovery. Based up on this value Oracle determines the checkpoint writes to meet the target.

If you DONT set FAST_START_MTTR_TARGET then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will not populated with recommend redo log file size.

SQL> show parameter FAST_START_MTTR_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0

SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------------------

User must set FAST_START_MTTR_TARGET to a non-zero value then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will be populated with recommend redo log file size.

Here I am setting FAST_START_MTTR_TARGET=60 (sec), you will see that OPTIMAL_LOGFILE_SIZE will be populated with recommended value.

SQL> alter system set FAST_START_MTTR_TARGET=60 scope=both;
System altered.

SQL> select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
151

Now the checkpoints are driven by FAST_START_MTTR_TARGET parameter.

In some cases user will see many log switches during batch job window and there is no log switches out of batch job window. In this case you need to optimal value for redo log size and may need to set archive_lag_target to force redo log switches to increase the frequency during non-batch job window.

For more information on archive_lag_target refer below Oracle document.
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams009.htm

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Thread 1 cannot allocate new log & Checkpoint not complete

Users will see these messages when Oracle wants to reuse the redolog file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

Cause: In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Mon Apr 15 07:20:42 2013
Thread 1 advanced to log sequence 5021
Current log# 1 seq# 5021 mem# 0: /oracle/ORCL/redoA/redo01.log
Mon Apr 15 07:21:15 2013
Thread 1 cannot allocate new log, sequence 5022
Checkpoint not complete

If you have many updates in the system, you might need more redo groups. If you have fewer redo groups then adding more redo groups will help.

Use below syntax to add more redo groups

ALTER DATABASE ADD LOGFILE GROUP <Group No> ('<Redo log member 1 path’ ,'<redo log member 2 path>') size 500M;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switch logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT

Step2:- Drop the log group1 which is ‘INACTIVE’ and recreate with bigger size.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 ('/db01/ORCL/redoA/log1_m1.dbf',' /db01/ORCL/redoB/log1_m2.dbf') size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

set lines 120;
set pages 999;
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by to_char(first_time,'YYYY-MON-DD');

Click here to learn about "Private Strand Flush Not Complete"  message in alert.log

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

“Private Strand Flush Not Complete” in Oracle

The below error message is very common and users see in alert<SID> .log file. You will see this error message when writing the redo info is not completed, when the log switch happens.

Note that the log switch cannot happen until all of the redo has been written.

Users might also notice these error messages when there is an issue with network or storage where you have archive log destination, this is due to delay or hand in LGWR switch .

Thread 1 cannot allocate new log, sequence 5929

Private strand flush not complete

Current log# 4 seq# 5928 mem# 0: /oracle/ORAC/origlogB/log_g14m1.dbf

Current log# 4 seq# 5928 mem# 1: /oracle/ORAC/mirrlogB/log_g14m2.dbf

Beginning log switch checkpoint up to RBA [0x10189.2.10], SCN: 6845363020

Thread 1 advanced to log sequence 5929 (LGWR switch)

This message related to internal Cache Redo File management and safe to discard these messages.

These messages are not cause for concern unless there is a significant time gap between “cannot allow new log” and “advanced to log sequence” message. In some cases users benefited with db_writer_processes and this help to avoid message from being generated.

Click here to learn about "Cannot allocate new log & Checkpoint not complete"
message in alert.log

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com