\n<\/div>\n
DEFINE ASMGROUP=\"DGDATA\"\n \nset linesize 200\nset pagesize 50000\ncol reference_index noprint\ncol type format a15\ncol files format a80\n\nWITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),\n v_parentindex AS (SELECT parent_index \n FROM v$asm_alias \n WHERE group_number = (SELECT group_number FROM v_asmgroup) \n AND alias_index=0),\n v_asmfiles AS (SELECT file_number, type \n FROM v$asm_file \n WHERE group_number = (SELECT group_number FROM v_asmgroup)),\n v_dbname AS (SELECT '\/'||upper(db_unique_name)||'\/' dbname from v$database)\nSELECT 'rm '|| files files FROM -- this line show the delete command\n(\n SELECT '+&ASMGROUP'||files files, type \n FROM (SELECT upper(sys_connect_by_path(aa.name,'\/')) files, aa.reference_index, b.type\n FROM (SELECT file_number,alias_directory,name, reference_index, parent_index \n FROM v$asm_alias) aa,\n (SELECT parent_index FROM v_parentindex) a,\n (SELECT file_number, type FROM v_asmfiles) b\n WHERE aa.file_number=b.file_number(+)\n AND aa.alias_directory='N'\n -- missing PARAMETERFILE, DATAGUARDCONFIG\n AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')\n START WITH aa.PARENT_INDEX=a.parent_index\n CONNECT BY PRIOR aa.reference_index=aa.parent_index)\n WHERE substr(files,instr(files,'\/',1,1),instr(files,'\/',1,2)-instr(files,'\/',1,1)+1) = (select dbname FROM v_dbname)\nMINUS (\n SELECT upper(name) files, 'DATAFILE' type FROM v$datafile\n UNION ALL \n SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile\n UNION ALL\n SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'\n UNION ALL\n SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'\n)\n);\n\nFILES\n--------------------------------------------------------------------------------\nrm +DGDATA\/O01XXXXX\/CONTROLFILE\/CURRENT.800.807811333\n<\/pre>\n\nSo then I just need to run from asmcmd:
\nASMCMD> rm +DGDATA\/O01XXXXX\/CONTROLFILE\/CURRENT.800.807811333<\/span><\/p>\n<\/span>Because WITH clause does not work on MOUNTED databases (like a standby) – SQL Statement Using ‘WITH’ Clause Fails with ORA-01219 [ID 1166073.1] –<\/span>, here the version without the WITH clause:<\/p>\nDEFINE ASMGROUP=\"DGDATA\"\n \nset linesize 200\nset pagesize 50000\ncol reference_index noprint\ncol type format a15\ncol files format a80\n\nSELECT 'rm '|| files files FROM -- this line show the delete command\n(\n SELECT '+&ASMGROUP'||files files, type \n FROM (SELECT upper(sys_connect_by_path(aa.name,'\/')) files, aa.reference_index, b.type\n FROM (SELECT file_number,alias_directory,name, reference_index, parent_index \n FROM v$asm_alias) aa,\n (SELECT parent_index FROM (SELECT parent_index \n FROM v$asm_alias \n WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') \n AND alias_index=0)) a,\n (SELECT file_number, type FROM (SELECT file_number, type \n FROM v$asm_file \n WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b\n WHERE aa.file_number=b.file_number(+)\n AND aa.alias_directory='N'\n -- missing PARAMETERFILE, DATAGUARDCONFIG\n AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')\n START WITH aa.PARENT_INDEX=a.parent_index\n CONNECT BY PRIOR aa.reference_index=aa.parent_index)\n WHERE substr(files,instr(files,'\/',1,1),instr(files,'\/',1,2)-instr(files,'\/',1,1)+1) = (select dbname FROM (SELECT '\/'||upper(db_unique_name)||'\/' dbname from v$database))\nMINUS (\n SELECT upper(name) files, 'DATAFILE' type FROM v$datafile\n UNION ALL \n SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile\n UNION ALL\n SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'\n UNION ALL\n SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'\n)\n);\n<\/pre>\n","protected":false},"excerpt":{"rendered":"These days I’ve been doing the migration of some BI databases into a private cloud, about 7 TB of data in total. We made sure that before starting there was enough disk space. However, in the middle of the largest “duplicate from active database”, it stops. When trying to restart, it complaints that disk group […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,6,1],"tags":[],"class_list":{"0":"post-15","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-asm","7":"category-oracle","8":"category-uncategorized","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":80,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/15\/revisions\/80"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}