Simulating 11g Snapshot Standby Database feature on Oracle 10g?

As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

What if you want to achieve the same on Oracle 10g? Well, I absolutely don’t have any clue about others, but, we have come across of such situation couple of days ago when our DR (Disaster Recovery Solution) team came to us with a request to test our standby database. They want the standby database in read write mode to do some real scenario tests and once the testing is done, they want the database to be back to standby mode.

We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.

The procedure as follows:

1. Set the following parameters on the standby database:

db_recovery_file_dest_size & db_recovery_file_dest

- Make sure the values are reflected.

2. Stop the media recovery process, if active.

3. When the standby is in MOUNT mode, Create a guaranteed restore point:

CREATE restore point before_rw guarantee flashback database;

3. Stop the log shipping on the primary database. (for safer side)

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

4. Failover the standby database using the following command:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

-Make sure the media recovery process is turned off

-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.

5. Open the database (read write mode).

AT THIS POINT, YOU CAN USE THIS DATABASE AS NORMAL READ WRITE DATABASE.

Reverting the database back to standby mode:

• Shutdown the database
• Startup database in mount mode
• Flash back database to restore point using the following:

FLASH BACK DATABASE TO RESTORE POINT before_rw;

• Convert the database back to standby mode using the following:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

• Shutdown the standby database and remove the previously set parameters.
• Start the standby database in mount state and drop the restored point.
• Enable the Media Recovery on the Standby database.
• Activate log shipping on the primary using the following:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENALE;

Since, we have done very little changes in the database, after converting to read write mode, the time which took to revert the database back to standby mode took few minutes only. Well, it is definitely need to be seen the time that take during the conversion to standby mode after huge changes in the read write database.

It worked well with us and Mr. Asif definitely deserved an appreciation

The tests have been carried out on AIX 64 bit with Oracle 10.2.0.3 release.

In the Part II, I will be posting my testing.

Happy Reading,

Jaffar

Back from vacation

I just back to office after a month vacation and going through the back log/pending work. Currently, having an uphill task of upgrading an Oracle 9i data warehouse database (3 TB size) to Oracle 10g and also migrating this database from HP to AIX.

Its been nearly two months that I didn't write anything to my blog also didn't participate in any kind of database forums as I was gearing up for my every first seminar (RMAN). I have successfully delivered my one exclusive RMAN seminars in Sweden and Denmark respectively in September month. Good amount of participants were present, 20 participants in Sweden and 28 participants in Denmark. Although, the seminars went well, I felt still there was a lot room for improvement. I was bit worried about the time constraint to complete the seminar. In the modified RMAN seminar contents, now, more practical examples with 11g RMAN new features and changes are also added.

The first thing which I did was installing 11g on Windows OS in my PC. I am more interested to test RMAN changes and testing Active Standby Database(snapshot database) in 11g database. I am also busy in preparing a new one day exclusive seminar on High Availability Data Guard.

In my next blog, I am going to discuss about my experience with Active Standby Database and findings.

Thank for your time,

Jaffar

An interesting situation with Standby database.

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