Space management in Oracle - Shrink Segments

In Oracle database, table rows are stored in data blocks. Usually a block contains mutiple rows. However, in some cases, a certain row can reside in more than one data block. Also, the rows can moves from a block to another block. 

* Row migration -- When the length of a row increases, exceeding the available free space in the block, the entire row moves to a new data block. The system leaves a pointer at the original location of the row. That pointer points to the new migrated location of the row. 

* Row Chaining -- Another case is when we insert a very long(large) row that doesn't fit into a single data block. Then, the system stores the rows in a chain of data blocks(one or more)

* As we add, delete and modify data in a table, the rows could be easily spread out(location-wise). We all know the logical structure of the database: Segments, Extents, Data Blocks. A segment is a set of extents. And an extent is a specific number of contiguous data blocks. As we insert more data, delete and update data, the segment can be easily a sparsely populated segment. Then, what is the problem with a sparsely populated segment? We might have to read in more data blocks when we run a query because the rows are spread out across more data blocks. It is especially true for a full table scan. Reading more data blocks could degrade our database performance.

As shown below, we can improve our database performance and increase space utilization after SHRINK segment(space).  The tablespace must be in Automatic Segment Space Managed(ASSM) mode, and ROW MOVEMENT attribute of the table should be enabled in order to use the SHRINK feature.

 

The SHRINK process first moves sparsed rows so that the rows are put together. Then, it adjusts the HWM(the High Water Mark) so that the unused space can be released. With COMPACT option, it allows us to move the rows without adjusting HWM.

Flashback Database Vs. RMAN Recovery

1)  Flashback Database Vs. RMAN incomplete recovery
Flashing back a database to an earlier point in time yields a same result as an incomplete(Point-int-time) RMAN recovery would yield. But the difference is that Flashback Database uses the flashback logs stored in FRA and RMAN incomplete recovery uses a backup set and archived logs.

I was personally curious whether Flashing back a database is really faster than doing a incomplete RMAN recovery. Oracle says that it is. But I wanted to test myself. So I tested and found that it is true. I was even impressed with how quickly the testing flashback job was completed. I dropped a user(schema) in the testing database. The schema had a lot of data. Then I logged in the testing database through RMAN session and ran the flashback database command. It only took a few seconds.


2) When to use Flashback database?  When to use RMAN recovery?
Flashback technology seems to be very effective when we want to recover from some errors that has happened recently(not a few days ago or not a couple of weeks ago).  How far back can we flash back our database?  It depends on how long we keep our undo data(undo_retention) or our flashback logs (db_flashback_retention_target).  It seems that these two parameters are not normally set to a large number. They are probably set to a relatively smaller number with the consideration of disk space management. undo_retention is set to 15 minuites by default. And db_flashback_retention_target seems to be set to something like 48 hours according to Oracle's documentation in OTN.

So I would probably use RMAN Incomplete Recovery if I want to recover my database to a relatively more earlier point in time. (example:  5 days ago, 1 week ago, or 3 weeks ago)  Flashback database basically involves with rolling back our database incrementally to earlier point in time. Rolling back our database incrementally all the way back to the point in time 2 weeks ago or a month ago will probably takes much longer than restoring and recovering the database with a backupset and relatively a small set of archived logs.

In sum, I think that RMAN Backup/Recovery and Flashback technology are just complementary to making a proper and effective database recovery strategy. I guess that it is good to have both of them in place.


(3) How to configure/enable Flashback Database
- We need to first configure Fast Recovery Area(FRA) because the flashback logs are only stored in FRA.
- We also need to ensure that our database is running in Archivelog mode
- Set db_flashback_retention_target parameter.
    SQL> alter system set db_flashback_retention_target = 2880 scope=both;
- Open the database in MOUNT EXCLUSIVE mode.
- Alter database to Flashback ON
     SQL> alter database flashback ON;

Flashback Drop - restoring a dropped table from the Recycle Bin

Let's say we have dropped a table by mistake and we need to restore it.

Can we restore the table with RMAN backup?  Yes, we can. But we got a lot of works to do. We restore the data file(tablespace) that contains the dropped table or the whole database to a new host from the backup. And then we can export and import the table from the database restored in the new host to the target database. Both the tablespace/database restoring process and the export/import process require a lot of detailed steps.

