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

ORA-00471: DBWR process terminated with error

Symptoms

1. Recently, we enable direct io option (i.e.  convosync=direct) of the database filesystem (vxfs) and restart database

2. After that, create a test table with about 1 million, suddenly the database crashed

3. Try to restart the database, we got the messages below.

ORA-00471: DBWR process terminated with error

Cause

1. Can mount the database in mount, can’t open the database

2. When try to open the database, we find that high IO read/writes is monitored on system by the command iostat -cmnzx 5

Fixed

1. Fallback the database filesystem setting ( i.e. remove the convosync=direct)

2. Can start and open the database

3. The high IO read/writes are not monitored when opening the database

ORA-04030: out of process memory when trying to allocate

Symptoms

SQL> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2031352 bytes
Variable Size             205521160 bytes
Database Buffers          310378496 bytes
Redo Buffers                6356992 bytes
Database mounted.
ORA-04030: out of process memory when trying to allocate 35692296 bytes (pga
heap,KCL disk IO request block array)

Causes

There is not memory enough on system to allocate

Fixed

Tuned down sga_target size to 250M as the following

SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2031352 bytes
Variable Size             213909768 bytes
Database Buffers          301989888 bytes
Redo Buffers                6356992 bytes
Database mounted.

SQL> show parameter target

NAME                                 TYPE        VALUE
———————————— ———– ——————————
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
pga_aggregate_target                 big integer 200M
sga_target                           big integer 500M
SQL> alter system set sga_target=250M scope=spfile;

System altered.

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

Total System Global Area  264241152 bytes
Fixed Size                  2029360 bytes
Variable Size              96471248 bytes
Database Buffers          159383552 bytes
Redo Buffers                6356992 bytes
Database mounted.
Database opened.
SQL>

ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

Symptoms

1. Logon as user account to create a test table

SQL> create table test as select * from all_tables;
create table test as select * from all_tables
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

2. Check space quota on the tablespace ‘SGDM_TBS’

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— ———- ———- ———- ———- —
SGDM_TBS                         33030144          0       4032          0 NO

Causes

The user account has no quota unlimited privilege on its default tablespace ‘SGDM_TBS’

Fixed

Logon as dba; grant quota unlimited on its tablespace

SQL>  alter user epg quota unlimited on SGDM_TBS ;

User altered.

SQL> select tablespace_name,bytes,max_bytes, username from  dba_ts_quotas ;

TABLESPACE_NAME                     BYTES  MAX_BYTES USERNAME
—————————— ———- ———- ——————————
SYSAUX                           16318464         -1 OLAPSYS
SYSAUX                           12582912         -1 WK_TEST
SYSAUX                          169213952         -1 SYSMAN
SYSAUX                             458752         -1 FLOWS_FILES
SGDM_TBS                         85590016         -1 EPG

Max_bytes=-1 means that user EPG has quota unlimited on tablespace SGDM_TBS

exec dbms_mview.refresh returns ORA-12018: following error encountered during code generation

symptoms

1. We can create the materialized view with the following statement

CREATE MATERIALIZED VIEW VI_CHANNEL_MV AS SELECT channel_key, channel_id, status, name_eng, name_chi, display_channel_name_eng, display_channel_name_chi, language FROM vi.vi_channel_t@vi_db_link;

2. We can do complete refresh the mview with complete option as the following

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’,’C’);

PL/SQL procedure successfully completed.

3.  But, can’t refresh the mview with dbms_view like as the following

call dbms_mview.refresh(‘VI_CHANNEL_MV’);

ERROR at line 1:

ORA-12018: following error encountered during code generation for

“EPG”.”VI_CHANNEL_MV”

ORA-00942: table or view does not exist

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2545

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2751

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2720

ORA-06512: at line 1

 Causes

1. Do session trace for refresh the mview by the command  alter session set events ‘10046 trace name context forever, level 12’;

2. Get the following detailed for the error ORA-00942

=====================
PARSE ERROR #9:len=52 dep=2 uid=99 oct=3 lid=99 tim=1533135953690 err=942 
SELECT * FROM “VI”.”MLOG$_VI_CHANNEL_T”@”VI_DB_LINK” <– this shows it can’t access the mview log table
CLOSE #9:c=0,e=0,dep=2,type=0,tim=1533135953690
WAIT #9: nam=’SQL*Net message to dblink’ ela= 0 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931221721
WAIT #9: nam=’SQL*Net message from dblink’ ela= 625 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931222368

Fixed

Logon the source database, grant select privilege to remote login user or role corresponding to that user

SQL> grant select on MLOG$_VI_CHANNEL_T  to SGDM_ROLE;

Logon the destination database, refresh the materialized view successfully

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’);

PL/SQL procedure successfully completed.

Impdp Hung

Symptoms

– Start impd and wait for about 2-3 minutes, it didn’t start import the dump file. Finally, I aborted the import as the following

$ impdp system/oracle parfile=imp_vstb.par

Import: Release 11.1.0.7.0 – 64bit Production on Tuesday, 08 December, 2009 15:33:14

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

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production

With the Real Application Clusters option

