Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Oracle12C

  • Problème de compilation de trigger RMAN après montée de version en 12.2


    alter trigger RMAN.VPC_CONTEXT_TRG disable; 

     

    => marche pas :(

    lancer :

     

    @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman

     

    puis UPGRADE CATALOG ; 

    source

  • Merci aux dev SQL

    Merci aux dev sql qui m'ont bien aidé sur ce forum : 

     

    https://www.developpez.net/forums/d2021961/bases-donnees/langage-sql/insert-into-long-s-executer-optimiser/#post11229277

  • 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

    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;

  • 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) */.