Oracle Databases - need to export nightly for backup/DR purposes

Question

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":

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

Thanks!

Answer 1

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

Answer 2

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"