Remotely connecting to database with "sysdba" privilege

Remote login to a database with "sysdba" privileges.

I have two servers lnx133 & lnx144.

Goal is to be on server lnx144 & connect to database on lnx133 with a user having sysdba system privilege.
DB server lnx133 

Details:
lnx133 server has a database "maze"
lnx144 is remote server
password file --> found in $ORACLE_HOME/dbs needs to have an entry for the user with which we intend to remotely login.,

example "system"
On lnx133

SQL> grant sysdba to system;
Grant succeeded.

Below query gives confirmation
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------                     ----- ----- -----
SYS                                     TRUE  TRUE  FALSE
SYSTEM                            TRUE  FALSE FALSE

You can also use strings command to read the binary file.

[oracle@lnx133 dbs]$ strings orapwmaze

]\[Z

ORACLE Remote Password file

INTERNAL

FB16A89DB241980E

c+'spTtN

F3A63E088B7E19ED

#fGF`B

SYSTEM

1A2A51E8BB0B485D

From any remote server ( in our case lnx144), I can use the following syntax to login to maze@lnx133 with "sysdba" privilege.

Note: we would need the entry in tnsnames.ora@lnx144 to be able to resolve to the database 'maze'@lnx133.

[oracle@lnx144 ~]$ tnsping maze

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 25-APR-2013 12:20:30

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = maze)))

OK (0 msec)

Now we are ready to connect to remote database 'maze' on lnx133.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:21:02 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS

----------------                  -------------------------          ------------

maze                       lnx133                                OPEN

So why would we need a user to login with sysdba system privilege? 

Well sysdba system privilege gives user privileges to startup, shutdown, create, drop, alter database (mount, open,  recover, backup, archivelog, restrict session..... )

To test with another user scott who doesn't have "sysdba" system privilege.

[oracle@lnx144 ~]$ sqlplus scott/abc123@maze

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:24:09 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;

ORA-01031: insufficient privileges

SQL> exit

Now lets try the same with a user "system" which has "sysdba" privilge.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:29:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Another important thing to be able to start up database by remote connection is only possible if you have a static entry in listener.ora (lnx133 in our case)... if you don't then you will be greeted by this error.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 13:03:21 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:

ORA-28547: connection to server failed, probable Oracle Net admin error

Update the static entry & reloaded listener, listener.ora now looks as follows

[oracle@lnx133 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

     (SID_DESC =

        (ORACLE_HOME = /u01/app/oracle/product/11g/11.2.0.2/dbhome_1)

          (SID_NAME = maze)

     )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx133)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Ready to start database remotely

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 13:10:25 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             335547288 bytes

Database Buffers           79691776 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS

----------------                 -------------------------          ------------

maze                              lnx133                      OPEN

SQL> sho user

USER is "SYS"

I hope it helps.....administering remote databases!!!!