Database Software

Hi,

i want to delete all old archived log with a pl/sql package.

My database run as archivelog without catalog. The backupsets and archived logs register on the controlfile.

Database: Oracle 10gR2
Version: 10.2.0.1.0

PROCEDURE delete_archivelog
IS
v_validated BINARY_INTEGER;

CURSOR v_resetlog
IS
SELECT recid, stamp, NAME, thread#, sequence#, resetlogs_change#,
first_change#, blocks
FROM v$archived_log
WHERE deleted = 'NO'
AND completion_time < SYSDATE - 5 / (24 60)*
ORDER BY recid ASC;
BEGIN
FOR rec_resetlog IN v_resetlog
LOOP
v_validated :=
DBMS_BACKUP_RESTORE.validatearchivedlog
*(recid => rec_resetlog.recid,*
stamp => rec_resetlog.stamp,
fname => rec_resetlog.NAME,
THREAD => rec_resetlog.thread#,
SEQUENCE => rec_resetlog.sequence#,
resetlogs_change => rec_resetlog.resetlogs_change#,
first_change => rec_resetlog.first_change#,
blksize => rec_resetlog.blocks
*);*

IF v_validated = 0
THEN
BEGIN
DBMS_BACKUP_RESTORE.deletearchivedlog
*(recid => rec_resetlog.recid,*
stamp => rec_resetlog.stamp,
fname => rec_resetlog.NAME,
THREAD => rec_resetlog.thread#,
SEQUENCE => rec_resetlog.sequence#,
resetlogs_change => rec_resetlog.resetlogs_change#,
first_change => rec_resetlog.first_change#,
blksize => rec_resetlog.blocks
*);*
DBMS_OUTPUT.put_line ( rec_resetlog.NAME
*|| ' - '*
*|| rec_resetlog.recid*
*);*
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR ( 'Error '
*|| TO_CHAR (SQLCODE)*
*|| ': '*
*|| SQLERRM,*
*1,*
*255*
*)*
*);*
END;
END IF;

DBMS_OUTPUT.put_line (rec_resetlog.NAME || ' - ' || v_validated);
END LOOP;
END;

But the v_validated is always 6. It must be 0.

Without dbms_backup_restore.validatearchivedlog throw a exception.
ORA-19633: controlfile record is out of sync with recovery catalog

But recid is on the controlfile.

What is the wrong?

Thanks,
Micsigen

Edited by: user990822 on 2009.11.29. 17:58