External OS User Authentication in Oracle

Oracle users can be authenticated in different ways,We generally login into our database with two ways . i.e, either as

C:\> sqlplus / as sysdba        or
C:\> sqlplus user/password@SID as sysdba.

This is only valid when we are the member of  "ORA_DBA"  OS  group(window) . If we are not the member of the "ORA_DBA" group,then we cannot login into database . Let have a look :  Here i have created a osuser  "oraext" and login with this user and try to connect with database.

Step 1 : Create OSuser 

C:\>net user oraext orapass /add
The command completed successfully.

Check the domain
C:\> echo %userdomain%
TECH-199

Step 2:  Login with "oraext"  user in  window machine and try to connect the database as:

c:\> sqlplus sys/sys@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 14:46:39 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR: ORA-01017:  invalid username/password; logon denied
Enter user-name:

Here,we are getting invalid username error,this is because the os user "oraext" is not the member of  the ORA_DBA group. Therefore to handle such case Oracle provide OS authentication method to connect database without authenticating any database username and password. Let's check 

In order to create the external user authentication we perform the following as :

1.) Check the values of the parameter "os_authentication_prefix"

SQL> SHOW PARAMETER os_authent_prefix
NAME                                 TYPE                       VALUE
---------------------             -----------                 -------------
os_authent_prefix            string                         OPS$

As we can see, the default value is "ops$". If this is not appropriate it can be changed using the alter system command.

2.) Create a database user with same name as the OS user which is prefixed by os_authent_prefix values followed by domain name. On Windows platforms we would expect an Oracle username of   "OPS$DOMAIN\xxxx"  for the Windows user "xxxx".

Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated  connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the domain-name and OS username . Therefore the username seems like "ops$tech-199\oraext"

SQL> create user "ops$tech-199\oraext" identified externally;

3.) Grant connect privileges to them .

SQL> grant connect to "ops$tech-199\oraext";

Now Login as user "oraext" in window  and open the cmd and connect as  :

C:\>sqlplus / 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 17:27:31 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Note: The parameter in sqlnet.ora file be SQLNET.AUTHENTICATION_SERVICES= (NTS)

Advantage of the OS authentication:

1.) Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.

2.) Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.

3.) Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.

Enjoy  :-) 

Oracle Session Information From SYS_CONTEXT

SYS_CONTEXT is a standard Oracle Database function used to retrieve session-level information. SYS_CONTEXT  allow us to retrieve a set of session parameters via the namespace parameter ‘USERENV’.Basically, these are global variables that Oracle stores on the current session/user. Here is a sample of some session and user-specific information that can be obtained in one function call:

SQL> select sys_context('USERENV','AUTHENTICATION_TYPE') 
          ,sys_context('USERENV','CURRENT_SCHEMA') 
          ,sys_context('USERENV','CURRENT_SCHEMAID') 
          ,sys_context('USERENV','CURRENT_USER') 
          ,sys_context('USERENV','CURRENT_USERID') 
          ,sys_context('USERENV','DB_DOMAIN') 
          ,sys_context('USERENV','DB_NAME') 
          ,sys_context('USERENV','INSTANCE') 
         ,sys_context('USERENV','IP_ADDRESS') 
         ,sys_context('USERENV','ISDBA') 
         ,sys_context('USERENV','LANG') 
         ,sys_context('USERENV','LANGUAGE') 
         ,sys_context('USERENV','NETWORK_PROTOCOL') 
         ,sys_context('USERENV','NLS_CALENDAR') 
         ,sys_context('USERENV','NLS_CURRENCY') 
         ,sys_context('USERENV','NLS_DATE_FORMAT') 
        ,sys_context('USERENV','NLS_DATE_LANGUAGE') 
        ,sys_context('USERENV','NLS_TERRITORY') 
        ,sys_context('USERENV','OS_USER') 
        ,sys_context('USERENV','SESSION_USER') 
        ,sys_context('USERENV','SESSION_USERID') 
       ,sys_context('USERENV','SESSIONID') 
       ,sys_context('USERENV','TERMINAL') from dual ;

The output of the above sample is :

Enjoy      :-) 

Identify IP Addresses and Host Names

We can identify the IP addresses of all the client connect to server. Below the command to identified  the IP address ,machine name,sid  .

SQL>   select   sid,   machine,     UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1))  ip  from   v$session   where   type='USER'   and    username is not null   order  by   sid;

Oracle provides the method to identifying and relating to the IP addresses and host names  for Oracle clients and servers. The few methods  are  : 

1.) UTL_INADDR
2.) SYS_CONTEXT
3.) V$INSTANCE
4.) V$SESSION

1.) UTL_INADDR  :  The UTL_INADDR package provides a PL/SQL procedures to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts.The utl_inaddr like the two function  : 

1.) get_host_address and 
2.) get_host_name.

Now we check the description of UTL_INADDR 

SQL> desc UTL_INADDR

FUNCTION  GET_HOST_ADDRESS  RETURNS  VARCHAR2
 Argument Name                Type                In/Out         Default?
 -----------------          ------------           --------      -------------
 HOST                         VARCHAR2            IN           DEFAULT
FUNCTION GET_HOST_NAME RETURNS VARCHAR2
 Argument Name               Type                    In/Out             Default?
 ----------------           ---------------        --------          ------------
 IP                                 VARCHAR2            IN                DEFAULT

I.) Get_host_address: The get_host_address function like the argument name "HOST" , data types "varchar2" and by default NULL. The get_host_address get local IP address and remote host given its IP address.

II.) Get_host_name: The get_host_name function like the argument name "IP" , data types "varchar2" and by default NULL. The get_host_name get local host name and remote host given its name.

Let see some example : 

The GET_HOST_ADDRESS function returns the IP address of the specified host name

SQL>select  UTL_INADDR.get_host_address('TECH-284') from  dual;

UTL_INADDR.GET_HOST_ADDRESS('TECH-284')
-----------------------------------------------------------
192.100.0.85

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.52.127') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.52.127')
----------------------------------------------------------
tech-199

The host name of the database server is returned if the specified IP address is NULL or omitted.

2.) SYS_CONTEXT : Oracle has a very useful built-in function called SYS_CONTEXT. The SYS_CONTEXT function is able to return the following host and IP address information for the current session.SYS_CONTENT has the following options as 

TERMINAL - An operating system identifier for the current session. This is often the client machine name.
HOST - The host name of the client machine.
IP_ADDRESS - The IP address of the client machine.
SERVER_HOST - The host name of the server running the database instance.

What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session.

The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )

Let see some example :

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
---------------------------------------------------
TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SYS_CONTEXT('USERENV','HOST')
------------------------------------------
TECH\TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------
192.100.0.112

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------------------
tech-199

For more about sys_context check the below link .
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm
http://www.adp-gmbh.ch/ora/sql/sys_context.html

3.) V$INSTANCE :  The host_name column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;
HOST_NAME
-----------------
TECH-199

4.) V$SESSION  :  V$SESSION displays session host information for each current session. The column such as terminal and machine give the following details.
TERMINAL: The operating system terminal name for the client.This is often set to the client machine name.
MACHINE :The operating system name for the client machine.This may include the domain name if present.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'HR';
TERMINAL           MACHINE
-------------       --------------------
TECH-199          TECH\TECH-199


Enjoy    :-)

Grant privileges on all tables in particular schema

In oracle, we cannot grant the privileges on schemas level .If we have to grant the privileges on all the tables of a particular schemas, then it is very tedious to grant privileges on all the tables one-by-one to a particular user. This task can be  performed by using a simple pl/sql procedure. Here is a Demo for this : 

Suppose we have to grant "select" privileges on all the tables to user  then we need to do something like this .

SQL>FOR x IN (SELECT * FROM user_tables)
          LOOP
          EXECUTE  IMMEDIATE  'GRANT  SELECT  ON  ' || your.table_names || '  TO <<user>>' ;
           END LOOP ;




Enjoy    :-)

Why and How to Drop Undo Tablespace ?

A condition may be occur when we have to  drop the Undo tablespace. Undo tablespace may be drop in various scenarios .In my case ,Once i have imported few tables with table_exists_action=append parameter in  database and these tables has created lots of undo's  i.e;  near about 102GB. So when we backup the database ,the backup size increases, i.e; backup consumes lots of space. Another scenario may be possible that while clonning if the undo tablespace get missed, then we can recover by just dropping the undo tablespace. Below is demo for drop and re-creating the undo tablespace. 

Step 1 : Shutdown immediate

SQL> shut immediate

Step 2 : Create pfile from spfile and edit pfile to set undo_management=manual (if it is set auto then set it to manual and if this parameter is not in pfile than set it i.e, undo_management=manual  otherwise it will consider it "auto"  management

Step 3 : Startup pfile=<modified pfile>

Step 4 : Drop undo tablespace as

SQL> drop  tablespace <undo_name> including contents and datafiles.

Step 5 : Create Undo tablespace

SQL> create undo tablespace undotbs1 datafile <location> size=100M;

Step 6 : Shutdown the database and edit pfile to reset  "undo_management=AUTO"

Step 7 : create spfile from pfile

SQL> create spfile from pfile=<pfile_location>

Step 8 : Startup the database

SQL> startup 


Enjoy   :-) 

Differences Between Dedicated Servers, Shared Servers, and Database Resident Connection Pooling

Oracle  creates  server  processes  to  handle  the requests  of  user  processes connected  to  an  instance. A  server process can be either a dedicated server process, where one server process services only one user process, or if  our database server is configured for shared server, it can be a shared server process, where a server process can service multiple user processes . Let's have a look

Dedicated Servers:  

1.)  When a client request is received, a new server process and a session are created for the client.

2.)  Releasing database resources involves terminating the session and server process

3.)  Memory requirement is proportional to the number of server processes and sessions. There is one server and one session for each client.
4.)  Session memory is allocated from the PGA.


