How to Disable Operating System Authentication of sys user ( sysdba privileged ) ?

How to Disable Operating System Authentication of sys user ( sysdba privileged )
--------------------------------------------------------------------------------
such as :-

sys user can connect like conn / as sysdba or connect / as sysdba

How can protect it? and how to sure that sys user must need a password ? without password it can't be able to connect to local server ?


OS authenication it is possible to disable OS authentication by putting non-existant OS group names in the config.c (or config.s) file, then (re)move the config.o and relink oracle,

ON WINDOWS, you just remove the connected user from ora_dba group. then this user cannot connect to database without password.

The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the same for all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

However this is not supported by oracle for the following reasons:

1) Many tools like RMAN rely on the OS authentication to work, in any documentation and references this behaviour is expected to work.

2) If you disable OS authentication like this the administrative connections AS SYSDBA/SYSOPER can only make use of the passwordfile, if there's something wrong with it no one can login, if you consider in a broader sense that availability is also part of security then this means it negatively impacts the security of your system.

3) Moreover it only provides a false sense of security since a DBA with access to the oracle software owner can rebuild the password file or relink oracle to restore it.

NOTE:-

On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:

- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.

It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege. Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a password.
There are two ways to tackle this problem:

a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ sqlplus /nolog
SQL> connect / as sysdba

This can also be used in shellscripts thus:

:
newgrp dbagroup

sysdba privilege and OS Authentication in oracle local database and password file for remotely

what are sysdba and sysoper privileges in oracle ?
why/how sys user can connect to the stop oracle database ?
when you need a password file in oracle database ?
what is OS Authentication in oracle database and How maintain it ?
How can prevent sys user to accessing oracle database without password ?
( like connect / as sysdba )
Why need DBA(unix) / ORA_DBA(windows) group in oracle database server?


All Answer:-
==============

There are two main administrative privileges in Oracle: SYSOPER and SYSDBA .
SYSDBA and SYSOPER are special privileges as they allow access to a database instance
even when it is not running and so control of these privileges is totally outside of
the database itself.

(such as
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default
to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the
same for all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

If you want to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer. )



So SYSDBA privilegesed user password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.


( SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously connected INTERNAL.)


when you want to administrate your database remotely then you must need a password file for SYSDBA privileged users.

If the connection to the instance is local or 'secure' then it is possible to use the operating system to determine if a user is allowed SYSDBA or SYSOPER access. In this case no password is required.
The syntax to connect using operating system authentication is:

CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER

Oracle determines if you can connect thus:

On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.

(means for locally authentication no need for any password file. but locally need OS Authentication by making the connected OS user in the member of DBA(unix) / ORA_DBA(windows) group )


connection to the instance is considered remotelly and 'secure' then you MUST use a password to connect with SYSDBA or SYSOPER privilege. When the passwordfile is initially created with the uility orapwd it holds the password for user SYS, other users can be added to the password file with the 'GRANT SYSDBA to USER' command.
Such a user can then connect to the instance for administrative purposes using the syntax:

CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER



The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used. Default location and file name is
$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

How to show ,add and remove user from password file ? see below :-


SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 12 14:47:40 2011

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

SQL> conn sys@test123 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

SQL> grant sysdba to halim identified by halim ;

Grant succeeded.

SQL>
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE FALSE

SQL>
SQL> grant sysoper to halim ;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE TRUE

SQL> revoke sysoper from halim ;

Revoke succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE FALSE

SQL>
SQL>

MMNL absent for 1206 secs; Foregrounds taking over in oracle Database

MMNL absent for 1206 secs; Foregrounds taking over in oracle Database
=====================================================================

on last thursday i face a problem, this is ...

I shutdown immediate my Database, after waiting a long time(about 3.30 hours +) for it to shutdown I checked its alet log and found this "MMNL absent for 1422 secs; Foregrounds taking over",
. Its not shutting down. I am using Oracle 10.2.0.4 and Sun solaries spark platform.
what can i do ?

alert log showing :-

Fri Jun 10 00:29:00 2011
Shutting down instance: further logons disabled
Fri Jun 10 00:29:01 2011
Stopping background process CJQ0
Fri Jun 10 00:29:01 2011
Stopping background process QMNC
Fri Jun 10 00:29:03 2011
Stopping background process MMNL
Fri Jun 10 00:29:04 2011
Stopping background process MMON
Fri Jun 10 00:29:05 2011
Shutting down instance (immediate)
License high water mark = 3965
Fri Jun 10 00:29:05 2011
Stopping Job queue slave processes, flags = 7
Fri Jun 10 00:29:05 2011
Job queue slave processes stopped
Fri Jun 10 00:49:05 2011
MMNL absent for 1206 secs; Foregrounds taking over



solution:-

At last i have to do something else. because its a production Database . so much downtime is not tolerable.

at last i execute "shutdown abort" from another session and it works within to minutes .


Description:-
=============

"MMNL absent for 1206 secs; Foregrounds taking over" its mean:-
MMNL(Memory Monitor Light) cannot connect to the Database then the problem of the MMNL absent for 1201 secs; Foregrounds taking over is related to the MMNL process.

