spool /dev/stderr is giving error

lakshmi@Linux · 04-04-2017, 06:39 AM

Hello Members,
I have a unix script, which need to be implemented in Linux.
In that we have a soft link /tmp/stderr.lst created for /dev/stderr and trying to spool data to /tmp/stderr.lst

Code is like below:

FILE_STDERR=/tmp/stderr.lst
ln -s /dev/stderr "$FILE_STDERR"

And in SQL section:
SPOOL $FILE_STDERR

But it is failing with the below error:
O/S Message: No such device or address
Done some investigation and found out that spool /dev/stderr is giving error like below:
SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"

I am able to redirect my output to /dev/stderr on command line:
echo "test msg" > /dev/stderr
test msg

But spooling only getting failed.
Could you please help.

Thanks & Regards
Lakshmi

TB0ne · 04-04-2017, 08:48 AM

Quote:

Originally Posted by lakshmi@Linux

Hello Members,
I have a unix script, which need to be implemented in Linux.
In that we have a soft link /tmp/stderr.lst created for /dev/stderr and trying to spool data to /tmp/stderr.lst

Code is like below:

FILE_STDERR=/tmp/stderr.lst
ln -s /dev/stderr "$FILE_STDERR"

And in SQL section:
SPOOL $FILE_STDERR

But it is failing with the below error:
O/S Message: No such device or address
Done some investigation and found out that spool /dev/stderr is giving error like below:
SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"

I am able to redirect my output to /dev/stderr on command line:
echo "test msg" > /dev/stderr
test msg

But spooling only getting failed. Could you please help.

No, we really can't help...mainly because you haven't actually posted the script, and we can't help you troubleshoot anything based on two lines of code (not in CODE tags, either), that don't relate to any Linux commands. You don't say what unix this was written on, what version/distro of Linux you're migrating TO, or provide us any details that would let us help you.

Read the "Question Guidelines" link in my posting signature, post the ENTIRE script (in CODE tags), and provide details, and we will be happy to try to help.

Jjanel · 04-05-2017, 01:38 AM

Quote:

O/S Message: No such device or address
...
SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"

What OS&version? OracleDBversion? Ideas: Post output from (like):
SQL> system ls -lL /dev/stderr
(that might be MySQL; for SQL, maybe host or ! or \!)
Log a support case with your SQL vendor. strace -f -o <file> sql

'follow' ls -l /dev/stderr links, BUT 'inside' SQL, 'things' may be *different* !

I've never used SQL/DB (&don't 'heavily' know Linux), so LQguru's input welcome.
(tho exploring this has been a 'good learning experience' for me!)
Discovery: here's obscure stuff about: mysql closing/socket/... (&read here !)
"/dev/stderr pointing to a socket will *always* return -ENXIO. This is by design."

Code:

$ egrep -w '6|13' /usr/include/asm-generic/errno-base.h
#define ENXIO            6      /* No such device or address */
#define EACCES          13      /* Permission denied */

p.s. *NOT 'it'* but interesting: su - user leaves pts owned by prior id!
$ id > /dev/stderr
uid=1000(alice) ...
$ su - mysql #I changed its shell&pwd!
Password:
$ id > /dev/stderr
bash: /dev/stderr: Permission denied
$ ls -lL /dev/stderr ;# == ls -l `tty`
crw--w----. 1 alice tty 136, 0 Apr 5 09:42 /dev/stderr
$ id #see: /proc/self/loginuid who am i/whoami logname who -m
uid=27(mysql) ...

lakshmi@Linux · 04-06-2017, 04:31 AM

Thanks for the response TB0ne & Jjanel,

I can not provide the complete script here as that is a quite big script and that will be a security violation as that script belongs to my client.

But I can abstract that code and replicate the error with the below piece of code, I hope this will be useful for the identification of issue.

I have removed the db credentials:

Code:

#!/bin/ksh

SQLPLUS="$ORACLE_HOME/bin/sqlplus"
DIR_TEMP=/tmp
FILE_STDIN=$DIR_TEMP/stdin.sql
FILE_STDERR=$DIR_TEMP/stderr.lst
typeset SELECTSTMT
OUTFILE=output_file.txt
ERROR_RTCODE="<<NON_ZERO_RETURN_CODE>>"
SELECTSTMT="select sysdate from dual"

DB_USER=
DB_PASS=
DB_SID=$ORACLE_SID
CONNECT_STRING=${DB_USER}/${DB_PASS}@${DB_SID}

create_link() {
   typeset source=$1
   typeset target=$2
   #
   # If the link does not already exist or a file by
   # the same name exists, attempt to remove the file
   # and create the link.
   #
   if ! [[ $source -ef $target ]]
   then
      rm -f "$target" && ln -s "$source" "$target"
   fi
}

 if ! create_link /dev/stdin  "$FILE_STDIN"  ||
       ! create_link /dev/stderr "$FILE_STDERR"
    then
        echo "ERROR: Could not create symbolic links!" 1<&2
        return 1
    fi

 {
    "$SQLPLUS" /NOLOG "@$FILE_STDIN" <<EOF || echo "$ERROR_RTCODE SQL*Plus"
    WHENEVER OSERROR EXIT FAILURE;
    WHENEVER SQLERROR EXIT FAILURE;
    CONNECT $CONNECT_STRING
    SET ECHO ON;
    SET VERIFY OFF;
    SET ARRAYSIZE 50;
    SET PAGESIZE 0;
    SET FEEDBACK ON;
    SET TAB OFF;
    SET HEADING OFF;

    SET TRIMSPOOL OFF;
    SET LINESIZE 200;
    SET WRAP ON;
    SET RECSEP OFF;
    SET ECHO OFF;
    SET TERMOUT OFF;
    SPOOL $FILE_STDERR
    ${SELECTSTMT}
/
    SPOOL OFF;
    SET ECHO ON;
    SET TERMOUT ON;
    EXIT
EOF
    } 3<&1 1<&2 2<&3 3<&- |\
   ( cat>$OUTFILE || echo "$ERROR_RTCODE cat" 1>&2 )

echo "wc_record_count == $(wc -l $OUTFILE)" 1>&2

Below is the output from the execution of script :

Code:

Connected.
SQL>     SET VERIFY OFF;
SQL>     SET ARRAYSIZE 50;
SQL>     SET PAGESIZE 0;
SQL>     SET FEEDBACK ON;
SQL>     SET TAB OFF;
SQL>     SET HEADING OFF;
SQL>
SQL>     SET TRIMSPOOL OFF;
SQL>     SET LINESIZE 200;
SQL>     SET WRAP ON;
SQL>     SET RECSEP OFF;
SQL>     SET ECHO OFF;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<<NON_ZERO_RETURN_CODE>> SQL*Plus
wc_record_count == 1 output_file.txt

And the output_file.txtx contains the below message:

Code:

O/S Message: No such device or address

And below are the details of my system:
Linux : Red Hat Enterprise Linux Server release 7.2 server

And DB is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Thank you in advance

Regards
Lakshmi

TB0ne · 04-06-2017, 08:59 AM

Quote:

Originally Posted by lakshmi@Linux

Thanks for the response TB0ne & Jjanel,
I can not provide the complete script here as that is a quite big script and that will be a security violation as that script belongs to my client. But I can abstract that code and replicate the error with the below piece of code, I hope this will be useful for the identification of issue.

Not at all, if this isn't the same script.

Quote:

I have removed the db credentials:

Code:

#!/bin/ksh

SQLPLUS="$ORACLE_HOME/bin/sqlplus"
DIR_TEMP=/tmp
FILE_STDIN=$DIR_TEMP/stdin.sql
FILE_STDERR=$DIR_TEMP/stderr.lst
typeset SELECTSTMT
OUTFILE=output_file.txt
ERROR_RTCODE="<<NON_ZERO_RETURN_CODE>>"
SELECTSTMT="select sysdate from dual"

DB_USER=
DB_PASS=
DB_SID=$ORACLE_SID
CONNECT_STRING=${DB_USER}/${DB_PASS}@${DB_SID}

create_link() {
   typeset source=$1
   typeset target=$2
   #
   # If the link does not already exist or a file by
   # the same name exists, attempt to remove the file
   # and create the link.
   #
   if ! [[ $source -ef $target ]]
   then
      rm -f "$target" && ln -s "$source" "$target"
   fi
}

 if ! create_link /dev/stdin  "$FILE_STDIN"  ||
       ! create_link /dev/stderr "$FILE_STDERR"
    then
        echo "ERROR: Could not create symbolic links!" 1<&2
        return 1
    fi

 {
    "$SQLPLUS" /NOLOG "@$FILE_STDIN" <<EOF || echo "$ERROR_RTCODE SQL*Plus"
    WHENEVER OSERROR EXIT FAILURE;
    WHENEVER SQLERROR EXIT FAILURE;
    CONNECT $CONNECT_STRING
    SET ECHO ON;
    SET VERIFY OFF;
    SET ARRAYSIZE 50;
    SET PAGESIZE 0;
    SET FEEDBACK ON;
    SET TAB OFF;
    SET HEADING OFF;

    SET TRIMSPOOL OFF;
    SET LINESIZE 200;
    SET WRAP ON;
    SET RECSEP OFF;
    SET ECHO OFF;
    SET TERMOUT OFF;
    SPOOL $FILE_STDERR
    ${SELECTSTMT}
/
    SPOOL OFF;
    SET ECHO ON;
    SET TERMOUT ON;
    EXIT
EOF
    } 3<&1 1<&2 2<&3 3<&- |\
   ( cat>$OUTFILE || echo "$ERROR_RTCODE cat" 1>&2 )

echo "wc_record_count == $(wc -l $OUTFILE)" 1>&2

Below is the output from the execution of script :

Connected.
SQL> SET VERIFY OFF;
SQL> SET ARRAYSIZE 50;
SQL> SET PAGESIZE 0;
SQL> SET FEEDBACK ON;
SQL> SET TAB OFF;
SQL> SET HEADING OFF;
SQL>
SQL> SET TRIMSPOOL OFF;
SQL> SET LINESIZE 200;
SQL> SET WRAP ON;
SQL> SET RECSEP OFF;
SQL> SET ECHO OFF;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<<NON_ZERO_RETURN_CODE>> SQL*Plus
wc_record_count == 1 output_file.txt

And the output_file.txtx contains the below message:
O/S Message: No such device or address

And below are the details of my system: Linux : Red Hat Enterprise Linux Server release 7.2 server And DB is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

You were specifically asked to put your code within CODE tags; why didn't you?? Makes it much harder to read otherwise. And if your client is using RHEL7 and Oracle 11g, you are in a well-supported configuration...have you contacted either Red Hat or Oracle support with these two commercial, pay-for products?

Try putting "set -x" at the beginning of your script, and run through it line by line, trying the commands to see where it fails. If you can't/won't post the script, there isn't a lot we can tell you. Your 'client' can provide you the contact information for RHEL and Oracle. And are you running this as root?

lakshmi@Linux · 04-06-2017, 09:35 AM

Thank you..

Changed my script to code tags.

This is the main logic of the script and the remaining stuff is framing a big sql query and the obtaining the inputs for the variables and some formatting logic etc.
But I am very much sure that the issue is with this block and the output from this script is given in my previous message.

As per the forum guide lines I have done the possible analysis from my end as forum is not meant for doing my home work.

The same script is executing fine in Unix and spooling is happening fine, But the same is failing in Linux.

If you can provide me any clue to find out this issue that will be a great help, so that I can find the solution from my end.

Any how I will be trying with set -x and will try to find out the root cause for the issue.
Thank you for the help

Regards
Lakshmi

TB0ne · 04-06-2017, 11:25 AM

Quote:

Originally Posted by lakshmi@Linux

Thank you..
Changed my script to code tags.

This is the main logic of the script and the remaining stuff is framing a big sql query and the obtaining the inputs for the variables and some formatting logic etc. But I am very much sure that the issue is with this block and the output from this script is given in my previous message.

As per the forum guide lines I have done the possible analysis from my end as forum is not meant for doing my home work. The same script is executing fine in Unix and spooling is happening fine, But the same is failing in Linux. If you can provide me any clue to find out this issue that will be a great help, so that I can find the solution from my end.

