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;
/