磁盘oracle认证

Oracle12cASM新功能展示KamranAghayevA.
Oracle认证大师,ACE总监Azercell电信公司DBA团队负责人Oracle认证大师OracleACE总监"RMAN的备份和恢复"一书的作者博客http://www.
kamranagayev.
com阿塞拜疆甲骨文用户组主席(AzerOUG)关于我有多少人使用ASM在座的必要调查有多少人在生产环境中使用Oracle12c数据库有多少人使用过任何Oracle12cASM的新功能OracleFlexASMASM互联–ASM专用网络ASM磁盘组相关的新功能失败组维修时间ASM磁盘故障处理的改进更快的磁盘重新同步和更换ASM磁盘清理ASM远程访问Oracle12cASM新功能OracleFlexASMOracleFlexASMOracleFlexASM[oracle@node1~]$asmcmdshowclustermodeASMcluster:Flexmodeenabled展示OracleASM集群的当前模式.
OracleFlexASM–GetthestatusofFlexASM[oracle@node1~]$srvctlstatusasm-detailASMisrunningonnode1,node2,node3ASMisenabled.
[oracle@node1database]$srvctlconfigasmASMhome:/u01/app/oracle/product/12.
0.
1/db_1Passwordfile:+DATA/orapwASMASMlistener:LISTENERASMinstancecount:3ClusterASMlistener:ASMNET1LSNR_ASM[oracle@node1database]$OracleFlexASM–增加/减少实例数[oracle@node1~]$srvctlmodifyasm–count2[oracle@node1database]$srvctlstatusasm-detailASMisrunningonnode1,node2ASMisenabled.
OracleFlexASM–增加/减少实例数oracle@node1database]$srvctlstartasm-nnode3PRCR-1013:Failedtostartresourceora.
asmPRCR-1064:Failedtostartresourceora.
asmonnodenode3CRS-2552:Therearenoavailableinstancesofresource'ora.
asm'tostart.
[oracle@node1database]$srvctlmodifyasm-count3[oracle@node1database]$srvctlstatusasm-detailASMisrunningonnode1,node2ASMisenabled.
[oracle@node1database]$srvctlstartasm-nnode3[oracle@node1database]$srvctlstatusasm-detailASMisrunningonnode1,node2,node3ASMisenabled.
[oracle@node1database]$DB1+ASM1node1DB2+ASM2node2DB3+ASM3node3OracleFlexASM–增加/减少实例数[oracle@node1database]$srvctlmodifyasm-count2DB1+ASM1node1DB2+ASM2node2DB3+ASM3node3OracleFlexASM–增加/减少实例数[oracle@node1database]$srvctlmodifyasm-count2DB1+ASM1node1DB2+ASM2node2DB3node3[oracle@node1~]$srvctlstatusasm-detailASMisrunningonnode1,node2OracleFlexASM–增加/减少实例数NOTE:ASMBprocessexitingduetoASMinstanceshutdown(inactivefor3seconds)NOTE:ASMBclearingidlegroupsbeforeexitTueNov0305:36:532015NOTE:client+ASM3:+ASMderegisteredTueNov0305:36:552015NOTE:clientRACDB3:RACDBshouldfailoverShuttingdowninstance(immediate)TueNov0305:37:012015NOTE:clientRACDB3:RACDBid0x10001hasreconnectedtoASMinst+ASM2TueNov0305:37:012015NOTE:ASMBregisteringwithASMinstanceasclient0x10001(reg:457157236)NOTE:ASMBconnectedtoASMinstance+ASM2(Flexmode;clientid0x10001)NOTE:ASMBrebuildingASMserverstateNOTE:ASMBrebuilt1(of1)groupsNOTE:ASMBrebuilt21(of21)allocatedfilesNOTE:fetchingnewlockedextentsfromserverNOTE:0locksestablished;0pendingwritessenttoserverSUCCESS:ASMBreconnected&completedASMserverstateOracleFlexASM–增加/减少实例数SQL>SELECTinst_id,group_number,instance_name,db_name,statusFROMgv$asm_clientORDERBYinstance_name;INST_IDGROUP_NUMBERINSTANCE_NAMEDB_NAMESTATUS11+ASM1+ASMCONNECTED21+ASM2+ASMCONNECTED11RACDB1RACDBCONNECTED21RACDB2RACDBCONNECTED21RACDB3RACDBCONNECTEDOracleFlexASM–增加/减少实例数[oracle@node1database]$srvctlmodifyasm-count1DB1+ASM1node1DB2+ASM2node2DB3node3OracleFlexASM–增加/减少实例数[oracle@node1database]$srvctlmodifyasm-count1DB1node1DB3node3DB2+ASM2node2[oracle@node1~]$srvctlstatusasm-detailASMisrunningonnode2ASMisenabled.
OracleFlexASM–增加/减少实例数TueNov0305:50:312015NOTE:ASMBregisteringwithASMinstanceasclient0x10002NOTE:ASMBconnectedtoASMinstance+ASM2(Flexmode;clientid0x10002)NOTE:ASMBrebuildingASMserverstateNOTE:ASMBrebuilt1(of1)groupsSQL>SELECTinst_id,group_number,instance_name,db_name,statusFROMgv$asm_clientORDERBYinstance_name;INST_IDGROUP_NUMBERINSTANCE_NAMEDB_NAMESTATUS21+ASM2+ASMCONNECTED21RACDB1RACDBCONNECTED21RACDB2RACDBCONNECTED21RACDB3RACDBCONNECTEDOracleFlexASM–增加/减少实例数SQL>ALTERSYSTEMRELOCATECLIENT'RACDB1:RACDB';Systemaltered.
SQL>SELECTinst_id,group_number,instance_name,db_name,statusFROMgv$asm_clientORDERBYinstance_name;INST_IDGROUP_NUMBERINSTANCE_NAMEDB_NAMESTATUS21+ASM2+ASMCONNECTED11RACDB1RACDBCONNECTED21RACDB2RACDBCONNECTED21RACDB3RACDBCONNECTEDOracleFlexASM–重新定位RDBMS实例[oracle@node2trace]$ps-ef|greppmonoracle224021001:2800:00:00asm_pmon_+ASM3[oracle@node2database]$kill-922402NOTE:ASMBregisteringwithASMinstanceasclient0x10002NOTE:ASMBconnectedtoASMinstance+ASM1(Flexmode;clientNOTE:ASMBrebuildingASMserverstate[oracle@node1~]$srvctlstatusdatabase-dRACDBInstanceRACDB1isrunningonnodenode1InstanceRACDB2isrunningonnodenode3InstanceRACDB3isrunningonnodenode2[oracle@node1~]$srvctlstatusasm-detailASMisrunningonnode1,node3ASMisenabled.
OracleFlexASM–增加/减少实例数ASM互联…[oracle@node1~]$oifcfggetifeth0192.
168.
0.
0globalpubliceth1192.
168.
1.
0globalcluster_interconnect,asmASM密码文件ASMCMD>cd+DATA/ASM/PASSWORDASMCMD>ls-ltTypeRedundStripedTimeSysNamePASSWORDUNPROTCOARSEOCT2719:00:00Ypwdasm.
256.
894222589ASMCMD>pwget--asm+DATA/ASM/PASSWORD/pwdasm.
282.
894937839ASMCMD>[oracle@node1~]$crsctlstatresora.
asm-p|grepPWFILEPWFILE=+DATA/ASM/PASSWORD/pwdasm.
282.
894937839ASMPasswordfile[oracle@node1~]$orapwdfile='+DATA'asm=yEnterpasswordforSYS:[oracle@node1~]$[oracle@node1~]$sqlplussys/oracle@node2:/+ASMassysdbaSQL*Plus:Release12.
1.
0.
1.
0ProductiononThuNov506:23:082015Copyright(c)1982,2013,Oracle.
Allrightsreserved.
Connectedto:OracleDatabase12cEnterpriseEditionRelease12.
1.
0.
1.
0-64bitProductionWiththeRealApplicationClustersandAutomaticStorageManagementoptionsSQL>ASMPasswordfile–12cASMCMDcommandspwcopyCopiesapasswordfiletothespecifiedlocationpwcreateCreatesapasswordfileatthespecifiedlocationpwdeleteDeletesapasswordfileatthespecifiedlocationpwgetReturnsthelocationofthepasswordfilepwmoveMovesthelocationofthepasswordfilepwsetSetsthelocationofthepasswordfileshowversionToshowtheversionofASMyouarerunningshowversion--softwarepatchToknowthepatchleveloftheentireclustershowclustermodecommandshowsifFlexASMisenabledornotASMPasswordfile–12cASMCMD命令ASMCMD>helppwcopypwcopyCopyapasswordfilefromonediskgrouptoanother,fromOStoadiskgroup,orfromadiskgrouptoOSSynopsispwcopy[--dbuniquename|--asm]ExamplesASMCMD[+]>pwcopy--asm+DG/mydir/mypwfile+DG1/pwfiles/mypwfilecopying+DG/mypwfile->+DG1/pwfiles/mypwfileASMCMD[+]>pwcopy+DG/mydir/mypwfile/home/asmuser/pwfiles/mypwfilecopying+DG/mypwfile->/home/asmuser/pwfiles/mypwfileSeeAlsopwcreatepwdeletepwmovepwsetpwgetASMPasswordfile–恢复ASM密码文件2014-02-0214:05:43.
885:[OCRRAW][3619968576]proprinit:Couldnotopenrawdevice2014-02-0214:05:43.
887:[OCRAPI][3619968576]a_init:16!
:Backendinitunsuccessful:[26]2014-02-0214:05:43.
888:[CRSOCR][3619968576]OCRcontextinitfailure.
Error:PROC-26:ErrorwhileaccessingthephysicalstorageORA-01017:invalidusername/password;logondeniedORA-15077:couldnotlocateASMinstanceservingarequireddiskgroup如果ASM密码文件不可用,那集群件就不会显示ASMPasswordfile–恢复ASM密码文件解除配置和重新配置集群!
ASM密码文件–备份ASM密码文件ASMCMD>pwget--asmASMCMD>pwcopy-–asm+DATA/ASM/PASSWORD/pwdasm.
original+DG_NEW/pwdasm.
bak以如下命令恢复它:ASMCMD>pwcopy–asm+DG_NEW/pwdasm.
bak+DG_NEW2/pwdasm_new(DocID1644005.
1)重命名磁盘SQL>CREATEDISKGROUPmydgNORMALREDUNDANCYFAILGROUPfgroup1DISK'/dev/asm-disk5'FAILGROUPfgroup2DISK'/dev/asm-disk6';SQL>SELECTgroup_number,name,pathFROMv$asm_diskGROUP_NUMBERNAMEPATH2MYDG_0000/dev/asm-disk52MYDG_0001/dev/asm-disk6重命名磁盘SQL>alterdiskgroupmydgdismount;Diskgroupaltered.
SQL>alterdiskgroupmydgmountrestricted;Diskgroupaltered.
SQL>alterdiskgroupmydgrenamedisk'MYDG_0000'to'MYDG_DISK1','MYDG_0001'to'MYDG_DISK2';Diskgroupaltered.
SQL>SQL>SELECTgroup_number,name,pathFROMv$asm_diskGROUP_NUMBERNAMEPATH2MYDG_DISK1/dev/asm-disk52MYDG_DISK2/dev/asm-disk6更换磁盘SQL>ALTERDISKGROUPMYDGADDDISK'/dev/asm-disk7'NAME'MYDG_DISK3';SQL>ALTERDISKGROUPMYDGDROPDISKMYDG_DISK1;SQL>ALTERDISKGROUPMYDGADDDISK'/dev/asm-disk7'DROPDISK'/dev/asm-disk6'rebalancepower10;SQL>ALTERDISKGROUPMYDGREPLACEDISKMYDG_DISK2WITH'/dev/asm-disk7';估算工作(V$ASM_ESTIMATE)SQL>EXPLAINWORKFORALTERDISKGROUPmydgADDDISK'/dev/asm-disk5';Explained.
SQL>SELECTest_workFROMV$ASM_ESTIMATE;EST_WORK24更快的再同步Diska+DATADiskbFG1FG2更快的再同步Diska+DATADiskbFG1FG2SQL>ALTERDISKGROUPmydgOFFLINEDISKMYDG_0001;更快的再同步Diska+DATADiskbFG1FG2SQL>ALTERDISKGROUPmydgONLINEDISKMYDG_0001POWER10;Resyncisrestartable!
失败组修复时间SQL>CREATEDISKGROUPmydgNORMALREDUNDANCYFAILGROUPfgroup1DISK'/dev/asm-disk5'FAILGROUPfgroup2DISK'/dev/asm-disk6';SQL>ALTERDISKGROUPmydgSETATTRIBUTE'DISK_REPAIR_TIME'='1M';SQL>ALTERDISKGROUPmydgOFFLINEDISKmydg_0001;WARNING:StartedDropDiskTimeoutforDisk1(MYDG_0001)ingroup2withavalue60WARNING:Disk1(MYDG_0001)ingroup2willbedroppedin:(60)secsonASMinst1失败组修复时间ThuNov1210:28:182015SQL>alterdiskgroupMYDGdropdiskMYDG_0001force/*ASMSERVER*/注:GroupBlockoutsiderollingmigrationprivilegedregionThuNov1210:28:192015SUCCESS:alterdiskgroupMYDGdropdiskMYDG_0001force/*ASMSERVER*/SQL>SELECTgroup_number,name,valueFROMv$asm_attributeWHEREgroup_number=2;GROUP_NUMBERNAMEVALUE2DISK_REPAIR_TIME3.
6h2FAILGROUP_REPAIR_TIME24.
0hASM磁盘清理+DATAFGroup1FGroup2ASM磁盘清理+DATAFGroup1FGroup2ASM磁盘清理+DATAFGroup1FGroup2ASM磁盘清理SQL>CREATEDISKGROUPmydgNORMALREDUNDANCYFAILGROUPfgroup1DISK'/dev/asm-disk5'FAILGROUPfgroup2DISK'/dev/asm-disk6';ASM磁盘清理SQL>CREATETABLESPACEmytbsDATAFILE'+MYDG'SIZE20M;CREATETABLEmyt(idNUMBER,nameVARCHAR2(10))TABLESPACEmytbs;INSERTINTOmytVALUES(1,'mytest');COMMIT;ALTERSYSTEMFLUSHBUFFER_CACHE;SELECT*FROMmyt;IDNAME1mytestROWIDOBJIDFILENUMBLOCKNUMROWSLOTAAAWgqAAHAAAACHAAA9220271350ASM磁盘清理selectrowid,to_number(utl_encode.
base64_decode(utl_raw.
cast_to_raw(lpad(substr(rowid,1,6),8,'A'))),'XXXXXXXXXXXX')asobjid,to_number(utl_encode.
base64_decode(utl_raw.
cast_to_raw(lpad(substr(rowid,7,3),4,'A'))),'XXXXXX')asfilenum,to_number(utl_encode.
base64_decode(utl_raw.
cast_to_raw(lpad(substr(rowid,10,6),8,'A'))),'XXXXXXXXXXXX')asblocknum,to_number(utl_encode.
base64_decode(utl_raw.
cast_to_raw(lpad(substr(rowid,16,3),4,'A'))),'XXXXXX')asrowslotfrommyt;ASM磁盘清理135(1024/8=128)-=7AUsize(1024kb)db_block_size=8kb7thblockinthesecondAUSQL>SELECTGROUP_NUMBER,FILE_NUMBER,BYTESFROMv$asm_fileWHEREgroup_number=2;GROUP_NUMBERFILE_NUMBERBYTES225620979712PXN_KFFXPXNUM_KFFXPDISK_KFFXPAU_KFFXPAUtype00152Primary10052Secondary21053Primary31153Secondary42154Primary52054SecondaryASM磁盘清理SQL>SELECTPXN_KFFXP,XNUM_KFFXP,DISK_KFFXP,AU_KFFXP,DECODE(LXN_KFFXP,0,'Primary',1,'Secondary','headermetadata')"AUtype"FROMX$KFFXPWHERENUMBER_KFFXP=256ANDGROUP_KFFXP=2ORDERBY1;PXN_KFFXPXNUM_KFFXPDISK_KFFXPAU_KFFXPAUtype00152Primary10052Secondary21053Primary31153Secondary42154Primary52054SecondaryASM磁盘清理SQL>SELECTPXN_KFFXP,XNUM_KFFXP,DISK_KFFXP,AU_KFFXP,DECODE(LXN_KFFXP,0,'Primary',1,'Secondary','headermetadata')"AUtype"FROMX$KFFXPWHERENUMBER_KFFXP=256ANDGROUP_KFFXP=2ORDERBY1;ASM磁盘清理[oracle@node1~]$strings/dev/asm-disk5|grepmytestmytest[oracle@node1~]$strings/dev/asm-disk6|grepmytestmytest[oracle@node1~]$ddif=/dev/asm-disk5bs=1024kcount=1skip=53of=/tmp/aud11+0recordsin1+0recordsout1048576bytes(1.
0MB)copied,0.
000862522s,1.
2GB/s[oracle@node1~]$strings/tmp/aud1|grepmytestmytestASM磁盘清理[oracle@node1~]$ddif=/tmp/aud1bs=8kcount=1skip=7of=/tmp/aud21+0recordsin1+0recordsout8192bytes(8.
2kB)copied,0.
000573621s,14.
3MB/s[oracle@node1~]$strings/tmp/aud2|grepmytestmytestASM磁盘清理[oracle@node1~]$ddif=/dev/asm-disk5bs=8kcount=1skip=6791of=/tmp/aud31+0recordsin1+0recordsout8192bytes(8.
2kB)copied,0.
000239265s,34.
2MB/s[oracle@node1~]$strings/tmp/aud3|grepmytestmytestSizeofdatablock:8kASMdisk:/dev/asm-disk5ASMfilenumber:256AUsize:1Mb(128*8k=1024)Datablock:135(secondAU(>128))Datablockoffset:135-128=7Allocationunit:53Blocknumber:53*128+7=6791ASM磁盘清理0000000ack"nulnulbelnul@soh;0)nulnulnulsohack0000020anaknulsohnulnulnul*hsohnul:0)nul0000040nulnulnulstxnul2nulnulnul@sohenqnulcannul0000160wusnulsohnulvtusnulnulnulnulnulnulnulnul0000200nulnulnulnulnulnulnulnulnulnulnulnulnulnulnul0017760sohstxstxAstxackmytestsohack;01+0recordsin1+0recordsout00200008192bytes(8.
2kB)copied,0.
00115169s,7.
1MB/s[oracle@node1~]$ddif=/dev/asm-disk5bs=8kcount=1skip=6791|od-aASM磁盘清理不要在生产环境中尝试!
!
!
ASM磁盘清理[oracle@node1~]$ddif=/dev/asm-disk5bs=8kcount=1skip=6791|od-a0000000nulnulnulnulnulnulnulnulnulnulnulnulnulnul1+0recordsin1+0recordsout00200008192bytes(8.
2kB)copied,0.
000424802s,19.
3MB/s[oracle@node1~]$[oracle@node1~]$ddif=/dev/zeroof=/dev/asm-disk5bs=8kcount=1seek=67911+0recordsin1+0recordsout8192bytes(8.
2`kB)copied,0.
000852265s,9.
6MB/sASMDiskscrubbingASM磁盘清理ALTERSYSTEMFLUSHBUFFER_CACHE;SELECT*FROMmyt;IDNAME1mytestASM磁盘清理Corruptblockrelativedba:0x01c00087(file7,block135)CompletelyzeroblockfoundduringmultiblockbufferreadReadingdatafile'+MYDG/RACDB/DATAFILE/mytbs.
256.
895397209'forcorruptionatrdba:0x01c00087(file7,block135)Readdatafilemirror'MYDG_0000'(file7,block135)foundsamecorruptdata(nologicalcheck)Readdatafilemirror'MYDG_0001'(file7,block135)foundvaliddataRepairedcorruptionat(file7,block135)ASM磁盘清理0000000ack"nulnulbelnul@soh;0)nulnulnulsohack0000020anaknulsohnulnulnul*hsohnul:0)nul0000040nulnulnulstxnul2nulnulnul@sohenqnulcannul0000160wusnulsohnulvtusnulnulnulnulnulnulnulnul0000200nulnulnulnulnulnulnulnulnulnulnulnulnulnulnul0017760sohstxstxAstxackmytestsohack;01+0recordsin1+0recordsout00200008192bytes(8.
2kB)copied,0.
00115169s,7.
1MB/s[oracle@node1~]$ddif=/dev/asm-disk5bs=8kcount=1skip=6791|od-aASM磁盘清理SQL>ALTERDISKGROUPmydgSCRUBREPAIR;SQL>ALTERDISKGROUPmydgSCRUBPOWERMAX(HIGH,AUTO);SQL>ALTERDISKGROUPmydgSCRUBDISKmydg_0000REPAIR;SQL>ALTERDISKGROUPmydgSCRUBFILE'+MYDG/RACDB/DATAFILE/USERS.
331.
34234515'REPAIR;Thanksforcoming!
!
ASM磁盘清理http://www.
kamranagayev.
comhttp://www.
facebook.
com/KamranAgayevhttp://www.
twitter.
com/KamranAgayev谢谢