Home » RDBMS Server » Server Administration » Archive log summary (10.2.0.2, linux)
Archive log summary [message #281919] Tue, 20 November 2007 00:45 Go to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
Hi,
I need to write a sql which gives sum of total size of archives generated per day.It needs to be in the below format

date size
------ ------
11.10. thursday 607
Re: Archive log summary [message #281922 is a reply to message #281919] Tue, 20 November 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This information is not (directly) available from the database.
You have to make an OS script.

Regards
Michel
Re: Archive log summary [message #281927 is a reply to message #281919] Tue, 20 November 2007 01:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,’YYYY-MM-DD’) DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,’YYYY-MM-DD’)
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;

http://kubilaykara.wordpress.com/2007/09/05/redo-log-generation/
Re: Archive log summary [message #281929 is a reply to message #281927] Tue, 20 November 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Log switch can occur before log is filled up and so archived log is smaller than redo log.

Regards
Michel
Re: Archive log summary [message #281934 is a reply to message #281919] Tue, 20 November 2007 01:29 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
Thanks taj,
Michel ,u mean the script is not consistent?
Re: Archive log summary [message #281940 is a reply to message #281929] Tue, 20 November 2007 02:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Log switch can occur before log is filled up and so archived log is smaller than redo log.
Yes, Micheal it is correct.
but OP want to know total archivelog size generated per day.
Re: Archive log summary [message #281954 is a reply to message #281940] Tue, 20 November 2007 03:01 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, he wants to know that and you can't give him from Oracle data as you don't know archived log size.

Regards
Michel
Previous Topic: temporary tablepsace
Next Topic: SYS-GRANT on V_$SESSION impossible
Goto Forum:
  


Current Time: Thu Dec 08 21:54:10 CST 2016

Total time taken to generate the page: 0.05602 seconds