blog menu1

ASMDiskCorruptionIssues Part 2


ASMDiskCorruptionIssues Part 2





369 rows selected.

SQL> select * from v$asm_operation;
no rows selected
SQL> select * from gv$asm_operation;
no rows selected
SQL> select * from v$version;

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> select * from V$ASM_ACFSSNAPSHOTS;
no rows selected

SQL> select * from V$ASM_ACFSVOLUMES;

FS_NAMEVOL_DEVICEVOL_LABELPRIMATOTAL_MBFREE_MB
/u02acfs/dev/asm/storageteam-215
TRUE1433614271.3203
/u03acfs/dev/asm/ebernalvol-168
TRUE1433614271.3203

SQL> select * from V$ASM_FILESYSTEM;

FS_NAMEAVAILABLE_TIMEBLOCK_SIZESTATECORRUNUM_VOLTOTAL_SIZETOTAL_FREETOTAL_SNAP_SPACE_USAGE
/u02acfs06-APR-2012 17:06:234AVAILABLEFALSE11433614271.32030
/u03acfs06-APR-2012 17:52:024AVAILABLEFALSE11433614271.32030

SQL> select * from V$ASM_VOLUME;

GROUP_NUMBERVOLUME_NAMECOMPOUND_INDEXSIZE_MBVOLUME_NUMBERREDUNDSTRIPE_COLUMNSSTRIPE_WIDTH_KSTATEFILE_NUMBERINCARNATIONDRL_FILE_NUMBERRESIZE_UNIT_MBUSAGEVOLUME_DEVICEMOUNTPATH
4STORAGETEAM67108865143361UNPROT4128ENABLED256779907659032ACFS/dev/asm/storageteam-215/u02acfs
5EBERNALVOL83886081143361UNPROT4128ENABLED256779910073032ACFS/dev/asm/ebernalvol-168/u03acfs

SQL> select * from V$ASM_VOLUME_STAT;

GROUP_NUMBERVOLUME_NAMECOMPOUND_INDEXVOLUME_NUMBERREADSWRITESREAD_ERRSWRITE_ERRSREAD_TIMEWRITE_TIMEBYTES_READBYTES_WRITTEN
4STORAGETEAM671088651285760400.61520.39527494430898176
5EBERNALVOL838860811285760400.65421.10727494430898176

SQL> select * from V$ASM_USER; 
no rows selected

SQL> select * from V$ASM_USERGROUP; 
no rows selected

SQL> select * from V$ASM_USERGROUP_MEMBER; 
no rows selected

SQL> select * from V$ASM_DISK_IOSTAT;

INSTNAMEDBNAMEGROUP_NUMBERDISK_NUMBERFAILGROUPREADSWRITESREAD_ERRSWRITE_ERRSREAD_TIMEWRITE_TIMEBYTES_READBYTES_WRITTENHOT_READSHOT_WRITESHOT_BYTES_READHOT_BYTES_WRITTENCOLD_READSCOLD_WRITESCOLD_BYTES_READCOLD_BYTES_WRITTEN
DBASM112DBASM11211FG144269118371500607.7255545.885597020162.5912E+1000004426911837155597020162.5912E+10
DBASM112DBASM11212FG243838119007000500.8437674.555345198083.1831E+1000004383811900705345198083.1831E+10
DBASM112DBASM11220FG138585211486160015249.5180105.07495546245121.8314E+100000385852114861695546245121.8314E+10
DBASM112DBASM11223FG2295664911346040026570.468144429.4935.1565E+101.8332E+100000295664911346045.1565E+101.8332E+10
DBASM112DBASM11221FG1151528928364200044587.405112692.2552.8082E+104.6107E+100000151528928364202.8082E+104.6107E+10
DBASM112DBASM11210FG143244140235600587.9548740.5335113528323.7284E+1000004324414023565113528323.7284E+10
DBASM112DBASM11222FG277335828504320019526.81869717.2331.5853E+104.6088E+10000077335828504321.5853E+104.6088E+10
DBASM112DBASM11213FG254269139600100587.5217257.446222136323.1365E+1000005426913960016222136323.1365E+10
+ASMasmvol40ASMDISKNEW92587800.531842.1029981643527168000002587816435271680
+ASMasmvol50ASMDISKNEW102587800.564845.1019981643527168000002587816435271680

10 rows selected.

SQL> select * from V$ASM_DISK_STAT;

