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 undoNAME 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 undoNAME 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
5alter 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.logalter 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 undoNAME 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
5alter 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
5alter 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.logalter 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.
