Friday, April 7, 2017

sqlplus hangs : does not respond, you have to kill it with CTRL+C

Hello Friends;

I am back with another situation.

Preface:
Sometimes it is observed that sqlplus command goes into infinite loop and does not produce any error as well.

sqlplus "/as sysdba"

Cursor just blinks and nothing else. No SQL prompt; No Messages

Environment:

1. Oracle binaries mounted over NFS on backup server.
2. Datafiles file systems are mounted from snap on the backup server.
3. SAP brbackup tool used for backup using TSM utility.

Problem:

BR0051I BRBACKUP 7.40 (16)
BR0169I Value 'util_file_online' of parameter/option 'backup_dev_type/-d' ignored for 'online_split' - 'util_file' assumed
BR0055I Start of database backup: bevnzheg.anf 2017-04-07 10:22:10
BR0484I BRBACKUP log file: /oracle/SID/sapbackup/bevnzheg.anf

The cursor blinks here for infinite. There is no progress and interestingly at the scheduler ends job status is running

Findings:

When truss was executed on the process, it is seen that process is sleeping after reaching the directory /$ORACLE_HOME/oradiag_SID/diag/clients/user_SID/Host_ID_80/metadata.

Remediation:

Upon multiple investigation it is found that system looks for some file in the directory as below :

ADR_CONTROL.ams
INC_METER_PK_IMPTS.ams
INC_METER_IMPT_DEF.ams
ADR_INVALIDATION.ams

These files should be owned by oracle_SID user only.

If the file is not found sqlplus process keeps on sleeping and neither prompt for the login / password nor provides any error.

Please copy the above file from some other location and re-verify with sqlplus. 

Please do reach with your comments.

No comments:

Post a Comment

What is The Abilene Paradox ?

Context : Corporate World. (excerpt from publicly available sources) On a birthday, a family decided to go out for dinner. Husb...