Hi Tom
Great help
Thanks,
Our database (9.0.1.0.1) is running in non archive mode
its redo log buffer size is 52 k(532480) and redo log size is 100 mb as default on installation.
Now we want to switch it to archive log mode with reduced redo log size either 1-4 mb,
How to proceed ,is deleteing all the redo group and recreate it of size 1 or 2 mb?any special procedure or precaution.
Is there any need to change the size of redo buffer also from 52 k to .....,or its ok.
Thanks

Hi Tom.
Thanks,
In my previous question u add :-
"The redo log size change (but why goto 1-4meg? that is pretty small) can be
done anytime. "

Is 1-4 mb is not a ideal size for a moderate size databse
with high insert and low updates.is any proformence problem?
Could you please suggest a fair enough redo log size,
also how to know log how frequent the logs are switching.
Thanks
NP

Original question.
Hi Tom
Great help
Thanks,
Our database (9.0.1.0.1) is running in non archive mode
its redo log buffer size is 52 k(532480) and redo log size is 100 mb as default
on installation.
Now we want to switch it to archive log mode with reduced redo log size either
1-4 mb,
How to proceed ,is deleteing all the redo group and recreate it of size 1 or 2
mb?any special procedure or precaution.
Is there any need to change the size of redo buffer also from 52 k to .....,or
its ok.
Thanks


Followup:
changing the log buffer is a simple init.ora change, that can be done anytime
you have scheduled a restart of the db.

The redo log size change (but why goto 1-4meg? that is pretty small) can be
done anytime. Just add N new logs of the size you want, and then drop the old
logs you don't want anymore. You might have to do an alter system switch
logfile to advance the logs off of the old ones but thats it.

Hi Tom
Thanks for previous help.
Recently we switched our Database to achvive log mode and resize redo log from 100 mb to 10 mb and now to 1 mb(presently) with three redo group mirror on 2 disk.

As our production environment is opertae under uneven load 24x 7.
Heavily in Morning (8 to 10 am) and afternoon (2 to 4 pm)
during this period it generate 20 mb of redos.(total 40 mb/day).
I am writing the exact timing of archvival
date time
10/07/02 4.04 pm
11/07/02 1.40 am
11/07/02 9.45 am
11/07/02 11.14am
11/07/02 2.56 pm
redo size (10 mb.
Now my question is that weather creating a larger redo (10 mb to 100 mb) is worth in this case or not.?
Is shifting to 1 mb size is a correct decision?
Is in case of crash / recovery and maintaning standby database smaller redo size is of worth under uneven load?
Thanks

Tom,

Your answer
>>The redo log buffer is flushed
>>
>>o every 3 seconds
>>o when 1/3 full
>>o when you commit.

Say, the contents of the redo log buffer are flushed to the redo log files because of the following 2 reasons
o every 3 seconds
o when 1/3 full
-not because of a commit

after the above scenario(flushing redo log buffer to the redo log files) happens a couple of times and there has been no commit done
We have uncommited data in the redo log files and when a log switch occurs(since the redo log file is full), the contents of the db buffer are written to the db.
a) if we do a rollback at this point what happens?
b) If we don't do a rollback and continue with the changes and if all the redo log files are filled up, what would happen?
c) At what point are the redo logs copied to the archive logs?
d) Since redo logs could contain uncommited data, will the archive logs also have uncommited data?

Thanks in advance for clearing my confusion.

As you said redo to be a replayable history but the most apt
will be replayable restricted log history.Since committed
transactions can be redone but uncommitted transactions can
only be rolled back(the basic principle of ARIES/RRH which means Algorithms for Recovery and Isolation Exploiting Seman
-tics/Repeating Restricted History)
Thanks
R.chacravarthi

For the answer to the reader 'A reader from Houston'

The redo logs are filled up and then copied over as archived redo logs, since redo logs have uncommitted data, this uncommitted goes into archived redo logs. Is that correct?
The uncommitted data present in the arched redo logs is copied over to a standby database. If we logon to the standby database, wouldn't it be showing uncommitted data? What am I missing here?

Thanks a lot.

There is so much activity on this site even on a weekend. Don't you take a break?

