Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle export

SOLVED
Go to solution
Giada Bonfà
Frequent Advisor

Oracle export

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.
8 REPLIES
Steve Steel
Honored Contributor

Re: Oracle export

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
If you want truly to understand something, try to change it. (Kurt Lewin)
Ian Lochray
Respected Contributor
Solution

Re: Oracle export

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
Exalted Contributor

Re: Oracle export

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com

Re: Oracle export

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
Advisor

Re: Oracle export

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

Re: Oracle export

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor

Re: Oracle export

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
LE_1
Advisor

Re: Oracle export

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