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:
Nice article Hemant. But why we should need to clear that view (and the data in the controlfile)?
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
Oracle Support Article#845361.1 provides another case.
Hemant K Chitale
Post a Comment