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.

Le dernier des DBAs - Page 6

  • 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/

  • 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.

  • 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

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

  • Constraint NOT NULL missing

     

    Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

    After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
    – Data Pump export of tables with NOT NULL and referential integrity constraints
    – Data Pump import with EXCLUDE=CONSTRAINT

     

    http://blog.umairmansoob.com/data-dump-import-missing-not-null-constraints/

  • 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;

    /