Maintain RMAN Catalog

Below is a windows BATCH script used to Maintain RMAN catalogue. The script accepts 3 parameters Target SID, Target password and catalogue password.

@echo off
REM
REM NT RMAN catalogue maintenance
REM
REM 3 parameters SID, target password and catalogue password
REM

set ORACLE_SID=%1
set LOG=C:\rmanscripts\maintain_%1.log

REM
REM First generate the rman script using the two supplied parameters
REM

echo connect target rmantarget/%2@%1 >C:\rmanscripts\maintain_catalog.rman
echo connect catalog rman/%3@recovery_catalog >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for maintenance type “sbt_tape” >>C:\rmanscripts\maintain_catalog.rman
echo crosscheck backup of database; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for maintenance type “sbt_tape” >>C:\rmanscripts\maintain_catalog.rman
echo crosscheck backup of archivelog all; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for delete type “sbt_tape”; >>C:\rmanscripts\maintain_catalog.rman
echo delete noprompt expired backup; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo exit >>C:\rmanscripts\maintain_catalog.rman

REM
REM Second generate the email call script
REM

echo connect rman/%3@recovery_catalog >C:\rmanscripts\send_email.sql
echo define subject=^&1 >>C:\rmanscripts\send_email.sql
echo define sender=^&2 >>C:\rmanscripts\send_email.sql
echo execute send_email_header(‘^&subject’,’^&sender’); >>C:\rmanscripts\send_email.sql
echo exit >>C:\rmanscripts\send_email.sql

date /t> %LOG%
time /t >> %LOG%
For /F “tokens=1,2,3,4* delims=/, ” %%i in (‘date/T’) Do SET DDMMYYYY=%%i-%%j-%%k

rman msglog=%LOG% cmdfile=C:\rmanscripts\maintain_catalog.rman

if not errorlevel 1 (
sqlplus -s /nolog @C:\rmanscripts\send_email.sql \”‘RMAN catalog maintenance complete on %DDMMYYYY%’\” %COMPUTERNAME%
) else (
sqlplus -s /nolog @C:\rmanscripts\send_email.sql \”‘RMAN catalog maintenance failure on %DDMMYYYY%’\” %COMPUTERNAME%
)
exit

Please compile send_email_header pl/sql procedure as rman user.

CREATE OR REPLACE PROCEDURE send_email_header(
msg_subject varchar2 ,
msg_sendner varchar2 )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection(‘127.0.0.1’, 25); — open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘HELO localhost’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘MAIL FROM: ‘||msg_sender);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘RCPT TO: ‘||’dba@oracledbasupport.co.uk’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘QUIT’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); — Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, ‘Unable to send e-mail message from pl/sql because of: ‘||
sqlerrm);
END;
/
show errors