Archives [message #323975] |
Fri, 30 May 2008 03:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
hey when i query
select first_time from v$log_history;
It shows me all the archives date created starting from the database creation but not the last archive date which was created today
but when i query
select name from v$archived_log;
This shows last 26 archive name and not the earlier ones..
Why so?
|
|
|
|
Re: Archives [message #323977 is a reply to message #323975] |
Fri, 30 May 2008 03:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
asangapradeep
Messages: 128 Registered: October 2005 Location: UK
|
Senior Member |
|
|
Quote: | select name from v$archived_log;
|
gives archived but not yet deleted ones. (deleted using rman and etc)
Quote: | select first_time from v$log_history;
|
maybe your database is new. this view will return max of 292 rows once enough logs have switched
|
|
|
Re: Archives [message #323978 is a reply to message #323976] |
Fri, 30 May 2008 03:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
ok
check this
SQL> select first_time from v$log;
FIRST_TIM
---------
30-MAY-08
28-MAY-08
28-MAY-08
SQL> select first_time from v$archived_log
FIRST_TIM
---------
08-MAY-08
08-MAY-08
09-MAY-08
10-MAY-08
10-MAY-08
11-MAY-08
12-MAY-08
13-MAY-08
13-MAY-08
14-MAY-08
15-MAY-08
FIRST_TIM
---------
16-MAY-08
18-MAY-08
19-MAY-08
19-MAY-08
19-MAY-08
20-MAY-08
20-MAY-08
21-MAY-08
22-MAY-08
23-MAY-08
24-MAY-08
FIRST_TIM
---------
25-MAY-08
26-MAY-08
27-MAY-08
28-MAY-08
28-MAY-08
27 rows selected.
SQL> select first_time from v$log_history;
FIRST_TIM
---------
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
04-MAR-08
05-MAR-08
FIRST_TIM
---------
05-MAR-08
05-MAR-08
05-MAR-08
05-MAR-08
05-MAR-08
06-MAR-08
06-MAR-08
06-MAR-08
07-MAR-08
07-MAR-08
08-MAR-08
FIRST_TIM
---------
09-MAR-08
09-MAR-08
10-MAR-08
11-MAR-08
11-MAR-08
12-MAR-08
12-MAR-08
13-MAR-08
13-MAR-08
14-MAR-08
14-MAR-08
FIRST_TIM
---------
16-MAR-08
17-MAR-08
17-MAR-08
18-MAR-08
18-MAR-08
19-MAR-08
20-MAR-08
22-MAR-08
22-MAR-08
24-MAR-08
25-MAR-08
FIRST_TIM
---------
26-MAR-08
26-MAR-08
27-MAR-08
28-MAR-08
29-MAR-08
29-MAR-08
30-MAR-08
31-MAR-08
31-MAR-08
01-APR-08
02-APR-08
FIRST_TIM
---------
03-APR-08
03-APR-08
04-APR-08
04-APR-08
05-APR-08
05-APR-08
06-APR-08
07-APR-08
08-APR-08
09-APR-08
10-APR-08
FIRST_TIM
---------
11-APR-08
11-APR-08
13-APR-08
15-APR-08
16-APR-08
17-APR-08
17-APR-08
18-APR-08
19-APR-08
21-APR-08
21-APR-08
FIRST_TIM
---------
22-APR-08
22-APR-08
23-APR-08
23-APR-08
24-APR-08
24-APR-08
24-APR-08
25-APR-08
26-APR-08
26-APR-08
28-APR-08
FIRST_TIM
---------
29-APR-08
29-APR-08
29-APR-08
30-APR-08
30-APR-08
01-MAY-08
01-MAY-08
02-MAY-08
02-MAY-08
03-MAY-08
04-MAY-08
FIRST_TIM
---------
04-MAY-08
05-MAY-08
05-MAY-08
06-MAY-08
07-MAY-08
07-MAY-08
08-MAY-08
08-MAY-08
09-MAY-08
10-MAY-08
10-MAY-08
FIRST_TIM
---------
11-MAY-08
12-MAY-08
13-MAY-08
13-MAY-08
14-MAY-08
15-MAY-08
16-MAY-08
18-MAY-08
19-MAY-08
19-MAY-08
19-MAY-08
FIRST_TIM
---------
20-MAY-08
20-MAY-08
21-MAY-08
22-MAY-08
23-MAY-08
24-MAY-08
25-MAY-08
26-MAY-08
27-MAY-08
28-MAY-08
28-MAY-08
132 rows selected.
Why isnt v$log_history showing me the archive on 30th and archive ed_log does not show that either.
|
|
|
|
Re: Archives [message #323980 is a reply to message #323979] |
Fri, 30 May 2008 03:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
hey answer to your question asangapradeep
SQL> select bytes, first_time from v$log;
BYTES FIRST_TIM
---------- ---------
104857600 30-MAY-08
104857600 28-MAY-08
104857600 28-MAY-08
|
|
|
Re: Archives [message #323982 is a reply to message #323975] |
Fri, 30 May 2008 03:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
asangapradeep
Messages: 128 Registered: October 2005 Location: UK
|
Senior Member |
|
|
run this and show the output.
Quote: | select status,bytes,first_time from v$log;
|
from the looks of it you only have one log starting on 30th i bet it's the current one
do a
Quote: | alter system archive log current;
|
and query the v$archived_log
|
|
|
Re: Archives [message #323985 is a reply to message #323982] |
Fri, 30 May 2008 03:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
ok
here you go
SQL> select status,bytes,first_time from v$log;
STATUS BYTES FIRST_TIM
---------------- ---------- ---------
CURRENT 104857600 30-MAY-08
INACTIVE 104857600 28-MAY-08
INACTIVE 104857600 28-MAY-08
SQL> select status, first_time from v$archived_log;
S FIRST_TIM
- ---------
A 08-MAY-08
A 08-MAY-08
A 09-MAY-08
A 10-MAY-08
A 10-MAY-08
A 11-MAY-08
A 12-MAY-08
A 13-MAY-08
A 13-MAY-08
A 14-MAY-08
A 15-MAY-08
S FIRST_TIM
- ---------
A 16-MAY-08
A 18-MAY-08
A 19-MAY-08
A 19-MAY-08
A 19-MAY-08
A 20-MAY-08
A 20-MAY-08
A 21-MAY-08
A 22-MAY-08
A 23-MAY-08
A 24-MAY-08
S FIRST_TIM
- ---------
A 25-MAY-08
A 26-MAY-08
A 27-MAY-08
A 28-MAY-08
A 28-MAY-08
27 rows selected.
Can you please tell me why all the views are not showing the same dates.
|
|
|
Re: Archives [message #323986 is a reply to message #323975] |
Fri, 30 May 2008 04:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
asangapradeep
Messages: 128 Registered: October 2005 Location: UK
|
Senior Member |
|
|
you didn't do the
Quote: | alter system archive log current;
|
when you do that or when a log switch happens you will see 30th on the v$archived_log view. and on the v$log_history
v$log_history keeps the history of all the online logs till it can like i said max i've seen this view show 292 rows.
v$archived_log has archived not yet deleted archive log information. do a
Quote: | backup archivelog all delete input;
|
with rman and you can see how all the rows disappear from v$archived_log view.
|
|
|
Re: Archives [message #323988 is a reply to message #323986] |
Fri, 30 May 2008 04:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
no no i do not want to delete anything.
So everytime i have to do alter system archive log current; everytime a log switch occurs???
|
|
|
|
Re: Archives [message #323992 is a reply to message #323975] |
Fri, 30 May 2008 04:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
asangapradeep
Messages: 128 Registered: October 2005 Location: UK
|
Senior Member |
|
|
log switches occurs when the log files are filled enough and happens automatically.
you can do a log switch with
Quote: |
alter system archive log current;
|
or
Quote: | alter system switch logfile;
|
|
|
|
|
|
Re: Archives [message #323999 is a reply to message #323998] |
Fri, 30 May 2008 04:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
Ok,
Is this what you want ?
SQL> select first_change#, sequence#, first_time from v$log;
FIRST_CHANGE# SEQUENCE# FIRST_TIM
------------- ---------- ---------
3137108 133 30-MAY-08
3080062 131 28-MAY-08
3103067 132 28-MAY-08
SQL> select first_change#, sequence#, first_time from v$archived_log;
FIRST_CHANGE# SEQUENCE# FIRST_TIM
------------- ---------- ---------
2996668 128 25-MAY-08
3018309 129 26-MAY-08
3050252 130 27-MAY-08
3080062 131 28-MAY-08
3103067 132 28-MAY-08
27 rows selected.
I have not pasted all rows as you said.
|
|
|
|
Re: Archives [message #324318 is a reply to message #324015] |
Sun, 01 June 2008 23:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
Hey apologies for late reply
Here is the result for v$log_history, v$Log,v$archived_log.
v$log_history does not show 2nd june neither does v$archived_log only v$log shows the date 2nd june??
SQL> select first_change#, sequence#, first_time from v$log_history;
FIRST_CHANGE# SEQUENCE# FIRST_TIM
------------- ---------- ---------
3137108 133 30-MAY-08
3165642 134 01-JUN-08
134 rows selected.
SQL> select first_change#, sequence#, first_time from v$log;
FIRST_CHANGE# SEQUENCE# FIRST_TIM
------------- ---------- ---------
3137108 133 30-MAY-08
3165642 134 01-JUN-08
3186324 135 02-JUN-08
SQL> select first_change#, sequence#, first_time from v$archived_log;
FIRST_CHANGE# SEQUENCE# FIRST_TIM
------------- ---------- ---------
3050252 130 27-MAY-08
3080062 131 28-MAY-08
3103067 132 28-MAY-08
3137108 133 30-MAY-08
3165642 134 01-JUN-08
Not added all rows as u had mentioned
|
|
|
|
Re: Archives [message #324324 is a reply to message #324319] |
Mon, 02 June 2008 00:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
Sorry for not posting the entire thing..posted the above thing in sheer haste.
SQL> select first_change#, sequence#, to_char(first_time,'DD-MM-YYY HH24:MI:SS')
2 first_time, status, archived from v$log;
FIRST_CHANGE# SEQUENCE# FIRST_TIME STATUS ARC
------------- ---------- ------------------ ---------------- ---
3137108 133 30-05-008 10:08:19 INACTIVE YES
3165642 134 01-06-008 12:11:49 INACTIVE YES
3186324 135 02-06-008 10:07:40 CURRENT NO
SQL> select first_change#, sequence#, to_char(first_time,'DD-MM-YYY HH24:MI:SS')
first_time from v$log_history
2 ;
FIRST_CHANGE# SEQUENCE# FIRST_TIME
------------- ---------- ------------------
3137108 133 30-05-008 10:08:19
3165642 134 01-06-008 12:11:49
134 rows selected.
SQL> select first_change#, sequence#, to_char(first_time,'DD-MM-YYY HH24:MI:SS')
first_time from v$archived_log;
FIRST_CHANGE# SEQUENCE# FIRST_TIME
------------- ---------- ------------------
3050252 130 27-05-008 09:58:24
3080062 131 28-05-008 10:21:35
3103067 132 28-05-008 12:47:35
3137108 133 30-05-008 10:08:19
3165642 134 01-06-008 12:11:49
|
|
|
|
Re: Archives [message #324344 is a reply to message #324327] |
Mon, 02 June 2008 01:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
Many thanks,it is clear now
Please have a look at this
SQL> select bytes/1024, first_time from v$log;
BYTES/1024 FIRST_TIM
---------- ---------
102400 30-MAY-08
102400 01-JUN-08
102400 02-JUN-08
SQL> select blocks*block_size/1024/1024 mbytes, first_time from v$archived_log
MBYTES FIRST_TIM
---------- ---------
.3984375 27-MAY-08
.150878906 28-MAY-08
.415527344 28-MAY-08
.434570313 30-MAY-08
.057128906 01-JUN-08
If i want to check the size in MB of archived logs which one of them would be the apt view and the query?
|
|
|
|
Re: Archives [message #324348 is a reply to message #324345] |
Mon, 02 June 2008 02:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sdhanuka
Messages: 173 Registered: March 2008
|
Senior Member |
|
|
Ok thanks michel
hey one more thing.
When we query the view v$log_history it shows all the archived files since the db installation but v$archived_log shows me files from 4th may onwards. I had installed Oracle 9i on 4th march 2008.
Why so ?
[Updated on: Mon, 02 June 2008 02:10] Report message to a moderator
|
|
|
|
|