Any how I will be trying with set -x and will try to find out the root cause for the issue.

You still don't say what version/distro of Linux you're using, and it also does not appear that you have done basic troubleshooting on your end. And again, why have you not contacted Oracle support for help with the Oracle software you're using?

Your script just says "spool"...have you tried specifying the full path to that command, wherever Oracle installed it??

Jjanel · 04-06-2017, 05:10 PM

IF this would demonstrate (and provide minimum exact steps for anyone to reproduce) issue/problem:
Please start a new Terminal Login session, and post results inside CODE tags, like:

Code:

login: lakshmi
Password: 
$ sql
SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr" 
O/S Message: No such device or address
SQL>

I'm guessing this is what OP is -trying- to say in #1.

Could some LQ'er who has access to oracleDB try this? (I'm curious whether spool uses sockets, and causes this, for /dev/stderr OR only if sql is done in a shell script OR if other code complexity involved)

lakshmi@Linux · 04-10-2017, 01:13 AM

Hi,

Version of my Linux:
Linux version 3.10.0-327.22.2.el7.x86_64 (mockbuild@x86-030.build.eng.bos.redhat.com) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-4) (GCC) ) #1 SMP Thu Jun 9 10:09:10 EDT 2016

And spool is working fine in my system , I am able to spool to any other files, No issue with the Oracle.

Code:

SQL> spool test_file.txt
SQL> select sysdate from dual;

SYSDATE
-----------
10-APR-2017

SQL> spool off;

As Jjanel mentioned, That is the exact problem
I am unable to spool to /dev/stderr which is working perfectly in Unix

Code:

SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"
SQL>

If this is not allowed in Linux , need to work out on the change of logic in my script
Or if this is related to some permissions , need to act accordingly.

Thanks & Regards
Lakshmi

TB0ne · 04-10-2017, 07:33 AM

Quote:

Originally Posted by lakshmi@Linux

Hi,
Version of my Linux:
Linux version 3.10.0-327.22.2.el7.x86_64 (mockbuild@x86-030.build.eng.bos.redhat.com) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-4) (GCC) ) #1 SMP Thu Jun 9 10:09:10 EDT 2016

That is the version of the kernel. Type in "cat /etc/*release*" for the actual version. Looks like RHEL 7, so that's a commercial, pay for product, like Oracle is.

Quote:

And spool is working fine in my system , I am able to spool to any other files, No issue with the Oracle.

Code:

SQL> spool test_file.txt
SQL> select sysdate from dual;

SYSDATE
-----------
10-APR-2017

SQL> spool off;

As Jjanel mentioned, That is the exact problem I am unable to spool to /dev/stderr which is working perfectly in Unix

Code:

SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"
SQL>

If this is not allowed in Linux , need to work out on the change of logic in my script Or if this is related to some permissions , need to act accordingly.

Did you look at what you posted? In the example where it's working, you're specifying a file. In the version where it's NOT working, you're specifying an existing Linux DEVICE...not a file. Have you tried specifying a file name instead of a device, to see if it works? If you want to check the permissions of that /dev/stderr device, do an "ls -l /dev/stderr" (same as your other system), and see what they are. Chances are, since it's a system device, it's owned by root, with permissions that don't allow the Oracle user to write to it. Have you tried running the script as root? Set flush off?
https://docs.oracle.com/database/122...L.htm#SQPUG126

Also, you're using Oracle and Red Hat Enterprise 7, both of which are commercial, PAY FOR products...as such, you're paying for support from them both. Have you contacted Oracle support for help with spool??

lakshmi@Linux · 04-10-2017, 08:35 AM

As I mentioned in the previous post, spooling is happening to the normal files (Not to the Linux Device)

Yes, I have already tried with the file name : test_file.txt before starting this thread itself , as mentioned. And is working fine. Below is tried in LINUX only.

Code:

SQL> spool test_file.txt
SQL> select sysdate from dual;

SYSDATE
-----------
10-APR-2017

SQL> spool off;

