Using SQL Developer 18.4 MacOSX, I’m getting this error
ORA-12801: error signaled in parallel query server P000
ORA-01722:
invalid number
When I try to display the tables list in the connections tab.
I’ve isolated the Query.
When I execute each part of the «union all» one by one, there is no error.
when I execute the all query you got the error.
When I disable the parallel option it works fine (ALTER SESSION disable PARALLEL query).
select * from (
SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, NULL partitioned,
o.sharded,
case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
NULL iot_type,
o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.Dba_OBJECTS O
WHERE O.OWNER = :SCHEMA
AND O.OBJECT_TYPE = 'TABLE'
union all
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, NULL partitioned,
o.sharded,
case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
NULL iot_type,
SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.Dba_OBJECTS O, sys.user_synonyms syn
WHERE syn.table_owner = o.owner
and syn.TABLE_NAME = o.object_NAME
and o.object_type = 'TABLE'
and :INCLUDE_SYNS = 1
)
Ora-12801 parallel query server failed to start is an error that prevents the user from properly running Oracle’s parallel query. This error occurs when the user has attempted to start the instance in the wrong parallel mode. The error can be caused by a combination of different factors, including the presence of incompatible components in the environment, improper parameter settings, or a lack of memory. This document will explain how to troubleshoot and resolve this error.
Prerequisites
Before attempting to fix the ORA-12801 error, make sure the user has the following items:
- Access to the Oracle Error Code Documentation
- Access to Oracle Support
- Understanding of the user’s environment
- Knowledge of the user’s query server
- Knowledge of Oracle parallel query
Identifying the Cause of ORA-12801
The first step in fixing this error is to identify the root cause of the issue. To determine the cause, the user should go through the following steps:
- Review the Oracle error message for clues about the cause of the issue.
- Execute the query again with different system parameters.
- If the same ORA-12801 error appears, continue troubleshooting by examining applicable components within the user’s environment.
- Examine the Oracle documents related to parallel query server issues.
Troubleshooting
Once the user has identified the cause of the ORA-12801 error, they should investigate the following common causes and troubleshooting steps:
Incompatible Components
If incompatible components (such as another query server or version) are present in the user’s environment, the ORA-12801 may occur. To resolve this issue, the user should detect and remove any incompatible components from their environment.
Improper Parameter Settings
Incorrect parameter settings can also cause the ORA-12801 error. The user should investigate the parameters related to the parallel query server, and modify any settings that are incorrect.
Insufficient Memory
In some cases, the ORA-12801 error is caused by a lack of available memory. To resolve this issue, the user should ensure that the system has adequate memory resources allocated for the parallel query server.
FAQ
Q: How can I detect incompatible components in my environment?
A: The user should use Oracle Support resources to detect and remove any incompatible components from the user’s environment.
Q: What should I do if I have insufficient memory?
A: To resolve this issue, the user should ensure that the system has adequate memory resources allocated for the parallel query server.
Q: What parameters related to the parallel query server should I investigate?
A: Parameters related to the parallel query server include the parameter DOP (degree of parallelization), and the parameters SGS_MAX_SERVERS, SGS_GOVERNANCE_LIMIT, and SGS_ENDDATE.
Resources
- Oracle Support Resources
ORA-12801 and ORA-12154 with OracleRestart / GRID / RAC on Oracle Database 12c
Symptom/Analysis:
Using Oracle 12c in a RAC environment, you may encounter the following errors:
ORA-12801: error signaled in parallel query server P000, instance rac2.localdomain:DBRAC2 (2)
ORA-12154: TNS:could not resolve the connect identifier specified
In this article, we will present you an issue that is inspired from a real case:
oli[email protected] ~ $ sqlplus sys/***@DBRAC1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 9 15:03:42 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select count(*) from [email protected]_ONE_MYHOME_TNS, SCOTT.OBJECTS; exit ERROR at line 1: ORA-12801: error signaled in parallel query server P000, instance rac2.localdomain:DBRAC2 (2) ORA-12154: TNS:could not resolve the connect identifier specified
The following queries return answers with no error:
SQL> select count(*) from [email protected]_ONE_MYHOME_TNS; COUNT(*) ---------- 20342
SQL> select count(*) from SCOTT.OBJECTS; COUNT(*) ---------- 90951
Strange !
select * from A is working.
select * from B is working.
select * from A,B is not working.
Let’s check if TNS_ADMIN Oracle environment variable is set in the session:
We will use the dbms_system.get_env function
dbms_system.get_env()
SQL>
set serveroutput on
DECLARE
RetVal VARCHAR2(4000);
BEGIN
dbms_system.get_env('TNS_ADMIN', RetVal);
dbms_output.put_line('TNS_ADMIN: '||RetVal);
END;
/
SQL> 2 3 4 5 6 7
TNS_ADMIN: /u01/app/MyNetworkAdmin/
PL/SQL procedure successfully completed.
TNS_ADMIN is set correctly.
The dblink is working and is correctly created:
dba_db_links
SQL> SELECT owner, db_link, username, host FROM dba_db_links ORDER BY owner, db_link; OWNER DB_LINK USERNAME HOST ------- ------------------------- ---------- -------------------- PUBLIC DBLINK_ONE_MYHOME_TNS SYSTEM DBONE-MYHOME-TNS
DBMS_METADATA.GET_DDL
SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_ONE_MYHOME_TNS','PUBLIC') ddl from dual;
DDL
-------------------------------------------------------------------------
CREATE PUBLIC DATABASE LINK "DBLINK_ONE_MYHOME_TNS"
CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1' USING 'DBONE-MYHOME-TNS'
/u01/app/MyNetworkAdmin/tnsnames.ora
DBONE-MYHOME-TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ONE)
)
)
So what ?
Let’s print the SQL plan:
SQL> explain plan for 2 select count(*) from [email protected]_ONE_MYHOME_TNS,SCOTT.OBJECTS; Explained
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------- Plan hash value: 1869185832 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19591 (2)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | Q1,01 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,01 | PCWP | | | 5 | MERGE JOIN CARTESIAN| | 1810M| 19591 (2)| 00:00:01 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 19911 | 65 (0)| 00:00:01 | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST| :TQ10000 | 19911 | 65 (0)| 00:00:01 | | S->P | BROADCAST | | 9 | REMOTE | DBA_OBJECTS | 19911 | 65 (0)| 00:00:01 | DBLIN~ | R->S | | | 10 | BUFFER SORT | | 90951 | 19526 (2)| 00:00:01 | Q1,01 | PCWP | | | 11 | PX BLOCK ITERATOR | | 90951 | 15 (0)| 00:00:01 | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL| OBJECTS | 90951 | 15 (0)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------- Remote SQL Information (identified by operation id): ---------------------------------------------------- 9 - SELECT 0 FROM "DBA_OBJECTS" "DBA_OBJECTS" (accessing 'DBLINK_ONE_MYHOME_TNS' ) Note ----- - Degree of Parallelism is 4 because of table property 29 rows selected.
Parallelism is enabled for the query.
Let’s disable it !
SQL> ALTER SESSION disable parallel query;
Let’s run our query again:
SQL> select count(*) from [email protected]_ONE_MYHOME_TNS,SCOTT.OBJECTS; COUNT(*) ---------- 1850125242
It is working now, but without using parallelism features.
Problem:
In fact, the problem comes from the environment variable TNS_ADMIN that is not (or not correctly) set for the PX servers process:
When parallelism is enabled, the PX servers are doing all the work. (as shown in our parallel plans)
On the server, PX server processes can be easily identified on the OS level. On Linux they are the processes ora_p***:
Source: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf
[[email protected] admin]$ ps -ef | grep ora_p0
oracle 25803 1 0 11:21 ? 00:00:00 ora_p000_DBRAC1 oracle 25805 1 0 11:21 ? 00:00:00 ora_p001_DBRAC1 oracle 25807 1 0 11:21 ? 00:00:00 ora_p002_DBRAC1 oracle 25809 1 0 11:21 ? 00:00:00 ora_p003_DBRAC1 oracle 28021 1 0 14:25 ? 00:00:00 ora_p004_DBRAC1 oracle 28023 1 0 14:25 ? 00:00:00 ora_p005_DBRAC1 oracle 28025 1 0 14:25 ? 00:00:00 ora_p006_DBRAC1 oracle 28027 1 0 14:25 ? 00:00:00 ora_p007_DBRAC1 oracle 28029 1 0 14:25 ? 00:00:00 ora_p008_DBRAC1 oracle 28031 1 0 14:25 ? 00:00:00 ora_p009_DBRAC1 oracle 28033 1 0 14:25 ? 00:00:00 ora_p00a_DBRAC1 oracle 28035 1 0 14:25 ? 00:00:00 ora_p00b_DBRAC1 oracle 28037 1 0 14:25 ? 00:00:00 ora_p00c_DBRAC1 oracle 28039 1 0 14:25 ? 00:00:00 ora_p00d_DBRAC1 oracle 28041 1 0 14:25 ? 00:00:00 ora_p00e_DBRAC1 oracle 28047 1 0 14:25 ? 00:00:00 ora_p00f_DBRAC1
The file /proc/25803/environ contains the Oracle environment variables set for the P000 Process:
Command “sudo strings /proc/25803/environ | grep TNS_” give no result.
Source: https://blogs.oracle.com/myoraclediary/entry/how_to_check_environment_variables
If we check the environ file of the listener processes, we can see that the TNS_ADMIN is correctly set.
[[email protected] ~]$ ps -ef | grep lsn
oracle 2731 12705 0 14:49 pts/0 00:00:00 grep --color=auto lsn oracle 4176 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit oracle 4309 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit oracle 4320 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit oracle 9059 1 0 12:01 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
[[email protected] ~]$ sudo strings /proc/9059/environ | grep TNS_
TNS_ADMIN=/u01/app/MyNetworkAdmin
This small environ.sh script for the lazy one can list the Oracle environment variables set for all the PX Server process and pmon:
environ.sh
#!/bin/bash
mypspmon='/tmp/pspmon'
myprocess1='pmon_DB'
myprocess2='ora_p00'
ps -ef | grep $myprocess1 | awk '{print $2}' > $mypspmon
ps -ef | grep $myprocess2 | awk '{print $2}' >> $mypspmon
while read ligne;
do
myenvironfile="/proc/${ligne#* }/environ"
if [ -e "$myenvironfile" ]
then
strings $myenvironfile
fi
done < $mypspmon
“sudo ./environ.sh | grep TNS” give no result
The TNS_ADMIN Oracle environment variable is not set for the PX server processes that are spawn to handle the parallel queries.
Solution:
- Be sure to set the TNS_ADMIN with srvctl !!
- Other solutions exist:
Source: https://docs.oracle.com/cd/B19306_01/rac.102/b14197/srvctladmin.htm#i1010191
[[email protected] ~]$ srvctl setenv listener -l LISTENER -t TNS_ADMIN='/u01/app/MyNetworkAdmin/' [[email protected] ~]$ srvctl setenv database -d DBRAC -t TNS_ADMIN='/u01/app/MyNetworkAdmin/'
Let’s check if the variable are correctly set
[[email protected] ~]$ srvctl getenv listener -l LISTENER -t TNS_ADMIN
LISTENER: TNS_ADMIN=/u01/app/MyNetworkAdmin/
[[email protected] ~]$ srvctl getenv database -d DBRAC -t TNS_ADMIN
DBRAC: TNS_ADMIN=/u01/app/MyNetworkAdmin/
TNS_ADMIN seems to be correctly set but we still receive ORA-12801 ORA-12154 errors.
Moreover “sudo ./environ.sh | grep TNS” still gives no result !
You need to restart database to set up thoroughly the TNS_ADMIN environment variable database !!
[[email protected] admin]$ srvctl stop listener -l LISTENER [[email protected] admin]$ srvctl start listener -l LISTENER [[email protected] admin]$ srvctl stop database -d DBRAC [[email protected] admin]$ srvctl start database -d DBRAC
[[email protected] admin]$ srvctl status listener -l LISTENER
Listener LISTENER is enabled Listener LISTENER is running on node(s): rac1,rac2
[[email protected] admin]$ srvctl status database -d DBRAC
Instance DBRAC1 is running on node rac1 Instance DBRAC2 is running on node rac2
Now our “sudo ./environ.sh | grep TNS” commands list the TNS_ADMIN environment variable used by our pmon and px server processes.
TNS_ADMIN=/u01/app/MyNetworkAdmin/ TNS_ADMIN=/u01/app/MyNetworkAdmin/ TNS_ADMIN=/u01/app/MyNetworkAdmin/ TNS_ADMIN=/u01/app/MyNetworkAdmin/ [...] TNS_ADMIN=/u01/app/MyNetworkAdmin/
And our query is working using parallelism features.
One of them would consist in modifying USR_ORA_ENV cluster resources attribute values with crsctl
[[email protected] admin]$ crsctl modify resource ora.dbrac.db -attr "USR_ORA_ENV=TNS_ADMIN=/u01/app/MyNetworkAdmin/"
CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.
In 12c, just add the flag -unsupported ad the end to avoid CRS-4995 errors.
[[email protected] admin]$ crsctl modify resource ora.dbrac.db -attr “USR_ORA_ENV=TNS_ADMIN=/u01/app/MyNetworkAdmin/” -unsupported
Comments:
- This issue is inspired from a real case. You can easily reproduce the issue
- On the server, on the OS level, if ORACLE_HOME is set and TNS_ADMIN is not set then $ORACLE_HOME/network/admin is used to locate tnsname.ora
SCOTT.OBJECTS table was created like that.
SCOTT.OBJECTS
create table SCOTT.OBJECTS as select * from DBA_OBJECTS; alter table scott parallel 32;
Add an entry in your tnsnames.ora and create a dblink
You can remove TNS_ADMIN environment database with “srvctl unsetenv database -d database -t TNS_ADMIN” command. And restart.
So it can be interesting to use symlink (ln -s) in $ORACLE_HOME/network/admin to point and use the same tnsnames.ora file when using sqlplus from the server.
[[email protected] admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/network/admin
[[email protected] admin]$ ll
total 0 lrwxrwxrwx 1 oracle oinstall 36 Jan 10 09:53 listener.ora -> /u01/app/MyNetworkAdmin/listener.ora lrwxrwxrwx 1 oracle oinstall 34 Jan 10 09:53 sqlnet.ora -> /u01/app/MyNetworkAdmin/sqlnet.ora lrwxrwxrwx 1 oracle oinstall 36 Jan 10 09:53 tnsnames.ora -> /u01/app/MyNetworkAdmin/tnsnames.ora
But remember, although the TNS_ADMIN environment is set on the OS level when starting the instance, you need to set the TNS_ADMIN with srvctl or crsctl before !!
If not, you may encounter ORA-12154 errors.
And not only for PDML !!
Post Views: 448
One of the greatest achievements of Oracle database software is its ability to process massive sums of information into a multitude of tables and functions. The downside is that in any system, dealing with huge amounts of data can naturally carry some headaches and frustration along the way. The ORA-12801 would most easily fall into this category, an Oracle error emanating most often in response to errors when processing large jobs.
The Problem
The ORA-12801 is an Oracle error that has some confusing origins, but is described as an error resulting in some form from a parallel query server. The error frequently accompanies another error, often the ORA-01652 (which concerns an inability to extend the temp segment in a tablespace). The ORA-12801 is essentially a general error, a sort of catchall for parallel query issues.
At this juncture, some of you may be wondering yourselves, “What is a parallel query?” First showing up in later versions of Oracle7, a parallel query option lets multiple processes request and receive data and perform operations at the same time. As you would imagine, this can greatly enhance the speed of the system. To achieve this kind of efficiency, there are several conditions for the parallel query that require attention. So what are those conditions and how do they pertain to resolving an ORA-12801 error?
The Solution
An absolutely crucial element in successful parallel query operation is the appropriate use of query slaves. If too few or too many are put to use, the system will receive little advantage from their presence. Generally, a good rule to follow is duplicate the number of disks or computers that the operation is used across, and set that as your number of parallel query slaves. Additionally, your tables should account for the parallel query. Run a full table scan to assess the timing of various settings for the degrees of parallel function to determine the best variable size for your performance.
Due to the nature of the ORA-12801, it’s a good idea to examine the error more closely. Set the event to 10397 to gather up information on the error (including the cause, action and some simple comments). You can also check out trace files in the BACKGROUND_DUMP_TEST and USER_DUMP_TEST for more details, as well as your alert.log. You will typically be given a parallel query server number in the error that will give an idea of how many processes are being run, hopefully reminding your of the aforementioned rule of thumb in keeping your query slave numbers at a level that can accommodate your operation. When accompanied with an error such as ORA-01652, you can typically increase the size of your sort_area_size or TEMP tablespace to allow for the job to run effectively.
Looking forward
Avoiding an ORA-12801 can be complicated, but should you remain cognizant of your parallel query operations and know the basics of investigating your system, you should be okay. Staying on top of parallel queries and ensuring that the proper amount of slave and table degree variables are allocated to the job can go a long way in preventing this (and several other) errors. Furthermore, by referring to your alert.log and dump tests from time to time, you can spot the origins of potential errors or obtain additional information of a pending error that is proving to be enigmatic. If you find that generic system errors such as the ORA-12801 are still tripping you up, it wouldn’t hurt to contact a licensed Oracle consultant to assist you with these problems and optimize your experience with Oracle products.
ORA-12801 is a generic error message and we must check the second message on the error stack to find the real error. From the manual:
ORA-12801: error signaled in parallel query server string
Cause: A parallel query server reached an exception condition.
Action: Check the following error message for the cause, and consult your error manual for the appropriate action.
There are literally thousands of different reasons for an ORA-12801 error, and that error almost never has anything to do with not enough processors. This is an example of how the site you linked to often contains bad or outdated information. Maybe 17 processes was «a lot» 17 years ago but it’s not today. Unfortunately, that site is often the first result from Google.
For troubleshooting your second error, ORA-01555, check the UNDO retention, which is the amount of time in seconds, like this:
select value from v$parameter where name = 'undo_retention'
The amount of space available for the UNDO tablespace is also relevant:
select round(sum(maxbytes)/1024/1024/1024) gb
from dba_data_files
where tablespace_name like '%UNDO%';
Once again, see the manual for more information on the parameter.