Home » RDBMS Server » Backup & Recovery » Old backups not getting deleted (Oracle 11g Enterprise Edition , 11.2.0.4 , Solaris 10)
Old backups not getting deleted [message #665990] Fri, 06 October 2017 00:26 Go to next message
SSharma7985
Messages: 6
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 .

Re: Old backups not getting deleted [message #665993 is a reply to message #665990] Fri, 06 October 2017 02:15 Go to previous messageGo to next message
John Watson
Messages: 7095
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 Go to previous messageGo to next message
EdStevens
Messages: 839
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."
Re: Old backups not getting deleted [message #666007 is a reply to message #665997] Fri, 06 October 2017 09:15 Go to previous messageGo to next message
SSharma7985
Messages: 6
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 .
Re: Old backups not getting deleted [message #666008 is a reply to message #666007] Fri, 06 October 2017 09:43 Go to previous messageGo to next message
John Watson
Messages: 7095
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.

Re: Old backups not getting deleted [message #666009 is a reply to message #666007] Fri, 06 October 2017 09:45 Go to previous messageGo to next message
John Watson
Messages: 7095
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.
Re: Old backups not getting deleted [message #666010 is a reply to message #666009] Fri, 06 October 2017 10:08 Go to previous message
SSharma7985
Messages: 6
Registered: January 2011
Location: India
Junior Member
hey john sorry its CONTROLFILE_RECORD_KEEP_TIME and is set to 40
Previous Topic: Can restoring backup from tape to disk help me to restore spilfw
Next Topic: Exp in Oracle 12c
Goto Forum:
  


Current Time: Wed Oct 18 00:47:10 CDT 2017

Total time taken to generate the page: 0.02597 seconds