Alter Database Convert To Snapshot Standby Hangs

 Alter Database Convert To Snapshot Standby Hangs

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later

"SQL > alter database convert to snapshot standby" 

hangs for indefinite time

alter database convert to snapshot standby
2021-02-09T12:39:12.909511-07:00
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/09/2021 12:39:12
.... (PID:5058): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]
.... (PID:5058): Begin: SRL archival

CAUSE 

snapshot standby conversion through sqlplus  hangs while clearing standby redologs

Solution

Follow below steps to resolve the issue

clear all standby redeologs manually

SQL > alter database clear logfile group <n>;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database convert to snapshot standby;

Example -

SQL> select group#,MEMBER from v$logfile where TYPE='STANDBY';

GROUP# MEMBER

---------- -------------------------------------------------         4 /clu01/WYOFNPRD/archivelog/WYPRDCDB/onlinelog/o1_mf_4_jk8xvopq_.log

SQL> alter database clear logfile group 4;

Database altered.

Doc - 2758384.1