archive log full ora-00257

############# sample 0

asmcmd show free 37G in archive_log

ASMCMD> lsdg

State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

MOUNTED EXTERN N N 512 4096 1048576 204800 37410 0 57410 0 ARCHIVE_LOG/

check asm usage:

ort ORACLE_SID=+ASM1

ARCHIVE_LOG/

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

set linesize 240

col g_n format 999

col f_n format 9999

col bytes format 999,999,999,999

col space format 999,999,999,999

col a_i format 999

col blocks format 999,999

col block_size format 999,999

col ftype format a16

col dir format a3

col s_created format a10

col full_alias_path format a60

col striped format a6

col rdun format a6

col au format 99999999

col calculated format 999,999,999,999

select x.gnum g_n,

x.filnum f_n,

substr(x.full_alias_path,1, 60) full_alias_path,

f.striped,

f.redundancy rdun,

f.bytes,

f.space,

case when calculated / x.au > 60 then calculated + 3 * x.au -- Over 60 direct extents consideration

else calculated

end calculated

from ( SELECT gnum,filnum,au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path

from ( SELECT g.name gname,

g.allocation_unit_size au,

a.parent_index pindex,

a.name aname,

a.reference_index rindex,

a.group_number gnum,

a.file_number filnum

FROM v$asm_alias a, v$asm_diskgroup g

WHERE a.group_number = g.group_number)

START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,

(select f.group_number gnum,

f.file_number filnum,

f.type ftype ,

f.blocks,

f.block_size,

f.redundancy,

case f.striped when 'FINE'

then ceil(((f.blocks * f.block_size + 1) / g.allocation_unit_size) / 8)

* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * 8 * g.allocation_unit_size

else ceil((f.blocks * f.block_size + 1) / g.allocation_unit_size)

* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * g.allocation_unit_size

from v$asm_file f , v$asm_diskgroup g

where f.group_number = g.group_number

and g.name='ARCHIVE_LOG'

order by f.group_number,file_number) f

where x.filnum != 4294967295

and x.gnum=f.gnum and x.filnum=f.filnum

order by full_alias_path

/

sulution:

catalog start with "+archive_log/RCFRONT/ARCHIVELOG"

crosscheck archivelog all;

delete archivelog until time 'sysdate -30';

############

 1.

C:\Users\Administrator>rman target  "sys/test123@test"

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 3 11:26:55 2017

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

connected to target database: IVRSPROD (DBID=3799003095)

RMAN>

RMAN> exit

delete noprompt expired archivelog all;

delete noprompt obsolete recovery window of 7 days;

delete noprompt archivelog until time 'sysdate - 7';

Recovery Manager complete.

2.

Wed Sep 06 15:13:42 2017

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance edruat - Archival Error

ORA-16014: log 2 sequence# 14 not archived, no available destinations

ORA-00312: online log 2 thread 1: '/edruatdb/data/log2a.rdo'

ORA-00312: online log 2 thread 1: '/edruatdb/data/log2b.rdo'

Wed Sep 06 15:17:27 2017

Incremental checkpoint up to RBA [0x13.62847.0], current log tail at RBA [0x13.62847.0]

SQL> alter system switch logfile;

System altered.

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

3.

碰到一個問題specification does not match any foreign archived log in the repository

原因如下:

因為控制檔案最多存儲100個歸檔日志,如果之前,沒有部署好清理歸檔的任務的話,導緻舊的歸檔日志無法被删除.比如2個月之前的歸檔日志就無法被删除.這樣導緻目錄消耗過大.

檢查方法：

list archivelog 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\V10203\ARCHIVELOG\2007_11_13\O1_MF_1_6_3MM4NR1W_.ARC';

list archivelog all;

詳細檢視RMAN-20242 failure at RMAN Backup Archivelog - possible Cause and Solution (文檔 ID 1634498.1)

解決方法

方法1:

腳本裡加入

catalog  start with 'C:\APP\ORACLE\ARCHIVELOG' noprompt ;

方法2:

及時備份腳本,定時任務清理歸檔.

1/ windows（如果沒有使用管理者使用者建立定時任務，那麼需要在 定時任務 屬性裡，選擇 安全選項，選擇最高權限 執行 ），確定定時任務可以在windows 下正常運作。

2.建議使用administrator 建立定時任務，建立資料庫，分c,d 盤。

sapmle ： 

[oracle@db 2018_01_08]$ crontab -l

* 12 * * * sh /tmp/cron/delete_archive.sh > /tmp/cron/delete_archive.log

more /tmp/cron/delete_archive.sh

#!/bin/bash

source /home/oracle/profile_vats

rman target / <<eof

delete noprompt archivelog all;

eof

chmod 777 /tmp/cron/delete_archive.sh

炊煙起了；夕陽下了；細雨來了

多調試,互動式程式設計體驗

記錄，獨立思考，對比

感謝轉載作者

修車

國産化

read and connect

匍匐前進,

講故事