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/

Tuning SQL ID

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;

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

Migrate database from One RMAN catalog to another RMAN catalog

Migrate database from One RMAN catalog to another RMAN catalog

http://www.nazmulhuda.info/migrate-database-from-one-rman-catalog-to-another-rman-catalog

 

Migrate database from One RMAN catalog to another RMAN catalog

In that scenario we will move catalog from stagecat(source catalog) to target database CLOUD.

 

[oracle@rmancatalog2 ~]$ rman target / catalog=rman/kjh7Rjsr1

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 21 09:28:47 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CLOUD (DBID=1801493836)

connected to recovery catalog database

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
49382        DB2   3896599226       PRIMARY          DB2
49382        DB2   3896599226       STANDBY          DB1
1                DB1   4139331099       PRIMARY          DB1
1                DB1   4139331099       STANDBY          DB2

SQL> select * from rman.rc_database;

DB_KEY	DBINC_KEY	DBID	NAME	RESELOGS_CHANGE#	RESETLOGS_TIME
1	247199	4139331099	DB1	116371882675	06/07/2015 18:23
49382	49383	3896599226	DB2	90051108188	09/02/2012 16:18

 

RMAN> import catalog rman/U98wsjdw2#@stagecat;

Starting import catalog at 2016-12-21 09:36:33

connected to source recovery catalog database

import validation complete

Finished import catalog at 2016-12-21 09:36:40

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
548781  DB3      829073653        PRIMARY          DB3
514674  DB4      1753676295       PRIMARY          DB4
523618  DB5      3779067219       PRIMARY          DB5
551022  DB6      4018492764       PRIMARY          DB6
545408  DB7      4110887114       PRIMARY          DB7

SQL> select * from rman.rc_database;

DB_KEY	DBINC_KEY	DBID	NAME	RESELOGS_CHANGE#	RESETLOGS_TIME
1	247199	4139331099	DB1	116371882675	06/07/2015 18:23:36
49382	49383	3896599226	DB2	90051108188	09/02/2012 16:18:57
548781	548782	829073653	DB3	1	08/09/2015 09:48:37
514674	514675	1753676295	DB4	1	29/06/2015 09:19:03
523618	523619	3779067219	DB5	1	27/08/2015 12:55:47
551022	551023	4018492764	DB6	1	02/09/2015 09:18:20
545408	545409	4110887114	DB7	1	22/06/2015 13:41:30

On the Source Catalog, the database will be automatically unregistered after the IMPORT CATALOG. If you need to retain the catalog on the source side even after the import then use keyword NO UNREGISTER.

Example:

RMAN> import catalog rman/U98wsjdw2#@stagecat no unregister;

 

 

Checking Firewall Ports on remote Host with SSH auth

#!/bin/bash
 
 input file format is:  hostname
 USAGE: ./scan_oem_ports_remote_hosts.sh hostfile
 if test 'X'"$1" = 'X'; then
         echo -n "ENter full path and name of file containing host names: ";read file
 else
         file="$1"
 fi
 grep -v "^#" $file|while read host 
 do
         if test 'X'"$host" = 'X'; then
                 echo "Malformed line in input file, skipping, should be name $host"
         else
                 RESULTS=$(ssh -n -q $host "timeout 5 bash -c 'oemap01.mydomain.com:4903  NOT open"}'

           if test "$RESULTS" != "OPEN"; then
                         /usr/bin/dig +short  $host | awk -v ip=$host '{print ip,"   " $NF "  -->oemap01.mydomain.com    4903  NOT open"}'  
           fi  
         fi
 done

Other Tools you can use for the same instead of /dev/tcp 
nc -vz -w5 hostname port (TCP) 
nc -vzu -w5 hostname port (UDP) 

Reference: https://superuser.com/questions/621870/test-if-a-port-on-a-remote-system-is-reachable-without-telnet

https://arunbavera.wordpress.com/category/em13c/

Patching OEM 13c Release 2 PSU for OMS and Agent

Patching OMS 13c Release 2 (13.2.0.0.181016) System PSU Patch :-

Download Link : OPatch version 13.9.3.3.0 for EMCC 13.x customers Only (not for FMW 12c) (Patch)

 

Download Link : OPatch version 13.9.3.3.0 for EMCC 13.x customers Only (not for FMW 12c) (Patch)

optimizer_index_cost_adj.sql

col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999


select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;

 

source : https://www.techrepublic.com/article/database-optimization-tweaking-oracle-sql-performance-parameters/#

https://www.techrepublic.com/html/tr/sidebars/5138236-0.html

 

PGA_AGGREGATE_TARGET vs PGA_AGGREGATE_LIMIT

WITH

pgastat_denorm_1 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       snap_id,

       dbid,

       instance_number,

       SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,

       SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,

       SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,

       SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,

       SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,

       SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,

       SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,

       SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,

       SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,

       SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,

       SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,

       SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,

       SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,

       SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem

  FROM dba_hist_pgastat

 WHERE name IN

