juin 21 2011

Disk operations with ASM.

Tag: DBUggla @ 11 h 12 min

A short article to keep some useful queries for ASM.

  1. Querie to list disks and groups.
    set pages 1000
    set lines 300
    col path format a50
    col instance_name format a20
    select v$asm_diskgroup.name,v$asm_disk.GROUP_NUMBER,v$asm_client.INSTANCE_NAME,v$asm_disk.PATH,v$asm_disk.name,v$asm_disk.STATE
    from v$asm_diskgroup, v$asm_client, v$asm_disk
    where v$asm_client.GROUP_NUMBER=v$asm_disk.GROUP_NUMBER and v$asm_disk.GROUP_NUMBER=v$asm_diskgroup.GROUP_NUMBER
    order by group_number,path;
    

    NAME                           GROUP_NUMBER INSTANCE_NAME        PATH                                               NAME                           STATE
    ------------------------------ ------------ -------------------- -------------------------------------------------- ------------------------------ --------
    DCLICDGR                                  1 MODEFRR12            /dev/oracle/asmdisk_decldr1                        DCLICDGR_0000                  NORMAL
    DCLICDG                                   2 MODEFRR12            /dev/oracle/asmdisk_decldd1                        DCLICDG_0000                   NORMAL
    DCLICDG                                   2 MODEFRR12            /dev/oracle/asmdisk_decldd2                        DCLICDG_0001                   NORMAL
    DCLICDG                                   2 MODEFRR12            /dev/oracle/asmdisk_decldd3                        DCLICDG_0002                   NORMAL
    DCLICDG                                   2 MODEFRR12            /dev/oracle/asmdisk_decldd4                        DCLICDG_0003                   NORMAL
    ESBDGR                                    3 ASCGFRR12            /dev/oracle/asmdisk_esbdr1                         ESBDGR_0000                    NORMAL
    ESBDG                                     4 ASCGFRR12            /dev/oracle/asmdisk_esbdd1                         ESBDG_0000                     NORMAL
    ESBDG                                     4 ASCGFRR12            /dev/oracle/asmdisk_esbdd2                         ESBDG_0001                     NORMAL
    ESBDG                                     4 ASCGFRR12            /dev/oracle/asmdisk_esbdd3                         ESBDG_0002                     NORMAL
    ETTDGR                                    5 ITECFRR12            /dev/oracle/asmdisk_ettdr1                         ETTDGR_0001                    NORMAL
    ETTDG                                     6 ITECFRR12            /dev/oracle/asmdisk_ettdd1                         ETTDG_0001                     NORMAL
    ETTDG                                     6 ITECFRR12            /dev/oracle/asmdisk_ettdd2                         ETTDG_0002                     NORMAL
    ETTDG                                     6 ITECFRR12            /dev/oracle/asmdisk_ettdd3                         ETTDG_0003                     NORMAL
    OCRDG                                     7 +ASM2                /dev/oracle/asmdisk_ocr1                           OCRDG_0000                     NORMAL
    OCRDG                                     7 +ASM2                /dev/oracle/asmdisk_ocr2                           OCRDG_0001                     NORMAL
    OCRDG                                     7 +ASM2                /dev/oracle/asmdisk_ocr3                           OCRDG_0002                     NORMAL
    PYRADGR                                   8 FISOFRR02            /dev/oracle/asmdisk_pyradr1                        PYRADGR_0000                   NORMAL
    PYRADG                                    9 FISOFRR02            /dev/oracle/asmdisk_pyradd1                        PYRADG_0001                    NORMAL
    PYRADG                                    9 FISOFRR02            /dev/oracle/asmdisk_pyradd2                        PYRADG_0002                    NORMAL
    PYRADG                                    9 FISOFRR02            /dev/oracle/asmdisk_pyradd3                        PYRADG_0003                    NORMAL
    PYRADG                                    9 FISOFRR02            /dev/oracle/asmdisk_pyradd4                        PYRADG_0004                    NORMAL
    DATA3                                    10 EDOCFRE12            /dev/oracle/asmdisk_data3                          DATA3_0000                     NORMAL
    EDOCDG                                   12 EDOCFRU02            /dev/oracle/asmdisk_edocdg                         EDOCDG_0000                    NORMAL
    EDOCARCHDG                               13 EDOCFRU02            /dev/oracle/asmdisk_edocarchdg                     EDOCARCHDG_0000                NORMAL
    
  2. Add / remove disk.
    alter diskgroup EDOCDG add disk '/dev/oracle/asmdisk_xp01','/dev/oracle/asmdisk_xp02'
    alter diskgroup EDOCDG drop disk 'EDOCDG_0000';
    
  3. Follow oprations.
    select * from v$asm_operation;
    
  4. Change rebalancing power (1 –>11) to speed up operations.
    alter diskgroup DATA2 REBALANCE POWER 5;
    

