Flashback DataGuard Database

Oracle Database - 11.2

Snapshot Standby

Convert to snapshot standby.

SQL> alter database recover managed standby database cancel;

Database altered.

~]$ srvctl stop database -d cocostd

SQL> startup mount
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size             905971376 bytes
Database Buffers         3019898880 bytes
Redo Buffers               13606912 bytes
Database mounted.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE    OPEN_MODE
---------- --------- ---------------- --------------------
         1 COCO      SNAPSHOT STANDBY MOUNTED

SQL> set lines 200
    col name for a20
    col time for a40
    select name, scn, time, database_incarnation#,
        guarantee_flashback_database,storage_size
        from v$restore_point;

NAME                        SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ---------------------------------------- --------------------- --- ------------
SNAPSHOT_STANDBY_REQ    2534154 12-FEB-24 05.19.29.000000000 PM                              2 YES     52428800
UIRED_02/12/2024 17:
19:29

SQL> select inst_id, name, database_role, flashback_on from gv$database;

   INST_ID NAME                 DATABASE_ROLE    FLASHBACK_ON
---------- -------------------- ---------------- ------------------
         1 COCO                 SNAPSHOT STANDBY RESTORE POINT ONLY

SQL> alter database open;

Database altered.

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME                 DATABASE_ROLE    OPEN_MODE
---------- -------------------- ---------------- --------------------
         1 COCO                 SNAPSHOT STANDBY READ WRITE

~]$ srvctl start instance -d cocostd -i coco2

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE    OPEN_MODE
---------- --------- ---------------- --------------------
         2 COCO      SNAPSHOT STANDBY READ WRITE
         1 COCO      SNAPSHOT STANDBY READ WRITE

Convert to physical standby.

~]$ srvctl stop database -d cocostd

SQL> startup mount
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size             905971376 bytes
Database Buffers         3019898880 bytes
Redo Buffers               13606912 bytes
Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size             905971376 bytes
Database Buffers         3019898880 bytes
Redo Buffers               13606912 bytes
Database mounted.
Database opened.

~]$ srvctl start instance -d cocostd -i coco2

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE    OPEN_MODE
---------- --------- ---------------- --------------------
         1 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY
         2 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select a.thread, a.received, b.applied
  2    from (select thread# thread, max(sequence#) received
  3           from v$archived_log
  4          group by thread#) a,
  5        (select thread# thread, max(sequence#) applied
         from v$log_history
  6    7          group by thread#) b
  8    where a.thread = b.thread
  order by 1;  9

    THREAD   RECEIVED    APPLIED
---------- ---------- ----------
         1         56         56
         2         59         59

Manually Activate Standby

Activate standby database.

SQL> alter system set log_archive_dest_state_2='defer' scope=both sid='*';

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on;

Database altered.

SQL> select inst_id, name, database_role, flashback_on from gv$database;

   INST_ID NAME      DATABASE_ROLE    FLASHBACK_ON
---------- --------- ---------------- ------------------
         1 COCO      PHYSICAL STANDBY YES
         2 COCO      PHYSICAL STANDBY YES

SQL> create restore point activate_dg guarantee flashback database;
Restore point created.

SQL> alter database activate standby database;

Database altered.

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE        OPEN_MODE
---------- --------- -------------------- ---------------
         1 COCO      PRIMARY              MOUNTED
         2 COCO      PRIMARY              MOUNTED

SQL> alter database open;

Database altered.

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE        OPEN_MODE
---------- --------- -------------------- ---------------
         1 COCO      PRIMARY              READ WRITE
         2 COCO      PRIMARY              MOUNTED

~]$ srvctl start instance -d cocostd -i coco2

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE        OPEN_MODE
---------- --------- -------------------- ---------------
         1 COCO      PRIMARY              READ WRITE
         2 COCO      PRIMARY              READ WRITE

Convert to physical standby.

~]$ srvctl stop database -d cocostd

~]$ srvctl start instance -d cocostd -i coco1 -o mount

SQL> set lines 200
    col name for a20
    col time for a40
    select name, scn, time, database_incarnation#,
        guarantee_flashback_database,storage_size
        from v$restore_point;

NAME                        SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ---------------------------------------- --------------------- --- ------------
ACTIVATE_DG             2523873 12-FEB-24 04.01.11.000000000 PM                              2 YES    104857600

SQL> flashback database to restore point ACTIVATE_DG;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size             905971376 bytes
Database Buffers         3019898880 bytes
Redo Buffers               13606912 bytes

SQL> alter database mount standby database;

Database altered.

SQL> drop restore point ACTIVATE_DG;

Restore point dropped.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

~]$ srvctl start instance -d cocostd -i coco2

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE    OPEN_MODE
---------- --------- ---------------- --------------------
         2 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY
         1 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';

SQL> select a.thread, a.received, b.applied
  from (select thread# thread, max(sequence#) received
         from v$archived_log
        group by thread#) a,
      (select thread# thread, max(sequence#) applied
         from v$log_history
        group by thread#) b
  where a.thread = b.thread
  order by 1;

    THREAD   RECEIVED    APPLIED
---------- ---------- ----------
         1         51         51
         2         54         54