How to delete expired or obsolete archvielog files.
How to delete expired or obsolete archvielog files.
RMAN provided two commands for deleting archive log.
1.delete expired archivelog
2.backup archivelog all delete input
1.delete expired archivelog
2.backup archivelog all delete input
These command can delete archive logs based on the input provided. There were two things that RMAN made sure every time it attempted to delete an archive log. The first is that specified archive log is backed up to all the locations specified under log_archived_dest_n parameters. The value under these parameters can be flash recovery area or non flash recovery area. Second is whether the archive logs to be deleted are obsolete or not. This in turn depends on your backup retention policy. An archive redo log is considered obsolete if it is not required in any recovery scenario.
Using RMAN we can remove the old archvielogs or old backup file using report obsolete or delete obsolete by setting rman rman retention policy
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\USER\PRODUCT\12.1.0\DBHOME_1\DATA
BASE\SNCFDEV.ORA'; # default
RMAN>
Above Configuration show RMAN retention policy is 1, based on this retention policy only report obsolete or delete obsolete will work.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\USER\PRODUCT\12.1.0\DBHOME_1\DATA
BASE\SNCFDEV.ORA'; # default
RMAN>
Above Configuration show RMAN retention policy is 1, based on this retention policy only report obsolete or delete obsolete will work.
Manually we can remove the expired archivelog using below 2 options:
1.delete .. expired archivelog;
2.backup archivelog all delete input;
1. delete .. expired archivelog;
By using this command we can remove the old archivelog manually.
Using OS command remove the old archivelog files from the archivelog log location.
Move to archivelog location then issue (to find the achivelog location , connect sqlplus / as sysdba )
execute below command to find the archive log.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
SQL>
Archvielog location using Flash revoerey area,
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\user\fast_recovery_area
db_recovery_file_dest_size big integer 6930M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL>
ARchivelog location is "E:\app\user\fast_recovery_area\dev\ARCHIVELOG".
move to this location and issue command below with 2 options.
Recommended options is move the old archivelog file to some other directory wait for 2 days then remove that files. or directly reomove like below
rm -rf *.arc or
rm -rf (specified archivelog files)
Then issue the command connecting RMAN.
C:\Users\user>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 11 17:19:15 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV (DBID=4027486540)
RMAN>
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>
Before crosscheck its shows like this.
RMAN> crosscheck archivelog all;
It will crosscheck the archivelog files with RMAN repository deleted files status changed to expired in RMAN repository.
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
39 1 48 X 11-OCT-17
Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_
8_DXW2ZZFW_.ARC
RMAN>
Then issue below command to remove from the RMAN repository.
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
39 1 48 X 11-OCT-17
Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_4
8_DXW2ZZFW_.ARC
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\
O1_MF_1_48_DXW2ZZFW_.ARC RECID=39 STAMP=957116888
Deleted 1 EXPIRED objects
RMAN>
RMAN> delete noprompt expired archivelog all;
this command will remove the files without prompting the Yes or no.
2.Backup archivelog all delete input:
Above command will remove the all archivelog file after taking the backup of all archvielog available in current RMAN repository.
We have options also
It will take backup 2 days before archivelog file and then remove it.
RMAN> backup archivelog until time 'sysdate-2' delete input;
Crosscheck commands:
To crosscheck all backups use:
RMAN> CROSSCHECK BACKUP;
To list any expired backups detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED BACKUP;
To delete any expired backups detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED BACKUP;
To crosscheck all archive logs use:
RMAN> CROSSCHECK ARCHIVELOG ALL;
To list all expired archive logs detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED ARCHIVELOG ALL;
To delete all expired archive logs detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
To crosscheck all datafile image copies use:
RMAN> CROSSCHECK DATAFILECOPY ALL;
To list expired datafile copies use:
RMAN> LIST EXPIRED DATAFILECOPY ALL;
To delete expired datafile copies use:
RMAN> DELETE EXPIRED DATAFILECOPY ALL;
To crosscheck all backups of the USERS tablespace use:
RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;
To list expired backups of the USERS tablespace:
RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;
To delete expired backups of the USERS tablespace:
RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;
1.delete .. expired archivelog;
2.backup archivelog all delete input;
1. delete .. expired archivelog;
By using this command we can remove the old archivelog manually.
Using OS command remove the old archivelog files from the archivelog log location.
Move to archivelog location then issue (to find the achivelog location , connect sqlplus / as sysdba )
execute below command to find the archive log.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
SQL>
Archvielog location using Flash revoerey area,
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\user\fast_recovery_area
db_recovery_file_dest_size big integer 6930M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL>
ARchivelog location is "E:\app\user\fast_recovery_area\dev\ARCHIVELOG".
move to this location and issue command below with 2 options.
Recommended options is move the old archivelog file to some other directory wait for 2 days then remove that files. or directly reomove like below
rm -rf *.arc or
rm -rf (specified archivelog files)
Then issue the command connecting RMAN.
C:\Users\user>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 11 17:19:15 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV (DBID=4027486540)
RMAN>
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>
Before crosscheck its shows like this.
RMAN> crosscheck archivelog all;
It will crosscheck the archivelog files with RMAN repository deleted files status changed to expired in RMAN repository.
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
39 1 48 X 11-OCT-17
Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_
8_DXW2ZZFW_.ARC
RMAN>
Then issue below command to remove from the RMAN repository.
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
39 1 48 X 11-OCT-17
Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_4
8_DXW2ZZFW_.ARC
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\
O1_MF_1_48_DXW2ZZFW_.ARC RECID=39 STAMP=957116888
Deleted 1 EXPIRED objects
RMAN>
RMAN> delete noprompt expired archivelog all;
this command will remove the files without prompting the Yes or no.
2.Backup archivelog all delete input:
Above command will remove the all archivelog file after taking the backup of all archvielog available in current RMAN repository.
We have options also
It will take backup 2 days before archivelog file and then remove it.
RMAN> backup archivelog until time 'sysdate-2' delete input;
Crosscheck commands:
To crosscheck all backups use:
RMAN> CROSSCHECK BACKUP;
To list any expired backups detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED BACKUP;
To delete any expired backups detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED BACKUP;
To crosscheck all archive logs use:
RMAN> CROSSCHECK ARCHIVELOG ALL;
To list all expired archive logs detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED ARCHIVELOG ALL;
To delete all expired archive logs detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
To crosscheck all datafile image copies use:
RMAN> CROSSCHECK DATAFILECOPY ALL;
To list expired datafile copies use:
RMAN> LIST EXPIRED DATAFILECOPY ALL;
To delete expired datafile copies use:
RMAN> DELETE EXPIRED DATAFILECOPY ALL;
To crosscheck all backups of the USERS tablespace use:
RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;
To list expired backups of the USERS tablespace:
RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;
To delete expired backups of the USERS tablespace:
RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;
