ACTIVATE FLASHBACK IN THE STANDBY DATABASE

ACTIVATE FLASHBACK IN THE STANDBY DATABASE

STANDBY CONVERSION --

Enable flashback on the standby

Create a restore point on standby

Put the  standby DB in active mode

Cancel / defer the log shipping on primary

Once the tasks are done using the standby database revert to standby database using flashback

SQL>  select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
TESTDB   TESTDB                        PHYSICAL STANDBY NOT ALLOWED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     92
db_recovery_file_dest                string      /u01/app/flash
db_recovery_file_dest_size           big integer 250G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL>
SQL>
SQL> show parameter db_flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2800
SQL>

SQL> SELECT open_mode FROM V$DATABASE;

select banner from v$version;
OPEN_MODE
--------------------
MOUNTED

select instance_name from gv$instance;
set linesize 125
set pages 9999
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from gv$instance;

Check MRP
--------

select instance_name from gv$instance;
set linesize 125
set pages 9999
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from gv$instance;


SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%';

PROCESS   DELAY_MINS
--------- ----------
MRP0               0

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

HOW TO OPEN THE PHYSICAL STANDBY FOR READ/WRITE TESTING AND FLASHBACK

SHUTDOWN IMMEDIATE
STARTUP MOUNT
SQL> create restore point save_point_export guarantee flashback database;

Restore point created.

SQL>
SQL>
SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
SAVE_POINT_EXPORT


SQL> select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                      PHYSICAL STANDBY YES

SQL>  alter database activate standby database;

Database altered.

SQL> select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                        PRIMARY          YES

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB     MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB READ WRITE           PRIMARY

HOW TO REVERT TO THE PHYSICAL STANDBY AFTER READ/WRITE TESTING

• Resume      standby apply

SWITCH BACK TO STANDBY
-----------------------------------------


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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB   READ WRITE           PRIMARY

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
SAVE_POINT_EXPORT09OCT18

SQL>
SQL>
SQL> shutdown 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.

Total System Global Area 1.0737E+10 bytes
Fixed Size                  4498208 bytes
Variable Size            4529855712 bytes
Database Buffers         6174015488 bytes
Redo Buffers               29048832 bytes
Database mounted.
SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
SAVE_POINT_EXPORT09OCT18

SQL>
SQL>
SQL> flashback database to restore point SAVE_POINT_EXPORT09OCT18;


Flashback complete.

 SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
SAVE_POINT_EXPORT09OCT18

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB   MOUNTED              PRIMARY

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

SQL> alter database convert to physical standby;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB   MOUNTED              PHYSICAL STANDBY

SQL>  select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                        PHYSICAL STANDBY YES

SQL>
SQL>
SQL> alter database flashback off;

Database altered.

SQL> select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                        PHYSICAL STANDBY RESTORE POINT ONLY

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0737E+10 bytes
Fixed Size                  4498208 bytes
Variable Size            4529855712 bytes
Database Buffers         6174015488 bytes
Redo Buffers               29048832 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>  select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                        PHYSICAL STANDBY RESTORE POINT ONLY

SQL> drop restore point SAVE_POINT_EXPORT;

Restore point dropped.

SQL>   select db_unique_name,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
TESTDB                        PHYSICAL STANDBY NO