Oracle Tips and Tricks — David Fitzjarrell

November 14, 2016

“That’s Not What I Wanted.”

Filed under: General — dfitzjarrell @ 11:40

"Let me try once more," Milo said in an effort to explain. "In other words--"
"You mean you have other words?" cried the bird happily. "Well, by all means, 
use them. You're certainly not doing very well with the ones you have now."
-- Norton Juster, The Phantom Tollbooth

A recent question in the Oracle database forums has a user wanting to open his physical standby database in READ WRITE mode but still keep it as a physical standby. He found this blog post, followed the instructions and did, indeed, get what used to be his standby database opened in READ WRITE mode. What the blogger failed to mention was that his instructions performed a failover of the standby to primary, and did so in a manner that Oracle does not recommend.

To be honest there isn’t a way to open a physical standby database READ WRITE, keep the primary as a primary and maintain the physical standby as a physical standby. Active Data Guard allows the physical standby to be opened READ ONLY while continuing to apply redo to keep the standby in synch with the primary. (This option has extra cost as it requires additional licensing.) Once a failover has occurred the only recourse is to rebuild the standby database; it can’t be simply ‘brought back’ at the will and whim of the DBA.

What was suggested by the author of that blog post was to use:

alter database activate standby database;

which WILL eventually open the standby READ WRITE, failing it over to be the new primary in the process. Yet Oracle, in the documentation, clearly does not recommend using this option:


Performs a failover. The standby database must be mounted before it can be activated with this statement.

Note: Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to failover because it causes
data loss. Instead, use the following best practices:

For physical standby databases, use the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement 
with the FINISH keyword to perform the role transition as quickly as possible with little or no data loss
and without rendering other standby databases unusable.

For logical standby databases, use the ALTER DATABASE PREPARE TO SWITCHOVER and

Notice the warning given by Oracle:

Note: Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to failover because it causes
data loss.

A far better option, although not permanent, would be to execute a switchover, where the primary and physical standby switch roles for a period of time before restoring the physical standby to its original role as PHYSICAL STANDBY. This takes more work to accomplish but it is reversible. An example of a switchover is shown below:

Ensure that no gaps exist in the redo apply:

SQL> select status, gap_status
  2  from v$archive_dest_status
  3  where dest_id = 2;
--------- ------------------------

Make certain that the temp file name or names on the standby match those on the primary.  On the primary execute:

SQL> select file_name from dba_temp_files;

and on the standby execute:

SQL> select name from v$tempfile;

There may be a delay configured for redo apply on the standby; to determine if a delay is configured execute:

SQL> select delay_mins from v$archive_dest where dest_id = 2; 

(If the DELAY_MINS is greater than 0 the delay can be removed by executing:



Check the SWITCHOVER_STATUS on the primary:

SQL> select switchover_status from v$database;

Valid values are TO STANDBY and SESSION ACTIVE.  Any other value signals that the redo transport 
isn't functioning and the switchover will fail.  If the status is SESSION ACTIVE query the
V$SESSION view for active sessions:

SQL> select sid, process, program
  2  from v$session where type = 'USER';

       SID PROCESS                  PROGRAM
---------- ------------------------ ---------------------------------------
         6 16952:11396              sqlplus.exe
        17 15728                    ORACLE.EXE (J002)
        18 10668                    ORACLE.EXE (J006)
       131 13196                    ORACLE.EXE (J003)
       134 16576:16772              sqlplus.exe
       135 9720                     ORACLE.EXE (J007)
       244 17404                    ORACLE.EXE (J004)
       248 17844                    ORACLE.EXE (J008)
       254 12992                    ORACLE.EXE (J000)
       364 8280                     ORACLE.EXE (J005)
       366 2320                     ORACLE.EXE (J001)

11 rows selected.


When everything looks good execute the switchover; if the status is TO STANDBY the command is:

SQL> alter database commit to switchover to physical standby;
Database altered.


If the status is SESSION ACTIVE you should modify the command as shown below:

SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.

Shutdown the 'new' standby and afterwards mount it:

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.

If all has gone according to plan the alert log on the 'new' standby should report:

Successful mount of redo thread 1, with mount id ...
Physical Standby Database mounted.

It's now time to check the 'old' standby for SWITCHOVER_STATUS.  The two valid values are  

SQL> select switchover_status from v$database;

Since no sessions are active the WITH SESSION SHUTDOWN clause will not be necessary:

SQL> alter database commit to switchover to primary;
Database altered.

Open the 'new' primary database:

SQL> alter database open;
Database altered.

Looking in the alert log you should see:

Standby became primary SCN: ...
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary

Check the LOG_ARCHIVE_DEST_n parameters and set up a new destination, if necessary:

SQL> alter system set log_archive_dest_2='service="yumpo" LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="yumpo" net_timeout=30 valid_for=(all_logfiles,primary_role)' scope = both;

System altered.

Start the redo apply on the 'new' standby:

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

It's a good idea to verify that the redo transport is working; execute at least two log switches
on the 'new' primary to prove the redo is being transported:

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
System altered.
SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;
--------- ------------------------

Presuming the status is VALID and there is no gap the switchover is complete.

To reverse the switchover the same steps are applied, starting at the ‘new’ primary and ending with the ‘new’ standby. Should there be no problems the primary database will be returned to the PRIMARY role, likewise the physical standby will return to the PHYSICAL STANDBY role. In between the switchover steps the original physical standby will be temporarily the primary database, opened in READ WRITE mode.

If you have Data Guard Broker configured a switchover is considerably easier to execute; you can read the Oracle documentation for a complete example.

Understanding Data Guard/Standby concepts is essential for knowing what can, and cannot, be done. And blog advice, even from this one, should be investigated before taking any action as the title and text may not accurately reflect what the recommended actions actually accomplish. It would be a shame to put yourself in an unrecoverable situation just because you jumped first and looked later.

In other words …


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: