Oracle 9.2.0 SYSDBA vs. SYSOPER schema required?

[Ralph Grothe]

Hello Sysadmins with DBA background,

I'm currently about to replace a backup shell script by a Perl script but need some Oracle knowledge while I have no DBA in reach whom I could ask.
(the usual thing, they always expect us sysadmins to confess to their inquisitory grillings immediately but are never available when we need info from them ;-)

I need to set tablespaces into the state of backup mode (usually done by something like "alter tablespace BLABLA begin backup".
In the script this was done through a here file construct invoking the sqlplus binary.
Obviously with Perl and DBI I can avoid this mess.
But I got a bit confused when I had a look at the POD of DBD::Oracle again.
There it says that you can (amongst others) supply the connect() constructor with an attribute hash ref to distinguish between the schemas SYSDBA and SYSOPER.
Here's an excerpt from the POD:

Connect Attributes

ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA
authorization and SYSOPER authorization.

$mode = 2; # SYSDBA
$mode = 4; # SYSOPER
DBI->connect($dsn, $user, $passwd, { ora_session_mode => $mode });


Now I wonder which schema I will have to use to acquire a database handle that allows me to set tablespaces into backup mode (and back afterwards), SYSDBA or SYSOPER?

Rgds.
Ralph

[Graham Cameron_1 · Accepted solution]

Either will do what you want.

SYSOPER is the less powerful, so is probably a bit safer.

SYSDBA is a superset of SYSOPER, and allows anything, ilcluding CREATE (and therefore DROP) DATABASE...

As a hp-ux analogy, SYSDBA is like root, and sysoper is like restricted SAM for non-root access.

HTH

-- Graham