ORA-609: opiodr aborting process unknown ospid

The Solution

To resolve this error, you must increase the values for INBOUND_ CONNECT_ TIMEOUT on both the listener side and the server side. If you are getting ORA-609 because of a connection timeout issue, increasing the values should fix the problem. You should change INBOUND_ CONNECT_ TIMEOUT to read as the following:

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Above, the timeout seconds are being set to 120 in place of the default 60. While this simple change should fix most ORA-609 errors, if you continue to see this error then you should try to locate which client is causing the error. Set the sqlnet.ora file parameter to

sqlnet.ora file: DIAG_ADR_ENABLED = OFF


Putain je me suis pris la tête avec mon client pour cette erreur à la con!!!

https://www.tekstream.com/resource-center/ora-609-opiodr-aborting-process-unknown-ospid/

Modèle gratuit de status pour EURL

https://www.wonder.legal/fr/creation-modele/statuts-eurl

eurl compte pro qonto

https://qonto.eu/fr/freelancers/compte-professionnel-eurl

https://qonto.eu/fr/pricing

 

Prendre le pack solo créateur d'entreprise.

 

Tuning SQL ID

http://www.runningoracle.com/product_info.php?products_id=435

 

Summary 
You can create an SQL TUNING TASK manually ad hoc with the following simple steps. 

ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 

0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. 
In case this is a current sql running use the v$session. 

select sql_id from v$session where sid = :x 

1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_69287

2. Run the SQL TUNING TASK

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287');
end;
/

3. You can monitor the processing of the tuning task with the statement 

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_69287'; 

4. When the task has a status=COMPLETED, then run:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/

 

 

https://dbaclass.com/article/how-to-run-sql-tuning-advisor-for-a-sql_id/

 

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 4883,
end_snap => 4888,
sql_id => '1pfbzr2wbnwsy',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'tune_sql_1pfbzr2wbnwsy',
description => 'tuning_du_sql_1pfbzr2wbnwsy');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1pfbzr2wbnwsy');

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('tune_sql_1pfbzr2wbnwsy') from dual;

Stats missing

Pour afficher les stats sur les tables :

set pages 200

col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/

 

Pour les tables avec partitions :

set pages 200

col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/

Pour afficher les stats sur les indexes ;

set pages 500
set lines 200
col index_owner form a10
col index_name for a40
col table_owner form a10
col owner form a10

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by 3
/

Pour afficher les stats manquant avec un taux de % de 10 :

select m.TABLE_OWNER,
m.TABLE_NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,
dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

 

Script for check all the enabled auditing on Database

--Check the parameter is enabled or disable for Audit
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
--Statement Audits Enabled on this Database
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
--Privilege Audits Enabled on this Database
select * from dba_priv_audit_opts;
-- Object Audits Enabled on this Database
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from dba_obj_audit_opts
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-';

--Default Audits Enabled on this Database
select * from all_def_audit_opts;

Cursor environment mismatch V$SQL_SHARED_CURSOR

SELECT
SS1.SQL_TEXT,
SS1.SQL_ID,
SS1.CHILD_NUMBER,
SS2.CHILD_NUMBER,
SS1.SQLTYPE,
SS2.SQLTYPE,
SS1.PARSING_SCHEMA_NAME,
SS2.PARSING_SCHEMA_NAME
FROM
V$SQL SS1,
V$SQL SS2
WHERE
SS1.SQL_ID=SS2.SQL_ID
AND SS1.SQLTYPE < SS2.SQLTYPE
ORDER BY
SS1.SQL_TEXT,
SS1.CHILD_NUMBER,
SS2.CHILD_NUMBER;

SELECT
SSC.*
FROM
V$SQL S,
V$SQL_SHARED_CURSOR SSC
WHERE
S.SQL_ID='xxxxxxxxxxxxx'
AND S.SQL_ID=SSC.SQL_ID
AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
SSC.CHILD_NUMBER;

SELECT
  S.CHILD_NUMBER,
  SBM.POSITION,
  SBM.DATATYPE,
  SBM.MAX_LENGTH,
  SBM.BIND_NAME
FROM
  V$SQL S,
  V$SQL_BIND_METADATA SBM
WHERE
  S.SQL_ID='xxxxxx'
  AND S.CHILD_ADDRESS=SBM.ADDRESS
ORDER BY
  S.CHILD_NUMBER,
  SBM.POSITION;

 

source : https://hoopercharles.wordpress.com/2010/07/23/sql_type_mismatch-in-vsql_shared_cursor/

Stats Oracle SYSTEME et schema

Voir si les stats Oracle sont calculées sur mon schéma : 

 

set lines 150
set pages 3000
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30

define schema=&schema

spool stats_&schema

-- Voir les stats sur mes tables
select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = '&schema' order by table_name;

--Voir les stats sur mes indexes
select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = '&schema' order by TABLE_NAME, INDEX_NAME;

--Voir les stats sur mes colonnes
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = '&schema' order by TABLE_NAME, COLUMN_NAME;

spool off
exit

-- Mise en place des statistiques systèmes et os (sans mode 'concurrent true' !)
execute sys.dbms_stats.gather_system_stats('Start');
execute sys.dbms_stats.gather_system_stats('Stop');
execute sys.dbms_stats.gather_system_stats('interval',interval => 15); # durée de 60 mins
execute sys.dbms_stats.delete_system_stats();

-- Vérifier si les statistiques sont calculées
select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by 1;

 

Autre source d'infos : 

https://fadace.developpez.com/oracle/statistiques-systeme/

http://dbaoraclesql.canalblog.com/archives/2019/03/30/37218417.html