小编给大家分享一下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'
如果想要更准确的结果(如果就是为了统计未删除的信息)我们可以选择 deleted='NO' 或者 NAME IS NOT NULL.
以上是“ARCHIVELOG如何统计归档日志更准确”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!