OraFAQ Forum: Backup & Recovery » Old backups not getting deleted

Home » RDBMS Server » Backup & Recovery » Old backups not getting deleted (Oracle 11g Enterprise Edition , 11.2.0.4 , Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Old backups not getting deleted [message #665990]

Fri, 06 October 2017 00:26

SSharma7985
Messages: 8
Registered: January 2011
Location: India

Junior Member

HI Experts,

We take RMAN backups daily night at around 10 PM EST as per below mentioned script .

NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';export NLS_DATE_FORMAT
ORACLE_SID=cfp106;export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
rman target / log=/util/dba/cfp106/reports/cfp106_rman_hot_backup_`date +%Y%m%d%H%M%S`.log <<EOF
set echo on;
RUN {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/cfp106/cfp106_bk/oradata/cfp106/cfp106_controlfile_backup_%F';
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk7 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
ALLOCATE CHANNEL disk8 DEVICE TYPE DISK FORMAT '/cfp106/cfp106_bk/oradata/cfp106/rman_full_online_backup_%d_set%s_piece%p_copy%c_%T_%U';
sql 'alter system switch logfile';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
crosscheck archivelog all;
delete noprompt expired archivelog all;
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/cfp106/cfp106_bk/oradata/cfp106/cfp106_archivelogs_%d_%u_%s_%T';
delete noprompt archivelog all backed up 2 times to disk;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE DEVICE TYPE TAPE;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 10 DAYS DEVICE TYPE DISK;
BACKUP CURRENT CONTROLFILE FORMAT '/cfp106/cfp106_bk/oradata/cfp106/cfp106_rman_hot_backup_cntrl_%s_%p_%t';
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
RELEASE CHANNEL disk4;
RELEASE CHANNEL disk5;
RELEASE CHANNEL disk6;
RELEASE CHANNEL disk7;
RELEASE CHANNEL disk8;
}
exit
EOF

In morning at around 04 AM EST , we push the backups to tape using below mentioned script .

NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';export NLS_DATE_FORMAT
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1;export ORACLE_HOME
ORACLE_SID=cfp106;export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH;export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
rman target / log=/util/dba/cfp106/reports/cfp106_rman_backups_totape_`date +%Y%m%d%H%M%S`.log <<EOF
set echo on;
run {
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_CFP106_ORA.opt)';
CONFIGURE DEVICE TYPE sbt PARALLELISM 1;
BACKUP DEVICE TYPE sbt BACKUPSET ALL NOT BACKED UP SINCE TIME 'SYSDATE-35/24';
}
.

Issue here is Inspite of running DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 10 DAYS DEVICE TYPE DISK , disk backup pieces are not getting removed . Rman just deletes the control files and does not remove the backup piece saying no obsolete backups found .

On disc i can see older backups .

jptrcolspora04{sasharma}: pwd
/cfp106/cfp106_bk/oradata/cfp106
jptrcolspora04{sasharma}: ls -lrt *20170919*
-rw-r----- 1 oracle dba 754056192 Sep 19 22:06 cfp106_archivelogs_CFP106_tosesmr1_952_20170919
-rw-r----- 1 oracle dba 889228800 Sep 19 22:07 cfp106_archivelogs_CFP106_tpsesmr1_953_20170919
-rw-r----- 1 oracle dba 1155046400 Sep 19 22:07 cfp106_archivelogs_CFP106_tmsesmr1_950_20170919
-rw-r----- 1 oracle dba 963630080 Sep 19 22:07 cfp106_archivelogs_CFP106_tssesmr2_956_20170919
-rw-r----- 1 oracle dba 1018653184 Sep 19 22:07 cfp106_archivelogs_CFP106_tlsesmr1_949_20170919
-rw-r----- 1 oracle dba 1059609600 Sep 19 22:08 cfp106_archivelogs_CFP106_tnsesmr1_951_20170919
-rw-r----- 1 oracle dba 1104746496 Sep 19 22:08 cfp106_archivelogs_CFP106_trsesmr1_955_20170919
-rw-r----- 1 oracle dba 1193869824 Sep 19 22:08 cfp106_archivelogs_CFP106_tqsesmr1_954_20170919
-rw-r----- 1 oracle dba 1079053312 Sep 19 22:10 cfp106_archivelogs_CFP106_ttsesmvj_957_20170919
-rw-r----- 1 oracle dba 965541376 Sep 19 22:10 cfp106_archivelogs_CFP106_tvsesn1p_959_20170919
-rw-r----- 1 oracle dba 1056766976 Sep 19 22:10 cfp106_archivelogs_CFP106_u0sesn1q_960_20170919
-rw-r----- 1 oracle dba 1160766464 Sep 19 22:10 cfp106_archivelogs_CFP106_tusesn10_958_20170919
-rw-r----- 1 oracle dba 935014400 Sep 19 22:10 cfp106_archivelogs_CFP106_u2sesn2b_962_20170919
-rw-r----- 1 oracle dba 1147851264 Sep 19 22:11 cfp106_archivelogs_CFP106_u1sesn1r_961_20170919

this is more than 2 weeks old .
I know i am missing something very basic .
Please assist .

Report message to a moderator

Re: Old backups not getting deleted [message #665993 is a reply to message #665990]

Fri, 06 October 2017 02:15

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

What is the CONTROLFILE_RECORD_KEEP_TIME parameter set to?

And, by the way, next time you past code please enclose it within [code] tags as described here How to use [code] tags and make your code easier to read

[Updated on: Fri, 06 October 2017 02:15]

Report message to a moderator

Re: Old backups not getting deleted [message #665997 is a reply to message #665990]

Fri, 06 October 2017 06:56

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Why do you have this:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;

Then do this

DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 10 DAYS DEVICE TYPE DISK;

Offhand, I'm not seeing why those files haven't been deleted. Your case doesn't match what usually leads to the question of why "older" backups are still hanging around.

As an aside, I'd get rid of the hard-coding of all those environment variables. Instead of

ORACLE_SID=cfp106;export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH

I do this

export ORACLE_SID=cfp106
ORAENV_ASK=NO
source oraenv

oraenv will take care of setting ORACLE_HOME, etc to the proper values. Setting ORAENV_ASK=NO before calling oraenv will cause it to use the current value of ORACLE_SID instead of prompting for it. By coding your scripts this way, you eliminate having to come back and change all those hard-coded values when you upgrade. One of my fundamental rules for writing code, any code, any language is "do not hard-code values that are possibly subject to change."

Report message to a moderator

Re: Old backups not getting deleted [message #666007 is a reply to message #665997]

Fri, 06 October 2017 09:15

SSharma7985
Messages: 8
Registered: January 2011
Location: India

Junior Member

Hi John --> retention is set to 40 days

Hi Steve --> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;

To retain backups for 2 months .

DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 10 DAYS DEVICE TYPE DISK;

To delete backups older thn 10 days from disk .

Report message to a moderator

Re: Old backups not getting deleted [message #666008 is a reply to message #666007]

Fri, 06 October 2017 09:43

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Hi John --> retention is set to 40 days

What does this mean? I asked about the CONTROLFILE_RECORD_KEEP_TIME, is that what you are describing? Because if you have not set it, you will get exactly the effect you describe: old backups not being deleted.

Report message to a moderator

Re: Old backups not getting deleted [message #666009 is a reply to message #666007]

Fri, 06 October 2017 09:45

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Hi Steve --> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;

To retain backups for 2 months .

This is wrong. If you want to keep backups for sixty days, use KEEP UNTIL SYSDATE+60. And of course increase your CONTROLFILE_RECORD_KEEP_TIME accordingly.

Report message to a moderator

Re: Old backups not getting deleted [message #666010 is a reply to message #666009]

Fri, 06 October 2017 10:08

SSharma7985
Messages: 8
Registered: January 2011
Location: India

Junior Member

hey john sorry its CONTROLFILE_RECORD_KEEP_TIME and is set to 40

Report message to a moderator

Previous Topic:	Prepare Cold Standby Server
Next Topic:	RMAN Another Processing Is Running

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 30 11:07:06 CDT 2025