ORA-00257

用户无法登陆系统,查看日志文件显示下面错误,闪回区满

 [2009-11-03 16:17:11][WARN] SQL Error: 257, SQLState: 64000

[2009-11-03 16:17:11][ERROR] ORA-00257: archiver error. Connect internal only, until freed.

 

SQL> show parameter log_archive_dest;

 

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string

log_archive_dest_10                  string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_6                   string

log_archive_dest_7                   string

log_archive_dest_8                   string

log_archive_dest_9                   string

 

NAME                                 TYPE        VALUE

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

log_archive_dest_state_1             string      enable

log_archive_dest_state_10            string      enable

log_archive_dest_state_2             string      enable

log_archive_dest_state_3             string      enable

log_archive_dest_state_4             string      enable

log_archive_dest_state_5             string      enable

log_archive_dest_state_6             string      enable

log_archive_dest_state_7             string      enable

log_archive_dest_state_8             string      enable

log_archive_dest_state_9             string      enable

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     280

Next log sequence to archive   280

Current log sequence           282

 

占用率达到99.95%

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                99.95                         0             247

BACKUPPIECE                   0                         0               0

IMAGECOPY                     0                         0               0

FLASHBACKLOG                  0                         0               0

 

 

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

 

SUM(PERCENT_SPACE_USED)*3/100

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

                       2.9985

 

SQL> show parameter recov

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /opt/oracle/flash_recovery_are

                                                 a

db_recovery_file_dest_size           big integer 10G

recovery_parallelism                 integer     0

 

解决方法有两种办法

方法一:增大闪回区.

SQL> alter system set db_recovery_file_dest_size=20G;

 

System altered.

 

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                 50.6                         0             250

BACKUPPIECE                   0                         0               0

IMAGECOPY                     0                         0               0

FLASHBACKLOG                  0                         0               0

 

6 rows selected.

 

SQL> show parameter rec

 

NAME                                 TYPE        VALUE

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

buffer_pool_recycle                  string

control_file_record_keep_time        integer     7

db_recovery_file_dest                string      /opt/oracle/flash_recovery_are

                                                 a

db_recovery_file_dest_size           big integer 20G

 

方法二:参考[主要还是按照这种方式来做.

http://cache.baidu.com/c?m=9f65cb4a8c8507ed4fece7631046893b4c4380146d96864968d4e414c422461b0526beea3a715055899e38301caf4b5ee1ed35702a002baa9dcc834ed7b1993f2fff7475325692044f934fa0960676d621e04de9d90ee6caf23093a9d7&p=882a9506ca9c5df034be9b7d49&user=baidu

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779391/viewspace-618045/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779391/viewspace-618045/