Oracle归档日志过多导致磁盘空间不足问题处理-工具盒子

概述

由于Oracle归档日志过多导致系统磁盘空间不足，从而导致无法连接Oracle数据库，执行sql语句报错。

ORA-00257: archiver error. Connect internal only, until freed.

SP2-0306: Invalid option.
Usage: CONN\[ECT\] \[{logon\|/\|proxy} \[AS {SYSDBA\|SYSOPER\|SYSASM}\] \[edition=value\]\]
where \<logon\> ::= \<username\>\[/\<password\>\]\[@\<connect_identifier\>\]
\<proxy\> ::= \<proxyuser\>\[\<username\>\]\[/\<password\>\]\[@\<connect_identifier\>\]
Enter password:
ERROR:
ORA-01005: null password given; logon denied

`SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus`

解决办法

查看Free_MB空间

[oracle@db1 ~]$ su - grid
Password: grid
[grid@db1 ~]$ asmcmd
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
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  1048576   4095944   321707                0          321707              0             N  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576    819188   819085                0          819085              0             N  INDEX1/
MOUNTED  NORMAL  N         512   4096  1048576     15345    14419             5115            4652              0             Y  OCRVOTE/```

删除Free_MB空间

打开控制文件

[oracle@db1 ~]$ sqlplus "/as sysdba"
  startup mount

删除归档

删除一天前的归档

[oracle@db1 ~]$ rman target /
RMAN> delete noprompt archivelog all completed before 'sysdate-1';

修改数据库状态为打开

[oracle@db1 ~]$ sqlplus / as sysdba
SQL>  alter database open;

其他

查看归档日志信息

Oracle归档空间日志文件位置根据Archive destination判断。

[oracle@db2 ~]$  sqlplus / as sysdba
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/11.2.0.3/dbs/arch
Oldest online log sequence     976392
Next log sequence to archive   976393
Current log sequence           976393