Story (Kingdom Hearts)
Kingdom Hearts Birth by Sleep
Castle Oblivion was originally the Land of Departure. After the events at the Keyblade Graveyard, Aqua brought a comatose Ventus to the Land of Departure, which had been destroyed in a storm of darkness by Master Xehanort. In order to keep Ventus safe, Aqua follows Eraqus's prior instructions to seal the world's Keyhole with his Keyblade and recreate it as a labyrinth that only she could navigate, using a means devised by prior guardians. She leaves Ventus in the castle's newly-formed Chamber of Waking and promises to bring Terra back with her before waking him up. As Aqua leaves to search for Terra, she looks back on her transformed home.
Between Kingdom Hearts Birth by Sleep and Kingdom Hearts Chain of Memories
The castle is eventually discovered by Xemnas of Organization XIII, who decides to convert it into a second headquarters for the Organization. It is intended to be a place to research memories, an important topic to the Nobodies of the Organization, who have only their memories with which to form personalities. Xemnas sends Xigbar and Xaldin to recruit more members for the Organization, as they need a large group in order to run the castle. Despite rumors among the Organization that Xemnas intends to locate the Chamber of Waking, to complement the Chamber of Repose built under Hollow Bastion, they never do.
Eventually, a strange Nobody named Naminé appears in the castle and is found by the Organization. She soon becomes the center of their research, due to her strange abilities of memory manipulation. In order to further research, pods are developed that can be used to piece together memories. Xemnas entrusts the castle and Naminé to the neophyte Marluxia, but the latter soon plots to use this power in a bid to seize control over the Organization. While Marluxia and his associates, Larxene and Axel, control the top half of the castle, the senior members Vexen, Lexaeus, and Zexion control the basements.
Kingdom Hearts Chain of Memories
After defeating Ansem, Seeker of Darkness, and restoring the damaged worlds, Sora, Donald Duck, and Goofy find themselves in a grassy field with the King's dog, Pluto. They follow Pluto along the path to Castle Oblivion, and as soon as they enter the castle, they forget all of their skills and are forced to rely upon the castle's memory-based card system. Although Marluxia and Larxene tell them their memory loss is simply the nature of the castle, it is revealed that the mysterious girl Naminé is actually the one reshaping Sora's memories, so that Marluxia can use Sora as a puppet against Xemnas. The scheme fails thanks to the meddling of Axel (who was actually on orders from Xemnas and Saïx to rid the Organization of traitors), who frees Naminé, and Marluxia and Larxene are destroyed at Sora's hand. Sora then uses one of Xemnas's pods so that Naminé can restore his memories, and is eventually moved to Twilight Town.
Meanwhile, Sora's friend Riku awakens from the darkness within the lowest basement of Castle Oblivion, where he faces the remaining darkness of "Ansem". Though he initially sets out to destroy the indelible darkness in his heart, with the help of DiZ, Naminé, and King Mickey, he is able to eventually embrace both the light and the dark, achieving balance. The three basement members, Vexen, Lexaeus, and Zexion seek to recruit Riku in order to counter Marluxia's use of Sora, but Riku denies their requests and vanquishes them. Riku eventually defeats the Ansem in his heart, and decides not to have Naminé seal it away completely; instead, he sets out on the "road to dawn" in order to help while Sora is sleeping.
Kingdom Hearts 358/2 Days
After members of Organization XIII are sent to Castle Oblivion, Roxas learns from Demyx and Xigbar on Day 24 that the members assigned to Castle Oblivion have been annihilated.
On Day 255, Axel went in Castle Oblivion to get rid of any evidence of the Organization's presence as the castle would no longer be used as a research facility/second headquarters. Xion goes to Castle Oblivion on the same day to seek answers about her existence. Axel tries to stop her, but his efforts are futile.
On Day 296, when Roxas learns from Axel that Xion was born in Castle Oblivion, he becomes determined to investigate the place for himself. The next day, Roxas was assigned to go there and hunt down the impostor. Roxas used the opportunity to investigate Castle Oblivion. Axel chose to tag along claiming that he knows the place better than anyone else in a black coat. However, barely has Roxas walked in the door when he is overcome by a strange weakness and passes out entirely, due to how close he physically was to Sora and Ventus.
During conversations between Axel and Saïx, Saïx asks Axel if he has found the Chamber of Waking, which Axel denies. Axel is sent various times by Xemnas to find the room, showing Xemnas's extreme determination to find the Chamber of Waking, yet he never succeeds, due to Aqua's previous actions.
Kingdom Hearts II
Roxas briefly sees the outside of Castle Oblivion in his final dreams, coinciding with Sora regaining the corresponding memories.
Kingdom Hearts coded
After defeating Sora's Heartless and returning to the data worlds, Data-Sora enters a data version of Castle Oblivion. He has meetings with the mysterious cloaked man, as well as various figures from Sora's memories. Like the real Sora did, Data-Sora uses World Cards to traverse the castle. One notable feature of this incarnation of Castle Oblivion is that the White Rooms do not change form completely; the design remains like the rest of the castle, although the shape of the room differs. Pictures of areas from the worlds are displayed on the walls. Near the end of the story, he meets Data-Naminé, who explains the reason of his existence.
Kingdom Hearts 3D: Dream Drop Distance
Ventus continues to sleep within the Chamber of Waking, following the end of Sora and Riku's Mark of Mastery exam. Upon Sora's return to Traverse Town, he smiles from Sora's happiness.
Role in the series
Castle Oblivion in the SpongeBob and Friends series is of the same role as in Kingdom Hearts, but it's also known to rest in a realm of mnemonic space which is where the castle's effects come from. It was built eons ago by Druids to act as a memory library and when they disappeared, the castle became abandoned and left to be a memory-wiping barren castle and later on become the Land of Departure.