Tuesday, August 27, 2013

what decides number of records in v$log_history

V$log_history retention is defined by MAXLOGHISOTRY when you create the database (or recreate the controlfile)

MAXLOGHISTORY Clause
This parameter is useful only if you are using Oracle Database in archivelog mode with Real Application Clusters. Specify the maximum number of archived redo log files for automatic media recovery of Real Application Clusters. The database uses this value to determine how much space to allocate in the control file for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file.

SQL> SELECT min(first_time), max(first_time) from v$log_history;

MIN(FIRST MAX(FIRST
--------- ---------
08-AUG-13 26-AUG-13

SQL> SELECT count(*) from v$log_history;                       

  COUNT(*)
----------
      5445
SQL> select * from V$CONTROLFILE_RECORD_SECTION where type='LOG HISTORY';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
LOG HISTORY                           56          5445         5445        3462       3461     580177