UNIFIED_AUDIT_TRAIL 对应的子表查询 PPA7 _oracle (ppa7) 进程是什么-优快云博客

Applies to:

Generation 1 - Exadata Cloud at Customer (First Generation Cloud Machine) - Version N/A and later
Gen 2 Exadata Cloud at Customer - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

ORA-12850 error happened intermittently when SELECT FROM UNIFIED_AUDIT_TRAIL.
"_px_compilation_debug" trace of PPA7 process shows that ORA-12850 error raised after Slave Plan Signature and QC Plan Signature mismatched.
Slave Plan Signature: SQL Plan Signature (0xb25a54df0):
qkn_qksctxPlanSig    = e973964
ctx_qksctxPlanSig    = 1
rwo_qksctxPlanSig    = 2fd0512f
px_qksctxPlanSig     = 0
dfo_qksctxPlanSig    = 1d2facd7
gd_qksctxPlanSig     = 0
sm_qksctxPlanSig     = 0
obj_qksctxPlanSig    = b9cd79ce　*<<<<<<<<<<<<
pgatgt_qksctxPlanSig = 1
vpd_qksctxPlanSig    = 0
eopn_qksctxPlanSig   = 0
phv2_qksctxPlanSig   = 8d9ffcc
phvfull_qksctxPlanSig = 8d9ffcc
phvfinal_qksctxPlanSig= 0
QC Plan Signature from Parse Msg: SQL Plan Signature (0x7f12a0dd012c):
qkn_qksctxPlanSig    = e973964
ctx_qksctxPlanSig    = 1
rwo_qksctxPlanSig    = 2fd0512f
px_qksctxPlanSig     = 0
dfo_qksctxPlanSig    = 1d2facd7
gd_qksctxPlanSig     = 0
sm_qksctxPlanSig     = 0
obj_qksctxPlanSig    = 45957ca7　*<<<<<<<<<<<<
pgatgt_qksctxPlanSig = 1
vpd_qksctxPlanSig    = 0
eopn_qksctxPlanSig   = 0
phv2_qksctxPlanSig   = 8d9ffcc
phvfull_qksctxPlanSig = 8d9ffcc
phvfinal_qksctxPlanSig= 0
Policies in slave's cursor
Policies in slave uga (QC's policies)
... ...
2020-03-05 19:48:05.276*:PX_Control:kxfx.c@5882:kxfxsStatus():
       joining FALSE ParseState KXFXUPARSE2 (3)　
2020-03-05 19:48:05.409*:PX_Control:kxfx.c@10462:kxfxmai():
       In signal-handler after catching 12850

Cause

This problem is due to Bug 37393792.
UNIFIED_AUDIT_TRAIL is a view UNION ALL both gv_$unified_audit_trail and partition table audsys.aud$unified, and the query of gv_$unified_audit_trail or GV$UNIFIED_AUDIT_TRAIL need to involve PPA7 processes of all instances in database to access .bin file etc. and join the result.
When PPA process found the Plan Signature mismatched with the one of QC process, ORA-12850 raised.

Solution

Apply Patch 37393792 or DBRU 19.26 above in which the fix for 37393792 has been included.

Note : Fix by default disabled, Need to enable the Fix_Control to enable.

Optimizer Bug Fixes With Disabled Fix Control In 19c Doc Id 2702770.1

exec dbms_optim_bundle.set_fix_controls('36004220:1,31720959:1','*', 'BOTH','YES');

As a workaround, SELECT from audsys.aud$unified instead, that does not need to involve PPA7 processes of all instances in database.

--Load .bin file audit log to audsys.aud$unified.
exec dbms_audit_mgmt.load_unified_audit_files;

--Flush Unified Audit log in memory to disk.
exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

--Transfer old records in CLI_SWPXXX to audsys.aud$unified.
exec dbms_audit_mgmt.transfer_unified_audit_records;

--SELECT FROM audsys.aud$unified instead of UNIFIED_AUDIT_TRAIL.

For example:

SELECT
   U.EVENT_TIMESTAMP            AS EVENT_TIMESTAMP,
   U.AUDIT_TYPE                 AS AUDIT_TYPE,
   U.SCN                        AS SCN,
   U.SESSIONID                  AS SESSIONID,
   U.DBUSERNAME                 AS DBUSERNAME,
   U.USERHOST                   AS USERHOST,
   U.OS_USERNAME                AS OS_USERNAME,
   U.CLIENT_PROGRAM_NAME        AS CLIENT_PROGRAM_NAME,
   U.ACTION_NAME                AS ACTION_NAME,
   U.RETURN_CODE                AS RETURN_CODE,
   U.OBJECT_NAME                AS OBJECT_NAME,
   U.SQL_TEXT                   AS SQL_TEXT,
   U.SQL_BINDS                  AS SQL_BINDS,
   U.CLIENT_IDENTIFIER          AS CLIENT_IDENTIFIER,
   U.UNIFIED_AUDIT_POLICIES     AS UNIFIED_AUDIT_POLICIES
FROM (
select   component as AUDIT_TYPE,
        cast((from_tz(auduni.event_timestamp, '00:00') at local) as timestamp) as event_timestamp,
        auduni.scn,
        auduni.sessionid,
        auduni.userid as dbusername,
        auduni.host_name as USERHOST,
        auduni.os_user as OS_USERNAME,
        auduni.client_program_name,
        act1.name ACTION_NAME,
        auduni.RETURN_CODE,
        auduni.obj_name as OBJECT_NAME,
        auduni.sql_text,
        auduni.sql_binds,
        auduni.CLIENT_IDENTIFIER,
        auduni.UNIFIED_AUDIT_POLICIES
from audsys.aud$unified auduni, sys.all_unified_audit_actions act1,
    sys.system_privilege_map spx1, sys.stmt_audit_option_map aom1
where   auduni.action = act1.action   (+)
and - auduni.system_privilege = spx1.privilege (+)
and   auduni.audit_option = aom1.option#   (+)
and   auduni.audit_type = act1.type
) U
WHERE U.EVENT_TIMESTAMP <= TO_TIMESTAMP(&CONDITTION_TIME ,'YYYY-MM-DD HH24:MI:SS.FF');

--------------PPA7 internal communication for RAC

You may have recently enabled "Defender" on the database and possibly on any Application Servers.

Cause

Enabling the Defender (Security Program) can have adverse effects on some processes, such as the PPA7 internal communication for RAC.

Solution

There are always potential risks when enabling 3rd party Security and/or Scanning software, Anti Virus, etc. on a Server that houses an Oracle database.
In this case, the PPA7 process was unable to communicate between RAC nodes.
Only after disabling Defender, did these error messages subside.

Based on the large number of software vendors that offer those and other security services, it is up to the vendor to provide information on how to configure this with Oracle.
Usually, they will provide "exceptions" to their configuration that allows functionality without impact to Oracle.

We provide the following as a generic response only:
How To Configure Anti-Virus On Oracle Database Server (Doc ID 782354.1)

However and again, speak to your vendor for your "Defender" software to see why this occurred and what they recommend.