Shared Servers : 

1.) When the first request is received  from  a client, the  Dispatcher  process places this request on a common  queue. The request is picked up by an available shared server process. The Dispatcher process then manages the communication between the client and the shared server process.

2.) Releasing database resources involves terminating the session

3.) Memory requirement is proportional to the sum of the shared servers and sessions. There is one session for each client.

4.) Session memory is allocated from the SGA.


Database Resident Connection Pooling : 

1.)  When the first request is received from a client, the Connection Broker picks an available pooled server and hands off the client connection to the pooled server.  If no pooled servers are available, the Connection Broker creates one.If the pool has reached its maximum size, the client request is placed onthe wait queue until a pooled server is available.

2.)  Releasing database resources involves releasing the pooled server to the pool.

3.) Memory requirement is proportional to the number of pooled servers and their sessions.There is one session for each pooled server.

4.) Session memory is allocated from the PGA.

Example of Memory Usage for Dedicated Server, Shared Server, and Database Resident Connection Pooling :

Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.If there are 5000 client connections, the memory used by each configuration is as  follows:

Dedicated Server 
Memory used = 5000 X (400 KB + 4 MB) = 22 GB


Shared Server 
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA.


Database Resident Connection Pooling 
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB
where 35KB is used for others operation




Enjoy    :-)

All About Temporary Tablespace Part IV

Monitoring Temporary Space Usage :

We can monitor temporary space usage in the database in real time. At any given time, Oracle can tell us about all of the database’s temporary tablespaces, sort space usage on a session basis, and sort space usage on a statement basis. All of this information is available from v$ views, and the queries shown in this section can be run by any database user with DBA privileges.

Temporary Segments :

The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

SQL> SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D 
WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; 

The query displays for each sort segment in the database the tablespace the segment resides in, the size of the tablespace, the amount of space within the sort segment that is currently in use, and the amount of space available. Sample output from this query is as follows:

TABLESPACE     MB_TOTAL     MB_USED       MB_FREE
-----------                ------------          ------------         ---------
TEMP                     10000                   9                    9991

This example shows that there is one sort segment in a 10,000 Mb tablespace called TEMP. Right now, 9 Mb of the sort segment is in use, leaving a total of 9,991 Mb available for additional sort operations. (Note that the available space may consist of unused blocks within the sort segment, unallocated extents in the TEMP tablespace, or a combination of the two.)

Sort Space Usage by Session :

The following query displays information about each database session that is using space in a sort segment. Although one session may have many sort operations active at once, this query summarizes the information by session. 

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks)* TBS.block_size/1024/1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr  AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace  ORDER BY sid_serial;

The query displays information about each database session that is using space in a sort segment, along with the amount of sort space and the temporary tablespace being used, and the number of sort operations in that session that are using sort space.Sample output from this query is as follows: 

SID_SERIAL  USERNAME OSUSER SPID MODULE PROGRAM   MB_USED TABLESPACE SORT_OPS
----------   -------- ------ ---- ------ --------- ------- ---------- --------
33,16998    RPK_APP    rpk  3061   inv   httpd@db1    9       TEMP       2

This example shows that there is one database session using sort segment space. Session 33 with serial number 16998 is connected to the database as the RPK_APP user. The connection was initiated by the httpd@db1 process running under the rpk operating system user, and the Oracle server process has operating system process ID 3061. The application has identified itself to the database as module “inv.” The session has two active sort operations that are using a total of 9 Mb of sort segment space in the TEMP tablespace.

Sort Space Usage by Statement :
The following query displays information about each statement that is using space in a sort segment.

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid ;

The query displays information about each statement using space in a sort segment,including information about the database session that issued the statement and the temporary tablespace and amount of sort space being used. 

Conclusion : 

When an operation such as a sort, hash, or global temporary table instantiation is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary tablespaces are a shared resource in the database, and we can’t set quotas to limit temporary space used by one session or database user. If a sort operation runs out of space, the statement initiating the sort will fail. It may only take one query missing part of its WHERE clause to fill an entire temporary tablespace and cause many users to encounter failure because the temporary tablespace is full. It is easy to detect when failures have occurred in the database due to a lack of temporary space. With the setting of a simple diagnostic event, it is also easy to see the exact text of each statement that fails for this reason. There are also v$ views that DBAs can query at any time to monitor temporary tablespace usage in real time. These views make it possible to identify usage at the database, session, and even statement level. Oracle DBAs can use the techniques outlined in this paper to diagnose temporary tablespace problems and monitor sorting activity in a proactive way.

   

(Ref : Roger Schrag Database Specialists )

Enjoy    :-) 

All About Temporary Tablespace Part III

How DBA determines and handle the database when temporary tablespace running out of space.  Here,we have two techniques to find how space in temporaray tablespace is being used :

1.) Direct Oracle to log every statement that fails for lack of temporary space.
2.) A set of queries to run at any time to capture in real time how temporary space is currently being used on a per-session or per-statement basis.

