Full Database Export

What is your Oracle version?

try use EXP command in your command prompt, and follow the steps/options to backup all there?

exp help=y will give you all the options for export.

exp user=your_user full=y consistent=y file=/path/to/export.dmp

That is probably the quick and easy answer, but you should also think about what is the purpose for doing this and how will you get the data back if you need it?

sjawales - the purpose is to be able to restore the data incase I need it. Therefore what is the best plan to take...please advice? Also on the file=/path/to/export.dmp I assume I need to creat a new Dir for the dump file...is that correct? Thx.

Cheers,
V.

You don't necessarily need to create a new directory, that is up to you.  Stick the file where ever you want to, assuming it has space.  I was just using a generic example.

From a restore perspective, be careful...  I wouldn't want to necessarily rely on an export as my sole means of disaster recovery of a production system.  If you're just dealing with a test/dev system and need to get back bits and pieces as needed and you don't particularly care about it otherwise, then I'm sure export will be fine.

Yes...am dealing with a dev, qa, & test only. But dev and qa are very important because of the developers projects. So if I use this syntax:

exp user=your_user full=y consistent=y file=/path/to/export.dmp

that should do it? I don't have to give the password or database instance name? Thx.

Cheers!
V.

>>I don't have to give the password or database instance name?

You need an authenticated login user account for selected schema here, else you will not able to access it and export it.

For Unix, I would not recommend abuckheit's solution - anyone doing a ps -ef will be able to see your database schema password.

Using the original syntax I gave you (modified by @database after the your_username if the database is not local) will prompt you for the password

On a whole, from a security standpoint, it's bad practice to invoke and command line tool in Unix with "tool user/pass" because of the password being visible in a process listing.

just change the  connect like to username@database...removing the /password and it will prompt you for password and keep it hidden

that will secure your pw from being visible.

by the way, this is the 10x zone so i assume you are using 10g or greater.  Export Datapump is only available for 10 and up.. if your not, my exp commands will still work for any version.

>>the purpose is to be able to restore the data incase I need it.

We'll need to know the possible cases in order to help you.  What will you need to recover from?

Export should be good for qa/test but it's only as good as the last export.

You may need to recover to a point-in-time.  exp won't work for this.  If you need to 'reset' a database to a known baseline on a regular basis, I would probably do a cold-backup and just keep restoring to that.  

You might also read up on the 'flashback' features of Oracle.  In 10g, you can flashback an entire database to a specific point in time.

abuckheit,

There is no  c:\   in unix...so what should it be in unix? Thx.

SQL> create directory dumpdir as 'c:\path\';

Cheers!
V.

abuckheit:

I have created a dumpdir in unix.

$ mkdir dumpdir
$ ls
dumpdir
$ ls -al
total 0
drwxr-xr-x   3 oracle   dba             256 Mar 05 09:00 .
drwxr-xr-x   8 oracle   dba             256 Mar 02 20:53 ..
drwxr-xr-x   2 oracle   dba             256 Mar 05 09:00 dumpdir
$ pwd
/install/backup
$ cd dumpdir
$ pwd
/install/backup/dumpdir

So do  I go ahead now and execute this expdp command?

expdp username/password@database FULL=Y directory=dumpdir dumpfile=fulldump.dmp logfile=fulldump.log

abuckheit,

I don't understand this part;

sql> create directory dumpdir as '/the/path';

replacing /the/path/ with wherever you want the dump file and log file.

I thought I've already referenced dumpfile=fulldump.dmp logfile=fulldump.log...on the command above...is that not all I need to proceed? Thx.

Cheers!
V.

I got this message when trying to do a datapump...please does anybody know why? Any advice...thx.

Cheers!
V.

DataPump is a new export/import feature in 10g.  It is a lot faster than the old exp/imp programs but as you can see, it has some restrictions and requires some setup.

If the database isn't that big and given the problems you are having setting it up, I would probably just use the original exp/imp programs.

What message are you getting?

The command "create directory dumpdir as '/the/path';" is a SQL*Plus command and not a UNIX command.

I did a mkdir dumpdir and the directory was created. But when I went to execute this command...

expdp username/password@database FULL=Y directory=dumpdir dumpfile=fulldump.dmp logfile=fulldump.log

I got an error message saying the directory dumpdir does not exist...with old exp/imp commands you have to give file=/path/file/ whatever the case should be but I don't know if the above command will work as it is...am in the process of doing the export on the test database but I would be happy to use data pump since its much faster...any suggestions? Will be greatly appreciated...thx.

Cheers!
V.

You should create the directory only from sql prompt,unless otherwise export datapump wil not identify you directory.If you create os level it will not help in data pump.

The directory which you are creating throught sql will not be visible in OS level.it is for oracle to identify the path.


Go thro this link your issue will be sorted out :
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php