Shulou(Shulou.com)05/31 Report--

Editor to share with you how ARCHIVELOG statistics archive log is more accurate, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Do some information about the history of an archive log and find that there are two views v$archived_log and the other is V$log_history

Let's first take a look at the official instructions:

V$archived_log, that is, this view shows the information in the control file. If the archive log has been deleted, the name column will be displayed as empty.

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 is relatively simple, that is, to control the historical information in the file.

V$LOG_HISTORY displays log history information from the control file

So what's the difference between them, or who counts them more accurately? (statistical tests have been done, and the number of archive logs generated every day is inconsistent)

As follows:

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) / 1024amp 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 days, I manually cut the archive three times, as follows

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

SQL > /

System altered.

While v$log_history shows two.

Let's look at it through rman and show that there are two results on the 18th, that is, the statistical information is the same as 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

Okay, we're looking at it through the system command, but it's three.

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

What is the situation? by comparing with rman, we can see that one statistics is the start time, and the other is the end time.

The start time is the same as the query result of the rman execution command (list archivelog all), and the other end time is the same as the result of the ls-lrt in the operating system. Of course, their statistical information is the same before the archive file is deleted, because after deletion, 'list archivelog all'' and'ls-lrt' will no longer be displayed.

Of course, we can directly count the size of undeleted archive logs

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'

If we want more accurate results (if it is for counting undeleted information) we can choose deleted='NO' or NAME IS NOT NULL.

The above is all the contents of the article "how ARCHIVELOG counts archived logs more accurately". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.