Search My Oracle Blog

Custom Search

22 April, 2011

ArchiveLogs in the controlfile

RMAN's listing will only show those that RMAN has not obsoleted or deleted.
RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
validation failed for archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_722_6t0vvymw_.arc RECID=549 STAMP=748046335
validation failed for archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_723_6t0xr7rg_.arc RECID=550 STAMP=748048264
validation failed for archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_724_6t0y14tl_.arc RECID=551 STAMP=748049471
validation failed for archived log

... entries deleted from this output ....

validation succeeded for archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_14_6v33hb8m_.arc RECID=565 STAMP=749168234
Crosschecked 17 objects

A number of ArchivedLogs were expected to be present on disk but couldn't be found. These are marked as EXPIRED.
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
549 1 722 X 07-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_722_6t0vvymw_.arc

550 1 723 X 09-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_723_6t0xr7rg_.arc

551 1 724 X 09-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_724_6t0y14tl_.arc

552 1 1 X 09-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_1_6t0zmypg_.arc

... entries deleted from this output ....

563 1 12 X 17-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_21/o1_mf_1_12_6v0j3111_.arc

564 1 13 X 21-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_13_6v339368_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_722_6t0vvymw_.arc RECID=549 STAMP=748046335
deleted archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_723_6t0xr7rg_.arc RECID=550 STAMP=748048264
deleted archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_724_6t0y14tl_.arc RECID=551 STAMP=748049471
deleted archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_1_6t0zmypg_.arc RECID=552 STAMP=748050174

... entries deleted from this output ....

deleted archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_13_6v339368_.arc RECID=564 STAMP=749168038
Deleted 16 EXPIRED objects

(You might notice that I have done a RESETLOGS on 09-April, but that fact is irrelevant for the purpose of this blog entry about the *number* of ArchiveLog entries in the controlfile and RMAN listings).

After an SQL 'ALTER SYSTEM SWITCH LOGFILE;' I have :
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
------- ---- ------- - ---------
565 1 14 A 22-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_14_6v33hb8m_.arc

566 1 15 A 22-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_15_6v33j837_.arc



V$ARCHIVED_LOG shows all the archivelogs that are registered in the controlfile. This list may well exceed the CONTROL_FILE_RECORD_KEEP_TIME. This list is not the same as that presented by RMAN.

Thus :
SQL> show parameter control_file_record_keep_time

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
SQL> select trunc(first_time), count(*)
2 from v$archived_log
3 group by trunc(first_time)
4 order by 1
5 /

TRUNC(FIR COUNT(*)
--------- ----------
01-DEC-10 1
08-JAN-11 6
09-JAN-11 275
13-JAN-11 1
14-JAN-11 2
15-JAN-11 11

... entries deleted from this output ....

15-APR-11 2
16-APR-11 3
17-APR-11 1
21-APR-11 1
22-APR-11 2

50 rows selected.

SQL>
SQL> select max(first_time)-min(first_time) from v$archived_log;

MAX(FIRST_TIME)-MIN(FIRST_TIME)
-------------------------------
142.657558

SQL>


The controlfile has ArchiveLogs going back more than 142 days !


.....

There is a way to "clear" *ALL* the entries. NOT to be done on Production databases. NOT to be done on a Primary for a Standby or on a Standby -- unless you know precisely the potential consequences and how to deal with them.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 201330340 bytes
Database Buffers 629145600 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL>
SQL> select * from v$controlfile_record_section
2 where type = 'ARCHIVED LOG';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 1349 414 1270 334 566

SQL>
SQL> -- do *NOT* do this on Production
SQL> -- do *NOT* do this if you have a Standby database
SQL> -- the section number may not be documented to be the same in all versions
SQL> exec dbms_backup_restore.resetcfilesection(11);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL> select * from v$controlfile_record_section
2 where type = 'ARCHIVED LOG';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 1349 0 0 0 0

SQL>
SQL> select count(*) from v$archived_log;

COUNT(*)
----------
0

SQL>
RMAN> list archivelog all;

using target database control file instead of recovery catalog
specification does not match any archived log in the repository

RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
specification does not match any archived log in the repository

RMAN>


*ALL* the entries have been purged from the controfile. Neither V$ARCHIVED_LOG nor RMAN lists any controlfiles.
Yet, the physical files do still exist :
[oracle@localhost 2011_04_22]$ pwd
/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22
[oracle@localhost 2011_04_22]$ ls -l
total 472
-rw-rw---- 1 oracle oracle 467968 Apr 22 22:17 o1_mf_1_14_6v33hb8m_.arc
-rw-rw---- 1 oracle oracle 5120 Apr 22 22:17 o1_mf_1_15_6v33j837_.arc
[oracle@localhost 2011_04_22]$


Can I add these files back in ?
RMAN> catalog start with '/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22';

using target database control file instead of recovery catalog
searching for all files that match the pattern /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22

List of Files Unknown to the Database
=====================================
File Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_15_6v33j837_.arc
File Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_14_6v33hb8m_.arc

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

List of Cataloged Files
=======================
File Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_15_6v33j837_.arc
File Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_14_6v33hb8m_.arc

RMAN>
RMAN> list archivelog all;

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

Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 14 A 22-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_14_6v33hb8m_.arc

1 1 15 A 22-APR-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_22/o1_mf_1_15_6v33j837_.arc


RMAN>
SQL> select * from v$controlfile_record_section
2 where type = 'ARCHIVED LOG';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 1349 2 1 2 2

SQL> select count(*) from v$archived_log;

COUNT(*)
----------
2


SQL> select thread#, sequence# from v$archived_log;

THREAD# SEQUENCE#
---------- ----------
1 15
1 14

SQL>


YES. Fortunately, I can "add" them back.

As I noted above, DO NOT try this on a Production database or on a Standby -- unless you know precisely the potential consequences and how to deal with them !

UPDATE 11-May-11 : MAXLOGHISTORY in the CREATE DATABASE (or CREATE CONTROLFILE) command also must be considered.
.
.

3 comments:

Kamran Agayev A. said...

Nice article Hemant. But why we should need to clear that view (and the data in the controlfile)?

Hemant K Chitale said...

Kamran,
I've edited my warnings about using this command on a Standby database with the words "unless you know precisely the potential consequences and how to deal with them."

Oracle Support Article#753893.1 provides a use case for "resetcfilesection(11)" on a Standby Database.

Hemant K Chitale

Hemant K Chitale said...

Oracle Support Article#845361.1 provides another case.

Hemant K Chitale

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016