Identifying SQL Statements that Fail Due to Lack of Temporary Space :

It is helpful that Oracle logs ORA-1652 errors to the instance alert log as it informs a DBA that there is a space issue. The error message includes the name of the tablespace in which the lack of space occurred, and a DBA can use this information to determine if the problem is related to sort segments in a temporary tablespace or if there is a different kind of space allocation problem.

Unfortunately, Oracle does not identify the text of the SQL statement that failed. However, Oracle does have a diagnostic event mechanism that can be used to give us more information whenever an ORA-1652 error occurs by causing Oracle server processes to write to a trace file. This trace file will contain a wealth of information,including the exact text of the SQL statement that was being processed at the time that the ORA-1652 error occurred. 

We can set a diagnostic event for the ORA-1652 error in our individual database session with the following statement:

SQL> alter session set events  '1652 trace name errorstack';

We can also set diagnostic events in another session (without affecting all sessions instance-wide) by using the “oradebug event” command in SQL*Plus.We can deactivate the ORA-1652 diagnostic event or remove all diagnostic event settings from the server parameter file with statements such as the following:

SQL> alter session set events '1652 trace name context off';

If a SQL statement fails due to lack of space in the temporary tablespace and the ORA-1652 diagnostic event has been activated, then the Oracle server process that encountered the error will write a trace file to the directory specified by the user_dump_dest instance parameter. 

The top portion of a sample trace file is as follows

*** ACTION NAME:() 2011-09-17 17:21:14.871
*** MODULE NAME:(SQL*Plus) 2011-09-17 17:21:14.871
*** SERVICE NAME:(SYS$USERS) 2011-09-17 17:21:14.871
*** SESSION ID:(130.13512) 2011-09-17 17:21:14.871
*** 2011-09-17 17:21:14.871
ksedmp: internal or fatal error
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Current SQL statement for this session:
SELECT "A1"."INVOICE_ID", "A1"."INVOICE_NUMBER", "A1"."INVOICE_DAT
E", "A1"."CUSTOMER_ID", "A1"."CUSTOMER_NAME", "A1"."INVOICE_AMOUNT",
"A1"."PAYMENT_TERMS", "A1"."OPEN_STATUS", "A1"."GL_DATE", "A1"."ITE
M_COUNT", "A1"."PAYMENTS_TOTAL"
FROM "INVOICE_SUMMARY_VIEW" "A1"
ORDER BY "A1"."CUSTOMER_NAME", "A1"."INVOICE_NUMBER"

From the trace file we can clearly see the full text of the SQL statement that failed. It is important to note that the statements captured in trace files with this method may not themselves be the cause of space issues in the temporary tablespace. For example, one query could run successfully and consume 99.9% of the temporary tablespace due to a Cartesian product, while a second query fails when trying to allocate just a small amount of sort space. The second query is the one that will get captured in a trace file, while the first query is more likely to be the root cause of the problem.

Click Here for next Part IV

All About Temporary Tablespace Part II

Oracle sorting Basics

As we know there are different cases where oracle sorts data .Oracle session sorts the data in memory.If the amount of data being sorted is small enough, the entire sort will be completed in memory with no intermediate data written to disk.When Oracle needs to store data in a global temporary table or build a hash table for a hash join, Oracle also starts the operation in memory and completes the task without writing to disk if the amount of data involved is small enough.

If an operation uses up a threshold amount of memory, then Oracle breaks the operation into smaller ones that can each be performed in memory. Partial results are written to disk in a temporary tablespace. The threshold for how much memory may be used by any one session is controlled by instance parameters. If the  workarea_size_policy parameter is set to AUTO, then the pga_aggregate_target parameter indicates how much memory can be used collectively by all sessions for activities such as sorting and hashing. Oracle will automatically assess and decide how much of this memory any individual session should be allowed to use. If the workarea_size_policy parameter is set to MANUAL, then instance parameters such as sort_area_size, hash_area_size, and bitmap_merge_area_size dictate how much memory each session can use for these operations. Each database user has a temporary tablespace designated in their user definition(check through dba_users view) . Whenever a sort operation grows too large to be performed entirely in memory, Oracle will allocate space in the temporary tablespace designated for the user performing the operation. 

Temporary segments in temporary tablespaces which we will call “sort segments”— are owned by the SYS user, not the database user performing a sort operation. There typically is just one sort segment per temporary tablespace, because multiple sessions can share space in one sort segment. Users do not need to have quota on the temporary tablespace in order to perform sorts on disk. Temporary tablespaces can only hold sort segments. Oracle’s internal behavior is optimized for this fact. For example, writes to a sort segment do not generate redo or undo. Also, allocations of sort segment blocks to a specific session do not need to be recorded in the data dictionary or a file allocation bitmap. Why? Because data in a temporary tablespace does not need to persist beyond the life of the database session that created it.

One SQL statement can cause multiple sort operations, and one database session can have multiple SQL statements active at the same time—each potentially with multiple sorts to disk. When the results of a sort to disk are no longer needed, its blocks in the sort segment are marked as no longer in use and can be allocated to another sort operation.A sort operation will fail if a sort to disk needs more disk space and there are 

