Alter system switch logfile hang解决方法

关于数据库
alter system switch logfile hang住的解决方案

sys@ORA10G>alter system switch logfile;
alter system switch logfile

我只有ctrl+c 来暂停服务

sys@ORA10G>select dest_id,dest_name,status from v$archive_dest;
  
DEST_ID DEST_NAME STATUS
---------- -------------------------------------------------- ---------
1 LOG_ARCHIVE_DEST_1 INACTIVE
2 LOG_ARCHIVE_DEST_2 ERROR
3 LOG_ARCHIVE_DEST_3 INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE
10 LOG_ARCHIVE_DEST_10 INACTIVE

发现，archive_dest_2有一个error 的错误提示。

sys@ORA10G>select * from v$log;
  
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 4 52428800 1 NO INACTIVE 3920027 11-OCT-12
3 1 5 52428800 1 NO INACTIVE 3957823 12-OCT-12
4 1 6 10485760 1 NO CURRENT 3963939 12-OCT-12
确实，当前的日志组在group 4上面，所以，我在切换日志组的时候，也就是切换到group 2的时候，发现hang住了
我查看了alert日志
日志，这样描述的。
Fri Oct 12 15:38:32 2012
ARC1: Failed to archive thread 1 sequence 4 (19502)
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 4 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Oct 12 15:38:36 2012
ORACLE Instance orcl - Archival Error
Fri Oct 12 15:38:36 2012
ORA-16014: log 2 sequence# 4 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/oradata/orcl/redo02.log'
Fri Oct 12 15:38:36 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc1_20081.trc:
ORA-16014: log 2 sequence# 4 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/oradata/orcl/redo02.log'
Fri Oct 12 15:38:36 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:39:15 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:39:36 2012
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:40:37 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:41:37 2012
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:42:37 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 4 (4)
Fri Oct 12 15:43:37 2012
ARC1: Failed to archive thread 1 sequence 4 (19504)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Oct 12 15:43:37 2012
ORACLE Instance orcl - Archival Error
  
ORA-16014: log 2 sequence# 4 not archived, no available destinations
这样就解释了，log 2 文件没有被归档，是因为没有合适目录。
log 2 的没有归档，也导致了 log 3 也没有办法归档，也是因为没有合适的主目录。
知道了设置错误的归档路径，我们就好解决问题了

sys@ORA10G>alter system set log_archive_dest_2='location=/u01/ORCL/archivelog' scope=both;
System altered.
sys@ORA10G>show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string location=/u01/ORCL/archivelog
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
  
sys@ORA10G>alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
我们要重新应用新的归档路径，我们重启库。

sys@ORA10G>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10G>startup
ORACLE instance started.
  
Total System Global Area 444596224 bytes
Fixed Size 2021440 bytes
Variable Size 176162752 bytes
Database Buffers 264241152 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
sys@ORA10G>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ORCL/archivelog
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
sys@ORA10G>show parameter log_archive_dest
  
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string location=/u01/ORCL/archivelog
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
sys@ORA10G>select dest_id,dest_name,status from v$archive_dest;
  
DEST_ID DEST_NAME STATUS
---------- -------------------------------------------------- ---------
1 LOG_ARCHIVE_DEST_1 INACTIVE
2 LOG_ARCHIVE_DEST_2 VALID
3 LOG_ARCHIVE_DEST_3 INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE
10 LOG_ARCHIVE_DEST_10 INACTIVE
  
10 rows selected.
  
sys@ORA10G>select * from v$log;
  
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 4 52428800 1 YES INACTIVE 3920027 11-OCT-12
3 1 5 52428800 1 YES INACTIVE 3957823 12-OCT-12
4 1 6 10485760 1 NO CURRENT 3963939 12-OCT-12
  
sys@ORA10G>alter system switch logfile;
  
System altered.
  
sys@ORA10G>/
  
System altered.
  
sys@ORA10G>/
  
System altered.
  
sys@ORA10G>select *from v$log;
  
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 7 52428800 1 YES INACTIVE 3967333 12-OCT-12
3 1 8 52428800 1 YES INACTIVE 3967335 12-OCT-12
4 1 9 10485760 1 NO CURRENT 3967340 12-OCT-12
  
sys@ORA10G>alter system switch logfile;
  
System altered.
  
sys@ORA10G>select *from v$log;
  
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 10 52428800 1 NO CURRENT 3967347 12-OCT-12
3 1 8 52428800 1 YES INACTIVE 3967335 12-OCT-12
4 1 9 10485760 1 YES INACTIVE 3967340 12-OCT-12
  
sys@ORA10G>
这样，我们切换日志 没有任何问题。很好的解决了 switch log file 问题。