GROUP_NUMBERDISK_NUMBERCOMPOUND_INDEXINCARNATIONMOUNT_SHEADER_STATUMODE_STSTATEREDUNDALIBRARYOS_MBTOTAL_MBFREE_MBHOT_USED_MBCOLD_USED_MBNAMEFAILGROUPLABELPATHUDIDPRODUCTCREATE_DATEMOUNT_DATEREPAIR_TIMERREADSWRITESREAD_ERRSWRITE_ERRSREAD_TIMEWRITE_TIMEBYTES_READBYTES_WRITTENPHASH_VALUEHOT_READSHOT_WRITESHOT_BYTES_READHOT_BYTES_WRITTENCOLD_READSCOLD_WRITESCOLD_BYTES_READCOLD_BYTES_WRITTENVSECTOR_SIZEFAILGRO
10167772163915944019CACHEDMEMBERONLINENORMALUNKNOWNASM Library - Generic Linux, version 2.0.4 (KABI_V2)19539195381846801070ASMDISKNEW1FG1ASMDISKNEW1ORCL:ASMDISKNEW1

05-APR-2012 18:19:2906-APR-2012 16:47:240607103111918001470.73836714.5045848064004.4288E+10U185893391000004324414023565113528323.7284E+10N512REGULAR
50838860803915944033CACHEDMEMBERONLINENORMALUNKNOWNASM Library - Generic Linux, version 2.0.4 (KABI_V2)1465314653262014391ASMDISKNEW10ASMDISKNEW10ASMDISKNEW10ORCL:ASMDISKNEW10

06-APR-2012 17:38:5406-APR-2012 17:39:03017598170867200518.1728451631.218721474567002368000
40530856200002587816435271680N512REGULAR
601006632963915944054CACHEDMEMBERONLINENORMALUNKNOWNASM Library - Generic Linux, version 2.0.4 (KABI_V2)146531465314603014391ASMDISKNEW11ASMDISKNEW11ASMDISKNEW11ORCL:ASMDISKNEW11

12-APR-2012 11:33:5212-APR-2012 11:40:05015566154393600525.871418.397647208966323961856
380898169700000000N512REGULAR
701174405123915944057CACHEDMEMBERONLINENORMALUNKNOWNASM Library - Generic Linux, version 2.0.4 (KABI_V2)146531465314603014391ASMDISKNEW12ASMDISKNEW12ASMDISKNEW12ORCL:ASMDISKNEW12

24-APR-2012 12:30:1424-APR-2012 12:30:26012074120084700175.415894.616504176644918669312
151723506700000000N512REGULAR

























































262014391ASMDISKNEW9ASMDISKNEW9ASMDISKNEW9ORCL:ASMDISKNEW9

06-APR-2012 16:59:5406-APR-2012 17:00:05017637170944200492.2798421920.143723072007005521920
393631782800002587816435271680N512REGULAR

13 rows selected.

SQL> select * from V$ASM_TEMPLATE;

GROUP_NUMBERENTRY_NUMBERREDUNDSTRIPESNAMEPRIMMIRR
160MIRRORCOARSEYPARAMETERFILECOLDCOLD
161MIRRORCOARSEYASMPARAMETERFILECOLDCOLD








2175MIRRORCOARSEYDATAGUARDCONFIGCOLDCOLD
2176MIRRORCOARSEYOCRFILECOLDCOLD








615UNPROTCOARSEYDATAGUARDCONFIGCOLDCOLD
616UNPROTCOARSEYOCRFILECOLDCOLD

112 rows selected.

SQL> show parameter asm

NAMETYPEVALUE
asm_diskgroupsstringDATA, RECO, ACFS, ACFSDG, TEST DG, TESTDG2
asm_diskstringstring
asm_power_limitinteger1
asm_preferred_read_failure_groupsstring

SQL> show parameter cluster

NAMETYPEVALUE
cluster_databasebooleanFALSE
cluster_database_instancesinteger1
cluster_interconnectsstring

SQL> show parameter instance_type

NAMETYPEVALUE
instance_typestringasm

SQL> show parameter instance_name

NAMETYPEVALUE
instance_namestring+ASM

SQL> show parameter spfile

NAMETYPEVALUE
spfilestring+SPFILE/asm/asmparameterfile/r egistry.253.779825791

SQL> show sga

