Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
It may not be required to apply Redos to the Standby side immediately. In this case, the DelayMins in the Broker and the Delay parameter in the LOG_ARCHOVE_DEST_n are set and the Redos are applied late to the Standby side.
LET’S TEST
1. The parameter is updated in the database where Redos are requested to be applied after a certain delay.
1 2 | DGMGRL> edit database 'physical' set property 'DelayMins'=1; Property "DelayMins" updated |
2. When the parameter is updated, the LOGs that fall into ALERT LOGs are as follows.
[Physical]—–>ALERT LOG1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Sat Mar 04 23:21:12 2017 MRP0: Background Media Recovery cancelled with status 16037 Sat Mar 04 23:21:12 2017 Errors in file /u01/app/oracle/diag/rdbms/physical/physical/trace/physical_pr00_17506.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 2324478 Sat Mar 04 23:21:12 2017 Errors in file /u01/app/oracle/diag/rdbms/physical/physical/trace/physical_pr00_17506.trc: ORA-16037: user requested cancel of managed recovery operation Sat Mar 04 23:21:12 2017 MRP0: Background Media Recovery process shutdown (physical) Sat Mar 04 23:21:13 2017 Managed Standby Recovery Canceled (physical) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING ARCHIVED LOGFILE Sat Mar 04 23:21:13 2017 Attempt to start background Managed Standby Recovery process (physical) Starting background process MRP0 Sat Mar 04 23:21:13 2017 MRP0 started with pid=53, OS id=17538 Sat Mar 04 23:21:13 2017 MRP0: Background Managed Standby Recovery process started (physical) Sat Mar 04 23:21:18 2017 Started logmerger process Sat Mar 04 23:21:18 2017 Managed Standby Recovery not using Real Time Apply Sat Mar 04 23:21:18 2017 Parallel Media Recovery started with 4 slaves Sat Mar 04 23:21:18 2017 Waiting for all non-current ORLs to be archived... Sat Mar 04 23:21:18 2017 All non-current ORLs have been archived. Sat Mar 04 23:21:18 2017 Media Recovery Waiting for thread 1 sequence 95 (in transit) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING ARCHIVED LOGFILE Sat Mar 04 23:21:20 2017 Archived Log entry 105 added for thread 1 sequence 95 ID 0x69ce60a7 dest 1: RFS[11]: Selected log 4 for thread 1 sequence 96 dbid 1775091820 branch 937609135 Sat Mar 04 23:21:20 2017 Media Recovery Log /u01/app/oracle/fast_recovery_area/PHYSICAL/archivelog/2017_03_04/o1_mf_1_95_dcpcyjl7_.arc Media Recovery Waiting for thread 1 sequence 96 (in transit) Sat Mar 04 23:21:21 2017 Archived Log entry 106 added for thread 1 sequence 96 ID 0x69ce60a7 dest 1: Sat Mar 04 23:21:21 2017 Primary database is in MAXIMUM PERFORMANCE mode RFS[12]: Assigned to RFS process (PID:17566) RFS[12]: Selected log 4 for thread 1 sequence 97 dbid 1775091820 branch 937609135 Sat Mar 04 23:21:21 2017 Media Recovery Log /u01/app/oracle/fast_recovery_area/PHYSICAL/archivelog/2017_03_04/o1_mf_1_96_dcpcykon_.arc Media Recovery Waiting for thread 1 sequence 97 (in transit) |
1 2 3 4 5 | [oracle@physical ~]$ ps -ef | grep rfs oracle 17702 16688 0 23:26 pts/3 00:00:00 grep rfs [oracle@physical ~]$ ps -ef | grep 17566 oracle 17566 1 0 23:21 ? 00:00:00 oraclephysical (LOCAL=NO) oracle 17707 16688 0 23:26 pts/3 00:00:00 grep 17566 |
1 2 3 4 5 6 7 8 9 | [Primary - 04-03-2017 23:34:58] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- DEPARTMENTS_YEDEK JOBS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 7 8 9 | [Physical - 04-03-2017 23:34:58] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- DEPARTMENTS_YEDEK JOBS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 7 8 9 | [Logical - 04-03-2017 23:34:58] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- DEPARTMENTS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary - 04-03-2017 23:35:03] SQL> drop table test.DEPARTMENTS_YEDEK; Table dropped. [Primary - 04-03-2017 23:35:17] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 7 8 9 | [Physical - 04-03-2017 23:35:03] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- DEPARTMENTS_YEDEK JOBS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 7 8 | [Logical - 04-03-2017 23:35:03] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- LOCATIONS_YEDEK REGIONS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK |
5. By performing the Log Switch operation, Redo, which hosts the DROP operation, is transferred to the Stanbdy side.
1 2 3 | [Primary - 04-03-2017 23:38:11] SQL> alter system switch logfile; System altered. |
[Physical]—–>ALERT LOG
1 2 3 4 5 6 7 8 | Sat Mar 04 23:38:30 2017 Archived Log entry 107 added for thread 1 sequence 97 ID 0x69ce60a7 dest 1: Sat Mar 04 23:38:30 2017 ARC5: Archive log thread 1 sequence 97 available in 1 minute(s) Sat Mar 04 23:38:30 2017 RFS[12]: Selected log 4 for thread 1 sequence 98 dbid 1775091820 branch 937609135 Sat Mar 04 23:38:30 2017 Media Recovery Delayed for 1 minute(s) (thread 1 sequence 97) |
6. After 1 minute, we check if the table is DROP.
1 2 3 4 5 6 7 8 | [Physical - 04-03-2017 23:41:48] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK EMPLOYEES_YEDEK |
[Physical]—–>ALERT LOG
1 2 3 4 | Sat Mar 04 23:39:30 2017 Media Recovery Log /u01/app/oracle/fast_recovery_area/PHYSICAL/archivelog/2017_03_04/o1_mf_1_97_dcpdyp23_.arc Resize operation completed for file# 3, old size 778240K, new size 788480K Media Recovery Waiting for thread 1 sequence 98 (in transit) |