Tom,
Are DDL changes made to the redo logs? Is redo and undo maintained for DDL changes?

Since it appears to be impossible to post a question directly, I'll try here. (Been waiting 2 weeks now - still, there's only one Tom!)

We have a database used for some app we didn't develop. Database was set up before I joined the company. According to the DB person who set it up, in Oracle online redo log files always have to be 1MB in size. (I now know this is false.)

I read somewhere that you should aim to have 1 log switch every 15 mins roughtly. (Obviously it varies.) Do you agree with this?

To begin with, we had about 1 log switch per month. Then it went up to 1 per week. Then it went up to as much as 4 per day.

It's still holding at about 4 per day - except that, now and then, I see little "bursts" of activity. Sometimes it appears that up to 5 log switches have occurred in less than 1 second!

Is that even physically possible? (We only have 4 online redo logs - ARCHIVELOG mode of course. ;-)

Speaking to app users (I don't even know what the app is supposed to do!), it appears they gradually fill up the database, but when a "project" is completed, it gets taken out of the database and put into our archives on CD.

I *imagine* this "taking it out of the database" is producing large amounts of redo fairly fast. If the DB went down half way through, Oracle would have to "undelete" all this stuff to get to a consistent state...

Questions:
1. Should I make the online redo logs larger?
2. How much larger?
3. Do I have to switch off the database to do this?
4. How (if at all) will this affect database recovery? (Recommendation from my Oracle book is to take a cold backup any time you do something major to the database.)

Also have questions about hot vs. cold backups, but I'll try to find a more appropreate thread to tack those onto...

Well, that's what I call a fast responce...

We're not having any particular issues that I know about, other than that (my magic book says) reducing the number of log switches might make things go faster. (Not having tried it, I can't really comment.)

What is there to checkpoint? Well, since the peak activity is (presumably) one big delete, I would imagine it's accessing each block only once... (I won't pretend to have a deep understanding of how Oracle works at the block level.)

Since the problem (if it is one!) is rapid log switches (rather than actually running out of online redo), I presume the thing to do is make the logs bigger rather than allocate more of them...

No need to backup? Yup, I like that idea... It's just I'm told certain events are hard to recover through. (e.g., reset logs is supposedly a *very* hard one. Not something I'm hoping to have to do, like, EVER.)

Just curious... the archived logs are sometimes smaller than the online ones. Does the archiver actually "know" how much real data is in there? I had assumed it just does an OS file copy...

But I have seen the archived log files of different sizes without someone doing a switch logfile command

Could it be checkpointing

Dear Tom,
What does the wait event "log file sync" deal with?
Please do reply.
Thanks.

A) the last thing the redo logs contain is a uncommited data.
B) Instance fails
C) startup
D) roll foward: replay the uncommited data
E) roll back the uncommited data
F) everything is ok
H) We have a checkpoint after 3 sec

WE ASK: after H) What are in the redo log files.
An uncommited data only again.
or
the uncommited data + E) the info of the undo generate in the redo log buffers transfers in the redo log files.

tx

A) the last thing the redo logs contain is a uncommited data.
B) Instance fails
C) startup
D) roll foward: replay the uncommited data
E) roll back the uncommited data
F) everything is ok
H) We have a checkpoint after 3 sec

Let say that the uncommited info is a update.

You have goods answers in your books that update generate more redo than insert.

Can you tell us more technical information about the redo generated in each step.

After D,
After E,
After H,

1) Explain us more information about the redo generate in the redo log buffer.

2) Explain us more information about the redo generate in the redo log file.

This information will contain what: pointer, old values, new values, the update instruction ... ?

Hi Tom,
I have a entered my archive log parameters as follows in the init.ora file
/*
log_archive_start=true
log_archive_dest="C:\oracle\oradata\ADMIN\archive"
log_archive_format=arch%t%s%u.arc
*/

But when i query my archive log list i get as follows:
/*
log_archive_start=true
log_archive_dest="C:\oracle\oradata\ADMIN\archive"
log_archive_format=arch%t%s%u.arc
*/

Tx for your answers

----------------------------------------------
if like you said
'Same redo that would be generated when you type rollback '
Here is what we understand about this process.
Is it good
----------------------------------------------

the values before the update:

select tk.val
from tom kyte tk
where tk.id = 1

tk.val is equal to 'LAST SCN COMMIT'

commit;

A) the last thing the redo logs contain is a uncommited data. ( a update )

the update

update tom_kyte tk
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

B) Instance fails
C) startup
D) roll foward: replay the uncommited data
E) roll back the uncommited data
F) everything is ok
H) We have a checkpoint after 3 sec

After step A)

Redo log buffer: We have sufficient infos to replay update. So we will have something like this

update tom_kyte tk -- before the instance fail
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

Redo log file: We have sufficient infos to replay update. So we will have something like this

update tom_kyte tk -- before the instance fail
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

undo tablespace: We have sufficient infos to rollback the update

update tom_kyte tk -- before the instance fail
set tk.val = 'LAST SCN COMMIT'
where tk.id = 1

AFTER STEP C) STARTUP

DATABASE BUFFER CACHE: have nothing
UNDO tablespace have nothing
REDO LOG BUFFER have nothing
REDO LOG FILES have the sufficients info to replay
the update.

update tom_kyte tk -- before the instance fail
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

AFTER STEP D)ROLL FOWARD

DATABASE BUFFER CACHE have infos generated

TK.VAL = 'UNCOMMIT DATA'

REDO LOG BUFFER have infos generated

update tom_kyte tk -- create on roll foward step
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

REDO LOG FILES have the same sufficients info to replay

update tom_kyte tk -- before the instance fail
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

UNDO tablespace have info generated

update tom_kyte tk -- create on roll foward step
set tk.val = 'LAST SCN COMMIT'
where tk.id = 1

AFTER STEP D)ROLL BACK

DATABASE BUFFER CACHE have infos about

TK.VAL = 'LAST SCN COMMIT'

REDO LOG BUFFER have infos generate

update tom_kyte tk -- create on roll foward step
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

update tom_kyte tk -- create on roll back step
set tk.val = 'LAST SCN COMMIT'
where tk.id = 1

REDO LOG FILES have the same sufficients info to replay

update tom_kyte tk
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

UNDO tablespace have info about

update tom_kyte tk -- create on roll foward step
set tk.val = 'LAST SCN COMMIT'
where tk.id = 1

AFTER F) H) ALL EVERY THING IS OK

DATABASE BUFFER CACHE: DBW0 have clean it with LRU algorith
UNDO tablespace have nothing ---> we have reuse the space
DATAFILE: have TK.VAL = 'LAST SCN COMMIT'
REDO LOG BUFFER have nothing ---> LGWR have clean it to redo log files

REDO LOG FILES have ...

update tom_kyte tk -- before the instance fail
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

+ the new redo log buffer info generate by roll foward and roll back

update tom_kyte tk -- create on roll foward step
set tk.val = 'UNCOMMIT DATA'
where tk.id = 1

update tom_kyte tk -- create on roll back step
set tk.val = 'LAST SCN COMMIT'
where tk.id = 1

IS IT CORRECT

I try to put your answers and 2 situation is again on the board.

Witch is the good one in I, J

A) the last thing the redo logs contain is a uncommited data.
B) Instance fails
C) startup
D) roll foward: replay the uncommited data
E) roll back the uncommited data
F) everything is ok

G) Instance fail again
H) startup
I) roll foward: Do we have to replay again the same uncommit data or we just have nothing to do.
J) roll back: Do we have to roll back again the same uncommit data or we just have nothing to do.
K) everything is ok

I try to put your answers and 2 situation is again on the board.

Witch is the good one in I, J

A) the last thing the redo logs contain is a uncommited data.
B) Instance fails
C) startup
D) roll foward: replay the uncommited data
E) roll back the uncommited data
F) everything is ok
We have a checkpoint after 3 sec


G) Instance fail again
H) startup
I) roll foward: Do we have to replay again the same uncommit data or we just have nothing to do.
J) roll back: Do we have to roll back again the same uncommit data or we just have nothing to do.
K) everything is ok



Followup April 29, 2008 - 11am US/Eastern:

