Sujeet APPS DBA

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>

Enable & Disable ADDM reports in Pluggable Database Oracle

you can configure ADDM reports for Pluggable database(PDBs).

By default it is disabled. You need to enable it manually by executing following commands:

Note:

Default value of AWR_PDB_AUTOFLUSH_ENABLED is false

In Oracle 12c, By default ADDM report is enabled at Container level.

It work same in Oracle 19c as its work in Oracle 12c.

Enable ADDM reports in PDB databases

1. Login in the PDB database.

[oracle@ora admin]$ sqlplus system/manager@PDB

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 15 11:41:32 2020

Last Successful login time: Tue Dec 15 2020 11:36:12 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2. Set the value of the following parameter.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED

NAME TYPE VALUE

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

awr_pdb_autoflush_enabled boolean TRUE

3. Set the AWR snapshot interval greater than 0 in the PDB.

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>120);

Disable the ADDM reports in PDB

1. Login in the PDB database.

2. Set the value of the following parameter.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=FALSE;

SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED

NAME TYPE VALUE

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

awr_pdb_autoflush_enabled boolean FALSE

3. Set the AWR snapshot interval greater than 0 in the PDB.

EXEC dbms_workload_repository.modify_snapshot_settings(interval=>0);

How to Run ADDM report on PDB

ADDM must be run from the Root Container database ( CDB )

Running ADDM from CDB will analyze the data of the PDB also and a sample ADDM looks like below.

[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 15 11:58:12 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name;

CON_NAME

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

CDB$ROOT

SQL>

SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/addmrpt.sql

*****************************END****************************************

How to check what current IDLE_TIME is set at in 11g ORACLE DATABASE?

Description

The Idle Time Resource Usage setting limits the maximum idle time allowed in a session.

Idle time is a continuous inactive period during a session, expressed in minutes.

Long-running queries and other operations are not subject to this limit.

Setting an Idle Time Resource Usage limit helps prevent users from leaving

applications open when they are away from their desks.

On my system:

SQL > select * from DBA_PROFILES where resource_name = 'IDLE_TIME';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT COMMON

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

DEFAULT IDLE_TIME KERNEL UNLIMITED NO

ORA_STIG_PROFILE IDLE_TIME KERNEL 15 NO

2 rows selected.

==============================================================

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');

======================================================

select * from user_resource_limits where resource_name='IDLE_TIME';

Modify profiles to meet the idle time requirement.

alter profile default limit idle_time 15;

Question:

How the idle_time parameter works. I want to disconnect sessions after the idle_time has expired.

Answer:

The idle_time parameter is used within a SQL*Plus profile. Oracle has several ways to disconnect idle sessions, both from within SQL*Plus via resources profiles (connect_time, idle_time), and with the SQL*net expire time parameter.

IMPORTANT NOTE:

You must set resource_limit=TRUE to automatically terminate Oracle connections.

SQL > alter system set resource_limit=true scope=both;

You can use profiles to set the connect time and idle time with "alter profile" statements. Note that idle_time is expressed in minutes and you can express idle_time for 15 minutes by setting idle_time to 15:

SQL > alter profile senior_claim_analyst limit connect_time 100 sessions_per_user 20 idle_time 15;

ORA-03114: not connected to ORACLE

The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,'00:

Shutting down Concurrent Manager : 03-OCT-2016 09:11:42

ORA-03114: not connected to ORACLE

ORACLE error 3114 in AFPRSR-Resubmit_Time

Cause:

AFPRSR-Resubmit_Time failed due to ORA-03114: not connected to ORACLE

.The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,'00:

List of errors encountered:

_ 1 _

Routine AFPCMT encountered an ORACLE error. ORA-03114: not connected

to ORACLE

.Review your error messages for the cause of the error. (=<POINTER>)

_ 2 _

Routine AFPPRD has encountered a fatal error. ORA-03114: not connected

to ORACLE

Solutions:

There are multiple scenarios that cause this error. Check the following list of solutions:

Lets devide it into two scenarios where we get this error.

1.DB Connection issue :-

2. Verify there is no firewall or router setting which terminates connections which are active for longer than x minutes.

3. Verify there is no firewall or router setting which terminates connections which are idle for longer than x minutes.

3. Verify the Oracle configuration. Let the database send a packet every x minutes, so that the firewall,

router, etc does not close the connection due to being 'idle'.