login as: opc
Authenticating with public key "SJM-DBSRV"
Last login: Sat Apr 17 00:53:05 2021 from sjm-appsrv.subnet01281043.vcn01281043. oraclevcn. com
[opc@sjm-dbsrv ~]$ sudo su root
[root@sjm-dbsrv opc]# cd /F/R2/Backup/DBHealth/
[root@sjm-dbsrv DBHealth]# ./OCI.bat
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 17 03:05:11 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
"P2-0310: unable to open file "/F/R2/Backup/DBHealth/Client_OCI.sql
Inside the batch file is the below line-
/C/R2Database/product/12.2.0/db_1/bin/sqlplus sys/*** as sysdba @/F/R2/Backup/DBHealth/Client_OCI.sql
Welcome to the forum ! We hope you enjoy your time here, and find it to be a friendly and helpful place.
Unfortunately there's not a great deal to go on here really, other than the error message itself. The error tells us that the Oracle client is unable to open the file containing the SQL statements that it (presumably) should then attempt to execute.
There are a variety of possible reasons for this, but you might wish to consider things such as the following:
Is the path to the SQL file correct ? You can test this by copying and pasting the exact path to the SQL file that the script uses, and seeing if you can use another command such as ls or cat on it. If you get an error from other commands indicating that the file was not found, then the file does not exist at that path. That might be because the path is wrong, or because the file does not actually exist in that directory.
Do you have permission to read the file ? Again, this will become obvious if you try to read the file with cat or some other command, as it will clearly give you a "Permission denied" error if this is the problem. It might be that you don't have permission to access the directory the file is in, or that you don't have permission to read the file itself.
Does the file contain valid SQL for Oracle ? Once you've verified that it exists at the path the script uses, and that you have permission to read it, does it actually contain valid SQL that Oracle would be able to read and understand ?
I'd start with looking at fundamental things like that, and go from there. Hope this helps ! If you have any further questions, please let us know and we can take things from there.
login as: opc
Authenticating with public key "SJM-DBSRV"
Last login: Sat Apr 17 03:03:26 2021 from sjm-appsrv.subnet01281043.vcn01281043.oraclevcn. com
[opc@sjm-dbsrv ~]$ cd /F/R2/Backup/DBHealth/
[opc@sjm-dbsrv DBHealth]$ ls -al OCI.bat
-rwxrwxrwx. 1 oracle oinstall 603 Apr 17 01:21 OCI.bat
[opc@sjm-dbsrv DBHealth]$ ls -al Client_OCI.sql
-rwxrwxrwx. 1 oracle oinstall 2213 Apr 16 10:14 Client_OCI.sql
[opc@sjm-dbsrv DBHealth]$ cat Client_OCI.sql
set markup html on spool on
spool /F/R2/Backup/DBHealth/Log/Client_OCI.html
Thanks for that. This tells us that the file does exist in the same directory as the script, but doesn't necessarily tell us that the script would be able to find the file. That's the reason I mentioned copy-and-pasting the path the script uses to the file, rather than just looking at it yourself in the location that you know it can be found.
For example, in the output you posted above, the error message mentions that sqlplus cannot find the file /F/R2/Backup/DBHealth/Client_OCI.sql. If you run the command:
ls -l /F/R2/Backup/DBHealth/Client_OCI.sql
what do you get ?
Also, it might be worth just double-checking that the SQL line in the script is exactly what you think it is. Double-check what you get if you do a:
grep Client_OCI.sql OCI.bat
in case the path is actually different from what you expect.
If all of this checks out there are a few other possibilities, but let's look at these first and go from there.
Hello @drysdalk
Thank you for the reply,
Please see the below output
login as: opc
Authenticating with public key "SJM-DBSRV"
Last login: Sat Apr 17 03:27:15 2021 from sjm-appsrv.subnet01281043.vcn01281043.oraclevcn. com
[opc@sjm-dbsrv ~]$ ls -l /F/R2/Backup/DBHealth/Client_OCI.sql
-rwxrwxrwx. 1 oracle oinstall 2213 Apr 16 10:14 /F/R2/Backup/DBHealth/Client_OCI.sql
[opc@sjm-dbsrv /]$ cd /F/R2/Backup/DBHealth/
[opc@sjm-dbsrv DBHealth]$ grep Client_OCI.sql OCI.bat
/C/R2Database/product/12.2.0/db_1/bin/sqlplus sys/R2int001 as sysdba @/F/R2/Backup/DBHealth/Client_OCI.sql
We have two users - opc and oracle , have tried running the same from root as well, but same error.
Have provided all the permission as well - chmod 777 to the file.
If I Copy, Paste the same line in putty it works, but from this batch it breaks.
OK, thanks. That appears to be correct, but there is one more possibility here. There could be characters in that file that we can't see, in particular if the batch file was created on a Windows or other non-UNIX system.
If you run this command:
cat -v OCI.bat
do you see things that look like ^M characters at the end of lines (in particular at the end of the line with the SQL filename in it) ? If so, then that would be the problem, and we'd have to strip these characters out of the file to get things to work. So I'd check that next, and see if perhaps there are characters in there that shouldn't be there.
How should I remove them, through Nano or VI editor?, as I can't see or remove them in windows platform.
Also, if these needs to be copied from Windows to Linux, each time it's copied do I have to edit them?
Edit : I tried nano and VI , i can't see ^M in these editor as well.
Appreciate your replies.
Great - that's what I thought we might find. The reason for this is a difference in how UNIX-style operating systems handle marking the end of a line in a file, as compared to other OS's (particularly those descended from MS-DOS, such as Windows).
What we have to do is strip out those Control-M characters. That's what is actually at the end of of those lines, a single character with an ASCII value of 13, or Control-M (represented on-screen as ^M). And because on a UNIX system they aren't indicators of the end of a line, the shell is treating the ^M's as part of the filename, which is why it keeps on saying it can't find the file.
There's a few ways we can get rid of these, but let's see if the easiest is available to us. Do you have a utility called dos2unix installed on your system ? If you do, then the easiest way to do this is simply to run:
dos2unix OCI.bat
and then everything should work just fine. If you don't have dos2unix installed (and if you can't install it, since if you can that's the best thing to do), then let us know and we can look at other ways of sorting this.
But as a bit of general advice: either create your scripts directly on the system they're going to be run on to avoid this problem in the future, or use a text editor on your Windows system that can save files with UNIX-style end-of-lines.