[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-02-26
13
Medium Priority
?
4,372 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 59

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 59

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 59

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 59

Accepted Solution

by:
Bill Prew earned 2000 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 59

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 59

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 59

Expert Comment

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

~bp
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

872 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