We have a data warehouse database sized more than 2TB using Oracle 9i on HP Superdom machine.
Couple of days back, I have come across of a very tricky situation with its standby database. Yes, you read it correctly. We have the standby database in place for the this database. For god sake, don’t argue with this as there are N number of reasons behind having a standby database for the data warehouse database.
The situation was that the mount point, which has all the standby database files, on standby database server became 100% full and the media recovery background process died/crashed (whatever you say) and archive applying was stopped. I went to the UNIX team and asked them increase the size of the mount point. They gave me a big surprise, saying that the mount point is already 2TB size and on HP Superdom the mount can’t exceed 2TB size. Therefore, increasing the size of the mount point was not possible.
After hearing their comments, I have initially thought that I can open the standby database in READ ONLY mode, move some datafiles to a new mount point in order to have some free space on this mount point. The idea doesn’t work as I was not able to open the database. Oracle was complaining about the media recovery when I tried to open the database in read only mode.
I was in a impression that this would be a dead end and I have ended of recreating the standby database again. Again a standby database which is more than 2TB…. Uffffssss
One of our UNIX admin guy gave me an idea. It was like moving few datafiles from the old location to the new location and creating the OS level link for the datafiles in the old location pointing to the new location.
I thought, I could try this and it worked beyond my imagination. I have shutdown the standby database, allowed him to move the datafiles (for him, it was like flat files) to new location and create the links for the files in the old location pointing to the new location.
I was bit worried while bringing the standby database up and starting media recovery process. The idea worked out and Oracle started applying archived logs on the standby database from the point where they were stopped.
I felt like in my career, every problem is a learning experience and give new ideas to deal with the situation.
That’s all now,