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

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

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'.