Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode)

 Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode)

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;
   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
---------- ---------- ----------- -------------------- ----------
         1         75          75 04-DEC-2019 11:09:55          0
         2         69          69 04-DEC-2019 11:07:26          0
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      RAJDBA           PHYSICAL STANDBY MOUNTED
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             75
         2             69
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /b01/fra
db_recovery_file_dest_size           big integer 820G
SQL> alter database flashback_on on;
alter database flashback_on on
                            *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Once the standby database is mounted, convert the Physical standby database to snapshot standby

SQL> alter database convert to snapshot standby;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         RAJDBA           SNAPSHOT STANDBY READ WRITE
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
 
SELECT 
      database_incarnation# as Incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
FROM 
      v$restore_point
ORDER BY 4
/
INCAR                  SCN NAME                      TIME                                                                            STORAGE_SIZE GUA
----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
    3    6,086,495,469,099 SNAPSHOT_STANDBY_REQUIRED 04-DEC-19 11.24.09.000000000 AM                                                  209,715,200 YES
                           _12/04/2019 11:24:09
SQL> select open_mode from v$database;
Press Return to Continue
OPEN_MODE
--------------------
READ WRITE
SQL> !hostname

SQL>

Steps on converting back a snapshot standby database to physical standby database.


SQL> select open_mode from v$database;
Press Return to Continue
OPEN_MODE
--------------------
READ WRITE
SQL> !hostname

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Press Return to Continue
Total System Global Area 1.0737E+10 bytes
Fixed Size                  3722144 bytes
Variable Size            1275069536 bytes
Database Buffers         9428795392 bytes
Redo Buffers               29831168 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
Press Return to Continue
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      RAJDBA           PHYSICAL STANDBY MOUNTED
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
Press Return to Continue
RECOVERY_MODE
-----------------------
MANAGED