Re: Ideas for exporting

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Thu, 8 Nov 2001 14:56:32 -0600
Message-ID: <9sercv$bjs$1@news.gte.com>

Chuck,

Using a single script in a central location, you can export all of your UNIX based databases via remote shell.

Assuming that you'll use a set of common command line arguments for the exp command across the different versions, the only thing you need to work out is the proper environment for each. Once you've setup the remote shell access between your central server and all of your UNIX dbms servers, something like the following script might work for you.

I'm assuming that the user that executes the script has an EXTERNALLY identified userid of the same name defined in each target instance and that this user has the proper permissions to execute a full export  EXP_FULL_DATABASE ). Further, I would not recommend using this to get ROWS=Y exports remotely unless you write to a pipe and compress the output on the fly.

Sorry, I can't help you with the Windows environment, although I know there are utilities available that will allow remote execution of commands. MKS toolkit comes to mind, but I'm sure there are other, free ones out there.

Hth...

<------------------SCRIPT BEGIN ----------------------->

:
#!/bin/ksh
#
#
# Crude script to execute remote exports between
# UNIX boxes.
#
#

# list of node/instance pairs to export

REMOTE_INSTANCES="node1:sid1 node1:sid2 node2:sid1 node1:sid3"

# local bin as defined during Oracle install ( root.sh )
LBIN=/usr/local/bin

# Where to find common unix commands on remote node
BINDIR=/usr/bin

TEMPFILE=/tmp/tempfile_$$.dmp
TODAY=`date +%m%d%y`

for DBINFO in $REMOTE_INSTANCES
do

        TARGET_HOST=`echo $DBINFO |cut -d: -f1`
        TARGET_SID=`echo $DBINFO|cut -d: -f2`
        LOCAL_DUMP_FILE=${TARGET_HOST}_${TARGET_SID}_${TODAY}.dmp
        LOCAL_LOG_FILE=${TARGET_HOST}_${TARGET_SID}_${TODAY}.stderr
        echo "dumping db $TARGET_SID on $TARGET_HOST...\n"

        # run exp on remote node with proper environment
        remsh $TARGET_HOST \
                "ORAENV_ASK=NO; ORACLE_SID=$TARGET_SID;\
                 . $LBIN/oraenv; exp userid=/ rows=n file=$TEMPFILE " \
                2> $LOCAL_LOG_FILE > $LOCAL_DUMP_FILE

        # get the dump file
        rcp ${TARGET_HOST}:$TEMPFILE $LOCAL_DUMP_FILE

        # cleanup temp file on remote node
        remsh $TARGET_HOST "$BINDIR/rm $TEMPFILE"

done

<--------------------SCRIPT END ---------------------------->

-Kevin

"Chuck" <chuck_hamilton_at_yahoo.com> wrote in message news:9se9pc$12t01d$1_at_ID-85580.news.dfncis.de...

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> My boss has asked me to write a shell script that will go out nightly
> to every server and do a no rows export of each database. This is for
> the purpose of backing up DDL, grants, users, passwords, etc. I'm
> looking for ideas that you think would be the best way to do this.
>
> I have a table that stores the Net8 service name of every instance
> and originally I thought I'd just query that in a script and do an
> export over the network for each one. The problem with that is that
> there are numerous versions of Oracle installed and I don't have a
> server that has all versions of the export utility. If I write the
> script to just do local databases, I can get to the right version of
> exp okay, but then I've got about a dozen copies of the script to
> maintain, some for unix and some for windows.
>
> How would you go attack this problem? Is there a better way?
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBO+qqc2m5A4kkb4ZsEQIjGQCdHlbxPPDSWMLPqWe9qQkSMt8sqD4An1O3
> 2BLGFWRbWQZqUpnN0BtqcIgI
> =/+G/
> -----END PGP SIGNATURE-----
>
>
>
>

Received on Thu Nov 08 2001 - 14:56:32 CST