转载

【ARCHIVELOG】怎样统计归档日志更准确

下午做一个归档日志历史记录的一些信息，发现两个视图 v$archived_log 另一个是 V$log_history

下面我们先看下官方说明：

v$archived_log 也就是这个视图显示的是控制文件中的信息，如果归档日志已经删除，name列将显示为空

点击(此处)折叠或打开

V$ARCHIVED_LOG displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

v$log_history 这个说的比较简单，就是控制文件中的历史信息

点击(此处)折叠或打开

V$LOG_HISTORY displays log history information from the control file

那么它们有什么区别呢，或者说谁统计的个数更准确？（已做过统计测试，每天生成的归档日志数量不一致）

如下所示：

v$log_history

点击(此处)折叠或打开

SQL> SELECT trunc(first_time) "Date",
2 to_char(first_time, 'Dy') "Day",
3 count(1) "Totals"
4 FROM V$log_history where to_date(first_time)>to_date(sysdate-15)
5 group by trunc(first_time), to_char(first_time, 'Dy')
6 Order by 1;
Date Day Totals
--------- ------ ----------
05-JUL-17 Wed 1
07-JUL-17 Fri 5
13-JUL-17 Thu 1
18-JUL-17 Tue 2

v$archived_log

点击(此处)折叠或打开

SQL> select trunc(completion_time) as "date",count(*) as "Count",(sum(blocks*block_size)/1024/1024) as "MB" from v$archived_log group by trunc(completion_time);
date Count MB
--------- ---------- ----------
18-JUL-17 3 28.9492188
07-JUL-17 7 229.628418
13-JUL-17 1 27.3828125

18-JUL-17 天，我手动切了三次归档，如下

点击(此处)折叠或打开

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.

而v$log_history 显示的却是2个

下面我们通过rman来看显示结果 18日这天是2个，也就是统计信息跟v$log_history 一样

点击(此处)折叠或打开

RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name MYDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 306 A 30-JUN-17
Name: /backup/mydbarchivelog/1_306_947429846.dbf
2 1 307 A 04-JUL-17
Name: /backup/mydbarchivelog/1_307_947429846.dbf
3 1 308 A 05-JUL-17
Name: /backup/mydbarchivelog/1_308_947429846.dbf
4 1 309 A 07-JUL-17
Name: /backup/mydbarchivelog/1_309_947429846.dbf
5 1 310 A 07-JUL-17
Name: /backup/mydbarchivelog/1_310_947429846.dbf
6 1 311 A 07-JUL-17
Name: /backup/mydbarchivelog/1_311_947429846.dbf
7 1 312 A 07-JUL-17
Name: /backup/mydbarchivelog/1_312_947429846.dbf
8 1 313 A 07-JUL-17
Name: /backup/mydbarchivelog/1_313_947429846.dbf
9 1 314 A 13-JUL-17
Name: /backup/mydbarchivelog/1_314_947429846.dbf
10 1 315 A 18-JUL-17
Name: /backup/mydbarchivelog/1_315_947429846.dbf
11 1 316 A 18-JUL-17
Name: /backup/mydbarchivelog/1_316_947429846.dbf

好，我们在通过系统命令来看 ,却是3个

点击(此处)折叠或打开

SQL> !ls -l /backup/mydbarchivelog
total 341292
-rw-r----- 1 oracle oinstall 27716608 Jun 28 16:33 1_303_947429846.dbf
-rw-r----- 1 oracle oinstall 1552896 Jun 29 11:10 1_304_947429846.dbf
-rw-r----- 1 oracle oinstall 20325888 Jun 30 09:21 1_305_947429846.dbf
-rw-r----- 1 oracle oinstall 19091968 Jul 7 13:58 1_306_947429846.dbf
-rw-r----- 1 oracle oinstall 22322176 Jul 7 13:58 1_307_947429846.dbf
-rw-r----- 1 oracle oinstall 21595136 Jul 7 13:58 1_308_947429846.dbf
-rw-r----- 1 oracle oinstall 48646656 Jul 7 15:15 1_309_947429846.dbf
-rw-r----- 1 oracle oinstall 47450112 Jul 7 15:49 1_310_947429846.dbf
-rw-r----- 1 oracle oinstall 41217024 Jul 7 16:00 1_311_947429846.dbf
-rw-r----- 1 oracle oinstall 40463360 Jul 7 22:00 1_312_947429846.dbf
-rw-r----- 1 oracle oinstall 28713472 Jul 13 11:10 1_313_947429846.dbf
-rw-r----- 1 oracle oinstall 30352896 Jul 18 12:57 1_314_947429846.dbf
-rw-r----- 1 oracle oinstall 1024 Jul 18 12:57 1_315_947429846.dbf
-rw-r----- 1 oracle oinstall 3072 Jul 18 12:57 1_316_947429846.dbf

什么情况呢，通过跟rman对比，我们可以看出，其实一个统计的是开始时间，另一个统计的是结束（完成）时间，
开始时间也就是rman执行命令(list archivelog all)查询结果一致，另一个结束时间，也就跟操作系统中执行命令(ls -lrt）结果一致，当然他们统计的信息是在归档文件未删除前是一致的，因为删除后，'list archivelog all' 和'ls -lrt’ 将不再显示。

当然，我们可以直接统计未删除的归档日志大小

点击(此处)折叠或打开

SQL> --not delete archivelog sum size
SQL> select ((sum(blocks * block_size)) /1024 /1024) as "MB" from v$archived_log where STANDBY_DEST ='NO' and deleted='NO'