1.) No unused blocks in the sort segment,and
2.) No space available in the temporary tablespace for the sort segment to allocate an additional extent.

This will most likely cause the statement that prompted the sort to fail with the Oracle error, “ORA-1652: unable to extend temp segment.” This error message also gets logged in the alert log for the instance.It is important to note that not all ORA-1652 errors indicate temporary tablespace issues. For example, moving a table to a different tablespace with the ALTER TABLE…MOVE statement will cause an ORA-1652 error if the target tablespace does not have enough space for the table.

Temporary tablespaces will appear full after a while in a normally running database. Extents are not de-allocated after being used. Rather it would be managed internally and reused. This is normal and to be expected and is not an indication that we do not have any temporary space. If we are not encountering any issue/error related to TEMP then we don't need to worry about this.

There is no quick way or scientific approach to calculate the required TEMP tablespace size. The only way to estimate the required TEMP tablespace size is regressive testing.The information inside the temporay segment gets released, not the segment itself.


Click Here for Next Part III

All About Temporary Tablespace Part I

There are lots of confusion about the temporary tablespaces.Here i have tried to covered the basic of temporary tablespace and one of the famous related error i.e; ORA-1652 says "unable to extend temp segment" . This problem can be solved as following : 

1.)  Increase the size of  temporary tablespace either by resizing the tempfile or by adding the tempfile.

2.) Check the SQL statements which is consuming the large temp tablespace and kill the corresponding session.(not proper solution).

3.) Check the SQL and tuned it.

Here i have explain what we can do when our database runs out of space.

Introduction : 

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if we join two large tables, and Oracle cannot do the sort in memory (see sort_area_size initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: create Index , Analyse, Select Distinct, Order By, Group By, Union, Intersect , Minus, Sort-Merge joins, etc.

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session. Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table. Here, we will cover the following points in next link : 

1.) How Oracle managed sorting operations
2.) How DBA determines and handle the database when temporary tablespace running out of space

Click Here for next Part II :

For more detail about temporary tablespace  Click Here

Drop all object of Schemas

Sometimes we need to drop the all objects of schemas while importing. The basic approach is drop the schemas and recreate the schemas. This method is quite efficient . To perform this operation we need the system or sysdba privileges to create and drop the user. If anyone have not the system and sysdba privileges then, dropping all objects is the option . Below is the Demo where we will drop all objects of "SCOTT" schemas.

Step 1 : Generate the scripts for dropping the schemas :

SQL> spool C:\genera_dropall.sql

SQL> select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')  from user_objects;

'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRAINTS;',';')          
------------------------------------------------------------------------------------------------------------
drop TABLE SALGRADE CASCADE CONSTRAINTS;                                                                                                                                                        
drop TABLE BONUS CASCADE CONSTRAINTS;                                                                                                                                                        
drop INDEX PK_EMP;                                                                                                                                                                      
drop TABLE EMP CASCADE CONSTRAINTS;                                                                                                                                        
drop TABLE DEPT CASCADE CONSTRAINTS;                                                                                                                                                    
drop INDEX PK_DEPT;                                                                                                                                                                        
6 rows selected.
Since we have the drop script as 'genera_dropall.sql'

Step  2  :  Now we will drop all_objects i.e, 'genera_dropall.sql' script is used to drop everything in schemas.

SQL>@"genera_dropall.sql" 
Now check the object in scott schemas

SQL> select * from tab;
TNAME                                                       TABTYPE           CLUSTERID
------------------------------                               ------------             --------------
BIN$5JksbkFeSai/0JTwbJOenQ==$0           TABLE
BIN$KtthiEIaRZmkv/5+FoYu5A==$0              TABLE
BIN$L/qcqzTxTsm8XHkDrfANOg==$0          TABLE
BIN$opUCTunxRf+0AUbhOzzBgw==$0        TABLE

The SQL was written against Oracle (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop).

Step  3  : Purge the recyclebin objects.

SQL>purge recyclebin ;
Recyclebin purged.

This will produce a list of drop statements. Not all of them will execute - if we drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with: 

SQL> select * from user_objects;
no rows selected


Enjoy    :-) 

ORA-01555: Snapshot Too Old

UNDO is  the  backbone  of  the  READ CONSISTENCY  mechanism  provided  by  Oracle. Multi-User Data  Concurrency  and  Read  Consistency  mechanism  make  Oracle  stand  tall  in  Relational  Database Management  Systems  (RDBMS) world .

Best  of  all,  automatic  undo  management  allows  the  DBA  to  specify  how  long  undo  information should  be  retained  after  commit, preventing  “snapshot too old”   errors  on  long  running  queries. This  is  done  by  setting  the UNDO_RETENTION  parameter.  The   default  is  900  seconds (5 minutes), and  we  can set  this  parameter  to  guarantee  that  Oracle  keeps undo  logs  for  extended  periods  of time. The  flashback  query  can  go  upto  the  point  of  time specified as a value in  the UNDO_RETENTION parameter.

