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.

Expertise en SGBDR - Page 45

  • Raw device pour Oracle

    Using Raw Devices for Oracle Databases

    Configure a raw device

    1. Configure the desired device in the /etc/sysconfig/rawdevices file:
      /dev/raw/raw1 	/dev/sdb1
    2. Start and enable the device by running:
      # service rawdevices start
# chkconfig rawdevices on
    At this point, you could set ownership and permissions with chmod and chown. However, because these devices will be created dynamically at boot time, permissions set in this manner will not persist after a reboot.
     
    Creating Oracle database that uses Raw Device option for Storing database files (For RHEL 4 / 3 / 2.1/ CentOS 4 / 3 / 2)
    Binding Logical Volumes to the raw devices:
     
    I have added the below lines into the /etc/sysconfig/rawdevices and restarted the rawdevices service to bind logical volumes
    created in the above steps.

     


    /usr/bin/raw /dev/raw/raw11 oracle_vg/lvol0    # test db -- SYSAUX Tablespace
    /usr/bin/raw /dev/raw/raw12 oracle_vg/lvol1    # test db -- UNDOTBS1
    /usr/bin/raw /dev/raw/raw13 oracle_vg/lvol2    # test db -- TEMP tablespace
    /usr/bin/raw /dev/raw/raw14 oracle_vg/lvol3    # test db -- EXAMPLE tablespace
    /usr/bin/raw /dev/raw/raw15 oracle_vg/lvol4    # test db -- USERS tablespace
    /usr/bin/raw /dev/raw/raw16 oracle_vg/lvol5    # test db -- redo1_1.log
    /usr/bin/raw /dev/raw/raw17 oracle_vg/lvol6    # test db -- redo1_2.log
    /usr/bin/raw /dev/raw/raw18 oracle_vg/lvol7    # test db -- redo1_3.log
    /usr/bin/raw /dev/raw/raw19 oracle_vg/lvol8    # test db -- control1
    /usr/bin/raw /dev/raw/raw20 oracle_vg/lvol9    # test db -- control2
    /usr/bin/raw /dev/raw/raw21 oracle_vg/lvol10    # test db -- control2	
    /usr/bin/raw /dev/raw/raw22 oracle_vg/lvol11    # test db -- spfile
    /usr/bin/raw /dev/raw/raw23 oracle_vg/lvol12    # test db -- pwfile
    /usr/bin/raw /dev/raw/raw24 oracle_vg/lvol13    # test db -- SYSTEM Tablespace

    Also, you need to change the ownership of these devices to oracle user. Raw devices are refreshed with the default
    permissions and ownership every time you reboot your system. For this reason, I add these lines to the /etc/rc.local so
    that every time machine reboots, this devices are assigned correct ownership/permissionsPlease add the below lines
    to the /etc/rc.local

    for i in `seq 1 25`
    do
    chown oracle:dba /dev/raw/raw$i
    chmod 600 /dev/raw/raw$i
    done

  • Quand utiliser le synchronuous I/O

    En anglais :

    You would be keen to disable asynchronous I/O  when you see high average_wait on event db_file_parallel_wait. Other reason for turning it off will be synchronous I/O is more reliable.

    SQL> select event,average_wait from v$system_event where event like 'db file parallel write';
    EVENT                                                            AVERAGE_WAIT
    ---------------------------------------------------------------- ------------
    db file parallel write                                                    28.2  [ centi seconds]

    This is not a very good ASYNCH I/O. Try Synchronous I/O

     

    Other parameters to affect write (as well as read) is dbwriter_processes. When asynchronous I/O operations are slower in operating system in comparison to synchronous I/O then turn off asynchronous I/O by setting disk_asynch_io to false and set multiple db writer processes by increasing dbwriter_processes values from 1 to 2,3 or 4  suitable value to your system. Alternate is  incrase  dbwr_io_slaves from 0 to 2,3,4 suitable value.

     

    Note 1: Asynchronous I/O operations are more prone to block corruptions than synchronous operations so many DBAs disable it and follow practice as mentioned in above paragraph. So if you do not have standby databases and oracle 11g then which autoamatically recovers corrupted block on primary then you would not want asynchronous I/O
    Note 2: For 11g R2 for tuning purpose, the “db file async I/O submit” should be treated as   “db file parallel write” in previous releases.

  • Procédure CALIBRATE_IO

    Introduit dans Oracle Database 11g Release 1 , la procédure CALIBRATE_IO donne une idée des capacités du système de stockage d'Oracle .

    Il y a quelques restrictions associées à la procédure .

    *La procédure doit être appelée par un utilisateur avec le privilège SYSDBA . *TIMED_STATISTICS doit être réglé sur TRUE , ce qui est la valeur par défaut lorsque STATISTICS_LEVEL est réglé sur TYPIQUE .

    *Les Datafiles doivent être accessibles en utilisant E / S asynchrones . Ceci est la valeur par défaut lorsque ASM est utilisé . (paramètre filesystemio_options doit être à setall).

    Doc officielle.

    Exemple d'utilisation de la procédure :

    SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);
 
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/