Menu Bar

Saturday, November 24, 2012

FLASHBACK Technology in Oracle Databases (Part 2)


"By the way, did I mention it also travels in time?" - The Ninth Doctor, referring to the TARDIS
Last time we talked about using Flashback technology to run queries against our database as it looked in the recent past.

This can certainly be useful, but what if it just isn't enough. What if we need to get back a table we just accidentally dropped, or recover a data screw up so bad that recovery is our only option.

There are other aspects to Flashback technology that let us do just that.

What we looked at last time would be covered under the topic sub-titles "Flashback Query" and "Flashback Version Query". What other options do we have available to us?

Looking into the heart of our TARDIS (refer to Part 1 for the introduction of the Oracle TARDIS), we find the following options awaiting us:

  • Flashback Database - a physical level restore that restores the entire database to a specific point in time using flashback logs rather than doing a database recovery and rolling archive logs forward
  • Flashback Table - recover a table to a point in time
  • Flashback Drop - restores an accidentally dropped table as well as all related indexes, triggers and constraints
  • Flashback Transaction - rollback a single transaction and optionally, all transactions dependent upon the rolled back transaction
  • Flashback Transaction Query - see all the changes made by a specific transaction

Today we'll be talking about the first three on that list (we'll deal with Flashback Transaction in part 3 of the series).

Flashback Database

Let's start with Flashback Database. Much like a database recovery, Flashback Database is a physical level recovery, much like a database point in time recovery, up to and including the OPEN RESETLOGS at the end, but usually much faster because the initial recovery of the data files from backup isn't needed.

In order to use Flashback Database, the Fast Recovery Area needs to be configured in the database that is a candidate for Flashback.

This means that the db_recovery_file_dest and db_recovery_file_dest_size initialization parameters need to be set. Furthermore, in order to enable Flashback Database, db_flashback_retention_target also needs to be set. This parameter the number of minutes we can go back into the past and perform a Flashback Database.

Once this is configured, we must also make sure to issue ALTER DATABASE FLASHBACK ON.

What this will do is to start writing images of data blocks to the fast recovery area. The flashback logs are written sequentially and often in bulk. The database automatically creates, deletes and resizes these logs as needed. These logs are not archived.

The only thing that the DBA really needs to know about for the flashback logs is that they exist (from a sizing of the fast recovery area point of view) and that they are adding a performance overhead to the database.

The database also needs to be in Archivelog Mode for this functionality to work.

When doing a Flashback Database, the database uses information in these logs, as well as some information from archive logs, in order to perform the requested operation. It is because of this that you can't enable flashback after a failure and expect that you can flashback your database to before the failure.

Similar to the syntax used in Flashback Query, there are several options you can apply to Flashback Database:

  • TO SCN
  • TO SEQUENCE
  • TO TIMESTAMP
  • TO RESTORE POINT

Each of these also supports a TO BEFORE construct as well. Doing FLASHBACK TO will recover the database right up until the parameter specified. Doing FLASHBACK TO BEFORE will recover the database right up until immediately before the parameter specified.

Demo Time

First things first, let's check that our database is set up correctly:


SQL> show parameter db_recovery

NAME                        TYPE        VALUE                          
--------------------------- ----------- ------------------------------ 
db_recovery_file_dest       string      c:\oracle\flash_recovery_area  
db_recovery_file_dest_size  big integer 4977M                          

SQL> show parameter flashback;

NAME                          TYPE        VALUE
----------------------------- ----------- -----
db_flashback_retention_target integer     1440

SQL> select flashback_on from v$database;

FLASHBACK_ON                                               
------------------                                               
NO                                                               

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON                                                     
------------------                                               
YES                                                              

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED

Next, let's check the status of the database as we left it after Part 1, then add a new table and populate it with some related data.


SQL> conn demo/demo
Connected.
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> select table_name from user_tables;

TABLE_NAME                                                    
---------------                                               
PREZ                                                          

SQL> desc prez
 Name                                      Null?    Type 
------------------------------------------ -------- -----------
 NAME                                      VARCHAR2(25)
 ELECTED                                   VARCHAR2(4)

SQL> select * from prez;

NAME                      ELEC                     
------------------------- ----                                               
Barack Obama              2008                     
George W. Bush            2013                     
Bill Clinton              2013                                           
George H. W. Bush         2013                     
Ronald Reagan             2013                                           

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                   
2012-11-24:22:02:36                                               

SQL> create table viceprez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into viceprez values ('Joe Biden', '2008');

1 row created.

SQL> insert into viceprez values ('Dick Cheney', '2000');

1 row created.

SQL> insert into viceprez values ('Al Gore', '1992');

1 row created.

SQL> insert into viceprez values ('Dan Quayle', '1988');

1 row created.

SQL> insert into viceprez values ('George H. W. Bush', '1980');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                    
------------------------- ----                                           
Joe Biden                 2008                     
Dick Cheney               2000                     
Al Gore                   1992                     
Dan Quayle                1988                     
George H. W. Bush         1980                     

SQL> select sysdate from dual;

SYSDATE                                                                      
-------------------                                                          
2012-11-24:22:09:07                                                          

SQL> quit

Note the time stamp here. At the time that the timestamp is shown the rows have not been committed to the database. The commit does not happen until we quit from SQL*Plus, where the commit happens implicitly.

So, a user gets into a database and starts deleting rows. They suddenly realize that they are in prod and not in test!

The DBA comes to the rescue (after first realizing that he must shut down the database in order to perform the flashback).

I've also included listings here showing the current incarnation of the database from RMAN between steps.


C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       CURRENT 1          19-NOV-12

RMAN>quit

C:\> sqlplus demo/demo
Connected.

SQL> delete from viceprez where elected< 2000;

3 rows deleted.

SQL> delete from prez where elected = 2013;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL>connect / as sysdba
Connected.


SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');
flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK. 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> quit

C:\> sqlplus / as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

no rows selected

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       CURRENT 849611     24-NOV-12

RMAN>quit

This is where the earlier comment about the placement of the commit came into play (and also ties neatly into my earlier post this month about Transaction Isolation Levels in Oracle). Because the changes had not been committed at the timestamp specified, when the flashback database was performed, those rows are missing from the resultant database.

Let's assume that we now go back and recreate the data in the VICEPREZ table and that our same user goes and deletes the exact same records again. We'll pick it up at the delete and the subsequent rescue by the DBA:


SQL> select sysdate from dual;

SYSDATE                                                                         
-------------------                                                             
2012-11-24:22:22:11                                                             

SQL> delete from viceprez where elected < '2000';

3 rows deleted.

SQL> delete from prez where elected = '2013';

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL> connect / as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:22:11','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       PARENT  849611     24-NOV-12
3       3       DEMO     3543868088       CURRENT 849931     24-NOV-12

RMAN>

The DBA is the hero of the hour, having managed to successfully recover the database back to pre-delete state without the large time investment of a complete database restore.

Of course, such functionality needs to be used with great care in a production environment to make sure that the rest of the data you lose isn't more of a pain than the data you've already lost.

One scenario that immediately comes to mind for the use of something like this would be in a large Enterprise application where Payroll is one of the subsystems in the database. The developers in this theoretical shop are in the process of making major functional changes to the code. They want to test on as close to live data as possible so they request a refresh of the test database.

Being a large Enterprise level database, it takes 2-3 hours to restore the test server from backup. The developers run their test payroll but it didn't work. They need another fresh copy of the production database in order to run their test again.

Before Flashback Database, another 2-3 hours would be wasted waiting for the refresh to complete.

If the DBA setup the test environment for Flashback and let the developers do their testing, if they needed a fresh copy of the database in test, a quick Flashback Database later and they have a fresh copy of the data in a fraction of the time.

Flashback Table

Our next scenario is for Flashback Table. With this feature you can flashback just one table in the database to a particular SCN, TIMESTAMP or RESTORE POINT. Creating a restore point is as simple as:


SQL> create restore point flashback_table;

Restore point created.

Again, let's assume that someone has run rampant over some table in the database. The table is stand alone, with no other dependencies on other objects, so it has been determined that it's safe to roll this one table back to a point in time in the past.

Demo Time


SQL> conn demo/demo
Connected.
SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> select sysdate from dual;

SYSDATE            
-------------------
2012-11-24:22:32:14

SQL> delete from viceprez where elected = '2008';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> flashback table viceprez to restore point flashback_table;
flashback table viceprez to restore point flashback_table
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled 