Why ORA-01555 error occur? 

1.) Oracles  does  this  by reading  the  "before image"  of  changed  rows  from  the  online  undo  segments.  If  we  have  lots  of  updates, long running SQL  i.e , rollback records  needed  by  a  reader  fo  consistent read are overwritten by other writers.

2.) It may also due small size of undo and small undo_retention period .

To  solve  this  issues  we need  to  increase the  undo  tablepsace  and  undo  retention  period. Now  the issue  is  how  much  should  be  the  optimal value of  undo  retention and undo tablespace. For this we use the advisor. By using OEM, it is quite easy to estimate the size and time duration of undo.

Calculate Optimal Undo_Retention  :
The following query will help us to optimize the UNDO_RETENTION parameter :


Optimal Undo Retention = Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_SEC)

To calculate Actual Undo Size :

SQL> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE_MB"
            FROM v$datafile a, v$tablespace b,dba_tablespaces c
           WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#;

Undo Blocks per Second : 

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"

FROM v$undostat ;

DB Block Size  :

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [Byte]"   FROM   v$parameter
  WHERE name = 'db_block_size';

We can do all in one query as

SQL> SELECT d.undo_size/(1024*1024) “ACT_UNDO_SIZE [MB]“, 
              SUBSTR(e.value,1,25) “ UNDO_RTN [Sec] “, 
              ROUND((d.undo_size / (to_number(f.value) * 
              g.undo_block_per_sec))) “OPT_UNDO_RET[Sec]” 
       FROM ( 
                       SELECT SUM(a.bytes) undo_size 
                        FROM v$datafile a, 
                         v$tablespace b, 
                        dba_tablespaces c 
                        WHERE c.contents = 'UNDO' 
                       AND c.status = 'ONLINE' 
                       AND b.name = c.tablespace_name 
                       AND a.ts# = b.ts# 
                       ) d, 
                            v$parameter e, 
                             v$parameter f, 
( 
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) 
             undo_block_per_sec 
         FROM v$undostat 
       ) g 
              WHERE e.name = 'undo_retention' 
              AND f.name = 'db_block_size'
/
ACT_UNDO_SIZE [MB]         UNDO_RTN [Sec]              OPT_UNDO_RET[Sec]

------------------------             ----------------------            ----------------------------
                      50                                  900                                              24000

Calculate Needed UNDO Size :

If  we  are  not  limited  by  disk  space, then  it would  be  better  to  choose  the  UNDO_RETENTION time that is best for us (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity :                                                                                                                                                              

Formula :Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC



Here again we can find in a single :

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/

We can avoid ORA-01555  error as follows :

1.) Do not run discrete transactions while sensitive queries or transactions are running, unless we are confident that the data sets required are mutually exclusive.

2.) Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.

3.) Code long running processes as a series of restartable steps.

4.) Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.

5.) Use a large optimal value on all rollback segments, to delay extent reuse.

6.) Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.

7.) Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.


Enjoy       :-)

How to configure Shared Server?

A  shared  server  process allows  a  single  server  process  to  service  several  clients,  based on the premise  that  usually  in  an  OLTP  environment, a user  is  more often  than  not, reading  and  editing data on  the screen  than  actually  executing  a  DML. What  this  means  is  that,  there will  be  chunks  of  time when  the dedicated  server  process, dedicated  to  a  particular c lient  will  be  sitting  idle. It  is  this idleness that is exploited by the shared server process in servicing several clients together.

Shared  server is enabled  by  setting  the  SHARED_SERVERS  initialization  parameter  to a  value greater  than  0. The other  shared  server  initialization  parameters  need  not  be  set. Because shared  server  requires  at least  one dispatcher  in order  to work, a  dispatcher  is  brought  up even  if  no dispatcher  has  been  configured. The  SHARED_SERVERS  initialization parameter  specifies  the  minimum  number  of shared  servers  that we  want  created  when  the instance is started. After  instance startup, Oracle Database can dynamically adjust the number  of  shared  servers  based  on  how  busy  existing shared servers are and the length of the request queue.

In  typical  systems, the number of  shared  servers stabilizes  at  a  ratio of  one  shared  server  for  every  ten  connections . For  OLTP  applications, when  the rate of   requests  is  low, or  when  the  ratio  of  server usage  to  request  is  low,  the connections-to-servers  ratio could  be  higher . If  we  know  the average  load  on  our  system, then we  can  set  SHARED_SERVERS  to  an  optimal  value. The Below  example   shows  how  we  can  use  this  parameter .

For Example 

