Oracledna

    Convert Oracle Physical Standby Database to the Snapshot Standby Database

    • oracledna
    • DataGuard
    • March 2, 2026

    This post demonstrates the convert the Oracle Physical Standby Database to the Snapshot Standby Database.

    The snapshot standby database allows write operations and developers can perform their testing activities. The Snapshot standby database will accept incoming archive redo logs, but does not apply the redo data . This redo data applied automatically after reverting the snapshot standby database to the physical standby database.

    1. Configure flash recovery area in physical standby database, if not already configured.


    SQL> @db
    NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME
    ———- ————— ———- —————— ————— —————- ——————
    PROD stdby MOUNTED PHYSICAL STANDBY stdby stdby 03-MAR-2026 00:17

     

    SQL> show parameter db_recovery
    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string
    db_recovery_file_dest_size big integer 0


    SQL> alter system set db_recovery_file_dest_size=10G scope=both;
    System altered.


    SQL> alter system set db_recovery_file_dest=’/u01/arch/stdby’ scope=both;
    System altered.


    SQL> show parameter db_recovery

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string /u01/arch/stdby
    db_recovery_file_dest_size big integer 10G

     

    2. Stop the managed recovery (MRP), if it is active.

    SQL> alter database recover managed standby database cancel;
    Database altered.

    3. Convert physical standby database to snapshot standby database.


    SQL>select name,open_mode,database_role from v$database;

    NAME OPEN_MODE DATABASE_ROLE
    ———- ———- ——————
    PROD MOUNTED PHYSICAL STANDBY

     

    SQL> alter database convert to snapshot standby;
    Database altered.

     

    SQL> select name,open_mode,database_role from v$database;

    NAME OPEN_MODE DATABASE_ROLE
    ———- ———- ——————
    PROD MOUNTED SNAPSHOT STANDBY

     

    SQL> alter database open;
    Database altered.

     

    SQL> select name,open_mode,database_role from v$database;
    NAME OPEN_MODE DATABASE_ROLE
    ———- ———- ——————
    PROD READ WRITE SNAPSHOT STANDBY

     

     

     

    From Standby alert log==
    ==================
    alter database convert to snapshot standby
    Starting background process RVWR
    2026-03-03T01:03:18.416253+05:30
    RVWR started with pid=26, OS id=16523
    2026-03-03T01:03:19.626142+05:30
    Allocating 4194304 bytes in shared pool for flashback generation buffer.
    Allocated 4194304 bytes in shared pool for flashback generation buffer
    Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/03/2026 01:03:18
    …. (PID:13893): Killing 3 processes (PIDS:3310,3382,3314) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 13893
    2026-03-03T01:03:19.764938+05:30
    Process termination requested for pid 3310 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]
    2026-03-03T01:03:19.765139+05:30
    Process termination requested for pid 3382 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]
    2026-03-03T01:03:19.765285+05:30
    Process termination requested for pid 3314 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]
    2026-03-03T01:03:21.790486+05:30
    …. (PID:13893): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]
    …. (PID:13893): Begin: SRL archival
    …. (PID:13893): End: SRL archival
    RESETLOGS after incomplete recovery UNTIL CHANGE 2275208 time 03/03/2026 00:59:45
    Resetting resetlogs activation ID 608653432 (0x24475078)
    Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously cleared
    Standby became primary SCN: 2275206
    2026-03-03T01:03:22.088801+05:30
    Setting recovery target incarnation to 3
    2026-03-03T01:03:22.343169+05:30
    …. (PID:13893): Redo network throttle feature is disabled at mount time
    CONVERT TO SNAPSHOT STANDBY: Complete – Database mounted as snapshot standby
    Completed: alter database convert to snapshot standby

    4. Perform testing.

    SQL> create user ram identified by ram123 default tablespace users temporary tablespace temp;
    User created.

    SQL> grant create session to ram;
    Grant succeeded.

    SQL> grant create table to ram;
    Grant succeeded.

    SQL> conn ram/ram123
    Connected.

    SQL> create table test (id number(2), name varchar(20));
    Table created.


    SQL> insert into test (id,name) values(1,’RAM’);
    1 row created.

    SQL> commit;
    Commit complete.


    SQL> select * from test;

    ID NAME
    ———- ———-
    1 RAM

    Disclaimer:

    Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.

    Popular Posts
    Categories