• Author / Uploaded
• Jabras Guppies

• Categories
• Oracle Corporation
• Base de datos Oracle
• Programación de computadoras
• Bases de datos
• Ingeniería Informática

Citation preview

ASM Query ========= 1. Check if ASM is healthy? crsctl check cssd If ASM was never initiated, use the following: /u01/app/oracle/product/10.2.0/asm/bin/localconfig add 2. Check the status of ASM instance ps -ef |grep -i asm 3. Check all ASM related dictionary views. (from the database instance) set linesize 200 set pagesize 200 col object_name format a40 select owner, object_name, object_type from dba_objects where object_name like '%ASM%' AND object_name like 'V%' and object_type='SYNONYM' order by 2; 4. How to create diskgroup? From ASM instance SQL: External CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK 'ORCL:VOL_SDC1', 'ORCL:V OL_SDC2'; CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK 'ORCL:VOL_SDC7', 'ORCL:VOL_SDC8'; Normal CREATE DISKGROUP DATA NORMAL REDUNDANCY FAILGROUP DATA DISK 'ORCL:VOL_SDC10' FAILGROUP DATA_FL1 DISK 'ORCL:VOL_SDD10'; High CREATE DISKGROUP DATA NORMAL HIGH FAILGROUP DATA DISK 'ORCL:VOL_SDC10' FAILGROUP DATA_FL1 DISK 'ORCL:VOL_SDD10' FAILGROUP DATA_FL2 DISK 'ORCL:VOL_SDE10' 5. How to completely drop diskgroup? DROP DISKGROUP DATA; 6. To add the disk to diskgroup ALTER DISKGROUP DATA ADD DISK '/dev/sdc4' NAME VOL_SDC4, '/dev/sdc5' NAME VOL_SD C5; 7. To drop the disk from diskgroup ALTER DISKGROUP DATA DROP DISK VOL_SDC45; 8. How to check the diskspace usage select name, round((round(total_mb,2)-round(free_mb,2))/1024,2) as USED_GB, roun d(free_mb/1024,2) as FREE_GB, round(total_mb/1024,2) as TOTAL_GB from v$asm_diskgroup; 9. To check the name and path of all the ASM disks (run from ASM instance) set linesize 200

set pagesize 200 col path format a40 select name, path from v$asm_disk order by 1; ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 2 of 11 8/30/2012 12:47 PM 10. Run from asm instance to check overall asm usage (you can run from db instan ce too but it will show only disks which are related database). set linesize 200 set pagesize 200 select d.group_number, dg.name as diskgroup_name, d.name as disk_name, d.total_mb from V$asm_disk d, v$asm_diskgroup dg where d.group_number=dg.group_number; 11. How to check the diskgroup details: set linesize 200 col DG_NAME format a10; select b.name as DG_NAME, a.disk_number, a.name as Disk_NAME, round(a.total_MB/1024,2) TS_in_GB, round(a.free_MB/1024,2) FS_in_GB, a.mode_status, a.state, a.header_status from V$asm_disk a, v$asm_diskgroup b where a.group_number=b.group_number --and b.name='DATA order by b.name, a.disk_number; 12. To check each DISK GROUP Details. The query will ask for Diskgroup NAME. --Login to ASM Instance as sysdba set linesize 200 col DG_NAME format a10; select b.name as DG_NAME, a.disk_number, a.name as Disk_NAME, round(a.total_MB/1024,2) TS_in_GB, ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 3 of 11 8/30/2012 12:47 PM round(a.free_MB/1024,2) FS_in_GB, a.mode_status, a.state, a.header_status from V$asm_disk a, v$asm_diskgroup b where a.group_number=b.group_number and b.name='&ENTER_DISKGROUP_NAME' order by b.name, a.disk_number;

13. To find out if there are any FREE DISKS available which are already presente d to ASM instance and not allotted to any Disk Group. --Login to ASM instance and execute the following. col name format a8 col path format a16 set linesize 200 select group_number, disk_number, state, name, path, MOUNT_STATUS, HEADER_STATUS , MODE_STATUS from v$asm_disk where header_status in ('FORMER', 'CANDIDATE'); 14. List the disks that are presented to ASM As root: /etc/init.d/oracleasm listdisks 15. Check ASM Mapping: chk_asm_mapping.sh /etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` | cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | while read v_asmdisk v_minor v_major do v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'` echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor, $v_major]" done ASM disk C1D1P1 based on /dev/ [105, 17] ASM disk C1D1P2 based on /dev/ [105, 18] ASM disk C1D1P3 based on /dev/ [105, 19] ASM disk C1D1P5 based on /dev/ [105, 21] ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 4 of 11 8/30/2012 12:47 PM ASM disk C1D1P6 based on /dev/ [105, 22] ASM disk C1D1P7 based on /dev/ [105, 23] ASM disk C1D1P8 based on /dev/ [105, 24] ASM disk C1D2P1 based on /dev/ [105, 33] ASM disk C1D2P2 based on /dev/ [105, 34] ASM disk C1D2P3 based on /dev/ [105, 35] ASM disk C1D2P5 based on /dev/ [105, 37] ASM disk C1D2P6 based on /dev/ [105, 38] ASM disk C1D2P7 based on /dev/ [105, 39] ASM disk C1D2P8 based on /dev/ [105, 40] ASM disk C1D3P1 based on /dev/ [105, 49] ASM disk C1D3P2 based on /dev/ [105, 50] ASM disk C1D3P3 based on /dev/ [105, 51] ASM disk C1D3P5 based on /dev/ [105, 53] ASM disk C1D3P6 based on /dev/ [105, 54] ASM disk C1D3P7 based on /dev/ [105, 55] ASM disk C1D3P8 based on /dev/ [105, 56] 16. To mount all disk group alter diskgroup all mount; 17. To mount one disk group alter diskgroup DATA mount; 18. To dismount all disks group alter diskgroup all dismount; 19. To dismount one disk group