I) cannot tell you - did we checkpoint the blocks after E
J) same as I

Yes a check point after E.

tx

if after 1 mb, data get written by LGWR, then what is use of keeping log buffer size more than 10mb.

hi tom!
i need you help for this q. ") You are a DBA who has two major clients. One is called The IEEE (Instant Excitement Erotic Escorts) and the other is a firm of divorce lawyers called Paya, Pan, Short and Wiener. Both have integrated web applications that enable their clientele to schedule appointments and perform other interactions directly against the firms’ databases. Both have been experiencing high transaction volumes, and your ongoing monitoring scheme has picked up on the opportunity to optimize performance by doing some instance tuning. Specifically, the IEEE’s transactional activity mostly consists of INSERTs, especially mid-month. Perhaps coincidentally, Paya, Pan, Short and Weiner have an unusually high proportion of UPDATE and DELETE activity late in the month. Assuming both database systems were set up initially with the default configuration from a Oracle 10g General Purpose template, what tuning measures would you consider for both, and what measures would you consider specifically for each?"

"Silly customer names to, if this were in an interview, I would question the level of the company I was interviewing with. "

There you go Tom, another example of how there's an exception to every rule. You would think there's never a case when you shouldn't explain the acronym you are using....

Maybe he's from an area of Canada where prositution is legal ;)

From Oracle doc:
---------------------------------------
7.4 Configuring and Using the Redo Log Buffer

Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. LGWR begins writing to copy entries from the redo log buffer to the online redo log if any of the following are true:
1-The log buffer becomes one third full.
2-LGWR is posted by a server process performing a COMMIT or ROLLBACK.
3-DBWR posts LGWR to do so.
---------------------------------------

In the case of #2, what if you have 3 sessions doing work at different levels (session "A" doing 10,000 row insert then commit, session "B" doing a 5,000 row delete then commit, session "C" doing a single row insert then commit), will the session "C" force the redo log buffer to write ALL the redo (essentially for all 3 sessions) to the redo logs (potentially causing a "log file sync" if it does it frequently enough)?
I'm "assuming" a single row insert committing every second (or less if its multiple sessions), would be a particularly "bad thing".

Is #3 is in reference to a timed event (the 3 second rule)? What other events would cause DBWR to do this?

documentation:
---------------------------
redo sync writes

Usually, redo that is generated and copied into the log buffer need not be flushed out to disk immediately. The log buffer is a circular buffer that LGWR periodically flushes. Redo sync writes increments when changes being applied must be written out to disk due to a commit.
---------------------------

here is an example
14:37:09 SQL> select name, value from V$SYSSTAT where name like '%redo sync%'

NAME                      VALUE
-------------------- ----------
redo synch writes      78265005
redo synch time       688273425

14:37:10 SQL> /

NAME                      VALUE
-------------------- ----------
redo synch writes      78267623
redo synch time       688297808

14:37:46 SQL>

So, in 35 seconds, redo synch writes incremented by over 2600, thats 75(ish) 'redo synch writes' events PER SECOND, so in this case, is someone literally committing more than 75 times a second?   

Is the sysstat entry of "redo writes" the value when the lgwr writes out on its own?

14:41:07 SQL> select name, value from V$SYSSTAT where name like '%redo writes%'

NAME                      VALUE
-------------------- ----------
redo writes            45835641

14:41:09 SQL> /

NAME                      VALUE
-------------------- ----------
redo writes            45835852

14:41:36 SQL>

Here its like 211 in a 35 second period (or 6 per second).

So, in theory, if the redo is writing out constantly does the v$sysstat "redo write sync" increment in that case?   Here I can see that event increasing rapidly, can I assume this is a direct correlation to users committing far too early (instead of letting the log buffer fill and then the 'redo writes' event increment) ?

Hi Tom,

I am updating a table and when number of affected rows are less it is not generating redo log. But oracle should generate redo log even if one row changed.

In below example when 99 rows changed redo size is 51004 but when 10 rows changed redo size is Zero.


Thanks and regards,

Kuldeep

