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

1，plsq登录报错

ORA-00257: archiver error. Connect internalonly. until freed

 

2，后台alert日志报错：

[oracle@powerlong4 ~]$  tail -fn 200/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/alert_powerdes.log

Tue Apr 28 10:00:56 2015

Errors in file/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc9_30051.trc:

ORA-19815: WARNING:db_recovery_file_dest_size of 19327352832 bytes is 100.00% used, and has 0remaining bytes available.

************************************************************************

You have following choices to free up spacefrom recovery area:

1. Consider changing RMAN RETENTION POLICY.If you are using Data Guard,

  then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such astape using RMAN

  BACKUP RECOVERY AREA command.

3. Add disk space and increasedb_recovery_file_dest_size parameter to

  reflect the new space.

4. Delete unnecessary files using RMANDELETE command. If an operating

  system command was used to delete files, then use RMAN CROSSCHECK and

  DELETE EXPIRED commands.

************************************************************************

Errors in file/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc9_30051.trc:

ORA-19809: limit exceeded for recoveryfiles

ORA-19804: cannot reclaim 31602688 bytesdisk space from 19327352832 limit

ARC9: Error 19809 Creating archive log fileto'/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_28/o1_mf_1_75_%u_.arc'

 

 

----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址： http://blog.youkuaiyun.com/mchdba/article/details/45332505
原作者：黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

 

3，查看归档日志

RMAN> crosscheck archivelog all; --先检查下

...

validation succeeded for archived log

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_72_bmkbwfrg_.arcRECID=63633 STAMP=877796493

validation succeeded for archived log

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_73_bmkyqplx_.arcRECID=63634 STAMP=877816822

validation succeeded for archived log

archived log file name=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_74_bmkysysc_.arcRECID=63635 STAMP=877816895

Crosschecked 1635 objects

 

看到有很多归档日志，么有应用到sendary库上面。

 

 

 

4，这里采用删除多余的归档日志的办法来操作，查看过期归档日志

[oracle@powerlong4 ~]$ rman target /

 

RMAN> delete expired archivelog all;

 

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=787 devicetype=DISK

specification does not match any archivedlog in the repository

 

RMAN>

 

5，删除过期的归档日志

delete expired archivelog all;

RMAN> delete expired archivelog all;

 

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=787 devicetype=DISK

specification does not match any archivedlog in the repository

 

RMAN>

看到没有过期的归档日志

 

6，看到没有过期的，不够我们可以删除指定到具体的时间，这样也可以腾出空间来

RMAN>delete archivelog until time"to_date('2015-04-27 13:00:00','yyyy-mm-dd hh24:mi:ss')";

 

部分执行过程如下：

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_70_bmjfsm48_.arcthread=1 sequence=70

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_71_bmjk1mvp_.arcthread=1 sequence=71

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_72_bmkbwfrg_.arcthread=1 sequence=72

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_73_bmkyqplx_.arcthread=1 sequence=73

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

archived log filename=/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_74_bmkysysc_.arcthread=1 sequence=74

 

RMAN>

 

select * from V$FLASH_RECOVERY_AREA_USAGE;

 

7，再去查看下使用空间

SQL> select * fromV$FLASH_RECOVERY_AREA_USAGE;

 

FILE_TYPE              PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

-------------------- -------------------------------------------

NUMBER_OF_FILES

---------------

CONTROL FILE                         0                             0

               0

 

REDO LOG                                 0                             0

               0

 

ARCHIVED LOG                            74.36                     0

            1635

 

 

FILE_TYPE              PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

-------------------- -------------------------------------------

NUMBER_OF_FILES

---------------

BACKUP PIECE                              25.5                     0

               7

 

IMAGE COPY                            0                             0

               0

 

FLASHBACK LOG                              0                             0

               0

 

 

FILE_TYPE              PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

-------------------- -------------------------------------------

NUMBER_OF_FILES

---------------

FOREIGN ARCHIVED LOG                        0                             0

               0

 

 

7 rows selected.

 

SQL>

 

OK，看到归档日志空间使用74%，还有足够的空间，所以可以登录使用。

重新登录plsql，还是报一样的错误。

 

8，重新启动oracle服务，这样就可以了

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

 

Total System Global Area 3373858816 bytes

Fixed Size              2218032 bytes

Variable Size              1694500816 bytes

Database Buffers    1660944384 bytes

Redo Buffers                16195584 bytes

Database mounted.

Database opened.

SQL>

 

9，然后使用plsql登录，现在可以正常登录了。

 

总结，是因为oracle的dataguard中断了，所以primary上的归档日志一直积累下来，造成归档的空间满了，所以数据库无法远程登录了，这个时候采用的常用办法可以有以下2个：1）：根据时间点删除过期的归档日志；2）增大db_recovery_file_dest_size的值。