Wednesday, April 28, 2010

11gR2: how to remove file from ASM diskgroup

I needed to delete some asm files from my previous database installation.


I wanted to use ASMCMD tool so I loaded my ASM environment:
[oracle@rac32 ~]$ . oraenv
ORACLE_SID = [RAC32] ? +ASM2
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle


Then I entered into asmcms tool:
[oracle@rac32 ~]$ asmcmd
ASMCMD> ls
DATA_RAC3/
FRA_RAC3/
ASMCMD> exit
[oracle@nov2211 ~]$ asmcmd -p
ASMCMD [+] > cd DATA_RAC3
ASMCMD [+DATA_RAC3] > ls
RAC3/
ASMCMD [+DATA_RAC3] > cd RAC3
ASMCMD [+DATA_RAC3/RAC3] > ls
ARCHIVELOG/
ASMPARAMETERFILE/
OCRFILE/

Ok .. there are some files and I would like to remove them.
ASMCMD [+DATA_RAC3/RAC3] > rm ARCHIVE*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

That error could be tied at the metalink note 444812.1 and bug 3751057.8


In that doc id they suggest to run a sql script and recreate the alter diskgroup drop file syntax.
Likely I found the following link (and thanks to Freek D'Hooge) http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=2403#
that help me to reach my goal.
SQL> column a format a120
SQL> set linesize 120
SQL> select 'alter diskgroup ' || gname || ' drop file ''+' || gname || sys_connect_by_path(aname, '/') || ''';' a
2 from ( select b.name gname, a.parent_index pindex, a.name aname,
3 a.reference_index rindex , a.system_created, a.alias_directory
4 from v$asm_alias a, v$asm_diskgroup b
5 where a.group_number = b.group_number
6 )
7 where alias_directory = 'N'
8 and system_created = 'Y'
9 start with (mod(pindex, power(2, 24))) = 0
10 and rindex in
11 ( select a.reference_index
12 from v$asm_alias a, v$asm_diskgroup b
13 where a.group_number = b.group_number
14 and (mod(a.parent_index, power(2, 24))) = 0
15 and a.name = '&DATABASENAME'
16 )
17 connect by prior rindex = pindex;
Enter value for databasename: RAC3
old 15: and a.name = '&DATABASENAME'
new 15: and a.name = 'RAC3'

A
------------------------------------------------------------------------------------------------------------------------
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ASMPARAMETERFILE/REGISTRY.253.706985453';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/OCRFILE/REGISTRY.255.706985457';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_81.272.707781651';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_2_seq_30.271.707775999';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_80.270.707775999';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_82.273.707787599';
...
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_699.1067.714573049';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_700.1058.714573053';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_701.1066.714573059';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_2_seq_579.1065.714573061';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_702.1064.714573069';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_691.1452.714532055';

1176 rows selected.



Then I simply copied and executed the output:
SQL> alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_81.272.707781651';

Diskgroup altered.

[oracle@nov2211 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 511993 511882 0 511882 0 N DATA_RAC3/
MOUNTED EXTERN N 512 4096 1048576 511993 511896 0 511896 0 N FRA_RAC3/





That's all

79 comments:

Timothy Weinand said...

Thanks for this handy little script. I had some leftover files from database instances I deleted and couldn't get rid of them. This took care of it.

Anonymous said...

The trick here is that you need to speak with the Middle Stone Brother (O14) and start the quest A Rocky Start in which you have to search for the Special Rock. [url=http://www.fillerobe.com]canada goose[/url] For instance, if you stay at Disney Paradise Pier, you can pay the cheaper cost of $165 a night. [url=http://www.d2now.com]canada goose jacket review[/url] Tyipsmtya
[url=http://www.jacketstorontoonsale.com]canada goose chilliwack bomber[/url] Sxmoxepxf [url=http://www.louboutinstoreuk.co.uk]christian louboutin outlet[/url] xltqqmpyr [url=http://www.cheapcanadagooseparkas.ca]canada goose toronto[/url]

Anonymous said...

generic xanax online can you buy xanax online in australia - buy xanax mexico online

Anonymous said...

cheap nike shoes yblbgaws nike factory kxqluzno nike online store fxvraqvh nike outlet online isbjcfjk nike outlet store zulcsedm nike outlet ijfxpeye nike running shoes lsmlxuxt nike shoes negpntfm nike store smjpwtie

Anonymous said...

xanax 0.5mg pictures of generic xanax tablets - have you ever bought xanax online

Anonymous said...

cheap nike shoes xzdlweds nike factory pofedutm nike online store wccaytna nike outlet online rkiptgcm nike outlet store rexdwojt nike outlet qczwddly nike running shoes ehtwddqq nike shoes unxmmpzo nike store cuahrcax

Anonymous said...

xanax online xanax withdrawal after one dose - buy xanax online overseas

Anonymous said...

xanax online lortab xanax drug interactions - white round pill generic xanax

Anonymous said...

buy tramadol online tramadol 50mg 319 - tramadol 50 mg narcotic

Anonymous said...

buy carisoprodol medication soma carisoprodol - carisoprodol maximum daily dosage

Anonymous said...

generic xanax xanax 1mg street price - xanax white pill s 900

Anonymous said...

buy carisoprodol carisoprodol 350 mg review - carisoprodol addiction

Anonymous said...

buy tramadol online help buy tramadol - tramadol cod online pharmacy

Anonymous said...

buy xanax online without prescription xanax 2 mg high dose - xanax dosage .125

Anonymous said...

generic xanax how to order xanax - xanax side effects how long

Anonymous said...

buy carisoprodol carisoprodol 350 mg ibuprofen - side effects taking carisoprodol

Anonymous said...

buy tramadol online tramadol hcl 50mg online - buy tramadol online overnight shipping

Anonymous said...

buy tramadol online zydol 50 mg tramadol hydrochloride - tramadol high altitude

Anonymous said...

buy tramadol online tramadol hcl 50 mg tab amn - buy tramadol online no prescription overnight

Anonymous said...

xanax online 2mg xanax not working - xanax withdrawal joint pain

Anonymous said...

buy tramadol online tramadol 50 mg get high - safe buy tramadol online

Anonymous said...

xanax order no prescription how long does 3mg xanax high last - 1 xanax bar effects

Anonymous said...

buy tramadol online where to buy tramadol online legal - tramadol hcl generic name

Anonymous said...

buy tramadol online tramadol hcl recall - tramadol withdrawal after short term use

Anonymous said...

buy tramadol online tramadol 50 mg 100 - buy tramadol valium online

Anonymous said...

cialis online cialis price comparison us - cialis 10 mg price

Anonymous said...

buying xanax xanax side effects weight - xanax withdrawal what to do

Anonymous said...

cialis online buy cialis us no prescription - generic cialis tadalafil 40 mg

Anonymous said...

buy cialis online cialis online malaysia - difference between cialis daily cialis

Anonymous said...

cialis online where to buy real viagra cialis online - cialis bathtubs

Anonymous said...

xanax online xanax withdrawal symptoms depression - xanax pills shapes

Anonymous said...

cialis online cialis daily use review - order cialis online no prescription

Anonymous said...

buy cialis online buy cialis mexico - buy cialis new delhi

Anonymous said...

buy tramadol next day tramadol 100mg info - buy tramadol er

Anonymous said...

http://landvoicelearning.com/#62431 is tramadol hcl a narcotic drug - tramadol withdrawal chest pain

Anonymous said...

buy tramadol usa tramadol 50 mg vs percocet - tramadol kratom

Anonymous said...

buy tramadol tramadol hcl 50 mg 319 - recovery from tramadol addiction

Anonymous said...

http://buytramadolonlinecool.com/#91646 tramadol for dogs 50 mg - cheap generic tramadol

Anonymous said...

buy tramadol online buy tramadol pay with mastercard - generic for tramadol

Anonymous said...

buy tramadol tramadol vicodin addiction - tramadol 50 mg price streets

Anonymous said...

buy tramadol cod tramadol hcl 200 er - tramadol addiction forum

Anonymous said...

buy tramadol tramadol repeat order - buy tramadol money order

Anonymous said...

learn how to buy tramdadol can order tramadol online - tramadol and pregnancy

Anonymous said...

tramadol 50 mg purchase tramadol with mastercard - tramadol for dogs uses

Anonymous said...

buy tramadol online no prescription overnight tramadol sale no prescription - tramadol 50mg dosage for dogs

Anonymous said...

ways to buy ativan online lorazepam 1 mg drug test - buy lorazepam online cheap

Anonymous said...

buy tramadol treatment of tramadol addiction - tramadol - withdrawal procedure

Anonymous said...

http://reidmoody.com/#45570 ativan taper alcohol - purchase ativan online no prescription

Anonymous said...

buy ativan online ativan online purchase - withdrawal from ativan

Anonymous said...

http://reidmoody.com/#96318 ativan dosage social anxiety - ativan dosage dogs

Anonymous said...

can you really buy xanax online xanax g3722 - xanax side effects yahoo

Anonymous said...

buy xanax online xanax effects dosage - how to buy xanax online

Anonymous said...

buy tramadol online tramadol hcl 50 mg tablets - online pharmacy for tramadol

Anonymous said...

can you buy xanax online buy mylan alprazolam - buy xanax ireland online

Anonymous said...

http://ranchodelastortugas.com/#71453 buy good xanax online - xanax zantac drug interactions

Anonymous said...

buy tramadol online can you buy tramadol online - cheap tramadol with cod

Anonymous said...

http://ranchodelastortugas.com/#82165 how many xanax 1 mg to get high - 1mg klonopin 1mg xanax

Anonymous said...

buy tramadol online cheap tramadol ultracet - tramadol high doses

Anonymous said...

buy xanax online xanax side effects hearing - xanax 2088

Anonymous said...

http://bayshorechryslerjeep.com/#2902 order xanax mail - buy xanax denver

Anonymous said...

buy xanax online xanax bars online - drug interactions xanax geodon

Anonymous said...

http://bayshorechryslerjeep.com/#2mg can i buy xanax online - xanax xanax bars

Anonymous said...

I am in fact glad to read this website posts which carries
plenty of useful facts, thanks for providing these statistics.


Also visit my blog post: Cheap Nike Free Run

Anonymous said...

I do not even know how I ended up here, but I thought this post was great.

I do not know who you are but definitely you're going to a famous blogger if you aren't already ;) Cheers!


Visit my blog post; fake oakley sunglasses

Anonymous said...

What a information of un-ambiguity and preserveness of precious familiarity concerning unexpected feelings.



My blog; polo ralph lauren outlet online

Anonymous said...

Your rеpoгt οffегs proven benefiсiаl to uѕ.

Ιt’ѕ very informative anԁ you really аre naturallу quitе ωell-infοrmeԁ in this
arеa. You have got opеned mу own eуe to variοus viеws on this kind of subject with intriguing, notablе and strong content.


Also ѵisit my wеbsite ... valium

Anonymous said...

After going over a number of the articles on your web
page, I seriously appreciate your way of writing a blog.

I book-marked it to my bookmark webpage list and will be checking back in
the near future. Please visit my website as well and let me know what you think.


Feel free to surf to my webpage - Christian Louboutin Flats

Anonymous said...

If you would like to take a great deal from this piece of writing then you have to apply
such strategies to your won weblog.

My webpage ... christian louboutin glitter pumps

Anonymous said...

You can certainly see your enthusiasm in the work you write.
The arena hopes for even more passionate writers
like you who are not afraid to say how they believe. Always go after your heart.


Here is my web site: christian louboutin glitter pumps

Anonymous said...

Hi, Neat post. There's an issue along with your web site in internet explorer, may check this? IE nonetheless is the marketplace leader and a huge component to other people will omit your excellent writing because of this problem.

My blog post ナイキエアジョーダン

Anonymous said...

It is not my first time to pay a quick visit this web site, i am browsing this web page dailly
and get pleasant information from here daily.


My webpage ... cheap nike air max free shipping

Anonymous said...

Howdy just wanted to give you a quick heads up. The words in
your content seem to be running off the screen in Chrome.
I'm not sure if this is a formatting issue or something to do with internet browser compatibility but I figured I'd post to let you know.
The layout look great though! Hope you get the problem fixed soon.
Cheers

Here is my weblog :: アバクロ tシャツ

Anonymous said...

Hmm it looks like your site ate my first comment (it was extremely long) so I guess I'll just sum it up what I wrote and say, I'm thoroughly enjoying your blog.
I as well am an aspiring blog blogger but I'm still new to the whole thing. Do you have any helpful hints for novice blog writers? I'd genuinely appreciate it.



Take a look at my weblog ... wholesale polo shirts

Anonymous said...

Choose a web host who actually supplies a cpanel. When low frequencies are used, resources
transmission can arise over a small distance. They are generally used with regard to communication in jets and ships sailing.
Cost-free online hosts can be acquired if you're scrubbing to save money. http://zedwed.upallpic.com/member.php?u=500925

Anonymous said...

Hey there! I've been following your site for a while now and finally got the courage to go ahead and give you a shout out from Atascocita Texas! Just wanted to mention keep up the good job!

Feel free to visit my site - Oakley Sunglasses Cheap

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose