Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 4, 2009

Oracle's Flashback Query - Should you unset _in_memory_undo?

By James Koopmann

The benefits of Oracle’s Flashback Query are well documented. But with minor nuances of parameters such as _in_memory_undo, how much should you really buy into them? Let’s take a bit deeper look at the _in_memory_undo parameter and how it effects flashback query.

Oracle’s flashback technology is surely ingenious when it comes to enabling us to recover from blunders, errors, foul-ups, and fat-fingered human mistakes. In the last few articles I’ve visited at a somewhat of a high level the features and benefits—anxiously waiting to dive deep down into more interesting aspects of Oracle’s flashback technology. In doing so, I have to admit, if you didn’t catch me, I quickly edited out one of the parameters (_in_memory_undo) that was returned during the SHOW PARAMETER undo command I had issued.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
_in_memory_undo                      boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

This parameter was left out of the discussions mostly because it did not seem to be of importance to the topic at hand. Well, that has drastically changed as I ventured into Oracle’s flashback query feature and what seemed to be the easiest of all flashback technologies. If you aren’t familiar with Oracle’s Flashback Query it allows the user to query data at a point in time in the past. This means that while the data may have been deleted or changed in a table, that data is still available for query results.

Regardless, this article is dedicated to some of the interesting tests / scenarios I came across when trying to figure out this little parameter.

Yes, I could have ventured, as everyone else seems to do, straight into a simple example of flashback query and be done with it. However, in the back of my head there was just something that I didn’t agree with in the Oracle documentation. In particular, my inner core questioned various statements about configuring flashback technology. Statements like you must configure the Oracle instance to be in archive log mode to use Oracle flashback technology and, in particular, for flashback query, didn’t sit right with me when I read that a flashback query couldn’t be done across a database shutdown/startup.

So, having a database lying around that was still not in archive log mode I thought it would be a good idea to make a couple of runs switching the _in_memory_undo parameter on and off to see just how far I could get with flashback queries. I hope they intrigue you as much as they did me.

_in_memory_undo=true

Setting _in_memory_undo=true is actually the default value for Oracle 11g (didn’t check for 10g but assume it is the same). Therefore, the following scenario might be what you’d expect from an out of the box vanilla Oracle instance if you had archive log mode turned off.

=================================================
_in_memory_undo=true
=================================================

Just to verify the default configuration, we can issue the ARCHIVE LOG LIST and SHOW PARAMETER UNDO commands. Clearly, this instance is in not in archive log mode and the _in_memory_undo parameter is set to TRUE.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/app/oracle/product/11.1.0/db_1/dbs/arch
Oldest online log sequence     300
Current log sequence           309
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
_in_memory_undo                      boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

=================================================
Test : Normal processing
     : _in_memory_undo=true
=================================================

For this test, and subsequent tests where _in_memory_undo is set to false, we will create a very simple test table called FLASH_TEST that has one column in it. This will represent the initial result set that we will attempt to flashback to and will contain just five rows. Here we just create the table, insert the five rows, and then do a simple select statement to verify they exist.

create table flash_test (col1 number);
insert into flash_test values (1);
insert into flash_test values (2);
insert into flash_test values (3);
insert into flash_test values (4);
insert into flash_test values (5);
commit;
SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5

The flashback SQL requires a timestamp to flashback to, so we query the system for a timestamp.

SQL> ho date
Wed May 27 07:13:16 MDT 2009

Insert some rows after the time we will be issuing a flashback query for and verify they are in the table with a simple select statement.

insert into flash_test values (6);
insert into flash_test values (7);
insert into flash_test values (8);
insert into flash_test values (9);
insert into flash_test values (10);
commit;

SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

Here is where we just verify that a flashback query will work—simply add the ‘AS OF TIMESTAMP’ clause to a simple select statement and we get the image of the table before the second set of inserts. Quite easy really. While some of the documentation is unclear as to whether archive log mode is actually required for simple flashback queries, this at least shows it is possible to do without archive log mode.

SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:07:13:16','MMDDYYYY:HH24:MI:SS');
      COL1
----------
         1
         2
         3
         4
         5

In many of my previous tests, I quickly found out that Oracle is very ingenious in hiding, storing, and rebuilding flashback queries. Once I started flushing the shared pool and buffer cache, it became more manageable as to understanding the physical structures and zeroing in on the _in_memory_undo parameter--t least through a shutdown and startup scenario. Here I just wanted to prove to myself that if I flushed the shared pool and buffer cache that I could still get a valid result set for a flashback query.

alter system flush shared_pool;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:07:13:16','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5

alter system flush buffer_cache;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:07:13:16','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5

First test scenario is done. What we learned was simply that flashback query is available for instances that are not in archive log mode and that flashback query is actually going to some physical structures such as undo and online redo logs.

=================================================
Test : Normal processing
     : Bounce the database
     : _in_memory_undo=true
=================================================

Now for the meat of what we started these scenarios for: Will flashback query work through an instance shutdown and startup? As you can quickly see, a dbshut and dbstart were issued along with a flush of the shared pool and buffer cache again. Ultimately, the flashback query did not work as depicted in the manuals.

Now don’t stop reading now. Please understand two things here in this section. One we already mentioned was that just as the manual stated a flashback query will not work across an instance shutdown and startup. However, it is also worthy to note that the flushing of the shared pool and buffer cache was not required here to get this to not work. If these two flushes are left out the flashback query would still not work. They are only here to compare with the second scenario where _in_memory_undo is set to false (not the default), so lets read on.

[oracle@ludwig ~]$ dbshut
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
Usage: /opt/app/oracle/product/11.1.0/db_1/bin/dbshut ORACLE_HOME
Processing Database instance "db11FS": log file /opt/app/oracle/product/11.1.0/db_1/shutdown.log

[oracle@ludwig ~]$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /opt/app/oracle/product/11.1.0/db_1/bin/dbstart ORACLE_HOME
Processing Database instance "db11FS": log file /opt/app/oracle/product/11.1.0/db_1/startup.log

alter system flush shared_pool;
alter system flush buffer_cache;

SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:07:13:16','MMDDYYYY:HH24:MI:SS');
SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:07:13:16','MMDDYYYY:HH24:MI:SS')
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

_in_memory_undo=false

As was previously stated, the default value for _in_memory_undo is TRUE. Here we repeat the previous test except we will set the _in_memory_undo to FALSE to see what will happen. Again, we issue the ARCHIVE LOG LIST and SHOW PARAMETER UNDO commands to show and prove to ourselves that the instance is still not in archive log mode and the init.ora parameter _in_memory_undo is set to FALSE.

=================================================
_in_memory_undo=false
=================================================
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/app/oracle/product/11.1.0/db_1/dbs/arch
Oldest online log sequence     330
Current log sequence           339
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo                      boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

=================================================
Test : Normal processing
     : _in_memory_undo=false
=================================================

Again, we will create a very simple test table called FLASH_TEST that has one column in it. This will represent the initial result set that we will attempt to flashback to and will contain just five rows. Here we just create the table, insert the five rows, and then do a simple select statement to verify they exist.

create table flash_test (col1 number);
insert into flash_test values (1);
insert into flash_test values (2);
insert into flash_test values (3);
insert into flash_test values (4);
insert into flash_test values (5);
commit;
SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5

The flashback SQL requires a timestamp to flashback to so we query the system for a timestamp.

SQL> ho date
Wed May 27 10:07:04 MDT 2009

Insert some rows after the time we will be issuing a flashback query for and verify they are in the table with a simple select statement.

insert into flash_test values (6);
insert into flash_test values (7);
insert into flash_test values (8);
insert into flash_test values (9);
insert into flash_test values (10);
commit;

SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

Here is where we just verify that a flashback query will work as before and that flushing the shared pool and buffer cache will have no effect on the flashback query.

SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');
      COL1
