Lessons From OOW09 #3 – What’s New with Streams?

Posted: October 20, 2009 | Author: | Filed under: 11g, openworld09, streams | 2 Comments

The big news with streams is the Golden Gate acquisition. Oracle acquired Golden Gate, a smaller company that has a solution very similar to what Oracle Streams is doing. During OpenWorld Oracle announced that Golden Gate is the new strategic direction and that future development will be towards integrating Streams with Golden Gate. Oracle will still support streams, fix bugs, etc – but don’t expect new features other than the Golden Gate integration.

I missed the session where Golden Gate explain what its all about, but I’m planning to invite Golden Gate representative to give us a presentation and explain exactly what they do.

In the mean while, interesting new stuff with the old streams:

  1. One to many replication should be faster and more reliable in 11gR2.
  2. New statement DML handlers – allows manipulating LCRs using SQL statements instead of the PL/SQL code used in the past. According to Oracle it should be 4 times faster this way. One common use case of DML handlers that can now be implemented with the statement handlers is converting “delete” statements into an update that marks a row as “deleted” by modifying a varchar in a column.
  3. Keep Columns – new rule based transformation that allows you to specify which columns should be preserved in an LCR. In the past you can specify columns to drop, but not which columns to keep.
  4. Built in script for recording changes in a log table. This is an incredibly common use case for Streams, and Oracle now has a single command that automatically sets up the necessary statement handles and keep columns. Just call DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE.
  5. XSTREAM – Oracle says its a new API for allowing external programs to insert and pull changes from streams. Its cool, but I’m not convinced its new, since I’ve heard about a similar feature in previous versions under a different name.
  6. Streams performance advisor – thats an 11gR1 feature, but I didn’t know about it. Its a PL/SQL package and a bunch of views that can be used to report on streams performance. It should also be able to detect your topology automatically. You use this by running DBMS_PERFORMANCE_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE and checking a bunch of views. Documented here.
  7. If I followed correctly, the new advisor package uses the UTL_SPADV package, that you can also use for performance analysis or monitoring.
  8. DBMS_COMPARISON – a package that can be used to compare tables between two servers (one should be 11g and the other 10g or 11g). Can be useful when troubleshooting streams.
  9. Streams cross DB tracing – allows tracking a message when trying to troubleshoot apply that doesn’t work properly. You enable message tracking in a session (set message tracking) and then you monitor the views to see the actions that happen to it.
  10. 11g has greatly improved and more detailed apply error messages. This is probably my favorite new feature 🙂 Most of the time I no longer need to print the LCR to debug the issue.
  11. Not sure if this is 11gR1 or R2 – but apparently propagation is no longer managed by “jobs” but has now moved to the new scheduler, making it much more manageable.

    12. Bunch of nice improvements. I’m looking forward to seeing what Golden Gate is doing and why it is so much better.

Automatic Maintenance Tasks

Posted: August 27, 2009 | Author: | Filed under: 11g, tips | 6 Comments

Automatic Maintenance Tasks is a new 11g feature which I recently noticed. Its a bit embarrassing, since I’ve had 11g in production for nearly a year and apparently I’ve been using the feature all along.

I discovered the feature when I noticed that the automatic statistics gathering job is running several times on a weekend, instead of just once as it did in 10g. Then I discovered that the job has a very strange name starting with ORA$, and that the name changes every time the job runs.

Turns out that Oracle’s automatic jobs are not longer jobs. They are now Maintenance Tasks.

Here’s how Oracle defines the tasks:
“When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window. Each job is assigned a job name that is generated at runtime. All automated maintenance task job names begin with ORA$AT. For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26. When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.”

And here’s the reason my statistics ran several times on a weekend:
“In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size”

What practically changed? Almost nothing, we had schedule windows in 10g, and the maintenance jobs (not tasks) ran within the defined windows. I’ve no clue why this change was necessary.

