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

12cR1 RAC Posts — 8e : Redo Shipping and Apply (RAC to nonRAC)

Continuing the series of posts on the RAC/ASM to SingleInstance/FileSystem DataGuard configuration … With both instances RAC1 and RAC2 running, I have redo shipping from both instances to the single instance STBY.(In my test server with 3 VMs, it takes a number of minutes to have all 3 instances running and communicating with each other … so there are spurious ORA and TNS errors until the 3 instances have stabilized.

Partition Storage — 2 : New Rows Inserted in 12.1 Partitioned Table

Following up from the previous post, given the current sizes of the Partitions (The underlying Tablespace is AUTOALLOCATE) : SQL> col segment_name format a30SQL> col partition_name format a12SQL> l 1 select segment_name, partition_name, segment_type, bytes/1024 2 from user_segments 3 where segment_name like 'MY_PART_%' 4* order by 1,2SQL> /SEGMENT_NAME PARTITION_NA SEGMENT_TYPE BYTES/1024------------------------------ ------------ ------------------ ----------MY_PART_TBL P_100 TABLE PARTITION 8192MY_PART_TBL P_200 TABLE PARTITION 8192MY_PART_TBL P_300 TABLE PARTITION 8192MY_PART_TBL P_400 TABLE PARTITION 8192MY_PART_TBL_NDX P_100 INDEX PARTITION 64MY_PART_TBL_NDX P_200 INDEX PARTITION 64MY_PART_TBL_NDX P_300 INDEX PARTITION 64MY_PART_TBL_NDX P_400 INDEX PARTITION 648 rows selected.SQL> I shall now insert rows so that a Partition has to grow beyond the first extent.

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