Dataguard test using flashback/restorepoint.

Pre chesk

 

 1.Verify FRA Space Availability at Primary and Standby end. – Start at 06:00PM IST – Offshore.

    SQL> SELECT file_type, sum(percent_space_used) used_space,

   100 - sum(percent_space_used) free_space,

   sum(percent_space_reclaimable) reclaimable_space,

  (100 - sum(percent_space_used)) + sum(percent_space_reclaimable) available_space

  FROM v$flash_recovery_area_usage

     Group by file_type;

 2. Run the FRA_CLEANUP to clear FRA Space.

 3. Check the FRA allocation

     SQL> Show Parameter DB_RECOVERY_FILE_DEST

4.Change the FRA allocation(If necessary)

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=<nn>G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='<absolute Path>';

 

Activating the Standby as Primary

 

Issue the following command on the primary to make the standby current

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

 

Check the apply Instance on the Standby

$ dgmgrl /

DGMGRL> show database <standby database>; (Check for apply Instance)

DGMGRL> exit

 

Stop Data Guard Broker on primary.

SQL>alter system set dg_broker_start=false scope=both sid=’*’;

 

Stop Data Guard Broker on Standby

SQL>alter system set dg_broker_start=false scope=both sid=’*’;

 

Stop the Redo apply on Standby Instance (as observed in step 3)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Create the Guaranteed Restore point on the Standby Database.

SQL> CREATE RESTORE POINT BEFORE_DR_TEST GUARANTEE FLASHBACK DATABASE;

 

Issue the following command on the primary to make the standby current

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

 

Disable the redo transport to standby

                          i.      Please find the LOG_ARCHIVE_DEST_n where the service is configured for redo transport

SQL> show parameters dest;

 

                        ii.      Disable the Redo Transport

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both sid=’*’;

 

Stop the Standby database

$ srvctl stop database –d <db_unique_name>

 

Disable all Services on Standby except the one identified for the DR

$srvctl disable service –d <db_unique_name> -s <service_name>

 

Start only first instance of Standby in mount state

$ srvctl start instance –d <db_unique_name> -i <Instance_name> -o mount

 

Activate the first instance of standby.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Remount the first Instance of standby.

SQL> STARTUP MOUNT FORCE;

 

Set the Protection mode for the first Instance of standby to ‘Maximum Performance’

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

 

Open the first instance of Standby

SQL> ALTER DATABASE OPEN;

Start the service identified for the DR on the Standby

$ srvctl start service –d <unique_db_name> -s <service_name>

 

 

Converting Activated Standby as Physical Standby

Shutdown all instances of Standby

$ srvctl stop database –d <db_unique_name>

 

Startup the first Instance of Standby in mount State

$ srvctl start instance –d <db_unique_name> -i <Instance_name> -o mount

 

Restore the activated standby back to the state it was of Physical Standby

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_DR_TEST;

 

Convert the Activated Standby to Physical Standby

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

 

Mount the Instance and Verify the Database Role.

SQL> STARTUP MOUNT FORCE;

SQL> SELECT database_role FROM V$DATABASE;

 

Enable all the disabled Services on Standby.

$srvctl enable service –d <db_unique_name> -s <service_name>

 

Shutdown and Restart all Instances of the Database.

$ srvctl stop database –d <db_unique_name>

$ srvctl start database –d <db_unique_name>

 

Start the Redo apply on preferred instance the Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Start the Data guard Broker on the standby.

SQL>alter system set dg_broker_start=true scope=both sid=’*’;

 

Enable the redo transport on the Primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

 

Start the Data guard Broker on the Primary

SQL>alter system set dg_broker_start=true scope=both sid=’*’;

 

Verify the  primary and standby are current

On the Primary

select thread#, max(sequence#) "Last Seq Generated"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

 

On the Standby

select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

 

select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and val.applied='YES' group by thread# order by 1;

 

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  (Repeat 12a and 12b to verify sync up)

 

 

Drop the Guaranteed Restore Point on the standby.

SQL> drop restore point BEFORE_DR_TEST;

 

Verify no services are up and running on the standby.

$ srvctl status service –d <unique_db_name>

 

Verify all Services up and running on the Primary.

$ srvctl status service –d <unique_db_name>

 

Restore FRA Changes on both Primary and Standby

 

Remove the standby target blackout from Grid.