If MMNL is absent then foregrounds processes take over to do the job that should be doing itself, for example to determine the longest query duration to tune undo retention appropriately. There are cases where because of incomplete database shutdown process after the startup the MMNL is not restarted!


If this is a hang situation during shutdown (after MMNL is stopped) or
during database open (when MMNL is not yet started), messages are written to the alert.log every 5 minutes.

These messages are for informational purposes only to indicate a hang situation which lasted a long period of time and MMNL is unable to run its job at that moment. When the hang lasts for several hours before the instance is completely stopped/killed, the tail of the alert.log may flood with these messages. Although the message text contains MMNL process, in this case MMNL is actually the victim of the hang issue rather than the cause.

The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

How can I know the size of my Oracle database?

How can I know the size of my Oracle database?
=====================================================


An oracle database consists of data files, redo log files, control files, temporary files.
Whenever you say the size of the database this actually means the summation of these files.

Also, if you are in archive log mode, you will generate files in your archive log
destination. Issue the following query to see where your archived redo
logs get placed:

select * from v$parameter where name = 'log_archive_dest';



overall Database Size Query will be ...


SELECT round( a.data_size
+ b.temp_size
+ c.redo_size
+ d.controlfile_size,4) "total_size in GB"
FROM (SELECT SUM (BYTES) / 1024 / 1024/ 1024 data_size
FROM dba_data_files) a,
(SELECT NVL (SUM (BYTES), 0) / 1024 / 1024/ 1024 temp_size
FROM dba_temp_files) b,
(SELECT SUM (BYTES) / 1024 / 1024/ 1024 redo_size
FROM SYS.v_$log) c,
(SELECT SUM (block_size * file_size_blks) / 1024/ 1024
/ 1024 controlfile_size
FROM v$controlfile) d



or you can see it

---its only oracle datafiles summation size

SELECT name, highwater/ 1024 / 1024/ 1024, last_value / 1024 / 1024/ 1024
FROM dba_high_water_mark_statistics where name = 'DB_SIZE';

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database?
==========================================================================

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database.
It contains snapshots of active database sessions taken once a second.
A database session is considered active if it was on the CPU or was waiting for
an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME
view for more information on wait classes.

This view contains one row for each active session per sample and returns the
latest session sample rows first. A majority of the columns describing the session
in the active session history are present in the V$SESSION view.


v$active_session_history only holds data for last half an hour, to view historical information
of more than half an hour, information will be available in DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active
session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY



SELECT sql_id, blocking_session, session_id, event
FROM v$active_session_history
where session_id=2958


/* Formatted on 2011/06/08 14:45 (Formatter Plus v4.8.8) */
SELECT sql_id, blocking_session, session_id, event
FROM dba_hist_active_sess_history
where blocking_session=4404



example:-


SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 8 14:54:04 2011

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

SQL> conn eraadmin@live
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> SELECT sql_id, blocking_session, session_id, event
2 FROM v$active_session_history
3 where session_id=2958 ;

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq: Table Q Get Keys
5gsdgs2syh71d 2958 latch free
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
f97da85fghdwh 2958 PX Deq Credit: send blkd
5bd61nfc8m84n 2958

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd

31 rows selected.


SQL>
SQL>
SQL>
SQL> select * from table(dbms_xplan.display_cursor('2ffckx228w8gm')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 2ffckx228w8gm, child number 0
-------------------------------------
SELECT DISTINCT b.cacmpcde || ' - ' || b.CACMPNAM,b.cacmpcde || ' - ' ||
b.CACMPNAM FROM SYRIGHTS A,SYPARMAS B WHERE A.USERCODE=:1 AND
A.COMPCODE=B.CACMPCDE ORDER BY 1

Plan hash value: 1114147443

--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)|
|
| 1 | SORT ORDER BY | | 2580 | 87720 | 154 (2)| 00:00
:02 |
| 2 | HASH UNIQUE | | 2580 | 87720 | 153 (2)| 00:00
:02 |
| 3 | NESTED LOOPS | | 2580 | 87720 | 151 (0)| 00:00
:02 |
| 4 | TABLE ACCESS FULL| SYPARMAS | 74 | 1702 | 3 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | PK_SYRIGHTS4 | 35 | 385 | 2 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
5 - access("A"."COMPCODE"="B"."CACMPCDE" AND "A"."USERCODE"=:1)


24 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('5gsdgs2syh71d')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 5gsdgs2syh71d, child number 0
-------------------------------------
SELECT DISTINCT ACTNUM FROM STTRPROF ORDER BY 1

Plan hash value: 240056120

--------------------------------------------------------------------------------
-----------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-----------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18877
(100)| | | | |
| 1 | PX COORDINATOR | | | | |
| | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 2 | PX SEND QC (ORDER) | :TQ10001 | 229K| 2690K| | 13998
(5)| 00:02:48 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT UNIQUE | | 229K| 2690K| 48M| 13998
(5)| 00:02:48 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWC | |
|* 7 | INDEX FAST FULL SCAN| PK_STTRPROF | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)


24 rows selected.

SQL>