When are Archived Redo Logs generated?

Question

We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data. The rest of schemas are not used.

Archived Redo Logs is activated. What is strange for us is that archived files are generated every day, including Sundays and holidays, when nobody comes to work.

I thought Archived Redo Logs are generated when inserting, deleting, or updating data, never because of select statements.

Is that correct?

Answer 1

Oracle itself will generally generate redo in the process of running various background jobs. For example, by default in 10g, there is a job that gathers statistics on objects. That will generate redo. By default, 10g has a job that gathers AWR information as well every hour. That will generate redo as well. There are a number of other background jobs (checkpointing, updating the SCN to timestamp mapping table, etc.) that are going to be running as well which will be generating redo on an ongoing basis just as a matter of course.

If you run the query

select current_scn, systimestamp 
  from v$database

in a (from a user standpoint) idle database, you'll note that the current_scn is incrementing every few seconds. That is Oracle doing various sorts of housekeeping. That housekeeping will generate a small amount of redo each time. Over the course of a full day, it's pretty common that this would add up to enough for a couple of archived logs even if your system is otherwise pretty idle.

Answer 2

You could use LogMiner to see exactly what the logs contain.

Answer 3

It's also possible for a SELECT statement to generate redo log entries due to delayed block cleanout.

This is covered in the Oracle documentation, however you can also find an Ask Tom article here that covers the topic.