?
Solved

script to create database dumps of multiple tables with different dumpfiles.

Posted on 2012-03-26
8
Medium Priority
?
377 Views
Last Modified: 2012-03-28
Hi Guys,
I have following script export  multiple tables with different dumpfiles.
Please suggest me where I am wrong.


#!/bin/bash
set -x
 if [ $# -lt 3 ]; then
  echo "usage : $0 <sid> <owner> <tab>"
 exit 1
fi
sid=$1
. ~/dbst.env $sid

owner=$2
tab=$3

dumpfile=/allant/"${sid}_${owner}_${tab}.dmp"
 logfile=/allant/"${sid}_${owner}_${tab}.log"

exp "/ as sysdba" log=$logfile   file =$dumpfile tables=${owner}.${tab} rows=yes indexes=no consistent=y statistics=none
0
Comment
Question by:dba1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37766886
You cannot export "as sysdba".
:p
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37766897
PS: Create an externally identified user:
CREATE USER OPS$ORACLE
  IDENTIFIED EXTERNALLY
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role
  GRANT DBA TO OPS$ORACLE;
  ALTER USER OPS$ORACLE DEFAULT ROLE ALL;

Open in new window

and execute exp like this:
exp / log=$logfile file =$dumpfile tables=${owner}.${tab} rows=yes indexes=no consistent=y statistics=none

Open in new window

0
 

Author Comment

by:dba1234
ID: 37767032
I have problem with the parameters. Please suggest on them.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37767130
Perhaps remove the space(s0 between the equal "=" signs. like in:
... file =$dumpfile ...
#-Here--^ 

Open in new window

0
 
LVL 35

Expert Comment

by:johnsone
ID: 37767177
Since when can you not export as SYSDBA?  That used to be a requirement of transportable tablespaces.

I recall you had some hoops to jump through to get the quotes correct.

In fact, current documentation still references the fact that you can (and need to in certain situations):

http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#BABFAAIF
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37767233
Invoking Export as SYSDBA
SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA except in the following situations:

   
At the request of Oracle technical support
   
When importing a transportable tablespace set

Which is not the case here...
Perhaps I should have posted "should not" instead.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 37767387
OK, I'll agree with "should not export as SYSDBA".  However, to do what is trying to be accomplished here, you need a pretty powerful user.  The user would at least need the EXP_FULL_DATABASE role.  Using SYSDBA would just be easier, probably not the best way, but easier.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 37768149
Since I'm definitely not a master of UNIX/Linux shell scripting syntax, I prefer to keep this as simple as possible.  For me that means putting all of the Oracle export parameters into a parameter file that "exp" can read when it is called, and that I find easy to edit, understand and/or test, and that leaves me with very simple, one-line syntax at the command line:

$ORACLE_HOME/exp parfile=/home/oracle/scripts/exp.ctl

Here is an example of a parameter file (named: "exp.ctl") that "exp" can read when it is called:

(This one will have "exp" split the output into up to four files, each one almost 2GB in size.)

userid=exp_user/exp_user
file=(/export/exp_LDB1/exp_1.dmp,/export/exp_LDB1/exp_2.dmp,
/export/exp_LDB1/exp_3.dmp,/export/exp_LDB1/exp_4.dmp)
filesize=1980M
log=/var/ora_logs/exp.log
full=Y
rows=Y
indexes=Y
constraints=Y
grants=Y
consistent = Y
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question