SPOILERS AHEAD


personaly i don't think Holy failed at all, i think it succeded in manipulating the lifestream in order to stop Meteor, yes Midgar was destroyed in the process since it was one of the best places for the Holy/Lifestream to escape and stop Meteor, since Midgar was a huge city-sized Mako Reactor any way, as well as the 2nd biggest scar on the planet, and there was obviously life left since we see Nanaki and his pups, as well as the sounds of human children laughing and playing in the valley below echoing through the canyon...