Home » RDBMS Server » Server Administration » Archives
Archives [message #323975] Fri, 30 May 2008 03:29 Go to next message
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 #323976 is a reply to message #323975] Fri, 30 May 2008 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From Reference:
Quote:
V$LOG_HISTORY displays log history information from the control file.

Quote:
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).


I don't know how you can compare the results of your 2 queries. At least, query the same values.

Regards
Michel
Re: Archives [message #323977 is a reply to message #323975] Fri, 30 May 2008 03:45 Go to previous messageGo to next message
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 messageGo to next message
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 #323979 is a reply to message #323975] Fri, 30 May 2008 03:48 Go to previous messageGo to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
how big your online redo logs? has any log switches occurred since 28th?
Re: Archives [message #323980 is a reply to message #323979] Fri, 30 May 2008 03:51 Go to previous messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 #323990 is a reply to message #323985] Fri, 30 May 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post SCN along with time with TIME part.
Post only the rows around the differences, it is impossible to compare several dozens of lines on a page.

Regards
Michel
Re: Archives [message #323992 is a reply to message #323975] Fri, 30 May 2008 04:13 Go to previous messageGo to next message
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 #323995 is a reply to message #323990] Fri, 30 May 2008 04:16 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
I dint quite get it what you want me to post michel
Re: Archives [message #323998 is a reply to message #323995] Fri, 30 May 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- views contain first_change# (add sequence#)
- a date contains a time part

You see differences in the output, put just the rows around these differences.

Regards
Michel
Re: Archives [message #323999 is a reply to message #323998] Fri, 30 May 2008 04:24 Go to previous messageGo to next message
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 #324015 is a reply to message #323999] Fri, 30 May 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now add v$log_history (isn't it part of your question?) and time part of date.
Also, as you post v$log, post status and archived columns.

Regards
Michel
Re: Archives [message #324318 is a reply to message #324015] Sun, 01 June 2008 23:58 Go to previous messageGo to next message
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 #324319 is a reply to message #324318] Mon, 02 June 2008 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sequence 135 is not history it is current (as you'd see it if you have posted status and archived columns from v$log as I requested, and there is still no time part ih your ouput, don't you know the usage of TO_CHAR function or NLS_DATE_FORMAT parameter?).

Regards
Michel
Re: Archives [message #324324 is a reply to message #324319] Mon, 02 June 2008 00:38 Go to previous messageGo to next message
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 #324327 is a reply to message #324324] Mon, 02 June 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you can see by yourself what I said.

Regards
Michel
Re: Archives [message #324344 is a reply to message #324327] Mon, 02 June 2008 01:48 Go to previous messageGo to next message
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 #324345 is a reply to message #324344] Mon, 02 June 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The second one.
The first one gives you the online log files size not the archived ones.

Regards
Michel
Re: Archives [message #324348 is a reply to message #324345] Mon, 02 June 2008 02:07 Go to previous messageGo to next message
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

Re: Archives [message #324352 is a reply to message #324348] Mon, 02 June 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It shows all the rows up to the limit you define at database creation time. It is a circular area in control file.

Regards
Michel
Re: Archives [message #324360 is a reply to message #324352] Mon, 02 June 2008 02:40 Go to previous message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Alright. Thank you
Previous Topic: job_scheduler
Next Topic: insufficient prev
Goto Forum:
  


Current Time: Sun Dec 04 21:03:48 CST 2016

Total time taken to generate the page: 0.23736 seconds