22 February, 2012

Here is a quick demonstration to show how a Partitioned Table can have two different indexes seemingly on the same leading column but with different HIGH_VALUEs. One index is a LOCALly Partitioned Index that is Equi-Partitioned with the table, the other index is a GLOBALly Partitioned Index where the Partition Keys can be different.

So I can have two different indexes, differently partitioned on the same table. A LOCAL Index is LOCALly Partitioned (properly known as an "Equi-Partitioned Index") -- meaning that it has Partitions that match the table definition. A GLOBALly Partitioned Index can have Partition definitions that do NOT match the table.

Now I can see the SEQUENCE#1 file for RESETLOGS_ID 775179971 having been archived today, 13-Feb-12 at 23:57. SEQUENCE#1 now has 6 entries !
Bottom Line : If you are testing Backup and Recovery scenarios and/or are frequently doing RESETLOGS on the same database, be very particular when viewing information about ArchivedLogs.
Most DBAs only query for SEQUENCE# in V$ARCHIVED_LOG.

Today's Question : Why did I issue a RECOVER DATABASE ... 4 times ? What is the difference between the different commands ?