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:

Anonymous 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