ALTER SYSTEM SWITCH LOGFILE hangs [Oracle 9i]

While learning Oracle DBA, I came across a situation, where I found the command

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

On digging further, I found the cause.

My 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.

You will need to issue the above command from a new session.