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!!!!
