Schedule a task to update TNS files

When you have a central file that controls workstation behavior (such as Oracle database locations), you want to automatically copy it down to the workstations each day, so you use a scheduled task.  You use SCCM to deploy the task.  Here are the steps

Requirements: 
	Source network folder name (TBD)
		File1=SQLNET.ORA
		File2=TNSNAMES.ORA	
	Destination folder on workstation is "C:\Oracle\product\network\admin"
	System environment variable on workstation: TNS_ADMIN=C:\Oracle\product\network\admin
	Service account username/password (TBD)
		- Task launches under security context of a network service with permission to read form the network location
		- Username/password are stored in clear text within the script "CreateScheduledTask.bat"
	
SCCM Package: "Download Database Control Files"
	Program: "CreateScheduledTask"
		- Run: "CreateScheduledTask.bat"
		- Script which creates the Scheduled Task	
		
	"DownloadDatabaseControlFiles.bat"
		- Script is launched by the scheduled task
		- Store this script in the same folder as the ORA files
		
Package Script: "CreateScheduledTask.bat"
	- Package source location: \\ORSCCM02\Sources\Software\DownloadDatabaseControlFiles
	- Package behavior:
		- Copy DownloadDatabaseControlFiles.bat from package to workstation TNSNAMES.ORA location
		- Create scheduled task on workstation
		- Execute the scheduled script to set the initial environment
			
Schedule Task: "Synch database control files"
	- Runs batch script "CopyDBControlFilesFromServer.bat"
	- Launches every day at 11:00am plus random delay (1-30 minutes) to reduce impact on file server
	- Uses security context of a service account
		- Service account rights: Log on as a batch job on local machines
			- set through Group Policy
		- Service account permissions: Read from the network location of .ORA files
			- set in file share/NTFS on server
		
Scheduled Script: "CopyDBControlFilesFromServer.bat"
	- Script location: "C:\Oracle\product\network\admin"
	- Script behavior: 
 		- check for source files - abort with error if not found
		- confirm script can change to correct drive location - abort with error if not
		- confirm script (self) is in the correct location, copy into  place if not.
		- copy 2 files from network to local drive
		- verify files are identical - abort with error if differences found

Script for creating task – run this through a SCCM package deployment

[Batch Script]
@Echo off
REM ===========================================================================
REM  CreateScheduledTask.bat v.1.0.1.0
REM ===========================================================================
REM  Purpose: Deploy a scheduled task to run every day at 11AM + (1 to 30 minute random delay)
REM 		  Task launches script to copy files from network to local drive
REM  Date: 7/27/2017
REM  Author: Roger C
REM ***************************************************************************

Set TNSPath=C:\Oracle\product\network\admin
Set TaskName="Download database control files"

Set /A minute=%RANDOM% * 30 / 32768 + 1
Set Hour=11
Set minute=00%minute%
Set StartTime=%Hour%:%minute:~-2%

Copy DownloadDatabaseControlFiles.bat "%TNSPath%"

schtasks /create /f /sc Daily /tn %TaskName% /tr "%TNSPath%\DownloadDatabaseControlFiles.bat" /st %StartTime% /ru domain\user /rp "Password"
if errorlevel==1 Exit /B 1

REM Run the batch file once to set the initial environment
cd "%TNSPath%"
Call "DownloadDatabaseControlFiles.bat"

Script for downloading the files – run this daily using a scheduled task

@Echo off
REM =========================================================================
REM  CreateScheduledTask.bat v.1.0.1.0
REM =============================================================================
REM    Purpose: copy database control files from network location to local drive (current folder)
REM Deployment: Run daily as a scheduled task on workstation
REM     Author: Roger C 7/27/2017
REM *************************************************************************

Set SourcePath=\\sccm02\Sources\Software\DBFiles
Set File1=SQLNET.ORA
Set File2=TNSNAMES.ORA

Set TNSPath=C:\Oracle\product\network\admin
SETX /M TNS_ADMIN %TNSPath%

if not exist  %SourcePath%\%File1% Exit /B 1
if not exist  %SourcePath%\%File2% Exit /B 1

if not exist "%TNSPath%" md "%TNSPath%">nul 2>nul
c:
cd %TNSPath%>nul 2>nul
if not "%cd%"=="%TNSPath%" Echo ABORT: Cannot change to directory "%TNSPath%" & goto :EOF
if not exist "%TNSPATH%\%~n0%~x0" copy "%~d0%~p0%~n0%~x0" "%TNSPATH%\%~n0%~x0">nul 2>nul

Copy "%SourcePath%\%File1%" "."
Copy "%SourcePath%\%File2%" "."

FC "%SourcePath%\%File1%" "%File1%" >nul
if errorlevel==1 Exit /B 1

FC "%SourcePath%\%File2%" "%File2%" >nul
if errorlevel==1 Exit /B 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s