Converting Physical Standby Database to Snapshot Standby Database (Manually)

Converting Physical Standby Database to Snapshot Standby Database (Manually)

Below are the major steps:

Configure the flash recovery area, if it is not already done.

alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest= '/u01/oracleFRA';

Check the status of database before converting:
SQL> select name,status,open_mode,database_role,protection_mode from v$instance,v$database;
NAME      STATUS       OPEN_MODE       DATABASE_ROLE    PROTECTION_MODE
--------- ------------ --------------- ---------------- --------------------
DELL      MOUNTED      MOUNTED         PHYSICAL STANDBY MAXIMUM PERFORMANCE

Stop the recovery MRP process.
alter database recover managed standby database cancel;

Now convert physical standby database to snapshot standby
STANDBY> alter database convert to snapshot standby;
STANDBY> alter database open;

Check the status of database after converting:
SQL> select name,status,open_mode,database_role,protection_mode from v$instance,v$database;
NAME      STATUS       OPEN_MODE       DATABASE_ROLE    PROTECTION_MODE
--------- ------------ --------------- ---------------- --------------------
DELL      OPEN         READ WRITE      SNAPSHOT STANDBY MAXIMUM PERFORMANCE

Do some testing:

SQL> create table dba_objects_bkp as select * from dba_objects;
Table created.
SQL> select count(*) from dba_objects_bkp;
  COUNT(*)
----------
     74510
SQL> insert into dba_objects_bkp select * from dba_objects;
74510 rows created.
SQL> select count(*) from dba_objects_bkp;
  COUNT(*)
----------
    149020

After testing, now again we will convert the snapshot standby database back to a regular physical standby database:

Check the status of database before converting:
SQL> select name,status,open_mode,database_role,protection_mode from v$instance,v$database;
NAME      STATUS       OPEN_MODE       DATABASE_ROLE    PROTECTION_MODE
--------- ------------ --------------- ---------------- --------------------
DELL      OPEN         READ WRITE      SNAPSHOT STANDBY MAXIMUM PERFORMANCE

Now shutdown the standby standby database and open in mount state:
SQL> shut immediate;
SQL> startup mount;

Convert snapshot standby to physical standby database and shutdown the database and open in mount state:
alter database convert to physical standby;
shutdown immediate;
startup mount;

Now start the recovery MRP process
alter database recover managed standby database disconnect from session;

Check the status of database after converting:
SQL> select name,status,open_mode,database_role,protection_mode from v$instance,v$database;
NAME      STATUS       OPEN_MODE       DATABASE_ROLE    PROTECTION_MODE
--------- ------------ --------------- ---------------- --------------------
DELL      MOUNTED      MOUNTED         PHYSICAL STANDBY MAXIMUM PERFORMANCE

During the whole activity alertlog will show the below useful information:

[oracle@rac2 trace]$ tail -f alert_DELL.log
Mon Jan 20 19:31:08 2020
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/DELL_mrp0_5467.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1017666
MRP0: Background Media Recovery process shutdown (DELL)
Managed Standby Recovery Canceled (DELL)
Completed: alter database recover managed standby database cancel
Mon Jan 20 19:34:23 2020
db_recovery_file_dest_size of 40960 MB is 0.15% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jan 20 19:40:33 2020
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_01/20/2020 19:40:33
krsv_proc_kill: Killing 20 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1017666
Resetting resetlogs activation ID 4000706007 (0xee75edd7)
Online log /u01/app/oracle/DELL/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/DELL/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/DELL/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1017664
Mon Jan 20 19:40:34 2020
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
Mon Jan 20 19:42:30 2020
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
Mon Jan 20 19:42:30 2020
Assigning activation ID 4000911416 (0xee791038)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/DELL/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 20 19:42:30 2020
ARCa: Becoming the 'no SRL' ARCH
Mon Jan 20 19:42:30 2020
ARC9: Becoming the 'no SRL' ARCH
Mon Jan 20 19:42:30 2020
SMON: enabling cache recovery
[5351] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:952634 end:952704 diff:70 (0 seconds)
Dictionary check beginning
Mon Jan 20 19:42:30 2020
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/DELL/temp01.dbf
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jan 20 19:42:31 2020
QMNC started with pid=53, OS id=5978
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Mon Jan 20 19:42:32 2020
Starting background process CJQ0
Mon Jan 20 19:42:32 2020
CJQ0 started with pid=56, OS id=6006
Mon Jan 20 19:44:25 2020
ARC0: Becoming the 'no SRL' ARCH
Mon Jan 20 19:44:26 2020

Starting and stoping of MRP process:

alter database recover managed standby database
Media Recovery Start: Managed Standby Recovery (DELL)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 110 (in transit)
Mon Jan 20 20:49:01 2020
Recovery interrupted!
Media Recovery user canceled with status 1013
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (DELL)
Mon Jan 20 20:49:23 2020
MRP0 started with pid=56, OS id=7654
MRP0: Background Managed Standby Recovery process started (DELL)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...

alter database recover managed standby database cancel
Mon Jan 20 19:31:08 2020
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/DELL_mrp0_5467.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1017666
MRP0: Background Media Recovery process shutdown (DELL)
Managed Standby Recovery Canceled (DELL)
Completed: alter database recover managed standby database cancel

Few errors are also captured in alertlog:

Errors in file /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/DELL_dbw0_5217.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/DELL/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/DELL_dbw0_5217.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/DELL/temp01.dbf'
File 201 not verified due to error ORA-01157