Which datatype is used to store video files

To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

You have three options:

• index the date and convert your timestamp to date when querying, eg:

  select * from foo where foo_date=cast(systimestamp as date) from dual;
  

• create a functional index on the date converted to a timestamp with local time zone, eg:

  create unique index i_foo on foo(cast(ts as timestamp with local time zone));
  select * from foo where cast(ts as timestamp with local time zone)=
                          cast(X as timestamp with local time zone);
  

• change the type of your columns of type date to timestamp with time zone or timestamp with local time zone

Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)

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.