-1

We are taking data cut from one DB and dumping it to another DB. During this period, we are deleting our user ID to access Oracle. Once loading is done, user ID is again created with same password to access DB.

However, my file - which contains this user ID to access the DB - is not being executed by Windows Task Scheduler.

I scheduled the task to occur every 5 minutes and perform it indefinitely whether user is logged in or not.

Could you please tell me why WTS is not working once user ID is enabled again?

TylerH
  • 20,799
  • 66
  • 75
  • 101
aman usrete
  • 25
  • 4
  • If you're not already doing so, you should be logging the output of your batch file so you can see why its failing when run from WTS. Could it be that Oracle is somehow blocking connections due to all the failures while the used was deleted? – TripeHound Aug 17 '18 at 09:21

1 Answers1

0

As this is a .BAT file (at least, that's what I understood), you can run it from the operating system command prompt. I suggest you to do it:

  • drop the user
  • start your "data cut" operation
  • run the .bat manually and see the message you got - should be ORA-01017: invalid username/password; logon denied
  • once loading is done, recreate the user
  • run the .bat manually again and observe the message you get now. Which one is it?

Make sure that .bat doesn't exit the command prompt session (either comment the EXIT command, or put PAUSE at the end of the script).

However, why are you dropping the user? Isn't that a little bit too much? You have to not only create it, but grant all privileges it had before. Are you sure you perform that step too? Depending on what that user does, there might be different privileges granted to it, as well as privileges that user grants to other users. Mind database links etc.

Why don't you simply revoke create session from that user? The effect (regarding your .bat script) will be just the same - it won't be able to establish connection). Once you're done, grant that privilege back.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Yes it is .bat file and batch is working fine once getting back user id either performing through windows task scheduler or command prompt manually but my task is to perform it through scheduling.the whole process is automated and loading and user deletion etc work is doing by another team ,I am coming in picture once loading is done and user id is created. Once user id is created for access successfully for Oracle SQL developer task scheduler must run the task 'on Next run time' but it is not starting it.every time it show next run time but not started task. – aman usrete Aug 17 '18 at 06:24
  • Why do you keep mentioning "Oracle SQL developer"? It is just a tool to access the database. I don't understand what it has to do with the whole story. As of the Windows Task Scheduler: I'm not saying that you should *not use it*, but to check what's going on doing it *manually*. – Littlefoot Aug 17 '18 at 06:28
  • manually it is working fine, no issue but I want to schedule it and once I get access it should work fine.We dont know when we get access so we scheduled it and it will trigger in interval of like 15 minutes and once it get access process will begin. – aman usrete Aug 17 '18 at 06:41
  • Does the scheduled task work at all? I mean, before you drop that Oracle user? For example: task is scheduled to run every 5 minutes, so it works all day long. Then you drop the user and task stops working (as it can't connect to the database). Then you recreate the user and expect the task to start working again, but it does not? – Littlefoot Aug 17 '18 at 06:54
  • Yes, task worked fine(before user was dropped). your understanding is correct. – aman usrete Aug 17 '18 at 06:59
  • So, try not to **drop** the user, just **revoke create session** from it. I know that it is done by "another team"; see if they can modify that step. Honestly, I don't know whether it'll help or not, but currently I don't have any other ideas. – Littlefoot Aug 17 '18 at 07:03
  • I am creating an batch file that will call my main batch file, so in every condition whether i have access of DB or not task scheduler will call this batch file and initiate task and once this batch file is initiated it will call my main batch file. – aman usrete Aug 17 '18 at 10:26
  • start path_of_main_batch_file/Main_batch_file_name.bat – aman usrete Aug 17 '18 at 10:27
  • So, START is the keyword? Oh well. I' glad you fixed it! Thank you for letting us know. – Littlefoot Aug 17 '18 at 10:32