ORA-01000: Maximum Open Cursors Exceeded

Once our client report that they are facing error  “ORA-01000: maximum open cursors exceeded” while  running a application . As it seems from error that the error is related to cursors limits i.e, open cursors are exceeding from it's defaults values. To solving this issue ,let's have a look on the open_cursor i.e, what is open_cursor and how it impact into  database.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 18:05:30 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> sho parameter open_cursors
NAME                          TYPE          VALUE
-----------------        ---------      -----------
open_cursors              integer            300

Since the no. of open_cursors is 300. So we list the top 10 sessions which are currently opening most cursors

SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

VALUE           NAME                              SID
-----            -----------------                ----------
300         opened cursors current          131
300        opened cursors current          125
300        opened cursors current           143
300        opened cursors current          149
300        opened cursors current           17
300        opened cursors current           132
300        opened cursors current           23
300        opened cursors current           1
300        opened cursors current           9
300        opened cursors current          10
10 rows selected.

Now we check what make session 131 open to many cursors?

SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=131;

SID  STATUS      EVENT                  WAIT(s)  STATE    BLK_SESN    SQL_ID
---   ----------   ------------------ --------     ---------   ---------    ---------------
131 INACTIVE rdbms ipc message   8745     WAITING                 6mqvntr9ytnga


Since the status of the cursor is INACTIVE so we can we kill the session by using the below command :

SQL> alter system kill session 'sid,serial#' immediate;

The other alternatives is to increase the no. of the open_cursors parameter as :

SQL> alter system set open_cursors=1500 scope=spfile;

In my case i have increased the values of the open_cursors and issue got solved.


Enjoy     :-) 

Open Standby in Read-write Mode When Primary is Lost

There may be scenario  where Primary database is lost and we are only left with the standby database . In this scenario's we have to open the standby database in read-write mode. Below are the steps to convert standby database to Primary database.

1.)  Open standby database in mount state : 
SQL> select name,open_mode from v$database;
NAME       OPEN_MODE
------     -------------
NOIDA      READ ONLY

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
 
Total System Global Area      263639040  bytes
Fixed Size                             1373964      bytes
Variable Size                         230689012  bytes
Database Buffers                  25165824     bytes
Redo Buffers                        6410240       bytes
Database mounted.

SQL> select open_mode ,protection_mode , database_role from v$database ;
OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------        ----------------------       ----------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY

2.) Recover if there is any archive logs:

SQL>recover standby database;
ORA-01153: an incompatible media recovery is active

To solve this issue, we cancel the media recovery by using the below command .

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> recover standby database
ORA-00279: change 2698969 generated at 10/05/2011 16:46:58 needed for thread
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000133_0761068614.0001
ORA-00280: change 2698969 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

3.) Finish the Recovery process :  

The below command will perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable and to open the database in read-write mode we fire the below command : 

SQL>alter database recover managed standby database finish;
Database altered.

4.) Activate the Standby Database : 

SQL> alter database activate physical standby database ;
Database altered.

5.) Check the new status

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------         ----------------------      ---------------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY

6.) Open the Database
SQL> alter database open ;
Database altered.

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE       PROTECTION_MODE             DATABASE_ROLE
---------           ----------------------        --------------------
READ WRITE       MAXIMUM PERFORMANCE      PHYSICAL STANDBY

Enjoy   :-) 

Journeyman On OTN

Hello  All

It is a good day for me. Today i become the Journeyman on OTN site . It's great pleasure while being the journeyman . Working with Oracle is  exciting, challenging and great fun.

I am Passionate about Oracle.Though  i am working with oracle for few years and find that oracle is a great Database . whenever I ask a question to myself how much i know about oracle , the answer is  "NOTHING" . Now my aim is know "something" about oracle . There are lots to be learn and the journey is too long.

The only advise I will like to give others and try to follow myself is that always  work or choose the job that you are enthusiastic or  passionate about .If you are motivated or enthusiastic ,you will be able to learn more and enjoy while working . I beleive that knowledge is not only about learning but about sharing to others.

Enjoy    :-)

EXP-00008: ORACLE error 942 encountered , ORA-00942

Once while visiting OTN , i found a user is facing the below error while performing exporting  

C:\> exp hr/hr tables=batch_job
 Export: Release 10.2.0.3.0 - Production on Mon Oct 10 14:34:35 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
 About to export specified tables via Conventional Path ...
. . exporting table                 BATCH_JOB
EXP-00008 : ORACLE error 942 encountered
ORA-00942: table or view does not exist
Export terminated successfully with warnings.

While connecting connecting with "HR" user

C:\Documents and Settings\admin>sqlplus hr/hr
 SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 10 14:34:58 2011
 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
 SQL>  select count(*) from batch_job;
COUNT(*)
----------
37311

Solution :  There may various possible solution .The following are

1.) One of the possible solution is that the view "SYS.EXU9TYP" which is used by EXP is missing. So to solve this issue follow the below steps : 

I.) Ensure no application user connect to database.

II.) Run the following script
SQL>@?/rdbms/admin/catexp.sql
SQL>@?/rdbms/admin/catpatch.sql

III.) Re-try EXP.

2.) Secondly,it also seems that the problem is clearly with the version of exp i.e;
Exporting  with  version : 10.2.0.3
Connecting  to  database : 10.2.0.1

3.) Export proper home and perform export, even with exp still we can solve our problem but always recommended to use expdp/impdp instead of exp/imp in oracle 10g and higher version . 

4.) Finally to find out exactly what's wrong, check for a trace file in CORE_DUMP_DEST directory.


Enjoy    :-) 

How to Register Listener in the Database ?

The  listener is a separate process that runs on the database server computer. It  receives incoming client connection requests and manages the traffic of these requests to the database server. There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred  as “Registering with the Listener” .  The two methods are 

1.) Static Instance Registration
2.) Dynamic Instance Registration

First we will discuss about the Static Instance Listener  :

This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like : 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = noida)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hyd)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = hyd)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech-199)(PORT = 1521))
  )

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                28-SEP-2011 15:03:39
Uptime                    7 days 0 hr. 22 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "hyd" has 1 instance(s).
  Instance "hyd", status UNKNOWN, has 1 handler(s) for this service...
Service "noida" has 1 instance(s).
  Instance "noida", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance. 

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration :  This dynamic registration feature is called service registration. The registration is performed by the PMON process  an instance background process   of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover  : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.  To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
For example:
SERVICE_NAMES=noida.TECH-199
INSTANCE_NAME=noida

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below: 

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
The command completed successfully.

Now start the listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
The listener supports no services
The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and  if we try to connect to the Instance then it will  throws the error i.e, ORA-12514 :

C:\> tnsping noida
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 16:23:03
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noida)))
OK (40 msec)

Now, we try to connect with Instance "NOIDA"

C:\> sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "NOIDA" . Let's start the instance and check again : 

C:\> set ORACLE_SID=noida
SQL> startup 
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1373964 bytes
Variable Size             222300404 bytes
Database Buffers           33554432 bytes
Redo Buffers                6410240 bytes
Database mounted.
Database opened.

Now check the listener status again :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 19 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "noida.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noidaXDB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noida_DGB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “NOIDA”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. 

Now agian connecting to the Instance :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 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>

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information: 

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.

- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it's not possible to register information to listener periodically. In this case we can do 'Manual service registration' using command: 

SQL> ALTER SYSTEM REGISTER; 

Reference ::  Click Here


Enjoy      :-)