Assume a database  is  being  used  by  a  telemarketing  center  staffed  by  1000  agents. On  average,  each  agent  spends  90%  of  the  time  talking  to  customers  and  only  10%  of  the  time  looking  up  and updating  records.  To  keep  the  shared  servers from  being  terminated  as  agents  talk  to  customers  and  then  spawned  again  as  agents  access  the  database, a  DBA  specifies  that  the  optimal  number  of shared  servers  is  100 . However, not  all  work  shifts are  staffed  at  the  same  level. On  the  night  shift,  only  200  agents  are  needed. Since  SHARED_SERVERS  is  a  dynamic parameter, a  DBA  reduces  the number of  shared  servers  to  20  at  night, thus  allowing  resources  to  be  freed up  for other  tasks  such  as  batch  jobs .

Setting the Initial Number of Dispatchers  

We  can  specify multiple  dispatcher  configurations  by  setting  DISPATCHERS  to  a  comma  separated list  of  strings, or  by  specifying  multiple  DISPATCHERS parameters  in  the  initialization  file. If  we specify  DISPATCHERS  multiple times, the  lines  must  be  adjacent  to  each  other  in  the  initialization parameter  file.  Internally,  Oracle  Database  assigns  an  INDEX  value  (beginning with zero)  to  each DISPATCHERS parameter. We  can later refer  to  that  DISPATCHERS  parameter  in  an  ALTER SYSTEM  statement  by  its  index  number. Some  examples  of  setting  the  DISPATCHERS  initialization parameter follow. 

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"  
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"
To  force  the  dispatchers  to  use  a  specific  port  as  the  listening  endpoint,  add  the  PORT  attribute  as follows:
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"


Determining the Number of Dispatchers :  

Once  we  know  the  number  of  possible  connections  for  each  process  for  the  operating  system, calculate  the  initial  number  of  dispatchers  to  create  during  instance  startup,  for  each  network  protocol,  using the following formula:

Number of dispatchers =  CEIL ( max. concurrent sessions / connections for each dispatcher )
CEIL returns the result roundest up to the next whole integer.

For example, assume  a  system  that  can  support  970  connections  for  each  process,  and  that  has :

A  maximum  of  4000  sessions  concurrently  connected  through   TCP/IP  and  A  maximum  of   2,500 sessions  concurrently  connected  through  TCP/IP  with  SSL  then   DISPATCHERS  attribute  for  TCP/IP  should  be  set  to  a  minimum  of  five  dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970) :

DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
Depending on performance, we  may need to adjust the number of dispatchers.

Steps to configure shared server :   To  configure   shared  server  we  have  to  enable  the  following parameter . All the  below  parameters  are dynamic . Below  is  Demo to  configur e the  shared  server. 

1.) alter system set shared_servers= 25;
2.) alter system set max_shared_servers= 50;
3.)  alter system set dispatcherS= '(PROT=tcp)(DISP=30)';
4.) Add (SERVER = SHARED) in tnsnames.ora file.
 the  tnsnames.ora  file  look  like

NOIDA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1521))
    )
    (CONNECT_DATA =
 (SERVER = SHARED)
      (SERVICE_NAME = noida)
    )

To check the status of server fire the below query :

SQL> select distinct server,username from  v$session ; 
SERVER                   USERNAME
-------------          -----------------
DEDICATED                SYS
DEDICATED

Once, i found that after configuring the shared server the above query shows the server status as 'NONE' .

What does it mean if SERVER = 'NONE' in v$session?

On  googling ,  i  found  that , in  Shared  Server  configuration  when  we  see  value  'NONE'  ,  it  means there  is  no  task  being  processed  by  shared  server  for  that  session. The  server  column  will  infact  show  status  of   'SHARED'  if  there is  some  task  being  processed  at  that  particular  time  by  the shared  server  process  for  that  session. Hence to check the status ,  fire  some  big  query  and  then  check the server  status .

Disabling Shared Server : 

We  can  disable  shared  server  by  setting   SHARED_SERVERS   to  0. we  can  do  this  dynamically with the  'alter system'  statement. When  we  disable  shared  server,  no  new  clients  can  connect  in  shared   mode.  However,  Oracle  Database  retains  some  shared  servers  until  all  shared  server connections  are closed. The  number  of shared  servers  retained  is  either  the  number  specified  by  the  preceding  setting  of  shared_servers   or   the  value of  the  max_ shared_servers   parameter ,  whichever  is  smaller. If  both  shared_servers  and  max_ shared_servers  are  set  to  0, then  all  shared  servers  will terminate and  requests  from  remaining  shared  server clients  will  be  queued  until  the  value of  shared_servers  or max_ shared_servers  is raised again . To terminate dispatchers once all shared server clients disconnect, enter this statement:

SQL> alter system set dispatchers='' ;  

Enjoy   :-) 

How To Check Which Oracle Features are Enabled ?

There is a very good view DBA_FEATURE_USAGE_STATISTICS . Its function is to display information about database feature usage statistics. Oracle 10g will keep track of any of its features that are being used.   As an example, if we SELECT from any of the Oracle Diagnostic Pack and Tuning Pack views like V$ACTIVE_SESSION_HISTORY, the database will record our usage of it in this DBA view.  

Some of the information tracked are :

•  Name of the feature
•  Number of times the system has detected usage for the feature
•  First sample time the system detected usage of the feature
•  Last sample time the system detected usage of the feat