What does that mean? Row movement is a setting that tells Oracle it's OK to generate new ROWID's for rows being recovered in this manner (since there's no guarantee that the old ROWID is still unique).

Fortunately, this is a setting that can be enabled after the fact!


SQL> alter table viceprez enable row movement;

Table altered.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> flashback table viceprez to restore point flashback_table;

Flashback complete.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Flashback Drop

For our last scenario, we're going to look at recovering a table that has been dropped.

In this scenario (ignoring the fact that a user shouldn't have drop rights on a table anyway), a user inserts a row into the table, realizes their error and attempts to remove it. However, instead of removing the row, they drop the table in error.

The DBA puts on his red cape and once again comes to the rescue!

Demo Time


SQL> insert into viceprez values ('The Man in the Moon', '2013');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> drop table viceprez;

Table dropped.

SQL> select * from viceprez;
select * from viceprez
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
VICEPREZ         BIN$bsEpu+DLQZWxoPngfpkZJQ==$0 TABLE        2012-11-24:22:40:18

SQL> flashback table viceprez to before drop;

Flashback complete.

SQL> show recyclebin;
SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            
VICEPREZ                                                                        

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> delete from viceprez where elected = '2013';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Conclusion

Used with caution, the above Flashback options could save you hours of time in the event that you accidentally drop the wrong table, or have the need to perform repetitive testing on a test copy of the database where the testing causes mass updates to the data (such as in a payroll update).

There are many more options to these commands than what we've talked about here. For full information, please refer to Chapter 18 of the Oracle 11g Database Backup and Recovery User's Guide.

Next Time

Next time, we'll dig a little into Flashback Transaction and Flashback Transaction Query.

Tuesday, November 20, 2012

FLASHBACK Technology in Oracle Databases (Part 1)


"If it helps, I can travel in time as well" - The Tenth Doctor, referring to the TARDIS
Today we're talking about Time Travel in Oracle Databases.

I almost made a reference in the title and quote of this post about Mr. Peabody and the Way Back Machine but that would have perhaps shown my age - besides, quoting The Doctor is way cooler.

The Doctor and the Tardis

I recently found myself in a situation where I needed to go back in time. I was rolling forward a copy of a database to a particular point in time from a backup. I needed to stop at that particular point in time because the project manager wanted me to run row counts on the 1500 odd tables in the "before" and "after" databases and I had taken the "before" at that specific point in time.

During my recovery operation I fat fingered the UNTIL TIME clause and went past my stopping point.

After the expected cursing had died down I started wishing I had a TARDIS of my very own. Apart from the coolness factor of being able to zip back and forth through time at will, being able to go back about 15 minutes and fix my mistake would have been a miracle in and of itself.

It was then that I remembered FLASHBACK and it became my own TARDIS - Time and Relational Data in Schemas.

What is Flashback Query

First introduced in Oracle 9i, Flashback Query enables as to view the database as it existed at a point in time in the recent past. In Oracle 9i and onwards, this is implemented via the DBMS_FLASHBACK package.

This functionality was enhanced in Oracle 9i Release 2 with the addition of the "AS OF TIMESTAMP" clause to the SELECT statement.

Prerequisites and Limitations

There are some prerequisites to use Flashback Query

  • Initialization parameter requirement: undo_management = auto
  • Initialization parameter requirement: undo_retention = nnnn (maximum number of seconds to be able to query into the past)
  • Initialization parameter requirement: undo_tablespace = <undo_tablespace_name>
  • FLASHBACK (specific tables) or FLASHBACK ANY TABLE system privilege
  • EXECUTE privilege on DBMS_FLASHBACK

There are some restrictions that you should be aware of as well:

  • Prior to 11g, DDL that alters the structure of a table invalidates old UNDO data, rendering Flashback Query inoperative. Post 11g, most DDL is supported.
  • Timestamps map to an SCN (System Change Number) Value. These are internally updated every 5 minutes. If operating within that window, query results may be inaccurate if using timestamps (use SCN's for increased accuracy).
  • You must DISABLE/ENABLE the flashback mode before changing time windows.
  • Only data state is affected. Any table that has had a DDL change will reflect the most recent state of the table.
  • You cannot use Flashback Query on V$ views, but you can use it on catalog tables like USER_TABLES.
  • The SYS user cannot use DBMS_FLASHBACK but can use AS OF TIMESTAMP in a SELECT.
  • DML or DDL operations cannot be performed in Flashback mode. Open a cursor, fetch the data, end the flashback session and manipulate the data or use INSERT INTO ... SELECT AS OF TIMESTAMP constructs.
  • The undo_retention parameter is a target - it is not a guarantee of data availability.

Demo Time

The first thing we would need to do is to make sure that the user we're going to use has the correct permissions so we'd want to execute the following script:


create user demo identified by demo default tablespace users;
grant create session, flashback any table, create table to demo;
grant execute on dbms_flashback to demo;
alter user demo quota unlimited on users;

Next, we will check our parameters to make sure that automatic undo is turned on and that we're archiving:


SQL> connect / as sysdba
Connected.
SQL> show parameter undo

NAME                         TYPE        VALUE
---------------------------- ----------- ---------------
undo_management              string      AUTO
undo_retention               integer     3600
undo_tablespace              string      UNDOTBS1

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED                              

After that, let's create a table and load a little data and validate what we have. At the end of the inserts, we'll commit the changes, grab our current SCN and our current date and time.


SQL> conn demo/demo
Connected.

SQL> create table prez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into prez values ('Barack Obama', '2008');

1 row created.

SQL> insert into prez values ('George W. Bush', '2000');

1 row created.

SQL> insert into prez values ('Bill Clinton', '1992');

1 row created.

SQL> insert into prez values ('George H. W. Bush', '1988');

1 row created.

SQL> insert into prez values ('Ronald Reagan', '1980');

1 row created.

SQL> select * from Prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> commit;

Commit complete.

SQL> alter session set nls_date_Format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> set serveroutput on
SQL> var scn number;
SQL> exec :scn := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE 
-------------------
2012-11-20:22:49:06

Next, we'll run an update where we "accidentally" update all the rows in the table to an incorrect value. We'll also grab a timestamp here.


SQL> update prez set elected = '2013';

5 rows updated.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2012-11-20:22:49:32

With that done, it's now time to check the table out with current values and values as they were at a specific SCN. The first way uses the DBMS_FLASHBACK package to view the data as it was at that first SCN we assigned to the variable :scn. Then, we do it again using SELECT ... AS OF SCN.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_system_change_number(:scn);

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> select * from prez as of scn :scn;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

For the next demo, we'll do effectively the same thing, using Timestamps instead of SCN's. I made sure to wait at least 5 minutes so that the internal SCN to TIMESTAMP mapping was updated correctly and we went and did round 2.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_time(to_date('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:32','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

Finally, there's another clause we can add to the SELECT statement to show what versions of a row may exist. You can specify SELECT ... VERSIONS BETWEEN.

We start by updating one row of the table, getting the new times and SCN's and then running the queries.


SQL> update prez set elected = '2008' where name = 'Barack Obama';

1 row updated.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                                               
2012-11-20:23:11:54                                               

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801                                              

PL/SQL procedure successfully completed.

SQL> var scn2 number;
SQL> exec :scn2 := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn2));
SCN = 820456                                         

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  versions between scn 818801 and 820456 
  3  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

SQL> select sysdate from dual;

SYSDATE                                               
-------------------                                               
2012-11-20:23:19:24                                               

SQL> select * from prez 
  2  versions between timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS')
  4  and to_timestamp('2012-11-20:23:20:00','YYYY-MM-DD:HH24:MI:SS')
  5  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

Conclusion

One of the important things to note here is that while you're in Flashback mode, no DML is allowed. One way to get around that is to use SELECT ... AS OF to create a temporary copy of the data you need and then work from there to correct it.

The following would work:


create table temp_prez as select * from prez as of scn :scn;

From there you'd be able to compare current and old data and update accordingly as needed.

For further information on Flashback Technology in Oracle Databases, be sure to check the Oracle Documentation.

Next Time

Next time, we'll dig a little deeper into the features added in Oracle 10g and later, specifically Flashback Table, Flashback Drop and Flashback Database.

Thursday, November 15, 2012

Question to self: Who am I ?


"I believe the appropriate metaphor here involves a river of excrement and a Native American water vessel without any means of propulsion." - Dr. Sheldon Cooper, The Big Bang Theory.
Today we'll talk about some Oracle stuff for a change.

I had one of my colleagues come to be a couple of weeks ago and say to me "How to I tell what database I'm connected to after I connect to the database" ?

I had to scratch my head a little on that one. When connected as SYSDBA, it's very simple to select from v$instance and you have your instance name.

However, if you're not connected as SYSDBA how do you do it.

Enter the SYS_CONTEXT function. SYS_CONTEXT allows you to extract parameter values associated with a particular namespace. Oracle provides a predefined namespace called USERENV which contains a wealth of information about the current session.

The following, for example would tell you which database you're currently connected to:


SELECT SYS_CONTEXT ('USERENV', 'INSTANCE_NAME') 
   FROM DUAL;

There's a full list of the predefined parameters in the Oracle SQL Language Reference Manual.

Wednesday, November 14, 2012

Isolation Levels in Oracle vs SQL Server


"I am somewhat preoccupied telling the Laws of Physics to shut up and sit down." -Vaarsuvius, Order of the Stick Webcomic
I sat in on a session at the PASS Summit in Seattle last week listening to a session delivered by Randy Knight talking about Isolation Levels in SQL Server.

Knowing that Oracle handles things differently to SQL Server made me want to research things a little closer.

As a DBA who spends time in both Oracle and SQL Server, it's important to understand Isolation Levels in these two RDBMS's and how they work, because their default behavior is very different.

According to the ANSI Standard, there are 4 default Isolation Levels in a Relational Database Management System, ordered below from least isolated to most isolated.

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable

Going into the details of what each of these mean is outside the scope of what I want to write about today. There's a good write up at Wikipedia or any of a dozen or more blogs, just search "Isolation Levels" on your favorite search engine.

It should be noted that SQL Server implements all 4 of the ANSI Standard Isolation Levels plus one of its own (SNAPSHOT Isolation - which has 2 different levels in itself - Statement level and Transaction Level). As with the basic tenets, going into the details of snapshot isolation is outside of the scope of what I want to talk about here. To learn more about SNAPSHOT Isolation, refer to Books Online.

Oracle, on the other hand, does not implement Read Uncommitted and also does not implement Repeatable Read. Oracle adds in their own extra option called Read Only Isolation. You can read more about this option in the Oracle Documentation.

Because I work daily in both Oracle and SQL Server, what I really wanted to find out was what are the core effects of how SQL Server approached the READ COMMITTED Isolation level vs how Oracle handles it.

In basic terms, SQL Server has a "pessimistic" view of data concurrency by default. When in the middle of a transaction, SQL Server will block readers on any row that has changed until that row is committed.

Oracle, on the other hand has an "optimistic" view of concurrency by default. When in the middle of a transaction, Oracle will have readers see a version of the row as it existed at the start of the update transaction, thus not blocking readers.

I found an article written by Tom Kyte, Oracle's technology expert, where he talked about possible data inaccuracies when Read Committed is implemented the way that Microsoft does it in SQL Server and wanted to summarize it here as I used it for my own testing.

The basic setup is like this:

A bank account database has a table for balances that contains two columns, account and balance. The table has many hundreds of thousands rows.

A query is started to sum balances. The database begins reading pages and accumulating balances. After 200,000 records are read a transaction is done in the database. An account that has already been read is decremented by $400 and an account that has not yet been read (row number 300,000) is incremented by $400.

How do both database systems handle this ?

The timeline would go like this (I am going to represent the summing of balances by selecting one row at a time):

  1. Transaction 1: select balance where account = 1;
  2. Transaction 1: select balance where account = 2;

    ... etc ...

  3. Transaction 2: Decrement the value of account 1 by $400;
  4. Transaction 2: Increment the value of account 300000 by $400;

    ... etc ...

  5. Transaction 1: select balance where account = 300000;

    ... etc ...

  6. Transaction 2: Commit

Let's start by looking at how Oracle handles it.

I preload a table with three rows. Account 1 has a balance of $500, Account 2 has a balance of $200 and Account 300000 has a balance of $100.

Oracle's SQL*Plus, differently from SQL Server Management Studio, has an implicit transaction. You don't need to specify BEGIN TRANSACTION - a transaction is assumed from the moment you start until you quit SQL*Plus (implicit commit) or explicitly issue a commit or rollback.

In Session 1, we execute the following


SQL> select balance from accounts where account = 1;

   BALANCE
----------
       500

SQL> select balance from accounts where account = 2;

   BALANCE
----------
       200

SQL>

We do not commit the transaction.

In Session 2 we update the values of our accounts


SQL> update accounts set balance = 100 where account = 1;

1 row updated.

SQL> update accounts set balance = 500 where account = 300000;

1 row updated.

SQL>

Once again we leave the transaction open and do not commit.

We switch back to session 1:


SQL> select balance from accounts where account = 300000;

   BALANCE
----------
       100

SQL>

The query immediately returns the value of account 300000 as it was at the beginning of this current transaction, giving us a consistent view of all the balances as of the time that the query started.

Returning to Session 2 now, we can commit the transaction over there just to tidy up loose ends.


SQL> Commit;

Commit complete.

SQL> 

Having seen how Oracle does it (the optimistic view), let's have a look at SQL Server.

On SQL Server, we need to explicitly tell SQL Server Management Studio that we want a transaction.


begin transaction

select * from accounts where account = 1;
select * from accounts where account = 2;


account                                 balance
--------------------------------------- ---------------------------------------
1                                       500

(1 row(s) affected)

account                                 balance
--------------------------------------- ---------------------------------------
2                                       200

(1 row(s) affected)

So far so good.

Over to Session 2, we perform our balance transfer between accounts


begin transaction

update accounts set balance = 100 where account = 1;
update accounts set balance = 500 where account = 300000;

(1 row(s) affected)

(1 row(s) affected)

Returning to Session 1, we try to query Account 300000:


select * from accounts where account = 300000;

SSMS sits here and spins. It is waiting for the transaction from Session 2 to complete and commit the change to the database. A writer is blocking a reader now.

We swap back to session 2 and complete the transaction:


commit transaction

Immediately the query in Session 1 returns a value:


account                                 balance
--------------------------------------- ---------------------------------------
300000                                  500

(1 row(s) affected)

That's not right .... or is it ? I'd have to say no - it's not right - not from a pure data accuracy standpoint.

From the SQL Server Read Committed Isolation level and the way it's implemented that is the correct value - the value was read after it was committed to the database but the net result is that the $400 has been counted twice. It was counted in the balance of account 1 and then again in the balance of account 300000 - our query has, in fact, returned incorrect data!

If I was wanting an accurate picture of my account balances under this scenario, it appears to be that Snapshot Isolation is what's needed, but even then it can be a little tricky.

I ran through the scenario above again using Snapshot Isolation, using these scripts in the same order of command execution as presented above::

Session 1


set transaction isolation level snapshot
begin transaction
select * from accounts
select * from accounts where account = 300000
commit transaction

Session 2


set transaction isolation level snapshot
begin transaction
update accounts set balance = 100 where account = 1;
update accounts set balance = 500 where account = 300000
commit transaction

The value in the last query of Session 1 showed 100 before I committed Session 2, with an immediate return, just like Oracle did. However, even after I had committed Session 2 and ended it completely, the return from the query on the balance on account 300000 in Session 1 was still showing $100, until I ran the commit transaction in Session 1, when it turned back to the $500 value that was actually sitting in the database.

This actually makes sense, since we had done a begin transaction at the start of the query in Session 1, so it was showing us a picture of the data as it existed at the start of Session 1.

If we run through the example one more time, this time not putting the queries in Session 1 inside a transaction, as soon as Session 2 commits, the balance of account 300000 is showing the updated value immediately.

Microsoft provides an excellent example of the different behavior under snapshot isolation and read committed snapshot isolation on their website (that uses vacation days instead of a bank account as the example).

So, there's a definite "Gotcha!" sitting in there. If this kind of level of accuracy is important to you and your application, make sure you completely understand how Isolation levels work in the database system you're using.

I should re-iterate that the demonstration scenario used in this post was used in Tom Kyte's article as linked above and replicated here in order to compare and contrast the differences between the default behavior in both Oracle and SQL Server and how it can be modified by using other options. While he mentioned "other vendors" in his article, I wanted to see for myself how this scenario specifically played out in SQL Server (as I already knew for certain how it would behave in Oracle).

Sunday, November 11, 2012

PASS Summit 2012 Thoughts and Ruminations


"Sir, I think you have a problem with your brain being missing" - Zoe, Firely, "The Train Job"
The quote above is a little how I feel after this past week in Seattle at PASS Summit 2012. This was my second PASS Summit (many thanks to my boss for sending me out for another great week of education. My brain is so overloaded with information, I'm beginning to feel like it isn't there at all.

I learned last year a few things that were very helpful for me this year. Last year I struggled with notepads and pens and then trying not to lose all the pieces of paper I had acquired after I got home.

In the last 8 months or so I bought myself an iPad. Not really being a fan of Apple's soft keyboard I got myself a bluetooth keyboard/case to go with it. Saw at least one person with the ZAGGFolio case, my personal preference is the Airbender from New Trent. Used it heavily all week long in combination with Evernote as the note taking app and it worked brilliantly.

A downside to the conference was the wifi - while it seemed to work brilliantly for anything that didn't require an SSL connection (with the sole exception, oddly enough, of the session review site, couldn't get that to work on my iPad at all), I couldn't make an SSL connection to anything.

A new change this year was using the Guidebook site for electronic recording of speaker review forms. Even with the tablet access issue, I was able to use my phone to record session reviews with ease.

I was fortunate enough to also be there for both Pre Cons on Monday and Tuesday.

On Monday, Argenis Fernandez and Robert Davis talked about Best Practices for DBA's. While they covered some topics that I had already researched well and implemented, there were still lots of ideas for things to look at. I also met a new friend on Monday, a DBA from Singapore who I will be sure to follow up with this week.

Had a brief moment to introduce myself to Argenis and thank him for his assistance on Twitter. Whenever I post to #sqlhelp he usually has some good input.

On Tuesday I decided to spend the day with Allen White and go over some detail in Powershell. As a complete Powershell novice, I was very impressed at the things that can be done with this language.

I will need to look into getting this framework installed on all my servers and seeing just what I can leverage from it.

Tuesday evening was a great time to catch up with a former colleague of mine from my previous employer (if you saw the Princess wandering around on Thursday, it was her) and also got to meet face to face with one of my customers for the first time.

I tend to skip the Keynotes because I'm just not that excited by the whiz bang new features that Microsoft has to offer as they are announced - because I know I won't see any of them for a long time anyway, and it gives me a couple of hours in the morning to catch up on email and tend to any issues before hitting the sessions.

Highlights of Wednesday's sessions, for me, were Erin Stellato's morning session on DBCC options, Bob Dylan (Brent Ozar) doing a Lightning Talk on the war going on inside tempdb and the fabulous afternoon session with Tom LaRock and Tim Chapman (Choose your own adventure). For those who weren't there, this was a cleverly designed session where Tim and Tom had concocted a poorly performing system. The scenario was - the server is slow and we can't work. Every minute the server is in this state, it's costing the company $250,000. Fix it.

They would ask for suggestions of what to look at from the room and then follow it up with "Why that? What will that tell you, what are you looking for".

There was some controlled chaos in some parts, with people yelling out all sorts of ideas but it was a brilliant concept for a session. Kudos to you, gentlemen.

Wednesday evening was the amazing Hard Rock Cafe Karaoke. Note for next time, if you want to sing, be up there early - by the time I had imbibed enough liquid courage to put my name down, the list was full. The live band were amazing and the performances by the attendees were (for the most part :) ) pretty amazing as well. One thing I did learn was that I have apparently led a musically sheltered life because I had never heard Creep by Radiohead before - and I heard it twice that night (and once more the following night at EMP). Big thanks to Pragmatic Worx, HP and Microsoft for sponsoring that one.

Thursday's highlights included a session from Brad Mcgehee on OS tuning and Brent Ozar's session on his sp_Blitz script. The new features in this new version give me high hopes that I might get some insights into tracking down a recurring process at my shop that locks the database to high heaven at the start of every month (I really didn't know you could pull execution plans out of the DMV's and place them in a free tool and get information on the parameters and what not out of it). Very excited about looking more into that at the start of next month.

The look on Brent's face after the session when my question started with "I have this really old COBOL based app that connects to SQL Server" was worth the price of admission.

Finally Friday came and topped off the week with some good tips from Kevin Kline on virtualization. We really don't virtualize database servers (they tend to be physical boxes) but I'm looking forward to digging more into this area.

There was so much to learn this week that it is going to take weeks to go through it all on every server and work out just what improvements can possibly be made.

Favorite session of the week is probably a split between Tom/Tim's "Choose Your Own Adventure" session or Brent's "sp_Blitz". If I had to choose, I think Tom and Tim would come out on top by a hair.

Next year, the Summit is off to North Carolina and I don't know if I'll be making it (we seem to be taking a direction more and more to Oracle in the future and less and less on SQL Server, and if that trend continues, I'll likely be investing more in Oracle education than in SQL Server)

Still, all in all a great week of education. Next on the list is the Rocky Mountain Oracle User Group Training Days in Denver in February. Same kind of thing as PASS Summit, for for Oracle (and always a great couple of days of training)! If you do Oracle, can't recommend this conference highly enough, go take a peek at http://www.rmoug.org/training/training-days-2013-registration/ for full information.

Many thanks to the organizers, the Board of Directors and the volunteers who made this all possible.