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

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/

Paramètre obsolète

https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/

 

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

• OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
• OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2