center.bat --总的调度中心，设置环境变量，调用其他脚本
@echo off
echo ================================================
echo Windows环境下Oracle数据库的自动清理归档脚本
echo 删除已成功应用的备库的归档日志
echo ================================================
::以“YYYYMMDD”格式取出当前时间。
SET BACKUPDATE=%date:~0,4%-%date:~5,2%-%date:~8,2%
SET ORACLE_HOME=E:\app\Administrator\product\11.2.0\dbhome_1
SET ORACLE_SID=mes
SET NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
SET LOG_DIR=E:\rman\del_arch_log
SET LOG_NAME=%LOG_DIR%\del_adg_arc_%ORACLE_SID%_%BACKUPDATE%.log
SET SQL_NAME=%LOG_DIR%\del_adg_arc.sql
sqlplus -s sys/oracle@mes as sysdba @E:\\rman\pro_sql.bat
rman target sys/oracle@mes cmdfile='E:\rman\del_arc.bat' LOG=%LOG_NAME%
pro_sql.bat --生成删除归档的语句到指定目录并保存成SQL脚本，此处是删除所有应用过的归档日志，如需保留几天，修改数字0
set feedback off heading off pagesize 0 linesize 100
col exec_sql format a50
spool %SQL_NAME%
SELECT 'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' || A.THREAD# || ';' EXEC_SQL
FROM V$ARCHIVED_LOG A
WHERE (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
(SELECT b.THREAD#,
b.SEQUENCE#,
b.RESETLOGS_CHANGE#
FROM V$ARCHIVED_LOG B
WHERE B.APPLIED = 'YES'
AND b.COMPLETION_TIME <= SYSDATE - 0)
AND a.NAME NOT IN (SELECT b.DESTINATION
FROM v$archive_dest b
WHERE b.DESTINATION IS NOT NULL)
AND A.COMPLETION_TIME <= SYSDATE - 0
ORDER BY A.THREAD#,
A.SEQUENCE#;
spool off
exit;
del_arc.bat --清理归档日志并调用上段中生成的SQL脚本
crosscheck archivelog all;
delete noprompt expired archivelog all;
@E:\rman\del_arch_log\del_adg_arc.sql
exit;
exit;
Processed:
0.008, SQL:
9
