OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Archives

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Archives [message #323975]

Fri, 30 May 2008 03:29

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?

Report message to a moderator

Re: Archives [message #323976 is a reply to message #323975]

Fri, 30 May 2008 03:40

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #323977 is a reply to message #323975]

Fri, 30 May 2008 03:45

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

Report message to a moderator

Re: Archives [message #323978 is a reply to message #323976]

Fri, 30 May 2008 03:45

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.

Report message to a moderator

Re: Archives [message #323979 is a reply to message #323975]

Fri, 30 May 2008 03:48

asangapradeep
Messages: 128
Registered: October 2005
Location: UK

Senior Member

how big your online redo logs? has any log switches occurred since 28th?

Report message to a moderator

Re: Archives [message #323980 is a reply to message #323979]

Fri, 30 May 2008 03:51

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

Report message to a moderator

Re: Archives [message #323982 is a reply to message #323975]

Fri, 30 May 2008 03:53

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

Report message to a moderator

Re: Archives [message #323985 is a reply to message #323982]

Fri, 30 May 2008 03:59

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.

Report message to a moderator

Re: Archives [message #323986 is a reply to message #323975]

Fri, 30 May 2008 04:04

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.

Report message to a moderator

Re: Archives [message #323988 is a reply to message #323986]

Fri, 30 May 2008 04:11

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???

Report message to a moderator

Re: Archives [message #323990 is a reply to message #323985]

Fri, 30 May 2008 04:12

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #323992 is a reply to message #323975]

Fri, 30 May 2008 04:13

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;

Quote:

alter system switch logfile;

Report message to a moderator

Re: Archives [message #323995 is a reply to message #323990]

Fri, 30 May 2008 04:16

sdhanuka
Messages: 173
Registered: March 2008

Senior Member

I dint quite get it what you want me to post michel

Report message to a moderator

Re: Archives [message #323998 is a reply to message #323995]

Fri, 30 May 2008 04:18

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #323999 is a reply to message #323998]

Fri, 30 May 2008 04:24

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.

Report message to a moderator

Re: Archives [message #324015 is a reply to message #323999]

Fri, 30 May 2008 04:59

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #324318 is a reply to message #324015]

Sun, 01 June 2008 23:58

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

Report message to a moderator

Re: Archives [message #324319 is a reply to message #324318]

Mon, 02 June 2008 00:20

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #324324 is a reply to message #324319]

Mon, 02 June 2008 00:38

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

Report message to a moderator

Re: Archives [message #324327 is a reply to message #324324]

Mon, 02 June 2008 00:43

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you can see by yourself what I said.

Regards
Michel

Report message to a moderator

Re: Archives [message #324344 is a reply to message #324327]

Mon, 02 June 2008 01:48

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?

Report message to a moderator

Re: Archives [message #324345 is a reply to message #324344]

Mon, 02 June 2008 01:50

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: Archives [message #324348 is a reply to message #324345]

Mon, 02 June 2008 02:07

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