ORA-16014: log 2 sequence# 14 錯誤解決

在執行：ALTER SYSTEM SWITCH LOGFILE；時提示修改成功，但是在ASM硬盤組內歸檔日志空間則沒有相應的歸檔日志文件，則說明日志切換不成功，然后再通過：SQL> ALTER SYSTEM ARCHIVE LOG CURRENT；修改時則提示如下錯誤：

ERROR at line 1:
ORA-16014: log 2 sequence# 14 not archived, no available destinations
ORA-00312: online log 2 thread 1:
'+DGDATA02/udevasm/onlinelog/group_2.258.945212025'

通過查詢相關日志說明，則可以是閃回空間不足導致的，查詢閃回空間大小

SQL> show parameter db_recovery

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0

發現閃回空間沒有設置大小導致無法進行日志切換，修改閃回空間為3G大小

SQL>  alter system set db_recovery_file_dest_size=3G scope=both;

再次查詢閃回空間大小：

SQL>  show parameter db_recovery
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 3G

通過查詢已經有閃回空間了，現在則需要測試一下切換一下日志是否成功

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT  ;
ALTER SYSTEM ARCHIVE LOG CURRENT
*
ERROR at line 1:
ORA-16014: log 2 sequence# 14 not archived, no available destinations
ORA-00312: online log 2 thread 1:
'+DGDATA02/udevasm/onlinelog/group_2.258.945212025'

發現執行ALTER SYSTEM ARCHIVE LOG CURRENT時還是無法切換成功，想到在修改閃回空間大小時沒有把數據庫重啟一下，最好重啟一下數據庫看看是否成功

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size            2260088 bytes
Variable Size          331350920 bytes
Database Buffers      729808896 bytes
Redo Buffers            5517312 bytes
Database mounted.
Database opened.

啟動后再執行一下日志切換：

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

發現執行成功了，現在進ASM中查看是否有歸檔日志，在查詢之前要查詢一下歸檔存儲在哪個位置

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           +DGRECOVERY/arc
Oldest online log sequence     9
Next log sequence to archive   14
Current log sequence           14

切換grid用戶查詢ASM硬盤組情況：

[oracle@udevasm ~]$ su - grid
Password: 
[grid@udevasm:/home/grid]$asmcmd
ASMCMD> ls
DGDATA01/
DGDATA02/
DGRECOVERY/
DGSYSTEM/
GRID1/
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     22520      260                0             260              0             N  DGDATA01/
MOUNTED  EXTERN  N         512   4096  4194304     20472    14132                0           14132              0             N  DGDATA02/
MOUNTED  EXTERN  N         512   4096  4194304     21500    21420                0           21420              0             N  DGRECOVERY/
MOUNTED  EXTERN  N         512   4096  4194304     20472    20392                0           20392              0             N  DGSYSTEM/
MOUNTED  EXTERN  N         512   4096  4194304     10232    10144                0           10144              0             N  GRID1/
ASMCMD> cd DGRECOVERY/

切換后的數據為：

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     22520      260                0             260              0             N  DGDATA01/
MOUNTED  EXTERN  N         512   4096  4194304     20472    14132                0           14132              0             N  DGDATA02/
MOUNTED  EXTERN  N         512   4096  4194304     21500    21420                0           21420              0             N  DGRECOVERY/
MOUNTED  EXTERN  N         512   4096  4194304     20472    20392                0           20392              0             N  DGSYSTEM/
MOUNTED  EXTERN  N         512   4096  4194304     10232    10144                0           10144              0             N  GRID1/

查詢歸檔空間是否有文件

ASMCMD> cd +DGRECOVERY
ASMCMD> ls
UDEVASM/
ASMCMD> cd UDEVASM/
ASMCMD> ls
ARCHIVELOG/
ASMCMD> cd ARCHIVELOG/
ASMCMD> ls
2017_06_08/
ASMCMD> cd 2017_06_08/
ASMCMD> ls
thread_1_seq_14.256.946154499
thread_1_seq_15.257.946154499
thread_1_seq_16.258.946154499
thread_1_seq_17.259.946154499
thread_1_seq_18.260.946154501
thread_1_seq_19.261.946154523
thread_1_seq_20.262.946154571
ASMCMD> pwd
+DGRECOVERY/UDEVASM/ARCHIVELOG/2017_06_08

可以再次通過日志切換查詢文件是否增加

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.

查詢ASM結果：

ASMCMD> ls
thread_1_seq_14.256.946154499
thread_1_seq_15.257.946154499
thread_1_seq_16.258.946154499
thread_1_seq_17.259.946154499
thread_1_seq_18.260.946154501
thread_1_seq_19.261.946154523
thread_1_seq_20.262.946154571
thread_1_seq_21.263.946154599

通過上述內容則可以判斷日志切換是成功的，而且thread_1_seq_21.263.946154599 這一個文件就是增加的

這樣日志切換就成功了