Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Export every day

Re: Export every day

From: Howard J. Rogers <>
Date: Fri, 12 Jul 2002 07:34:02 +1000
Message-ID: <agktiv$jl7$>

"Stephan" <> wrote in message news:fNlX8.51204$
> Hi,
> Besides a cold backup I perform an user-export every day.
> I ask myself: when data is inserted/deleted at the time the export is
> running, what can happen?

Nothing. Export is doing selects. You do selects whilst other people are working on the database without trouble, don't you? Export is no different.

> Or do I have to 'freeze' te database when I start the export? Ad if so,

No you don't need to. The beauty of writers and readers not blocking other readers and writers. However, there is a big issue surrounding the use of the CONSISTENT=Y parameter, which you should read up about. The short story is that if you start an export at 10.00am, and it runs for half an hour, with CONSISTENT=N, you will get the data from table A at 10.00am, table B at 10.04, table C at 10.08 and so on. Each table will be consistent in its own right, but each table will be 'snapshotted' at a different time from all the others. Usually, that's not a particular problem, but there are concerns about logical consistency that can arise from that practice. Setting CONSISTENT=Y means table A is snapshotted at 10.00am, *and so is every other table involved in the export*. Unfortunately, since the export takes half an hour, it means that export needs to be able to find up to half an hour's rollback information available to generate an image of table Z *as it used to be* at 10.00am. If that rollback is not available, the export will fail with a 1555 Snapshot Too Old error.

So, CONSISTENT=N is usually OK, but CONSISTENT=Y is available, but requires lots of rollback space to be in use by the transactions taking place on the database during the export.

Otherwise, there is no need to worry about the fact that data is changing whilst you are doing your export.

> I heard about archive mode?

Yes.... and so??? If you value your data, and mustn't lose any of it, then you need to be in archivelog mode, however you take your backups, hot or cold. If you can always re-key your data, and don't mind losing all data since the time of your last cold backup or export, then you don't need to be in archivelog mode. Most production databases would definitely want to be in archivelog.

It is a *huge* subject, and I used to be able to point you to a paper of mine that would explain it. But I can't now, although there are copies of it floating around (maybe someone else will chip in with a link).

The VERY short answer is that you get your database into the MOUNT state, and issue the ALTER DATABASE ARCHIVELOG command. You can then open the database fully. You also need to arrange for archives to be produced automatically, and to a specified location... so your init.ora needs to contain the parameters LOG_ARCHIVE_START=TRUE and LOG_ARCHIVE_DEST=C:\somewhere_you_want_to_put_the_logs. Of course, if you change the init.ora, you need to startup again to get the changes read and recognised.

That is enough information to get you started, but as I said... there's a LOT more to be said on the subject.

> Thanks
> Stephan
Received on Thu Jul 11 2002 - 16:34:02 CDT

Original text of this message