Seeing Adult Link for the first time. He's all grown up, finally allowed to wear pants and ready to kick some ass.
Hitting Like Likes with any sort of magical arrows. These are the things of nightmares but there is something immensely satisfying about seeing them burn to death. (Or freeze, or explode in holy light...)
Ocarina of Time emptied truck loads of respect for Princess Zelda upon all who played it. Especially after getting all six medallions and appearing at the Temple of Time to get the Light Arrows, where the badass Sheikah ninja Sheik unmasks to reveal none other than Zelda herself, who had spent the length of the seven year Time Skip masquerading as Sheik to hide from Ganon.
The first time using the Golden Gauntlets. To see Link not only lift an obelisk a hundred times his size, but send it FLYING into the air behind him, is, well... you know.
Even Navi gets a crowning moment at the start of the final boss fight. "There's no way he's going to hold me back again! This time, we fight together!"
Saria's moment of awesome was when she became the sage of the forest. Also, this hints that she had gotten into the chamber in the back part of the temple as well, meaning that she fought through the temple all by herself. A little, unarmed Kokiri girl and her pink fairy. Awesome. note It's implied that by becoming a sage, you have to die...still a Crowning Moment for bravery.
Sure, pretty much every time Link takes on Ganon/dorf is a Crowning Moment Of Awesome, but in Ocarina, the final battle is truly epic: after you escape the collapsing tower, Ganondorf goes One-Winged Angel and turns into Ganon for the first time, towering over Link with twin swords that are each nearly as big as The Hero is tall. Then Ganon bellows in rage, wordlessly promising that this is going to be a Curb-Stomp Battle. That's his Crowning Moment. Then, when Link finally knocks him down, Zelda finally gets hers, using all her might as a Sage and sorceress to pin Ganon in place. Last of all, Link rips into Ganon with the Master Sword and finishes it by stabbing him through the forehead, allowing the Sages to banish Ganon. Hero of Time, no kidding.
Let's face it: Ganon was a lame last boss on the NES, but when he appeared in 3D at the end of Ocarina of Time, he finally represented the kind of Monstrosity Miyamoto imagined.
What also makes that fight intro particularly awesome is two simple things - first, Ganon is the only boss in the game with no subtitle, because he doesn't need one. This is Ganon, you don't need a description of him, the simple knowledge that this is Ganon tells you A) this is the final battle, and B) it is going to be awesome. Then as Ganon starts flailing his arms, he knocks the Master Sword out of Link's grasp and beyond the ring of fire around them, and you realize not only are you about to fight Ganon again, but you're expected to do it without the Master Sword. And then you do, proving Link really is a true hero—he doesn't need the Master Sword to kick Ganon's ass, he's picked up enough tricks and items to do it himself.
Adding onto the bit about Ganon not having boss subtitles, there's an alternate interpretation that amplifies the awesomeness a thousand fold: It isn't just that Ganon needs no description, it's that he's such a terrifying, Badass monster that he's beyond any description. "Ganon" is literally the best the game can come up with.
Also, for the player, capturing Poes on horseback is a big feat, especially if it's a gold Poe. Those back away so fast you have to be a real good shot to hit one and then very fast to get to it before the flames disappear.
Similarly, getting the Biggest Quiver. You need to fire off arrows on horseback at various targets. With each shot, you can get either 0, 30, 60, or 100 points. You have 20 arrows. You need a score of 1500. That's the equivalent of fifteen bulls-eyes, and unless your first ten shots are perfect, there is zero margin for error.
Getting Epona. You've just wasted Ingo in two straight horse races, and he agrees to give you Epona... but locks you into the ranch. What do you do? Jump the ranch walls and watch Epona do an Awesome pose. And then later on in Gerudo Valley you're confronted with a broken bridge. You jump the FREAKING CANYON.
Or even better than jumping over one of the wall fences, jump over the fence that Ingo just closed AND over him to boot and, if lucky enough, get treated to the Bad Ass horseman pose as the ultimate "screw you" to Ingo.
Playing the Song of Storms at the windmill for the first time (chronologically). The usually happy guy is mad at a later timeline becouse a kid played that song and messed up the windmill badly and drained the well which had sealed an evil spirit. Turns out you go back in time and create a time loop by being that kid, playing the song he ironically taught you, and opening the most terrific (and terrifying) dungeon in the game.
The cutscene after Link gets the Master Sword, showing Ganondorf praising Link for letting him into the Sacred Realm. The scene itself isn't much (just Ganandorf standing in a white area), but what makes it awesome is the ensuing Fridge Horror: You failed. All that adventuring and hardship you faced to defeat the bosses of the three dungeons to acquire the three spiritual stones were for naught. Ganondorf won. He got the Triforce (kinda). And you might make it through the next seven years unscathed, but no one else will. Now it's time for payback.
The horrifying part isn't failing. The horrifying part is that Ganondorf could not have gotten into the Sacred Realm if you and Zelda had just kept out of it. Granted, he would still have assassinated the Hylian King, but he probably wouldn't have had the power he needed to cause quite the Bad Future he did. A true case of Nice Job Breaking It, Hero if there ever was one.
Simply ascending Ganondorf's Tower at the end of the game is one of the best parts of the game, thanks in part both to its incredibly ominous music and what this speaks for our favorite villain's character. The music itself is an expertly crafted piece by Ganondrof which echoes through the entire fabric of the tower, and it showcases Ganondorf as musically talented and a villain of refined tastes; but also it speaks of his incredible arrogance and confidence that he feels so certain of his victory that he can put on a little show before the curtain falls. The music in question also subtly illustrates Ganondorf's evil, it starts out soft and slightly ominous, echoing through the tower giving a tiny glimpse of the evil that is to come, and then as Link ascends further up the tower the music becomes overwhelming in its power, the escalation of that chaos vibrates through the tower and consumes all other thoughts — one can only pay attention to the music at this point — the chaos of that music is as terrifying as Ganondorf is; symbolizing that they are in the presence of an ultimate, dastardly, perverse and all-consuming evil that one would be smart to run away from or else face complete and utter destruction.
The "starts soft and grows louder and more sinister" is less that Ganon's playing is changing, and more that the music is just getting louder to Link as he's climbing closer to its source. Still awesome, though.
Darunia, the wacky, Hot-Blooded leader of the Gorons running off to fistfight a fire dragon to try to buy you a little time to save his people. He knows that he doesn't have the megaton hammer, and thus, is pretty much incapable of winning, yet he hoists up his gigantic rocky balls and runs in anyway. That, friends, is a man you should be honored to call your sworn brother.
Awesome Moment for the game itself: It got into the Guinness Book of Records as the best-reviewed game of all time.
A sort of retroactive meta-moment thanks to the official Zelda timeline: The games set after Ocarina of Time fall into one of three timelines, "Child", "Adult", and "Demise". AllfourZeldagames made prior to OoT are in the Demise timeline, which follows from Link failing to defeat Ganon and dying in Ocarina of Time. All Zelda games made after OoT, except for prequels and the Oracle games, are set in either the Child or Adult timeline, both of which follow from the ending of Ocarina of Time. This means that when you beat Ocarina of Time, you are changing Hyrulean history so even if you lose, Link still wins. That's what makes him the Hero of Time.