There is no issue with spool command as it is working fine with the Normal files (from initial post on wards I am saying the same), I suppose Oracle will support in case if the spool is not working at all, but here that is not the case.

Yes I have already check the permissions for /dev/stderr, this is a soft link created for /proc/self/fd/2 and having the permissions : lrwxrwxrwx

I didn't have the root permissions so I can not run the script as root.

I have already provided the version details, you can find them in 4th post

Quote:

And below are the details of my system: Linux : Red Hat Enterprise Linux Server release 7.2 server And DB is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

As mentioned earlier , spooling to UNIX DEVICE is happening perfectly and script is executing fine in UNIX, issue is in LINUX only and that too with LINUX DEVICE, Normal files are getting spooled very well in LINUX also.

Code:

SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"
SQL>

And please note that I am very much new to Linux and to this Forum as well, That's why I am here for help.
And we are in the analysis phase of our project where we can not ask for the support as the project is not yet signed off. That's is the reason why I am trying to get this done through this forum.

If this can not be sorted out here and support team only needs to find out the solution, please post the same thing. So that I will close the thread and will check for the alternatives.

And also requesting you to use the some kind words in post, so that the forum will be approachable to new members.

Sorry If I wrote any thing wrong. I wrote here what I feel as a new member.

Thanks & Regards
Lakshmi

sundialsvcs · 04-10-2017, 09:26 AM

In Linux, /dev, /sys, and /proc are all "virtual filesystems," created by Linux for ease of reference. You cannot create files there.

TB0ne · 04-10-2017, 10:50 AM

Quote:

Originally Posted by lakshmi@Linux

As I mentioned in the previous post, spooling is happening to the normal files (Not to the Linux Device)
Yes, I have already tried with the file name : test_file.txt before starting this thread itself , as mentioned. And is working fine. Below is tried in LINUX only.

Code:

SQL> spool test_file.txt
SQL> select sysdate from dual;

SYSDATE
-----------
10-APR-2017

SQL> spool off;

There is no issue with spool command as it is working fine with the Normal files (from initial post on wards I am saying the same), I suppose Oracle will support in case if the spool is not working at all, but here that is not the case.

Wrong...Oracle will support you with any/all issues with the software you purchase, and they should be your first/best point of contact.

Quote:

Yes I have already check the permissions for /dev/stderr, this is a soft link created for /proc/self/fd/2 and having the permissions : lrwxrwxrwx
I didn't have the root permissions so I can not run the script as root.

The device it points to is owned by root, with 700 permissions. You can't overwrite that.

Quote:

I have already provided the version details, you can find them in 4th post

Indeed..my apologies for overlooking it.

Quote:

As mentioned earlier , spooling to UNIX DEVICE is happening perfectly and script is executing fine in UNIX, issue is in LINUX only and that too with LINUX DEVICE, Normal files are getting spooled very well in LINUX also.

Code:

SQL> spool /dev/stderr;
SP2-0606: Cannot create SPOOL file "/dev/stderr"
SQL>

And please note that I am very much new to Linux and to this Forum as well, That's why I am here for help. And we are in the analysis phase of our project where we can not ask for the support as the project is not yet signed off. That's is the reason why I am trying to get this done through this forum.

Sorry, that's wrong as well. Doesn't matter what internal stage your project is in as far as support for commercial products go. Either you're in the trial/demo license phase, or you've purchased it. Either way..you are fully supported by both Red Hat and Oracle. You need to contact Oracle.

Quote:

If this can not be sorted out here and support team only needs to find out the solution, please post the same thing. So that I will close the thread and will check for the alternatives.

Your alternative is to write to a file, and have your script read that file...from what you've posted, you already know that's an alternative, and that you've tested at least part of this.

Quote:

And also requesting you to use the some kind words in post, so that the forum will be approachable to new members.

Request whatever you'd like; no one posted anything 'unkind' to you.

!!! · 04-10-2017, 12:32 PM

Code:

SQL> ! ls -lL /dev/stderr

Post result.

Also try your echo test>/dev/stderr ***inside*** SQL. You seem knowledgable, so I believe you will find a way to try this, using the host/system/! pass-to-shell sql concept. Also, see man for meaning of uppercase L ls switch.