ORA-00257: archiver error. Connect internal only

1. 查看是什么错误

[oracle@field ~]$ oerr ora 00257

00257, 00000, "archiver error. Connect internal only, until freed."

// *Cause: The archiver process received an error while trying to archive

// a redo log. If the problem is not resolved soon, the database

// will stop executing transactions. The most likely cause of this

// message is the destination device is out of space to store the

// redo log file.

// *Action: Check archiver trace file for a detailed description

// of the problem. Also verify that the

// device specified in the initialization parameter

// ARCHIVE_LOG_DEST is set up properly for archiving.

[oracle@field ~]$

由此得知应该是归档文件满了，再看一下alert文件

************************************************************************

ARC0: Failed to archive thread 1 sequence 9 (19809)

Wed Dec 1 12:33:27 2010

Errors in file /opt/ora10g/admin/inomc/bdump/inomc_arc1_12217.trc:

ORA-19815: Message 19815 not found; No message file for product=RDBMS, facility=ORA; arguments: [db_recovery_file_dest_size] [1073741824] [100.00] [0]

Wed Dec 1 12:33:27 2010

************************************************************************

You have following choices to free up space from flash recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

***********************************************************************

2. 查看归档文件的位置和大小

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

为空，可以用archive log list检查一下归档目录和log sequence

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 8

Next log sequence to archive 8

Current log sequence 10

SQL>

再看一下

SQL> show parameter db_recovery_file

NAME TYPE VALUE

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

db_recovery_file_dest string /opt/ora10g/flash_recovery_are

a

db_recovery_file_dest_size big integer 1G

SQL>

3. 检查flash recovery area的使用情况

select * from V$FLASH_RECOVERY_AREA_USAGE;

使用该命令后数据库居然一点反应都没有

然后立即想到用rman登陆数据库，使用delete obsolete 和delete expired删除作废与过期的文件。当然在执行delete expired之前先crosscheck backup；

但是令人失望的是

[oracle@field bdump]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 1 12:50:09 2010

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06003: ORACLE error from target database:

ORA-00604: error occurred at recursive SQL level 1

ORA-01013: user requested cancel of current operation

[oracle@field bdump]$

4. 直接进去归档目录删除文件

[oracle@field bdump]$ echo $ORACLE_BASE

/opt/ora10g

然后根据

SQL> show parameter db_recovery_file

NAME TYPE VALUE

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

db_recovery_file_dest string /opt/ora10g/flash_recovery_are

a

db_recovery_file_dest_size big integer 1G

SQL>

进入到flash_recovery_area

[oracle@field INOMC]$ ls

archivelog autobackup backupset

可以看到有三个文件归档日志、自动备份和备份集，分别查看其大小

[oracle@field archivelog]$ du -s -h

289M .

[oracle@field autobackup]$ du -s -h

29M .

[oracle@field backupset]$ du -s -h

690M .

没有办法啊，直接把备份集删掉吧

[oracle@field backupset]$ rm -r *

用rman还是无法登陆

5. 尝试关闭数据库再启动

连正常关闭都没有反应只能shutdown abort

[oracle@field archivelog]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 1 13:09:01 2010

connected to target database (not started)

RMAN>

RMAN> startup

Oracle instance started

database mounted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of startup command at 12/01/2010 13:09:26

ORA-16038: log 2 sequence# 8 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1: '/opt/ora10g/oradata/inomc/redo02.log'

RMAN>

启动的时候又报错

再检查alert文件

Wed Dec 1 13:10:25 2010

Errors in file /opt/ora10g/admin/inomc/bdump/inomc_arc0_15104.trc:

ORA-16014: Message 16014 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [8]

ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [1] [/opt/ora10g/oradata/inomc/redo02.log]

进入到/opt/ora10g/oradata/inomc/该目录下，

[oracle@field inomc]$ ls

control01.ctl control03.ctl redo01.log redo03.log system01.dbf text01-1.dbf text01-3.dbf undotbs01.dbf

control02.ctl INOMC redo02.log sysaux01.dbf temp01.dbf text01-2.dbf text01-4.dbf users01.dbf

日志文件都在

6. 清理日志文件

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1 1 10 52428800 1 NO CURRENT

640633 01-12?-10

3 1 9 52428800 1 NO INACTIVE

630661 01-12?-10

2 1 8 52428800 1 NO INACTIVE

620190 01-12?-10

SQL>

查看到redo02处于INACTIVE状态下

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database open ;

Database altered.

SQL>

好了，数据库终于启动了

7. 在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。同时由于手工删除了备份集，需要重新进行备份，防止数据库崩溃后无法恢复

RMAN> crosscheck backup ;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=130 devtype=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T223218_6ghpzmvx_.bkp recid=16 stamp=735604339

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_11_20/o1_mf_s_735604485_6ghq46dw_.bkp recid=17 stamp=735604486

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_12_01/o1_mf_s_736581887_6hbx8339_.bkp recid=18 stamp=736581891

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_12_01/o1_mf_s_736582120_6hbxhbkl_.bkp recid=19 stamp=736582122

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_12_01/o1_mf_s_736583938_6hbz83n9_.bkp recid=20 stamp=736583939

Crosschecked 5 objects

删除过期和废弃的

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces

BP Key BS Key Pc# Cp# Status Device Type Piece Name

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

16 16 1 1 EXPIRED DISK /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T223218_6ghpzmvx_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T223218_6ghpzmvx_.bkp recid=16 stamp=735604339

Deleted 1 EXPIRED objects

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 7 days

using channel ORA_DISK_1

no obsolete backups found

RMAN>

这里就不备份了

8. 检查空间状态

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

BACKUPPIECE 2.76 0 4

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

6 rows selected.

SQL>

使用率降低到37.73%

2010-12-8 by 饼干

ITPub博客

ORA-00257: archiver error. Connect internal only

最新文章