Home > TroubleShooting > ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1

ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1

symptoms

1. Alert logs show the following ORA errors

adrci> show incidents

ADR Home = /opt/oracle/diag/rdbms/vstbpro/vstbpro1:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
——————– ———————————————————– —————————————-
197285               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:01:59.020491 +08:00
197273               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:04:34.095472 +08:00
197272               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:01:32.782533 +08:00
197271               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 13:59:34.266461 +08:00
197270               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 13:58:31.472789 +08:00
197260               ORA 600 [12240]                                             2009-12-14 17:55:49.662613 +08:00
197221               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:46:58.782261 +08:00
197220               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:43:57.491617 +08:00
197212               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:48:23.337204 +08:00
197144               ORA 700 [kgeade_is_0]                                       2010-01-02 13:44:21.351298 +08:00
197143               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:19.078170 +08:00
197142               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:16.154236 +08:00
197141               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:13.235147 +08:00

2. Query gv$session to trigger the following errors, but there is problem to query v$session

SQL>  select machine from gv$session;
select machine from gv$session
*
ERROR at line 1:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1
allocated
ORA-12801: error signaled in parallel query server P002, instance 3599
ORA-00000: normal, successful completion

3. The initial value of parallel_execution_message_size are different on two instance

Cause

Parallel_execution_message_size is not the same on all nodes in a cluster, a parallel query can not spawn on all nodes parallel execution servers what is need for a query against a gv$ view.

Fix

1. Set the parallel_execution_message_size to be same on both cluster nodes and restart them to take effect immediately

2. Verify the solution by querying the gv$session after restarting. It shows that the query is successfully returned as below.

SQL> select machine from gv$session;

MACHINE
—————————————————————-
wvpsgesb01
wvpsgesb01
wvpdb08
wvpdb08
wvpsgesb01
wvpsgesb01
wvpsgesb01

Advertisements
Categories: TroubleShooting
  1. manjari
    December 1, 2011 at 8:17 am

    alternate command(gv$session) for windows

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: