Steps on converting a Physical Standby database to Snapshot Standby database

Steps on converting a Physical Standby database to Snapshot Standby database:

1) Check if Flashback is enabled or not. For this conversion is achieved using flashback database, but the physical standby database does not need to have flashback database explicitly enabled. Even if the flashback is not enabled explicitly the conversion will work. But Flash Recovery Area should have configured on the physical standby database.

STDBY.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;

show parameter db_recovery_file_dest

2) Stop Redo Apply, if it is active.

$
dgmgrl sys/<password>@<PRIM/STDBY>


DGMGRL>

show database 'STDBY';
edit database 'STDBY' set state = 'APPLY-OFF';

show database 'STDBY';

  OR

STDBY.SYS.SQL>

select process, status, thread#, sequence#, block#, blocks from gv$managed_standby;

alter database recover managed standby database cancel;

select process, status, thread#, sequence#, block#, blocks from gv$managed_standby;

3) Disable Fast Start Failover if it enabled and configured.

$
dgmgrl sys/<Password>@<PRIM/STDBY>


DGMGRL>

show configuration
disable fast_start failover;
show configuration

4) Change protection mode to maximum performance if any other.

$
dgmgrl sys/<Password>@<PRIM/STDBY>

DGMGRL>

show configuration
edit configuration set protection mode as maxperformance;
show configuration

  OR
STDBY.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;

alter database set standby database to maximize performance;

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;

5) If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversion commands and it should be open in mount state. If it’s a standalone open it in mount state.

$
srvctl stop database -d STDBY -o immediate

srvctl start instance -d STDBY -i STDBY1 -o mount

  OR

STDBY.SYS.SQL>

shutdown immediate

startup mount

6) Ensure that the database is mounted and in sync with primary.

STDBY.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;

select arch.thread# "thread", arch.sequence# "last sequence received", appl.sequence# "last sequence applied", (arch.sequence# - appl.sequence#) "difference" from (select thread#, sequence# from gv$archived_log where (thread#, first_time ) in (select thread#, max(first_time) from gv$archived_log group by thread#)) arch, (select thread#, sequence# from gv$log_history where (thread#, first_time ) in (select thread#, max(first_time) from gv$log_history group by thread#)) appl  where arch.thread# = appl.thread# order by 1;

7) Perform the conversion from physical standby to snapshot standby database.

DGMGRL>

show configuration
convert database standby to snapshot standby;
show configuration

  OR

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;

alter database convert to snapshot standby;

8) The database is dismounted during this conversion and we have to restart the database, which will be the snapshot standby database.

$
srvctl stop instance -d STDBY -i STDBY1 -o immediate

srvctl start database -d STDBY

  OR

STDBY.SYS.SQL>

shut immediate

startup mount
alter database open

9) Check for database role, open mode & Restore point create by this conversion.

STDBY.SYS.SQL>
set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000
set trimspool on time on timi on feedback on verify off echo off heading on;
col name for a40
col scn for 9999999999999999999999
col time for a40
col guarantee_flashback_database for a30
col db_unique_name for a15

select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b;


select name, scn, time, guarantee_flashback_database, storage_size, database_incarnation# from gv$restore_point;