One of my favorite RPGs from the SNES era that is almost completely forgotten now is the Lufia series. The first game in that series, Lufia and the Fortress of Doom, is one of my favorite games from that era. It is a solid RPG that put a lot more emphasis on telling a good character-driven story than many other games of its time. However, one thing particularly good about the game is its opening sequence.
Lufia and the Fortress of Doom starts out with the player controlling a group of characters led by a red-haired warrior named Maxim who have just invaded the floating fortress that serves as the final stronghold of a group of all-powerful evil beings called Sinistrals. The four heroes are all at very high level, are equipped with very powerful weapons and armor (including the legendary sword Dual Blade that is central to the plot), and have a large arsenal of powerful magic. In other words, the game starts with what would otherwise be considered to be the final dungeon of a game, including a battle against the most powerful foes in the game, the Sinistrals themselves. However, after the heroes make great sacrifices in order defeat the Sinistrals, one hundred years pass, and the player is introduced to the real hero of the game: Maxim's descendant.
The idea of the hero of a game being the descendant of a great hero who saved the world from evil in some time long ago was hardly new when Lufia and the Fortress of Doom was made. After all, such a plot, the story of Erdrick/Loto and his descendant, was very important to the original Dragon Quest (actually, it is important to at least the first three Dragon Quest games), and has appeared in many other places (in Final Fantasy V there are even two different levels to the idea, and it is central to game plots as recently as the Jecht plot in Final Fantasy X). However, while that plot was far from new, the fact that Lufia both lets you play through the climactic final battle of the old heroes and keeps the two different time periods close together results in a game where the battle of the past actually feels relevant to the events of the game's present.
Because the two eras of Lufia are only spread a hundred years apart, the evil of the Sinistrals and Maxim's heroism are within living memory. In fact, you meet one of the former heroes very early into the real story of the game, and an important goal later in the game is trying to find and meet the other surviving hero. The relatively small hundred-year gap also means that the game's hero is only the great-grandson or so of the legendary heroes Maxim and Selan, rather than some kind of undefined "the bloodline flows through you" descendant (a typical plot element that I despise, actually). Also, the inevitable revival of the villains and the search for Maxim's Dual Blade are made to be far more directly connected to the events of the past than is usually seen.
More important than the small time jump, though, is the way that the old heroes and villains are given real identities and characterization. The sense of long-held camaraderie between the heroes, their brave and desperate stand against terrible odds, and Selan's touching and tragic final wish that she could hold her child one last time before her death all add up to make the old heroes memorable and likable. Meanwhile, the fact that the most important villains are all named and given identities and personalities right at the beginning becomes very important to the plot of the main game, particularly for one very surprising plot twist late in the game. Many turns in the plot and a lot of the real emotion of the game is built upon the characters and events of the "ancient battle", and that would be far less effective if the battle was simply talked about, rather than shown.
Also, one final great quality of Lufia's opening is that it is a lot of fun. It is a really great opening simply because it starts the game with a lot of action and gameplay, letting the player actually play the game while simultaneously introducing a lot of central plot elements without an excessive amount of exposition and boring narration. It grabs the player's attention with fun battles and good characters right from the start, without any kind of excessive build up. It is hard to find openings that do so quite as cleverly as this one.