Svrmgr in SQL Plus

[agasimani]

Oracle says that we can use SQL Plus instead of Server Manager from 8i onwards. But when I try to login to SQL Plus using Internal, it says "Insufficient Priviledges". Why? How can I use SQL Plus instead of Serve Manager?

[padmam]

Hi, 15th April 2001 20:09 hrs chennai

I am able to connect by simply typing "internal" from SQL login window or after logging in to SQL Plus as follows

==What I noticed================
First i logged as sys the results

20:03:48 SQL> select user from dual;

USER
------------------------------
SYS

20:04:06 SQL> conn system/yamuna
Connected.
20:04:17 SQL> connect internal
Connected.
20:04:22 SQL> conn sys
Enter password: *****
Connected.
20:04:35 SQL> conn system/yamuna
Connected.
20:04:44 SQL> select user from dual;

USER
------------------------------
SYSTEM

20:04:50 SQL> connect internal
Connected.
20:05:00 SQL> select user from dual;
USER
------------------------------
SYS

I also noticed from logging through SQL window just type internal with right password or wrong password or without password you will be logged in without error.

To connect as Internal from server it is not even prompting for pasword.

I would like to know whether you are trying to connect fom client system ?

Also Note internal is more over nothing a sys user.

Cheers

Padmam

[amar]

Hi Padmanabhan,

I have a small question:

Can you really connect as "SQL> conn sys" without specifying "as sysdba" or "as sysoper" ? I get ORA-28009
if I try to connect without specifying either "sysdba" or "sysoper".

-amar

[agasimani]

Thanks Padmam for your reply. But the thing I want to know is how to startup the database from SQL Plus. When database is not opened how can one connect using sys or system or any other login name?

[DBAtrix]

connect internal will be (or is) desupported. oracle wants dba's to use "/ as sysdba", e.g.:
-----------------------------------------------------------------------------------
$ sqlplus

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Apr 16 09:43:36 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.1.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.1.0 - Production
-----------------------------------------------------------------------------------

batch logins will require the use of the /nolog option on the command line:
$ORACLE_HOME/bin/sqlplus /nolog

i think that's the way to go. it make no difference what state the instance or database is in. all management of can be done with SQL*Plus in 8i.

d.



[Edited by DBAtrix on 04-16-2001 at 10:17 AM]

[sambavan]

If you need to perform any type of maintenance, then you get to login as internal. There you have to specify what type of privileges that you would be having sysoper/ sysdba. The sysdba would give you all the permissions on the database, but the sysoper would disable some of the privileges.

sqlplus internal as sysdba/sysoper

would assume the functionality of svrmgrl.


$ sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 09:11:41 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: internal as sysdba

Connected to:
Oracle8i Release 8.1.6.3.0 - Production
JServer Release 8.1.6.3.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
:
:


SQL> exit


Sam

[padmam]

Dear Amar, 16th April 2001 20:15 hrs chennai

Are you asking me whether i am able to connect via sqlplus (DOS mode) as follows if "yes" yes i am able to connect.

See the spooled file below.

I have all deafult settings nothing changed.Please if you have more doubts mail me across at padu20002000@yahoo.com.

===========
SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 20:12:22 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: sys
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL>
=====================SVRMGR====
Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect sys
Password:
Connected.
=====================

Agasimani for you this reply
===================

through dos prompt type "sqlplus"
after you see the sqlplus prompt

1)Either you can connect as "internal" or "internal as sysdba"

2)If you shutdown normal in sqlplus it takes lot of time
so give shutdown immediate ===startup as usual command

3)So you can work as normal as it was in svrmgrl

See the report spooled for you
====================
C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 20:51:46 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: internal as sysdba

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 45413644 bytes
Fixed Size 70924 bytes
Variable Size 28045312 bytes
Database Buffers 17219584 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;

STATUS
-------
OPEN

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 20:53:50 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: internal

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 45413644 bytes
Fixed Size 70924 bytes
Variable Size 28045312 bytes
Database Buffers 17219584 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;

STATUS
-------
OPEN

SQL>

cheers

padmam

[sambavan]

When you say connect internal, what happens is that oracle by default assumes the permissions of sysdba and not sysoper. In case if you want someone else other than internal to perform the shutdown or startup operation then you have to include them under one of the groups sysdba/sysoper to get the rights to start & shutdown the instance.


Sam

[padmam]

Dear Agasimani, 16th April 2001 21:28 hrs chennai

Coming to your point say i dont have my instance up.Follow the steps as follows to connect and startup instance via slqplus

1)Shutdown the running db from sqlplus as shown below

2)try connecting by sys it will show error but it will start an idle instance as shown below from sqlplus

3)connect as internal as shown below an idle instance is started without error from sqlplus

4)Now from sqlplus startup .

5)Internal is used for backward compatibility in server for a DBA.

6)you cannot login from sqlplus as sys or system even after connecting as "sys as sysdba" user when oracle is down. see the report at the bottom

7)But you can connect as internal then you can start the instance from sqlplus

For eg once i faced a problem of connecting to db as any user
sys or system etc.

Since logon DB trigger created by user is firing so i connected as internal and disabled the trigger and dropped it.

===========================Report=====
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-1999 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 21:23:24 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: internal

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Producti
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 21:24:21 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: sys
Enter password:
ERROR:
ORA-01034: ORACLE not available


Enter user-name: internal
Connected to an idle instance.

SQL> exit
Disconnected

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 21:25:02 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: internal
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 45413644 bytes
Fixed Size 70924 bytes
Variable Size 28045312 bytes
Database Buffers 17219584 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL>

SQL> select status from v$instance;

STATUS
-------
OPEN

SQL>

======From sys you cannot shutdown DB but as internal possible =====

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 16 21:37:34 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: sys
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn internal
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

cheers

padmam

[padmam]

Dear sam, 16th April 2001 22:12 hrs chennai

Yes it works out if we connect as sys as sysdba to shutdown
and startup

============

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> conn sys/ganga@new as sysdba
Connected.
SQL> select status from v$instance;

STATUS
-------
OPEN

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 45413644 bytes
Fixed Size 70924 bytes
Variable Size 28045312 bytes
Database Buffers 17219584 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.

cheers

Padmam