About

Martin Klier

usn-it.de

Oracle 12c RMAN delete obsolete behaves interactively in DBMS_SCHEDULER job type BACKUP_SCRIPT

Situation

In Oracle Database 12c, we have the long-missed feature of DBMS_SCHEDULER job type “BACKUP_SCRIPT”, that allows us to create backup jobs without creating them as an OS file, and without need for a wrapper script that’s called by a job type EXECUTABLE (as we usually did that in the last years).

Sometimes we also have to use the RMAN command “delete obsolete” to get rid of unneeded backup pieces. When executed interactively, it comes back with the question “Do you really want to delete the above objects (enter YES or NO)?”. But when running from a script, it just deletes:

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           67     30-JUN-15 
Backup Piece         67     30-JUN-15          +ORAFRA/...nnndf...
deleted backup piece
backup piece handle=+ORAFRA/...nnndf0_tag...
Deleted 1 object
Recovery Manager complete.

Problem

Strange is, that when using it within a DBMS_SCHEDULER job type BACKUP_SCRIPT, “delete obsolete” thinks it was running interactively, and the scheduler seems to compensate it, by simply answering “NO” – what leaves us with a growing number of old backup pieces!
(Taken from v$rman_output):

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           61     30-JUN-15
Backup Piece         61     30-JUN-15          +ORAFRA/.../nnndf0_tag....
RMAN-06546 Error occurred getting response - assuming NO response

Solution

Well, the “solution” is not complicated, we simply have to know that we are not in a script and use “interactive” syntax for the job definition:

delete noprompt obsolete;

Is it a bug or a feature…? At least, good to know.

Take care
Martin Klier

My test case

RMAN script file

This is how we classically invoke RMAN backup scripts as command files, and when “delete obsolete” knows that it has no chance to prompt for a user without the “noprompt” keyword given.

$ rman cmdfile=backup.rman
connect target /
run {
 backup as compressed backupset database;
 backup as compressed backupset archivelog all delete all input;
 backup as backupset current controlfile;
 report obsolete;
 delete obsolete;
}

RMAN_SCRIPT in scheduler

This is the “broken” version, lacking “noprompt”, that does not delete obsolete backup pieces. And, please bear in mind that you will need a basic credential to create this job. You may want to use DBMS_CREDENTIAL.create_credential() for that.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."MY_DB_BACKUP"',
job_type => 'BACKUP_SCRIPT',
job_action => 'connect target /
run {
 backup as compressed backupset database;
 backup as compressed backupset archivelog all delete all input;
 backup as backupset current controlfile;
 report obsolete;
 delete obsolete;
}',
repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'Europe/London',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Performing Databases Backup Job (KLM)',
auto_drop => FALSE,
credential_name => 'BACKUP_CRED',
enabled => TRUE);
END;
/
DOAG Database Conference 2015 begins #DOAGDB15
How to create an Oracle AWR report with SQL and PL/SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.