Shulou(Shulou.com)06/01 Report--

Today, my colleague asked a question: a set of Oracle 11g databases on his side cannot delete old archives using RMAN, resulting in high disk utilization.

Oracle@ps251n2s: [/ opt/oracle/archive/db] ls-ltr | more

Total 3059881082

-rw-r- 1 oracle oinstall 725320704 May 20 2016 1_1_91233774.dbf

-rw-rw---- 1 oracle oinstall 1756937216 Jun 7 2016 1_2_91233774.dbf

-rw-rw---- 1 oracle oinstall 1756731392 Jun 21 2016 1_3_91233774.dbf

-rw-rw---- 1 oracle oinstall 1758001152 Jul 3 2016 1_4_91233774.dbf

-rw-rw---- 1 oracle oinstall 1756729344 Jul 15 2016 1_5_91233774.dbf

-rw-rw---- 1 oracle oinstall 1765750784 Jul 26 22:00 1_6_91233774.dbf

-rw-rw---- 1 oracle oinstall 1760346112 Aug 6 14:04 1_7_91233774.dbf

.

Using RMAN list archivelog all; to view the log does not show the records of the old archive log

RMAN > list archivelog all

Using target database control file instead of recovery catalog

List of Archived Log Copies for database with db_unique_name DBS

=

Key Thrd Seq S Low Time

-

1206 1 1206 A 14-JAN-17

Name: / opt/oracle/archive/db/1_1206_91233774.dbf

1207 1 1207 A 14-JAN-17

Name: / opt/oracle/archive/db/1_1207_91233774.dbf

1208 1 1208 A 14-JAN-17

Name: / opt/oracle/archive/db/1_1208_91233774.dbf

.

No old archive log records were found using crosscheck check.

RMAN > crosscheck archivelog all

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=16 device type=DISK

Validation succeeded for archived log

Archived log file name=/opt/oracle/archive/db/1_1206_91233774.dbf RECID=1206 STAMP=933244655

Validation succeeded for archived log

Archived log file name=/opt/oracle/archive/db/1_1207_91233774.dbf RECID=1207 STAMP=933249510

Validation succeeded for archived log

Archived log file name=/opt/oracle/archive/db/1_1208_91233774.dbf RECID=1208 STAMP=933254500

Validation succeeded for archived log

Archived log file name=/opt/oracle/archive/db/1_1209_91233774.dbf RECID=1209 STAMP=933259445

Validation succeeded for archived log

View the v$archived_log view and there are no old archive log records

So Baidu found an article http://3y.uu456.com/bp_3i90z4529l10ttd0odyk_1.html which basically means that MAN deletes archive logs based on the archive logs recorded in controlfile, not the actual archive logs on your disk.

If your archive log is no longer in controlfile, it cannot be recognized by RMAN and cannot be deleted. When the archived information is overwritten in the control file is controlled by a parameter control_file_record_keep_time.

Check that the parameters in the database are 7 days by default

SYS@db > show parameter CONTROL_FILE_RECORD_KEEP_TIME

NAME TYPE VALUE

-

Control_file_record_keep_time integer 7

Is it possible that the number of entries archived in the control file can only be retained for 7 days? So go to MOS to find relevant documents and find a Why Are Controlfile Entries For Archived Logs Not Overwritten After CONTROL_FILE_RECORD_KEEP_TIME? (document ID 1056085.1), the CONTROL_FILE_RECORD_KEEP_TIME parameter is not forced to keep the archive log entry in the control file for how many days, but is controlled by the ARCHIVED LOG entry in the V$CONTROLFILE_RECORD_SECTION view. When RECORDS_TOTAL=RECORDS_USED, the old archive log will be overwritten.

So looking at the V$CONTROLFILE_RECORD_SECTION view in the database, you can see that the records_total=224 of the ARCHIVED LOG line means that only 224 pieces of archive log file information can be retained in the control file.

SYS@dbs > select * from v$controlfile_record_section where type='ARCHIVED LOG'

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

ARCHIVED LOG 584 224 224 88 87 1235

Query v$archived_log views with a total of 224 entries

SYS@dbs > select count (*) from v$archived_log

COUNT (*)

-

two hundred and twenty four

At this point, it should be inferred that because only 224 pieces of archive log data can be retained in the control file, the previous archive information has been brushed out by the control file, and RMAN cannot recognize these archive files, so these archive logs cannot be deleted.

What if you use RMAN to delete these old archive logs now? I want to use the catalog start with command to re-register these files into the control file

RMAN > catalog start with'/ opt/oracle/archive/db'

.

File Name: / opt/oracle/archive/db/1_1005_91233774.dbf

File Name: / opt/oracle/archive/db/1_1006_91233774.dbf

File Name: / opt/oracle/archive/db/1_1007_91233774.dbf

Do you really want to catalog the above files (enter YES or NO)? Yes

Cataloging files...

Cataloging done

.

RMAN > list archivelog all

.

1236 1 301 A 22-OCT-16

Name: / opt/oracle/archive/db/1_301_91233774.dbf

1237 1 302 A 22-OCT-16

Name: / opt/oracle/archive/db/1_302_91233774.dbf

.

The old archive log file has been registered in the control file, and then delete it using RMAN

Delete archivelog until logseq 1007

Query the v$controlfile_record_section view again, and records_total has also been "stretched".

SYS@db > select * from v$controlfile_record_section where type='ARCHIVED LOG'

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

ARCHIVED LOG 584 896 742 807 652 1800

This is the end of the question.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.