ORA-16014: log 1 sequence# 1221 not archived, no available destinations

Today in my Primary database I was dong some log switching. It took a bit long to complete the log switch and so I decided to look into the alert log. I could able to see some issue been recorded in the alert log. Below is the issue.

Issue:

Alert log:
ARC1: Error 19504 Creating archive log file to '+DATA/vis/datafile/archive/1_1221_970193294.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance VIS - Archival Error
ORA-16038: log 1 sequence# 1221 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01b.dbf'
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01a.dbf'
Archiver process freed from errors. No longer stopped
Tue Jul 24 19:17:42 2018
ARC3: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance VIS - Archival Error
ORA-16014: log 1 sequence# 1221 not archived, no available destinations
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01b.dbf'
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01a.dbf'
Tue Jul 24 19:23:06 2018
ARC3: Error 19504 Creating archive log file to '+DATA/vis/datafile/archive/1_1221_970193294.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance VIS - Archival Error
ORA-16038: log 1 sequence# 1221 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01b.dbf'
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01a.dbf'
Tue Jul 24 19:23:06 2018
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance VIS - Archival Error
ORA-16014: log 1 sequence# 1221 not archived, no available destinations
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01b.dbf'
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01a.dbf'
Tue Jul 24 19:27:14 2018
Incremental checkpoint up to RBA [0x4c6.50f.0], current log tail at RBA [0x4c6.56f.0]
Tue Jul 24 19:28:06 2018
ARC1: Error 19504 Creating archive log file to '+DATA/vis/datafile/archive/1_1221_970193294.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance VIS - Archival Error
ORA-16038: log 1 sequence# 1221 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01b.dbf'
ORA-00312: online log 1 thread 1: '+DATA/vis/logfile/log01a.dbf'
Tue Jul 24 19:28:07 2018
ARC3: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Archival stopped, error occurred. Will continue retrying

Here in my case log sequence 1221 cannot be archived and the same belongs to the archive group 1.

Reason :

One of the online redo is corrupted and so archiver cannot archive that to the disk. Now to bring back the database to normal state, need to clear the corrupted redo log.

Solution:
Clear the corrupted redo log



Now the database will start normally, but we can not use those old logs. Now need to take a full backup in-order to have proper and valid backup.

NOTE : Since I faced this issue a few times, I tried different options. The above is not the only solution to overcome this issue. For me one time I faced this issue and simply I checked the archive mount point usage and found only 32 MB is available as free space. My log size 150 MB. So this was the reason for the issue. Archiver is trying to write the archived log in the archive destination and due to in-adequate amount of space it waits and registered the above issue in alert log file. After clearing up the unwanted old log files I could able to make some free space and the archival process is completed normally. So always try to find the root cause for the issue and take the action/workaround accordingly.
****************************நன்றி****************************

AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.

Today I ran adcfgclone to refresh my UAT Instance and faced the below issue after alomost 70% of the process is completed. On digging the particular log file for issues, found the below two error.


Error : 1
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated

adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.

.end err out.

