All Redo log group Members are in active state, ORA-20 max number of processes exceeded, Database went hung state After restart
A) All Redo log group Members all are in active state
If the redo log members all are in active state means Huge Insert or update is running on database, which is uncommitted and also which makes database to hung state and session count increases to maximum process limit.
FInd the session and kill it, if the user end malfunction query.
B) Database was in hang state due to ORA-20 max number of processess exceeded
Solution:
As the first instance of the database hang is due to the ORA-20 error max process exceeded. This is the cause for the database to go into hang state.
If you are not able to logging to the database kill few LOCAL=NO sessions and try to login to database
verify the total count of Oracle process
ps -ef | grep LOCAL = NO | wc –l
Increasing the processes parameter to a higher value: alter system set processes= scope=spfile;
Stop and restart the database
1.Gather information about the processes that exist when the ORA-20 occurs
Query:
select p.username “V$PROCESS – OS USERNAME”, p.terminal, p.program, s.username “V$SESSION – USERNAME”,
s.command, s.status, to_char(s.LOGON_TIME,’DD-MON-YY HH24:MI:SS’) “LOGON TIME”, s.LAST_CALL_ET/60 “LAST_CALL TIME”,
s.server, s.process, s.machine, s.port, s.terminal, s.program, s.sid, s.serial#, p.spid FROM v$session s,v$process p
WHERE p.addr=s.paddr order by p.background desc;
2) If the issue happened in the past, you can try capturing the data from the AWR views. This is available only from 10g onwards and requires license for Oracle Diagnostic Pack.
Query to Find from past session
select program, module, machine, count (*) from DBA_HIST_ACTIVE_SESS_HISTORY where SNAP_ID between snapid and snapid and INSTANCE_NUMBER=1 group by program, module, machine;
3)In some cases ORA-20 errors are reported while starting up the database, after a crash. This is mostly due to the stray semaphores and shared memory segments
C)Database went hung state After restart
After which you have restarted the database and as the process after the instance restart it will do the recovery. So, the instance crash recovery is being performed by the PMON process. It is taking time in your case as the recovery will all depends the amount of transactions that has to the recovered.
Wait until recover completes.
12c Multitnenant Database:
Please note that : Oracle Database performs crash and instance recovery for the entire multitenant container database (CDB). You cannot recover individual pluggable databases (PDBs).
Symptoms
High CPU Utilization by SMON process
Database may hang during large transaction recovery.
If Database is shutdown abort, then the database may hang during consequent startup.
Database repeatedly crashes while open.
Database internal errors which could result in transaction recovery failure.
To monitor transaction recovery progress:
====================
set linesize 100
select ktuxeusn, to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’) “Time”, ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = ‘DEAD’;
alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’;
select usn, state, undoblockstotal “Total”, undoblocksdone “Done”, undoblockstotal-undoblocksdone “ToDo”, decode(cputime,0,’unknown’,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Estimated time to complete” from v$fast_start_transactions;
