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,
Happy reading,
Jaffar
5 comments:
Mr.Hussain not for arguing purposes but if we are talking about an Oracle database and an Oracle data guard solution, since dwh means lots of nologging operations, I wonder how this standby designed to survive this problem?
We have a similar BIS system and because of earthquake risk our customers also want this system to be available for their BCP needs.
Thank you for sharing, best regards.
>>since dwh means lots of nologging operations, I wonder how this standby designed to survive this problem?
Yes, I too agree with you on this point.
Well, I would said that this is not purly data warehouse, rather, mixture of online and datawarehouse without any nologging operations.
Hi,
Yes, Really interesting situation...:-)
[quote]every problem is a learning experience[/quote]
That is true...
Regards
Taj
Hi Hussain,
Thanks for sharing your idea.I have one possible solution on which I would like to discuss with you.
When the mount point is full,as per my knowledge goes to rename(i.e relocate) the datafiles the database need not be open mode.So we can mount the database in standby mode(which is already in mounted state in your case) and relocate the datafiles using alter database rename as we do in normal scenario.Also we can bounce the standby database and alter the db_file_name_convert parameter and mount the standby database.This will get updated in the standby controlfile which is sufficient to start the recovery process again.What do you think?
I agree with Yadu. Though you would have set standby_file_management=manual. It becomes tricky to be able to map all the files under one mount point on primary to be mapped to two mount points on standby database. You could move existing files to new mount on secondary location. And remap full file location (including filename) on primary to secondary absolute path (including filename) on a file by file basis.
Post a Comment