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 destinationsORA-00312: online log 2 thread 1:'+DGDATA02/udevasm/onlinelog/group_2.258.945212025'

通过查询相关日志说明，则可以是闪回空间不足导致的，查询闪回空间大小

SQL> show parameter db_recoveryNAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest             stringdb_recovery_file_dest_size         big integer 0

发现闪回空间没有设置大小导致无法进行日志切换，修改闪回空间为3G大小

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

再次查询闪回空间大小：

SQL>  show parameter db_recoveryNAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest             stringdb_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 destinationsORA-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 bytesFixed Size            2260088 bytesVariable Size          331350920 bytesDatabase Buffers      729808896 bytesRedo Buffers            5517312 bytesDatabase 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 ModeAutomatic archival           EnabledArchive destination           +DGRECOVERY/arcOldest online log sequence     9Next log sequence to archive   14Current log sequence           14

切换grid用户查询ASM硬盘组情况：

[oracle@udevasm ~]$ su - gridPassword: [grid@udevasm:/home/grid]$asmcmdASMCMD> lsDGDATA01/DGDATA02/DGRECOVERY/DGSYSTEM/GRID1/ASMCMD> lsdgState    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  NameMOUNTED  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> lsdgState    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  NameMOUNTED  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 +DGRECOVERYASMCMD> lsUDEVASM/ASMCMD> cd UDEVASM/ASMCMD> lsARCHIVELOG/ASMCMD> cd ARCHIVELOG/ASMCMD> ls2017_06_08/ASMCMD> cd 2017_06_08/ASMCMD> lsthread_1_seq_14.256.946154499thread_1_seq_15.257.946154499thread_1_seq_16.258.946154499thread_1_seq_17.259.946154499thread_1_seq_18.260.946154501thread_1_seq_19.261.946154523thread_1_seq_20.262.946154571ASMCMD> pwd+DGRECOVERY/UDEVASM/ARCHIVELOG/2017_06_08

可以再次通过日志切换查询文件是否增加

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;System altered.

查询ASM结果：

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

通过上述内容则可以判断日志切换是成功的，而且thread_1_seq_21.263.946154599 这一个文件就是增加的

这样日志切换就成功了