在执行：ALTER SYSTEM SWITCH LOGFILE；时提示修改成功，但是在ASM硬盘组内归档日志空间则没有相应的归档日志文件，则说明日志切换不成功，然后再通过：SQL> ALTER SYSTEM ARCHIVE LOG CURRENT；修改时则提示如下错误：
|
1
2
3
4
|
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'
|
通过查询相关日志说明，则可以是闪回空间不足导致的，查询闪回空间大小
|
1
2
3
4
5
6
|
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
|
发现闪回空间没有设置大小导致无法进行日志切换，修改闪回空间为3G大小
|
1
|
SQL> alter system
set
db_recovery_file_dest_size=3G scope=both;
|
再次查询闪回空间大小：
|
1
2
3
4
5
|
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 3G
|
通过查询已经有闪回空间了，现在则需要测试一下切换一下日志是否成功
|
1
2
|
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
|
|
1
2
3
4
5
6
7
|
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时还是无法切换成功，想到在修改闪回空间大小时没有把数据库重启一下，最好重启一下数据库看看是否成功
|
1
2
3
4
|
SQL>
shutdown
immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
|
|
1
2
3
4
5
6
7
8
9
|
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.
|
启动后再执行一下日志切换：
|
1
2
3
4
5
6
|
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
|
发现执行成功了，现在进ASM中查看是否有归档日志，在查询之前要查询一下归档存储在哪个位置
|
1
2
3
4
5
6
7
|
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硬盘组情况：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[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/
|
切换后的数据为：
|
1
2
3
4
5
6
7
|
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/
|
查询归档空间是否有文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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
|
可以再次通过日志切换查询文件是否增加
|
1
2
|
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
|
查询ASM结果：
|
1
2
3
4
5
6
7
8
9
|
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 这一个文件就是增加的
这样日志切换就成功了
本文转自xiaocao1314051CTO博客，原文链接： http://blog.51cto.com/xiaocao13140/1933626，如需转载请自行联系原作者
