[size=large][size=large][/size][size=medium]今天在自己机器做了个实验,插入10万条,由于空间少,重启数据库时出现:
[size=x-large][/size]SQL> startup
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1218460 bytes
Variable Size 167774308 bytes
Database Buffers 16777216 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-16038: log 3 sequence# 103 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 3 thread 1: '/home/lc_orauser/oradata/niutest/redo03.log'
后来发现是 闪回区的空间被全部占用
select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 104 NO INACTIVE
3 103 NO INACTIVE
2 105 NO CURRENT
--1、清空闪回区空间,根据查询视图v$log可知,当前活动日志为2号日志组,则此时需要清空3号日志组的,
alter database clear unarchived logfile group 3;
然后再
alter database open;
解决了。
--2、增大db_recovery_file_dest_size的值
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:/oracle/product/10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
系统已更改。
SQL> alter database open;
数据库已更改。
为什么会出现这种情况呢?
(1).检查flash recovery area的使用情况:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 6.36 0 4
BACKUPPIECE .22 0 1
IMAGECOPY 63.68 0 5
FLASHBACKLOG .51 .25 2
已选择6行。
SQL>
(2).计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.1231
可以看到,这里已经有2.1231G使用了,这说明我们刚开始设置的db_recovery_file_dest_size=2G不足,导致online redo log无法归档,在这里,我们通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。
(3).也可以通过删除flash recovery area中不必要的备份来释放flash recovery area空间来解决这个问题:
(1). delete obsolete;
(2). crosscheck backupset;
delete expired backupset;[/size][/size]
[size=x-large][/size]SQL> startup
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1218460 bytes
Variable Size 167774308 bytes
Database Buffers 16777216 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-16038: log 3 sequence# 103 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 3 thread 1: '/home/lc_orauser/oradata/niutest/redo03.log'
后来发现是 闪回区的空间被全部占用
select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 104 NO INACTIVE
3 103 NO INACTIVE
2 105 NO CURRENT
--1、清空闪回区空间,根据查询视图v$log可知,当前活动日志为2号日志组,则此时需要清空3号日志组的,
alter database clear unarchived logfile group 3;
然后再
alter database open;
解决了。
--2、增大db_recovery_file_dest_size的值
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:/oracle/product/10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
系统已更改。
SQL> alter database open;
数据库已更改。
为什么会出现这种情况呢?
(1).检查flash recovery area的使用情况:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 6.36 0 4
BACKUPPIECE .22 0 1
IMAGECOPY 63.68 0 5
FLASHBACKLOG .51 .25 2
已选择6行。
SQL>
(2).计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.1231
可以看到,这里已经有2.1231G使用了,这说明我们刚开始设置的db_recovery_file_dest_size=2G不足,导致online redo log无法归档,在这里,我们通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。
(3).也可以通过删除flash recovery area中不必要的备份来释放flash recovery area空间来解决这个问题:
(1). delete obsolete;
(2). crosscheck backupset;
delete expired backupset;[/size][/size]