The below sql will give the detail of the oracle services which are being used.

SQL> select NAME, VERSION, DETECTED_USAGES, CURRENTLY_USED, FIRST_USAGE_DATE, LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED  = 'TRUE' order by 1,2;

Output : 

How to  check the Oracle Edition  : 
We can the check the oracle edition i.e; whether it is enterprise edition or standard edition by using the below command :

SQL>select * from product_component_version;

PRODUCT                                                     VERSION                 STATUS

----------------------------------                        -------------               ------------
NLSRTL                                                         11.2.0.1.0             Production
Oracle Database 11g Enterprise Edition      11.2.0.1.0             Production
PL/SQL                                                         11.2.0.1.0             Production
TNS for 32-bit Windows:                              11.2.0.1.0             Production


Enjoy    :-)

IMP-00010 not a valid export file header failed verification , IMP-00000:

While performing an exp/imp operation , i got IMP-00010 while importing the dumps .On googling i found that this error occur generally due to two reasons .

1.) File Corrupted During Transfer.
Note : FTP should be done in BINARY MODE

2.) When export dump file is higher version and import in lower version.
e.g; exp dump file is Oracle 10g and import in Oracle 9i

Export From            <---->   Import to   <----> Use Import/Export Utility
Version : 10.2.0.1.0 <----->   10.1.0.2.0   <----->   10.1.0.2.0
Version : 10.2.0.1.0 <----->   9.2              <----->   9.2
Version : 10.2.0.1.0 <----->   9.0.1           <----->   9.0.1

Note : Suppose we want to import 10.2.0.1.0 export dump file in 9.2.0 then we must use 9.2.0 export tools for export data from 10.2.0.1.0. and run CATEXP.SQL script which is located in Located : $ORACLE_HOME/rdbms/admin

Consider an example : Dumpfile exported in Oracle 11gr1  and has to be Imported  in 10gr1, 9i. Then use Oracle 11gr1 client (IMPORT) binary to export 11g dumpfile. Install oracle 11g client and connect to 10g,9i,8i server and import .

Enjoy     :-) 

Block Developers Tools on Production Database

I have come across a very good post on how to prevent users from using additional tools to connect to production database. This can also allow us to connect for specific users only. This can be done by creating the a AFTER LOGON trigger create ON DATABASE .Below is the script 

c:\> sqlplus / as sysdba

SQL> CREATE OR REPLACE TRIGGER block_tools_from_prod
          AFTER LOGON ON DATABASE
DECLARE
           v_prog sys.v_$session.program%TYPE;
BEGIN
         SELECT   program    INTO   v_prog
         FROM     sys.v_$session
         WHERE    audsid = USERENV('SESSIONID')
         AND   audsid != 0          ---- Don't Check SYS Connections
        AND  ROWNUM = 1;    ---- Parallel processes will have the same AUDSID's
      IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR   -- Toad
      UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
      UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
      UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
      UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/

Reference : http://psoug.org/snippet.htm/Block_TOAD_and_other_tools_516.htm


Enjoy    :-)

Determine OS block size for Linux and Windows

A block is a uniformly sized unit of data storage for a filesystem. Block size can be an important consideration when setting up a system that is designed for maximum performance. 

Block size in Linux :  If we want to confirm the block size of any filesystem of Ubuntu or any other Linux OS, tune2fs command is here to help:

ubuntu# tune2fs -l /dev/sda1 | grep Block
Block count:              4980736
Block size:                 4096
Blocks per group:       32768

From this example, we can see that the default block size for the filesystem on  /dev/sda1 partition is 4096 bytes, or 4k. That's the default block size for ext3 filesystem.

OS block size in Solaris : 

$perl -e '$a=(stat ".")[11]; print $a'
8192

or 
$df -g | grep 'block size' 

Block size in Window Machine  : If  OS  is using ntfs system use the below command  :

C:\>fsutil fsinfo ntfsinfo D:
NTFS Volume Serial Number :        0x7a141d52141d12ad
Version :                                          3.1
Number Sectors :                            0x00000000036b17d0
Total Clusters :                                0x00000000006d62fa
Free Clusters  :                               0x00000000001ed190
Total Reserved :                             0x0000000000000170
Bytes Per Sector  :                         512
Bytes Per Cluster :                     4096   <<===   (block size)
Bytes Per FileRecord Segment       : 1024
Clusters Per FileRecord Segment   : 0
Mft Valid Data Length :                    0x0000000005b64000
Mft Start Lcn  :                                 0x00000000000c0000
Mft2 Start Lcn :                                0x000000000036b17d
Mft Zone Start :                                0x000000000043c9c0
Mft Zone End   :                               0x000000000044b460

or we can also find the block size as : 

--> Go to "My Computer"   --> manage --> Disk Defragmenter --> select any disk -->  click on "analyze" button , then it will present us a report and in that report "Cluster Size" represents the  OS "Block Size".

Enjoy   :-)