SQL> desc sqltemp
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR VARCHAR2(16)
SQL_TEXT VARCHAR2(2000)
DISK_READS NUMBER
EXECUTIONS NUMBER
PARSE_CALLS NUMBER
HASHVAL NUMBER
LOADTIME VARCHAR2(19)

SQL> set autotrace traceonly statistics
SQL> update sqltemp set SQL_TEXT=null where rownum<100;

99 rows updated.


Statistics
----------------------------------------------------------
92 recursive calls
113 db block gets
23 consistent gets
18 physical reads
51004 redo size
800 bytes sent via SQL*Net to client
704 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99 rows processed

SQL> roll
Rollback complete.

SQL> update sqltemp set SQL_TEXT=null where rownum<10;

9 rows updated.


Statistics
----------------------------------------------------------
1 recursive calls
11 db block gets
3 consistent gets
0 physical reads
0 redo size <===== zero byte redo generated
805 bytes sent via SQL*Net to client
703 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed <=======

SQL> roll
Rollback complete.

Hi Tom,

In my last post sqltemp table was a non-sys table in a user schema.

New test case is as below. I have created table in my own schema.

----
SQL> create table kultemp as select * from user_objects ;

SQL> set autotrace on

SQL> update kultemp set OBJECT_NAME=rpad('*',128,'*') where rownum<10;

9 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=9 Bytes=594
          )

   1    0   UPDATE OF 'KULTEMP'
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'KULTEMP' (TABLE) (Cost=9 Card=
          1640 Bytes=108240)





Statistics
----------------------------------------------------------
          4  recursive calls
         15  db block gets
         34  consistent gets
          0  physical reads
          <b>0  redo size</b>
        808  bytes sent via SQL*Net to client
        719  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          <b>9  rows processed</b>

SQL> roll
Rollback complete.

Hello Sir,

the above link does not work


Thanks

Hi Tom,

I am a fan of asktom site. Site contains great stuff on oracle and has always been a place to find solution of problem and clarification on concept topics.

With all due respect to this site and Tom may I ask whether the question answered by Tom or by his some aid/assistant who is helping him?

Thanks and regards,

Hi Tom
I have a question for redo apply times.
We have cloned 2 of our databases (both at 10.2.0.2.0 and of size 5TB approx) using offline FS copy on same OS version and now want to keep them synched in mount for a month using redo apply from primary sites.(initSID.ora files were copied too for identical parameters)
sys A has a redolog file size of 600 MB whereas B has that of 100 MB.
On average over a week , we observed sys A takes about 30 sec. for 1 redofile (600M) apply.
and B takes about 2 min of 1 archivedlogfile apply.
Since I am not sure what could exlain this behaviour, pl let me know what other info you'd need before you answer to this.

Greetings Tom

Thankyou for your response.

i meant they have been copied by bringing the DB&applications offline, stopping all activity on the box and copying files by scripts at OS/storage level.

A & B are not same, only except they have similar sizes,
A is bw,OLAP (600M redo) whereas B (100 M redo) is OLTP.(however I checked other imp parameters and they look almost the same too.)

As for underlying setup, both run on identical but individual LPARs carved out of the same server box.

I agree to the scattered v/s concentrated blocks involved in redo changes has atleast part role to play, though I havent proved it.

But my question is , is there any Oracle parameters or settings that could be affecting/tweaked, since as of now it 1 takes over 20 times the log apply times (per MB of redo) as that of other.

Hi Tom,

I have read in one of the articles realted to REDO that -
"Change Vectors for a transaction REDO record - are generated in PGA "


Is it correct ? if so - how it happens in PGA -Could you
please explain about this ? thanks for your time and help

I know chage vector is nothing but description of change made to a block with a particular version in bits and bytes (op_code +dba+version+change values etc)

Thanks

Hello Tom,

we have a production DB with high load it's version is 10.2.0, we use a monitoring software which is insider, it sometimes shows warnings on 'redo buffer allocation retry' as this value reached 5 - 6 and the software recommends to increase log buffer size as this value should be near to 0.
currently we have 3 groups of redo logs with 50MB size and archive log is enabled as we have a standby DB,
log buffer size is around 14MB , and redo files switch every 4 minutes, what is your recommendation for such situation.

Thanks,
Yousef