The Oracle Instructor

Today, I finished in Munich the two days OCM Exam. That was really hard work! Nobody should underestimate the requirements in form of knowledge and experience for that hands on exam.

If you look at the topics, presented here and you can honestly say: “I am familiar with this stuff.”, then go for it. Otherwise practice and practice and repeat that until you can. 

Well, I am quite confident that I passed the exam, but it was not easy – although I am teaching  most of the Oracle University courses, it is built upon.

As mentioned before, we can query the content of a table from the past (since 9i with flashback query) and even reinstate the past content of that table (since 10g with flashback table to timestamp) by making additional use of before images stored in the undo tablespace. Of course, this is only possible as long as these before images in the undo tablespace are not overwritten by new ones.

A new feature of the 11g version is to safe the information from the before images for selected tables, thereby enabling a flashback query or flashback table to timestamp for these selected tables even years after their modification. That is called Total Recall and works like this:

SQL> create flashback archive fla1 tablespace tbs1 retention 10 year;

SQL> alter table hr.departments flashback archive fla1;

You will now be able to do a flashback query for the departments table in 10 years until today and to any time in between!

One question that I get asked often in my Data Guard courses (also this week) is, how to start the observer for Fast-Start Failover in the background.

Many customers use a remote terminal emulation to manage their Data Guard configuration, so it would be a problem if it would be required to keep the window open on which they started the dgmgrl-shell for the observer. This is also not very well documented in our – apart from that very helpful – Online Documentation. On the Linux command line, it works like this:

nohup dgmgrl -silent sys/oracle@prima "start observer" &

This gives you a text file on your current directory called nohup.out on which you can do a tail -f later on, if you would like to see te actions of the observer. The Data Guard Observer is kind of quiet, it waits with messages until there is actually something to do. You can verify its presence with dgmgrl from the primary site with the command

DGMGRL> show configuration verbose

Configuration
 Name:                myconf
 Enabled:             YES
 Protection Mode:     MaxAvailability
 Fast-Start Failover: ENABLED
 Databases:
 prima - Primary database
 physt - Physical standby database
 - Fast-Start Failover target

Fast-Start Failover
 Threshold: 30 seconds
 Observer:  uhesse

Current status for "myconf":
SUCCESS

Alternatively, you can query v$database like this:

SQL> select FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT,FS_FAILOVER_OBSERVER_HOST
     from v$database;

Presently, I am teaching a 10g Data Guard course in Munich (Germany). Over the last quarters this has become one of my most frequent courses – which is quite a good thing because I really like the product and the course

I have uploaded a collection of Data Guard related Metalink Notes to the Downloads page that I start to distribute to my students with this class.

I just came back from an Inhouse course in Basel (Switzerland) for the Novartis AG. It was about Partitioning in general and about some 11g New Features in this area. The present Novartis IT staff was very experienced (some of them worked with Oracle since version 5) and friendly – so we had a pleasant atmosphere to work in. I would like to share one of my demonstrations from this class with the Oracle Community.

One very useful feature about partitioning that is available since version 8 is the possibility of partition pruning. That means, that the optimizer during the parse phase of a SQL statement is able to exclude certain partitions from scanning because according to the definition of the partitioned table present in the Data Dictionary, the desired rows can’t be in those partitions.

When we for example have a table that is partitioned by range on a date column with one partition for each quarter of the year, it is implicitly clear for a query like

select * from t where time_id=to_date('01-01-2009','dd-mm-yyyy');

that the desired rows can only be in partition Q1. Without the presence of indexes, this query is round 4 times faster (because we have 4 assumed roughly equal filled partitions) than a Full Table Scan. An 11g New Feature is interval partitioning. Before 11g, you have to create a new partition on a range partitioned table manually, if you want to add a new range (for example a new quarter). Interval partitioning does that automatically.

The following demonstration shows the benefit of partition pruning & interval partitioning. I did it on my notebook with Oracle Enterprise Linux and 11.1.0.6 . The two tables with 20 Million rows need round 600 MB space and it took a couple of minutes (less than 15) to create them.

SQL> create table nonpart (id number, name varchar2(50), salary number);
begin
for i in 1..10000000 loop -- insert 10 Million rows
 insert into nonpart values (i, 'John Doe', 9000);
end loop;
commit;
end;
/
SQL> set timing on
SQL>select salary from nonpart where id=4711 -- FTS on 10 Million rows table;

 SALARY
----------
 9000

Elapsed: 00:00:04.13

Now we create the partitioned table with very little coding effort:

SQL> create table part
 (id number, name varchar2(50), salary number)
partition by range (id)
interval (10000) -- one partition each 10000 ids
(partition p1 values less than (10001));

The following insert creates 999 partitions automatically!

SQL> insert into part select * from nonpart nologging;
SQL> commit;

Due to partition pruning, even with this relatively tiny tables we see a dramatic speed up of the query:

SQL> select salary from part where id=4711 -- scan on 10.000 rows partition;

    SALARY
----------
      9000
Elapsed: 00:00:00.05

You can also tell from investigating the execution plan via set autotrace on or explain plan for that the above query made use of partition pruning, but I like to see (and show) that in this fashion a little more obviously