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

Materialized views increases query performance - Why and How ?

In data warehouse environments, we usually run long-running queries. Those queries might take hours. So it is very important for DBAs and developers to make efforts to reduce query response time(query performance).

How can we improve query response time?  Use indexes,  partitions the data,  and implement parallel execution.

Are these all the options that we have?

The queries that we run in data warehouse are involved in very intensive computation, data aggregation, summarization and joinings. Using indexes, partitioning tables and parallel execution will definitely help reduce the query response time. But how much?  The queries might still run slow if they are involved with expensive table joinings and intensive data aggregation and summarization.  How can we overcome this problem?

What if we store the result of such a long-running query in a separate table(summary table)? We can just query against the summary table to retrieve the data without running the original, long-running query again. This is great!!!  We can probably use "Create Table ... AS Select...(CTAS)" to create the summary table.

This solution seems to a great idea in the first place. However, there are a couple of drawbacks with this approach.  (1) What if the data in the original tables(base tables) changes? The summary table will become out-of-date. We can't run a query against the summary table and expect to get a result that reflects the current state of our data. We need to manually refresh the summary table: probably drop the table and re-run the CTAS statement. Do we have to do this whenever the origianl(base) tables are modified? Um...I am not sure about that.

(2) Another inconvinience with this approach is that the end users have to know the existence and the name of the summary tables in order to use them. They also have to modify their applications so that the applications can now use the summary tables.  As a result, this approach leads to a conlusion that using summary tables is not the perfect solution for improving query response time.

What can we do then? In order to resolve this problem, Oracle has introduced something called MV (Materialized Views). DBAs identify expensive SQL queries and create MVs for those queries. Now when the users run the same expensive queries, Oracle server internally rewrites the user's original query to a new query so that it can make use of the MV that the DBAs have created before. The re-written query just accesses the MV to retrieve the data, instead of accessing the original(base) tables and going through the whole expensive summarization and joinings. As a result, the users get their query result back much faster. This whole query rewrite process happens behind the scene. The user doesn't even have to know the existence of the MV. 

They would probably feel that their query(original) now runs much faster than it used to. They would be HAPPY now.

Transportable tablespace - Fastest way to move a large volume of data

In data warehouse environments, we move data from one system to another system. There are a few ways to transport data. We can use flat files(most common and simple), distributed queries or transportable tablespaces.  Today, I like to talk about how to use a transportable tablesapce.

Using a transportable tablespace.
- This is the fastest way to move large volumes of data between two Oracle databases. 
- Limitations: the source and target systems must have the same O/S and must use the same character set.

< Example >
Let's say we have a table, called 'sales' in our database1(SH schema). And we want to move all the sales records since year 2000 to database2(SH schema). The amount of records that we have to move is very large. We decided to use a transportable tablespace.


> In the source system

1. create a tablespace that will hold a table that we will create in the next step.
 SQL>    CREATE TABLESPACE  temp_sales_tbls
               DATAFILE  ' /tmp/temp_sales.dbf '
               SIZE 30 M REUSE Autoextend ON;

2. create a table in the new tablespace. And we will store the sales records that we need to transport in this new table.
 SQL >    CREATE TABLE  temp_sales_table   NOLOGGING
                TABLESPACE temp_sales_tbls
                AS
                select * from sh.sales where time_id  > '31-DEC-1999' ;

3. Alter the tablespace to READ-ONLY  to prevent any changes made to it.
 SQL >   ALTER TABLESPACE temp_sales_tbls READ  ONLY;

4. Create a directory object that points to the location where the dump file for the Data Pump export are written.
  SQL >   CREATE DIRECTORY dum_dir AS '/temp/dump/' ;

5. Run a Data Pump untility, expdp, to greate a dump file that contains the meta data for the tablespace, temp_sales_tbls.
  $>   EXPDP sh/sh DIRECTORY=dump_dir  DUMPFILE=sales.dmp 
         TRANSPORT_TABLESPACES=temp_sales_tbls


> In the target system

6. Now we copy the dump file(sales.dmp) and the datafile(temp_sales.dbf ) to the target system.
  - copy the dump file to where a new directory object will point.
     (we are going to create this new directory in step 7)
     i.e)  /temp/dump_import_loc/
  - copy the datafile to where the datafiles of database 2 are located.
      i.e)  /home/oracle/oradata/

7. Create a directory object pointing to the location
  SQL> CREATE DIRECTORY dump_import_dir  AS '/temp/dump_import_loc/' ;

