Oracle archive log deletion

We all know that the controlfile records information about each archivelog. Of course, after deleting these physical files in the OS

The archivelog information is still recorded in the controlfile. the logs are displayed visually in the Oracle OEM manager. After we manually clear the files in the archive directory, these records are not cleared from controlfile, that is, Oracle does not know that these files do not exist! In this case, if we want to manually clear it, we can try this method after experiment:

1. Enter RMAN

2. Connect target/

3. Crosscheck archivelog all;

4. Delete expired archivelog all;

At this time, we will not see it again in the OEM. If you have never done this operation, we can compare the controlfile size after the controlfile before this action!

How Does Oracle Delete the archive and recycle the space correctly?

How Does Oracle Delete the archive and recycle the space correctly?

An Oracle archive log is often full, as the file space occupied by/oraarchive is 100%. Everyone must complain about why Oracle does not have an archive maintenance tool. Many people delete the file directly. Wrong, Oracle has, it is intelligent to delete archive and flashback correctly. However, it is important to remember that Oracle archive logs are important for ORACLE data recovery and backup. You cannot delete archive logs unless necessary.

Deleting archived logs

Log on to the database server host as an oracle user or connect to the database server through a network

Go to Oracle Data Backup Tool

RMAN target/

Or RMAN target/@ orcl

Run

Delete archivelog all completed before 'sysdate-7 ';

Description

SYSDATA-7, indicating the current system time 7 days ago, the before keyword indicates the archived log 7 days ago, and if the flash back function is used, the flash back data is also deleted.

In the same way, you can delete all the logs from the past seven days to the present. However, this command should be clear. After this deletion, it is best to back up the database immediately.

Delete archivelog from time 'sysdate-7 ';

 

In Unix/Linux, you can also use find to find the archived data seven days ago and delete it using the exec sub-operation.

 

Find/oraarchive-xdev-mtime + 7-name "*. DBF"-exec Rm-f {};

In this way, unmanaged archive files will still be left in RMAN.

You still need to execute the following two commands in RMAN

Crosscheck archivelog all;

Delete expired archivelog all;

So it is not as easy to use as the above method, but the advantage of using find is that you can perform a lot of operations on the condition and the exec subitem to implement more complex functions.

3. Brief introduction to the report obsolete command

 

Use the report obsolete command to report expired backups

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type key completion time filename/handle

----------------------------------------------------------------

Backup set 125 01-nov-04

Backup piece 125 01-nov-04/data1/Oracle/orabak/full_0000541045804

Backup set 131 04-nov-04

Backup piece 131 04-nov-04/data1/Oracle/orabak/full_avatar2_20041104_131

....

Backup set 173 06-dec-04

Backup piece 173 06-dec-04/data1/Oracle/orabak/full_avatar2_20041206_173

Backup set 179 11-dec-04

Backup piece 179 11-dec-04/data1/Oracle/orabak/arch544588206.arc

.....

Backup piece 189 17-dec-04/data1/Oracle/orabak/arch545425606.arc

Backup set 190 17-dec-04

Backup piece 190 17-dec-04/data1/Oracle/orabak/arch545106665.arc

Backup set 191 20-dec-04

Backup piece 191 20-dec-04/data1/Oracle/orabak/arch_avatar2_20041220_194

Archive log 2973 20-dec-04/opt/Oracle/oradata/avatar2/archive/1_2985.dbf

Archive log 2971 20-dec-04/opt/Oracle/oradata/avatar2/archive/1_2984.dbf

.....

Archive log 2705 17-dec-04/opt/Oracle/oradata/avatar2/archive/4102717.dbf

Archive log 2704 17-dec-04/opt/Oracle/oradata/avatar2/archive/4152716.dbf

Archive log 2703 17-dec-04/opt/Oracle/oradata/avatar2/archive/4262715.dbf

Archive log 2702 17-dec-04/opt/Oracle/oradata/avatar2/archive/4152714.dbf

 

4. Run the delete obsolete command to delete expired backups:

 

