Steps on converting a Snapshot Standby database to Physical Standby database

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

1) 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 a65
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;

2) 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

3) 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;

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

DGMGRL>

show configuration
convert database standby to physical standby;
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 convert to physical standby;

5) Start database in mount, if not already mounted.

The database is dismounted during this conversion and we have to restart the database, which will be the physical 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 read only> à If expected

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;

6) Enable Fast Start Failover if it configured and enabled before else skip this step.

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


DGMGRL>

show configuration
enable fast_start failover;
show configuration

7) Change protection mode from maximum performance to maximum protection/availability, whatever it is originally.

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

DGMGRL>

show configuration
edit configuration set protection mode as maxavailability / maxprotection;
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 availability / protection;

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;

8) Start Redo Apply, if it is not active.

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


DGMGRL>

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

show database 'STDBY';

  OR

STDBY.SYS.SQL>

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

alter database recover managed standby database disconnect from session;

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

9) Ensure that the database is in correct state 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;