Re: ORA-28009
Date: Mon, 15 Feb 2010 13:16:47 +0000 (UTC)
Message-ID: <hlbhfu$j4b$5_at_solani.org>
On Sun, 14 Feb 2010 14:45:45 -0800, syd_p wrote:
> Hi,
> I am running a shell script which call a perl script which uses DBI to
> connect to a remote (ie another server) which creates schemas ie table
> spaces, users, tables in the waiting DB.
>
> Or rather it does not:
> Database Connection Error: ORA-28009: connection as SYS should be as
> SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin) The problem is as
> follows:
> ./create_stuff.sh <database type> <database name> <host> <username>
> <password> <port>
>
> I ran one of the many scripts locally on the unix command line with the
> hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.
>
> The problem is that the shell script, calls more shell scripts, calls
> perl scripts and passing in "sys" as a parameter fails at the Oracle 10G
> level cos we need the AS sysdba.
> And if I call the script with "sys as sysdba" "bloggs" (or similar
> tried a few things) it goes wrong big time.
>
> Possibly the answer is to create a user that has the powers of "sys as
> sysdba" or something similar. But I am stuck really...
>
> can anyone help plz?
>
> Syd
This is actually a wrong group for DBI questions but nevertheless, the answer is using ora_session mode. Here is an example:
$dsn = "dbi:Oracle:"; # no dbname here
$ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
$dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA}); It has been reported that this only works if $dsn does not contain a SID so that Oracle then uses the value of the ORACLE_SID (not TWO_TASK) environment variable to connect to a local instance. Also the username and password should be empty, and the user executing the script needs to be part of the dba group or osdba group.
Having said all that, it is a bad idea to write Perl scripts running as SYSDBA.
-- http://mgogala.freehostia.comReceived on Mon Feb 15 2010 - 07:16:47 CST