Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Saturday, June 18, 2011

The difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup

Most probably most of you know but I wanted to demonstrate the difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup.

Lets first check the current archivelog destinations in the target database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 18 12:16:24 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      LOCATION=USE_DB_RECOVERY_FILE_
DEST
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL> 

As you can see there are two archivelog destinations defined "log_archive_dest_1" and "log_archive_dest_2".

Lets list the archivelogs in the target database by using RMAN tool.

[oracle@localhost ORCL]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 18 12:24:32 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

24      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

26      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc


RMAN> 

Lets now try to backup and delete backed up archivelogs by using "DELETE INPUT".

RMAN> backup archivelog all delete input;

Starting backup at 18-JUN-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=404 RECID=24 STAMP=754143819
input archived log thread=1 sequence=405 RECID=26 STAMP=754143861
input archived log thread=1 sequence=406 RECID=28 STAMP=754143995
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T122636_6zsyzf26_.bkp tag=TAG20110618T122636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc RECID=24 STAMP=754143819
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc RECID=26 STAMP=754143861
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_406_6zsyzcjd_.arc RECID=28 STAMP=754143995
Finished backup at 18-JUN-11

Now list the remaining archivelog files, since only one copy of archivelog files are deleted, we still see the first copies of archivelogs remaining in the first archivelog destination.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

27      1    406     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_406_701609923.dbf


RMAN> 

Now lets try to backup and delete the all the archivelogs backed up by using "DELETE ALL INPUT". We will start the demonstration with newly generated archivelogs.
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
29      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf

30      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc

31      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf

32      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc

Backup and delete all of them.

RMAN> backup archivelog all delete all input;

Starting backup at 18-JUN-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=407 RECID=30 STAMP=754144245
input archived log thread=1 sequence=408 RECID=32 STAMP=754144248
input archived log thread=1 sequence=409 RECID=34 STAMP=754144369
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T123249_6zszc1s6_.bkp tag=TAG20110618T123249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc RECID=30 STAMP=754144245
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf RECID=29 STAMP=754144245
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc RECID=32 STAMP=754144248
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf RECID=31 STAMP=754144248
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_409_6zszc108_.arc RECID=34 STAMP=754144369
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_409_701609923.dbf RECID=33 STAMP=754144369
Finished backup at 18-JUN-11

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> 

As a result, by using "DELETE INPUT" we made RMAN, backup only one copy of all the archivelogs in one archivelog destination and then delete only the backed up copies of all the archivelogs in only one archivelog destination, but by using "DELETE ALL INPUT", we made RMAN backup only one copy of all the archivelogs in one archivelog destination and then delete all the copies of all the archivelogs in all the archivelog destinations.

4 comments:

Gokhan Atil said...

It's a useful hint for the ones who uses multiple archive log destinations. Thank for sharing.

Vijay Penamatsa said...

Thanks a lot for the clear demonstration of the difference between the 2 options.

sguinales said...

U can use
backup archivelog like '%' delete input;

Only delete the dest you want. like ORacle doc said:

"For example, assume that you archive to /arc_dest1, /arc_dest2, and /arc_dest3, and you run the following command:

BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;

In this case RMAN backs up only one copy of each log sequence number in these directories, and then deletes all copies of any log that it backed up from the archiving destinations. If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would only delete the specific archived redo log files that it backed up (for example, it would delete the archived redo log files in /arc_dest1 if those were the files used as the source of the backup, but it would leave the contents of the /arc_dest2 and /arc_dest3 intact)."

Ural Ural said...

Thank you all for your precious comments.
Cheers,
Ural