Do we have a quicker way? We can just use Flashback technology to restore the dropped table quickly. "Flashback Drop" allows us to restore a dropped table from the Recycle Bin as long as the table has not been purged yet.

When we drop a table, the table and all associated objects(indexes, contraints) are renamed as "BIN$..."  They still exists in the database and still belongs to the owner. They are just remapped to the recycle bin. Because of this reason, we can simply restore the dropped table.     
           SQL> FLASHBACK TABLE table_name TO BEFORE DROP;

When the table is restored, the associated objects and contraints are also restored except for the referential integrity contraints and bitmap indexes.  Note that the name of the restored associated objects and contraints, however, still remains as "BIN$..."  Not reverted back to their original name. It is the DBA's job to rename the restored objects and contraints if needed.

In order to be able to use Flashback DROP feature, we first have to make sure that RECYCLEBIN initialization parameter is set to 'ON' (default). Lastly, be aware that Oracle automatically reclaim space in the recycle bin when additional space is needed by purging the oldest dropped object in the recyle bin (FIFO basis). Once an object is purged, that object no longer exists in the database. As a result, we cannot restore the object from the recycle bin with FLASHBACK DROP.

Oracle Flashback Technology and Flashback Data Archive

Oracle first introduced Flashback Query with Oracle 9i.  And more flashback technologies were introduced with Oracle 10g. Oracle states that Flashback features are usually more efficient and less discruptive than RMAN point-in-time recovery.

RMAN point-in-time recovery:
we first need to restore and then recover the damaged datafile(s) or the database. The restoring and recovering processes may take a long time. If we have to restore a database  from a tape backupset, it will even take longer.
   - Restore :  Copy the data file/database from backup(s)
   - Recover:  Apply all the changes to the data file/database from incremental backups, archived redo logs and online redo logs to the point in time to which we want to return the datafile(s) or the database.

Flashback technologies mostly rely on undo data with the exception of Flashback Drop and Flashback Database.

• Flashback Query :  we can specify a target time(timestamp) or SCN(System Change Number) and run queries against a database to view data as it would have appeared at the specified target time or SCN.
• Flashback Version Query : shows all versions of data between two different target times or SCNs.
• Flashback Transaction Query : shows changes made by a single transaction
• Flashback Transaction:  reverse a transaction
• Flashback Table: recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. We can use flashback queries and/or flashback version queries to determine a desirable point in time to which we need to return our table.
• Flashback Drop: reverse the effects of a Drop Table statement. When we drop a table without PURGE option, the table is renamed as "bins$..." The table still exists in the database. The table object is just remapped into a recycle bin. Flashback Drop allows us to bring the dropped table back from the recycle bin.
• Flashback Database : Required to use flashback logs in a Fast Recovery Area(FRA). A flashback database produces the same result as a database poin-in-time recovery.

Then, how far back can we return our data to?
Most of the flashback technologies make use of undo data only. So they seem to have some advantages over RMAN point-in-time recovery. However, there is a limitation with Flashback technologies: how far back can we return our data to? The answer is 'only as far back as the undo data allows'.  We know that 'UNDO_RETENTION' initial parameter specifies how long we would like to keep unexpired undo data to support the flashback operations as well as Read Consistency. The undo data that falls within the UNDO_RETENTION period is called 'unexpired' undo. In order to guarantee the undo retention for the specified time period, we can alter the undo tablespace to 'RETENTION GUARANTEE.'


Flashback Data Archive(Oracle Total Recall)
The default setting of UNDO_RETENTION is 900 seconds(15 min). What if we want to keep the undo data much longer: like one year or two. Flashback Data Archive(Oracle Total Recall) allows us to keep undo data for a much much longer period of time by archiving the undo data in a seperate tablespace. If we set RETENTION of the flashback archive to 2 years, we can view our data as  it would have appeared 2 years ago, without having to restore and recover our database to that point in time. A Flashback Data Archive allows us to track and store transactional changes to a table over its lifetime. It is especially useful for compliance with record stage policies and audit report, according to Oracle's documentation.

-------------------------------------------------------------------------
Reference:   http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642.pdf