how to use exp command to export Oracle DB with files in different disk location

Question

Hello, we get problem, while trying to export Oracle DB. OS: CentOS ~ 5.2 DB: Oracle 10g.

Exp command exports db files only in location:

/home/oracle/OraHome_1/oradata/master/xxx.dbf

, but tool can't export files in different location (we know about this files after getting trace) like this:

'/disk1/dblog06.dbf', '/home/disk2/system01.dbf',

Please, advice me, how to get dump file. or buckup it.

Thanks.

Answer 1

I am not sure if that is the question, but the exp command will export database objects according to their logical schema (user name, table name). It does not matter which physical database file the data is coming from.

exp works through an Oracle instance, which needs to have mounted the datafiles.

Are these other files part of the Oracle database? Maybe another database? You need to find out which Oracle server uses them, and then run exp against that instance.

Answer 2

EXPORT is not a backup tool. It is meant for transferring data from one database to another, or perhaps from one schema to another.

If you want to recover your data in the event of a database crash or corruption then you need to use the appropriate tool. There are OS solutions to this, but Oracle comes with a sophisticated backup and recovery tool: RMAN. Find out more.

Answer 3

You appear to have misunderstood what exp does, and particularly what the file parameter is for. The file is the output dump file, normally given a .dmp extension. Export takes data out of the database instance, it does not work under the hood on the datafiles - you have to tell it which data you want (full, user, tables, or tablespaces) and where to put it, not where it comes from.

If you really did try to exp file=/home/disk2/system01.dbf then what you actually asked it to do was trash your database; you're lucky that it did not overwrite the datafile and cause a catastrophic failure. Oracle seems to have saved you from yourself there, though possibly only thanks to having exclusive locks on the files at the time.

You need to read up on how it works and see if it actually does what you want - as APC notes it's not a backup tool. Looks at the Oracle documentation for your version, or somewhere like http://www.orafaq.com/wiki/Import_Export_FAQ, and also look at using data pump instead of exp.