12cR1 RAC Posts — 8g : Switchover from RAC Primary to SingleInstance Standby

Continuing this series of posts where I have a SingleInstance/FileSystem Standby database for a RAC/ASM database … Checking the status of the configuration : DGMGRL> show configuration;Configuration - rac Protection Mode: MaxPerformance Members: rac - Primary database stby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS (status updated 28 seconds ago)DGMGRL>SQL> select thread#, max(sequence#) 2 from v$archived_log 3 group by thread# 4 order by 1 5 / THREAD# MAX(SEQUENCE#)---------- -------------- 1 66 2 36SQL> alter system archive log current;System altered.RFS[2]: Selected log 8 for thread 2 sequence 38 dbid 2519807290 branch 931825279Mon Apr 10 23:03:17 2017Archived Log entry 39 added for thread 2 sequence 37 ID 0x96312536 dest 1:Mon Apr 10 23:03:18 2017Media Recovery Waiting for thread 2 sequence 38 (in transit)Mon Apr 10 23:03:18 2017Recovery of Online Redo Log: Thread 2 Group 8 Seq 38 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_8.300.937936389 Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_8.306.937936389RFS[1]: Selected log 6 for thread 1

Undo and Redo

Quick and Rough Notes : Undo and RedoUndo is where Oracle logs how to reverse a transaction (one or more DMLs in a transaction)Redo is where Oracle logs how to replay a transactionUndo and Redo are written to as the transaction proceeds, not merely at the end of the transaction(imagine a transaction that consists of 1million single-row inserts, each distinct insert is written to undo and redo) Undo segmentsOracle dynamically creates and drops Undo segments depending on transaction volumeAn undo segment consists of multiple extents.

FBDA — 5 : Testing AutoPurging

Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr SQL> select systimestamp from dual;SYSTIMESTAMP---------------------------------------------------------------------------06-APR-16 10.53.20.328132 PM +08:00SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), count(*) 2 from sys_fba_hist_93250 3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn) 4 order by 1,2;SCN_TO_TIMESTAMP(STARTSCN)---------------------------------------------------------------------------SCN_TO_TIMESTAMP(ENDSCN)--------------------------------------------------------------------------- COUNT(*)----------02-APR-16 11.32.55.000000000 PM02-APR-16 11.46.11.000000000 PM 45002-APR-16 11.32.55.000000000 PM03-APR-16 11.45.24.000000000 PM 55002-APR-16 11.46.11.000000000 PM03-APR-16 11.41.33.000000000 PM 502-APR-16 11.46.11.000000000 PM03-APR-16 11.45.24.000000000 PM 44503-APR-16 11.41.33.000000000 PM03-APR-16 11.45.24.000000000 PM 503-APR-16 11.45.24.000000000 PM04-APR-16 11.05.33.000000000 PM 100006-APR-16 10.40.43.000000000 PM06-APR-16 10.42.54.000000000 PM 17 rows selected.SQL> SQL> select count(*) from sys_fba_tcrv_93250; COUNT(*)---------- 1002SQL> More changes on 07-Apr SQL> insert into test_fbda 2 select 3000, to_char(3000), trunc(sysdate) 3 from dual;1 row created.SQL> commit;Commit complete.SQL> update test_fbda 2 set date_inserted=date_inserted 3 where id_column=3000;1 row updated.SQL> delete test_fbda 2 where id_column < 1001 ;1000 rows deleted.SQL> commit;Commit complete.SQL> SQL> l 1 select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*) 2 from sys_fba_hist_93250 3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn) 4* order by 1,2,3SQL> /STARTTIME ENDTIME COUNT(*)-------------------------------- -------------------------------- ----------02-APR-16 11.32.55.000000000 PM 02-APR-16