ORA-00257归档日志导致空间不足

SQL> set linesize 140
SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
   ---------   ---------- ---------- ----------            ----------               ---         -----  ----------- ----
         1          1                              489            52428800          1          NO  INACTIVE
         2          1                              491            52428800          1          NO  INACTIVE
         3          1                              492            52428800          1          NO  CURRENT
         4          1                              490            52428800          1          NO  INACTIVE

SQL> alter system switch logfile;

alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

查看ALERT.LOG发现以下错误：

ORA-19815: ▒▒▒▒: db_recovery_file_dest_size ▒ֽ▒ (▒▒ 2147483648 ▒ֽ▒) ▒▒ʹ▒▒ 100.00%,

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /db/oracle/flash_recovery_area
db_recovery_file_dest_size      big integer 2G

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                   100                      0              54
BACKUPPIECE                  0                         0               0
IMAGECOPY                       0                         0               0
FLASHBACKLOG              0                         0               0

6 rows selected.

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ----                                              --------- ------------
         1          1        489   52428800          1 YES INACTIVE                                                 6516997 21-JUN-10
         2          1        491   52428800          1 YES INACTIVE                                                 6563207 22-JUN-10
         3          1        492   52428800          1 YES ACTIVE                                                     6596382 22-JUN-10
         4          1        493   52428800          1 NO  CURRENT                                                6631490 23-JUN-10

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                43.59                      0              54
BACKUPPIECE                  0                         0               0
IMAGECOPY                       0                         0               0
FLASHBACKLOG              0                         0               0

6 rows selected.

 

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

转载于:http://blog.itpub.net/21158541/viewspace-665949/