ORA-00257 歸檔程式錯誤

ORA-00257:歸檔程式錯誤。在釋放之前僅限于内部連接配接

診斷過程：

$sqlplus "/as sysdba"

SQL*Plus: Release10.2.0.1.0 - Production on Mon Jan 17 10:00:24 2011

Copyright (c) 1982, 20​05​, Ora​cle. All rights reserved.

Connected to:

Oracle Database10​gRelease10.2.0.1.0 - 64bit Production

檢視正在歸檔的狀态

SQL>select * from v$log;

GROUP#   THREAD# SEQUENCE#     BYTES   MEMBERS ARCHIV

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

STATUS                          FIRST_CHANGE# FIRST_TIME

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

        1         1      4703  52428800         1NO

INACTIVE                            370297526 15-JAN-11

        2         1      4704  52428800         1NO

INACTIVE                            370421816 16-JAN-11

        3         1      4705  52428800         1NO

CURRENT                             370577854 17-JAN-11

發現ARC狀态為NO，表示系統沒法自動做歸檔。

檢視歸檔日志所在位置

SQL>show parameter recover;

NAME                                TYPE       VALUE

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

db_recovery_file_dest               string     /oracle/flash_recovery_area

db_recovery_file_dest_size          big integer10G

recovery_parallelism                integer    0

SQL> exit

Disconnected from Oracle Database10gRelease10.2.0.1.0 - 64bi

$ exit

# cd

# ls

.TTauthority          cdrom                 proc

.Xauthority           configassist.log      sbin

.dt                   dev                   smit.log

.dtprofile            etc                   smit.script

.java                 home                  smit.transaction

.mh_profile           lib                   sqlnet.log

.sh_history           lost+found            tftpboot

.vi_history           lpp                   tmp

.wmrc                 mnt                   u

Mail                  nmo42_aix5L_64.tar.gz unix

TT_DB                 nsr                   usr

audit                 opt                   var

bin                   oradata

# cd oradata

flash_recovery_area misdata

lost+found          orabak

# cd flash_recovery_area

MISDATA

# cd  MISDATA

archivelog autobackup onlinelog

# cd  archivelog

2011_01_01 2011_01_04 2011_01_07 2011_01_10 2011_01_13 201

2011_01_02 2011_01_05 2011_01_08 2011_01_11 2011_01_14 201

2011_01_03 2011_01_06 2011_01_09 2011_01_12 2011_01_15 202

#

5）檢視FLASH_RECOVERY_AREA空間使用情況

# du -a -k flash_recovery_area

0      flash_recovery_area/MISDATA/onlinelog

0      flash_recovery_area/MISDATA/archivelog/2020_06_02

50560  flash_recovery_area/MISDATA/archivelog/2011_01_17/o1_mf

.arc

49396  flash_recovery_area/MISDATA/archivelog/2011_01_17/o1_mf

49400  flash_recovery_area/MISDATA/archivelog/2011_01_17/o1_mf

149356 flash_recovery_area/MISDATA/archivelog/2011_01_17

0      flash_recovery_area/MISDATA/archivelog/2011_01_16

49400  flash_recovery_area/MISDATA/archivelog/2011_01_01/o1_mf

49396  flash_recovery_area/MISDATA/archivelog/2011_01_15/o1_mf

49396  flash_recovery_area/MISDATA/archivelog/2011_01_15

2224140 flash_recovery_area/MISDATA/archivelog

0      flash_recovery_area/MISDATA/autobackup

2224140 flash_recovery_area/MISDATA

2224140 flash_recovery_area

檢視flash recovery area已經占用的空間

SQL>select * from v$flash_recovery_area_usage

 2 /

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

CONTROLFILE                              0                        0

ONLINELOG                                0                        0

ARCHIVELOG                           97.6                        0

             2445

BACKUPPIECE                              0                        0

IMAGECOPY                                0                        0

FLASHBACKLOG                             0                        0

6 rows selected.

轉移或清除對應的歸檔日志,删除一些不用的日期目錄的檔案，注意保留最後幾個檔案

注意:在删除歸檔日志後必須用​​RMAN​​維護控制檔案，否則空間顯示仍然不釋放。

在rman裡删除過期日志

$rman target /

Recovery Manager: Release11.2.0.1.0 - Production on Wed Feb 24 08:13:00 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ****

RMAN>crosscheck archivelog all;                     檢查一些無用的archivelog

RMAN>delete expired archivelog all;                 删除過期archivelog

增大閃回區大小

SQL>alter system set db_recovery_file_dest_size=80Gscope=both;

SQL>select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE           PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

CONTROL FILE                         0                        0

REDO LOG                             0                        0

ARCHIVED LOG                      3.26                         0

            17

BACKUP PIECE                         0                      0

IMAGE COPY                           0                        0

FLASHBACK LOG                        0                        0

FOREIGN ARCHIVED LOG   

檢視歸檔日志清單