A short article to keep some useful queries for ASM.
- 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
- Add / remove disk.
alter diskgroup EDOCDG add disk '/dev/oracle/asmdisk_xp01','/dev/oracle/asmdisk_xp02'
alter diskgroup EDOCDG drop disk 'EDOCDG_0000';
- Follow oprations.
select * from v$asm_operation;
- 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
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
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.
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
Truc de base, mais j’arrive jamais à me souvenir….
- Vérifier l’etat de la base.
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
MODEFRR1 NOARCHIVELOG
- 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
...
- Arrêt de la base.
$ srvctl stop database -d MODEFRR1
- 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.
- Démarrage 2eme instances.
$ srvctl start database -d MODEFRR1
- 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
- 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.
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;