----------
         1
         2
         3
         4
         5

alter system flush shared_pool;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5

alter system flush buffer_cache;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5

At this point, everything is the same and flashback query will work for instances that are not in archive log mode and that flashback query is actually going to some physical structures such as undo and online redo logs.

=================================================
Test : Normal processing
     : Bounce the database
     : _in_memory_undo=false
=================================================

Here is where things get interesting. Remembering that the bounce of the database instance with _in_memory_undo=true did not work, now, with _in_memory_undo=false we are able to shutdown and startup the instance and have flashback query work—totally contradictory to what Oracle documentation states. Moreover, this is where the flushing of the shared pool and buffer cache come into play. Just to verify that nothing was loaded into memory on startup we can easily see that flushing has no effect on the flashback query and it is still going against physical structures (undo/redo) to build the result set.

[oracle@ludwig ~]$ dbshut
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
Usage: /opt/app/oracle/product/11.1.0/db_1/bin/dbshut ORACLE_HOME
Processing Database instance "db11FS": log file /opt/app/oracle/product/11.1.0/db_1/shutdown.log

[oracle@ludwig ~]$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /opt/app/oracle/product/11.1.0/db_1/bin/dbstart ORACLE_HOME
Processing Database instance "db11FS": log file /opt/app/oracle/product/11.1.0/db_1/startup.log

alter system flush shared_pool;
alter system flush buffer_cache;

SQL> select * from flash_test;
      COL1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');
      COL1
----------
         1
         2
         3
         4
         5

=================================================
Test : Normal processing
     : Switching through archive logs
     : _in_memory_undo=false
=================================================

The question now becomes what do we need to do in order to get the flashback query to fail. Granted this is not an exhaustive scenario but does show some interesting workings within Oracle. Thinking that flashback technology needs to be in archive log mode, I felt pressed to make it fail through the online redo logs. So here, once again we flush the shared pool and buffer cache and then switch through online redo so we can be certain the inserts no longer exist. As you can see, the flashback query still works regardless of cycling through the online redo logs.

alter system flush buffer_cache;
alter system flush shared_pool;
alter system switch logfile; (performed enough times to cycle through redo logs)
alter system flush buffer_cache;
alter system flush shared_pool;

SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5
=================================================
Test : Normal processing
     : Switching UNDO tablespaces
     : _in_memory_undo=false
=================================================

One would, after flushing memory and cycling through the redo logs, conclude that the remaining piece of information to rebuild result sets for flashback query would be in the undo. So let’s again flush the shared pool and buffer cache while switching to a new undo tablespace. To my surprise the flashback query still works. I don’t know about you but I’m getting a bit perplexed here.

Likewise, for purposes that will be shown later in the article, if we decided to switch undo tablespaces the following error would occur:

alter system flush buffer_cache;
alter system flush shared_pool;
alter system set undo_tablespace=undotbs2;
alter system flush buffer_cache;
alter system flush shared_pool;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');

      COL1
----------
         1
         2
         3
         4
         5

So, thinking, somehow, Oracle still knows that the undo information is in an unused undo segment I decide to drop the UNDOTBS1 tablespace altogether. To my surprise, finally, I am able to make flashback query fail with our old friend the ORA-01555 snapshot tool old message.

drop tablespace undotbs1;
alter system flush buffer_cache;
alter system flush shared_pool;
SQL> SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS');
SELECT * FROM flash_test AS OF TIMESTAMP TO_TIMESTAMP ('05272009:10:07:04','MMDDYYYY:HH24:MI:SS')
              *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small

While this article was not conclusive in determining all the nooks and crannies that Oracle saves undo information, it does provide us an interesting comparison of the setting of the _in_memory_undo parameter. While the _in_memory_undo parameter is designed to improve performance by performing in-memory undo handling and limiting disk access, it also poses an interesting question to whether you would actually want to set this parameter. Clearly, there could be some advantages to setting this value to false. As much as I hate to use the statement, be careful on this one as your version and mileage may vary.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM