File delete/create - Created date does not change

[acl03]

I have a pretty involved script that performs Oracle data pump exports each night. It uses the Run method in the WScript.Shell object to run the oracle export command. The export runs and outputs my Oracle database to a file called FULLEXPORT.DMP.

Before the script runs the oracle export, it does two things:

1. Delete FULLEXPORT_OLD.DMP.
2. Rename yesterday's file to FULLEXPORT_OLD.DMP

This way i can always hold on to the last 2 backups (these files are zipped and uploaded to an FTP server later in the script every day for tape backup).

The problem: The creation date of FULLEXPORT.DMP and FULLEXPORT_OLD.DMP never change, but the modified date does.

How is this possible, as the file gets created one day, renamed the next, and finally deleted on the third day. here is the code i use to do my renames/deletes, followed by the export (there are some references to functions that do not pertain to my question):

'clean up old export files
On Error Resume Next
	objFSO.DeleteFile(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix)
On Error GoTo 0
If objFSO.FileExists(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix) Then
		errors = 1
End If
checkForErrors "Delete of oldest dump file ", dumpFilePrefix & "-OLD" & dumpFileSuffix, errors, NonFatalError

If objFSO.FileExists(exportDir & dumpFilePrefix & dumpFileSuffix) Then
	On Error Resume Next	
		objFSO.MoveFile exportDir & dumpFilePrefix & dumpFileSuffix, exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix
	On Error GoTo 0
End If
If Not objFSO.FileExists(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix) Then
	errors = 1
End If
checkForErrors "Rename of previous dump file to '-old'",null,errors, NonFatalError

If objFSO.FileExists(exportDir & dumpFilePrefix & dumpFileSuffix) Then
	errors = 1
	checkForErrors dumpFilePrefix & dumpFileSuffix & " exists. Cannot perform Oracle Export.", null,errors, FatalError
End If	

'perform oracle export
exportCmd = "C:\oracle\product\10.2.0\db_1\bin\expdp.exe username/password@oracleserver parfile=" & parfileDir & parFilename
logOutput = logOutput & getLogHeader & "About to start Oracle export."
errors = oShell.Run (exportCmd, 0, 1)
checkForErrors "Oracle export",null,errors, FatalError

Any ideas? Thanks in advance.

Thanks,
Andrew

Hard work often pays off over time, but procrastination pays off right now!

 

[Geates]

>The problem: The creation date of FULLEXPORT.DMP and FULLEXPORT_OLD.DMP never change, but the modified date does.

I don't see what the problem is. This makes 100% sense to me. The creation date will never change. If it did, it wouldn't be an accurate property. Does your birthday ever change? Of course not. It wouldn't be your birthday if it did. When you change the name of an object, you are modifying it, not re-creating it.

Your code is also missing a few chars.

'perform oracle export
exportCmd = "C:\oracle\product\10.2.0\db_1\bin\expdp.exe username/password@oracleserver parfile=" & parfileDir & "parFilenamelogOutput=" & logOutput & getLogHeader & "About to start Oracle export."


-Geates

 

[mrmovie]

might want to wrap the exe and the parfile parameter in Chr(34)'s so you dont get into trouble with white space....also seeing as you are shelling out then why not keep things as dynamic as you can and use things like %systemdrive% or %programfiles% etc etc

 

[MasterRacker]

acl03, are you saying the the newly created FULLEXPORT.DMP, has an old creation date? Geates is correct that any individual file should not change creation date just because it's renamed.

If the newly created files are not getting the date of their creation, you could try copying current to old, then deleting current, instead of renaming just to see if it acts differently.



Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[acl03]

Thanks for all the responses everyone...

Right, both files show the same creation date, a few months old, and it never changes.

I understand that the renamed file should still maintain it's creation date, but not the file that was deleted, meaning the oldest creation date among the two files should be from two days ago.

Currently both files show a creation date ih May of 2009.





Thanks,
Andrew

Hard work often pays off over time, but procrastination pays off right now!

 

[Geates]

It sounds as if the file entry isn't being cleared from the index. Almost as if the file isn't ever deleted in the first place. Even though it shouldn't work like this, I suppose it makes some sense because you are placing content in a namespace that is identical to the one that was deleted - aka, modifying? Perhaps if you saved the -old.dmp to another directory and copy it over, it might be seen as a new object and re-create the namespace.