8. Run a Data Pump utility, impdp, to make the datafile(temp_sales.dbf) accessible to database2.is as
$> IMPDP  sh/sh  DIRECTORY=dump_import_dir 
      DUMPFILE=sales.dmp  LOGFILE=imp.log
      TRANSPORT_DATAFILES='/home/oracle/oradata/temp_sales.dbf '

9. We can verify our works so far. 
 SQL>  connect sh/sh
 SQL>  Select count(*) from temp_sales_table;
 SQL>  select * from temp_sales_table;

10. Finally, don't forget that we have 'sales' table in the target database. Our original goal is to move the sales records since year 2000 from thee source database to the target database. We need to inset the imported sales records into the 'Sales' table in the target database.  In the target database,
  SQL>  connect sh/sh
  SQL >  INSERT  /*+ APPEND */  INTO sales
               SELECT * FROM  temp_sales_table;

--------------------------------------
*** This is a simple example. There might be more processes involved in a real-life situation.

Enhancing database security - Application Contexts & Virtual Private Database

< Hypothesis >
(1) You are a DBA for your company's database.
(2) Your company's database has a schema called HR where you basically store all the HR-related data.
(3) There is a table called employees in HR schema. This table contains name, emp_id, position, salary andcommission for all the employees in your company.
(4)  All the employees can access employees table. (Granted 'Select' on the table) 
(5) However, for security and data-confidentiality reason, you (DBA) need to find a way to limit the data access of each employee so that they can only see their own record. In other words, you should be able to control the data access of each user in row-level.

Issue   
Oracle database provides several security measures.  Password profile, authentication methods, system/object privileges and roles. However, none of these measure provides an data access control in row level. Object privileges can control data access on object level at most.
Impact
Because all the employees are granted "SELECT" object privilege on employees table. They can basically view anybody's records including salary and commission, which we don't want.

Solution
We can make use of an application context and Virtual Private Database to overcome this issue.
Virtual Private Database(VPD) combines an application context and fine-grained access control. The following steps show how application context and VPD are implemented. However the steps are simplified and doesn't show all in details.

1. Application Context: When we create an application context, a memory area is allocated to the context. An application context is like an associative array. We add attributes to the context and set values of the attributes. In regard to VPD, we can store users' current session information in an application context. Then based on the user's session information stored in the context, we can control a fine-grained data access control.

(1) Make sure HR user has all privileges required to create an application context.
- We can just let HR user to control the data access in HR schema.
- We can grant roles and privileges to HR as followings.
       >  GRANT create session  TO HR WITH ADMIN OPTION;
       >  GRANT create any context, drop any context, create role, alter any role, drop any role,

           create table, create procedure, create any trigger, administer database trigger,

            create any directory, alter profile, create profile, drop profile, audit system, alter system,

            grant any object privilege TO HR;

       >   GRANT execute on DBMS_SESSION to HR;

(2) Creating an application context (as HR user)

      SQL> CREATE CONTEXT context_name USING package_name ; 

(3) Create a PL/SQL package that set the context attributes. 
- This package should contains a procedure that sets the context attributes.
       ...
       procedure set_emp_id
       IS 
          v_emp_id NUmber;
      Begin
         SELECT employee_id INTO v_emp_id FROM hr.employees
         WHERE email = SYS_CONTEXT('userenv', 'session_user') ;
          DBMS_Session.SET_CONTEXT('context_name','attribute_name','v_emp_id) ;
      End;
      ...
(4) Create a trigger that calls the PL/SQL package to set the context attributes when a user logs in.
          Create or Replace TRIGGER hr_context_logon
          After Logon on database
          Begin
                Package_Name.procedure_name;
          End;

2. Virtual Private Database - fine-grained access control implementation. Create a security policy function thatreturns a predicate(a Where condition).
(1) GRANT execute on dbms_rls to HR. Grant exempt access policy to HR.
(2) Create a policy function as following (as HR user)
...
Funiction limit_emp( object_schema IN varchar2, object_name varchar2)
  return varchar2
Is
Begin
   return 'emp_id = SYS_CONTEXT('context_name', 'attribute_name')';
END;
...
(3) Creating the policy (as HR user)
  SQL>    dbms_rls.add_policy( object_schema=>'HR', object_name=>'orders', 
                       policy_name=> 'policy_name', function_schema=>'HR', 
                       policy_function=> 'package_name.fuction_name', statement_types => 'select');

All set...  Now when an employee run a select statement against EMPLOYEES table, she/he should only see his/her own record only. The security function returns a predicate such as "emp_id =  xxx" This xxx is the employee id value that was stored in the application context. This application context was created when the employee logged in.