Switching user to oracle to connect Oracle 11g DB with 'sysdba'

Shell Programming and Scripting
1

I need to connect my Oracle 11g DB from shell script with 'sysdba' permissions. To do this I have to switch user from 'root' to 'oracle'.
I've tried the following with no success.

  su - oracle -c "<< EOF1
        sqlplus -s "/ as sysdba" << EOF2
        whenever sqlerror exit sql.sqlcode;
        set echo off 
        set heading off
    
        shutdown immediate
        startup mount
        alter database archivelog;
        alter database open;
        
        exit;
        EOF2
    EOF1"

I'm getting the following error.

> [root@vEMS-23 devel]# ./enableArchiveLogs.sh en ERROR: ORA-01031:
> insufficient privileges
> 
> 
> SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS
> {SYSDBA|SYSOPER|SYSASM}] where <logon>  ::=
> <username>[/<password>][@<connect_identifier>] [edition=value] | /
> SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS
> {SYSDBA|SYSOPER|SYSASM}] where <logon>  ::=
> <username>[/<password>][@<connect_identifier>] [edition=value] | /
> SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting
> SQL*Plus [root@vEMS-23 devel]#

As far as I may understand it fails to authenticate as 'sysdba'. It doesn't occurs if I eliminate 'su - oracle -c' and run script as an oracle user initially.

Any clues?
Thanks in advanced.

2

Hi,
there are some syntactical errors with quoting and the here-documents. Try this:

su - oracle -c 'sqlpus -s / as sysdba <<EOF
        whenever sqlerror exit sql.sqlcode;
        set echo off 
        set heading off
    
        shutdown immediate
        startup mount
        alter database archivelog;
        alter database open;
        
        exit;
EOF
'

The terminating delimiter of there here-doc has to be the only string in its line and not indented (unless you use <<- ).

3

Works like a charm! Tons of thanks.