Oracle归档日志满问题处理

合集下载

相关主题

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle11g ORA-00257归档日志满问题处理

数据库报错：archive log 日志已满，数据库不能连接

ORA-00257: archiver error. Connect internal only, until freed 错误的处理方法

$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

查看flash recovery area已经占用的空间

SQL> select sum(percent_space_used)*3/100 from

v$flash_recovery_area_usage;

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 96.15 0

50

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

7 rows selected.

查看归档日志所在位置

SQL> show parameter recover;

NAME TYPE VALUE

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

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

db_recovery_file_dest string /oracle/flash_recove ry_area

db_recovery_file_dest_size big integer 10G

recovery_parallelism integer 0

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

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

$ rman target sys/口令

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=20G 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 0

REDO LOG 0 0 0

ARCHIVED LOG 38.52 0 17

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------------- ------------------ ------------------------- NUMBER_OF_FILES

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

BACKUP PIECE 0 0 0

IMAGE COPY 0 0 0

FLASHBACK LOG 0 0 0

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------------- ------------------ ------------------------- NUMBER_OF_FILES

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

FOREIGN ARCHIVED LOG