-Geates

 

[acl03]

I don't really mind the creation date staying the same, it just leads me to believe that something may not be working properly. As long as the contents of the file are really replaced with the new export, I don't really care.

Oh...and the logging issue you mentioned above, I am using my own VBS-based logging, so it is really separate lines like this:

'perform oracle export
exportCmd = "C:\oracle\product\10.2.0\db_1\bin\expdp.exe username/password@oracleserver parfile=" & parfileDir & parFilename

logOutput = logOutput & getLogHeader & "About to start Oracle export."

The variable logOutput is used to write a log at the end of the script.


Thanks,
Andrew

Hard work often pays off over time, but procrastination pays off right now!

 

[acl03]

If anyone is interested, this is my complete script...my thanks to many tek-tips users without whom this would not be possible

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' Oracle automated full backup
'' Written by: Andrew Levine   March, 2009
'' 
'' This script performs the following steps:
'' 
'' 1. Creates .par file for use by oracle export (expdp.exe)
'' 2. Cleans up old export files (deletes -old file, renames current backup to -old)
'' 3. Performs oracle export
'' 4. Compresses oracle export (.dmp file) to .zip file
'' 5. Sends .zip file to specified FTP server
'' 6. Deletes .zip file if FTP upload was successful
'' 7. Appends results to .log file, emails results to specified addresses
''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set oShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set WshNetwork = WScript.CreateObject("WScript.Network")
Const ForReading = 1, ForWriting = 2, ForAppending = 8,  FailIfNotExist = 0, OpenAsDefault = -2
Const NonFatalError = 0, FatalError = 1
Dim overallErrorState
Dim logOutput
Dim actionData
Dim workingDir
Dim errors

overallErrorState = 0

serverName = WshNetwork.ComputerName

Function fixNum (x)
	If Len(x) = 1 Then
		x = "0" & x
	End If
	fixNum = x
End Function

Function getFormattedDate
	getFormattedDate = Year(Date) & "-" & fixNum (Month(Date)) & "-"& fixNum (Day(Date))
End Function

Function getLogHeader 
	logTime = FormatDateTime(Now(),3) 
	If Len(logTime) < 11 Then 
		logTime = " " & logTime
	End If
	getLogHeader = vbCrLf  &  " " & getFormattedDate & " " & logTime & "   "
End Function

Function checkForErrors (action, actionData, errorState, isFatal)
	If errorState = 0 Then
		logOutput = logOutput & getLogHeader & action & actionData & " successful."
	ElseIf errorState = 1 Then
			If isFatal = 1 Then
				logOutput = logOutput & getLogHeader & "FATAL ERROR: " & action & actionData  & " failed! SCRIPT ABORTING!!!"
				overallErrorState = 2
				writeLog
			Else
				overallErrorState = 1
				logOutput = logOutput & getLogHeader & "WARNING: " & action & actionData  & " failed!"
			End If
	End If
	errors = 0
End Function


Function writeLog
		'Write Log file
		logOutput = logOutput &  vbCrLf  & String (115, "=")
		Set objTextFile = objFSO.OpenTextFile (logDir & serverName & "_vbs_backup.log", ForAppending, True)
			objTextFile.WriteLine(logOutput)
		objTextFile.Close
		Set objTextFile = Nothing

		'send Email Notification
		emailAddress = "email1@mycompany.com,email2@mycompany.com"
		smtpServer = "192.168.1.11"

		If overallErrorState = 0 Then 
			subjectLine = serverName & " Oracle backup completed successfully."
		ElseIf overallErrorState = 1 Then
			subjectLine = serverName & " Oracle backup completed with warnings. Please review logfile."
		Else 
			subjectLine = serverName & " Oracle backup aborted with a fatal error! Please review logfile."
		End If

		Set objEmail = CreateObject("CDO.Message")
		objEmail.From = serverName & "_Oracle_Export"
		objEmail.To = emailAddress
		objEmail.Subject = subjectLine
		objEmail.Textbody = logOutput & vbCrLf & vbCrLf & "This script (" & Wscript.ScriptFullName & ") was run on " & serverName & "." & vbCrLf & vbCrLf & "To view or modify this job, go to Control Panel-->Scheduled Tasks."
		objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
		objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = smtpServer
		objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
		objEmail.Configuration.Fields.Update
		objEmail.Send
		
		'clean up memory
		Set oShell = Nothing
		Set objFSO = Nothing
		Set WshNetwork = Nothing
		Set objEmail = Nothing
		
		WScript.quit
