Miladin Modrakovic's Blog: Oraclue

Converting physical standby to snapshot standby

Depens on Data guard configuration change protection mode:

On Primary database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  PERFORMANCE ;
SQL>  alter system archive log current;

Use these queries to check synchronization between primary and standby:

Primary: SQL> select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied=’YES’
group by thread# order by 1;

Stop logs shipment on primary database:
ALTER SYSTEM SET log_archive_dest_state_2=’DEFER’ SCOPE=BOTH SID=’*’;

On Physical Standby:

On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

Stop Redo Apply on standby , if it is active.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Ensure that the database is mounted, but not open.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> ALTER DATABASE OPEN;

Check database status:

select database_role,open_mode from v$database;

Start all instances and database services

srvctl  start service -d <database_name> -s <service_name>

Reverting back to physical standby

On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

Then on run this on instance which is still up:

SQL> shutdown immediate

SQL> startup mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> shutdown immediate

SQL> startup mount

Start redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

On primary database:

Start redo transport services:

SQL> alter system set log_archive_dest_state_2=enable scope=both sid=’*’;

SQL>  alter system archive log current;

Use queries from the top to verify that all logs are received and apply.