alter diskgroup data1 dismount 20. How to add a datafile in a DISKGROUP? a) Find which tablespace needs a datafile set linesize 200 set pagesize 200 col tablespace_name format a15 col name format a50 ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 5 of 11 8/30/2012 12:47 PM --break on tablespace_name skip 1 --compute sum of size_in_mb on tablespace_name select t.name tablespace_name, d.name, d.bytes/1024/1024 as size_in_MB from v$tablespace t inner join V$datafile d on t.ts#=d.ts# order by t.ts#; set linesize 84 set pagesize 30 clear break b) Find out which diskgroup you want to add datafile to? select name from v$asm_diskgroup; or Run #8 from this document to find the free diskgroup you want to add datafile to . c) Once the diskgroup is identified, add a datafile using the following syntax. alter tablespace users add datafile '+DATA' size 100M; alter tablespace users add datafile '+DATA' size 32M; d) Verify the datafile is created. Run the SQL from 20a again. ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 6 of 11 8/30/2012 12:47 PM 21) Can you create a datafile with our choice of datafile name? Yes, we can. See the example below. alter tablespace users add datafile '+DATA/imranpr/datafile/users04.dbf' size 64 M; 22) How to create alias on datafile names rather than the fully qualified ASM fi lenames? export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm export ORACLE_SID=+ASM sqlplus / as sysdba select instance_name from v$instance; ALTER DISKGROUP +DATA add alias +DATA/imranpr/datafile/users01.dbf for +DATA/imranpr/datafile/users.283.773012059 ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 7 of 11 8/30/2012 12:47 PM 23. What are all the oracleasm commands in 10g? 24. What are all the oracleasm commands in 11g? /etc/init.d/oracleasm configure

/etc/init.d/oracleasm status /etc/init.d/oracleasm disable /etc/init.d/oracleasm enable /etc/init.d/oracleasm createdisk vol_sdc1 /dev/sdc1 /etc/init.d/oracleasm scandisks /etc/init.d/oracleasm deletedisk vol_sdc1 /etc/init.d/oracleasm scandisks /etc/init.d/oracleasm listdisks /etc/init.d/oracleasm querydisks /etc/init.d/oracleasm start /etc/init.d/oracleasm stop /etc/init.d/oracleasm status /etc/init.d/oracleasm restart [root@RHEL5-PH-223 ~]# /usr/sbin/oracleasm Usage: oracleasm [--exec-path=] [ ] oracleasm --exec-path oracleasm -h oracleasm -V The basic oracleasm commands are: configure Configure the Oracle Linux ASMLib driver init Load and initialize the ASMLib driver exit Stop the ASMLib driver ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 8 of 11 8/30/2012 12:47 PM scandisks Scan the system for Oracle ASMLib disks status Display the status of the Oracle ASMLib driver listdisks List known Oracle ASMLib disks querydisk Determine if a disk belongs to Oracle ASMlib createdisk Allocate a device for Oracle ASMLib use deletedisk Return a device to the operating system renamedisk Change the label of an Oracle ASMlib disk update-driver Download the latest ASMLib driver 25. Other commands for managing non-RAC (possibly 10g commands?) init+ASM.ora .cluster_database= true .asm_diskstring = /dev/sd*1 .instance_type=asm .shared_pool_size=100M .large_pool_size = 80M .db_cache_size=60M .asm_diskgroups = DATA , FRA .processes=128 Initialize ASM for non-RAC ./localconfig add Manually start CSSD (non-RAC) /etc/init.d/init.cssd start Manually stop CSSD (non-RAC) /etc/init.d/init.cssd stop Resetting CSS to new Oracle Home localconfig reset /apps/oracle/product/11.1.0/ASM crsctl check has crsctl stop has crsctl start has crsctl check resource crsctl start resource -all crsctl stop resource -all crs_stat -t ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H

oo5AIeQ8LifUwS_... 9 of 11 8/30/2012 12:47 PM ******************************************************************************** **************************************** Starting from 11g: crsctl stop resource -all ? when ran on RAC, it will shutdown resources on all n odes. crsctl start resource -all ? when ran on RAC, it will startup all resource on al l nodes. Other notes: ******************************************************************************** **************** [root@Linux-VM-204 ~]# /u01/app/oracle/product/10.2.0/asm/bin/localconfig Usage:/u01/app/oracle/product/10.2.0/asm/bin/localconfig [add] [delete] [ reset ] [-silent] [-paramfile ] fdisk -l /dev/sdc Install ASMLib /etc/init.d/oracleasm configure /etc/init.d/oracleasm status /etc/init.d/oracleasm disable /etc/init.d/oracleasm enable /etc/init.d/oracleasm createdisk vol_sdc1 /dev/sdc1 /etc/init.d/oracleasm scandisks /etc/init.d/oracleasm deletedisk vol_sdc1 /etc/init.d/oracleasm scandisks /etc/init.d/oracleasm listdisks /etc/init.d/oracleasm querydisks /etc/init.d/oracleasm start /etc/init.d/oracleasm stop /etc/init.d/oracleasm status /etc/init.d/oracleasm restart Good Links for scripts: http://www.shutdownabort.com/dbaqueries/Administration_ASM.php http://www.dbasupport.com/oracle/ora10g/ASM0302.shtml 10g ASM Document http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm#i102 1337 11g ASM Document http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmfiles.htm#i1020023 ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 10 of 11 8/30/2012 12:47 PM ASM Queries --Moid - Google Docs https://docs.google.com/document/d/1B8aAzHjmO-H oo5AIeQ8LifUwS_... 11 of 11 8/30/2012 12:47 PM