Data Guard: Using a Physical Standby database for testing

Here is a test I made. Usually, a standby database is used to provide contingency. But
we can use it sometimes for some testing. Let's say we want to try a job, to check some performance issues, to test a new application, etc.. but we do not want to do it in the main database. Here is a way to do it:



BEWARE:

We use guaranteed restore points. If you create tablespaces in the standby database for the test phase, there is a bug that will not let you drop them.

Bug 14163359 : ORA-38881: CANNOT DROP TABLESPACE DUE TO GUARANTEED RESTORE POINTS

SQL> drop tablespace mig including contents and datafiles;
drop tablespace mig including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace MIG on primary database due to guaranteed
restore points.


SQL>

So, now:

We start with a environment with one primary database (PRIMARY) and one Physical Standby database (STBY). Data Guard Broker is configured.

A. ON STANDBY DATABASE:

A.1 First, we configure a recovery area with enough space (in case we do not have):
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;

    System altered.

    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/BDARCH';

          System altered.

    SQL>

A.2 Cancel Redo Apply and create a guaranteed restore point:
     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

          Database altered.

     SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;

          Restore point created.

      SQL>

A.3  Let's review the restore point details:

     SQL> select NAME,SCN,TIME from v$restore_point;

NAME                             SCN TIME
------------------------- ---------- ---------------------------------------------------------------------------
STANDBY_FLASHBACK_TESTING     648446 13-APR-16 09.39.47.000000000 AM

     SQL>



B ON PRIMARY DATABASE:

B.1  Now, we do a switch of redo logs so they are propagated to the standby database. On this way we make sure that the restore point SCN is archived in the standby database (If is a RAC environment, do some switches, not just one):
 To check the maximum sequence we need we can use this query:
 select SEQUENCE# ,first_change#, next_change# from  v$archived_log where <restore point SCN > between first_change# and NEXT_CHANGE#;


SQL> alter system archive log current;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>


B.2  We check that the archivers have been sent to the standby database, but have not been applied (since redo apply is disabled):
     ON PRIMARY:
    
SQL> select sequence#, applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
        24 YES
        25 NO
        25 YES
        26 NO
        26 YES
        27 NO
        27 YES
        28 NO
        28 NO
        29 NO
        29 NO



ON STANDBY:

SQL> select sequence#, applied from v$archived_log;

SQL>  SEQUENCE# APPLIED
---------- ---------
        24 YES
        25 NO
        25 YES
        26 NO
        26 YES
        27 NO
        27 YES
        28 NO
        28 NO
        29 NO
        29 NO



C ON PRIMARY DATABASE:

C.1 Stop the propagation of archivers  to the standby:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.

SQL>


D ON STANDBY:

D.1   Activate the Physical Standby:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL>

D.2  We verify that the status of the controlfile has changed from Standby to Current:

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT

SQL>


D.3  Open the standby database. (can take a while):

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; (tested without standby redo logs)

SQL> alter database open;




Database altered.

SQL>

D.3  No we can do any test we need. Create tables, objects, run jobs, etc.


E ON STANDBY

E.1 Now we revert the process. We mount the standby database:

SQL> STARTUP MOUNT FORCE;
ORACLE instance started.

Total System Global Area  396668928 bytes
Fixed Size                  2253624 bytes
Variable Size             125832392 bytes
Database Buffers          264241152 bytes
Redo Buffers                4341760 bytes
Database mounted.
SQL>


E.2 Flashback to the restore point:

SQL>  FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;

Flashback complete.

SQL>

E.3 Verify that the status of the controlfile has changed to BACKUP:

SQL>  select controlfile_type from v$database;

CONTROL
-------
BACKUP

SQL>

E.4 Convert to standby database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> STARTUP MOUNT FORCE;
ORACLE instance started.

Total System Global Area  396668928 bytes
Fixed Size                  2253624 bytes
Variable Size             125832392 bytes
Database Buffers          264241152 bytes
Redo Buffers                4341760 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.


E.5 Verify that the status of the controlfile has changed to STANDBY:

SQL> select controlfile_type from v$database;

CONTROL
-------
STANDBY

SQL>


E.6. Verify that the older archive logs (the ones generated since we have suspended the propagation) are being applied.

SQL> select sequence#, applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
        20 YES
        21 YES
        22 YES
        23 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 NO
        29 NO
        30 YES

 SEQUENCE# APPLIED
---------- ---------
        28 YES
        29 YES

35 rows selected.

SQL>


F ON PRIMARY:

F.1 Enable the sending of archivelogs to the standby and verify it is working:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL>
 ON PRIMARY:

  SQL> select sequence#, applied from v$archived_log;
    SEQUENCE# APPLIED
---------- ---------
        30 NO
        30 YES
        28 YES
        29 YES
        31 NO
        31 YES

83 rows selected.

SQL>

  ON STANDBY:

 
   SQL> select sequence#, applied from v$archived_log;
      SEQUENCE# APPLIED
---------- ---------
        20 YES
        21 YES
        22 YES
        23 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 NO
        29 NO
        30 YES

 SEQUENCE# APPLIED
---------- ---------
        28 YES
        29 YES
        31 YES

36 rows selected.

SQL>


G ON STANDBY.

G.1 We  open the standby in READ ONLY to check that the changes have been undone:


SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> <check if what we did is undone>

G.2 Eliminate the restore point:

SQL> STARTUP FORCE MOUNT;
ORACLE instance started.

Total System Global Area  396668928 bytes
Fixed Size                  2253624 bytes
Variable Size             125832392 bytes
Database Buffers          264241152 bytes
Redo Buffers                4341760 bytes
Database mounted.
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> DROP RESTORE POINT Standby_flashback_testing ;

Restore point dropped.

SQL>


Check that the broker is correctly configured.