Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks!

share|improve this question

migrated from serverfault.com Apr 19 '12 at 18:35

This question came from our site for professional system and network administrators.

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 '12 at 16:12
    
@cjc - I agree with you. Unfortunately, I'm being taasked with at least asking for "free help" first. –  TheCleaner Apr 19 '12 at 17:58
    
Ah, can't close it: it has a bounty on it. –  cjc Apr 19 '12 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 '12 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. –  ik_zelf Apr 20 '12 at 5:23

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 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!!!

#!/usr/bin/ksh

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/11.1.0.7/db ; 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
LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32     ; export LIBPATH

###------------------------------------------------------------------------------
### Function: export isolated schemas
###------------------------------------------------------------------------------
export_owner() {
BKORIG=${1}
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

PIPE_CMPRSS=${EXP_PATH}/${BKOWNR}_PIPE_TO_GZIP; rm -f $PIPE_CMPRSS; /usr/sbin/mknod $PIPE_CMPRSS p
PIPE_SPLITS=${EXP_PATH}/${BKOWNR}_PIPE_TO_SPLT; rm -f $PIPE_SPLITS; /usr/sbin/mknod $PIPE_SPLITS p

/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

rm -f $PIPE_CMPRSS
rm -f $PIPE_SPLITS

}

export_owner  "SCHEMA_NAME"
share|improve this answer
    
Script is from xeriff.com/wkx/… –  Phil Apr 20 '12 at 11:50
    
@Phil - Judging from Mixcoatl's profile, that is his/her website. –  Nick Chammas Apr 26 '12 at 11:46
    
nice Reply @Mixcoatl :) –  DevYudh Feb 27 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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