UDI-00001: user requested cancel of current operation <– abort the import

Cause

– Founding the following errors in alert.log

 Errors in file /opt/oracle/diag/rdbms/vstbpro/vstbpro2/trace/vstbpro2_pmon_888.trc:

ORA-04031: unable to allocate 56 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback”)

2009-12-08 15:48:46.475000 +08:00

Errors in file /opt/oracle/diag/rdbms/vstbpro/vstbpro2/trace/vstbpro2_pmon_888.trc:

ORA-04031: unable to allocate 56 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback”)

– Founding streams_pool_size was zero

Fixed

Increase the streams_pool_size and sga_target size, after that restart the instance

SQL> alter system set sga_target=2048M scope=spfile sid=’vstbpro2′;

System altered.

SQL> alter system set streams_pool_size=50M scope=spfile sid=’vstbpro2′;

System altered.

Symptons

1. Run impdp as the following

$ impdp system/oracle parfile=imp_vstb.par

Import: Release 11.1.0.7.0 – 64bit Production on Tuesday, 08 December, 2009 15:29:06

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

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option

UDI-22303: operation generated ORACLE error 22303
OCI-22303: type “SYS”.”KU$_STATUS1020″ not found

Cause

caused by setting aq_tm_processes=0

Solution

Change aq_tmp_processes to 1

SQL> show parameter aq

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     0
SQL> alter system set aq_tm_processes=1 scope=both;

Refresh Materialized View Hung

Problem Summary:

– Execute refresh the mview, it held and had no return for a long time. Finally aborting the refreshment.
SQL> exec dbms_mview.refresh(‘CMS_PROG_PROMO_IMG_MV’, ‘C’);

– Perform session trace

SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8′;

– The session trace shows that the following message
WAIT #4: nam=’enq: JI – contention’ ela= 2934487 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545181045005
WAIT #4: nam=’enq: JI – contention’ ela= 1962539 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545183007934
WAIT #4: nam=’enq: JI – contention’ ela= 2939193 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545185947369
*** 2009-12-08 12:07:27.579
WAIT #4: nam=’enq: JI – contention’ ela= 1962673 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545187910260
WAIT #4: nam=’enq: JI – contention’ ela= 2939132 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545190849689
WAIT #4: nam=’enq: JI – contention’ ela= 1962711 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545192812606
WAIT #4: nam=’enq: JI – contention’ ela= 2939196 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545195752019

– The wait event means (enq: JI – contention) that Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view

Problem Caused: There is an another session holding the materialized view

Solution to fix the problem: Kill the holding session

RAC instance resources don’t start automatically after rebooting cluster nodes

Symptoms

1. Can start database instances normally after registering databases into oracle cluster registry.
2. Reboot a cluster node, the database instance can’t start automatically
3. Got the following massages in the cluster registry service daemon log ($CRS_HOME/log/wvpdb09/crsd/crsd.log)

2009-11-26 09:52:13.988: [  CRSAPP][54] StartResource error for ora.sgcmspro.sgcmspro1.inst error code = 1
2009-11-26 09:52:13.988: [  CRSAPP][55] StartResource error for ora.sgdmpro.sgdmpro1.inst error code = 1
2009-11-26 09:52:13.988: [  CRSAPP][56] StartResource error for ora.sgptpro.sgptpro1.inst error code = 1
2009-11-26 09:52:14.083: [  CRSAPP][57] StartResource error for ora.vstbpro.vstbpro1.inst error code = 1
2009-11-26 09:52:15.225: [  CRSRES][53] Start of `ora.wvpdb09.LISTENER_WVPDB09.lsnr` on member `wvpdb09` succeeded.
2009-11-26 09:52:15.693: [  CRSRES][121] CRS-1002: Resource ‘ora.wvpdb09.LISTENER_WVPDB09.lsnr’ is already running on member ‘wvpdb09’
2009-11-26 09:52:15.933: [  CRSRES][56] Start of `ora.sgptpro.sgptpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.936: [  CRSRES][54] Start of `ora.sgcmspro.sgcmspro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.939: [  CRSRES][55] Start of `ora.sgdmpro.sgdmpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.944: [  CRSRES][57] Start of `ora.vstbpro.vstbpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:19.019: [  CRSRES][139] startRunnable: setting CLI values
2009-11-26 09:52:19.030: [  CRSRES][139] Attempting to start `ora.wvpdb09.ons` on member `wvpdb09`
2009-11-26 09:52:20.843: [  CRSRES][139] Start of `ora.wvpdb09.ons` on member `wvpdb09` succeeded.
2009-11-26 09:52:20.870: [  CRSRES][58] Start of `ora.wvpdb09.ASM1.asm` on member `wvpdb09` succeeded.
2009-11-26 09:52:20.872: [  CRSRES][141] Skip online resource: ora.wvpdb09.ons

Caused

The database instance resource started before the ASM instance
Solution

Add the dependency between database instance resource and ASM instance by the following command

# srvctl modify instance -d ORASID -i ORASID1 -s +ASM1
# srvctl modify instance -d ORASID -i ORASID2 -s +ASM2