'Connecting to Oracle DB from shell script with 'sysdba'

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 ::= [/][@] [edition=value] | / SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where ::= [/][@] [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?

I've got an answer:

The answer is pretty simple. I just a bit messed with syntax. The following is the working piece of code:

su - oracle -c 'sqlplus -s / as sysdba <<EOF
   whenever sqlerror exit sql.sqlcode;
   set echo off 
   set heading off

   shutdown immediate
   startup mount
   alter database noarchivelog;
   alter database open;

   exit;
EOF
'
shelloracle11g


Solution 1:[1]

CONNECT chan/testing SELECT * FROM student; EXIT;

save as /shell_with_oracledb_test/r_connection2.sql

write shell file as echo "sqlplus /nolog @/shell_with_oracledb_test/r_connection2.sql" | su - oracle

name this file -> filename.sh

excute filename.sh file

This is sample code.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chan Myae Thu

Related Questions