('PGA memory freed back to OS'

,'aggregate PGA auto target'

,'aggregate PGA target parameter'

,'bytes processed'

,'extra bytes read/written'

,'global memory bound'

,'maximum PGA allocated'

,'maximum PGA used for auto workareas'

,'maximum PGA used for manual workareas'

,'total PGA allocated'

,'total PGA inuse'

,'total PGA used for auto workareas'

,'total PGA used for manual workareas'

,'total freeable PGA memory'

)

   AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)

 GROUP BY

       snap_id,

       dbid,

       instance_number

),

pgastat_denorm_2 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h.dbid,

       h.instance_number,

       s.startup_time,

       MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,

       MIN(h.bytes_processed) bytes_processed,

       MIN(h.extra_bytes_rw) extra_bytes_rw

  FROM pgastat_denorm_1 h,

       dba_hist_snapshot s

 WHERE s.snap_id = h.snap_id

   AND s.dbid = h.dbid

   AND s.instance_number = h.instance_number

 GROUP BY

       h.dbid,

       h.instance_number,

       s.startup_time

),

pgastat_delta AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h1.snap_id,

       h1.dbid,

       h1.instance_number,

       s1.begin_interval_time,

       s1.end_interval_time,

       ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,

       (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,

       h1.aggr_pga_auto_target,

       h1.aggr_pga_target_param,

       (h1.bytes_processed - h0.bytes_processed) bytes_processed,

       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,

       h1.global_memory_bound,

       h1.max_pga_allocated,

       h1.max_pga_used_aut_wa,

       h1.max_pga_used_man_wa,

       h1.tot_pga_allocated,

       h1.tot_pga_inuse,

       h1.tot_pga_used_aut_wa,

       h1.tot_pga_used_man_wa,

       h1.tot_freeable_pga_mem

  FROM pgastat_denorm_1 h0,

       pgastat_denorm_1 h1,

       dba_hist_snapshot s0,

       dba_hist_snapshot s1,

       pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */

 WHERE h1.snap_id = h0.snap_id + 1

   AND h1.dbid = h0.dbid

   AND h1.instance_number = h0.instance_number

   AND s0.snap_id = h0.snap_id

   AND s0.dbid = h0.dbid

   AND s0.instance_number = h0.instance_number

   AND s1.snap_id = h1.snap_id

   AND s1.dbid = h1.dbid

   AND s1.instance_number = h1.instance_number

   AND s1.snap_id = s0.snap_id + 1

   AND s1.startup_time = s0.startup_time

   AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */

   AND min.dbid = s1.dbid

   AND min.instance_number = s1.instance_number

   AND min.startup_time = s1.startup_time

)

SELECT snap_id,

       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,

       TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,

       ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,

       ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,

       ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,

       ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,

       ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,

       ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,

       ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,

       ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,

       ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,

       ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,

       ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,

       ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,

       ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,

       ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,

       0 dummy_15

  FROM pgastat_delta

 GROUP BY

       snap_id

 ORDER BY

       snap_id;

Captured and imported the result into Excel spreadsheet. Then I created the following two charts showing PGA allocation and usage for the past 60 days and 7 days.

Source :https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/

Adjusting the optimizer_index_cost_adj Parameter

https://xcoolwinds.wordpress.com/2013/08/23/adjusting-the-optimizer-index-cost-adj-parameter/

 

Issue the following query to view the average wait times for both of the wait events:

SQL> select event, average_wait from v$system_event
       where event like 'db file s%read';
EVENT                                                            AVERAGE_WAIT
---------------------------------                            -------------------------------
db file sequential read                                                   .91
db file scattered read                                                   1.41

 

SQL>

Based on the output of this query, single block sequential reads take roughly 75% of the time it takes to perform a multiblock (scattered) read. This indicates that the optimizer_index_cost_adj parameter should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the database level isn't recommended—instead, use this parameter sparingly for specific statements where you want to force the use of an index. Note that setting the parameter at the session level requires the DBA to grant the user privileges to issue the alter session command (or you must do it through some other procedural mechanism). A good alternative to having to issue the alter session statement is to specify the OPT_PARAM hint to change the parameter for a specific query, such as /*+ opt_param('optimizer_index_cost_adj',50) */. 

Lister la taille des blobs partitionnés

La requête suivant permet de te lister la taille des partitions qui contiennent des LOBS  :

 

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(DS.BYTES)/(1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE
DS.TABLESPACE_NAME not in ('SYSAUX','SYSTEM')
AND
SEGMENT_TYPE='LOB PARTITION'
GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME,PARTITION_NAME
ORDER BY 4;

La requête suivante permet de te lister la taille des partitions pour le segment TOTO : 

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME ='TOTO'
GROUP BY DS.TABLESPACE_NAME,
SEGMENT_NAME;

Je ne suis pas un escroc, je suis un opportuniste...

Suppression des objets pour un schéma

begin
for f in (select table_name from user_tables) loop
execute immediate 'drop table "'||f.table_name||'" cascade constraints';
end loop;
for f in (select object_type, object_name from user_objects
where object_type in ('SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM','DATABASE LINK')) loop
execute immediate 'drop '||f.object_type||' "'||f.object_name||'"';
end loop;
for f in (select type_name from user_types) loop
execute immediate 'drop type "'||f.type_name||'" force';
end loop;
end;

/

Tuner une requête Oracle

http://www.br8dba.com/tag/manually-run-sql-advisor/#2

[ORCL] List of all the free space in the tablespace or individual datafile

ts_extent_map.sql