Friday, April 24, 2020

How to fix : ORA-03113: end-of-file on communication channel

Today when I tried to open my db (that db was created and lunched inside container) I saw ORA-03113: end-of-file on communication channel.


 SQL> startup  
 ORACLE instance started.  
 Total System Global Area 3.4206E+10 bytes  
 Fixed Size   2270360 bytes  
 Variable Size  3422554984 bytes  
 Database Buffers 3.0736E+10 bytes  
 Redo Buffers   45649920 bytes  
 Database mounted.  
 ERROR at line 1:  
 ORA-03113: end-of-file on communication channel  
 Process ID: 11344  
 Session ID: 380 Serial number: 3 



There is a lot of reason can be, after check it out from the forum all I need is clear unarchivelog.

First start instance mount mode.


 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 3.4206E+10 bytes  
 Fixed Size   2270360 bytes  
 Variable Size  3422554984 bytes  
 Database Buffers 3.0736E+10 bytes  
 Redo Buffers   45649920 bytes  
 Database mounted.Database mounted. 



-check how many group we have:


SQL> set linesize 200  
 SQL> select group#, members, status from v$log;  
   GROUP#  MEMBERS STATUS  
 ---------- ---------- ----------------  
  1   2 INACTIVE  
  2   2 INACTIVE  
  3   2 INACTIVE  
  4   2 INACTIVE  
  5   2 INACTIVE  
  6   2 CURRENT  
  14   2 INACTIVE  
  8   2 INACTIVE  
  9   2 INACTIVE  
  10   2 INACTIVE  
  11   2 INACTIVE  
   GROUP#  MEMBERS STATUS  
 ---------- ---------- ----------------  
  12   2 INACTIVE  
  13   2 INACTIVE  
  7   2 INACTIVE  
 14 rows selected.  
 SQL>  


Writing this simple PLSQL anonyms block to clear unarchived log files.



SQL> BEGIN FOR i IN 1 .. 14 LOOP execute immediate 'alter database clear unarchived logfile group '||i; END LOOP; END; / PL/SQL procedure successfully completed. SQL>


--look alertlog
alter database clear unarchived logfile group 13
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 04/15/2020 12:35:11 (CHANGE 228093) CANNOT BE USED FOR RECOVERY.
Clearing online log 13 of thread 1 sequence number 195
Completed: alter database clear unarchived logfile group 13
alter database clear unarchived logfile group 14
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 04/15/2020 12:40:12 (CHANGE 228198) CANNOT BE USED FOR RECOVERY.
Clearing online log 14 of thread 1 sequence number 196
Wed Apr 15 15:22:49 2020
Completed: alter database clear unarchived logfile group 14




you can restart db or just try to switch from mount mode to open

alter database open;

or

 SQL> shut immediate  
 ORA-01109: database not open  
 Database dismounted.  
 ORACLE instance shut down.  
 SQL>  
 SQL> startup  
 ORACLE instance started.  
 Total System Global Area 3.4206E+10 bytes  
 Fixed Size   2270360 bytes  
 Variable Size  3422554984 bytes  
 Database Buffers 3.0736E+10 bytes  
 Redo Buffers   45649920 bytes  
 Database mounted.  
 Database opened.  
 SQL>  




Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...