Warning : Take care about ownerships and rights on the disk special files !

A good synthetic article about ASM can be found here : http://www.oracle-base.com/articles/10g/AutomaticStorageManagement10g.php


mai 11 2011

How to crash a prod DB #1

Tag: DB,UnixUggla @ 14 h 24 min

Short extract from .sh_history file….

cd $ORACLE_HOME
ls -la
cd bin
ls -la
ls -la | grep tns*
sqlplus
chmod u=rwx *

Ouch….. ;)
Dear customer, let me try to explain you….

If you don’t understand, here is a clue…

# ls -al oracle
-rwsr-s--x    1 oracle   dba       209822679 Oct 16 2009  oracle


avr 14 2011

HPUX / RAC 11gR2 various tips

Tag: DBUggla @ 11 h 36 min

Here is a list of tips useful to install/troubleshoot a 11gR2 RAC cluster with HPUX 11.31.

  • mrouted configuration to allow multicast.
    Activate the mrouted daemon.
    export MROUTED=1
    
    into /etc/rc.config.d/netdaemons
  • Route to 169 from interlink.
    If like me you don’t have the interconnect link routed and known by DNS, ensure you have a route to 169 via your interconnect interface and IP.
    169.254.0.0           172.16.15.140      U     0    lan5002    1500
    
  • Do not forget patch hppac.
    This patch is not common and required by Oracle Clusterware.
    swinstall -p -x logdetail=true -s calisson.osdgre.external.hp.com:/var/depot/hp-ux/11.31/qpk PHSS_37042
    swinstall -x logdetail=true -s calisson.osdgre.external.hp.com:/var/depot/hp-ux/11.31/qpk PHSS_37042
    
  • Restart root.sh script for troubeshooting.
    This is an excellent tip to troubleshoot root.sh, this will allow to restart only root.sh and not the full install.
    Follow the excellent note : http://www.rachelp.nl/index_kb.php?menu=articles&actie=show&id=61
    dd if=/dev/zero of=/dev/oracle/asmdisk_ocr1 bs=1024k count=2048
    dd if=/dev/zero of=/dev/oracle/asmdisk_ocr2 bs=1024k count=2048
    dd if=/dev/zero of=/dev/oracle/asmdisk_ocr3 bs=1024k count=2048
    
    rm /var/opt/oracle/scls_scr/hx004105/grid/cssfatal
    rm /var/opt/oracle/ocr.loc
    /appl/grid/product/11.2.0/cluster/root.sh
    

    The Oracle Metalink articles ID 1050908.1 and ID 1053970.1 may greatly help to find issues.
  • Full cleanup crs to reinstall from scratch.
    I got issues not cleaning up /var/tmp/.oracle and /tmp/.oracle.
    Cleanup inittab, remove : "h1:3:respawn:/sbin/init.d/init.ohasd run >/dev/null 2>&1 </dev/null"
    init q
    rm -rf /appl/oraInventory
    rm -rf /appl/grid
    rm -rf /var/tmp/.oracle
    rm -rf /tmp/.oracle
    rm -rf /var/opt/oracle
    rm /sbin/init.d/init.ohasd
    rm /sbin/init.d/ohasd
    find /etc/rc*.d -name *ohasd -exec rm {} \;
    for i in 1 2 3; do
    dd if=/dev/zero of=/dev/oracle/asmdisk_ocr$i bs=1024k
    done
    rm /etc/oratab
    rm /usr/local/bin/dbhome /usr/local/bin/coraenv /usr/local/bin/oraenv
    

    Rebuild home.
    mkdir /appl/grid
    chown grid:oinstall /appl/grid
    chmod g+w /appl
    
  • cluvrfy command to check cluster status.
    Before adding a node, the following command should exit successfully.
    cluvfy stage -post hwos -n <existing and new nodes> -verbose
    cluvfy stage -pre nodeadd -n <new node> -fixup -verbose
    

    Especially take care of $ORACLE_HOME ownership and rights.
    A good article about cluvfy : http://satya-racdba.blogspot.com/2010/01/cluvfy-cluvfy-help-or-cluvfy-h-cluvfy.html
  • Add node command reminder.
    ./addNode.sh -silent CLUSTER_NEW_NODES={node_name} CLUSTER_NEW_VIRTUAL_HOSTNAMES={node_name-vip}
    
  • Root.sh to run from /appl/grid ($ORACLE_BASE) (strange).
    To be confirmed, but I got issues depending from the running location. So try to run from various location…
  • Copy 2nd DVD
    11gR2 is composed of 3 DVDs (2xDB + 1xGI), installation will be fine although second can not be extracted. But it will lack DBCA assistants etc… that will make next steps painful.
  • Delete engine to restart installation.
    To remove an engine after installation (because of missing DVD as an example. ;) ).

    • Edit oraInventory/ContentsXML/inventory.xml by removing engine entry.
    • Remove engine’s oracle home.

