ORA-00257: 归档程序错误。在释放之前仅限于内部连接-CSDN博客

ORA-00257: 归档程序错误。在释放之前仅限于内部连接

诊断过程：

$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 17 10:00:24 2011

Connected to:

Oracle Database 10g Release 10.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 1 NO

INACTIVE 370297526 15-JAN-11

2 1 4704 52428800 1 NO

INACTIVE 370421816 16-JAN-11

3 1 4705 52428800 1 NO

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 integer 10G

recovery_parallelism integer 0

SQL> exit

查看归档日志列表

Disconnected from Oracle Database 10g Release 10.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

# ls

flash_recovery_area misdata

lost+found orabak

# cd flash_recovery_area

# ls

MISDATA

# cd MISDATA

# ls

archivelog autobackup onlinelog

# cd archivelog

# ls

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空间使用情况

# cd oradata

# ls

flash_recovery_area misdata

lost+found orabak

# 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

.arc

49400 flash_recovery_area/MISDATA/archivelog/2011_01_17/o1_mf

.arc

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

.arc

49396 flash_recovery_area/MISDATA/archivelog/2011_01_15/o1_mf

.arc

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

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

BACKUPPIECE 0 0

IMAGECOPY 0 0

FLASHBACKLOG 0 0

6 rows selected.

转移或清除对应的归档日志, 删除一些不用的日期目录的文件，注意保留最后几个文件

注意: 在删除归档日志后必须用RMAN维护控制文件，否则空间显示仍然不释放。

在rman里删除过期日志

$ rman target yourdbname

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 24 08:13:00 2010

connected to target database: ****

RMAN> crosscheck archivelog all; 检查一些无用的archivelog

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

增大闪回区大小

SQL> alter system set db_recovery_file_dest_size=80G scope=both;

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

CONTROL FILE 0 0

REDO LOG 0 0

ARCHIVED LOG 3.26 0

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

BACKUP PIECE 0 0

IMAGE COPY 0 0

FLASHBACK LOG 0 0

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

FOREIGN ARCHIVED LOG

按照上边的方法便可解决 ORA-00257 归档程序错误；