Convert Physical Standby database to Snapshot standby database

Convert Database to READ ONLY

create a test table on Primary Database on Primary Server (Primary)

sqlplus / as sysdba

SQL> CREATE TABLE TEST_STDBY_DATE (Name varchar2(2000));

Table created.

SQL> insert into TEST_STDBY_DATE values('This is PRODDB Test on '||to_char(sysdate,'YYYYMMDD HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

alter system switch all logfile;

alter system switch all logfile;

alter system switch all logfile;

2) Make sure all Log Shipping is completed and NO Gaps exists

- Run below quesries on both Primary and Standby and make sure all the recent logs are applied

select thread#,max(sequence#) from v$archived_log group by thread#;

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

select thread#,max(sequence#) from v$archived_log where completion_time >sysdate-1  group by thread#;

select process,status,sequence# from v$managed_standby;

- Verify missing archive logs

sqlplus / as sysdba

select * from gv$archive_gap;

Stop the Recovery and open database in READ ONLY Mode

As oracle on Standby server

. <dbname>dr

- Stop Managed Recovery

sqlplus / as sysdba

SQL> alter database recover managed standby database cancel;

SQL> ALTER DATABASE OPEN read only;

- Very test table

SQL>  select * from  TEST_STDBY_DATE;

Note : If the above is successful, bring back the database in Recovery Mode

1) Change database back to Managed Recovery Mode

sqlplus / as sysdba

SQL>shutdown immediate

$sqlplus ‘/as sysdba’

SQL>startup mount;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL>select open_mode,database_role from v$database;

2) Monitor the Alert.log and validate the new Logs are getting applied

1. Stop the standby database 

server[oracle]_db>sqlplus ‘/as sysdba’

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate;

2. Start the database in mount status and check the log sequence and make sure the logs are applied

server[oracle]_db> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 25 15:55:41 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

nolog>startup mount

ORACLE instance started.

Total System Global Area 6413680640 bytes

Fixed Size                  2265224 bytes

Variable Size            1543507832 bytes

Database Buffers         4848615424 bytes

Redo Buffers               19292160 bytes

Database mounted.

nolog> 

db> @sby

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                   2690                  2689          1

         2                   2590                  2589          1

         3                   2591                  2590          1

         4                   2625                  2624          1

3. Convert the database to snapshort standby

db> alter database recover managed standby database disconnect from session; 

Database altered.

db> !ps -ef |grep mrp

oracle   24689     1  0 14:06 ?        00:00:00 ora_mrp0_db1

oracle   25097 24205  0 14:07 pts/15   00:00:00 /bin/ksh -c ps -ef |grep mrp

oracle   25099 25097  0 14:07 pts/15   00:00:00 grep mrp

db> select max(sequence#) from v$archived_log where applied='YES' ;

MAX(SEQUENCE#)

--------------

          2690

db> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

ENSURE FRA IS CONFIGURED BEFORE YOU CONVERT TO SNAPSHOT STANDBY

db> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

db> 

5. Shutdown the database and open in readwrite mode

sqlplus / as sysdba

shutdown immediate

Exit

sqlplus / as sysdba

startup;

select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

READ WRITE           SNAPSHOT STANDBY

SQL> set lines 300

SQL> select NAME,SCN,TIME from v$restore_point;

NAME

---------------------------------------------------------------------------------

       SCN TIME

---------- -----------------------------------------------------------------------

SNAPSHOT_STANDBY_REQUIRED_10/28/2020 17:02:08

1.6197E+13 28-OCT-20 05.02.08.000000000 PM

Rolling back the changes done for DR

1. Stop the database and convert to physical standby

sqlplus / as sysdba

shutdown immediate

Exit

sqlplus  / as sysdba

startup mount;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered

SQL> Shutdown immediate

2. Starting the database for recovery mode

sqlplus “/as sysdba”

startup mount;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION ;

Database altered.

3. Check the restore poing and recovery process

Checking restore poing is nomore

SQL> select NAME,SCN,TIME from v$restore_point;

no rows selected 

checking the recovery in progress

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

--------- -------- ---------- ---------- ----------

RFS       LGWR              2         83       3495

RFS       LGWR              1         87       4407

MRP0      N/A               2         83       3495

4. Check all the archives are synched and applied.

DB> @sby

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                   2690                  2689          0

         2                   2590                  2589          0

         3                   2591                  2590          0

         4                   2625                  2624          0

Ensure you set below configuration in prod and standby

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;   Standby

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;   Prod