ALTER SYSTEM SWITCH LOGFILE hangs or Archive Location Full-Oracle Database

ALTER SYSTEM SWITCH LOGFILE hangs and there was nothing I could do about it.


On digging further, I found the cause.

My archive location was full:


Then I checked the archive mount point. And found it was full.From another session I issued the following command: (Note that we were not using ASM)

rthacc:TCEACC:>df -hk /d*/TCEACC
Filesystem             size   used  avail capacity  Mounted on
/dev/md/dsk/d205        33G    33G   2.1M   100%    /db_arch
/dev/md/dsk/d206       598G   147G   444G    25%    /db_dump


Immediately changed log archive location to a location where we had space:

SQL> alter system set log_archive_dest_1='LOCATION=/db_dump/TCEACC/temp_arch reopen=600';

System altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         4         ONLINE
/dbo/redo/ICEACC/ICEACC_redo_04.log
NO

         3         ONLINE
/dbo/redo/ICEACC/ICEACC_redo_03.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         2         ONLINE
/dbo/redo/ICEACC/ICEACC_redo_02.log
NO

         1         ONLINE
/dbo/redo/ICEACC/ICEACC_redo_01.log

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO





SQL> alter system switch logfile;

System altered.(it was hanged before)




SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1       4493  157286400        512          1 NO
CURRENT             5901915177 26-MAY-12   2.8147E+14

         2          1       4490  157286400        512          1 YES
ACTIVE              5901867761 26-MAY-12   5901894892 26-MAY-12

         3          1       4491  157286400        512          1 YES
ACTIVE              5901894892 26-MAY-12   5901904781 26-MAY-12


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1       4492  157286400        512          1 YES
ACTIVE              5901904781 26-MAY-12   5901915177 26-MAY-12





SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TCEACC    READ WRITE

alter system set log_archive_dest_1='location='/db_dump/TCEACC/temp_arch';


Login to rman to the catalog server as we cannot do anything on db as it is locked after reaching 100%.:


rman target / catalog rman/rman@RCATS;

then run the following backup script:

rman target / catalog rman/rman@RCATS


rman target / catalog rman/rman@RCATS

cat rman_ICEACC_archive.cmd
run {
allocate channel 'dev_0' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
allocate channel 'dev_1' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
allocate channel 'dev_2' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
allocate channel 'dev_3' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
allocate channel 'dev_4' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
allocate channel 'dev_5' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=TCEACC,OB2BARLIST=ora_TCEACC_rman_archive)';
backup filesperset 10
format 'ora_ICEACC_rman_archive<TCEACC_%s:%t:%p>.dbf'
(archivelog all delete input)
;
}

show parameter log_archive_dest;


change the archive log destination to previous.


and check the if the db_dump location still having archive file then have to run rman archive backup script again and again.



N.B.->If the directory for archiving is full you will see lot of erros in alert.log saying that. You will have to free space in that directory and the database will continue normally.

If all your redo logs are full and the directory for archiving is full, then the database will stopped, waiting for you to free space. sometimes I saw a database go down when the archiving is not possible and all your redo logs are full.


There could be an another reason of  ALTER SYSTEM SWITCH LOGFILE hangng:

If the database was in manual archive log mode.

i.e. Automatic archiving was disabled.


SQL> archive log list

Database log mode Archive Mode

Automatic archival Disabled

Archive destination C:\oracle\ora92\RDBMS

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12


In this case when all the online redo logs get filled up we need to manually archive the online redo log files.

Until then, the “ALTER SYSTEM SWITCH LOGFILE” command will make the database wait till the online redo log files

are archived with the command

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;


System altered.