RMAN> Delete Obsolete;

 

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Using channel ora_disk_1

Deleting the following obsolete backups and copies:

Type key completion time filename/handle

----------------------------------------------------------------

Backup set 125 01-nov-04

Backup piece 125 01-nov-04/data1/Oracle/orabak/full_0000541045804

....

Archive log 2704 17-dec-04/opt/Oracle/oradata/avatar2/archive/4152716.dbf

Archive log 2703 17-dec-04/opt/Oracle/oradata/avatar2/archive/4262715.dbf

Archive log 2702 17-dec-04/opt/Oracle/oradata/avatar2/archive/4152714.dbf

 

Do you really want to delete the above objects (enter yes or no )? Yes

Deleted backup piece

Backup piece handle =/data1/Oracle/orabak/full_avatar2_20041206_173 recid = 173 stamp = 544156241

.....

Deleted archive log

Archive log filename =/opt/Oracle/oradata/avatar2/archive/4152715.dbf recid = 2703 stamp = 545108268

Deleted archive log

Archive log filename =/opt/Oracle/oradata/avatar2/archive/4152714.dbf recid = 2702 stamp = 545107659

Deleted 286 objects

 

 

RMAN> crosscheck archivelog all;

 

Released channel: ora_disk_1

Allocated channel: ora_disk_1

Channel ora_disk_1: SID = 19 devtype = Disk

Specification does not match any archive log in the recovery catalog

 

 

5. Check archive logs with crosscheck in RMAN. Both archive logs fail:

RMAN> crosscheck archivelog all;

 

Released channel: ora_disk_1

Allocated channel: ora_disk_1

Channel ora_disk_1: SID = 14 devtype = Disk

An error occurred while verifying the archived logs.

Archive log file name = D: oracleoradatatestarchive000047.dbf Record ID = 1 timestamp = 572866

683

An error occurred while verifying the archived logs.

Archive log file name = D: oracleoradatatestarchive11_48.dbf Record ID = 2 timestamp = 57286

6931

2 objects that have been cross-checked

 

6. Try to synchronize the data. If it doesn't work, the result won't work. Crosscheck still fails:

RMAN> Resync Catalog;

 

Starting Resync for all recovery Directories

Complete all Resync

 

RMAN> crosscheck archivelog all;

 

Released channel: ora_disk_1

Allocated channel: ora_disk_1

Channel ora_disk_1: SID = 14 devtype = Disk

An error occurred while verifying the archived logs.

Archive log file name = D: oracleoradatatestarchive000047.dbf Record ID = 1 timestamp = 572866

683

An error occurred while verifying the archived logs.

Archive log file name = D: oracleoradatatestarchive11_48.dbf Record ID = 2 timestamp = 57286

6931

2 objects that have been cross-checked

 

7. Use List expired to check whether any invalid archive log exists, and verify that no invalid archive log exists:

RMAN> List expired archivelog all;

 

The description does not match any archived logs in the recovery directory.

 

8. Try changing the language environment. The result is crosscheck again. The two archive logs are all successful:

RMAN> exit

 

 

The recovery manager is complete.

 

C:> set nls_lang = american_america.zhs16gbk

 

C:> RMAN catalogrman/RMAN @ safetarget/

 

Recovery MANAGER: Release 9.2.0.1.0-Production

 

Copyright (c) 1995,200 2, Oracle Corporation. All rights reserved.

 

Connected to target database: Test (dbid = 1870953724)

Connected to recovery Catalog Database

 

RMAN> crosscheck archivelog all;

 

Allocated channel: ora_disk_1

Channel ora_disk_1: SID = 9 devtype = Disk

Validation succeeded for archived log

Archive log filename = D: oracleoradatatestarchiveapps47.dbf recid = 1 Stamp = 57286

6683

Validation succeeded for archived log

Archive log filename = D: oracleoradatatestarchive11_48.dbf recid = 2 Stamp = 5728

66931

Crosschecked 2 objects