It definitely looks like infrastructure prepared for a future cool feature. At present, it just looks weird. For instance:

  • You can’t add tasks. Oracle has 3 predefined tasks – statistics, space advisor and tuning advisor. You can add or remove maintenance windows and define in which window to run each task, but you can’t add your own task.
  • There are lots of seemingly unnecessary definitions around. For example, from the dictionary tables, you can see there are task clients and task jobs. Currently it looks like they are the same thing, since there is a one-to-one relation between them, but it probably won’t stay this way.
  • The documentation doesn’t document much. There are fields such as client attributes with values that are not really explained anywhere.
  • The API is really weak. As I said, you can’t do much beyond enable/disable tasks in specific schedules

So far, it looks like this feature adds confusion but no value. I hope Oracle will do something fun with it in the future.

Shrinking Tables Once More

Posted: April 13, 2009 | Author: | Filed under: 11g, plsql, tips | 3 Comments

Commenting on my post from last week, “an oracle dba” said:

“”shrink” would down the HWM of object, but may not for datafile. and it would invalidate the dependent plsql/trigger definitely.”

I did not believe that! Surely something as innocent as shrinking has no reason to invalidate the triggers. Maybe in 10g, but 11g has Fine Grained Dependencies, surely merely shrinking can’t invalidate a trigger in 11g!

So I tried it:

SQL> create table test2 (ID number, c_date date);

Table created.

SQL> create trigger trig1
  2  after insert on test
  3  referencing new as newrow
  4  for each row
  5  begin
  6  insert into test2 values (:newrow.id,sysdate);
  7  end trig1;
  8  /

Trigger created.

SQL> select object_name,object_type,status from user_objects where object_name='TRIG1';

OBJECT_NAME         OBJECT_TYPE          STATUS
-------------       -------------------  -------
TRIG1               TRIGGER             VALID

SQL> delete from test where mod(id,5)=0;

1000 rows deleted.

SQL> alter table test enable row movement ;

Table altered.

SQL>  alter table test shrink space cascade;

Table altered.

SQL> alter table test disable row movement ;

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TRIG1';

OBJECT_NAME         OBJECT_TYPE          STATUS
-------------       -------------------  -------
TRIG1                TRIGGER             INVALID

Learn something new every day 🙂
Thanks “an oracle dba”!

Alert Log Aliases for 11g

Posted: August 11, 2008 | Author: | Filed under: 11g, tips | Leave a comment

I love having short cuts to frequently used commands. 11g forced me to change few of them:

alias tailalert=’tail -f $ORACLE_BASE/admin/$ORACLE_DB/bdump/alert_$ORACLE_SID.log’
Became
alias tailalert=’adrci exec=”set editor vim;set home diag/rdbms/`echo $ORACLE_DB | tr A-Z a-z`/$ORACLE_SID; show alert -tail -f”‘

and

alias vialert=’vi $ORACLE_BASE/admin/$ORACLE_DB/bdump/alert_$ORACLE_SID.log’
Became
alias vialert=’adrci exec=”set editor vim;set home diag/rdbms/`echo $ORACLE_DB | tr A-Z a-z`/$ORACLE_SID; show alert”‘

I’m not sure why the DB Name appears as lower case in the path while the SID is upper case. Maybe a mistake I’ve made during the installation, but in other places that use DB name as a parameter (such as datafile path), it appears in upper case (as it should).

XML Alert Log

Posted: April 4, 2008 | Author: | Filed under: 11g, musing | 4 Comments

By now everyone discovered the new alert log in 11g, XML format and all. For the 11g challenged, I’ll point at the new official way of viewing your alert log: Say hello to ADRCI and bye bye to vi, less, more, tail and grep. Except that I’m not saying bye to vi any time soon, I’m saying “VI, meet XML Alert log. I hope you two will be good friends and get along fine because you’ll be seeing each other a lot in the near future”.

When I tried to look at the XML alert log with an XML viewer, it turned out that the alert log (and listener log) are not XML documents at all. At least not well formed one. Well formed XML document should contain one root element which contains all other elements. Oracle logs contain many MSG elements, all at top level.

I also noticed that Oracle failed to release an xml schema, or any other document describing the format of the alert logs and would allow proper parsing of it (proper – not based on guesses and reverse engineering).

