Flashback only standby database - 19c Dataguard

 ### On Primary: (before simulation)

SQL> select name, switchover_status,flashback_on from v$database;

SQL> alter system archive log current;

------------------------------------------------------------

### On Standby: (before simulation)

SQL> SET NUMFORMAT 999999999999999999

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> select name, time, scn from v$restore_point;

SQL> CREATE RESTORE POINT BEFORE_SIMULATION GUARANTEE FLASHBACK DATABASE; 

SQL> select name, time, scn from v$restore_point;

SQL> STARTUP MOUNT FORCE;

SQL> col space_limit for 999999999999999999999

SQL> col space_used for 999999999999999999999

SQL> select space_limit/1024/1024/1024 avail_GB, space_used/1024/1024/1024 Used_GB from v$recovery_file_dest;

SQL> col name for a20

SQL> col time for 999999999999999999999999999999

SQL> col time for a35

SQL> select name, time, scn from v$restore_point;

?SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

(if recovery needs to continue)

------------------------------------------------------------

### On Standby: (DR simulation invoked..)

?SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

(if recovery needs to be cancelled)

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

SQL> STARTUP MOUNT FORCE;

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> ALTER DATABASE OPEN;

SQL> select open_mode, database_role from v$database;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

SQL> select scn_to_timestamp(STANDBY_BECAME_PRIMARY_SCN) from v$database;

------------------------------------------------------------

### On Primary:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

### Handover to application team for testing DR Standby....

------------------------------------------------------------

### On Standby: (after bcp test - recovery / rollback)

SQL> select current_scn, standby_became_primary_scn from v$database;

        CURRENT_SCN STANDBY_BECAME_PRIMARY_SCN

------------------- --------------------------

     13223120496345             13222215208347

SQL> STARTUP MOUNT FORCE;

SQL> select name, time, scn from v$restore_point;

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_SIMULATION;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> STARTUP MOUNT FORCE;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

SQL> drop restore point before_simulation;

------------------------------------------------------------

### On Primary: (after bcp test - recovery / rollback)

SQL> alter system set log_archive_dest_state_2=enable;

SQL> alter system switch logfile;

------------------------------------------------------------

### make sure restore point is dropped..!

END.

The listener supports no services

New listener or new database cloned, listener not registered.

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.27.134)(PORT=1526)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER11

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                02-JAN-2021 21:43:33

Uptime                    0 days 0 hr. 0 min. 33 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /app/oracle/product/11.2.0.2/dbhome_1/network/admin/listener.ora

Listener Log File         /app/oracle/diag/tnslsnr/prjutyu01/listener11/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.27.134)(PORT=1526)))

The listener supports no services

The command completed successfully

LSNRCTL> 

Solution:

    (To register the listener with database)

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.27.134)(PORT=1526)))' scope=both;

SQL> alter system register;