ora-00257错误处理

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5762451.html

这个假期忙于生活事务，搁下了，数据库也很争气，出了一点小问题把记录下
客户的第三方打电话说业务已经处于停滞状态，也无法登陆数据库，报错信息如下：

由错误看，是归档的问题，于是检查了下数据库的asm磁盘组空间。

DG_NAME TYPE TOTAL_MB    FREE_MB free(%) 
--------------- ------ ---------- ---------- -------- -- -------
DATADG EXTERN  2764761    1022514 37.1 

空间还有的是啊(1个T呢)
于是检查了下数据库

看来还是归档的问题，那就看看alert日志，客户发过来的日志如下：
Sun Apr 02 20:40:03 2017
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 51500 for destination LOG_ARCHIVE_DEST_3
Sun Apr 02 20:40:03 2017
Errors in file /oracle/app/oracle/diag/rdbms/tcdb/TCDB1/trace/TCDB1_arc0_40024.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes is 95.75% used, and has 9116319744 remaining bytes available.
************************************************************************
You have following choices to free up space from 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 as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+DATADG'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance PLDB1 - Archival Error
ORA-16038: log 2 sequence# 51499 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '+DATADG/tcdbdg/onlinelog/group_2.311.871466183'
ORA-00312: online log 2 thread 1: '+DATADG/tcdbdg/onlinelog/group_2.312.871466183

这样错误就明显了。检查如下
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     48549
Next log sequence to archive   48554
Current log sequence       48554

SQL> show parameter recover

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest           string +DATADG
db_recovery_file_dest_size     big integer 200G

检查flash_recovery_area的使用情况
SQL> /
SUM(PERCENT_SPACE_USED)
--------------------
95.81

ASMCMD [+datadg/tcdb/archivelog] > du
Used_MB      Mirror_used_MB
  19680               19680

首先：
1.修改flash_recovery_area可用大小
SQL> alter system set db_recovery_file_dest_size=300g scope=both;  
System altered.

2.删除部分归档日志
delete archivelog until time 'sysdate-1/2' 

至此问题处理完毕
--The end