Error : 2
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [SETUP PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /ora12prd/VIS/inst/apps/VIS_apps_dr/admin/install
      adgendbc.sh             INSTE8_SETUP       1


AutoConfig is exiting with status 1

RC-50014: Fatal: Execution of AutoConfig was failed
Raised by oracle.apps.ad.clone.ApplyApplTop
ERROR: AutoConfig completed with errors. Check logfile at /ora12prd/VIS/inst/apps/VIS_apps_dr/admin/log/ApplyAppsTier_07181145.log for details.
ApplyApplTop Completed Successfully.

# Checking the status of AutoConfig run of ApplyApplTop

Warning : AutoConfig has completed with  errors .
Please review the AutoConfig section in the logfile. If required, you can re-run AutoConfig from command line  after fixing the problem


Cause :

The Network Topology tables had not been populated with the database information, either during the clone or during AutoConfig. Much of the Network Topology data seeding is done during AutoConfig on the dbTier.

Solution :

Rerun AutoConfig on all nodes, starting with the database node(s), as per standard procedures.

1. Login as oracle user and perform the below steps

            sqlplus apps/apps

            exec fnd_conc_clone.setup_clean

2. Run autoconfig on db node.

3. Run autocnfig on apps node.

4. Run adcfgclone now, this time it should work fine.

Ref : "AC-50480" Error Generating the "listener.ora" File. (Doc ID 1089036.1)

****************************நன்றி****************************

Unable to locate 'linkxlC' utility in path

Today I ran adcfgclone at Apps Tier to refresh my TEST Instance and faced the below issue.

Issue:

no linkxlC in /u01/oracle/ERPDEV3/apps/apps_st/comn/clone/bin/../jre/bin /usr/bin /etc /usr/sbin /usr/ucb /home/oracle/bin /usr/bin/X11 /sbin .
Checking for make...    found - /usr/bin/make
 Checking for ld...     found - /usr/bin/ld
 Checking for linkxlC...
 Unable to locate 'linkxlC' utility in path
 Checking for ar...     found - /usr/bin/ar
 Unable to locate all utilities with system path.
 PATH = /u01/oracle/ERPDEV3/apps/apps_st/comn/clone/bin/../jre/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:.

WARNING: Could not find all the required OS utilities in the $PATH. Please review the checks above



Solution:

Export the path variable along with the location of linkxlC



Steps:

which linkxlC
/usr/vacpp/bin/linkxlC

$ export PATH=/usr/vacpp/bin:$PATH

Now run adcfgclone, this time it should run without any issues.


****************************நன்றி****************************

Status : Failure - Test failed : IO Error : Got minus one from a read call

One of my colleague reported he is facing issue while trying to connect  instance from back-end using sql-developer.

Issue :
Status : Failure - Test failed : IO Error : Got minus one from a read call


Solution : 
After changing a parameter "tcp.validnode_checking" in sqlnet.ora file, this issue is resolved and can connect from back-end successfully.

Before change

 After change

****************************நன்றி****************************

TNS-12549: TNS:operating system resource quota exceeded

Today I faced a new issue after refreshed my TEST Instance. After the refresh has completed, took a complete bounce on both DB and APPS Tiers. While bringing back the DB, listener process is not getting started and throws the below error. Quick googling provide me the solution for this issue.

Issue: 

TNS-12549: TNS:operating system resource quota exceeded
TNS-12560: TNS:protocol adapter error
TNS-00519: Operating system resource quota exceeded
Linux Error: 28: No space left on device

Solution: 

Delete unwanted files under the /var mount point or under the /var/tmp folder. Now re-start the listener and this time, listener will start without any issues.

Ref : Listener / Database Startup fails with underlying OS error Linux Error: 28: No Space Left On Device (Doc ID 553709.1)


****************************நன்றி****************************

Issue in DR Stand-By Instance while start-up

After creating the Stand-by Instance using RMAN backup from Primary Instance, below issue occurs while trying to start the standby database.

dbmgrprd@db_dr:/oracle/RMAN#!sq

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 17:51:18 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2228344 bytes

Variable Size             432017288 bytes

Database Buffers          620756992 bytes

Redo Buffers               13934592 bytes

Database mounted.

ORA-10458: standby database requires recovery

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '+DATA/vis/datafile/system01.dbf'

On investigating the alert log file, found the database is searching for the archive log sequence 970 to apply.

ALTER DATABASE OPEN

Beginning standby crash recovery.

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Media Recovery Waiting for thread 1 sequence 970

Fetching gap sequence in thread 1, gap sequence 970-970

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Wed Jul 11 17:51:34 2018

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/vis/datafile/archive

Creating archive destination file : +DATA/vis/datafile/archive/1_970_970193294.dbf (430376 blocks)

Wed Jul 11 17:51:54 2018

Creating archive destination file : +DATA/vis/datafile/archive/1_970_970193294.dbf (430376 blocks)

Wed Jul 11 17:52:15 2018

Creating archive destination file : +DATA/vis/datafile/archive/1_970_970193294.dbf (430376 blocks)

Wed Jul 11 17:52:35 2018

Standby crash recovery need archive log for thread 1 sequence 970 to continue.

Please verify that primary database is transporting redo logs to the standby database.

Wait timeout: thread 1 sequence 970

Standby crash recovery aborted due to error 16016.

Errors in file /oracle/VIS/db/11.2.0/admin/VIS_db_dr/diag/rdbms/vis/VIS/trace/VIS_ora_4718654.trc:

ORA-16016: archived log for thread 1 sequence# 970 unavailable

Recovery interrupted!

Completed standby crash recovery.

Errors in file /oracle/VIS/db/11.2.0/admin/VIS_db_dr/diag/rdbms/vis/VIS/trace/VIS_ora_4718654.trc:

ORA-10458: standby database requires recovery

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '+DATA/vis/datafile/system01.dbf'

Since all the parameters for connectivity are already established, wondering where the problem might be. After some thorough checking, found the directory "archive" is missed under the archive location specified in the pfile. This is the reason for the logs are not getting transferred from Primary to Stand-by Instance. After creating the required directory structure, manually copied the file(archive sequence #970) and followed the below steps.

On Primary:

scp <missing archive file> <user>@<stand-by server IP>:<stand-by archive file destination>

On Stand-by:

Assign the proper ownership and permission to the file

SQL> alter database register logfile '</path/filename>';

SQL>alter database recover managed standby database disconnect;

Alert log content:

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log +DATA/vis/datafile/archive/1_970_970193294.dbf

Completed: alter database recover managed standby database disconnect

Since the media recovery happened successfully, tried to open the stand-by database

SQL>alter database open;

Now the issue is solved and the database opens successfully.

Note: Repeat the above steps as per the missing log files according to your issue criteria.

****************************நன்றி****************************