Oracle export

[Giada Bonfà]

Hi,
I've to make a database full export. Before issuing :

exp system/passws file=EXP.DMP full=y buffer=300000

do I have to open the database in restrict mode?
What happens if some users are working on the db while I'm making the export?

Will my export be good?

Thank you.

[Steve Steel]

Hi


I would try to have no users active when I export and even limit the access.

see

http://www.orafaq.org/faqiexp.htm

EXPORT:
Set the BUFFER parameter to a high value (eg. 2800000)
Make sure all applications on the site has been closed
Make sure the export file is on a physical drive not used by oracle
DO NOT export to a NFS mounted filesystem. It will take forever.

Steve Steel

[Ian Lochray · Accepted solution]

Here is an extract from Oracle's documentation describing the "consistent" parameter of the "exp" command. (I hope the formatting is OK).
CONSISTENT
Default: N

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the export command. You should specify CONSISTENT=Y when you anticipate that other applications will be updating the target data after an export has started.

If you specify CONSISTENT=N (the default), each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.

The following chart shows a sequence of events by two users: USER1 exports partitions in a table and USER2 updates data in that table.

Time Sequence USER1 USER2
1
Begins export of TAB:P1


2

Updates TAB:P2
Updates TAB:P1
Commit transaction

3
Ends export of TAB:P1


4
Exports TAB:P2





If the export uses CONSISTENT=Y, none of the updates by USER2 are written to the export file.

If the export uses CONSISTENT=N, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, USER2's transaction is only partially recorded in the export file, making it inconsistent

[Steven E. Protter]

Its always better to have no activity going on when you export, but you don't have to open the database in exlusive mode to get a good export.

SEP

[Gene Kornacki_3]

I've run into problems with export files on very busy db's. 100 + users at a time. If your activity is low you can get away with it. Just use consistent=y if this is your case.

[LE_1]

If you use CONSISTENT=Y,
be careful about Rollback (with database activity)
or you'll get a error
'Snapshot too old'

[Yogeeraj_1]

hi,

hope that this export is NOT your way of backing up of the database!

The only real backup you can make is using RMAN.

RMAN is real easy to configure and takes care of any consitency issues as long as you are running in Archive log mode.

if you need any further help, let us know.

Hope this helps!
Best Regards
Yogeeraj

[Graham Cameron_1]

Agree with most that EXP/IMP is not an ideal way of backing up but it is certainly better than nothing, and I have seen it used in many businesses.
There are several other ways, of which RMAN is the best, but not the only. For example, where I currently work, we shutdown the database and do file copy at the O/S level. (Management still believe that this is a supposed 24x7 environment !!)

-- Graham

[LE_1]

Export is not a "real" backup of the database bc
it's a program after all, you can have bugs...

Export is useful when you want to restore one table that a user has deleted for example

RMAN is nice because it doesn't backup empty database blocks... and it checks block corruption