Total System Global Area284565504bytes
Fixed Size1344456bytes
Variable Size258055224bytes
ASM Cache25165824bytes

SQL> spool off

Example Output (Script #3):

SQL> -- ASM Versions 10.1, 10.2, 11.1 & 11.2 SQL> SET MARKUP HTML ON SQL> set echo on 
SQL> set pagesize 200
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;

'THISASMREPORTWASGENERATEDAT:==)>'
THIS ASM REPORT WAS GENERATED AT: ==)>05-JUN-2012 23:58:18

SQL> select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

'HOSTNAMEASSOCIATEDWITHTHISASMINSTANCE:==)>'
HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)>asmlnx2.us.oracle.com
SQL> SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/')) FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX, A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
A.SYSTEM_CREATED, A.ALIAS_DIRECTORY, C.TYPE FILE_TYPE FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
WHERE A.GROUP_NUMBER = B.GROUP_NUMBER AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
AND A.FILE_NUMBER = C.FILE_NUMBER(+) AND A.FILE_INCARNATION = C.INCARNATION(+) START WITH (MOD(PINDEX, POWER(2, 24))) = 0
CONNECT BY PRIOR RINDEX = PINDEX;

FULL_PATHSAFILE_TYPE
+DATA/DBASM112YY
+DATA/DBASM112/DATAFILEYY
+DATA/DBASM112/DATAFILE/USERS.259.779841377YNDATAFILE
+DATA/DBASM112/DATAFILE/EXAMPLE.261.779841663YNDATAFILE
+DATA/DBASM112/DATAFILE/SYSTEM.256.779841377YNDATAFILE
+DATA/DBASM112/DATAFILE/SYSAUX.257.779841377YNDATAFILE
+DATA/DBASM112/DATAFILE/UNDOTBS1.258.779841377YNDATAFILE
+DATA/DBASM112/TEMPFILEYY
+DATA/DBASM112/TEMPFILE/TEMP.260.779841641YNTEMPFILE





490 rows selected.
SQL> spool off



