Solved

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

Posted on 2011-02-26
13
3,495 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 51

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 51

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
 
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 51

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

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 51

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 51

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 51

Expert Comment

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

~bp
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now