时间：2021-07-01 10:21:17 帮助过：28人阅读
##注：空间大把的有
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE .04 0 1
REDO LOG .33 0 4
ARCHIVED LOG 0 0 0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
发现ARCHIVELOG、BACKUPPIRCR都不大，就是0，没什么占用率，这样FLASH_RECOVERY_AREA空间的空间还大把的有。
因为之前报错时，不知道如何处理问题，就像直接shutdown immediate一个节点看看，没想到半天没任何反应，就直接使用shutdown abort强行关闭,该节点出现下列节点挂载实例报问题
d.故障节点挂载实例报错：
SQL> startup
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2420
Session ID: 1 Serial number: 5
e.查找资料发现，下面做法可行：
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
发现在打开数据文件报错
SQL> recover database until time ‘2017-05-09‘
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: ‘+DATADG/fmall/datafile/system.259.907327891‘
用覆盖形式恢复也报错
f.后面有资料提出可使用RMAN操作系统删掉归档来解决问题
rman下crosscheck然后delete。
[oracle@~ ]rman target sys/pass
RMAN > crosscheck archivelog all;
validation succeeded for archived log
archived log file name=+ARCH/fmall/archivelog/2017_03_08/thread_2_seq_6362.21528.938070989 RECID=43022 STAMP=938070990
validation succeeded for archived log
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID: 14578
Session ID: 235 Serial number: 2647
校验日志的可用性，报错或者等待很久没有结果出来
RMAN > delete archivelog all completed before ‘sysdate-30‘;
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
如果你删除三十天前的归档日志，等待很久却没有结果，甚至像上面一样报错可以参考我下面的做法
RMAN > delete force noprompt archivelog until time ‘sysdate-30‘;
强制性删除三十天前的归档日志
网站异常告警解除，说明归档日志腾出空间，致使数据库可正常提供应用连接。
g.实例证明，归档日志空间已满导致应用无法连接数据库
有经验的DBA告诉我说，RAC会有一个ASM的磁盘空间议题，我经过查询ASM磁盘空间发现，我的妈呀，ARCH的空间就才腾出282M。
SQL> select name,total_mb,free_mb from v$asm_disk;
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
GRIDDG_0001 4768 4585
ARCH_0000 944137 282
DATADG_0000 1238390 1202771
GRIDDG_0000 4768 4553
BACKUP_0000 953675 949001
再次进入RMAN，使用delete archivelog的方式再删除15天后，查看ASM磁盘空间大小：
SQL> select name,total_mb,free_mb from v$asm_disk;
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
GRIDDG_0001 4768 4585
ARCH_0000 944137 716045
DATADG_0000 1238390 1202771
GRIDDG_0000 4768 4553
BACKUP_0000 953675 949001
空出了将近700多G的空间，太可怕了，短短十五天就能够用掉将近七百多G的归档日志空间，我的数据库大小才1.1G，归档日志就一天以十几二十G的速度疯长。一定是哪里出了问题，必须彻查。
当然首先要做的估计就是把清理归档日志命令写成脚本纳入crontab里，按一定时间进行处理。初步怀疑是应用程式sql语句中存在类似于死循环的东西，导致归档日志如此庞大，后续有什么发现，我将再次记录下来。
致此，归档日志空间已满议题是暂时解决了。
本文出自 “10793382” 博客，请务必保留此出处http://10803382.blog.51cto.com/10793382/1924973
ORACLE 11G DB RAC ORA-00257archiver error解决办法
标签：orcale11g rac ora00257 处理asm磁盘空间不足问题