tag:blogger.com,1999:blog-41619539581344145992024-03-05T23:11:48.381-05:00Vijay R. Dumpa Oracle BlogVijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.comBlogger45125tag:blogger.com,1999:blog-4161953958134414599.post-77477243179065073142021-01-06T13:14:00.035-05:002022-12-31T17:05:36.218-05:00Independent Watch Brands I follow<br />
<a href="https://passionfinejewelry.com/collections/watches">https://passionfinejewelry.com/collections/watches</a><br />
<br />
<br />
<br />
<b>HOT Independent Watch Brands:</b><br />
<a href="https://ming.watch/">https://ming.watch/</a><br />
<br />
<a href="https://kuronotokyo.com/">https://kuronotokyo.com/</a><br />
<br />
<a href="https://sartory-billard.com/">https://sartory-billard.com/</a><br />
<br />
<a href="https://lundis-bleus.com/en/shop/">https://lundis-bleus.com/en/shop/</a><br />
<br />
<a href="https://atelierwen.com/">https://atelierwen.com/</a><br />
<br />
<a href="http://habring2.com/index.php/en/">http://habring2.com/index.php/en/</a><br />
<br />
<a href="https://www.sufhelsinki.com/">https://www.sufhelsinki.com/</a><br />
<br />
<a href="https://www.ochsundjunior.swiss/#">https://www.ochsundjunior.swiss/#</a><br />
<br />
<br />
<b>COOL Independent Watch Brands:</b><br />
<a href="https://oakandoscar.com/collections/all-watches">https://oakandoscar.com/collections/all-watches</a><br />
<br />
<a href="https://halioswatches.com/">https://halioswatches.com/</a><br />
<br />
<a href="https://www.unimaticwatches.com/current-editions/">https://www.unimaticwatches.com/current-editions/</a><br />
<br />
<a href="https://doxawatches.com/">https://doxawatches.com/</a><br />
<br />
<a href="https://synchronusa.com/pre-order/">https://synchronusa.com/pre-order/</a><br />
<br />
<a href="https://baltic-watches.com/en">https://baltic-watches.com/en</a><br />
<br />
<a href="https://www.christopherward.com/watches">https://www.christopherward.com/watches</a><br />
<br />
<a href="https://www.lorierwatches.com/collections/watches">https://www.lorierwatches.com/collections/watches</a><br />
<br />
<a href="https://usd.farer.com/collections/all-watches">https://usd.farer.com/collections/all-watches</a><br />
<br />
<a href="https://anordain.com/collections/all">https://anordain.com/collections/all</a><br />
<br />
<a href="https://www.peltonusa.com/watches">https://www.peltonusa.com/watches</a><br />
<br />
<a href="https://www.nezumistudios.com/product-category/watches/">https://www.nezumistudios.com/product-category/watches/</a><br />
<br />
<a href="https://www.autodromo.com/watches/">https://www.autodromo.com/watches/</a><br />
<br />
<a href="https://weisswatchcompany.com/collections/all">https://weisswatchcompany.com/collections/all</a><br />
<br />
<a href="https://montawatch.com/collections">https://montawatch.com/collections</a><br />
<br />
<a href="https://danhenrywatches.com/collections/">https://danhenrywatches.com/collections/</a><br />
<br />
<a href="https://www.traskawatch.com/">https://www.traskawatch.com/en</a><br />
<br />
<a href="https://zeloswatches.com/collections/all">https://zeloswatches.com/collections/all</a><br />
<br />
<a href="https://www.undone.com/en/collection/lists/collaborations">https://www.undone.com/en/collection/lists/collaborations</a><br />
<br />
<a href="https://www.bangalorewatchco.in/">https://www.bangalorewatchco.in/</a><br />
<br />
<a href="https://www.wmtwatches.com/">https://www.wmtwatches.com/</a><br />
<br />
<a href="https://ow-watch.ch/collections/main">https://ow-watch.ch/collections/main</a><br />
<br />
<a href="https://www.stowa.de/Flieger/">https://www.stowa.de/Flieger/</a><br />
<br />
<a href="https://www.laco-watches.com/en/index">https://www.laco-watches.com/en/index</a><br />
<br />
<a href="https://www.furlanmarri.com/">https://www.furlanmarri.com/</a><br />
<br />
<br />
<b>High End Independent Watch Brands:</b><br />
<a href="https://www.fpjourne.com/en">https://www.fpjourne.com/en</a><br />
<br />
<a href="https://www.urwerk.com/">https://www.urwerk.com/</a><br />
<br />
<a href="https://www.mbandf.com/en">https://www.mbandf.com/en</a><br />
<br />
<a href="https://ressencewatches.com/">https://ressencewatches.com/</a><br />
<br />
<a href="https://www.rogerdubuis.com/en">https://www.rogerdubuis.com/en</a><br />
<br />
<a href="https://www.voutilainen.ch/the-watches/">https://www.voutilainen.ch/the-watches/</a><br />
<br />
<a href="https://www.debethune.ch/en">https://www.debethune.ch/en</a><br />
<br />
<a href="https://www.romaingauthier.com/">https://www.romaingauthier.com/</a><br />
<br />
<a href="https://www.hytwatches.com/">https://www.hytwatches.com/</a><br />
<br />
<a href="https://www.h-moser.com/">https://www.h-moser.com/</a><br />
<br />
<a href="https://www.sarpanevawatches.com/">https://www.sarpanevawatches.com/</a><br />
<br />
<a href="https://www.alange-soehne.com/en/home">https://www.alange-soehne.com/en/home</a><br />
<br />
<br />
<b>Watch Collaborations:</b><br />
<a href="https://shop.hodinkee.com/collections/limited">https://shop.hodinkee.com/collections/limited</a><br />
<br />
<a href="https://www.massenalab.com/">https://www.massenalab.com/</a><br />
<br />
<a href="https://us.bape.com/collections/collaboration/GOODS">https://us.bape.com/collections/collaboration/GOODS</a><br />
<br />
<a href="https://revolution.watch/shop/watches/limited-edition-pieces.html">https://revolution.watch/shop/watches/limited-edition-pieces.html</a><br />
<br />
<a href="https://shop.fratello.com/collections/limited-editions">https://shop.fratello.com/collections/limited-editions</a><br />
<br />
<a href="https://windupwatchshop.com/collections/limited-editions">https://windupwatchshop.com/collections/limited-editions</a><br />
<br />
<a href="https://thennowbeyond.thehourglass.com/collections/commemorative-watches">https://thennowbeyond.thehourglass.com/collections/commemorative-watches</a><br />
<br />
<a href="https://www.harrods.com/en-gb/sets/fine-watches-exclusives-limited-editions">https://www.harrods.com/en-gb/sets/fine-watches-exclusives-limited-editions</a><br />
<br />
<a href="https://www.bucherer.com/bucherer-blue.html">https://www.bucherer.com/bucherer-blue.html</a><br /><br />
<br />
<br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-2910615515465901512019-12-01T22:16:00.009-05:002024-02-13T16:49:50.345-05:00Watch Strap sites I followWatch Strap sites I follow:<br />
<br />
<a href="https://shop.hodinkee.com/collections/straps">https://shop.hodinkee.com/collections/straps</a><br />
<br />
<a href="https://shop.analogshift.com/collections/straps">https://shop.analogshift.com/collections/straps</a><br />
<br />
<a href="https://windupwatchshop.com/collections/straps">https://windupwatchshop.com/collections/straps</a><br />
<br />
<a href="https://www.crownandbuckle.com/">https://www.crownandbuckle.com/</a><br />
<br />
<a href="https://shop.fratello.com/collections/straps">https://shop.fratello.com/collections/straps</a><br />
<br />
<a href="https://shop.synchronwatches.com/product/tropic-strap-2/">https://shop.synchronwatches.com/product/tropic-strap-2/</a><br />
<br />
<a href="https://aquastar.ch/product/genuine-tropic-22mm-strap-for-aquastar-deepstar/">https://aquastar.ch/product/genuine-tropic-22mm-strap-for-aquastar-deepstar/</a><br />
<br />
<a href="https://molequin.com/collections/all-straps">https://molequin.com/collections/all-straps</a><br />
<br />
<a href="http://www.basandlokes.com/view-all-watch-straps/">http://www.basandlokes.com/view-all-watch-straps/</a><br />
<br />
<a href="https://www.veblenist.com/straps">https://www.veblenist.com/straps</a><br />
<br />
<a href="https://revolutionwatch.com/shop/accessories/watch-straps.html">https://revolutionwatch.com/shop/accessories/watch-straps.html</a><br />
<br />
<a href="https://www.cheapestnatostraps.com/">https://www.cheapestnatostraps.com/</a><br />
<br />
<a href="https://www.lugs.shop/shop/">https://www.lugs.shop/shop/</a><br />
<br />
<a href="https://delugs.com/">https://delugs.com/</a><br />
<br />
<a href="https://www.genteel-handmade.com/products/">https://www.genteel-handmade.com/products/</a><br />
<br />
<a href="https://erikasoriginals.com/">https://erikasoriginals.com/</a><br />
<br />
<a href="https://www.laccrado.com/">https://www.laccrado.com/</a><br />
<br />
<a href="http://www.strapsbyfleur.com/">http://www.strapsbyfleur.com/</a><br />
<br />
<a href="http://www.kastantona.com/shop/">http://www.kastantona.com/shop/</a><br />
<br />
<a href="https://www.bandrbands.com/">https://www.bandrbands.com/</a><br />
<br />
<a href="https://www.colareb.it/en/products/watch-strap/">https://www.colareb.it/en/products/watch-strap/</a><br />
<br />
<a href="https://www.uncleseiko.com/store/c1/Featured_Products.html">https://www.uncleseiko.com/store/c1/Featured_Products.html</a><br />
<br />
<a href="https://forstnerbands.com/collections/full-collection">https://forstnerbands.com/collections/full-collection</a><br />
<br />
<a href="https://bulangandsons.com/collections/watch-straps-accessories">https://bulangandsons.com/collections/watch-straps-accessories</a><br />
<br />
<a href="https://en.accurate-form.jp/collections/all-strap">https://en.accurate-form.jp/collections/all-strap</a><br />
<br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-35803986367649362722018-04-29T17:51:00.006-04:002021-12-28T12:48:10.016-05:00Wristwatch News and Reviews sites I followJust want to share some of the Wristwatch News and Reviews online sites I follow, below sites are not in any particular order.<br />
<br />
<a href="https://www.hodinkee.com/">https://www.hodinkee.com/</a><br />
<br />
<a href="http://www.timezone.com/">http://www.timezone.com/</a><br />
<br />
<a href="https://www.ablogtowatch.com/">https://www.ablogtowatch.com/</a><br />
<br />
<a href="https://www.fratellowatches.com/">https://www.fratellowatches.com/</a><br />
<br />
<a href="http://watchesbysjx.com/">http://watchesbysjx.com/</a><br />
<br />
<a href="http://wornandwound.com/">http://wornandwound.com/</a><br />
<br />
<a href="https://monochrome-watches.com/">https://monochrome-watches.com/</a><br />
<br />
<a href="https://timeandtidewatches.com/">https://timeandtidewatches.com/</a><br />
<br />
<a href="https://www.plus9time.com/">https://www.plus9time.com/</a><br />
<br />
<a href="https://www.watchprosite.com/">https://www.watchprosite.com/</a><br />
<br />
<a href="https://www.watchtime.com/">https://www.watchtime.com/</a><br />
<br />
<a href="https://www.watchtime.in/">https://www.watchtime.in/</a><br />
<br />
<a href="https://iwmagazine.com/">https://iwmagazine.com/</a><br />
<br />
<a href="https://timeandtidewatches.com/">https://timeandtidewatches.com/</a><br />
<br />
<a href="http://www.timeandwatches.com/">http://www.timeandwatches.com/</a><br />
<br />
<a href="https://quillandpad.com/">https://quillandpad.com/</a><br />
<br />
<a href="http://www.onthedash.com/">http://www.onthedash.com/</a><br />
<br />
<a href="https://www.revolution.watch/">https://www.revolution.watch/</a><br />
<br />
<a href="https://www.deployant.com/">https://www.deployant.com/</a><br />
<br />
<a href="http://www.atimelyperspective.com/">http://www.atimelyperspective.com/</a><br />
<br />
<a href="https://www.crownandbuckle.com/blog/">https://www.crownandbuckle.com/blog/</a><br />
<br />
<a href="https://www.timeline.watch/?year_filter=1970">https://www.timeline.watch/?year_filter=1970</a><br />
<br />
<a href="http://oceanictime.blogspot.com/">http://oceanictime.blogspot.com/</a><br />
<br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-83266879117821611372018-03-06T14:27:00.037-05:002023-02-13T09:59:20.190-05:00Vintage watch online sites I followJust want to share some of the Vintage watch online sites I follow, other than <a href="http://www.ebay.com">www.ebay.com</a>, <a href="http://www.chrono24.com">www.chrono24.com</a> and <a href="http://rolexpassionmarket.com/">rolexpassionmarket.com</a>.<br />
Below sites are not in any particular order.<br />
<br />
<a href="https://shop.hodinkee.com/collections/vintage-watches">https://shop.hodinkee.com/collections/vintage-watches</a><br />
<br />
<a href="https://shop.analogshift.com/collections/watches">https://shop.analogshift.com/collections/watches</a><br />
<br />
<a href="https://www.shucktheoyster.com/portfolio-category/vintage-watches/">https://www.shucktheoyster.com/portfolio-category/vintage-watches/</a><br />
<br />
<a href="https://thekeystone.com/collections/mens">https://thekeystone.com/collections/mens</a><br />
<br />
<a href="https://www.craftandtailored.com/collections/current-timepiece-collection">https://www.craftandtailored.com/collections/current-timepiece-collection</a><br />
<br />
<a href="https://revolution.watch/shop/watches/vintage.html">https://revolution.watch/shop/watches/vintage.html</a><br />
<br />
<a href="https://shinyhappyobjects.com/watches">https://shinyhappyobjects.com/watches</a><br />
<br />
<a href="http://www.db1983.com">http://www.db1983.com</a><br />
<br />
<a href="https://www.windvintage.com/watches">https://www.windvintage.com/watches</a><br />
<br />
<a href="https://amsterdamvintagewatches.com/shop/">https://amsterdamvintagewatches.com/shop/</a><br />
<br />
<a href="https://www.acollectedman.com/collections/all">https://www.acollectedman.com/collections/all</a><br />
<br />
<a href="https://mentawatches.com/shop/">https://mentawatches.com/shop/</a><br />
<br />
<a href="http://www.matthewbaininc.com/vintage-watches/">http://www.matthewbaininc.com/vintage-watches/</a><br />
<br />
<a href="https://www.harbor-watches.com/">https://www.harbor-watches.com/</a><br />
<br />
<a href="https://www.lunaroyster.com/shop">https://www.lunaroyster.com/shop</a><br />
<br />
<a href="https://www.ssongwatches.com/collections/vintage">https://www.ssongwatches.com/collections/vintage</a><br />
<br />
<a href="https://www.coolvintagewatches.com/shop-2">https://www.coolvintagewatches.com/shop-2</a><br />
<br />
<a href="https://www.visionvintagewatches.com/vintage-watches">https://www.visionvintagewatches.com/vintage-watches</a><br />
<br />
<a href="https://www.omegaenthusiastltd.com/">https://www.omegaenthusiastltd.com/</a><br />
<br />
<a href="https://www.thosewatchguys.com/watches/">https://www.thosewatchguys.com/watches/</a><br />
<br />
<a href="https://www.carsandwatches.com/watches/new-watches-arrivals">https://www.carsandwatches.com/watches/new-watches-arrivals</a><br />
<br />
<a href="https://bulangandsons.com/collections/watches-for-sale">https://bulangandsons.com/collections/watches-for-sale</a><br />
<br />
<a href="https://vintagewatchspecialist.com/">https://vintagewatchspecialist.com/</a><br />
<br />
<a href="https://ticktockguru.com/collections/watches">https://ticktockguru.com/collections/watches</a><br />
<br />
<a href="https://theoandharris.com/product-category/vintage-watches/">https://theoandharris.com/product-category/vintage-watches/</a><br />
<br />
<a href="https://www.watchpool24.com/en/all-offers/all-watches">https://www.watchpool24.com/en/all-offers/all-watches</a><br />
<br />
<a href="https://shop.watchsteez.com/collections/watches">https://shop.watchsteez.com/collections/watches</a><br />
<br />
<a href="https://corrvintagewatches.com/shop/">https://corrvintagewatches.com/shop/</a><br />
<br />
<a href="https://classicwatchny.com/watch-collection-2/">https://classicwatchny.com/watch-collection-2/</a><br />
<br />
<a href="https://brusselsvintagewatches.com/en/watches-for-sale/">https://brusselsvintagewatches.com/en/watches-for-sale/</a><br />
<br />
<a href="https://universalgenevepolerouter.com/marketplace-adverts/">https://universalgenevepolerouter.com/marketplace-adverts/</a><br />
<br />
<a href="https://dannysvintagewatches.com/shop">https://dannysvintagewatches.com/shop</a><br />
<br />
<a href="https://www.retrowatchguy.com/collections/all?sort_by=created-descending">https://www.retrowatchguy.com/collections/all?sort_by=created-descending</a><br />
<br />
<a href="https://dutchwatchclassics.com/watches/">https://dutchwatchclassics.com/watches/</a><br />
<br />
<a href="https://vintagewatchhunter.com/">https://vintagewatchhunter.com/</a><br />
<br />
<a href="https://thegrandseikoguy.com/product-category/grand-seiko/">https://thegrandseikoguy.com/product-category/grand-seiko/</a><br />
<br />
<a href="https://thetimecurator.com/collections/frontpage">https://thetimecurator.com/collections/frontpage</a><br />
<br />
<a href="https://kleinvintagewatch.com/watchesforsale">https://kleinvintagewatch.com/watchesforsale</a><br />
<br />
<a href="https://www.dcvintagewatches.com/?Collection=All">https://www.dcvintagewatches.com/?Collection=All</a><br />
<br />
<a href="https://www.hubcityvintage.com/shop">https://www.hubcityvintage.com/shop</a><br />
<br />
<a href="https://blairwatchproject.com/sales/">https://blairwatchproject.com/sales/</a><br />
<br />
<a href="https://www.thetokeiclub.jp/">https://www.thetokeiclub.jp/</a><br />
<br />
<a href="https://classicseiko.com/collections/available-watches">https://classicseiko.com/collections/available-watches</a><br />
<br />
<a href="https://en.arbitro.shop/collections/vintage-watches">https://en.arbitro.shop/collections/vintage-watches</a><br />
<br />
<a href="https://www.wristlabs.store/?Collection=All">https://www.wristlabs.store/?Collection=All</a><br />
<br />
<a href="https://watches83.com/en/">https://watches83.com/en/</a><br />
<br />
<a href="https://www.thepaleh.com/collections/vintage">https://www.thepaleh.com/collections/vintage</a><br />
<br />
<a href="https://hairspring.com/">https://hairspring.com/</a><br />
<br />
<a href="https://centurytimewatches.co.uk/shop/">https://centurytimewatches.co.uk/shop/</a><br />
<br />
<a href="https://www.adamvintage.com/store/c1/vintage-watches">https://www.adamvintage.com/store/c1/vintage-watches</a><br />
<br />
<a href="https://www.collectorscornerny.com/watches">https://www.collectorscornerny.com/watches</a><br />
<br />
<br />
<a href="https://www.watchrecon.com/">https://www.watchrecon.com/</a><br />
<br />
<a href="https://www.watchpatrol.net/">https://www.watchpatrol.net/</a><br />
<br />
<a href="http://www.secondtimearoundwatchco.com/watches-for-sale/">http://www.secondtimearoundwatchco.com/</a><br />
<br />
<a href="https://wannabuyawatch.com/">https://wannabuyawatch.com/</a><br />
<br />
<a href="https://www.hqmilton.com/timepieces/eras/vintage?era=vintage&sort=lowest-price">https://www.hqmilton.com/timepieces/eras/vintage?era=vintage&sort=lowest-price</a><br />
<br />
<a href="https://watchestobuy.com/">https://watchestobuy.com/</a><br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-31475834035334710602017-08-14T10:58:00.001-04:002017-08-14T17:23:09.160-04:00My other passion!!Just want to post something not Oracle related, my other passion!!<br />
<br />
Vintage Watch buying questioner:<br />
<br />
Is the watch running? If yes, is it keeping time? <br />
Do you know how old it is? <br />
How long does it run if fully wound (if a manual-wind mechanical watch)? <br />
Is the watch is manual-wind / Automatic / Quartz(Battery)?<br />
Where did you get the watch? <br />
Why are you selling it? <br />
Do you know when it was last serviced? <br />
Can you provide additional photos of the inner case back / outer case back / crown / serial number(s) / lugs / strap / buckle, etc.? <br />
Do you know if the dial has been refinished or replaced? <br />
Do you know if the dial has been re-lumed? Does the lume glow in the dark for a long period of time? <br />
What is the length (in millimeters) of the watch from the left side of the case to the right side of the case excluding the crown? <br />
Does it come with the original box and / or papers such as the directions and receipt?<br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-74992464488226848212013-09-02T09:30:00.003-04:002013-09-03T17:42:20.914-04:00Oracle masala at work - 5:Oracle masala at work - 5:<br />
<br />
<br />
1. Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] on 11.2.0.3 BP7 <br />
2. Bug 15936951: AUTO DOP IN 11.2.0.3 NOT OBEYING PARALLEL DEGREE LIMIT AS IT DID IN 11.2.0.2 <br />
3. Logical block corruption in Oracle: <br />
4. Modifying Scan name: <br />
5. Online rebuild index fails with ORA-08104: <br />
6. How to change the session level parameters for a specific OS user: <br />
7. NLSSORT filter shown in the SQL plan, causing the FULL table scans: <br />
8. Modify the Timezone on Grid Infrastructure from EST to GMT: <br />
9. How to switch the SCAN IP address: <br />
<br />
<br />
<b>Details:</b><br />
<b>1. Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] on 11.2.0.3 BP7 </b><br />
<br />
<b>Fix: <br />
</b>Apply Exadata Patch 14409183 on 11.2.0.3 BP7. <br />
<br />
No-Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] in 11.2.0.3 <br />
Fix: Hitting the bug-13397104 please apply the latest 11.2.0.3.4 GI PSU which will fix this issue. or apply the Patch 13397104 on 11.2.0.3. <br />
<br />
<b>Other workaround: <br />
</b>Setting the _gc_read_mostly_locking=false (11.2.0.3) <br />
<br />
<br />
<b>2. Bug 15936951: AUTO DOP IN 11.2.0.3 NOT OBEYING PARALLEL DEGREE LIMIT AS IT DID IN 11.2.0.2</b> <br />
<br />
<b>Issue: <br />
</b>There is a hint that has been identified to cause this issue : /*+ parallel(table_alias) */ <br />
When we use the hint just with table_name/table_alias, it's calculating the AUTO DOP wrongly, causing other statements to queue. <br />
<br />
As of this note no patch available, <br />
<br />
<b>WORKAROUND: <br />
</b>Do not specify parallel(table_alias) hints. <br />
-- OR -- <br />
<br />
If we change this to any of the below, it should calculate DOP correctly and not queue: <br />
/*+ parallel(table_alias, number) */ <br />
/*+ parallel */ <br />
-- OR -- <br />
<br />
Implement DBRM to cap the parallel threads. <br />
<br />
<br />
<b>3. Logical block corruption in Oracle:</b> <br />
<br />
<b>Key Points: <br />
</b>RMAN Vs dbv Vs ANALYZE VALIDATE STRUCTURE CASCADE ? <br />
Data block Vs Index block error (ORA-01498/ORA-01499) ? <br />
Get the block dump for further analysis, what caused the corruption ? <br />
<br />
RMAN Level 0 (Full backup) is required to fix the block corruption. <br />
11g Active Data Guard Automatic Block Repair works well too. <br />
<br />
<b>Error/Issue: <br />
</b>SELECT Query failed with "ORA-12805: parallel query server died unexpectedly" <br />
<br />
<b>Alert Log Error: <br />
</b>ORA 600 [kjblpkeydrmqscchk:pkey]<br />
ORA 603<br />
ORA 7445 [_wordcopy_bwd_dest_aligned()+185]<br />
ORA 7445 [_wordcopy_bwd_dest_aligned()+209]<br />
<br />
a.<br />
<b>-- Export(expdp) error:<br />
</b>export - "ORA-00600: internal error code, arguments: [kluuscn_50]"<br />
<br />
<br />
<b>Tryed export with below init parameters, but no luck:<br />
</b>DB_BLOCK_CHECKSUM = OFF <br />
DB_BLOCK_CHECKING = OFF<br />
<br />
<br />
<b>-- Tryed "DBMS_REPAIR.skip_corrupt_blocks" before taking export, "but no luck".<br />
</b>SQL> BEGIN<br />
DBMS_REPAIR.skip_corrupt_blocks (<br />
schema_name => 'UT_REP',<br />
object_name => 'T1',<br />
object_type => DBMS_REPAIR.table_object,<br />
flags => DBMS_REPAIR.skip_flag);<br />
END;<br />
/<br />
2 3 4 5 6 7 8<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL><br />
SQL> commit;<br />
<br />
Commit complete.<br />
<br />
SQL> exit<br />
<br />
b.<br />
<b>-- DBMS_STATS error:<br />
</b>dbms_stats - "ORA-20011: Approximate NDV failed: ORA-12805: parallel query server died unexpectedly"<br />
<br />
c.<br />
<b>-- Analyze table error:<br />
</b>SQL> analyze table ut_rep.t1 VALIDATE STRUCTURE CASCADE;<br />
<br />
analyze table ut_rep.t1 VALIDATE STRUCTURE CASCADE<br />
*<br />
ERROR at line 1:<br />
ORA-01498: block check failure - see trace file<br />
<br />
<br />
<b>-- How many files and blocks where effected/Identifying corrupt blocks?:<br />
</b>BACKUP VALIDATE TABLESPACE UT_COMP; -- Checks Physical corruption only on given tablespace level.<br />
BACKUP VALIDATE CHECK LOGICAL TABLESPACE UT_COMP; -- Checks Logical and Physical corruption on given tablespace level.<br />
<br />
BACKUP VALIDATE CHECK LOGICAL DATABASE; -- Checks Logical and Physical corruption on the database level.<br />
<br />
<br />
<b>RMAN e.g..:<br />
</b>rman target /<br />
spool log to 'x.log'<br />
<br />
BACKUP VALIDATE CHECK LOGICAL TABLESPACE UT_COMP;<br />
<br />
spool log off;<br />
exit;<br />
<br />
<b>-- Displays information about database blocks that were corrupted after running the above RMAN command.<br />
-- When the RMAN recover the corrupt block then automatically updates this view.<br />
</b>select * from V$DATABASE_BLOCK_CORRUPTION;<br />
<br />
<b>-- Identifying the object name<br />
</b>select owner, segment_name, segment_type, partition_name<br />
from dba_extents<br />
where file_id = 486<br />
and 2048140 between block_id and block_id + blocks - 1;<br />
<br />
<b>Fix:<br />
</b>a.<br />
$ rman target / catalog_user/catalog_user_password@catalogdb<br />
<br />
BLOCKRECOVER CORRUPTION LIST;<br />
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';<br />
-- (OR) --<br />
<br />
-- This checks FRA and backupset.<br />
$ rman target / catalog_user/catalog_user_password@catalogdb<br />
BLOCKRECOVER DATAFILE <data file#> BLOCK <block#>;<br />
-- (OR) --<br />
<br />
-- Recovery from backupset<br />
BLOCKRECOVER DATAFILE <data file#> BLOCK <block#> DATAFILE <data file#> BLOCK <block#> FROM BACKUPSET;<br />
-- (OR) --<br />
<br />
-- Recovery from image copy<br />
BLOCKRECOVER DATAFILE <data file#> BLOCK <block#> DATAFILE <data file#> BLOCK <block#> FROM DATAFILECOPY;<br />
-- (OR) --<br />
<br />
-- Recovery from backupset which have "FULL" tag<br />
BLOCKRECOVER DATAFILE <data file#> BLOCK <block#> DATAFILE <data file#> BLOCK <block#> FROM TAG = FULL;<br />
<br />
b.<br />
-- If you don't have FULL RMAN backups!!! If it's OK to miss some data !!!<br />
-- In this example, I am creating a GOOD_T1 table from T1 tables, without 3 corrupted blocks (2048140,2048862,2039785).<br />
create table ut_rep.good_t1<br />
nologging<br />
parallel 8<br />
tablespace ut_comp<br />
as<br />
select * from ut_rep.t1<br />
where rowid not in (select rowid from ut_rep.t1<br />
where to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') in (2048140,2048862,2039785));<br />
<br />
-- *****<br />
-- I used below logic to pull the effected blocks to analyze the data.<br />
-- *****<br />
rowid,<br />
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as obj_id,<br />
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as file_num,<br />
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as block_num,<br />
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as row_slot<br />
<br />
c.<br />
If above method did not work, we need to go by ROWID's method:<br />
<br />
1a.<br />
Find the object_id:<br />
select data_object_id from dba_objects where owner = 'UT_REP' and OBJECT_NAME = 'T398'<br />
-- 6540044<br />
<br />
1b.<br />
Problem file# - blocks#'s:<br />
486 - 3619385<br />
489 - 3006218<br />
<br />
select dbms_rowid.rowid_create(rowid_type => 1,<br />
object_number => 6540044,<br />
relative_fno => 486,<br />
block_number => 3619385,<br />
row_number => 0) begin_rowid,<br />
dbms_rowid.rowid_create(rowid_type => 1,<br />
object_number => 6540044,<br />
relative_fno => 486,<br />
block_number => 3619385 + 1,<br />
row_number => 0) end_rowid<br />
from dual;<br />
<br />
begin_rowid - AAY8sMAHmAANzo5AAA<br />
end_rowid - AAY8sMAHmAANzo6AAA<br />
<br />
select dbms_rowid.rowid_create(rowid_type => 1,<br />
object_number => 6540044,<br />
relative_fno => 489,<br />
block_number => 3006218,<br />
row_number => 0) begin_rowid,<br />
dbms_rowid.rowid_create(rowid_type => 1,<br />
object_number => 6540044,<br />
relative_fno => 489,<br />
block_number => 3006218 + 1,<br />
row_number => 0) end_rowid<br />
from dual;<br />
<br />
begin_rowid - AAY8sMAHpAALd8KAAA<br />
end_rowid - AAY8sMAHpAALd8LAAA<br />
<br />
1c.<br />
create table UT_REP.T398_good<br />
as<br />
SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A where rowid < 'AAY8sMAHmAANzo5AAA';
insert into UT_REP.T398_good
SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A where rowid > 'AAY8sMAHmAANzo6AAA' and rowid < 'AAY8sMAHpAALd8KAAA';
insert into UT_REP.T398_good
SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A * where rowid > 'AAY8sMAHpAALd8LAAA';<br />
<br />
1d.<br />
truncate table ut_rep.t398;<br />
alter table ut_rep.t398 move;<br />
<br />
select 'alter index ut_rep.'||index_name||' rebuild;' from dba_indexes where owner = 'UT_REP' and table_name = 'T398';<br />
<br />
1e.<br />
insert /*+ append */ into ut_rep.t398 select * from UT_REP.T398_good;<br />
commit;<br />
<br />
-- Missing records count:<br />
select count(*) from ut_rep.t398;<br />
select count(*) from UT_REP.T398_good;<br />
<br />
d.<br />
dbed utility !! -- Not tested.<br />
<br />
<br />
-- Another very importenet thing is, What caused the logical block corruption?<br />
-- Oracle might ask for block dumps for analysis.<br />
sqlplus / as sysdba<br />
oradebug setospid <spid><br />
oradebug unlimit<br />
alter system dump datafile '<full path of datafile>' block <block #>;<br />
oradebug tracefile_name<br />
exit<br />
<br />
<br />
<b>4. Modifying Scan name:<br />
</b><br />
a. -- If existing databases are running on that cluster backup the spfile of all databases into a different location.<br />
Example : create pfile='location' from spfile;<br />
<br />
b. -- Update the pfile in the location from Step1 with the new scan name for remote_listener parameter.<br />
<br />
c. -- Check if the new scan name has been setup on the cluster.<br />
<br />
-- nslookup <new Scan Name><br />
<br />
Example: <br />
nslookup ut-atl1.dwire.com<br />
<br />
Server: atl1.dwire.com<br />
Address: 10.xx.xxx.xx<br />
<br />
Name: ut-atl1.dwire.com<br />
Addresses: 10.xx.xxx.xx, 10.xx.xxx.xx, 10.xx.xxx.xx<br />
<br />
d. -- Shutdown all instances on that cluster, stop the existing scan listener on the cluster. <br />
-- Verify if everything is stopped.<br />
<br />
<br />
Set the ORACLE_HOME to GRID_HOME.<br />
. oraenv<br />
+ASM1<br />
<br />
$GRID_HOME/bin/srvctl stop scan_listener<br />
$GRID_HOME/bin/srvctl stop scan<br />
<br />
e. -- Configure new Scan Name.<br />
<br />
-- $GRID_HOME/bin/srvctl modify scan -n <new Scan Name><br />
<br />
Example: <br />
$GRID_HOME/bin/srvctl modify scan -n ut-atl1.dwire.com<br />
<br />
f. -- Modify Scan Name in CRS. Execute this command as root<br />
<br />
-- sudo crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=<new Scan Name>"<br />
<br />
Example: <br />
cd $GRID_HOME/bin/<br />
sudo crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=ut-atl1.dwire.com"<br />
<br />
g. $GRID_HOME/bin/srvctl modify scan_listener -u <br />
<br />
h. $GRID_HOME/bin/srvctl start scan_listener<br />
<br />
i. -- Confirm if the new scan is working properly and the correct name.<br />
$GRID_HOME/bin/srvctl status scan_listener<br />
$GRID_HOME/bin/srvctl config scan<br />
<br />
j. -- Startup the instance through sqlplus in nomount state.<br />
<br />
k. -- Create the spfile inside the ASM with the pfile that was changed as part of Step b.<br />
<br />
l. -- shutdown the instance.<br />
<br />
m. -- Start the database through srvctl.<br />
<br />
n. -- Verify if everything is working properly. Check the remote_listener parameter value in the database.<br />
-- It should have the new scan name.<br />
<br />
Example : <br />
show parameter remote_listener<br />
<br />
<br />
<b>5. Online rebuild index fails with ORA-08104:<br />
</b><br />
<b>Error:<br />
</b>SQL> alter index ut_rep.ih132 rebuild online;<br />
alter index ut_rep.ih132 rebuild online<br />
*<br />
ERROR at line 1:<br />
ORA-08104: this index object 2249271 is being online built or rebuilt<br />
<br />
SQL> exit<br />
<br />
<b>Fix:<br />
</b>During the kill immediate(ALTER SYSTEM KILL SESSION '<sid>,<SERIAL#>,@2' IMMEDIATE;) some extents still exists on the tablespace and the index has a marker, when a online operation is initiated.<br />
To cleanup use the DBMS_REPAIR.ONLINE_INDEX_CLEAN package as below.<br />
<br />
a.<br />
SELECT object_id FROM dba_objects WHERE object_name = 'IH132';<br />
-- 123456789<br />
<br />
b.<br />
DECLARE<br />
RetVal BOOLEAN;<br />
OBJECT_ID BINARY_INTEGER;<br />
WAIT_FOR_LOCK BINARY_INTEGER;<br />
BEGIN<br />
OBJECT_ID := 123456789;<br />
WAIT_FOR_LOCK := NULL;<br />
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);<br />
COMMIT;<br />
END;<br />
/<br />
<br />
<br />
<b>6. How to change the session level parameters for a specific OS user:<br />
</b><br />
a.<br />
connect sys@DB1<br />
<br />
create or replace trigger logon_test<br />
AFTER LOGON ON test.SCHEMA<br />
<br />
BEGIN<br />
<br />
IF sys_context('USERENV','OS_USER') in ('vdumpa')<br />
THEN<br />
execute immediate ('ALTER SESSION SET optimizer_features_enable=''11.2.0.1''');<br />
END IF;<br />
<br />
END;<br />
/<br />
<br />
-- To check the execute immediate:<br />
SQL> select 'ALTER SESSION SET optimizer_features_enable=''11.2.0.1''' from dual;<br />
<br />
'ALTERSESSIONSETOPTIMIZER_FEATURES_ENABLE=''11.2.0.1''<br />
------------------------------------------------------<br />
ALTER SESSION SET optimizer_features_enable='11.2.0.1'<br />
<br />
SQL> select 'ALTER SESSION SET "_push_join_union_view"=false' from dual;<br />
<br />
'ALTERSESSIONSET"_PUSH_JOIN_UNION_VIEW"=FALSE'<br />
------------------------------------------------<br />
ALTER SESSION SET "_push_join_union_view"=false<br />
<br />
-- Few important sys_context parameters:<br />
sys_context('USERENV','HOST') -- Host name<br />
sys_context('USERENV','OS_USER') -- OS user name<br />
sys_context('USERENV','SERVICE_NAME') -- Service name<br />
sys_context('USERENV','SESSION_USER') -- DB user name<br />
sys_context('USERENV','TERMINAL') -- Terminal name<br />
sys_context('USERENV','MODULE') -- Module name<br />
sys_context('USERENV','IP_ADDRESS') -- IP address of the machine from which the client is connected.<br />
<br />
b.<br />
-- vdumpa is logging in as test user.<br />
connect test@DB1<br />
<br />
c.<br />
-- This is the test to check the session parameter value.<br />
<br />
connect sys@DB1<br />
SQL> show parameter optimizer_features_enable<br />
<br />
NAME TYPE VALUE<br />
------------------------------------ ----------- ------------------------------<br />
optimizer_features_enable string 11.2.0.2<br />
<br />
SQL> select sid, username, osuser from gv$session where username = 'TEST';<br />
<br />
SID USERNAME OSUSER<br />
---------- ------------------------------ ------------------------------<br />
486 TEST vdumpa<br />
<br />
SQL> select name,value from v$ses_optimizer_env where sid = 486 and name like '%optimizer_features_enable%';<br />
<br />
NAME VALUE<br />
---------------------------------------- -------------------------<br />
optimizer_features_enable 11.2.0.1<br />
<br />
<br />
<b>7. NLSSORT filter shown in the SQL plan, causing the FULL table scans:<br />
</b><br />
a.<br />
<b>-- Good plan<br />
</b>SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ap8rhqm23xdbz',0));<br />
<br />
PLAN_TABLE_OUTPUT<br />
------------------------------------------------------------------------------------------------ <br />
SQL_ID ap8rhqm23xdbz, child number 0<br />
-------------------------------------<br />
SELECT * FROM XYZ WHERE BATCHMODULEID=:B1<br />
<br />
Plan hash value: 848178827<br />
<br />
------------------------------------------------------------------------------------------------<br />
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />
------------------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | | | | 1 (100)| |<br />
| 1 | TABLE ACCESS BY INDEX ROWID| XYZ | 1 | 189 | 1 (0)| 00:00:01 |<br />
|* 2 | INDEX UNIQUE SCAN | SYS_C0021226 | 1 | | 1 (0)| 00:00:01 |<br />
------------------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
---------------------------------------------------<br />
<br />
2 - access("BATCHMODULEID"=:B1)<br />
<br />
<br />
19 rows selected.<br />
<br />
<b>-- Bad plan<br />
</b>SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ap8rhqm23xdbz',1));<br />
<br />
PLAN_TABLE_OUTPUT<br />
--------------------------------------------------------------------------------------- <br />
SQL_ID ap8rhqm23xdbz, child number 1<br />
-------------------------------------<br />
SELECT * FROM XYZ WHERE BATCHMODULEID=:B1<br />
<br />
Plan hash value: 2660499978<br />
<br />
--------------------------------------------------------------------------------------<br />
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />
--------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | | | | 4378 (100)| |<br />
|* 1 | TABLE ACCESS FULL| XYZ | 7400 | 1365K| 4378 (2)| 00:00:53 |<br />
--------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
---------------------------------------------------<br />
<br />
1 - filter(NLSSORT(INTERNAL_FUNCTION("BATCHMODULEID"),'nls_sort=''GENERIC_B<br />
ASELETTER''')=NLSSORT(:B1,'nls_sort=''GENERIC_BASELETTER'''))<br />
<br />
<br />
19 rows selected.<br />
<br />
<b>Note: Notice the NLSSORT filter in the bad plan.. Causing FULL table scans!!!<br />
</b>b.<br />
SQL> set linesize 120;<br />
<br />
SELECT inst_id, hash_value, child_number, PLAN_HASH_VALUE,OLD_HASH_VALUE, executions, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime<br />
from gv$sql<br />
where sql_id='ap8rhqm23xdbz';<br />
SQL> SQL> SQL> 2 3<br />
INST_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE OLD_HASH_VALUE EXECUTIONS AVG_ETIME<br />
---------- ---------- ------------ --------------- -------------- ---------- ----------<br />
4 3292444031 0 2660499978 3621482215 21 3.72152276<br />
2 3292444031 0 2660499978 3621482215 5 41.48197<br />
2 3292444031 1 2660499978 3621482215 5 43.6717298<br />
<br />
c.<br />
-- Created function Index to fix the issue:<br />
SQL> create index KFAXPROD.mh_idx1_XYZ on KFAXPROD.XYZ (NLSSORT(BATCHMODULEID,'NLS_SORT=GENERIC_BASELETTER'))<br />
tablespace KFAX_DATA;<br />
<br />
d.<br />
-- After index creation both hash values average execution time dropped dramatically.<br />
SQL> set linesize 120<br />
SQL> SELECT inst_id, hash_value, child_number, PLAN_HASH_VALUE,OLD_HASH_VALUE, executions, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime<br />
from gv$sql<br />
where sql_id='ap8rhqm23xdbz';<br />
2 3<br />
INST_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE OLD_HASH_VALUE EXECUTIONS AVG_ETIME<br />
---------- ---------- ------------ --------------- -------------- ---------- ----------<br />
1 3292444031 0 848178827 3621482215 11 .000612636<br />
2 3292444031 0 3848578838 3621482215 5 .0176316<br />
<br />
<br />
<b>8. Modify the Timezone on Grid Infrastructure from EST to GMT:<br />
</b><br />
a. Please make sure to stop the CRS on one node<br />
<br />
b. Change the Timezone on the OS side.<br />
<br />
c. To display current setting:<br />
$ cat /etc/sysconfig/clock<br />
ZONE="America/New_York"<br />
UTC=false<br />
ARC=false<br />
<br />
d. To find out all valid setting: ls -l /usr/share/zoneinfo and Anything that appears in this directory is valid to use.<br />
<br />
e. Once OS default timezone is changed, make sure:<br />
1. For 11.2.0.1, shell environment variable TZ is set correctly for grid/oracle user and root.<br />
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct time zone.<br />
$cd /u01/app/grid/11.2.0.3/crs/install<br />
$ grep TZ s_crsconfig_<nodename>_env.txt<br />
#TZ=America/New_York<br />
TZ=GMT<br />
<br />
f. start the CRS and all DB's.<br />
<br />
g. Go to the next node and repeat the same.<br />
<br />
Reference: <br />
Note: 1209444.1 - How To Change Timezone for 11gR2 Grid Infrastructure<br />
<br />
<br />
<b>9. How to switch the SCAN IP address:<br />
</b><br />
Sometimes we need to use the same SCAN name, when we are building the new servers to support no application connection change during the cut over.<br />
In normal procedure, SCAN IP's should be in DNS (Not in /etc/host file) and build the GRID InfoStructure. Here we will have IP's in /etc/host file to build the servers; during the cut over we move it to DNS.<br />
<br />
Here is process that we useed to switch the scan IP address:<br />
<br />
a. Have network team change the DNS ip address's for the SCAN name.<br />
<br />
b. Remove scan name entry in /etc/hosts file on all nodes.<br />
<br />
c. Run nslookup on SCAN name and check returns new IP addresses.<br />
<br />
d. Shutdown scan listener:<br />
srvctl stop scan_listener<br />
srvctl stop scan<br />
<br />
e. Modify SCAN:<br />
srvctl modify scan –n <scan name><br />
<br />
f. Restart scan listener:<br />
srvctl modify scan_listener -u<br />
srvctl start scan_listener<br />
<br />
g. To confirm the change:<br />
srvctl config scan<br />
srvctl config scan_listener<br />
<br />
h. Add new scan listener to the system(listener.ora) on all nodes.<br />
<br />
Note: This procedure is somewhat similar to above "Modifying Scan name" procedure.<br />
<br />
Reference: <br />
Note: 972500.1 - How to Modify SCAN Setting or SCAN Listener Port after Installation<br />
Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-29429970343427154422012-10-11T17:23:00.002-04:002013-10-11T14:50:32.062-04:00DBMS_BACKUP_RESTORE.resetCfileSection() package:<strong>DBMS_BACKUP_RESTORE.resetCfileSection() package:</strong><br />
<br />
<br />
-- Note that you can execute the following PL/SQL to remove all entries from V$RMAN_STATUS:<br />
-- The preceding function removes all job-related entries. No rows will be visible until new backup jobs are shown in V$RMAN_BACKUP_JOB_DETAILS.<br />
<br />
sqlplus / as sysdba<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(28);<br />
commit;<br />
<br />
<br />
-- Removing entries in v$DATABASE_BLOCK_CORRUPTION view<br />
-- Be aware that the corrupted blocks will remain corrupted until we fix the block corruption issue.<br />
-- Error: Bug 13386807 : RMAN BACKUP FAILED ORA-03114 ORA-07445: [KRBINSERTBCR()+665] <br />
<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35); <br />
<br />
<br />
-- Issue: V$DATABASE_BLOCK_CORRUPTION has rows referring to a non-existing datafile.<br />
<br />
-- Removing entries in v$backup_corruption view<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(17);<br />
-- Removing entries in v$copy_corruption view<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(18);<br />
<br />
<br />
-- Removing entries in v$deleted_object view on RMAN catalog<br />
-- Issue: RMAN backup is suddenly running very slowly after having deleted a large number of backupsets from catalog.<br />
<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(19);<br />
<br />
<br />
-- Removing entries in v$archived_log view<br />
-- Issue: Removing entries in v$archived_log referencing a particluar DEST_ID<br />
<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(11);<br />
<br />
<br />
-- Removing entries in v$backup_spfile on RMAN catalog <br />
-- RMAN Error: ORA-01400: cannot insert NULL into ("RMAN"."BSF"."MODIFICATION_TIME")<br />
<br />
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(21);Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com4tag:blogger.com,1999:blog-4161953958134414599.post-25611694154067575892012-09-06T14:13:00.001-04:002013-01-17T09:38:49.650-05:00Oracle masala at work - 4<strong>Oracle masala at work - 4:</strong><br />
<br />
<strong>1. I don't have Primary Key or Unique Key on the tables and the Oracle Streams apply process is slow, what should I do?:</strong><br />
<strong></strong><br />
<strong>2. How to mine a transaction using dictionary online catalog tables:</strong><br />
<strong>3. Securefile CLOB/BLOB column compression in 11g, minimum downtime plan:</strong><br />
<strong>4. How to format Oracle output in XML:</strong><br />
<strong>5. ACS and ORA-24247 errors after moving to oracle 11g:</strong><br />
<strong>6. Reclaiming Unused LOB Space (Starting with Oracle 10g R2):</strong><br />
<strong>7. AUTO DOP - Parallel hint (/*+ parallel (x,4) */) doesn’t work with 11.2.0.2:</strong><br />
<strong>8. Index status "INPROGRS"? -- Domain Index</strong><br />
<strong>9. How to stop propagting tranctions in Oracle streams. -- Tag</strong><br />
<strong>10. Myth behind the plan directive values of PARALLEL_DEGREE_LIMIT_P1 in DBRM?</strong><br />
<strong>11. TNS:connection closed:</strong><br />
<strong>12. How can i remove a job from another user?</strong><br />
<strong>13. Snapshot / Backup controlfile location, must be shared location in RAC environment from 11gR2:</strong><br />
<strong><br /></strong><strong>Details:</strong><br />
<strong>1. I don't have Primary Key or Unique Key on the tables and the Oracle Streams apply process is slow, what should I do?:</strong><br />
<br />
-- If you can't create PK/UK's on the source table, you can create alternate keys on the target side.<br />
<br />
<br />
Steps:<br />
a).<br />
-- Check if any alternate keys exist on the target database<br />
<br />
SELECT tc.owner, tc.table_name, tc.column_name, a.compare_old_on_delete,<br />
a.compare_old_on_update,<br />
DECODE (k.column_name, NULL, 'N', 'Y') manual_key_column<br />
FROM dba_tab_columns tc, dba_apply_table_columns a, dba_apply_key_columns k<br />
WHERE a.object_owner(+) = tc.owner<br />
AND a.object_name(+) = tc.table_name<br />
AND a.column_name(+) = tc.column_name<br />
AND k.object_owner(+) = tc.owner<br />
AND k.object_name(+) = tc.table_name<br />
AND k.column_name(+) = tc.column_name<br />
AND DECODE (k.column_name, NULL, 'N', 'Y') = 'Y'<br />
AND owner = '<owner_name>'</owner_name><br />
AND table_name = '<table_name>';</table_name><br />
<br />
b).<br />
-- Add alternate keys on the target database<br />
<br />
connect streams_admin<br />
exec dbms_apply_adm.stop_apply (apply_name => '<apply_name>');</apply_name><br />
<br />
execute DBMS_APPLY_ADM.SET_KEY_COLUMNS(object_name => '<owner_name>.<table_name>', column_list => '<col1>, <col2>');</col2></col1></table_name></owner_name><br />
<br />
execute DBMS_APPLY_ADM.COMPARE_OLD_VALUES(object_name => '<owner_name>.<table_name>', column_list => '*', operation => '*', compare => false); </table_name></owner_name><br />
<br />
exec dbms_apply_adm.start_apply (apply_name => '<apply_name>');</apply_name><br />
<br />
<br />
<strong>2. How to mine a transaction using dictionary online catalog tables:</strong><br />
<br />
Note: Without a dictionary file, LogMiner displays all tables and columns using their internal object ID's and all values as hex data.<br />
<br />
a).<br />
-- add the log file to the log miner session<br />
<br />
execute dbms_logmnr.add_logfile (LOGFILENAME => '+ODS_T1_BACKUP_01/ODS/ARCHIVELOG/2011_03_16/thread_1_seq_197269.606.745926547', OPTIONS => DBMS_LOGMNR.NEW);<br />
<br />
b).<br />
-- alternatively, if dictionary from online catalog is being used :<br />
<br />
EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);<br />
<br />
c).<br />
alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';<br />
<br />
set pagesize 10000<br />
set termout off<br />
<br />
spool mine.out<br />
<br />
column scn format 9999999999999999<br />
column cscn format 9999999999999999<br />
-- determine what the transaction relates to .<br />
select seg_name, operation, timestamp, sql_redo, sql_undo,scn, cscn, xidusn || '.' || xidslt || '.' || xidsqn trnsid, sql_redo, sql_undo <br />
from v$logmnr_contents <br />
where xidusn || '.' || xidslt || '.' || xidsqn='3.14.183859';<br />
<br />
spool off<br />
<br />
<br />
<strong>3. Securefile CLOB/BLOB column compression in 11g, minimum downtime plan:</strong><br />
<br />
-- The initialization parameter db_securefile determines the use of SecureFiles in the database. PERMITTED is the default value.<br />
<br />
a).<br />
set timing on<br />
set time on<br />
set echo on<br />
<br />
spool 01_ODS_UTS_REP_COMPRESS_T345.log<br />
<br />
ALTER SESSION ENABLE PARALLEL DML;<br />
<br />
-- Check table can be redefined<br />
EXEC DBMS_REDEFINITION.can_redef_table('UTS_REP', 'T345');<br />
<br />
CREATE TABLE UTS_REP.T345COMP<br />
TABLESPACE UET_COMP<br />
COMPRESS FOR ARCHIVE HIGH<br />
LOB (C900008110) STORE AS SECUREFILE<br />
( DEDUPLICATE COMPRESS HIGH TABLESPACE UET_COMP )<br />
PARALLEL LOGGING<br />
AS SELECT * FROM UTS_REP.T345 WHERE ROWNUM < 1;<br />
<br />
ALTER TABLE UTS_REP.T345 PARALLEL LOGGING;<br />
<br />
<strong>-- *** Create indexes on the compressed table with different name</strong><br />
-- Start Redefinition<br />
EXEC DBMS_REDEFINITION.start_redef_table('UTS_REP', 'T345', 'T345COMP');<br />
<br />
-- Synchronize new table with interim data<br />
EXEC DBMS_REDEFINITION.sync_interim_table('UTS_REP', 'T345', 'T345COMP');<br />
<br />
SPOOL OFF;<br />
exit;<br />
<br />
Note: C900008110 column is CLOB in this case.<br />
<br />
<br />
b).<br />
<strong>-- OUTAGE STARTS HERE</strong><br />
<br />
spool 02_ODS_UTS_REP_SYNC_T345.log<br />
<br />
-- Synchronize new table with interim data<br />
EXEC DBMS_REDEFINITION.sync_interim_table('UTS_REP', 'T345', 'T345COMP');<br />
<br />
-- Complete redefinition<br />
EXEC DBMS_REDEFINITION.finish_redef_table('UTS_REP', 'T345', 'T345COMP');<br />
<br />
-- Validate the table counts.<br />
SELECT COUNT(*) FROM UTS_REP.T345;<br />
<br />
SELECT COUNT(*) FROM UTS_REP.T345COMP;<br />
<br />
-- Confirm that the table was converted<br />
SELECT column_name, securefile<br />
FROM dba_lobs<br />
WHERE table_name = 'T345';<br />
<br />
SPOOL OFF;<br />
exit;<br />
<br />
<br />
c).<br />
<strong>-- *** Rename the indexes on T345COMP table(here T345COMP table is the original table) and Rename indexes on T345 table with original names.</strong><br />
<strong>-- *** Re-create synonyms and Re-grant the permissions on the compressed T345 table.</strong><br />
<br />
-- VERIFY THE SIZES AND COMPRESSIONS ON THE TWO TABLES BEFORE DROPPING. <br />
-- Now the T345 table should be compressed, but with missing grants. And T345COMP should be the original table without compression.<br />
<br />
<strong>-- OUTAGE ENDS HERE</strong><br />
<br />
-- Do this step with CAUTION<br />
-- If the Compression was successful, please drop UTS_REP.T345COMP else please drop the T345 table and rename T345COMP to T345(original uncompressed table)<br />
<br />
-- *** DROP TABLE UTS_REP.T345COMP CASCADE CONSTRAINTS; -- (OR) Next week cleanup step<br />
<br />
<br />
<strong>4. How to format Oracle output in XML:</strong><br />
<br />
set pages 0<br />
set linesize 200<br />
set long 9999999<br />
set head off<br />
<br />
select dbms_xmlgen.getxml('select * from emp where rownum < 6') xml from dual;<br />
<br />
<strong></strong><br />
<strong>5. ACS and ORA-24247 errors after moving to oracle 11g:</strong><br />
Cause:<br />
-- From 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL) <br />
-- using the new DBMS_NETWORK_ACL_ADMIN package. <br />
<br />
Error:<br />
ORA-24247: network access denied by access control list (ACL)<br />
ORA-06512: at "SYS.UTL_TCP", line 17<br />
ORA-06512: at "SYS.UTL_TCP", line 246<br />
ORA-06512: at "SYS.UTL_SMTP", line 127<br />
ORA-06512: at "SYS.UTL_SMTP", line 150<br />
ORA-06512: at "EBI_STATS.INF_LONG_RUNNING_JOBS", line 78<br />
ORA-06512: at line 2<br />
<br />
<strong>Solution:</strong><br />
-- To resolve this error, connect to the database as a user with DBA privileges and issue the following script which will grant a user or role access <br />
-- to External Network Services.<br />
-- Be sure to replace the user names EBI_STATS and DBA_STATS with the name of the user or role in which you want to grant access. This value is case-sensitive.<br />
<br />
<br />
BEGIN<br />
<br />
<br />
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(<br />
acl => 'network_services.xml',<br />
description => 'Network Services ACL',<br />
principal => 'DBA_STATS',<br />
is_grant => true,<br />
privilege => 'connect');<br />
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(<br />
acl => 'network_services.xml',<br />
principal => 'DBA_STATS',<br />
is_grant => true,<br />
privilege => 'resolve');<br />
<br />
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(<br />
acl => 'network_services.xml',<br />
principal => 'EBI_STATS',<br />
is_grant => true,<br />
privilege => 'connect');<br />
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(<br />
acl => 'network_services.xml',<br />
principal => 'EBI_STATS',<br />
is_grant => true,<br />
privilege => 'resolve');<br />
<br />
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(<br />
acl => 'network_services.xml',<br />
host => '*');<br />
COMMIT;<br />
<br />
END;<br />
/<br />
<br />
<br />
<strong>6. Reclaiming Unused LOB Space (Starting with Oracle 10g R2):</strong> <strong></strong><br />
<br />
-- The LOB segment must reside in an ASSM tablespace. <br />
-- Shrinking a LOB segment does generate redo, For example, shrinking a 100GB LOB will generate approximately 100GB of redo.<br />
-- CASCADE clause that shrinks all dependent objects (including LOBS) along with the table data itself.<br />
<br />
ALTER TABLE <table_name>ENABLE ROW MOVEMENT;</table_name><br />
<br />
ALTER TABLE <table_name>MODIFY LOB (<lob_column>) (SHRINK SPACE);</lob_column></table_name><br />
-- OR --<br />
ALTER TABLE <table_name>SHRINK SPACE CASCADE;</table_name><br />
<br />
ALTER TABLE <table_name>DISABLE ROW MOVEMENT;</table_name><br />
<br />
-- How to find candidate object for Shrinkage?:<br />
set serveroutput on size 999999;<br />
<br />
begin<br />
if (dbms_space.verify_shrink_candidate<br />
('<owner_name>','<table_name>','TABLE', 1073741824) -- Target Shrinkage is 1GB (1073,741,824)</table_name></owner_name><br />
) then<br />
DBMS_OUTPUT.PUT_LINE('Candidates for Shrinking?: YES');<br />
else<br />
DBMS_OUTPUT.PUT_LINE('Candidates for Shrinking?: NO');<br />
end if;<br />
end;<br />
/<br />
<br />
<br />
<br />
<strong>7. AUTO DOP - Parallel hint (/*+ parallel (x,4) */) doesn’t work with 11.2.0.2:</strong> <strong></strong><br />
<br />
Solution: Apply patch 10628995 (or) goto 11.2.0.3 or up (or) remove x from the parallel hint (select /*+ parallel (4) */ count(*) from x;)<br />
<br />
<br />
<strong>8. Index status "INPROGRS"? -- Domain Index</strong> <strong></strong><br />
<br />
-- How to find INPROGRS domain indexes<br />
SELECT index_name, table_name, status, domidx_status, domidx_opstatus FROM dba_indexes WHERE status = 'INPROGRS';<br />
<br />
-- If I try to drop index, what happens?<br />
DROP INDEX OSP.BCDMI_IDX_BUS_NAME;<br />
-- Error: ORA-29868: cannot issue DDL on a domain index marked as LOADING<br />
<br />
-- If I try to rebuild index, what happens?<br />
ALTER INDEX OSP.IDX_BUS_NAME REBUILD PARTITION DNB_MARKET_INSIGHT_201206;<br />
-- Error: <br />
ERROR at line 1:<br />
ORA-29952: cannot issue DDL on a domain index partition marked as LOADING<br />
<br />
-- Fix:<br />
-- Drop the index FORCE.<br />
DROP INDEX DNB.BCDMI_IDX_BUS_NAME FORCE;<br />
<br />
-- Recreate the index.<br />
CREATE INDEX OSP.BCDMI_IDX_BUS_NAME ON OSP.BLD_COMM_DNB_MARKET_INSIGHT<br />
(BUS_NAME)<br />
INDEXTYPE IS CTXSYS.CONTEXT;<br />
<br />
<br />
<strong>9. How to stop propagting tranctions in Oracle streams. -- Tag</strong> <strong></strong><br />
<br />
execute DBMS_STREAMS.SET_TAG(hextoraw('99'));<br />
COMMIT;<br />
<br />
DELETE FROM APP.SESSION WHERE CREATIONTIME < trunc(SYSDATE-7);<br />
COMMIT;<br />
ALTER INDEX APP.SDO$PSESSION$ REBUILD ONLINE;<br />
<br />
execute DBMS_STREAMS.SET_TAG(NULL);<br />
COMMIT;<br />
<br />
<br />
<strong>10. Myth behind the plan directive values of PARALLEL_DEGREE_LIMIT_P1 in DBRM?</strong> <strong></strong><br />
<br />
<strong>Why would you see 2x+1 sessions in DBRM, where x is value of PARALLEL_DEGREE_LIMIT_P1? </strong><br />
<br />
formula: 2x+1<br />
<br />
Where 2 is:<br />
first one is for "join operation".<br />
second one is for "sort operation".<br />
<br />
where x is:<br />
Value of PARALLEL_DEGREE_LIMIT_P1 when creating the plan directive<br />
+1 is for "Query Coordinator".<br />
<br />
So,<br />
When you give PARALLEL_DEGREE_LIMIT_P1 => 4<br />
<br />
Total: 2x+1 = 9 -- Maximum of 9 sessions.<br />
<br />
<br />
<strong>11. TNS:connection closed:</strong> <br />
<br />
Oracle Error:<br />
TNS-12537: TNS:connection closed<br />
<br />
<strong>Issue could be related to the permission on the $ORACLE_HOME/bin/oracle file too!!</strong><br />
<br />
-- Check the current permission of oracle file:<br />
$ /home/oracle > cd /u01/app/oracle/product/11.2.0/dbhome_1/bin;ls -la oracle<br />
-rwxrwsr-x 1 oracle dba 232443941 Jul 3 11:26 oracle*<br />
<br />
-- Fix:<br />
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin > chmod 6751 oracle <br />
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin > ls -la oracle<br />
-rwsr-s--x 1 oracle dba 232443941 Jul 3 11:26 oracle*<br />
<br />
<br />
<strong>12. How can i remove a job from another user?</strong> <strong></strong><br />
<br />
-- Check the Job id and broken status<br />
select job, schema_user, last_date, broken, what from dba_jobs where schema_user in ('XYZ','ABC');<br />
<br />
-- Is it still running?<br />
select * from dba_jobs_running where job in (15490, 7602);<br />
<br />
<br />
-- Remore the Job Id<br />
EXECUTE SYS.DBMS_IJOB.REMOVE (15490);<br />
EXECUTE SYS.DBMS_IJOB.REMOVE (7602);<br />
COMMIT;<br />
<br /><br />
<br />
<strong>13. Snapshot / Backup controlfile location, must be shared location in RAC environment from 11gR2:</strong> <strong></strong><br />
<br />
The snapshot controlfile MUST be accessible by all nodes of a RAC database; if the snapshot controlfile does not reside on a shared device, below error will be raised at the time of RMAN backup while taking snapshot of controlfile.<br />
This applies to backing up controlfile using SQL*Plus / having autobackup of controlfile configured on non shared location.<br />
<br />
Error:<br />
ORA-00245: control file backup operation failed<br />
<br />
Solution:<br />
Solution is the change the Snapshot /backup controlfile location to shared device.<br />
<br />
rman target /<br />
<br />
show all;<br />
<br />
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ODS_T1_BACKUP_01/snapcf_ODS.f';<br />
<br />
show all;Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-32290800410773177402012-06-29T17:10:00.002-04:002012-06-29T17:19:58.445-04:00Oracle Exadata Preventive Maintenance:<strong>Oracle Exadata Preventive Maintenance: </strong><br />
<br />
- Validate software versions are at recommended levels - [ID 888828.1]<br />
- Validate firmware versions are at recommended levels - [ID 888828.1]<br />
- Validate health of all hardware components in the machine - (exachk or HealthCheck [ID 1070954.1])<br />
- Visually inspect the machine for failed or damaged components like slide rails, cable management arms<br />
<br />
<br />
<strong>Exadata machine includes the following consumable components:</strong><br />
<br />
* Batteries in the disk controllers in the storage and database servers which protect the write cache in the event of a power failure.<br />
<br />
* Energy Storage Module (ESM) in the PCIe flash cards in the storage servers which protect the DRAM cache in the event of a power failure. <br />
<br />
Note: Failure of these consumable components will adversely impact performance (Note - there will be no loss of data or wrong results).<br />
<br />
<br />
<strong>Replacement Schedule for Consumable Components:</strong><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRWxg76TadI_CnuK_4Iy-57lxxrJu7zjVJggNDy_oTT_v_bCNv047frO-Ao9xWAw0pzVNdtLr1VTdXaRoN6eBEWimm9vYnJJrWug52fnVd4vSAHdYdrX3ezjjx68tpreeKAOsmbLdhVffK/s1600/Exa_com_Replacement.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><strong><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRWxg76TadI_CnuK_4Iy-57lxxrJu7zjVJggNDy_oTT_v_bCNv047frO-Ao9xWAw0pzVNdtLr1VTdXaRoN6eBEWimm9vYnJJrWug52fnVd4vSAHdYdrX3ezjjx68tpreeKAOsmbLdhVffK/s640/Exa_com_Replacement.JPG" vca="true" width="640" /></strong></a></div>
Note: <br />
1. Pro-active replacement schedule assumes worst case life expectancies. <br />
2. Years 6,7, and later assume system was shipped within 5 years from last ship date or extended support is being offered. <br />
<br />
<br />
<strong>Maintenance Window for pro-active replacement: </strong><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlCGEiMBBgmfqbxrDISq4n72XGT-BkrJlA8FCJCnxZvKMFgxDhOrX9D6gJsLRHVEUopMcMCYz6cKSVgeDW7stJdFLGTQJhrCxYSFm_RbYMYMmgb_NbwkiTbOS4dQF077h9MwznN9rGbHnF/s1600/Exa_mai_WindowJPG.JPG"><strong><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlCGEiMBBgmfqbxrDISq4n72XGT-BkrJlA8FCJCnxZvKMFgxDhOrX9D6gJsLRHVEUopMcMCYz6cKSVgeDW7stJdFLGTQJhrCxYSFm_RbYMYMmgb_NbwkiTbOS4dQF077h9MwznN9rGbHnF/s640/Exa_mai_WindowJPG.JPG" /></strong></a><br />
Note: <br />
1. Times are estimates and will vary from system to system.<br />
2. Full system downtime estimates vary based on whether 1 or 2 service engineers are used.<br />
<br />
<br />
<strong>You will see below alert from cell nodes:</strong><br />
Hardware Alert: CRITICAL<br />
<br />
Description: All Logical drives are in WriteThrough caching mode. Either battery is in a learn cycle or it needs to be replaced. Please contact Oracle Support<br />
<br />
<br />
Affected Cell Name atl03cel01<br />
<br />
Server Model SUN MICROSYSTEMS SUN FIRE X4275 SERVER SATA<br />
<br />
Chassis Serial Number 1005XFG05E<br />
<br />
Version OSS_11.2.2.2.0.9909907V2_LINUX.X64_110310<br />
<br />
<br />
Recommended Action: Battery is either in a learn cycle or it needs replacement. Please contact Oracle Support.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com1tag:blogger.com,1999:blog-4161953958134414599.post-38889252697526434882012-05-31T23:59:00.001-04:002012-10-04T14:22:43.942-04:00NetBackup setup over InfiniBand on Exadata:<strong>NetBackup setup over InfiniBand on Exadata:</strong><br />
<strong></strong><br />
In order to backup all the data within the Exadata compute nodes, we need to use a dedicated media server which is directly connected to the frame through an infiniband connection. <br />
<br />
The storage admins must specify the infiniband IP as the target. However, the master server is not on the infiniband network. We must "trick" the master server to use the proper IP. In the local /etc/hosts file, associate the primary IP on a routeable network to the infiniband hostname (hostname-priv). The master server will try to backup the hostname-priv through the routable network. When the media servers recieves the request, it will lookup the IP associated with the hostname-priv, which will (on the media server) be the infiniband IP. <br />
<br />
On the target exadata DB host, you must specify the media server name associated to the infiniband IP in the /etc/hosts file.<br />
<br />
<br />
<strong>Example setup:</strong><br />
<strong></strong><br />
-- NetBackup <strong>Master Server</strong> /etc/hosts<br />
# Aliases needed to trick exadata to backup over infiniband.<br />
10.62.121.100 atl01db01-priv.corp.dwire.com atl01db01-priv<br />
<br />
-- <strong>Media Server</strong> /etc/hosts<br />
### Compute Node Private Interface details farm 1<br />
192.168.10.1 atl01db01-priv.corp.dwire.com atl01db01-priv<br />
<br />
-- <strong>DB server</strong> /etc/hosts<br />
# EXADATA NBU Media Server Infiniband<br />
192.168.16.89 atli1.corp.dwire.com atli1<br />
<br />
-- The IPs of the example target server (atl01db01) are:<br />
192.168.10.1 atl01db01-priv.corp.dwire.com atl01db01-priv<br />
10.62.125.100 atl01db01.corp.dwire.com atl01db01<br />
10.62.121.100 atl0101.corp.dwire.com atl0101<br />
<br />
<br />
-- bp.conf file on DB server<br />
atlcom01db01:<br />
$ cat /opt/openv/netbackup/bp.conf<br />
SERVER = atli1.corp.dwire.com<br />
CLIENT_NAME = atlcom01db01-privVijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com2tag:blogger.com,1999:blog-4161953958134414599.post-31835426509685920082012-05-29T18:16:00.001-04:002012-10-04T14:26:58.592-04:00Configure InfiniBand Listener on Exadata:<strong>Configure InfiniBand Listener on Exadata:</strong><br />
<br />
InfiniBand listener can be used for ETL's, RMAN active duplicate procedure etc.. for fast data transfer using IB network.<br />
<br />
<br />
Make sure following entries are in sqlnet.ora file on all the database servers, we use soft link from GRID home to DB home.<br />
<br />
AUTOMATIC_IPC = ON<br />
names.directory_path = (TNSNAMES,EZCONNECT)<br />
names.default_domain = world<br />
name.default_zone = world<br />
sqlnet.expire_time = 15<br />
<br />
<br />
<br />
<strong>1.</strong><br />
Add/Identify 8 new IP addresses on the private Infiniband, which are different from existing "-priv" IPs (Same subnet as the IB network) -- Network Admin Task.<br />
<br />
Add Private Virtutal IPs to /etc/hosts for all Exadata compute nodes and client (Informatica Servers or any other):<br />
<br />
### Compute Node Listener Interface details<br />
192.168.16.xx atl01db01-ibvip.corp.dwire.com atl01db01-ibvip<br />
192.168.16.xx atl01db02-ibvip.corp.dwire.com atl01db02-ibvip<br />
192.168.16.xx atl01db03-ibvip.corp.dwire.com atl01db03-ibvip<br />
192.168.16.xx atl01db04-ibvip.corp.dwire.com atl01db04-ibvip<br />
192.168.16.xx atl01db05-ibvip.corp.dwire.com atl01db05-ibvip<br />
192.168.16.xx atl01db06-ibvip.corp.dwire.com atl01db06-ibvip<br />
192.168.16.xx atl01db07-ibvip.corp.dwire.com atl01db07-ibvip<br />
192.168.16.xx atl01db08-ibvip.corp.dwire.com atl01db08-ibvip<br />
<br />
-- Infiniband Sample config: <br />
<br />
cat /etc/sysconfig/network-scripts/ifcfg-bondib0<br />
<br />
#### DO NOT REMOVE THESE LINES ####<br />
#### %GENERATED BY CELL% ####<br />
DEVICE=bondib0<br />
USERCTL=no<br />
BOOTPROTO=none<br />
ONBOOT=yes<br />
IPADDR=192.168.16.1<br />
NETMASK=255.255.252.0<br />
NETWORK=192.168.16.0<br />
BROADCAST=192.168.19.255<br />
BONDING_OPTS="mode=active-backup miimon=100 downdelay=5000 updelay=5000 num_grat_arp=100"<br />
IPV6INIT=no<br />
MTU=65520<br />
<br />
<br />
<strong>2.</strong><br />
As <strong>root</strong> user, add secondary network to the cluster:<br />
<br />
-- Currenly How many networks configured? -- Only one network found<br />
/u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net<br />
<br />
<strong>Output:</strong><br />
$ /u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net<br />
ora.net1.network<br />
<br />
<br />
<strong> -- Add the second network</strong><br />
/u01/app/11.2.0.2/grid/bin/srvctl add network -k 2 -S 192.168.16.0/255.255.252.0/bondib0<br />
<br />
<strong>-- Help 11.2.0.2</strong><br />
<strong>> srvctl add network -help</strong><br />
<br />
Adds a network configuration to the Oracle Clusterware.<br />
Usage: srvctl add network [-k net_num] -S subnet/netmask/[if1[if2...]] [-w network_type] [-v]<br />
-k net_num network number (default number is 1)<br />
-S subnet/netmask/[if1[if2...]] NET address spec for network<br />
-w network_type The network type (static, dhcp, mixed)<br />
-h Print usage<br />
-v Verbose output<br />
<br />
-- Verify newly added network has been added<br />
/u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net<br />
<br />
<strong>Output:</strong><br />
$ /u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net<br />
ora.net1.network<br />
ora.net2.network<br />
<br />
<br />
<strong>3.</strong><br />
-- Register newly created Virtual IP's with the CRS as <strong>root</strong> user:<br />
<br />
cd /u01/app/11.2.0.2/grid/bin<br />
srvctl add vip -n atl01db01 -A atl01db01-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db02 -A atl01db02-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db03 -A atl01db03-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db04 -A atl01db04-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db05 -A atl01db05-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db06 -A atl01db06-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db07 -A atl01db07-ibvip/255.255.252.0/bondib0 -k 2<br />
srvctl add vip -n atl01db08 -A atl01db08-ibvip/255.255.252.0/bondib0 -k 2<br />
<br />
<br />
<strong>4.</strong><br />
As <strong>oracle</strong> user:<br />
-- Register newly added listener to CRS to make sure listener will come up automatically after the server restart.<br />
<br />
srvctl add listener -l LISTENER_IB -k 2 -p TCP:1522<br />
srvctl config listener<br />
<br />
<strong>Output:</strong><br />
$ srvctl config listener<br />
Name: LISTENER<br />
Network: 1, Owner: oracle<br />
Home: CRS home<crs home=""><br />End points: TCP:1521<br />Name: LISTENER_IB<br />Network: 2, Owner: oracle<br />Home: CRS home<crs home=""><br />End points: TCP:1522<br /><br /><strong>-- Help 11.2.0.2</strong><br /><strong>> srvctl add listener -help</strong><br /><br />Adds a listener configuration to the Oracle Clusterware.<br /><br />Usage: srvctl add listener [-l lsnr_name] [-s] [-p "[TCP:]port[, ...][/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]"] [-o oracle_home] [-k net_num]<br /><br />-l lsnr_name Listener name (default name is LISTENER)<br />-o oracle_home ORACLE_HOME path (default value is CRS_HOME)<br />-k net_num network number (default number is 1)<br />-s Skip the checking of ports<br />-p "[TCP:]port[, ...][/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]" Comma separated tcp ports or listener endpoints<br />-h Print usage<br /><br /><br /><strong>5.</strong><br />Update $ORACLE_HOME/network/admin/tnsnames.ora file on all nodes:<br /><br />Imp:<br />Each TNSNAMES.ORA file is different for each server depending on the Local and Remote listener settings.<br /><br />Local Listener, have information about local node and local IB vip host information.<br />Remote Listener, have information about other IB vip hosts information except the Local node<br /><br /><br /><strong>Sample entries for node 1 (atl01db01):</strong><br /><br /><strong>Local:</strong><br />LISTENER_IBLOCAL.WORLD =<br />(DESCRIPTION =<br />(ADDRESS_LIST =<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01-ibvip)(PORT = 1522))<br />)<br />)<br /><br />LISTENER_IPLOCAL.WORLD =<br />(DESCRIPTION =<br />(ADDRESS_LIST =<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01)(PORT = 1521))<br />)<br />)<br /><br /><strong>Remote:</strong><br />LISTENER_IBREMOTE.WORLD =<br />(DESCRIPTION =<br />(ADDRESS_LIST =<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db02-ibvip)(PORT = 1522)) <strong>-- no node 1 information on node 1!!</strong><br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db03-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db04-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db05-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db06-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db07-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db08-ibvip)(PORT = 1522))<br />)<br />)<br /><br />LISTENER_IPREMOTE.WORLD =<br />(DESCRIPTION =<br />(ADDRESS_LIST =<br />(ADDRESS = (PROTOCOL = TCP)(HOST = ATL02-SCAN)(PORT = 1521))<br />)<br />)<br /><br />Imp:<br />Repeat the same for the other nodes, changing Local and Remote values accordingly.<br /><br /><br /><strong>6 .</strong><br />-- Update the listeners_network DB parameter on all the databases with the listener addresses from both the networks, i.,e network1 and network2<br /><br />alter system set listener_networks='((NAME=network2) (LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))','((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both sid='*';<br /><br />-- Restart the listener and validate the entry.<br />show parameter listener_networks<br /><br /><br />-- What do I see with "ps -ef"?<br />> ps -ef | grep LISTENER<br />oracle 12874 1 0 May09 ? 00:01:20 /u01/app/11.2.0.2/grid/bin/tnslsnr LISTENER -inherit<br />oracle 24335 1 0 May12 ? 00:05:22 /u01/app/11.2.0.2/grid/bin/tnslsnr LISTENER_IB -inherit<br /><br /><br /><strong>7.</strong><br />-- Add TNS entry on client side:<br /><br /><strong>Sample TNS entry:</strong><br /><br />ODS.WORLD =<br />(DESCRIPTION =<br />(LOAD_BALANCE = ON)<br />(ADDRESS_LIST =<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db02-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db03-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db04-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db05-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db06-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db07-ibvip)(PORT = 1522))<br />(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db08-ibvip)(PORT = 1522))<br />)<br />(CONNECT_DATA =<br />(SERVER = DEDICATED)<br />(SERVICE_NAME = ODS_INFA_SERVICE.world)<br />)<br />)<br /><br /><br /><strong>-- How did you create the Service?:</strong><br />$ srvctl add service -d ODS -s ODS_INFA_SERVICE -r ODS1,ODS2,ODS3,ODS4 -a ODS5,ODS6,ODS7,ODS8 -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG -k2<br /><br /><strong>-- Service configuration:</strong><br />$ srvctl config service -d ODS -s ODS_INFA_SERVICE<br />Service name: ODS_INFA_SERVICE<br />Service is enabled<br />Server pool: ODS_ODS_INFA_SERVICE<br />Cardinality: 4<br />Disconnect: false<br />Service role: PRIMARY<br />Management policy: AUTOMATIC<br />DTP transaction: false<br />AQ HA notifications: true<br />Failover type: SELECT<br />Failover method: BASIC<br />TAF failover retries: 180<br />TAF failover delay: 5<br />Connection Load Balancing Goal: LONG<br />Runtime Load Balancing Goal: NONE<br />TAF policy specification: NONE<br />Edition:<br />Preferred instances: ODS1,ODS2,ODS3,ODS4<br />Available instances: ODS5,ODS6,ODS7,ODS8<br /><br /><br /><strong>-- Help 11.2.0.2</strong><br /><strong>> srvctl add service -help</strong><br /><br />Adds a service configuration to the Oracle Clusterware.<br /><br />Usage: srvctl add service -d db_unique_name -s service_name {-r "preferred_list" [-a "available_list"] [-P {BASIC | NONE | PRECONNECT}] <br />-g pool_name [-c {UNIFORM | SINGLETON}] } [-k net_num] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] <br />[-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}] <br />[-x {TRUE | FALSE}] [-j {SHORT | LONG}] <br />[-B {NONE | SERVICE_TIME | THROUGHPUT}] <br />[-e {NONE | SESSION | SELECT}] [-m {NONE | BASIC}] <br />[-z failover_retries] [-w failover_delay] [-t edition] [-f]<br /><br />-d db_unique_name Unique name for the database<br /><br />-s service Service name<br />-r "preferred_list" Comma separated list of preferred instances<br />-a "available_list" Comma separated list of available instances<br />-g pool_name Server pool name<br />-c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)<br />-k net_num network number (default number is 1)<br />-P {NONE | BASIC | PRECONNECT} TAF policy specification<br />-l role Role of the service (primary, physical_standby, logical_standby, snapshot_standby)<br />-y policy Management policy for the service (AUTOMATIC or MANUAL)<br />-e Failover type Failover type (NONE, SESSION, or SELECT)<br />-m Failover method Failover method (NONE or BASIC)<br />-w integer Failover delay<br />-z integer Failover retries<br />-t edition Edition (or "" for empty edition value)<br />-j clb_goal Connection Load Balancing Goal (SHORT or LONG). Default is LONG.<br />-B Runtime Load Balancing Goal Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)<br />-x Distributed Transaction Processing Distributed Transaction Processing (TRUE or FALSE)<br />-q AQ HA notifications AQ HA notifications (TRUE or FALSE)<br />Usage: srvctl add service -d db_unique_name -s service_name -u {-r "new_pref_inst" <br />-a "new_avail_inst"} [-f]<br />-d db_unique_name Unique name for the database<br />-s service Service name<br />-u Add a new instance to service configuration<br />-r new_pref_inst Name of new preferred instance<br />-a new_avail_inst Name of new available instance<br />-f Force the add operation even though a listener is not configured for a network<br />-h Print usage</crs></crs>Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com5tag:blogger.com,1999:blog-4161953958134414599.post-62455449300363357352012-05-25T22:44:00.005-04:002012-06-14T12:39:57.343-04:00Exadata Flash Cache Maintenance:<b>Exadata Flash Cache Maintenance:</b> <br />
This is one of the common maintenance task on Exadata cell nodes; just want to publish the steps to correct Flash Cache issue. <br />
<br />
<strong>-- Pre re-create flashcache steps</strong><br />
<strong>1.</strong><br />
-- Run the following command to check if there are other offline disks<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome <strong>!=</strong> 'Yes'<br />
<br />
If any grid disks are returned, then it is not safe to take the storage server offline because proper Oracle ASM disk group redundancy will not be intact. <br />
<br />
Taking the storage server offline when one or more grid disks are in this state will cause Oracle ASM to dismount the affected disk group, causing the databases to shut down abruptly.<br />
<br />
<strong>Output:</strong><br />
CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome <strong>!= </strong>'Yes' -- No records selected. -- Safe<br />
CellCLI> <br />
<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome <strong>=</strong> 'Yes'<br />
DATA_CD_00_atl02cel06<br />
DATA_CD_01_atl02cel06<br />
DATA_CD_02_atl02cel06<br />
DATA_CD_03_atl02cel06<br />
DATA_CD_04_atl02cel06<br />
DATA_CD_05_atl02cel06<br />
DATA_CD_06_atl02cel06<br />
DATA_CD_07_atl02cel06<br />
DATA_CD_08_atl02cel06<br />
DATA_CD_09_atl02cel06<br />
DATA_CD_10_atl02cel06<br />
DATA_CD_11_atl02cel06<br />
RECO_CD_00_atl02cel06<br />
RECO_CD_01_atl02cel06<br />
RECO_CD_02_atl02cel06<br />
RECO_CD_03_atl02cel06<br />
RECO_CD_04_atl02cel06<br />
RECO_CD_05_atl02cel06<br />
RECO_CD_06_atl02cel06<br />
RECO_CD_07_atl02cel06<br />
RECO_CD_08_atl02cel06<br />
RECO_CD_09_atl02cel06<br />
RECO_CD_10_atl02cel06<br />
RECO_CD_11_atl02cel06<br />
SYSTEMDG_CD_02_atl02cel06<br />
SYSTEMDG_CD_03_atl02cel06<br />
SYSTEMDG_CD_04_atl02cel06<br />
SYSTEMDG_CD_05_atl02cel06<br />
SYSTEMDG_CD_06_atl02cel06<br />
SYSTEMDG_CD_07_atl02cel06<br />
SYSTEMDG_CD_08_atl02cel06<br />
SYSTEMDG_CD_09_atl02cel06<br />
SYSTEMDG_CD_10_atl02cel06<br />
SYSTEMDG_CD_11_atl02cel06<br />
<br />
<br />
<strong>2.</strong><br />
-- Get a listing of physical disks and celldisk. You can use this later to match up the WWN's to ensure you are removing the correct module.<br />
CellCLI> list physicaldisk<br />
CellCLI> list celldisk<br />
<br />
<strong>Output:</strong><br />
<strong>-- Missing disk output: - missing few disks from 3 and 4 in this output.</strong><br />
CellCLI> list physicaldisk<br />
16:0 JK1130YAHARN4T normal<br />
16:1 JK1130YAHBWWZT normal<br />
16:2 JK1130YAHBV76T normal<br />
16:3 JK1130YAHBV7TT normal<br />
16:4 JK1130YAH49UJT normal<br />
16:5 JK1130YAHBJ5HT normal<br />
16:6 JK1130YAHBJNHT normal<br />
16:7 JK1130YAHATA3T normal<br />
16:8 JK1130YAHAJX7T normal<br />
16:9 JK1130YAHBJNJT normal<br />
16:10 JK1130YAHBSBZT normal<br />
16:11 JK1130YAHBV7PT normal<br />
[1:0:0:0] 5080020000c47c0FMOD0 normal<br />
[1:0:1:0] 5080020000c47c0FMOD1 normal<br />
[1:0:2:0] 5080020000c47c0FMOD2 normal<br />
[1:0:3:0] 5080020000c47c0FMOD3 normal<br />
[2:0:0:0] 5080020000c47eaFMOD0 normal<br />
[2:0:1:0] 5080020000c47eaFMOD1 normal<br />
[2:0:2:0] 5080020000c47eaFMOD2 normal<br />
[2:0:3:0] 5080020000c47eaFMOD3 normal<br />
<br />
<strong>-- Some times output may look like this. -- This output is from different server</strong><br />
CellCLI> list physicaldisk<br />
24:0 JK1130YAG52NAT normal<br />
24:1 JK1130YAG4EPZT normal<br />
24:2 JK1130YAG0VABT normal<br />
24:3 JK1130YAG536XT normal<br />
24:4 JK1130YAG51TVT normal<br />
24:5 JK1130YAG0VA9T normal<br />
24:6 JK1130YAG51TUT normal<br />
24:7 JK1130YAG0VRKT normal<br />
24:8 JK1130YAG51ZYT normal<br />
24:9 JK1130YAG0VD4T normal<br />
24:10 JK1130YAG0VN3T normal<br />
24:11 JK1130YAG52NKT normal<br />
[1:0:0:0] 5080020000c4262FMOD0 normal<br />
[1:0:1:0] 5080020000c4262FMOD1 normal<br />
[1:0:2:0] 5080020000c4262FMOD2 normal<br />
[1:0:3:0] 5080020000c4262FMOD3 normal<br />
[2:0:0:0] 5080020000c421cFMOD0 normal<br />
[2:0:1:0] 5080020000c421cFMOD1 normal<br />
[2:0:2:0] 5080020000c421cFMOD2 normal<br />
[3:0:0:0] 5080020000c422eFMOD0 normal<br />
[3:0:1:0] 5080020000c422eFMOD1 <strong>poor performance</strong><br />
[3:0:2:0] 5080020000c422eFMOD2 <strong>poor performance</strong><br />
[3:0:3:0] 5080020000c422eFMOD3 <strong>poor performance</strong><br />
[4:0:0:0] 5080020000c427cFMOD0 normal<br />
[4:0:1:0] 5080020000c427cFMOD1 normal<br />
[4:0:2:0] 5080020000c427cFMOD2 normal<br />
[4:0:3:0] 5080020000c427cFMOD3 normal<br />
<br />
CellCLI> list celldisk<br />
CD_00_atl02cel06 normal<br />
CD_01_atl02cel06 normal<br />
CD_02_atl02cel06 normal<br />
CD_03_atl02cel06 normal<br />
CD_04_atl02cel06 normal<br />
CD_05_atl02cel06 normal<br />
CD_06_atl02cel06 normal<br />
CD_07_atl02cel06 normal<br />
CD_08_atl02cel06 normal<br />
CD_09_atl02cel06 normal<br />
CD_10_atl02cel06 normal<br />
CD_11_atl02cel06 normal<br />
FD_00_atl02cel06 normal<br />
FD_01_atl02cel06 normal<br />
FD_02_atl02cel06 normal<br />
FD_03_atl02cel06 normal<br />
FD_04_atl02cel06 <strong>not present</strong><br />
FD_05_atl02cel06 <strong>not present</strong><br />
FD_06_atl02cel06 <strong>not present</strong><br />
FD_07_atl02cel06 <strong>not present</strong><br />
FD_08_atl02cel06 normal<br />
FD_09_atl02cel06 normal<br />
FD_10_atl02cel06 normal<br />
FD_11_atl02cel06 normal<br />
FD_12_atl02cel06 <strong>not present</strong><br />
FD_13_atl02cel06 <strong>not present</strong><br />
FD_14_atl02cel06 <strong>not present</strong><br />
FD_15_atl02cel06 <strong>not present</strong><br />
<br />
<br />
<strong>3.</strong><br />
-- Inactivate all the grid disks when Oracle Exadata Storage Server is safe to take offline using the following command:<br />
CellCLI> ALTER GRIDDISK ALL INACTIVE<br />
<br />
The preceding command will complete once all disks are inactive and offline. <br />
Depending on the storage server activity, it may take several minutes for this command to complete.<br />
<br />
<br />
<strong>4.</strong><br />
-- Verify all grid disks are INACTIVE to allow safe storage server shut down by running the following command:<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
CellCLI> LIST GRIDDISK<br />
<br />
If all grid disks are INACTIVE, then the storage server can be shutdown without affecting database availability<br />
<br />
<strong>Output:</strong><br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
DATA_CD_00_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_01_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_02_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_03_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_04_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_05_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_06_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_07_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_08_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_09_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_10_atl02cel06 <strong>UNKNOWN</strong><br />
DATA_CD_11_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_00_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_01_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_02_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_03_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_04_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_05_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_06_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_07_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_08_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_09_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_10_atl02cel06 <strong>UNKNOWN</strong><br />
RECO_CD_11_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_02_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_03_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_04_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_05_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_06_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_07_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_08_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_09_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_10_atl02cel06 <strong>UNKNOWN</strong><br />
SYSTEMDG_CD_11_atl02cel06 <strong>UNKNOWN</strong><br />
<br />
-- This should be INACTIVE, when you do "ALTER GRIDDISK ALL INACTIVE"<br />
CellCLI> LIST GRIDDISK<br />
DATA_CD_00_atl02cel06 active<br />
DATA_CD_01_atl02cel06 active<br />
DATA_CD_02_atl02cel06 active<br />
DATA_CD_03_atl02cel06 active<br />
DATA_CD_04_atl02cel06 active<br />
DATA_CD_05_atl02cel06 active<br />
DATA_CD_06_atl02cel06 active<br />
DATA_CD_07_atl02cel06 active<br />
DATA_CD_08_atl02cel06 active<br />
DATA_CD_09_atl02cel06 active<br />
DATA_CD_10_atl02cel06 active<br />
DATA_CD_11_atl02cel06 active<br />
RECO_CD_00_atl02cel06 active<br />
RECO_CD_01_atl02cel06 active<br />
RECO_CD_02_atl02cel06 active<br />
RECO_CD_03_atl02cel06 active<br />
RECO_CD_04_atl02cel06 active<br />
RECO_CD_05_atl02cel06 active<br />
RECO_CD_06_atl02cel06 active<br />
RECO_CD_07_atl02cel06 active<br />
RECO_CD_08_atl02cel06 active<br />
RECO_CD_09_atl02cel06 active<br />
RECO_CD_10_atl02cel06 active<br />
RECO_CD_11_atl02cel06 active<br />
SYSTEMDG_CD_02_atl02cel06 active<br />
SYSTEMDG_CD_03_atl02cel06 active<br />
SYSTEMDG_CD_04_atl02cel06 active<br />
SYSTEMDG_CD_05_atl02cel06 active<br />
SYSTEMDG_CD_06_atl02cel06 active<br />
SYSTEMDG_CD_07_atl02cel06 active<br />
SYSTEMDG_CD_08_atl02cel06 active<br />
SYSTEMDG_CD_09_atl02cel06 active<br />
SYSTEMDG_CD_10_atl02cel06 active<br />
SYSTEMDG_CD_11_atl02cel06 active<br />
<br />
<br />
<strong>5.</strong><br />
-- Check the status of FLASHCACHE<br />
CellCLI> LIST FLASHCACHE detail<br />
<br />
Output: -- When have issue.<br />
CellCLI> LIST FLASHCACHE detail<br />
name: atl02cel06_FLASHCACHE<br />
cellDisk: FD_10_atl02cel06,FD_09_atl02cel06,FD_01_atl02cel06,FD_11_atl02cel06,FD_03_atl02cel06,FD_08_atl02cel06,FD_00_atl02cel06,FD_02_atl02cel06<br />
creationTime: 2010-07-14T19:51:22-04:00<br />
degradedCelldisks: FD_13_atl02cel06,FD_14_atl02cel06,FD_07_atl02cel06,FD_15_atl02cel06,FD_05_atl02cel06,FD_06_atl02cel06,FD_04_atl02cel06,FD_12_atl02cel06<br />
effectiveCacheSize: 182.625G<br />
id: 60a179f7-2fdf-44f4-a63d-9cfdd03d42cc<br />
size: 365.25G<br />
<strong>status: warning</strong><br />
<br />
-- Status should be normal, but here it's warning...<br />
<br />
<br />
<strong>6.</strong><br />
-- We can now drop the flashcache here using the command <br />
<br />
CellCLI> drop flashcache all<br />
<br />
This will ensure that on startup the flashcache does not start<br />
<br />
<br />
<strong>7.</strong><br />
-- Stop the cell services using the following command:<br />
CellCLI> ALTER CELL SHUTDOWN SERVICES ALL<br />
<br />
<br />
<strong>8.</strong><br />
Unix SA -- REBOOT atl02cel06.(root) for replacing the FLASHCACHE cards<br />
<br />
#shutdown -h -y now<br />
<br />
The cell services will be started automatically.<br />
<br />
---------------------------------------------------<br />
<br />
a. Replace the failed flash disk based on the PCI number and FDOM number.<br />
NOTE: You can also use the WWN from the "list physicaldisk" command earlier.<br />
Example: [2:0:0:0] 5080020000fcf12FMOD0 normal<br />
[2:0:0:0] 5080020000f "cf12" FMOD0 normal<br />
The number "cf12" above should be on an orange sticker on the back of each flash pci card.<br />
<br />
b. Power up the cell. The cell services will be started automatically.<br />
<br />
c. After boot, verify the replaced disks have the same firmware...<br />
<br />
/opt/oracle.SupportTools/CheckHWnFWProfile -c strict<br />
-OR-<br />
dmesg | grep -i marvell # Verify all revisions are the same<br />
<br />
-- If firmware is not the same across all disks, then do the following...<br />
rm /opt/oracle.cellos/TRIED_FW_UPDATE_ONCE<br />
reboot<br />
---------------------------------------------------<br />
<br />
<br />
<br />
<strong>-- Re-create flashcache Steps</strong><br />
<strong>9.</strong><br />
-- Configure Flash Cache<br />
<br />
CellCLI> drop celldisk all flashdisk force<br />
CellCLI> create celldisk all flashdisk<br />
CellCLI> create flashcache all<br />
CellCLI> LIST FLASHCACHE detail<br />
<br />
<br />
<strong>10.</strong><br />
-- Bring all grid disks online using the following command:<br />
<br />
CellCLI> ALTER GRIDDISK ALL ACTIVE<br />
<br />
<strong>Note:</strong><br />
<strong>-- When the grid disks become active, Oracle ASM will automatically synchronize the gird disks to bring them back into the disk group.</strong><br />
<strong>-- If ASM is down, need to bring up ASM on atleast one node to see the celldisk "ONLINE", if not celldisk will NOT be in ONLINE until the ASM is up!!</strong><br />
<br />
<br />
<br />
<br />
<strong>--Validation steps</strong><br />
<strong>11.</strong><br />
-- Verify all grid disks have been successfully put online using the following command:<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
<br />
Wait until asmmodestatus is ONLINE for all grid disks. <br />
<br />
<strong>Output:</strong><br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
DATA_CD_00_atl02cel06 ONLINE<br />
DATA_CD_01_atl02cel06 ONLINE<br />
DATA_CD_02_atl02cel06 ONLINE<br />
DATA_CD_03_atl02cel06 ONLINE<br />
DATA_CD_04_atl02cel06 ONLINE<br />
DATA_CD_05_atl02cel06 ONLINE<br />
DATA_CD_06_atl02cel06 ONLINE<br />
DATA_CD_07_atl02cel06 ONLINE<br />
DATA_CD_08_atl02cel06 ONLINE<br />
DATA_CD_09_atl02cel06 ONLINE<br />
DATA_CD_10_atl02cel06 ONLINE<br />
DATA_CD_11_atl02cel06 ONLINE<br />
RECO_CD_00_atl02cel06 ONLINE<br />
RECO_CD_01_atl02cel06 ONLINE<br />
RECO_CD_02_atl02cel06 ONLINE<br />
RECO_CD_03_atl02cel06 ONLINE<br />
RECO_CD_04_atl02cel06 ONLINE<br />
RECO_CD_05_atl02cel06 ONLINE<br />
RECO_CD_06_atl02cel06 ONLINE<br />
RECO_CD_07_atl02cel06 ONLINE<br />
RECO_CD_08_atl02cel06 ONLINE<br />
RECO_CD_09_atl02cel06 ONLINE<br />
RECO_CD_10_atl02cel06 ONLINE<br />
RECO_CD_11_atl02cel06 ONLINE<br />
SYSTEMDG_CD_02_atl02cel06 ONLINE<br />
SYSTEMDG_CD_03_atl02cel06 ONLINE<br />
SYSTEMDG_CD_04_atl02cel06 ONLINE<br />
SYSTEMDG_CD_05_atl02cel06 ONLINE<br />
SYSTEMDG_CD_06_atl02cel06 ONLINE<br />
SYSTEMDG_CD_07_atl02cel06 ONLINE<br />
SYSTEMDG_CD_08_atl02cel06 ONLINE<br />
SYSTEMDG_CD_09_atl02cel06 ONLINE<br />
SYSTEMDG_CD_10_atl02cel06 ONLINE<br />
SYSTEMDG_CD_11_atl02cel06 ONLINE<br />
<br />
<br />
<strong>12.</strong><br />
-- Oracle ASM synchronization is only complete when all grid disks show asmmodestatus=ONLINE.<br />
-- Before taking another storage server offline, Oracle ASM synchronization must complete on the restarted Oracle Exadata Storage Server. <br />
-- If synchronization is not complete, then the check performed on another storage server will fail. <br />
<br />
CellCLI> list griddisk attributes name where asmdeactivationoutcome != 'Yes'<br />
<br />
<br />
<strong>13.</strong><br />
-- Wait until asmmodestatus shows ONLINE for all grid disks.<br />
-- List the disks and confirm they are all normal.<br />
<br />
CellCLI> LIST GRIDDISK ATTRIBUTES asmmodestatus<br />
CellCLI> list physicaldisk<br />
CellCLI> list celldisk<br />
CellCLI> list griddisk<br />
<br />
-- All disks are visable after re-creating the flashcache/ compare the output from Step 2<br />
CellCLI> list physicaldisk<br />
16:0 JK1130YAHARN4T normal<br />
16:1 JK1130YAHBWWZT normal<br />
16:2 JK1130YAHBV76T normal<br />
16:3 JK1130YAHBV7TT normal<br />
16:4 JK1130YAH49UJT normal<br />
16:5 JK1130YAHBJ5HT normal<br />
16:6 JK1130YAHBJNHT normal<br />
16:7 JK1130YAHATA3T normal<br />
16:8 JK1130YAHAJX7T normal<br />
16:9 JK1130YAHBJNJT normal<br />
16:10 JK1130YAHBSBZT normal<br />
16:11 JK1130YAHBV7PT normal<br />
[1:0:0:0] 5080020000c47c0FMOD0 normal<br />
[1:0:1:0] 5080020000c47c0FMOD1 normal<br />
[1:0:2:0] 5080020000c47c0FMOD2 normal<br />
[1:0:3:0] 5080020000c47c0FMOD3 normal<br />
[2:0:0:0] 5080020000c47eaFMOD0 normal<br />
[2:0:1:0] 5080020000c47eaFMOD1 normal<br />
[2:0:2:0] 5080020000c47eaFMOD2 normal<br />
[2:0:3:0] 5080020000c47eaFMOD3 normal<br />
[3:0:0:0] 5080020000c47feFMOD0 normal<br />
[3:0:1:0] 5080020000c47feFMOD1 normal<br />
[3:0:2:0] 5080020000c47feFMOD2 normal<br />
[3:0:3:0] 5080020000c47feFMOD3 normal<br />
[4:0:0:0] 5080020000c47b2FMOD0 normal<br />
[4:0:1:0] 5080020000c47b2FMOD1 normal<br />
[4:0:2:0] 5080020000c47b2FMOD2 normal<br />
[4:0:3:0] 5080020000c47b2FMOD3 normal<br />
<br />
-- <br />
CellCLI> list celldisk<br />
CD_00_atl02cel06 normal<br />
CD_01_atl02cel06 normal<br />
CD_02_atl02cel06 normal<br />
CD_03_atl02cel06 normal<br />
CD_04_atl02cel06 normal<br />
CD_05_atl02cel06 normal<br />
CD_06_atl02cel06 normal<br />
CD_07_atl02cel06 normal<br />
CD_08_atl02cel06 normal<br />
CD_09_atl02cel06 normal<br />
CD_10_atl02cel06 normal<br />
CD_11_atl02cel06 normal<br />
FD_00_atl02cel06 normal<br />
FD_01_atl02cel06 normal<br />
FD_02_atl02cel06 normal<br />
FD_03_atl02cel06 normal<br />
FD_04_atl02cel06 normal<br />
FD_05_atl02cel06 normal<br />
FD_06_atl02cel06 normal<br />
FD_07_atl02cel06 normal<br />
FD_08_atl02cel06 normal<br />
FD_09_atl02cel06 normal<br />
FD_10_atl02cel06 normal<br />
FD_11_atl02cel06 normal<br />
FD_12_atl02cel06 normal<br />
FD_13_atl02cel06 normal<br />
FD_14_atl02cel06 normal<br />
FD_15_atl02cel06 normal<br />
<br />
--<br />
CellCLI> list griddisk<br />
DATA_CD_00_atl02cel06 active<br />
DATA_CD_01_atl02cel06 active<br />
DATA_CD_02_atl02cel06 active<br />
DATA_CD_03_atl02cel06 active<br />
DATA_CD_04_atl02cel06 active<br />
DATA_CD_05_atl02cel06 active<br />
DATA_CD_06_atl02cel06 active<br />
DATA_CD_07_atl02cel06 active<br />
DATA_CD_08_atl02cel06 active<br />
DATA_CD_09_atl02cel06 active<br />
DATA_CD_10_atl02cel06 active<br />
DATA_CD_11_atl02cel06 active<br />
RECO_CD_00_atl02cel06 active<br />
RECO_CD_01_atl02cel06 active<br />
RECO_CD_02_atl02cel06 active<br />
RECO_CD_03_atl02cel06 active<br />
RECO_CD_04_atl02cel06 active<br />
RECO_CD_05_atl02cel06 active<br />
RECO_CD_06_atl02cel06 active<br />
RECO_CD_07_atl02cel06 active<br />
RECO_CD_08_atl02cel06 active<br />
RECO_CD_09_atl02cel06 active<br />
RECO_CD_10_atl02cel06 active<br />
RECO_CD_11_atl02cel06 active<br />
SYSTEMDG_CD_02_atl02cel06 active<br />
SYSTEMDG_CD_03_atl02cel06 active<br />
SYSTEMDG_CD_04_atl02cel06 active<br />
SYSTEMDG_CD_05_atl02cel06 active<br />
SYSTEMDG_CD_06_atl02cel06 active<br />
SYSTEMDG_CD_07_atl02cel06 active<br />
SYSTEMDG_CD_08_atl02cel06 active<br />
SYSTEMDG_CD_09_atl02cel06 active<br />
SYSTEMDG_CD_10_atl02cel06 active<br />
SYSTEMDG_CD_11_atl02cel06 active<br />
<br />
<br />
<strong>14.</strong><br />
-- Go to all the cells and run this command:<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
<br />
--<br />
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus<br />
DATA_CD_00_atl02cel06 ONLINE<br />
DATA_CD_01_atl02cel06 ONLINE<br />
DATA_CD_02_atl02cel06 ONLINE<br />
DATA_CD_03_atl02cel06 ONLINE<br />
DATA_CD_04_atl02cel06 ONLINE<br />
DATA_CD_05_atl02cel06 ONLINE<br />
DATA_CD_06_atl02cel06 ONLINE<br />
DATA_CD_07_atl02cel06 ONLINE<br />
DATA_CD_08_atl02cel06 ONLINE<br />
DATA_CD_09_atl02cel06 ONLINE<br />
DATA_CD_10_atl02cel06 ONLINE<br />
DATA_CD_11_atl02cel06 ONLINE<br />
RECO_CD_00_atl02cel06 ONLINE<br />
RECO_CD_01_atl02cel06 ONLINE<br />
RECO_CD_02_atl02cel06 ONLINE<br />
RECO_CD_03_atl02cel06 ONLINE<br />
RECO_CD_04_atl02cel06 ONLINE<br />
RECO_CD_05_atl02cel06 ONLINE<br />
RECO_CD_06_atl02cel06 ONLINE<br />
RECO_CD_07_atl02cel06 ONLINE<br />
RECO_CD_08_atl02cel06 ONLINE<br />
RECO_CD_09_atl02cel06 ONLINE<br />
RECO_CD_10_atl02cel06 ONLINE<br />
RECO_CD_11_atl02cel06 ONLINE<br />
SYSTEMDG_CD_02_atl02cel06 ONLINE<br />
SYSTEMDG_CD_03_atl02cel06 ONLINE<br />
SYSTEMDG_CD_04_atl02cel06 ONLINE<br />
SYSTEMDG_CD_05_atl02cel06 ONLINE<br />
SYSTEMDG_CD_06_atl02cel06 ONLINE<br />
SYSTEMDG_CD_07_atl02cel06 ONLINE<br />
SYSTEMDG_CD_08_atl02cel06 ONLINE<br />
SYSTEMDG_CD_09_atl02cel06 ONLINE<br />
SYSTEMDG_CD_10_atl02cel06 ONLINE<br />
SYSTEMDG_CD_11_atl02cel06 ONLINEVijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com2tag:blogger.com,1999:blog-4161953958134414599.post-56879782737897028712012-01-16T11:47:00.003-05:002012-01-16T11:52:13.642-05:00I/O Resource Management(IORM) with Exadata<strong>I/O Resource Management(IORM) with Exadata</strong><br /><br /><strong>a)<br />-- How to configure IORM on cell nodes.</strong><br /><br />ODS and EDW- Level 1 (40%+40%=80%)<br />All other(EDWSTG and any other) - Level 2 (100% - that's nothing but overall 20%)<br />Note: IORM have 8 levels like DBRM.<br /><br /><br /><strong>-- To create IORM, need to create the IORMPLAN on each cell node</strong><br />CellCLI> alter iormplan dbplan=((name='ODS', level=1, allocation=40),(name='EDW', level=1, allocation=40),(name=other,level=2,allocation=100))<br />IORMPLAN successfully altered<br /><br /><strong>-- How to Activate</strong><br />CellCLI> alter iormplan active<br /><br /><strong>-- How to Inactivate</strong><br />CellCLI> alter iormplan inactive<br /><br /><strong>-- Check if the IORM is Active?</strong><br />CellCLI> LIST IORMPLAN<br /> atl02cel02_IORMPLAN active<br /> <br /><strong>-- Check if current IORM plan in place?</strong><br />CellCLI> LIST IORMPLAN detail<br /> name: atl02cel01_IORMPLAN<br /> catPlan:<br /> dbPlan: name=ODS,level=1,allocation=40<br /> name=EDW,level=1,allocation=40<br /> name=other,level=2,allocation=100<br /> status: active<br /><br /><br /><strong>b)<br />-- Monitoring IORM with cellcli command.</strong><br /><br /><strong>-- How to check Small IO load per database</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM<br /> DB_IO_RQ_SM EDW 7,387,543 IO requests<br /> DB_IO_RQ_SM ODS 5,262,853 IO requests<br /> DB_IO_RQ_SM EDWSTG 11,526,325 IO requests<br /><br /><strong>-- How to check Large IO load per database</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG<br /> DB_IO_RQ_LG EDW 13,210,476 IO requests<br /> DB_IO_RQ_LG ODS 5,865,946 IO requests<br /> DB_IO_RQ_LG EDWSTG 4,644,494 IO requests<br /><br /><br /><strong>-- How to check Small IO load/sec. per database, last minute</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM_SEC<br /> DB_IO_RQ_SM_SEC EDW 14.5 IO/sec<br /> DB_IO_RQ_SM_SEC ODS 86.5 IO/sec<br /> DB_IO_RQ_SM_SEC EDWSTG 135 IO/sec<br /><br /><strong>-- How to check Large IO load/sec. per database, last minute</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG_SEC<br /> DB_IO_RQ_LG_SEC EDW 54.4 IO/sec<br /> DB_IO_RQ_LG_SEC ODS 47.0 IO/sec<br /> DB_IO_RQ_LG_SEC EDWSTG 73.5 IO/sec<br /><br /><br /><strong>-- How to check Small IO Waits per database. per min.</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_WT_SM<br /> DB_IO_WT_SM EDW 35,576,746 ms<br /> DB_IO_WT_SM ODS 5,686,954 ms<br /> DB_IO_WT_SM EDWSTG 7,236,957 ms<br /><br /><strong>-- How to check Large IO Waits per database. per min.</strong><br />CellCLI> LIST METRICCURRENT where name=DB_IO_WT_LG<br /> DB_IO_WT_LG EDW 2,284,982,657 ms<br /> DB_IO_WT_LG ODS 384,891,454 ms<br /> DB_IO_WT_LG EDWSTG 394,093,606 msVijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com2tag:blogger.com,1999:blog-4161953958134414599.post-7416919512927155262011-08-09T22:23:00.002-04:002011-08-09T22:37:56.476-04:00How to find a SQL or session is running on which cell node and what it is doing?<strong>How to find a SQL or session is running on which cell node and what it is doing?</strong>
<br />
<br /><strong>-- Active SQL from session ID: 1271:</strong>
<br />UPDATE etl_job_control
<br /> SET start_dt = (SELECT end_dt
<br /> FROM etl_job_control
<br /> WHERE job_name = 's_m_network_stg'),
<br /> end_dt =
<br /> (SELECT DATE '1970-01-01' + ((SELECT MAX (mod_t)
<br /> FROM pin_user.event) / 24 / 60 / 60)
<br /> FROM DUAL),
<br /> last_update_dt = SYSDATE
<br /> WHERE job_name = :jn;
<br />
<br />
<br />-- Wait event on the session
<br />atl02db01:ODS1\sys: SQL> select event from gv$session where sid = 1271 and serial# = 31026;
<br />
<br />EVENT
<br />--------------------------------
<br />cell multiblock physical read
<br />
<br />
<br /><strong>-- Let's find the details from cell node</strong>
<br />LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail <strong>-- It looks like session runs on one cell node at any given time to get the data from the grid disk, as shown below session moved from cell 9 to 10.</strong>
<br />
<br />atl02cel09:
<br />CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
<br /> name: 5382
<br /> asmDiskGroupNumber: 3
<br /> asmFileIncarnation: 739372987
<br /> asmFileNumber: 2518
<br /> consumerGroupID: 4866
<br /> consumerGroupName: OTHER_GROUPS
<br /> dbID: 3108331340
<br /> dbName: ODS
<br /> dbRequestID: 5382
<br /> fileType: Datafile
<br /> id: 5382
<br /> instanceNumber: 1
<br /> ioBytes: 524288
<br /> ioBytesSofar: 0
<br /> ioGridDisk: DATA_CD_10_atl02cel09
<br /> ioOffset: 790293632
<br /> ioReason: "BufferCache Read"
<br /> ioType: CacheGet
<br /> objectNumber: 8104218
<br /> parentID: 5382
<br /> requestState: "Queued for Disk Read"
<br /> sessionID: 1271
<br /> sessionSerNumber: 31026
<br /> sqlID: 7p843mkscna11
<br /> tableSpaceNumber: 197
<br />
<br />atl02cel10:
<br />CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
<br /> name: 2301
<br /> asmDiskGroupNumber: 3
<br /> asmFileIncarnation: 731923539
<br /> asmFileNumber: 2059
<br /> consumerGroupID: 4866
<br /> consumerGroupName: OTHER_GROUPS
<br /> dbID: 3108331340
<br /> dbName: ODS
<br /> dbRequestID: 2301
<br /> fileType: Datafile
<br /> id: 2301
<br /> instanceNumber: 1
<br /> ioBytes: 376832
<br /> ioBytesSofar: 0
<br /> ioGridDisk: DATA_CD_01_atl02cel10
<br /> ioOffset: 669064480
<br /> ioReason: "BufferCache Read"
<br /> ioType: CacheGet
<br /> objectNumber: 8104222
<br /> parentID: 2301
<br /> requestState: "Queued for Disk Read"
<br /> sessionID: 1271
<br /> sessionSerNumber: 31026
<br /> sqlID: 7p843mkscna11
<br /> tableSpaceNumber: 197
<br />
<br /><strong>-- Want to see all the active request from instance 1 for database ODS</strong>
<br />CellCLI> LIST ACTIVEREQUEST where instanceNumber=1 and dbName=ODS
<br />
<br />Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-35987433606999345822011-07-31T21:23:00.005-04:002012-05-17T20:07:54.050-04:00Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's<strong>Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's</strong><br /><br /><strong>a).<br />-- Exadata Smart Scan(cell offload processing)</strong><br /><br />Exadata Smart Scan processes queries at the storage layer, returning only relevant rows and columns to the database server. <br />As a result, much less data travels over fast 40GB Infiniband interconnect, dramatically improving the performance and concurrency of simple and complex queries.<br /><br /><br /><strong>-- Which predicates are supported by a cell? What happens when predicates contain functions or expressions?</strong><br /><br /> a) Numeric functions: they can all be offloaded with a single exception, the <br /> WIDTH_BUCKET function. For example the predicate "width_bucket(n,1,10,100) = 1" <br /> is not offloaded.<br /><br /> b) Character functions returning character values: they can all be offloaded.<br /> <br /> c) Character functions returning number values: they can all be offloaded.<br /> <br /> d) Datetime functions: <br /> DATE datatype is supported.<br /> When a TIMESTAMP datatype is involved, offloading almost never happens.<br /> While predicates like "d = sysdate" (note that "d" is a column of DATE datatype) <br /> and "add_months(d,1) = to_date('01-01-2010','DD-MM-YYYY')" can be offloaded, <br /> something like "add_months(d,1) = sysdate" cannot.<br /> Every datetime function can be offloaded provided that it is not used along with <br /> SYSDATE or CURRENT_DATE.<br /><br />Note: There is no synchronization between the db layer and the storage layer for the purpose of offloading datetime functions. Whatever they would do, it is simply impossible to have exactly synchronized timers. Hence, inconsistent results might be generated. Therefore, IMO, the date/time for stuff like SYSDATE must be provided by the instance where a SQL statement is started.<br /><br /> months_between(d,sysdate) = 0<br /> months_between(d,current_date) = 0<br /> months_between(d,to_date('01-01-2010','DD-MM-YYYY')) = 0<br /><br /> e) NULL-related functions: they can all be offloaded.<br /><br /> <strong>-- List of the functions supporting offloading, is available through the V$SQLFN_METADATA view.</strong><br /> SELECT offloadable, count(DISTINCT name)<br /> FROM v$sqlfn_metadata<br /> GROUP BY offloadable;<br /><br /> <strong>-- Smart Scan Disablers</strong><br /> IOT<br /> Clustered Tables<br /> LOB<br /> Rowdependencies Enabled<br /><br /><strong>-- You can use the below query to see the "cell physical IO interconnect bytes returned by smart scan" in bytes</strong><br />SELECT a.NAME, b.VALUE<br /> FROM v$sysstat a, v$mystat b<br /> WHERE a.statistic# = b.statistic#<br /> AND ( a.NAME IN <br /> ('physical read total bytes', 'physical write total bytes',<br /> 'cell IO uncompressed bytes')<br /> OR a.NAME LIKE 'cell physical%'<br /> );<br /><br /><br /><strong>b).<br />-- How to create and drop Exadata Smart Flash Cache?</strong><br /><br /><strong>-- Drop flashcache (To flush Exadata Smart Flash Cache).</strong><br />CellCLI> DROP FLASHCACHE<br /><br /><strong>-- Create flashcache on all disks.</strong><br />CellCLI> CREATE FLASHCACHE ALL<br /><br /><strong>-- Flashcache cell disk and size deatils.</strong><br />CellCLI> LIST FLASHCACHE detail<br /> name: atlcel01_FLASHCACHE<br /> cellDisk: FD_10_atlcel01,FD_00_atlcel01,FD_04_atlcel01,FD_14_atlcel01,FD_15_atlcel01,FD_02_atlcel01,FD_08_atlcel01,FD_07_atlcel01,FD_05_atlcel01,FD_09_atlcel01,FD_13_atlcel01,FD_03_atlcel01,FD_12_atlcel01,FD_01_atlcel01,FD_11_atlcel01,FD_06_atlcel01<br /> creationTime: 2011-03-27T03:18:23-04:00<br /> degradedCelldisks:<br /> effectiveCacheSize: 365.25G<br /> id: 40ef10f1-8c82-4419-b5a1-9d51706813fe<br /> size: 365.25G<br /> status: normal<br /><br /><br /><strong>-- To determine whether an object is currently being kept in the Exadata Smart Flash Cache</strong><br /><br /><strong>-- Get the object id</strong><br />atldb01:ODS1\sys: SQL> select object_id, object_type from dba_objects where object_id = 8104207;<br /><br /> OBJECT_ID OBJECT_TYPE<br />---------- -------------------<br /> 8104207 TABLE PARTITION<br /><br /><br /><strong>-- Look at the segment statistics and get the DATAOBJ#</strong><br />atldb01:ODS1\sys: SQL> select distinct inst_id, owner, object_name, ts#, obj#, dataobj# from gv$segment_statistics<br /> 2 where owner = 'PIN_USER'<br /> 3 and object_name = 'EVENT';<br /><br /> INST_ID OWNER OBJECT_NAME TS# OBJ# DATAOBJ#<br />---------- ------------------------------ ------------------------------ ---------- ---------- ----------<br /> 2 PIN_USER EVENT 197 8104207 8104207<br /> 1 PIN_USER EVENT 197 8104202 8104202<br /> 3 PIN_USER EVENT 197 8104201 8104201<br /> 2 PIN_USER EVENT 197 8104218 8104218<br /> 2 PIN_USER EVENT 197 8104221 8104221<br /> 2 PIN_USER EVENT 197 8104212 8104212<br /> 2 PIN_USER EVENT 197 8104215 8104215<br /> 2 PIN_USER EVENT 197 8104227 8104227<br /> 1 PIN_USER EVENT 197 8104218 8104218<br /> 1 PIN_USER EVENT 197 8104212 8104212<br /> 1 PIN_USER EVENT 197 8104205 8104205<br /><br /><br /><strong>-- List the object details from Flash Cache.</strong><br />CellCLI> LIST FLASHCACHECONTENT where objectNumber=8104207 and tableSpaceNumber=197 and dbUniqueName=ODS detail<br /> cachedKeepSize: 0<br /> cachedSize: 4194304<br /> dbID: 3108331340<br /> dbUniqueName: ODS<br /> hitCount: 5972<br /> missCount: 8820<br /> objectNumber: 8104207<br /> tableSpaceNumber: 197<br /><br /><strong>Note: DBA can cache an object by setting CELL_FLASH_CACHE setting to KEEP. The default value of this setting is DEFAULT.</strong><br /><br /><br />When you are accessing the object data from the Flash Cache, you should see high number of optimized request("physical read requests optimized" and "cell flash cache read hits") than the high number of I/O requests("physical read total IO requests").<br /><br /><strong>-- You can use the below query to see the "flash cache read hits" in bytes.</strong><br />SELECT a.NAME, b.VALUE<br /> FROM v$sysstat a, v$mystat b<br /> WHERE a.statistic# = b.statistic#<br /> AND ( a.NAME LIKE '%flash cache read hits'<br /> OR a.NAME LIKE 'cell physical%'<br /> OR a.NAME LIKE 'physical read tot%'<br /> OR a.NAME LIKE 'physical read req%'<br /> );<br /><br /><br /><strong>c).<br />-- Storage Index Capability's</strong><br /><br />Storage Indexes are memory structures which do not persist when the Exadata cells are restarted. They are dynamically built when tables are referenced for the first time after the cells restart.<br /><br /><br /><strong>-- You can use the below query to see the savings of Storage Index in MB.</strong><br />SELECT a.NAME, b.VALUE / 1024 / 1024 value_in_mb<br /> FROM v$sysstat a, v$mystat b<br /> WHERE a.statistic# = b.statistic#<br /> AND ( a.NAME IN<br /> ('physical read total bytes', 'physical write total bytes',<br /> 'cell IO uncompressed bytes')<br /> OR a.NAME LIKE 'cell physical%'<br /> );<br /><br />Value of "cell physical IO bytes saved by storage index" shows the savings of the physical I/O by storage index and the total Physical I/O is recorded under "physical read total bytes".<br /><br />Queries that benefit from storage index can execute more quickly using fewer resources which allows other workloads to use the unused I/O resources.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com8tag:blogger.com,1999:blog-4161953958134414599.post-25214385802725621062011-07-10T17:27:00.008-04:002011-08-03T16:59:29.149-04:00How to setup notifications on cell node:<strong>How to setup notifications on cell node:</strong><br /><br /><br /><strong>-- Setting up notification on a cell</strong><br />CellCLI> ALTER CELL smtpServer='smtpmail.corp.dwire.com', -<br /> smtpFromAddr='OracleSupport@dwire.com', -<br /> smtpFrom='DWIRE Oracle Support', -<br /> smtpToAddr='OracleSupport@dwire.com,Vijay.Dumpa@dwire.com', -<br /> notificationPolicy='critical,warning,clear', -<br /> notificationMethod='mail,snmp'<br /><br /> <br /><strong>-- List the notification details </strong><br />CellCLI> list cell detail<br /> name: atl02cel01<br /> bmcType: IPMI<br /> cellVersion: OSS_11.2.1.2.6_LINUX.X64_100511<br /> cpuCount: 16<br /> fanCount: 12/12<br /> fanStatus: normal<br /> id: 1009XFG023<br /> interconnectCount: 3<br /> interconnect1: bond0<br /> iormBoost: 0.0<br /> ipaddress1: 192.168.10.1/22<br /> kernelVersion: 2.6.18-128.1.16.0.1.el5<br /> makeModel: SUN MICROSYSTEMS SUN FIRE X4275 SERVER SAS<br /> metricHistoryDays: 7<br /> notificationMethod: mail,snmp<br /> notificationPolicy: critical,warning,clear<br /> offloadEfficiency: 3.6M<br /> powerCount: 2/2<br /> powerStatus: normal<br /> smtpFrom: "DWIRE Oracle Support"<br /> smtpFromAddr: OracleSupport@dwire.com<br /> smtpServer: smtpmail.corp.dwire.com<br /> smtpToAddr: OracleSupport@dwire.com,Vijay.Dumpa@dwire.com<br /> snmpSubscriber: host=catl0sm41,port=162,community=celadmin<br /> host=catl0sm58,port=162,community=celadmin<br /> host=catl0sm59,port=162,community=celadmin<br /> host=catl0sm157,port=162,community=celadmin<br /> status: online<br /> temperatureReading: 25.0<br /> temperatureStatus: normal<br /> upTime: 46 days, 19:18<br /> cellsrvStatus: running<br /> msStatus: running<br /> rsStatus: running<br /><br />CellCLI> exit<br />quitting<br /><br /><br />CellCLI> ALTER CELL VALIDATE mail <strong>-- A test message is sent using mail configuration.</strong><br />CellCLI> ALTER CELL VALIDATE mail<br />Cell atl02cel01 successfully altered<br /><br /><strong>-- Test email content, received in the outlook</strong><br />Subject: CELL atl02cel01 Test Message<br />This test e-mail message from Oracle Cell atl02cel01 indicates successful configuration of your e-mail address and mail server. <br /><br /><br />CellCLI> ALTER CELL VALIDATE configuration <strong>-- To verify firmware configuration. (Takes a minute to return)</strong><br />CellCLI> ALTER CELL VALIDATE configuration<br />Cell atl02cel01 successfully altered<br /><br /><br /><strong>Note: Need to execute on each cell node.</strong><br /><br /><strong>-- How to disable the notification alert?</strong><br />cellcli -e alter cell notificationMethod=null<br /><br /><strong>-- How to enable the notification alert back?</strong><br />cellcli -e alter cell notificationMethod='mail,snmp'Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-83108658738288207192011-07-10T16:51:00.006-04:002011-07-20T23:17:04.267-04:00Exadata Disk creation and Management:<strong>Exadata Disk creation and Management:</strong><br /><br /><strong>Exadata Storage Layout:<br />PHYSICAL DISK -> LUN -> CELL DISK -> GRID DISK -> ASM DISK</strong><br /><br /><strong>a). </strong><br /><strong>-- List LUN and PHYSICAL DISK.</strong><br />CellCLI> LIST LUN <strong>-- List of all LUN's on a cell.</strong><br />CellCLI> LIST LUN<br /> 0_0 0_0 normal<br /> 0_1 0_1 normal<br /> 0_2 0_2 normal<br /> 0_3 0_3 normal<br /> 0_4 0_4 normal<br /> 0_5 0_5 normal<br /> 0_6 0_6 normal<br /> 0_7 0_7 normal<br /> 0_8 0_8 normal<br /> 0_9 0_9 normal<br /> 0_10 0_10 normal<br /> 0_11 0_11 normal<br /> 1_0 1_0 normal<br /> 1_1 1_1 normal<br /> 1_2 1_2 normal<br /> 1_3 1_3 normal<br /> 2_0 2_0 normal<br /> 2_1 2_1 normal<br /> 2_2 2_2 normal<br /> 2_3 2_3 normal<br /> 4_0 4_0 normal<br /> 4_1 4_1 normal<br /> 4_2 4_2 normal<br /> 4_3 4_3 normal<br /> 5_0 5_0 normal<br /> 5_1 5_1 normal<br /> 5_2 5_2 normal<br /> 5_3 5_3 normal<br /><br /><br />CellCLI> LIST LUN where disktype = harddisk <strong>-- List all the hard disks on a cell.</strong><br />CellCLI> LIST LUN where disktype = harddisk<br /> 0_0 0_0 normal<br /> 0_1 0_1 normal<br /> 0_2 0_2 normal<br /> 0_3 0_3 normal<br /> 0_4 0_4 normal<br /> 0_5 0_5 normal<br /> 0_6 0_6 normal<br /> 0_7 0_7 normal<br /> 0_8 0_8 normal<br /> 0_9 0_9 normal<br /> 0_10 0_10 normal<br /> 0_11 0_11 normal<br /><br />CellCLI> LIST LUN where disktype = flashdisk <strong>-- List all the flash disks on a cell.</strong><br />CellCLI> LIST LUN where disktype = flashdisk<br /> 1_0 1_0 normal<br /> 1_1 1_1 normal<br /> 1_2 1_2 normal<br /> 1_3 1_3 normal<br /> 2_0 2_0 normal<br /> 2_1 2_1 normal<br /> 2_2 2_2 normal<br /> 2_3 2_3 normal<br /> 4_0 4_0 normal<br /> 4_1 4_1 normal<br /> 4_2 4_2 normal<br /> 4_3 4_3 normal<br /> 5_0 5_0 normal<br /> 5_1 5_1 normal<br /> 5_2 5_2 normal<br /> 5_3 5_3 normal<br /><br /><br />CellCLI> LIST LUN where celldisk = null <strong>-- List all the LUN's not associated with a cell disk.</strong><br />CellCLI> LIST LUN where celldisk = null<br /><br /><br />CellCLI> LIST LUN where name = 0_7 detail <strong>-- Check isSystemLun=FALSE, this indicates that LUN is not located on a system disk.</strong><br />CellCLI> LIST LUN where name = 0_7 detail<br /> name: 0_7<br /> cellDisk: CD_07_atl02cel01<br /> deviceName: /dev/sdh<br /> diskType: HardDisk<br /> id: 0_7<br /> isSystemLun: FALSE<br /> lunAutoCreate: FALSE<br /> lunSize: 1861.712890625G<br /> lunUID: 0_7<br /> physicalDrives: 24:7<br /> raidLevel: 0<br /> lunWriteCacheMode: "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"<br /> status: normal<br /><br /><br />CellCLI> LIST PHYSICALDISK where name = 24:7 detail <strong>-- List the physical disk attributes (pass the "physicalDrives" name)</strong><br />CellCLI> LIST PHYSICALDISK where name = 24:7 detail<br /> name: 24:7<br /> deviceId: 23<br /> diskType: HardDisk<br /> enclosureDeviceId: 24<br /> errMediaCount: 0<br /> errOtherCount: 0<br /> foreignState: false<br /> luns: 0_7<br /> makeModel: "HITACHI H7220AA30SUN2.0T"<br /> physicalFirmware: JKAOA28A<br /> physicalInsertTime: 2010-07-13T21:22:00-04:00<br /> physicalInterface: sata<br /> physicalSerial: JK1130YAHBXL2T<br /> physicalSize: 1862.6559999994934G<br /> slotNumber: 7<br /> status: normal<br /><br /><br /><strong>b).</strong><br /><strong>-- How to create Cell disk?</strong><br /><br />CellCLI> LIST CELLDISK <strong>-- List all the cell disks on a cell node.</strong><br />CellCLI> LIST CELLDISK<br /> CD_00_atl02cel01 normal<br /> CD_01_atl02cel01 normal<br /> CD_02_atl02cel01 normal<br /> CD_03_atl02cel01 normal<br /> CD_04_atl02cel01 normal<br /> CD_05_atl02cel01 normal<br /> CD_06_atl02cel01 normal<br /> CD_07_atl02cel01 normal<br /> CD_08_atl02cel01 normal<br /> CD_09_atl02cel01 normal<br /> CD_10_atl02cel01 normal<br /> CD_11_atl02cel01 normal<br /> FD_00_atl02cel01 normal<br /> FD_01_atl02cel01 normal<br /> FD_02_atl02cel01 normal<br /> FD_03_atl02cel01 normal<br /> FD_04_atl02cel01 normal<br /> FD_05_atl02cel01 normal<br /> FD_06_atl02cel01 normal<br /> FD_07_atl02cel01 normal<br /> FD_08_atl02cel01 normal<br /> FD_09_atl02cel01 normal<br /> FD_10_atl02cel01 normal<br /> FD_11_atl02cel01 normal<br /> FD_12_atl02cel01 normal<br /> FD_13_atl02cel01 normal<br /> FD_14_atl02cel01 normal<br /> FD_15_atl02cel01 normal<br /><br /><strong>-- Hard Disk</strong><br />CellCLI> LIST CELLDISK where name = CD_07_atl02cel01 detail<br /> name: CD_07_atl02cel01<br /> comment:<br /> creationTime: 2010-07-14T19:56:09-04:00<br /> deviceName: /dev/sdh<br /> devicePartition: /dev/sdh<br /> diskType: HardDisk<br /> errorCount: 0<br /> freeSpace: 560M<br /> freeSpaceMap: offset=1832.046875G,size=560M<br /> id: 00000129-d363-0901-0000-000000000000<br /> interleaving: none<br /> lun: 0_7<br /> raidLevel: 0<br /> size: 1861.703125G<br /> status: normal<br /><br /><strong>CellCLI> CREATE CELLDISK CD_07_atl02cel01 LUN='0_7' HARDDISK</strong><br /><br /><br /><strong>-- Flash Disk</strong><br />CellCLI> LIST CELLDISK where name = FD_00_atl02cel01 detail<br /> name: FD_00_atl02cel01<br /> comment:<br /> creationTime: 2011-03-27T03:17:59-04:00<br /> deviceName: /dev/sdq<br /> devicePartition: /dev/sdq<br /> diskType: FlashDisk<br /> errorCount: 0<br /> freeSpace: 0<br /> id: caf0be8d-4061-451a-a9da-090945a9c8d5<br /> interleaving: none<br /> lun: 1_0<br /> size: 22.875G<br /> status: normal<br /><br /><strong>CellCLI> CREATE CELLDISK FD_00_atl02cel01 LUN='1_0' FLASHDISK</strong><br /><br /><br /><strong>c).</strong><br /><strong>-- How to create Grid disk?</strong><br /><strong>All the grid disks are created on cell disks (HARDDISK, not FLASHDISK).</strong><br /><br />CellCLI> LIST GRIDDISK <strong>-- List all the grid disks on a cell node.</strong><br />CellCLI> LIST GRIDDISK<br /> DATA_CD_00_atl02cel01 active<br /> DATA_CD_01_atl02cel01 active<br /> DATA_CD_02_atl02cel01 active<br /> DATA_CD_03_atl02cel01 active<br /> DATA_CD_04_atl02cel01 active<br /> DATA_CD_05_atl02cel01 active<br /> DATA_CD_06_atl02cel01 active<br /> DATA_CD_07_atl02cel01 active<br /> DATA_CD_08_atl02cel01 active<br /> DATA_CD_09_atl02cel01 active<br /> DATA_CD_10_atl02cel01 active<br /> DATA_CD_11_atl02cel01 active<br /> RECO_CD_00_atl02cel01 active<br /> RECO_CD_01_atl02cel01 active<br /> RECO_CD_02_atl02cel01 active<br /> RECO_CD_03_atl02cel01 active<br /> RECO_CD_04_atl02cel01 active<br /> RECO_CD_05_atl02cel01 active<br /> RECO_CD_06_atl02cel01 active<br /> RECO_CD_07_atl02cel01 active<br /> RECO_CD_08_atl02cel01 active<br /> RECO_CD_09_atl02cel01 active<br /> RECO_CD_10_atl02cel01 active<br /> RECO_CD_11_atl02cel01 active<br /> SYSTEMDG_CD_02_atl02cel01 active<br /> SYSTEMDG_CD_03_atl02cel01 active<br /> SYSTEMDG_CD_04_atl02cel01 active<br /> SYSTEMDG_CD_05_atl02cel01 active<br /> SYSTEMDG_CD_06_atl02cel01 active<br /> SYSTEMDG_CD_07_atl02cel01 active<br /> SYSTEMDG_CD_08_atl02cel01 active<br /> SYSTEMDG_CD_09_atl02cel01 active<br /> SYSTEMDG_CD_10_atl02cel01 active<br /> SYSTEMDG_CD_11_atl02cel01 active<br /><br />CellCLI> LIST GRIDDISK where celldisk=CD_07_atl02cel01 detail <strong>-- List the different grid disks names on one celldisk and the OFFSET is starting point on the physical disk.</strong><br />CellCLI> LIST GRIDDISK where celldisk=CD_07_atl02cel01 detail<br /> name: DATA_CD_07_atl02cel01<br /> availableTo:<br /> cellDisk: CD_07_atl02cel01<br /> comment:<br /> creationTime: 2010-07-14T19:58:53-04:00<br /> diskType: HardDisk<br /> errorCount: 0<br /> id: 00000129-d365-88f0-0000-000000000000<br /> offset: 32M<br /> size: 1582G<br /> status: active<br /><br /> name: RECO_CD_07_atl02cel01<br /> availableTo:<br /> cellDisk: CD_07_atl02cel01<br /> comment:<br /> creationTime: 2010-08-20T12:27:25-04:00<br /> diskType: HardDisk<br /> errorCount: 0<br /> id: 0000012a-9053-7f16-0000-000000000000<br /> offset: 1582G<br /> size: 250G<br /> status: active<br /><br /> name: SYSTEMDG_CD_07_atl02cel01<br /> availableTo:<br /> cellDisk: CD_07_atl02cel01<br /> comment:<br /> creationTime: 2010-07-14T19:56:50-04:00<br /> diskType: HardDisk<br /> errorCount: 0<br /> id: 00000129-d363-a6e5-0000-000000000000<br /> offset: 1832.59375G<br /> size: 29.109375G<br /> status: active<br /><br /><strong>CellCLI> CREATE GRIDDISK DATA_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=1582G,offset=32M -- Where "offset" is starting point on the disk.<br />CellCLI> CREATE GRIDDISK RECO_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=250G,offset=1582G<br />CellCLI> CREATE GRIDDISK SYSTEMDG_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=29.109375G,offset=1832.59375G</strong><br /><br /><br /><strong>d).</strong><br /><strong>-- How to create ASM disk on Exadata?</strong><br /><br />CellCLI> LIST GRIDDISK attributes name, size, asmmodestatus where asmmodestatus='UNUSED' <strong>-- To identify all the unused grid disks to create ASM disks.</strong><br /><br />asmca<br />(or)<br />. oraenv<br />+ASM1<br /><br />sqlplus / as sysasm<br /><br />set linesize 200<br />col path for a50<br /><br /><strong>-- No CANDIDATE disks.</strong><br />SQL> select name, header_status, path from v$asm_disk<br /> 2 where header_status <> 'MEMBER';<br /><br /><strong>no rows selected</strong><br /><br /><strong>-- Print Existing disk name and the paths.</strong><br />SQL> select * from (select name, header_status, path from v$asm_disk order by name)<br /> 2 where rownum < 16;<br /><br />NAME HEADER_STATU PATH<br />------------------------- ------------ --------------------------------------------------<br />DATA_CD_00_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_00_atl02cel01<br />DATA_CD_00_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_00_atl02cel02<br />DATA_CD_00_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_00_atl02cel03<br />DATA_CD_00_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_00_atl02cel04<br />DATA_CD_00_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_00_atl02cel05<br />DATA_CD_00_ATL02CEL06 MEMBER o/192.168.10.6/DATA_CD_00_atl02cel06<br />DATA_CD_00_ATL02CEL07 MEMBER o/192.168.10.7/DATA_CD_00_atl02cel07<br />DATA_CD_00_ATL02CEL08 MEMBER o/192.168.10.8/DATA_CD_00_atl02cel08<br />DATA_CD_00_ATL02CEL09 MEMBER o/192.168.10.9/DATA_CD_00_atl02cel09<br />DATA_CD_00_ATL02CEL10 MEMBER o/192.168.10.10/DATA_CD_00_atl02cel10<br />DATA_CD_01_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_01_atl02cel01<br />DATA_CD_01_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_01_atl02cel02<br />DATA_CD_01_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_01_atl02cel03<br />DATA_CD_01_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_01_atl02cel04<br />DATA_CD_01_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_01_atl02cel05<br /><br />15 rows selected.<br /><br />col failgroup_type for a15;<br />SQL> select * from (select name, header_status, path, failgroup, failgroup_type, sector_size from v$asm_disk order by name)<br /> 2 where rownum < 16;<br /><br />NAME HEADER_STATU PATH FAILGROUP FAILGROUP_TYPE SECTOR_SIZE<br />------------------------- ------------ --------------------------------------------- ----------- ------------------ -------------<br />DATA_CD_00_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_00_atl02cel01 ATL02CEL01 REGULAR 512<br />DATA_CD_00_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_00_atl02cel02 ATL02CEL02 REGULAR 512<br />DATA_CD_00_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_00_atl02cel03 ATL02CEL03 REGULAR 512<br />DATA_CD_00_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_00_atl02cel04 ATL02CEL04 REGULAR 512<br />DATA_CD_00_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_00_atl02cel05 ATL02CEL05 REGULAR 512<br />DATA_CD_00_ATL02CEL06 MEMBER o/192.168.10.6/DATA_CD_00_atl02cel06 ATL02CEL06 REGULAR 512<br />DATA_CD_00_ATL02CEL07 MEMBER o/192.168.10.7/DATA_CD_00_atl02cel07 ATL02CEL07 REGULAR 512<br />DATA_CD_00_ATL02CEL08 MEMBER o/192.168.10.8/DATA_CD_00_atl02cel08 ATL02CEL08 REGULAR 512<br />DATA_CD_00_ATL02CEL09 MEMBER o/192.168.10.9/DATA_CD_00_atl02cel09 ATL02CEL09 REGULAR 512<br />DATA_CD_00_ATL02CEL10 MEMBER o/192.168.10.10/DATA_CD_00_atl02cel10 ATL02CEL10 REGULAR 512<br />DATA_CD_01_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_01_atl02cel01 ATL02CEL01 REGULAR 512<br />DATA_CD_01_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_01_atl02cel02 ATL02CEL02 REGULAR 512<br />DATA_CD_01_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_01_atl02cel03 ATL02CEL03 REGULAR 512<br />DATA_CD_01_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_01_atl02cel04 ATL02CEL04 REGULAR 512<br />DATA_CD_01_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_01_atl02cel05 ATL02CEL05 REGULAR 512<br /><br />15 rows selected.<br /><br /><br />col name for a10<br />col compatibility for a10<br />col database_compatibility for a22<br />SQL> select name, allocation_unit_size, block_size, compatibility, database_compatibility from v$asm_diskgroup;<br /><br />NAME ALLOCATION_UNIT_SIZE BLOCK_SIZE COMPATIBIL DATABASE_COMPATIBILITY<br />---------- -------------------- ---------- ---------- ----------------------<br />SYSTEMDG 4194304 4096 11.2.0.0.0 11.2.0.0.0<br />DATA 4194304 4096 11.2.0.0.0 11.2.0.0.0<br />RECO 4194304 4096 11.2.0.0.0 11.2.0.0.0<br /><br /><br />. oraenv<br />+ASM1<br /><br />sqlplus / as sysasm<br /><br /><strong>-- Createing SYSTEMDG diskgroup:</strong><br />CREATE DISKGROUP SYSTEMDG NORMAL REDUNDANCY<br />DISK 'o/*/SYSTEMDG*'<br />ATTRIBUTE 'AU_SIZE' = '4M',<br /><strong>'cell.smart_scan_capable'='TRUE',</strong><br />'compatible.rdbms'='11.2.0.0',<br />'compatible.asm'='11.2.0.0';<br /><br /><strong>-- Createing DATA diskgroup</strong><br />CREATE DISKGROUP DATA NORMAL REDUNDANCY<br />DISK 'o/*/DATA*'<br />ATTRIBUTE 'AU_SIZE' = '4M',<br /><strong>'cell.smart_scan_capable'='TRUE',</strong><br />'compatible.rdbms'='11.2.0.0',<br />'compatible.asm'='11.2.0.0';<br /><br /><strong>-- Createing RECO diskgroup</strong><br />CREATE DISKGROUP RECO NORMAL REDUNDANCY<br />DISK 'o/*/RECO*'<br />ATTRIBUTE 'AU_SIZE' = '4M',<br /><strong>'cell.smart_scan_capable'='TRUE',</strong><br />'compatible.rdbms'='11.2.0.0',<br />'compatible.asm'='11.2.0.0';Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-71647197228081066662011-01-02T10:58:00.013-05:002011-02-23T23:29:18.001-05:00Exadata V2 Storage Server (Cell node) Architecture and Management:<strong>Exadata V2 Storage Server (Cell node) Architecture and Management:</strong><br /><br /><strong>Architecture Overview:</strong><br /><br /><strong>Storage Servers: </strong><br />The Exadata Storage Server is a SAN storage device specifically built for Oracle database use. Each holds 12 SAS or SATA disks (2 TB(SAS)/7 TB(SATA) total raw capacity), <br />dual Xeon CPUs, dual InfiniBand, and 384 GB of flash memory. Smart Scan support reduces the data that must travel over the InfiniBand network, <br />while Hybrid Columnar Compression reduces data footprint by 4 to 50 times, depending on the data and compression mode.<br /><br />- Each Exadata storage cell has 12 disks.<br />- One physical disk is called LUN and is also called cell disk.<br />- A grid disk is part of a cell disk.<br />- A disk group is made of many grid disks.<br /><br />- On the first two disks, the first 4 partitions (29GB) are reserved for system software. The two disks contain mirror copy. <br />- On the other 10 disks, 29GB can not be used. These 10 disks can be utilized by creating DBFS file system.<br />- So, Exdata creates a file system of 290GB called SYSTEMDG for FULL RAC cluster.<br />- SYSTEMDG contains OCR and Votedisk information.<br /><br /><strong>ExadataCell Disk Storage Capacity:</strong><br />. 12 x 600 GB SAS 15K rpm disks (7.2 TB/cell @ 100 TB total) - But actual size is about 558GB. A little bit of space is also kept aside for celldisk metadata ~48MB per disk.<br />. 12 x 2TB SATA disks (24 TB/cell @ 336 TB total)<br />. 4 x 96 GB Sun Flash Cards (384GB/cell @ total 5TB) - But when execute celci command 'list flashcache' returns 365.25. <br /> - A little bit of space is also kept aside for celldisk metadata on these cards too.<br /><br /><br /><strong>Exadata Disk groups:</strong><br />DATA (Data)<br />RECO (Redo logs, Archive logs and Flash Recovery Area)<br />SYSTEMDG (OCR and Votedisk)<br /><br /><br /><strong>Cell node unique feature:</strong><br />Smart Scans<br />Hybrid Columnar Compression<br />Storage Indexes<br />Flash Cache<br />ExadataI/O Resource Management in Multi-Database Environment<br /><br /><br /><strong>Background Processes in the Exadata Cell Environment on database server:</strong><br />The background processes for the database and Oracle ASM instance for an Exadata<br />Cell environment are the same as other environments, except for the following background process:<br /><br />- diskmon Process - The diskmon process is a fundamental component of Exadata Cell, and is responsible for implementing I/O fencing.<br /><br />- XDMG Process (Exadata Automation Manager)<br /> Its primary task is to watch for inaccessible disks and cells, and to detect when the disks and cells become accessible.<br /><br />- XDWK Process (Exadata Automation Worker)<br /> The XDWK process begins when asynchronous actions, such as ONLINE, DROP or ADD for an Oracle ASM disk are requested by the XDMG process.<br /> The XDWK process will stop after 5 minutes of inactivity.<br /><br /><strong>Output:</strong><br />> ps -ef | egrep "diskmon|xdmg|xdwk"<br />oracle 4684 4206 0 06:42 pts/1 00:00:00 egrep diskmon|xdmg|xdwk<br />oracle 10321 1 0 2010 ? 00:38:15 /u01/app/11.2.0/grid/bin/diskmon.bin -d -f<br />oracle 10858 1 0 2010 ? 00:00:18 asm_xdmg_+ASM1<br /><br /><br /><strong>As a departure from ASM storage technology that uses a process architecture borrowed from database instances, <br />the storage servers have a brand new set of processes to manage disk I/O. They are:</strong><br />- RS, the restart service. Performing a similar role to SMON, RS monitors other processes, and automatically restarts them if they fail unexpectedly. <br /> RS also handles planned restarts in conjunction with software updates. <br /> The main cellrssrm process spawns several helper processes, including cellrsbmt, cellrsbkm, cellrsomt, and cellrsssmt. <br /><br />- MS, the management service. MS is the back-end process that processes configuration and monitoring commands. It communicates with cellcli, described in the next section. <br /> MS is written in Java, unlike the other background processes which are distributed in binary form and are likely written in C. <br /><br />- CELLSRV, the cell service. CELLSRV handles the actual I/O processing of the storage server. <br /> It is not uncommon to see heavy usage from CELLSRV process threads during periods of heavy load. <br /> Among other things, CELLSRV provides: <br /> . Communication with database nodes using the iDB/RDS protocols over the InfiniBand network <br /> . Disk I/O with the underlying cell disks <br /> . Offload of SQL processing from database nodes <br /> . I/O resource management, prioritizing I/O requests based on a defined policy <br /><br />- I/O Resource Manager (IORM). Enables storage grid by prioritizing I/Os to ensure predictable performance<br /><br /><br /><br /><strong>Cell node Management Overview:</strong><br />DBA's login as OS user "celladmin" to manage cell nodes.<br />Each cell node internally run ASM instance to manage cell node disks. This means, you can't see the ASM pmon process on the cell node.<br /><br />Cell Admin. Tool's: cellcli and dcli.<br />Cell monitoring Tool's: OSWatcher, ORION (I/O performance benchmarking tool) and ADRCI<br /><br /><br /><strong>Cell Nodes Logs and Traces:</strong><br />$ADR_BASE/diag/asm/cell/`hostname`/trace/alert.log<br />$ADR_BASE/diag/asm/cell/`hostname`/trace/ms-odl.*<br />$ADR_BASE/diag/asm/cell/`hostname`/trace/svtrc_<pid>_0.trc -- ps -ef | grep "cellsrv 100"<br />$ADR_BASE/diag/asm/cell/`hostname`/incident/*<br /><br />/var/log/messages*, dmesg<br />/var/log/sa/*<br />/var/log/cellos/*<br /><br />cellcli -e list alerthistory <br /><br />$OSSCONF/cellinit.ora -- #CELL Initialization Parameters<br />$OSSCONF/cell_disk_config.xml<br />$OSSCONF/griddisk.owners.dat<br />$OSSCONF/cell_bootstrap.ora <br /><br />/opt/oracle/cell/cellsrv/deploy/log/cellcli.lst*<br /><br />$OSSCONF/alerts.xml <br />$OSSCONF/metrics/* <br />oswatcher data<br /><br />df -h -> check if /opt/oracle file system full? /opt/oracle only 2GB in size on cell node !!!<br /><br />Where :<br />$OSSCONF is: /opt/oracle/cell11.2.1.3.1_LINUX.X64_100818.1/cellsrv/deploy/config<br />$ADR_BASE is: /opt/oracle/cell11.2.1.3.1_LINUX.X64_100818.1/log<br /><br /><br /><strong>Cell Check and shutdown/startup commands:</strong><br />Note: For full list of commands use: cellcli -e help<br /><br />cellcli -e alter cell shutdown services all<br />cellcli -e alter cell startup services all<br />cellcli -e alter cell shutdown services cellsrv<br />cellcli -e alter cell restart services cellsrv<br />cellcli -e list lun detail<br />cellcli -e list griddisk detail<br />cellcli -e list celldisk detail<br />cellcli -e list physicaldisk detail<br />cellcli -e list flashcache detail<br />cellcli -e list physicaldisk attributes name, diskType, luns, status<br />cellcli -e list physicaldisk where disktype=harddisk attributes physicalfirmware<br />cellcli -e list lun attributes name, diskType, isSystemLun, status<br /><br />imagehistory (root/sudo)<br />imageinfo (root/sudo)<br />service celld status (root/sudo)<br />lsscsi | grep MARVELL <br /><br /><br /><strong>Smart scan layers:</strong><br />Smart scan involves multiple layers of code<br />KDS/KTR/KCBL - data layers in rdbms<br />KCFIS - smart scan layer in rdbms<br />Predicate Disk - smart scan layer in cellsrv<br />Storage index - IO avoidance optimization in cellsrv<br />Flash IO - IO layer in cellsrv to fetch data from flash cache<br />Block IO - IO layer in cellsrv to fetch data from hard-disks<br />FPLIB - filtering library in cellsrv<br /><br /><br /><strong>How to Isolate the Issue Whether or Not it's Exadata Related?</strong><br />Issue can be?<br />Wrong results when running the query on an Exadata DB -- I personally faced this issue.<br />Query is slower when running on an exadata database<br /><br /><strong>Is smart scan issue?</strong><br />Cell_offload_processing=false (default true)<br />If the problem does not occurs, it’s the smart scan issue.<br /><br /><strong>Is this a FPLIB issue?</strong><br />_kcfis_cell_passthru_enabled=true (default false)<br />If the problem does not occurs, it’s the FPLIB issue<br /><br /><strong>Is storage index issue?</strong><br />_kcfis_storageidx_disabled=true (default false)<br />Problem still occurs, it’s not a storage index issue.<br /><br /><strong>Is flash cache issue?</strong><br />For 11.2.0.2, _kcfis_keep_in_cellfc_enabled=false (default true) do not use flash cache <br />For 11.2.0.1, _kcfis_control1=1 (default 0)<br />Problem still occurs, it’s not a flash cache problem.<br /><br /><strong>Cell related Database view's:</strong><br />select * from sys.GV_$CELL_STATE;<br />select * from sys.GV_$CELL;<br />select * from sys.GV_$CELL_THREAD_HISTORY;<br />select * from sys.GV_$CELL_REQUEST_TOTALS;<br />select * from sys.GV_$CELL_CONFIG;<br /><br /><br /><strong>Bloom filter in Exadata:</strong><br />In Oracle 10g concept of bloom filtering was introduced.<br />When two tables are joined via a hash join, the first table (typically the smaller table) is scanned and the rows that satisfy the ‘where’ clause predicates (for that table) are used to create a hash table.<br />During the hash table creation a bit vector or bloom filter is also created based on the join column.<br />The bit vector is then sent as an additional predicate to the second table scan.<br />After the ‘where’ clause predicates have been applied to the second table scan, the resulting rows will have their join column hashed and it will be compared to values in the bit vector.<br />If a match is found in the bit vector that row will be sent to the hash join. If no match is found then the row will be disregarded.<br />On Exadata the bloom filter or bit vector is passed as an additional predicate so it will be overloaded to the storage cells making bloom filtering very efficient.<br /><br /><strong>How to Identify a Bloom Filter in an Execution plan:</strong><br />You can identify a bloom filter in a plan when you see :BF0000 in the Name column of the execution plan.<br /><br />To disable the feature, the initialization parameter _bloom_pruning_enabled must be set to FALSE.<br /><br /><br /><br /><strong>Just want to say few words about other Exadata components too:</strong><br /><br />The Sun Oracle Exadata Database Machine hardware consists of preconfigured Oracle Database servers connected to Sun Oracle Exadata Storage Servers <br />by an InfiniBand fabric. Each of these has been configured to take advantage of the latest advances in Oracle database technology.<br /><br /><strong>Database Servers: </strong><br />Industry-standard Oracle Database 11gR2 servers feature advanced software that makes the extreme performance of Exadata possible. <br />Automatic Storage Management (ASM) provides advanced storage management capabilities; the Database Resource Manager (DBRM) lets users <br />prioritize the resources available to each database; and the Intelligent Database protocol (IDB) allows Smart Scan offloading of database queries <br />to the Storage Servers, greatly reducing network overhead.<br /><br /><strong>InfiniBand switch: </strong><br />At 40 Gbit/s on each port, the InfiniBand network linking the Exadata database servers to the Storage Servers is ten times as fast as Fibre Channel, <br />with lower latency. Multipathing protects against network failures.<br /><br /><strong>Management switch:</strong><br />A single Cisco Catalyst 4948 48-port gigabit Ethernet switch handles management traffic.<br /><br /><strong>KVM and rack:</strong><br />One 32-port Avocent KVM switch with associated keyboard/mouse drawer provides console access to database servers and storage cells. <br />The switch is IP-enabled, meaning remote console access is available either via the individual system ILOM ports or the KVM switch.<br />All the components are housed in a 42U Sun 1242E rack with integrated zero-U power distribution units.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com8tag:blogger.com,1999:blog-4161953958134414599.post-76149347568073658332010-12-22T16:34:00.009-05:002011-08-10T21:54:08.290-04:00What makes Exadata different from regular RAC?:<strong>What makes Exadata different from regular RAC?</strong>
<br />We recently migrated 6 of our 10+ TB production databases to Exadata server.
<br />
<br /><strong>1. Smart scan:</strong>
<br />
<br />To disable smart scan set cell_offload_processing=FALSE (default TRUE) for that database.
<br />
<br /><strong>List of cell DB parameters:</strong>
<br />NAME TYPE VALUE
<br />------------------------------------ ----------- ------------------------------
<br />cell_offload_compaction string ADAPTIVE
<br />cell_offload_decryption boolean TRUE
<br />cell_offload_parameters string
<br />cell_offload_plan_display string AUTO
<br />cell_offload_processing boolean TRUE
<br />cell_partition_large_extents string DEFAULT
<br />
<br /><strong>2. Flash cache:</strong>
<br />
<br /> - Frequently accessed data and index are automatically cached.
<br /> - control file read and write are cached.
<br /> - File header read and writes are cached.
<br /> - DBA's can pin objects in flash cache.
<br />
<br /> - table scan data is not cached.
<br /> - backup data is not cached.
<br /> - DB pump data is not cached.
<br /> - data file formating data is not cached.
<br /> - IO to mirror copy is not cached.
<br />
<br /> - DBA can cache an object by setting CELL_FLASH_CACHE setting to KEEP. The default value of this setting is DEFAULT.
<br />
<br /><strong>3. Exadata Hybrid Columnar Compression:</strong>
<br />
<br /> EHCC is not pure columnar, it is hybrid columnar
<br />
<br />Multiple Levels of compression
<br />- Query Low
<br />- Query High
<br />- Archive Low
<br />- Archive High
<br />
<br /><strong>Algorithms:</strong>
<br /> LZO: Faster compression speeds, but lower ratios (Query Low)
<br /> ZLIB: Decent speeds and good ratios (Query High and Archive Low)
<br /> BZ2: Slow, but the highest ratios (Archive High)
<br />
<br /><strong>4. Exadata storage index:</strong>
<br />
<br /> Transparent I/O Elimination with No Overhead.
<br />
<br />- Exadata storage indexes maintain summary information about table data in memory.
<br /> * Store MIN and MAX values of columns.
<br /> * Typically one index entry for every MB of disk.
<br />- Eliminates disk I/O if MIN and MAX can never match "WHERE" clause of a query.
<br />
<br /><strong>5. DBFS (database file system) - Rest of the 10 SYSTEMDB disks can be used as DBFS.</strong>
<br />
<br /><strong>6. IORM (I/O resource manager - Exadata specific)</strong>
<br />
<br />
<br /><strong>Monitoring Exadata:</strong>
<br />
<br /> - Install GC agent on each compute node.
<br /> - No GC agent on cell nodes.
<br /> - Agent on one compute node talks with all cell nodes and collects data.
<br /> - Grid control plugin is installed in OMS.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-68376400772726272072010-06-22T17:29:00.002-04:002010-06-22T19:35:35.608-04:00Oracle masala at work - 3:<strong>Oracle masala at work - 3: </strong><br /><br />1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:<br />2. Restart Data Pump job after an error:<br />3. NFS mount options on Linux(RHEL) for expdp and impdp:<br />4. Useful Underutilized some Oracle Utilities:<br /><br /><br /><strong>1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:</strong><br /><br />rman target /<br /><br />-- If there is an error, this command will come back with the files to recover.<br />list failure;<br /><br />-- This command will show you the exact cause of the error. 169 is failure ID from the "list failure;" command.<br />list failure 169 detail;<br /><br />-- It responds with a detailed explanation of the error and how to correct it.<br />advise failure;<br /><br />-- It responds with a detailed fix preview.<br />repair failure preview;<br /><br />-- This command will fix the failure.<br />repair failure;<br /><br />-- The following RMAN command recovers all corrupted blocks.<br />recover corruption list;<br /><br /><br /><strong>2. Restart Data Pump job after an error:</strong><br /><br />-- In order to find the System assigned name for the EXPDP/IMPDP job you can run the following query.<br />SELECT * FROM DBA_DATAPUMP_JOBS;<br /><br />-- Attach the job from the above SQL.<br />$ impdp system/manager attach=job_name<br /><br />-- Restart the job.<br />Import> start_job<br /><br />-- Check the status of the job.<br />Import> status<br /><br /><br /><strong>3. NFS mount options on Linux(RHEL) for expdp and impdp:</strong><br /><br />mount options:<br />atlt200:/oracle_scratch on /oracle_scratchnfs type nfs (rw,rsize=32768,wsize=32768,hard,nointr,bg,nfsvers=3,tcp,actimeo=0,timeo=600,addr=xx.xx.xxx.xx)<br /><br />uname -a: Linux atlt200 2.6.9-78.ELsmp #1 SMP Wed Jul 9 15:46:26 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux<br />Server: Dell 2950<br /><br /><br /><strong>4. Useful Underutilized some Oracle Utilities:</strong><br /><br />bbed (Block Browser and Editor, Password is "blockedit")<br />oradebug<br />nid (Rename DBID and Database Name)<br />adrci (11g)<br />trcsess (trcsess utility allows trace information from multiple trace files to be identified and consolidated into a single trace file from 10g)<br />trace Analyzer (trcanlzr.sql - Trace Analyzer utility is available via download on the Oracle Metalink web site)<br />trcasst (Analyxing Listener Trace file)<br />deinstall (11gR2 - Deinstall Failed GRID or Oracle home’s - File is under $ORACLE_HOME/deinstall directory)<br />csscan (Character Set Migration Utility - $ csscan \"sys/keepsaf3 as sysdba\" FULL=Y, to create character set migration utility schema, run @?/rdbms/admincsminst.sql)<br />wrap (hide the PL/SQL code)<br />cemutlo (cemutlo -n gives the cluster name)<br /><br />renamedg (11gR2 ASM - Rename the ASM diskgroup)<br />kfed (ASM - display the diskgroup information)<br /><br /><br />Tools:<br />CHM (formerly know as IPD/OS) - Oracle Cluster Health Monitor.<br />ORION - Oracle I/O Calibration Tool - Load test tool.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com1tag:blogger.com,1999:blog-4161953958134414599.post-47826676617262741022010-06-22T16:46:00.001-04:002010-06-22T19:37:30.091-04:00Renaming ASM Disk group in Oracle 11gR2:<strong>Renaming ASM Disk group in Oracle 11gR2:</strong><br /><br /><br />The renamedg utility is new in Oracle 11gR2 and it's documented in the storage administrator’s guide. <br />You can use this tool to rename ASM diskgroups. The prerequisite is to unmount the disk group on all cluster nodes. <br />The tool works in 2 phases, in the first phase it generates a configuration file and in the second phase it discovers the disks and rename the disk group.<br /><br /><br /><br /><strong>How to use renamedg command to rename the ASM diskgroup?</strong><br /><br />The following example demonstrates how to rename the ASM diskgroup from A_DATA to B_DATA.<br /><br /><br /><strong>a. Setup the environment:</strong><br /><br />$ . oraenv<br />+ASM1<br /><br />$ export PATH=$PATH:/dev/oracleasm/disks<br />$ echo $PATH<br /><br /> <br /><strong>b. Dismount the ASM diskgroup to be renamed on all cluster nodes:</strong><br /><br />$ asmcmd umount A_DATA -- on all nodes.<br /><br /><br /><strong>c. Verify the diskgroup was dismounted:</strong><br /><br />$ crsctl status resource ora.A_DATA.dg<br /><br />NAME=ora.A_DATA.dg<br />TYPE=ora.diskgroup.type<br />TARGET=OFFLINE, OFFLINE<br />STATE=OFFLINE, OFFLINE<br /><br /><br /><strong>d. Using renamedg utility to rename the diskgroup:</strong><br /><br />$ renamedg phase=both dgname=A_DATA newdgname=B_DATA verbose=true <br /><br /><br /><strong>Output:</strong><br />----***********---- <br />Parsing parameters..<br /><br />Parameters in effect:<br /><br /> Old DG name : A_DATA<br /> New DG name : B_DATA<br /> Phases :<br /> Phase 1<br /> Phase 2<br /> Discovery str : (null)<br /> Clean : TRUE<br /> Raw only : TRUE<br />renamedg operation: phase=both dgname=A_DATA newdgname=B_DATA verbose=true<br />Executing phase 1<br />Discovering the group<br />Performing discovery with string:<br />Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)<br />Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)<br />Checking for hearbeat...<br />Re-discovering the group<br />Performing discovery with string:<br />Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)<br />Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)<br />Checking if the diskgroup is mounted<br />Checking disk number:0<br />Checking disk number:1<br />Checking if diskgroup is used by CSS<br />Generating configuration file..<br />Completed phase 1<br />Executing phase 2<br />Looking for ORCL:S_1873_0681<br />Modifying the header<br />Looking for ORCL:S_1873_0685<br />Modifying the header<br />Completed phase 2<br />Terminating kgfd context 0x2b06e27d20a0<br />----***********---- <br /><br /><br /><strong>e. Mounting the ASM diskgroup:</strong><br /><br />$ asmcmd mount B_DATA -- on all nodes.<br /><br /><br /><strong>f. Check if the diskgroup was renamed and mounted successfully:</strong><br /><br />$ crsctl status resource ora.B_DATA.dg<br /><br />NAME=ora.B_DATA.dg<br />TYPE=ora.diskgroup.type<br />TARGET=ONLINE , ONLINE<br />STATE=ONLINE on atld380, ONLINE on atld381<br /><br />Yaaa, diskgroup has been renamed from A_DATA to B_DATA successfully.<br /><br /><br /><br /><strong>Possible error when issuing renamedg command:</strong><br /><br />Error: "KFNDG-00408: disk (string:string) could not be discovered error"<br />Solution: Add the ASM disk path to $PATH environment variable "export PATH=$PATH:/dev/oracleasm/disks"<br /><br /><br /><strong>Output:</strong><br />----***********---- <br />Checking for hearbeat...<br />Re-discovering the group<br />Performing discovery with string:<br />Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0638 with disk number:0 and timestamp (32937868 354065408)<br />Checking if the diskgroup is mounted<br />Checking disk number:0<br />Checking if diskgroup is used by CSS<br />Generating configuration file..<br />Completed phase 1<br />Executing phase 2<br />Looking for ORCL:S_1873_0638<br /><strong>ERROR: -9(Error 7217, OS Error ()<br />)KFNDG-00408: file not found; arguments: [] [ORCL:S_1873_0638]</strong><br /><br />Terminating kgfd context 0x2b3ddb1d80a0<br />----***********---- <br /><br /><br /><br /><strong>The renamedg command usage:</strong><br /><br />$ renamedg -help<br /><br />Parsing parameters..<br />phase Phase to execute (phase=ONE|TWO|BOTH), default BOTH<br /><br />dgname Diskgroup to be renamed<br /><br />newdgname New name for the diskgroup<br /><br />config intermediate config file<br /><br />check just check-do not perform actual operation,<br /> (check=TRUE/FALSE), default FALSE<br /><br />confirm confirm before committing changes to disks,<br /> (confirm=TRUE/FALSE), default FALSE<br /><br />clean ignore errors (clean=TRUE/FALSE), default TRUE<br /><br />asm_diskstring ASM Diskstring (asm_diskstring='discoverystring',<br /> 'discoverystring1' ...)<br /><br />verbose verbose execution (verbose=TRUE|FALSE), default<br /> FALSE<br /><br />keep_voting_files Voting file attribute,<br /> (keep_voting_files=TRUE|FALSE), default FALSE<br /><br /><br /><strong>Q:</strong><br />a. <br />Do we need to modify the asm_diskgroups initialization parameter after renaming the disk groups?<br /><br />No, you don’t have to. The renamedg utility is smart enough to update the asm_diskgroups initialization parameter.<br /><br /><br />b. <br />How do I rename the ASM disk group with datafile in it?<br /><br />After rename the disk group, start the database in MOUNT mode and rename the datafiles using "ALTER DATABASE RENAME FILE . . . TO . . ." command to OPEN the database.<br /><br /><br />c. <br />Does it mater how many disks in the disk group?<br /><br />No, tried with 2 disks DG and also with 8 disks DG.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com3tag:blogger.com,1999:blog-4161953958134414599.post-59456870585882235182010-06-05T21:48:00.003-04:002010-06-06T16:04:00.728-04:00Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:<strong>Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:</strong><br /><br />The DBMS_METADATA_DIFF package provides interfaces to compare and generate database objects structure difference between databases.<br /><br /><br />SQL> select * from tab;<br /><br />TNAME TABTYPE CLUSTERID<br />------------------------------ ------- ----------<br />X TABLE<br />Y TABLE<br /><br />SQL> desc x<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> ENO NOT NULL NUMBER(10)<br /> ENAME VARCHAR2(20)<br /> SAL NUMBER(10)<br /><br />SQL> desc y<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> ENO NUMBER(10)<br /> ENAME VARCHAR2(20)<br /><br /><br />set heading off;<br />set echo off;<br />set pages 999;<br />set long 90000;<br /><br /><strong>1. Compare the tables in same schema:</strong><br /><br />SQL> show user<br />USER is "TEST"<br />SQL> select dbms_metadata_diff.compare_alter('TABLE','X','Y','TEST','TEST') from dual;<br /><br />ALTER TABLE "TEST"."X" DROP ("SAL")<br /> ALTER TABLE "TEST"."X" DROP CONSTRAINT "X_PK"<br /> ALTER TABLE "TEST"."X" RENAME TO "Y"<br /><br /><br />SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST') from dual;<br /><br />ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))<br /> ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P<br />CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT<br />S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA<br />SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE<br /> ALTER TABLE "TEST"."Y" RENAME TO "X"<br /><br />SQL><br /><br /><br /><strong>2. Compare the tables in different schemas in same database:</strong><br /><br />SQL> show user<br />USER is "SYSTEM"<br />SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST2') from dual;<br /><br />ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))<br /> ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P<br />CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT<br />S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA<br />SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE<br /> ALTER TABLE "TEST"."Y" RENAME TO "X"<br /><br />SQL><br /><br /><br /><strong>3. Compare the tables in different databases:</strong><br />-- Assume we are comparing the object between Test and Stage databases and the database link is created between Stage and Test.<br />-- Stage DB: SODS<br />-- Test DB: TODS<br />-- Database Link from Stage to Test: TODS.WORLD<br /><br /><br />TODS:<br />SQL> desc x<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> ENO NOT NULL NUMBER(10)<br /> ENAME VARCHAR2(20)<br /> SAL NUMBER(10)<br /><br />SODS:<br />SQL> desc x<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> ENO NUMBER(10)<br /> ENAME VARCHAR2(20)<br /><br /><br />SQL> show user<br />USER is "TEST"<br />SQL> select * from global_name;<br /><br />GLOBAL_NAME<br />--------------------------------<br />SODS.WORLD<br /><br /><br />SQL> select dbms_metadata_diff.compare_alter('TABLE','X','X','TEST','TEST',NULL,'TODS.WORLD') from dual;<br /><br />ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))<br /> ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P<br />CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT<br />S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA<br />SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE<br /> ALTER TABLE "TEST"."Y" RENAME TO "X"<br /><br />SQL><br /><br /><br /><strong>Notice: Use of the DBMS_METADATA_DIFF package requires the Oracle Enterprise Manager Change Manager license.</strong>Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0tag:blogger.com,1999:blog-4161953958134414599.post-50392575865333283642010-05-25T21:12:00.005-04:002010-06-22T16:50:54.052-04:00Compare and Fix the Object Data in Oracle 11g:<strong>Compare and Fix the Object Data in Oracle 11g:</strong><br /><br /><br />The DBMS_COMPARISON package provides interfaces to compare and sync database objects at different databases. The index columns in a comparison must uniquely identify every row involved in a comparison. <br /><br /><strong>The following constraints satisfy this requirement:</strong><br /><br />- A primary key constraint<br />- A unique constraint on one or more non-NULL columns<br /><br />If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.<br /><br /><br /><strong>Simple Test:</strong><br />drop table test.x PURGE;<br />drop table test2.x PURGE;<br /><br />create table test.x (eno number(10), ename varchar2(20), sal number(10));<br />create table test2.x (eno number(10), ename varchar2(20), sal number(10));<br /><br />alter table test.x add constraint x_pk primary key (eno);<br />alter table test2.x add constraint x_pk primary key (eno);<br /><br /><br />insert into test.x values (100, 'VJ', 100000);<br />insert into test.x values (101, 'ASHRAY', 100001);<br />insert into test.x values (102, 'DUMPA', 200000);<br />insert into test.x values (103, 'REDDY', 300000);<br />commit;<br /><br />insert into test2.x values (100, 'VJ', 100000);<br />insert into test2.x values (101, 'ASHRAY', 100000);<br />insert into test2.x values (102, 'DUMPA', 200001);<br />insert into test2.x values (103, 'REDDY', 300000);<br />commit;<br /><br />select * from test.x;<br />select * from test2.x;<br /><br /><br /><strong>-- Drop the existing comparison:</strong><br /><br />SELECT comparison_name, schema_name FROM dba_comparison;<br /><br />BEGIN<br /> dbms_comparison.drop_comparison (comparison_name => 'comp1');<br />END;<br />/<br /><br /><br /><strong>-- Create the comparison:</strong><br />-- dblink_name is NULL because both schemas on the same database.<br /><br />BEGIN<br /> dbms_comparison.create_comparison (comparison_name => 'comp1',<br /> schema_name => 'test',<br /> object_name => 'x',<br /> dblink_name => NULL,<br /> remote_schema_name => 'test2',<br /> remote_object_name => 'x'<br /> );<br />END;<br />/<br /><br /><br /><strong>-- Check the difference "YES/NO":</strong><br /><br />set serveroutput on size 99999;<br /><br />DECLARE<br /> CONSISTENT BOOLEAN;<br /> scan_info dbms_comparison.comparison_type;<br />BEGIN<br /> CONSISTENT :=<br /> dbms_comparison.compare (comparison_name => 'comp1',<br /> scan_info => scan_info,<br /> perform_row_dif => TRUE<br /> );<br /> DBMS_OUTPUT.put_line ('Scan ID: ' || scan_info.scan_id);<br /><br /> IF CONSISTENT = TRUE<br /> THEN<br /> DBMS_OUTPUT.put_line ('No differences were found.');<br /> ELSE<br /> DBMS_OUTPUT.put_line ('Differences were found.');<br /> END IF;<br />END;<br />/<br />Scan ID: 11<br />Differences were found.<br /><br />PL/SQL procedure successfully completed.<br /><br /><br /><strong>-- Comparison Summary:</strong><br />-- Where scan_id is from the above dbms_comparison.compare PL/SQL block.<br /><br />set linesize 120;<br />COL schema_name for a20;<br />COL object_name for a20;<br />COL comparison_name for a20;<br /><br />SELECT s.scan_id, c.comparison_name, c.schema_name, c.object_name,<br /> s.current_dif_count<br /> FROM user_comparison c, user_comparison_scan_summary s<br /> WHERE c.comparison_name = s.comparison_name AND s.scan_id = 11;<br /> <br /><br /><strong>-- Comparison Details:</strong><br /><br />set linesize 120;<br />COL record_value for a50;<br />COL local_rowid for a12;<br />COL remote_rowid format a12;<br /> <br />SELECT c.column_name, r.index_value record_value,<br /> CASE<br /> WHEN r.local_rowid IS NULL<br /> THEN 'No'<br /> ELSE 'Yes'<br /> END local_rowid,<br /> CASE<br /> WHEN r.remote_rowid IS NULL<br /> THEN 'No'<br /> ELSE 'Yes'<br /> END remote_rowid<br /> FROM dba_comparison_columns c,<br /> dba_comparison_row_dif r,<br /> dba_comparison_scan s<br /> WHERE c.comparison_name = 'COMP1'<br /> AND r.scan_id = s.scan_id<br />-- AND s.last_update_time > SYSTIMESTAMP - 1 / 24 / 12 -- Last 5 min.<br /> AND r.status = 'DIF'<br /> AND c.index_column = 'Y'<br /> AND c.comparison_name = r.comparison_name<br />ORDER BY r.index_value;<br /><br /><br /><strong>-- Fix the difference in the remote table:</strong><br /><br />DECLARE<br /> scan_info dbms_comparison.comparison_type;<br />BEGIN<br /> dbms_comparison.converge<br /> (comparison_name => 'comp1',<br /> scan_id => 11,<br /> scan_info => scan_info,<br /> converge_options => dbms_comparison.cmp_converge_local_wins<br /> );<br /> DBMS_OUTPUT.put_line ('Local Rows Merged: ' || scan_info.loc_rows_merged);<br /> DBMS_OUTPUT.put_line ('Remote Rows Merged: ' || scan_info.rmt_rows_merged);<br /> DBMS_OUTPUT.put_line ('Local Rows Deleted: ' || scan_info.loc_rows_deleted);<br /> DBMS_OUTPUT.put_line ('Remote Rows Deleted: ' || scan_info.rmt_rows_deleted);<br />END;<br />/<br />Local Rows Merged: 0<br />Remote Rows Merged: 2<br />Local Rows Deleted: 0<br />Remote Rows Deleted: 0<br /><br />PL/SQL procedure successfully completed.<br /><br /><br /><strong>Results:</strong><br /><br /><strong>Before:</strong><br />select * from test.x;<br />select * from test2.x;<br /><br />SQL> select * from test.x;<br /><br /> ENO ENAME SAL<br />---------- -------------------- ----------<br /> 100 VJ 100000<br /> 101 ASHRAY 100001<br /> 102 DUMPA 200000<br /> 103 REDDY 300000<br /><br />SQL> select * from test2.x;<br /><br /> ENO ENAME SAL<br />---------- -------------------- ----------<br /> 100 VJ 100000<br /> 101 ASHRAY 100000 <=== Old<br /> 102 DUMPA 200001 <=== Old<br /> 103 REDDY 300000<br /><br /><br /><strong>After:</strong><br />select * from test.x;<br />select * from test2.x;<br /><br />SQL> select * from test.x;<br /><br /> ENO ENAME SAL<br />---------- -------------------- ----------<br /> 100 VJ 100000<br /> 101 ASHRAY 100001<br /> 102 DUMPA 200000<br /> 103 REDDY 300000<br /><br />SQL> select * from test2.x;<br /><br /> ENO ENAME SAL<br />---------- -------------------- ----------<br /> 100 VJ 100000<br /> 101 ASHRAY 100001 <=== New<br /> 102 DUMPA 200000 <=== New<br /> 103 REDDY 300000<br /><br />SQL><br /><br /><strong>Q:</strong><br />a.<br />Can I fix the CLOB/BLOB column data?<br /><br />No, the DBMS_COMPARISON package cannot compare data in columns of the following data types:<br /><br />- LONG, LONG RAW, ROWID, UROWID, CLOB, NCLOB, BLOB and BFILE<br />- User-defined types (including object types, REFs, varrays, and nested tables)<br />- Oracle-supplied types (including any types, XML types, spatial types, and media types)<br /><br /><br />b.<br />Can I fix the object data in 11g by comparing the object in 10g?<br /><br />Yes, The local database that runs the DBMS_COMPARISON package must be 11g but the remote database must be 10gR1 and up.<br /><br /><br />c.<br />Can I compare packages/procedure/functions?<br /><br />No, the DBMS_COMPARISON package can compare the following types of database objects:<br /><br />Tables, Single-table views, Materialized views, Synonyms for tables, single-table views and materialized views.<br /><br /><br />d.<br />Do I need to have primary key on compare object?<br /><br />No, but each column in the single-column/composite index must either have a NOT NULL constraint or be part of the primary key.<br /><br /><br />e.<br />What privileges do I need to compare the objects?<br /><br />Granting EXECUTE on CREATE_COMPARISON package to selected users or roles. (or) Granting EXECUTE_CATALOG_ROLE to selected users or roles.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com3tag:blogger.com,1999:blog-4161953958134414599.post-62220699433428745092010-04-20T21:10:00.006-04:002010-04-21T14:28:00.155-04:00No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:<strong>No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:</strong><br /><br /><strong>No Segment/Virtual Indexes (8i and up):</strong><br /><br />Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.<br />Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions. <br />Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.<br />The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.<br />Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.<br />They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.<br />Statistics can be gathered on virtual indexes in the same way as regular indexes.<br />Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.<br /><br /><strong>To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns): </strong><br /><br />SELECT index_owner, index_name<br /> FROM dba_ind_columns<br /> WHERE index_name NOT LIKE 'BIN$%' <br />MINUS<br />SELECT owner, index_name <br /> FROM dba_indexes;<br /><br /><br /><br /><strong>Invisible Indexes (11gR1 and up):</strong><br /><br />Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. <br /><br />ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;<br />ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;<br /><br /><br /><strong>Using invisible indexes, you can do the following:</strong><br />- Test the optimizer behave on the application before dropping an index.<br />- Use the index for certain operations or modules of an application without affecting the overall performance of the application.<br />- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).<br /><br /><br />An invisible index is maintained during DML statements.<br />Statistics can be gathered on an invisible indexes.<br />The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.<br />Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.<br />We can REBUILD an invisible index.<br /><br />INDEX hit will not work with an invisible index.<br />/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint<br />We CAN'T modify index partition to Invisible, it's at Index level.<br /><br /><br /><br /><strong>Unusable Indexes (11gR2):</strong><br /><br /><strong>Zero Sized Unusable Indexes:</strong><br />In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.<br />This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.<br /><br />ALTER INDEX <index name> MODIFY PARTITION <OLD partition name> UNSABLE;<br /><br />Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.<br />The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.<br />unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.<br />When you query against NEW(Active) partition, it uses the corresponding index scan.<br />If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com1tag:blogger.com,1999:blog-4161953958134414599.post-22836282434283468672009-09-26T16:29:00.005-04:002009-09-26T16:40:48.204-04:00OEM Grid Control Maintenance Tasks - Weekly/Monthly:<strong>OEM Grid Control Maintenance Tasks - Weekly/Monthly:</strong><br /><br /><strong>OMS Monthly Maintenance Tasks:</strong><br /><br /><strong>1. OEM database partition Maintenance:</strong><br /><br />a. Shut down all OMSs:<br /> $OMS_HOME/bin/emctl stop oms<br /><br />b. Connect as SYSMAN to the OEM database and run following two procedures:<br /> SQL>connect sysman/xxxx<br /> SQL>set timing on;<br /> SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');<br /> SQL>commit;<br /> SQL>exec emd_maintenance.partition_maintenance;<br /> SQL>commit;<br /><br /> If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:<br /><br /> SQL>connect / as sysdba<br /> SQL>alter system set job_queue_processes = 0;<br /> SQL>connect sysman/xxxx<br /> SQL>set timing on;<br /> SQL>exec emd_maintenance.remove_em_dbms_jobs;<br /> SQL>exec emd_maintenance.partition_maintenance;<br /> <br />SQL>@<OMS_HOME>/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql<br /> SQL>alter system set job_queue_processes = 10;<br /> SQL>exec emd_maintenance.submit_em_dbms_jobs;<br /> SQL>commit;<br /><br />Note: The partition maintenance will be performed automatically if you have the following configuration:<br /> Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.<br /><br />c. Confirm the Partition maintenance was successful:<br /> select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;<br /><br />d. Restart all OMSs:<br /> $OMS_HOME/bin/emctl start oms<br /><br /><strong>2. Rebuild/Shrink tables and indexes as required:</strong><br /><br /><br /><strong>OMS Weekly Maintenance Tasks:</strong><br /><br /><strong>1. Check and Clear OMS and OEM database system errors.</strong><br /><br /> a. OMS:<br /> Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab<br /><br /> b. OEM Database alertlog errors:<br /> Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log<br /> <br /><strong>2. Fix target metric collection errors.</strong> <br /><br /> a. Cluster agent:<br /> $AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover <br /> $AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure<br /> $AGENT_HOME/bin/emctl clearstate agent<br /> $AGENT_HOME/bin/emctl status agent<br /> <br /> b. Standalone agent:<br /> $AGENT_HOME/bin/agentca -f<br /> $AGENT_HOME/bin/emctl clearstate agent<br /> $AGENT_HOME/bin/emctl status agent<br /><br /><strong>3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.</strong><br /><br /><strong>4. Force delete all the "Deleted Targets"</strong> <br /> Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list <br /> (or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)<br /><br /><strong>5. Clear Critical and Warning alerts:</strong> <br /> Goto: Alerts tab on the OEM main page.<br /> <br /><strong>6. Analyze Metric Rollup Tables as required:</strong><br /><br /> MGMT_METRICS_RAW<br /> MGMT_METRICS_1DAY<br /> MGMT_METRICS_1HOUR<br /> <br /> exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);<br /> exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);<br /> exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);<br /><br /><br />Reference:<br />Doc ID: 456101.1<br />Doc ID: 370695.1Vijay R. Dumpahttp://www.blogger.com/profile/05400068852161662003noreply@blogger.com0