Sujeet APPS DBA: February 2021

Error running SQL and EXEC commands in parallel

This issue has occurred when we do applying the patches on the Apps_Tier.

The cause of this is auto patch previous sessions are still exists in the server and failing the ad workers

with the following error.

Error

11 workers have quit. Waiting for 9 more.

12 workers have quit. Waiting for 8 more.

All workers have quit.

Completed: file apgtxlat.sql on worker 1 for product ap username AP.

Error running SQL and EXEC commands in parallel

You should check the file

AutoPatch error:

Error running SQL and EXEC commands in parallel

Cause:

1. adpatch or adop process was killed from OS level while patch is being applied

2. Database shutdown or terminated

Solution:

Kill the ad patch sessions and try to apply the patch again.

1. [applmgr@oracle log]$ ps -eaf | grep adpatch

appluat 14552 14525 0 08:15 pts/4 00:00:00 grep adpatch

[applmgr@oracle log]$ ps -efa | grep adworker

2.[applmgr@oracle log]$ Mydb>kill -9 <pid>

ADJRIINITPASSWD.sql Fails With An ORA-01031 Error:

Error: Unable to execute statement <

Begin

ad_jar.get_jripasswords(:l_storepass, :l_keypass);

End;

> len = 63

AD Administration error:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SESSION", line 94

ORA-06512: at "APPS.AD_JAR", line 17

ORA-06512: at line 3

AD Administration error:

Unable to get passwords from Vault

Solution

Connect with apps schema.

[applmgr@oracle ~]$ sqlplus apps/******

SQL> create or replace context AD_JAR using AD_JAR;

Context created.

[ora@oracle ~]$ sqlplus sys as sysdba

SQL> GRANT select on DBA_USERS_WITH_DEFPWD to SYSTEM with grant option;

Grant succeeded.

SQL> conn system/******

Connected.

SQL> GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role;

Grant succeeded.

Re-test issue.

ORA-15041: diskgroup "DATA" space exhausted

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M;

ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M

ERROR at line 1:

ORA-01119: error in creating database file '+DATA'

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15041: diskgroup "DATA" space exhausted

solution :-

[oracle@dba ~]$ sqlplus sys/***@QA as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 1 06:10:22 2020

Connected to:

Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production

SQL> truncate table AUD$;

Table truncated.

SQL> commit;

Commit complete.

...........................................................................................................

Check ASM disk status

ERROR:

ORA-02002: error while writing to audit trail

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-02002: error while writing to audit trail

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-02002: error while writing to audit trail

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

----------------------or---------------.............

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M;

ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M

ERROR at line 1:

ORA-01119: error in creating database file '+DATA'

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15041: diskgroup "DATA" space exhausted

>>>>>>>>>>>>>>>>>>>>>>>>>>>>SOLUTION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> select group_number, name, total_mb, free_mb from v$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB

------------ ------------------------------ ---------- ----------

1 DATA 262144 600

2 RECO 262144 257936

SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024

------------------------------ -------------------- --------------------

TEMP 33 0

SQL> select name, total_mb, free_mb, state from v$asm_disk;

NAME TOTAL_MB FREE_MB STATE

------------------------------ ---------- ---------- --------

RECODISK1 65536 49028 NORMAL

RECODISK2 65536 49028 NORMAL

RECODISK3 65536 49044 NORMAL

DATA_0003 65536 152 NORMAL

DATA_0000 65536 140 NORMAL

DATA_0001 65536 172 NORMAL

DATA_0002 65536 136 NORMAL

RECODISK4 65536 49020 NORMAL

8 rows selected.

SQL>

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME ---TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE

-------------- ------

DATA 262144 600 0 600 EXTERN

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE

---------- ---------- --------

/dev/DATADISK4 152 65536 NORMAL

/dev/DATADISK3 140 65536 NORMAL

/dev/DATADISK2 172 65536 NORMAL

/dev/DATADISK1 136 65536 NORMAL

SQL> select group_number, operation, state, error_code from v$asm_operation;

no rows selected

SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024

------------------------------ -------------------- --------------------

TEMP 33 0

SQL>

SQL> select * from dba_temp_files where tablespace_name like 'TEMP';

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS

---------- ------------------------------ ---------- ---------- -------

RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

SHARED INST_ID

------------- ----------

+DATA/CDB/TEMPFILE/temp.264.1029707049 1 TEMP 34603008 4224 ONLINE

1 YES 3.4360E+10 4194302 80 33554432 4096 SHARED

ORA-00020: maximum number of processes (1000) exceeded

Oracle database connection issue happen when number of processes exceeded.

Please find below for more detail in alert.log file.

ORA-00020: maximum number of processes (1000) exceeded

ORA-20 errors will not be written to the alert log for

the next minute. Please look at trace files to see all

the ORA-20 errors.

2020-02-04T04:48:11.688221-05:00

Process m000 submission failed with error = 20

2020-02-04T04:49:06.528563-05:00

ORA-00020: maximum number of processes (1000) exceeded

--------------------------------------------------------------------------

The ORA-00020 is a serious production error because a user cannot connect.

The ORA-00020 is caused by two things:

To fix this, use the

SQL > ALTER SYSTEM KILL command.

Execute following script in the Database.

alter system kill session '957,37560';

You may also need to kill session at the OS level with the KILL -9

[oracle@ora ~]$ kill -9

you can execute following script on Linux server.

kill -9 41158

kill -9 41563

Too few process buckets: Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.

Cause:

All process state objects are in use.

Action:

Increase the value of the PROCESSES initialization parameter.

Check Current Setting of Parameters.

Execute following script in the Database.

sql> show parameter sessions

sql> show parameter processes

sql> show parameter transactions

sql> alter system set processes=1500 scope=spfile;
sql> alter system set sessions=1555 scope=spfile;
sql> alter system set transactions=1610 scope=spfile;

Restart database after this operation as follows.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2230072 bytes

Variable Size 339740872 bytes

Database Buffers 184549376 bytes

Redo Buffers 7942144 bytes

Database mounted.

Database opened.

SQL>

Error running SQL and EXEC commands in parallel

ADJRIINITPASSWD.sql Fails With An ORA-01031 Error:

ORA-15041: diskgroup "DATA" space exhausted

ORA-00020: maximum number of processes (1000) exceeded

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database