Nakuru1234
asked on
Full Database Export
I would like to do a cutoff full backup/export of a individual database instances that reside in one of the box. Please can you advice on the FULL_EXP_DATABASE syntax? Thx.
Cheers!
V.
Cheers!
V.
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?
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?
ASKER
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.
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.
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.
ASKER
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.
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.
You need an authenticated login user account for selected schema here, else you will not able to access it and export it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just change the connect like to username@database...removi
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.
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.
ASKER
abuckheit,
There is no c:\ in unix...so what should it be in unix? Thx.
SQL> create directory dumpdir as 'c:\path\';
Cheers!
V.
There is no c:\ in unix...so what should it be in unix? Thx.
SQL> create directory dumpdir as 'c:\path\';
Cheers!
V.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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 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.
ASKER
I got this message when trying to do a datapump...please does anybody know why? Any advice...thx.
Cheers!
V.
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.
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.
ASKER
I did a mkdir dumpdir and the directory was created. But when I went to execute this command...
expdp username/password@database
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.
expdp username/password@database
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
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
try use EXP command in your command prompt, and follow the steps/options to backup all there?