I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things.

I have 2 ways I see currently that I can backup a server running 3 Oracle databases for a 3rd party app we have.

  1. I can use Symantec BE 2010 R3's oracle agent. Which I downloaded and configured but it tells me that in order to backup the databases "when it is in NOARCHIVELOG mode, the database must be MOUNTED but not OPEN". So I guess the question here would be how to change the databases to ARCHIVELOG mode so that they can be open when I back them up or how can I pass a command to CLOSE the database prior to backing them up and then OPEN it back up after the backup completes?

  2. I can use the "exp" command. The problem here is that I'm not sure what parameters/options to pass for each DB. The 3rd party has steps to take, but they include choosing "(2) tables" and some other choices that don't seem to be default choices in the EXP command help. For instance here's a screenshot of their exp "choices":

enter image description here

Any assistance with either one of these methods or if there's another/better method, please let me know.


  • 1
    If you don't know much about Oracle, and you're responsible for making sure the database is properly backed up, you may want to hire an Oracle DBA to consult with you to get a backup system in place. Oracle isn't a trivial thing, and, if your business is dependent on the database, it's not a thing to screw around with.
    – cjc
    Apr 19, 2012 at 16:12
  • @cjc - I agree with you. Unfortunately, I'm being taasked with at least asking for "free help" first.
    – TheCleaner
    Apr 19, 2012 at 17:58
  • Ah, can't close it: it has a bounty on it.
    – cjc
    Apr 19, 2012 at 18:17
  • Thanks, I flagged it as well for moving to dba. I didn't realize we had such a site. I know the commands for a full dump, but I'm wanting to follow the 3rd party vendor's recommendation so that I can "do it as they want".
    – TheCleaner
    Apr 19, 2012 at 18:30
  • I forgot to ask: are you interested to see opinions about your management? They put your company at risk. In the end they will blame technology, or you.
    – user953
    Apr 20, 2012 at 5:23

2 Answers 2


if your database is a serious production database, it should be running in archivelog mode, no question about that. This is assuming that users are constantly doing transactions and that you don't want to lose them. If your database is only bulk loaded once a day, an export dump might be a valid solution as backup, if you know how to use it to re-create a new database, once you lost the prod database.

For now, logon as a database administrator on your server and switch to archivelog mode

  1. sqlplus / as sysdba
  2. shutdown immediate;
  3. startup mount;
  4. alter database archivelog;
  5. alter database open;

database is open for production and you can make online backups. This does not mean your database is out of the danger zone, there are still a few things that need to be setup to keep it running without problems.

First thing that will happen is that the filesystem where your archivelog files are written will get filled up, causing a full halt of the database.

best advice: hire a dba to do the job for you, it is easier to make a disaster than to prevent, or even worse, to recover from one. Without the proper config recovery can be close to impossible. There are lots of consultancy firms that offer adhoc services via some pre-paid system. Maybe giving the application to a service provider might be smarter. This al depends on what you do and what kind of protection against disaster[s] you want.

second best: start reading the docs Oracle® Database 2 Day DBA

  • That'll work for me, thanks. I can use this information for leverage if nothing else to help prove that I shouldn't be the one screwing around with the databases.
    – TheCleaner
    Apr 19, 2012 at 19:35
  • A good answer, but I'd like to reinforce what others have said. There's so many things that could go wrong with even the simple procedure above & care must be taken that the archivelogs are dealt with - I daren't even mention rman. Are you even paying Oracle for the licenses you're using, given you don't have a DBA?
    – Philᵀᴹ
    Apr 19, 2012 at 19:43
  • @Phil - the product is licensed, or at least it was purchased. This was all done with a sister company 5 years ago, and nobody knows where the license is at this point. They are just now asking IT if we can "back it up".
    – TheCleaner
    Apr 19, 2012 at 19:45
  • It was more a concern on your part. I'm not having a go. Was only thinking that you'd be doomed if anything went wrong ;) Not that Oracle support are fantastic!
    – Philᵀᴹ
    Apr 19, 2012 at 19:49

I've developed a shell script that you can use to export schemas of your database.

You only need to adapt the variables at the beginning of the script and set the schema to be exported at the end of script.

It was tested in HP-UX, Linux, AIX and Solaris. You only need to correct the path for the commands mknod, gzip and split .

Good luck!!!


EXP_PATH="./"; export EXP_PATH;                                         # output directory
DATA="$(date +%Y-%m-%d_%H%M%S)"; export DATA;                           # date component for the file names

ORACLE_SID=ORAPRD                               ; export ORACLE_SID     # oracle instance name
ORACLE_HOME=/u01/app/oracle/product/ ; export ORACLE_HOME    # oracle home
LIBPATH=$ORACLE_HOME/lib                        ; export LIBPATH        #
INFRA=$ORACLE_HOME/infra                        ; export INFRA          #
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252          ; export NLS_LANG       # oracle national language to be used by the exporter
PATH=$PATH:$ORACLE_HOME/bin:                    ; export PATH           # add oracle bin to path

### Function: export isolated schemas
export_owner() {
BKOWNR=$(echo "$BKORIG" | tr '\$' 'S')

EXPFILE=$(echo "$EXP_PATH/exp_${DATA}_${ORACLE_SID}_${BKOWNR}.dmp." | tr "\$" "S"); export EXPFILE
EXPLOGF=$(echo "$EXP_PATH/exp_${DATA}_${ORACLE_SID}_${BKOWNR}.log"  | tr "\$" "S"); export EXPLOGF


/usr/bin/split -b 2000m -a 3 $PIPE_SPLITS $EXPFILE &
/usr/bin/gzip  < $PIPE_CMPRSS > $PIPE_SPLITS &

exp / owner="$BKORIG" file=$PIPE_CMPRSS log=$EXPLOGF buffer=100000 statistics=none consistent=y



export_owner  "SCHEMA_NAME"
  • Script is from xeriff.com/wkx/…
    – Philᵀᴹ
    Apr 20, 2012 at 11:50
  • @Phil - Judging from Mixcoatl's profile, that is his/her website. Apr 26, 2012 at 11:46
  • nice Reply @Mixcoatl :)
    – DevYudh
    Feb 27, 2014 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.