Convert physical standby to standalone database
09:02 01 Jul 2014

I have a standard primary / physical-standby configuration (one primary, one standby), and would like to completely break the connection between primary and standby; converting physical standby to development database that is dissociated from primary/production system.

This seems like it should be fairly trivial, but doesn't really follow any documented procedures that I can find (most of which focus on switching over to standby for recovery purposes).

My gameplan is to activate the standby database by "simulating" a failover (without the primary actually failing, of course), following instructions here:
8.2.2 Performing a Failover to a Physical Standby Database http://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB5176

This would entail running following on primary:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

And then, if no errors, running this on standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
     (confirm result is TO PRIMARY or SESSIONS ACTIVE)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Then, on both primary and standby, bring up in OPEN mode and disable further replication:

SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';

I have 3 problems/questions with this approach:

  • As a general statement, this feels a little overly complex - is there a more direct way to do this? For example, I'd like to be able to do this without having to shutdown primary database (which is required in order to run ALTER SYSTEM FLUSH REDO TO target_db_name;).
  • Current output of SELECT SWITCHOVER_STATUS FROM V$DATABASE; on standby is "NOT ALLOWED". I assume this status will change with the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; but would like confirmation of this, as I don't want to find myself in trouble-shooting mode.
  • Is ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''; the clean way to shutdown replication? Should I instead ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='disable'?

    • database oracle-database