ORA-01620 while creating a RAC standby for non-RAC primary

Today I was trying to test some thing and had to create a RAC standby for a Non-RAC (single-instance) Primary database. I know, it doesn't make sense to have a RAC standby for a single-instance primary, but that's not the point. First instance was mounted without any problems, but when attempted to mount the second standby instance got the following error message:

SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01620: no public threads are available for mounting

I couldn't find quick notes on the web to get around this issue and therefore had to find my own for this unique situation (having a RAC standby for SI-Primary). Here is what I did on Primary:

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 13 size 5M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 14 size 5M;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

Then created new copy of standby controlfile and shipped it over to standby and attempted to mount both the instances, it worked fine! Of course, had the primary been a RAC, we wouldn't have seen this in first place :-) BTW, this is on Oracle11g 11.1.0.7

Tablespace Point-in-time Recovery (TSPITR) using RMAN

Scenario: A logical corruption occurred due to an user error and have to quickly get back to the state just before the time of the error. Possible options: Database point-in-time recovery, but the downside is the entire database has to be unavailable and other transactions after the PITR would be lost. The other option would be Tablespace Point-in-time-Recovery (TSPITR) - the obvious choice here. I had known that RMAN could be used to perform TSPITR, but I realized just today how powerful, simple, automated, error-free and fast it could be to perform TSPITR using RMAN - it's really amazing. The intent of this post is just to high-light and appreciate the usefulness of RMAN to perform TSPITR.

Assuming we have the latest backup, just executing the following statement would do everything needed to get the entire tablespace to a point-in-time in the past. Believe me, you don't have to do anything other than just this statement!


recover tablespace APP_DATA until time
"to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')"
auxiliary destination ’/opt/oracle/temp’;

Once the above statement is executed, RMAN does the following for us:

• Creates auxiliary instance (including the pfile etc..)
• Mounts the auxiliary instance
• Makes the candidate tablespace into OFFLINE
• Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
• Applies archives (completes recovery)
  
• Opens the database
• Performs an export of the objects residing in the tablespace
• Shutdown aux instance
• Import the objects into the target database
• Remove aux instance and cleanup

That's it....you now have all the objects back in the tablespace!.

You can view the output of the RMAN session by downloading this file.. which is quite self-explanatory and informative too!