Solved

Run Exp and Expdp in a batch file and export .dmp to a compressed RAR File.

Posted on 2011-02-26
13
3,702 Views
Last Modified: 2013-12-01
Hi All

I have an Oracle 10G R2 64Bit Database running on Windows Server 2003 R2 64bit standard edition. I am trying to run the exp and expdp command to export the complete database to a .DMP file.. Currently i am running manually by going to run command, typing exp, then entering username and password, then entering buffersize, then putting the location and then Enter 2 times...

Now, what i want to do is, I want to create 2 separate batch files in which one should trigger the exp command and the other should trigger the expdp command.

1. EXP

this exp command has to answer the username/pwd and then the buffersize and then the location, then it must compress the .dmp to the particular location.

2. EXPDP

this has to compress in rar format and put in a separate location.

Please help.

Thank You
0
Comment
Question by:brandsco
  • 7
  • 6
13 Comments
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34987338
0
 
LVL 1

Author Comment

by:brandsco
ID: 34987528
i tried the following...

@echo off
REM
start D:\oracle\product\10.2.0\db_1\BIN\exp system/manager1 FULL=Y FILE=D:\DB_EXP_BKP\DBDUMP.dmp LOG=D:\BKP_LOGS\DBLog.log CONSISTENT=y

I am able to export properly... in the specified filenames.. is it possible to save the file using the currentdate as the filename??
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34987592
If you do the following at a command line prompt, what does it display (exactly)?

ECHO%DATE%

~bp
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:brandsco
ID: 34987599
it says the following

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\siraj>echo%date%
'echoSat' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\siraj>
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34987613
Very sorry, my bad, you want a space in there, and I see now I mistyped it.  SHould have been:

ECHO %DATE%

(space between ECHO and %DATE%)

~bp
0
 
LVL 1

Author Comment

by:brandsco
ID: 34987621
Ok No problem billprew....

I receive the following


C:\Users\siraj>echo %date%
Sat 02/26/2011

C:\Users\siraj>
0
 
LVL 53

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 34987671
Here's a way to set a variable to the current date (YYYYMMDD format which usually makes sense for sortability), and then use that variable in the naming of the DMP file:

@echo off
REM
set Stamp=%DATE:~-4%%DATE:~-10,2%%DATE:~-7,2%
start D:\oracle\product\10.2.0\db_1\BIN\exp system/manager1 FULL=Y FILE=D:\DB_EXP_BKP\DBDUMP_%Stamp%.dmp LOG=D:\BKP_LOGS\DBLog.log CONSISTENT=y

Open in new window

~bp
0
 
LVL 1

Author Comment

by:brandsco
ID: 34987712
Wow billprew, it worked very well... thnk you very much... could you please tell me what is this variable?? as per my understanding it is something that we set a command to a particular text or string.

i did not understand this %DATE:~-4%%DATE:~-10,2%%DATE:~-7,2%
i understood that 4, 2 & 2 is the no. of characters... but can u please explain me in detail??

Thank You Very Much
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34987938
Let me share a little of how I describe it, and then give you a few links for further info.

In BAT script files, you can assign variables to values, and then reference those values else where in the BAT script.  This can improve readability and also make the scripts easier to maintain.  Something common that you is things like this:

set FromDir=c:\temp\from
set ToDir=c:\temp\dest
REM various logic in the script...
copy "%DromDir%\somefile.txt" "%ToDir%"
del "%DromDir%\somefile.txt"

Open in new window


So we save ourselves a bit of typing, it makes the script a little easier to read since now we have an idea of what the purpose of the folders is, and if we ever change the folder location, we can just change it in once SET line at the top of the script, and the rest of the script we perform properly using the new location.

You also notice that to refer to the contents or value of a variable in the script, we surround it by % signs, as shown above.  That tells the script to get the value of the variable at that point in time and insert it in that place in the command.

There are also a number of built in system variables that are available in BAT scripts.  To get a sense for these do just a SET command with no other parms at a command prompt and notice the list of builtin variables.  DATE and TIME are two of these, and always contain the current data nad time when referenced in a script.

So when you did the ECHO %DATE% command earlier, you were asking to display the current value of the DATE variable, which was shown as "Sat 02/26/2011".  Notice that there is a 3 character day name, and then the actual date with slashes in it.  Depending on location and preference settings in Windows, the format of the DATE variable can vary since some countries want the day before the year, etc.  That's why I asked for a sample from you, so that I knew where each value was position.

So now we had an easy way to access the current date in "Sat 02/26/2011" format, but typically if we are naming files with the date stamp, that's not exactly the format we want. So we can use some advanced capability of the variable referencing capability to essentially grab out substrings from the default date format to build a new variable that has the date formatted the way we like it for this script.

Take a look at SET /? from a command line, as well as the following page, looking at the "Substrings" section:

http://www.robvanderwoude.com/ntset.php

Notice that in addition to referencing the value of a defined variable with the %DATE% format, there are some additional capabilities, in this case substrings.  So we can use a syntax that looks like %DATE:~x,y% where x is the offset of the first character we want to extract, and y is the number of characters (keep in mind for this syntax the x value offset is zero based, so the first character is 0, not 1).

So with your date format, try this command:

ECHO %DATE:~0,3%

You should see "Sat" since we asked to extract the first 3 characters from the string.

There a few more options with this substring method, like if you leave of the y (length) option, it gives the remainder of the string starting at offset y.  In addition, you can make the offset relative to the RIGHT of the string rather than the LEFT by using a negative value for x.  In our case since one thing we wanted was the year as YYYY and our date format was "Sat 02/26/2011", I got those by using %DATE:~-4%.  Here my x offset value is -4, so the negative tells it to start 4 characters from the right or end of the current DATE variable value, and since I didn't specify a y length value, it gets the rest of the characters, in this case the 4 crharacter year.  In the same fashion %DATE:~-10,2% gets the month by moving in from the end of the string 10 positions, and then extracting 2 characters from there.  So the three pieces we extracted were:

%DATE:~-4%
%DATE:~-10,2%
%DATE:~-7,2%

You may wonder why I did this working from the right end of the DATE variable rather than the LEFT end.  The reason for this is because in this fashion if you ever change the date format on your system to not include the day name, this will still work.  So it's ever so slightly more flexible.  Of course if you changed the order of the elements in the actual date, say from MM/DD/YYYY to DD/MM/YYYY then the script would need to be adjusted.

Hope this helps, here are a few links related to BAT scripts if you want to poke further.

http://www.robvanderwoude.com/batchfiles.php
http://ss64.com/nt/
http://www.dostips.com/

~bp
0
 
LVL 1

Author Comment

by:brandsco
ID: 34990430
Billprew. U are great. thank you very very very much for all the information. I really appreciate that.
Can i also ask a small doubt or a question please?
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34991400
Questions are always welcome.

~bp
0
 
LVL 1

Author Comment

by:brandsco
ID: 34991433
billprew, sorry about that i was working around with the links that you have provided and found an effective way to export, move the files to a certain location, compress and then delete the files...

10000000000 thanks to you...
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 34992431
Great, very welcome.  Way to be resourceful!

~bp
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

790 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