Managing Dataguard.

To check status of Archive files in both Primary and Standby

SELECT process, status, thread#, sequence#,delay_mins, block#, blocks
FROM v$managed_standby  ;


Determine the most recently archived redo log file. 

SELECT MAX(sequence#), thread#
FROM v$archived_log
GROUP BY thread# ;



Determine the most recently archived redo log file at each redo transport destination.
SELECT destination, status, archived_thread#, archived_seq#
FROM v$archived_dest_status
WHERE status <> 'deferred'
AND status <> 'inactive';

Spatial Index Partition Error

ORA-14086: a partitioned index may not be rebuilt as a whole

Action    : Rebuild the index a partition at a time
Example: ALTER INDEX index_name REBUILD PARTITION partition_name;


ORA-29954: domain index partition is marked LOADING/FAILED/UNUSABLE

Action     : Wait if the specified index partition is marked LOADING Rebuild the specified 

                   index partition if it is marked FAILED or UNUSABLE.

Example :ALTER INDEX index_name REBUILD PARTITION partition_name;

Spatial Table Partition

Partitioning is an Oracle Database feature in which a single table is decomposed into multiple smaller tables called partitions.Range partitioning is the supported partitioning scheme for tables with spatial indexes. 

Range Partition Table 

CREATE TABLE tab_part
(geom MDSYS.SDO_GEOMETRY,
 id   NUMBER(10),
 mod  NUMBER(2)

)
PARTITION BY RANGE (mod)
(PARTITION p1_tab VALUES LESS THAN (3),
 PARTITION p2_tab VALUES LESS THAN (5),
 PARTITION p3_tab VALUES LESS THAN (7)
);

We should have entry in user_sdo_geom_metadata before  creating spatial Index.

INSERT INTO user_sdo_geom_metadata
SELECT 'TAB_PART' TABLE_NAME, COLUMN_NAME, DIMINFO, SRID 

FROM   user_sdo_geom_metadata 

WHERE TABLE_NAME= 'TAB1'      -- TAB1 have same entry in user_sdo_geom_metadata

AND COLUMN_NAME ='geom';

Create the partitioned spatial index specifying the keyword UNUSABLE

CREATE INDEX tab_part_sidx ON TAP_PART(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
LOCAL (
PARTITION P1 ,
PARTITION P2 ,
PARTITION P3 )
UNUSABLE;

If the index is created UNUSABLE, and each
partition is rebuilt separately, then if one or more partition’s indexes fail to build correctly only those
partition’s indexes will need to be rebuilt.

After creating the index UNUSABLE, each partition’s spatial index can then be created using the
ALTER INDEX … REBUILD PARTITION command.

alter index TAB_PART_SIDX rebuild partition p1;
alter index TAB_PART_SIDX rebuild partition p2;
alter index TAB_PART_SIDX rebuild partition p3;