Home » SQL & PL/SQL » SQL & PL/SQL » creating batch file along with date
icon2.gif  creating batch file along with date [message #183081] Wed, 19 July 2006 05:39 Go to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
i wanted to concatenate my file name with current date and time whenever the batch file is taking export.i want to know how to extract date and time from DOS prompt.Currently my file name is syr.dmp.i want this file name to be like syr19_07_2006_1530.dmp
i.e.,i want the current date and time to be concatenated with the filename.
currently am using the following code
batch file name is backup.bat
cd\
echo off
c:\oracle\product\10.2.0\db_1\bin\exp.exe userid=scott/tiger@orcl
file=e:\syr.dmp owner=scott
echo on
Re: creating batch file along with date [message #183115 is a reply to message #183081] Wed, 19 July 2006 07:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This should do the trick for ya:
@echo off
setlocal
for /f "tokens=2" %%i in ("%date:/=_%") do set vdate=%%i
for /f "tokens=1,2 delims=:" %%i in ("%time: =%") do set vdate=%vdate%_%%i%%j
c:\oracle\product\10.2.0\db_1\bin\exp.exe userid=scott/tiger@orcl file=e:\syr%vdate%.dmp
 owner=scott
endlocal
echo on

When executed:
c:\> test.bat
c:\oracle\product\10.2.0\db_1\bin\exp.exe userid=scott/tiger@orcl
 file=e:\syr07_19_2006_803.dmp owner=scott
creating batch file along with date and moving it to clients machine [message #183329 is a reply to message #183115] Thu, 20 July 2006 07:01 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
i am takin backup of my archivelog files to archivebackup and i am creating a folder archive with current date and time as you posted yesterday.i am able to move this folder to other disk drives in my own machine.but i am not able to move it to client machines in my network.is it possible?


@echo off
setlocal
for /f "tokens=2" %%i in ("%date:/=_%") do set vdate=%%i
for /f "tokens=1,2 delims=:" %%i in ("%time: =%") do set vdate=%vdate%_%%i%%j
mkdir e:\archive\archive%vdate%
xcopy e:\archive c:\archivebackup\archive%vdate% /E
end local
echo on

this code is creating a new folder name as archive07_20_2006_1701
and it has all the files from the archive.but i want to move this folder to my client machine.
Re: creating batch file along with date and moving it to clients machine [message #183333 is a reply to message #183329] Thu, 20 July 2006 07:17 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use FTP to do it; you can also create a .bat script to do it; something like this: first, a control file:
username
password
lcd
prompt
ascii
put filename.txt
bye
And now the .BAT file:
ftp -v -s:c:\temp\control_file xxx.yyy.zzz.www

pause

exit
You might need to adjust it to suit your needs, but, generally speaking, this *should* be OK.
Re: creating batch file along with date and moving it to clients machine [message #183334 is a reply to message #183329] Thu, 20 July 2006 07:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I assuming you mean you are trying to copy the folder and not move it?

You may need to verify permissions on your client machine. What error are you getting?

You may also want to change your XCOPY command to:
xcopy e:\archive c:\archivebackup\archive%vdate% /e /i /z

this will avoid having to create the directory each time and will copy the files in restartable mode.

[Updated on: Thu, 20 July 2006 07:52]

Report message to a moderator

Re: creating batch file along with date and moving it to clients machine [message #183341 is a reply to message #183329] Thu, 20 July 2006 07:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just to check (I've seen it before on AskTom) - you are aware that a 'backup' from EXP cannot be rolled forwards using the contents of the archive logs.

I may have completely the wrong end of the stick, but from the posts in this thread, it looks like you are taking an export and copying the archive logs at the same time.
Re: creating batch file along with date and moving it to clients machine [message #183343 is a reply to message #183341] Thu, 20 July 2006 08:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
JRowbottom wrote on Thu, 20 July 2006 07:54

it looks like you are taking an export and copying the archive logs at the same time.

Good point, I wasn't sure if the EXP and the copying of the archivelogs were for two distinct purposes or not since the OP's first code showed EXP and the second code showed archivelog.
icon2.gif  Re: creating batch file along with date and moving it to clients machine [message #186051 is a reply to message #183343] Sat, 05 August 2006 04:59 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Hi,

Thanks for your replies.The backup is fine now.It is beautifull concatenating the current date and time with the file.
Daily I'm tanking cold backup of my database.So I want to routine this task daily at 10pm. But I couldn't able to shutdown my oracle database inside the batch script.
Please see the following code
set echo off
set oracle_sid=orcl
sqlplus username="system/manager as sysdba"
shutdown immediate
exit;

In this script login upto system as sysdba.But this script is not shutdown the database.How to do it?Please help?

[Updated on: Mon, 07 August 2006 01:30] by Moderator

Report message to a moderator

Re: creating batch file along with date [message #186054 is a reply to message #183081] Sat, 05 August 2006 06:33 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

I am late, but do wanted to share my views...
what abt this

exp userid=sys/sysadmin123@ora  file=oradbf.dmp full=y
copy/y oradbf.dmp d:\backup\oradbf31."%date%" 


This will take the backup and store it in folder d:\backup\
with file name oradbf31 with current date.

If i am wrong let me know...

Naveen
Re: creating batch file along with date [message #186062 is a reply to message #186054] Sat, 05 August 2006 09:07 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
navkrish wrote on Sat, 05 August 2006 06:33

exp userid=sys/sysadmin123@ora  file=oradbf.dmp full=y
copy/y oradbf.dmp d:\backup\oradbf31."%date%" 


That won't work. You can't have '/' in Windows filenames not to mention it won't include the time component the OP was requesting or be in the format the OP requested.

C:\TEMP>echo %date%
Sat 08/05/2006

C:\TEMP>dir
 Volume in drive C has no label.
 Volume Serial Number is 947B-C791

 Directory of C:\TEMP

08/05/2006  10:03 AM    <DIR>          .
08/05/2006  10:03 AM    <DIR>          ..
08/05/2006  10:00 AM                 0 testdoc.txt
               1 File(s)              0 bytes
               2 Dir(s)   1,693,564,928 bytes free

C:\TEMP>copy testdoc.txt testdoc."%date%"
The system cannot find the path specified.
        0 file(s) copied.

[Updated on: Sat, 05 August 2006 09:31]

Report message to a moderator

Re: creating batch file along with date and moving it to clients machine [message #186073 is a reply to message #186051] Sat, 05 August 2006 09:41 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Amul, the following will work:

echo off
set oracle_sid=orcl
echo shutdown immediate | sqlplus -s "system/manager as sysdba"

If you want to avoid hardcoding the password in the file, you can use the following:
echo off
set oracle_sid=orcl
echo shutdown immediate | sqlplus -s "/ as sysdba"

Instead of the above commands, you could also simply use:
:: Stops the Oracle database and the associated Windows service
net stop oracleserviceorcl

and
:: Starts the Windows service and the Oracle database
net start oracleserviceorcl

If you are going to utilize the net stop/start commands, you may want to verfiy the following values in the registry:

  • ORA_ORCL_SHUTDOWNTYPE should be i (default is i (Immediate))
  • ORA_SHUTDOWN is set to TRUE (default is FALSE)
  • ORA_ORCL_SHUTDOWN is set to TRUE (default is FALSE)
  • ORA_ORCL_AUTOSTART is set to TRUE (default is TRUE)

Previous Topic: Database Domain
Next Topic: Queries and tables
Goto Forum:
  


Current Time: Fri Dec 02 13:51:41 CST 2016

Total time taken to generate the page: 0.08949 seconds