CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE MANUALLY USING SQLPLUS

<<Back to Oracle DataGuard Main Page

HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE MANUALLY USING SQLPLUS

To open the physical standby database as snapshot standby, Flashback must be on.
If not already set, set db_recovery_file_dest and db_recovery_file_dest_size parameter to enable the flashback.

SQL> alter system set db_recovery_file_dest='/ora_data/orcl/flash';
System altered.

SQL>alter system set db_recovery_file_dest_size=30G;
System altered.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
ORCL1P     MOUNTED              NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> Startup

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
ORCL1P     READ WRITE           NONE    SNAPSHOT STANDBY


NOTE: You must calculate and adequately set the value of db_recovery_file_dest_size parameter. The value depends on the amount of data changes to be done while the database is in snapshot standby mode.

CONVERTING SNAPSHOT STANDBY DATABASE BACK TO PHYSICAL STANDBY DATABASE USING SQLPLUS

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
ORCL1P     READ WRITE           NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> Startup nomount

SQL> alter database mount;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

SQL> shut immediate

SQL> alter database mount standby database;
Database altered.

SQL>  recover managed standby database disconnect from session;
Media recovery complete.

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
ORCL1P     MOUNTED              NONE    PHYSICAL STANDBY


Some Important Info from Alertlog
=======================================================================
From Physical Standby to Snapshot Standby

Fri Feb 28 11:25:21 2020
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/28/2020 11:25:21
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 5566956250298
Resetting resetlogs activation ID 268691150 (0x1003e6ce)
......................................
......................................
......................................
Standby became primary SCN: 5566956250296
Fri Feb 28 11:25:22 2020
Setting recovery target incarnation to 4
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
=======================================================================

From Snapshot Standby to Physical Standby 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ORCL1P)
Flashback Restore Start
Fri Feb 28 11:29:36 2020
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 410770031 (0x187bda6f)


Related Posts

Step by Step How to Activate Physical Standby Database

How to Convert Physical Standby Database to Snapshot Standby Using dgmgrl