Example Output (Script #4):


[grid@asmlnx2 ~]$ cat /tmp/asmcmd_script.out
ASMCMD commands to gather complementary metadata information:
======================

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 14653 262 0 262 0 N ACFS/
MOUNTED EXTERN N 512 4096 1048576 14653 262 0 262 0 N ACFSDG/
MOUNTED NORMAL N 512 4096 2097152 78152 73888 19538 27175 0 N DATA/

======================

Name
access_control.enabled
access_control.umask
au_size
cell.smart_scan_capable
compatible.advm
compatible.asm
compatible.rdbms
disk_repair_time
sector_size
access_control.enabled
access_control.umask
au_size
cell.smart_scan_capable
compatible.advm
compatible.asm
compatible.rdbms
disk_repair_time
sector_size
access_control.enabled
access_control.umask
au_size
cell.smart_scan_capable
compatible.advm
compatible.asm
compatible.rdbms
disk_repair_time
sector_size
access_control.enabled
access_control.umask
au_size
cell.smart_scan_capable
compatible.advm
compatible.asm
compatible.rdbms
disk_repair_time
sector_size
access_control.enabled
access_control.umask
au_size
cell.smart_scan_capable
compatible.asm
compatible.rdbms
disk_repair_time
sector_size
======================

DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
asmvol CONNECTED 11.2.0.3.0 11.2.0.3.0 +ASM ACFS
asmvol CONNECTED 11.2.0.3.0 11.2.0.3.0 +ASM ACFSDG
DBASM112 CONNECTED 11.2.0.3.0 11.2.0.0.0 DBASM112 RECO
DBASM112 CONNECTED 11.2.0.3.0 11.2.0.0.0 DBASM112 DATA
======================

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 14653 262 0 262 0 N ACFS/
MOUNTED EXTERN N 512 4096 1048576 14653 262 0 262 0 N ACFSDG/
MOUNTED NORMAL N 512 4096 2097152 78152 73888 19538 27175 0 N DATA/
MOUNTED NORMAL N 512 4096 2097152 78152 44300 19538 12381 0 N RECO/

======================

Path
ORCL:ASMDISKNEW1
ORCL:ASMDISKNEW10
ORCL:ASMDISKNEW11
======================

DB_Name Instance_Name Path
DBASM112 DBASM112 +data/dbasm112/datafile/example.261.779841663
DBASM112 DBASM112 +data/dbasm112/datafile/sysaux.257.779841377

asmvol +ASM +acfs/STORAGETEAM.256.779907659
asmvol +ASM +acfsdg/EBERNALVOL.256.779910073
======================

Instance Process OSPID Path
1 oracle@asmlnx2.us.oracle.com (DBW0) 6937 ORCL:ASMDISKNEW1
1 oracle@asmlnx2.us.oracle.com (DBW0) 6937 ORCL:ASMDISKNEW14
1 oracle@asmlnx2.us.oracle.com (DBW0) 6937 ORCL:ASMDISKNEW4
1 oracle@asmlnx2.us.oracle.com (DBW0) 6937 ORCL:ASMDISKNEW5
1 oracle@asmlnx2.us.oracle.com (DBW0) 6937 ORCL:ASMDISKNEW8
======================

Group_Name Dsk_Name Reads Writes
DATA ASMDISKNEW1 597954560 44995096576
DATA ASMDISKNEW2 578252800 26305249280
DATA ASMDISKNEW3 618582016 39451017216
DATA ASMDISKNEW4 641384448 31851446272
======================

[oracle@dmorldb04 ~]$ asmcmd -p dsget
parameter:o/*/*
profile:o/*/*

======================

Group_Name Dsk_Num State Power
======================

+SPFILE/asm/asmparameterfile/registry.253.779825791
======================

Group_Name Group_Num Name
DATA 1 ARCHIVELOG
DATA 1 ASMPARAMETERFILE
DATA 1 AUTOBACKUP
DATA 1 BACKUPSET
DATA 1 CHANGETRACKING
DATA 1 CONTROLFILE
DATA 1 DATAFILE
DATA 1 DATAGUARDCONFIG
DATA 1 DUMPSET
DATA 1 FLASHBACK
DATA 1 FLASHFILE
DATA 1 OCRFILE
DATA 1 ONLINELOG
======================

DG_Name User_Num OS_ID OS_Name
======================

DG_Name Grp_Name Owner
======================
Username sysdba sysoper sysasm
SYS TRUE TRUE TRUE
ASMSNMP TRUE FALSE FALSE
======================

Diskgroup Name: ACFS

Volume Name: STORAGETEAM
Volume Device: /dev/asm/storageteam-215
State: ENABLED
Size (MB): 14336
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /u02acfs

Diskgroup Name: ACFSDG

Volume Name: EBERNALVOL
Volume Device: /dev/asm/ebernalvol-168
State: ENABLED
Size (MB): 14336
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /u03acfs



SQL> select * from V$ASM_DISKGROUP_STAT;

GROUP_NUMBERNAMESECTOR_SIZEBLOCK_SIZEALLOCATION_UNIT_SIZESTATETYPETOTAL_MBFREE_MBHOT_USED_MBCOLD_USED_MBREQUIRED_MIRROR_FREE_MBUSABLE_FILE_MBOFFLINE_DISKSCOMPATIBILITYDATABASE_COMPATIBILITYV
1DATA51240962097152MOUNTEDNORMAL7815273888042641953827175011.2.0.0.011.2.0.0.0N
2RECO51240962097152MOUNTEDNORMAL78152447920333601953812627011.2.0.0.011.2.0.0.0N
3SPFILE51240961048576MOUNTEDEXTERN8887882805908828011.2.0.0.010.1.0.0.0N
4ACFS51240961048576MOUNTEDEXTERN146532620143910262011.2.0.0.011.2.0.0.0N
5ACFSDG51240961048576MOUNTEDEXTERN146532620143910262011.2.0.0.011.2.0.0.0N
7TESTDG251240961048576MOUNTEDEXTERN1465314603050014603010.1.0.0.010.1.0.0.0N
6TESTDG51240961048576MOUNTEDEXTERN1465314603050014603010.1.0.0.010.1.0.0.0N
7 rows selected.


No comments:

Post a Comment

Subscribe to: Posts (Atom)
  • Redshift - dataypes
    Amazon Redshift Data Types When you are setting up a connection to an external data source, Spotfire needs to map the data types in the...
  • Oracle DB Growth
    SET   SERVEROUT   ON   SET   VERIFY   OFF   SET   LINES 200   SET   PAGES 2000   DECLARE      v_ts_id                   ...
  • Kafka - Overview
    Producer  --->>  Kakfa server (Kafka Broker), cluster is a group of broker which has  a toic - ---> consumer (request back for mo...