End Function

logOutput = logOutput &  vbCrLf  & String (115, "=")
logOutput = logOutput & getLogHeader & Wscript.ScriptFullName & " started on " & serverName & "."

workingDir = "H:\ora-dba\NightlyBackup\"
parfileDir = "H:\ora-dba\parfiles\"
exportDir = "H:\ora-dba\exports\"
logDir = "H:\ora-dba\logs\"

shortDate = Year(Date) & fixNum (Month(Date)) & fixNum (Day(Date))

'create par file
parFilename = serverName & "_expfull.par"
dumpFilePrefix = serverName & "_FULLEXPORT"
dumpFileSuffix = ".DMP"
parDateTime = getFormattedDate & "_" & Int(Timer)
parOutput = "Directory=data_pump_dir" & vbCrLf & "dumpfile=" & dumpFilePrefix & dumpFileSuffix & vbCrLf & "Logfile=log_file_dir:" & dumpFilePrefix & ".log" & vbCrLf & "Job_name=" & serverName & "_" & parDateTime & "_auto_full_export" & vbCrLf & "Full=y" & vbCrLf & "PARALLEL=3"
'''parOutput = "Directory=data_pump_dir" & vbCrLf & "dumpfile=" & dumpFilePrefix & dumpFileSuffix & vbCrLf & "Logfile=log_file_dir:" & dumpFilePrefix & ".log" & vbCrLf & "Job_name=" & serverName & "_" & parDateTime & "_auto_partial_export" & vbCrLf & "SCHEMAS=vjf01" & vbCrLf & "PARALLEL=3"
Set objTextFile = objFSO.OpenTextFile (parfileDir & parFilename, ForWriting, True)
errors = objTextFile.WriteLine(parOutput)
objTextFile.Close
Set objTextFile = Nothing
checkForErrors "Creation of ", parfileDir & parFilename, errors, FatalError

'clean up old export files
On Error Resume Next
	objFSO.DeleteFile(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix)
On Error GoTo 0
If objFSO.FileExists(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix) Then
		errors = 1
End If
checkForErrors "Delete of oldest dump file ", dumpFilePrefix & "-OLD" & dumpFileSuffix, errors, NonFatalError

If objFSO.FileExists(exportDir & dumpFilePrefix & dumpFileSuffix) Then
	On Error Resume Next	
		objFSO.MoveFile exportDir & dumpFilePrefix & dumpFileSuffix, exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix
	On Error GoTo 0
End If
If Not objFSO.FileExists(exportDir & dumpFilePrefix & "-OLD" & dumpFileSuffix) Then
	errors = 1
End If
checkForErrors "Rename of previous dump file to '-old'",null,errors, NonFatalError

If objFSO.FileExists(exportDir & dumpFilePrefix & dumpFileSuffix) Then
	errors = 1
	checkForErrors dumpFilePrefix & dumpFileSuffix & " exists. Cannot perform Oracle Export.", null,errors, FatalError
End If	

'perform oracle export
exportCmd = "C:\oracle\product\10.2.0\db_1\bin\expdp.exe user/password@server parfile=" & parfileDir & parFilename
logOutput = logOutput & getLogHeader & "About to start Oracle export."
errors = oShell.Run (exportCmd, 0, 1)
checkForErrors "Oracle export",null,errors, FatalError

'Zip oracle export file
zippath = ".\izarc\IZARCC.exe"
fileToZip =  dumpFilePrefix & dumpFileSuffix
zipFileName = dumpFilePrefix & "_" & shortdate &".zip"
zipCmd = zipPath & " -a -cx -$"& exportDir & " " & exportDir & zipFileName & " " & exportDir & fileToZip
logOutput = logOutput & getLogHeader & "About to compress export to: " & zipFileName & "."
errors = oShell.Run (zipCmd, 0, 1)
checkForErrors "Creation of ",zipFileName, errors, FatalError

'Create text file instructions for ftp upload
ftpServer = "192.168.1.5"
ftpFilename = workingDir & ftpServer & "_ftp.txt"
ftpUser = "username"
ftpPassword = "password"
ftpFolder = "oracle_dmp"
ftpLogFile = logDir & servername & "_" & ftpServer & "_ftp.log"
ftpFileOutput = "open " & ftpServer & vbCrLf & ftpUser & vbCrLf & ftpPassword & vbCrLf & "cd " & ftpFolder  & vbCrLf & "put " & exportDir & zipFileName & vbCrlf & "quit"
Set objTextFile = objFSO.OpenTextFile (ftpFilename, ForWriting, True)
errors = objTextFile.WriteLine(ftpFileOutput)
objTextFile.Close
Set objTextFile = Nothing
checkForErrors "Creation of FTP instructions ",ftpFilename,errors, FatalError

'Send file via ftp and export results to ftp log file
ftpCmd = "%comspec% /c ftp.exe -s:" & ftpFilename & " > " & ftpLogFile
logOutput = logOutput & getLogHeader & "Starting FTP Upload to " & ftpServer & "."
errors = oShell.Run (ftpCmd, 0, 1)
'check to see if FTP.exe terminated properly
If errors <> 0 Then
	logOutput = logOutput & getLogHeader & "Fatal Error! FTP.exe errored out attempting to upload " & zipFileName & " to " & ftpServer & ". SCRIPT ABORTING!!!"
	overallErrorState = 1
	writeLog
Else
	'Parse FTP log for error/success messages
	Set objTextFile = objFSO.OpenTextFile (ftpLogFile, ForReading, FailIfNotExist, OpenAsDefault)
	ftpResult = objTextFile.ReadAll
	objTextFile.close
	Set objTextFile = Nothing
	
	If InStr(ftpResult, "226 Transfer complete.") > 0 Then
		logOutput = logOutput & getLogHeader & "Uploaded " & zipFileName & " to " & ftpServer & "."
		logOutput = logOutput & getLogHeader & "About to delete " & zipFileName & "."
		On Error Resume Next
			objFSO.DeleteFile(exportDir & zipFileName)
		On Error GoTo 0
  	ElseIf InStr(ftpResult, "File not found") > 0 Then
	   	logOutput = logOutput & getLogHeader & "Fatal Error! FTP Error: File Not Found. SCRIPT ABORTING!!!"
	   	overallErrorState = 2
	   	writeLog
	ElseIf InStr(ftpResult, "cannot log in.") > 0 Then
		logOutput = logOutput & getLogHeader & "Fatal Error! FTP Error: Login Failed. SCRIPT ABORTING!!!"
		overallErrorState = 2
		writeLog
	ElseIf InStr(ftpResult, "Access is denied.") > 0 Then
		logOutput = logOutput & getLogHeader & "Fatal Error! FTP Error: Access is denied. SCRIPT ABORTING!!!"
		overallErrorState = 2
		writeLog
	ElseIf InStr(ftpResult, "Unknown host") > 0 Then
		logOutput = logOutput & getLogHeader & "Fatal Error! FTP Error: Unknown host. (Could not see FTP server.) SCRIPT ABORTING!!!"
		overallErrorState = 2
		writeLog
	Else
		logOutput = logOutput & getLogHeader & "Fatal Error! FTP Error: Unknown. SCRIPT ABORTING!!!"
		overallErrorState = 1
		writeLog
	End If
End If
 
If objFSO.FileExists(zipFileName) Then
		logOutput = logOutput & getLogHeader & zipFileName & " not deleted from " & serverName & "."
		overallErrorState = 1
Else
		logOutput = logOutput & getLogHeader & "Successfully deleted " & zipFileName & " from " & serverName & "."
End If

If overallErrorState = 0 Then 
	logOutput = logOutput & getLogHeader & "Script completed successfully!"
ElseIf overallErrorState = 1 Then
	logOutput = logOutput & getLogHeader & "Script completed WITH WARNINGS."
End If

writeLog

Thanks,
Andrew

Hard work often pays off over time, but procrastination pays off right now!