mar 08 2011

Gestion des rapports AWR.

Tag: DBUggla @ 14 h 21 min

Un petit aide mémoire pour la gestion des rapports AWR.
Requête pour connaître la rétention et la période de lancement.

select
      extract( day from snap_interval) *24*60+
      extract( hour from snap_interval) *60+
      extract( minute from snap_interval ) "Snapshot Interval",
      extract( day from retention) *24*60+
      extract( hour from retention) *60+
      extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

http://www.dba-oracle.com/oracle10g_tuning/t_sdbms_workoad_repository_snapshot_settings.htm

Une bonne page qui résume bien les opérations que l’on peut faire avec les rapports awr.
http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php


mar 01 2011

Passer une base Oracle en archivelog mode.

Tag: DBUggla @ 14 h 15 min

Truc de base, mais j’arrive jamais à me souvenir….

  1. Vérifier l’etat de la base.
    SQL> select name,log_mode from v$database;
    
    NAME      LOG_MODE
    --------- ------------
    MODEFRR1  NOARCHIVELOG
    
  2. Définir la destination des archives.
    SQL> alter system set log_archive_dest_1='location=+DCLICDGR' scope=both sid='*';
    System altered.
    
    SQL> show parameter log_archive_dest_1
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_1                   string      location=+DCLICDGR
    log_archive_dest_10                  string
    ...
    
  3. Arrêt de la base.
    $ srvctl stop database -d MODEFRR1
    
  4. Passage en mode archive.
    $ sqlplus / as    sysdba
    
    SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 1 12:03:41 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area 1068937216 bytes
    Fixed Size                  2188072 bytes
    Variable Size             683674840 bytes
    Database Buffers          377487360 bytes
    Redo Buffers                5586944 bytes
    
    SQL> alter database archivelog;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.
    
  5. Démarrage 2eme instances.
    $ srvctl start database -d MODEFRR1
    
  6. Vérification.
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            +DCLICDGR
    Oldest online log sequence     4
    Next log sequence to archive   5
    Current log sequence           5
    
  7. Commande (que j’oublie à chaque fois) pour générer des logs.
    SQL> alter system archive log current;
    
    System altered.
    
    SQL> alter system switch logfile;
    
    System altered.
    

fév 21 2011

Deplacer les « undo » Oracle

Tag: DBUggla @ 15 h 39 min

Quelques commandes pour déplacer les « undo » sur Oracle RAC 10gR2.

  • Requête pour trouver les undo:
    select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,STATUS,USER_BYTES from dba_data_files where TABLESPACE_NAME like 'UNDO%';
    
  • Créer les nouveaux undo :
    create undo tablespace UNDOTBS3 datafile '/oradata/GFSTEUR1/group01/undotbs03_1.dbf' size 15G autoextend off;
    create undo tablespace UNDOTBS4 datafile '/oradata/GFSTEUR1/group01/undotbs04_1.dbf' size 15G autoextend off;
    
  • Pointer sur les nouveaux undo :

    Attention : ne pas oublier le SID !!!

    ALTER SYSTEM SET undo_tablespace = UNDOTBS3 SCOPE=BOTH SID='GFSTE1R1';
    ALTER SYSTEM SET undo_tablespace = UNDOTBS4 SCOPE=BOTH SID='GFSTE2R1';
    
  • Effacer les anciens undo :
    DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;