Also, it is interesting to note that each error message element contains many attributes, bloating the alert log to a worrying size. I really could live without knowing that every single message in my alert log was written by org_id=’oracle’. On the other hand, knowing the PID is nice. The fact that all this information is kept as attributes of MSG element and not as child elements should make it easier to keep related messages together when using XML PATH to get information out of the file.

Why read only?

Posted: August 7, 2007 | Author: | Filed under: 11g, plsql | 3 Comments

Over at AMIS technology blog, Jurgen Kemmelings writes about read only tables, a new feature in Oracle 11g.

I’m trying to find good use cases for read only tables. Maybe they are like constants in C++. I used constants for two reasons: I wanted the compiler to find errors if I attempt to modify something I shouldn’t by mistake, and I wanted to take advantage of optimizations that are possible if the compiler knows in advance that something will not change.

Problem is, I’m not sure how much this applies to Oracle’s read only tables.
In some cases, the compiler will probably catch me if I attempt to update a read only table, but if I used dynamic queries, it probably won’t (Oh, I wish I had 11g here so I could test it!). Well, some protection is better than nothing. What about optimizations? Will Oracle’s optimizer use the fact that the table can’t change? It makes the table a better candidate for caching, assuming it is not too large.

In general, there is not much information about this feature out there. I hope that in the near future white papers and articles will be written, and the usefulness of this feature will become clearer.

Installing Oracle 11g

Posted: August 1, 2007 | Author: | Filed under: 11g, Oracle | 3 Comments

I didn’t install Oracle 11g. Augusto Bott over at Pythian group installed 11g on Ubuntu, which is really cool considering how Ubuntu is not officially supported and all. What’s the challenge in installing Oracle on a supported platform?

Apparently, the challenge is finding Oracle 11g to install. I’ve no idea how Augusto got his copy. I guess you have to have an insider access to Oracle for it, because I’ve searched OTN today and all I found was a bunch of whitepapers. Fascinating reading, but not what I wanted. I thought 11g was released already, but it doesn’t seem to be fully released.

So, here I am, itching to play with the new toy, and all I can do is read articles about how the other kids are having tons of fun with it.

If any of my kind readers happened to be an Oracle insider and know how to get a copy, please feel free to drop me a line, or better yet – a CD!

To upgrade or not to upgrade

Posted: July 17, 2007 | Author: | Filed under: 11g, Oracle | 2 Comments

So 11g was finally announced and it seems to have some very cool features. Database replay is the largest and most attractive new feature, maybe it will finally allow us to have an accurate staging environment. Direct NFS also sounds interesting, especially for the added instrumentation and monitor opportunities. Table Space encryption sounds good, especially if it will allow us to get rid of the scary encryption device we currently use. Aside from completely new features, Oracle promises improvements to features I already enjoy – Data Pump, Partitions, Automatic Memory Management, building indexes online and lots more.

I didn’t get to install a test instance of 11g yet, but I’m planning to get to it as I can’t wait to try it hands-on.

Contrary to what Oracle believes, we are not even thinking of installing it on anything that remotely resembles a production system until Oracle releases service pack 1 or maybe even 2. Oracle managed to teach the entire DBA community to avoid new versions like the plague and new features even more. I thought Oracle knew this, but now I’m not so sure. I opened an SR in Metalink regarding a bug I found on 10.2 and needed a fix (It was a rather horrible bug which completely prevented us from collecting statistics), and Oracle Support claimed that this bug will not be fixed in 10.2 and that we should upgrade to 11g which will be released this month. We insisted that we won’t upgrade to 11g in the next year and that we will need statistics sooner, and eventually they did release an opatch, but this was rather unpleasant experience.

While we don’t plan to upgrade to 11g soon, it does raise an interesting question about the systems that we never got around to upgrading from 9i to 10g. Do we upgrade them to 10g or wait with 9i until 11g becomes stable? I prefer to upgrade, because my work is easier when all of my systems are the same version (and you get addicted to features like regular expressions very quickly), but the customers know that 11g was released and are applying pressure toward waiting a bit and then giving them the latest.

If you are planning to install 11g on production any time soon, the following article by Dizwell will probably change your mind.  In any case, you probably want to read Arup Nanda’s 11g new features series.