Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. The method I’ve had the most success with, is to install both the 32- and 64-bit Oracle clients (in separate directories). I’m not sure why you have to have both clients. The only explanation I’ve seen is that part of SQL Server 64 is still 32-bit. I’m not sure if this is true, but if you look at the shortcuts, SSMS and Visual Studio’s EXEs reside in the 32-bit Program Files folder:

SQL Server Management Studio and DTEXECUI.exe can be found in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\

Visual Studio: C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\Devenv.exe

If you look at SQL Server and SQL Agent, they both reside in the 64-bit folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

Once both clients were installed, I was able to access the provider in SSIS, and also successfully schedule and run package. The trouble started when the requirements for the project changed, and we decided to use SQL in a stored procedure with a linked server instead of SSIS. I thought this would be very simple to get working, since I already had the providers installed and working in SSIS. I was wrong.

We created the linked server and used OPENROWSET to pass in the queries that the client had written and tested in SQLPlus running against their test system. Now it was time to try out our linked server. I passed it a simple query and instead of seeing some records, got this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Withheld] reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (”IID_IDBCreateCommand”) from OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Witheld]

Naturally, I thought something had changed with our credentials used to access Oracle, so we tested the query and credentials in SQLPlus, which worked. Thinking something had gotten messed up on the SQL Server, I decided to check the providers, and ran the query from SSIS against the provider/Oracle data source. This worked.

We tried creating the linked servers with different settings, and even tried creating them on different servers. Nothing worked.

At this point, I was very frustrated, and thinking about how best to do what we wanted in SSIS. I did some research on the web and eventually stumbled on OPENQUERY - Access denied which suggested the “Allow InProcess” option be enabled on the provider. I enabled this option (instructions below), closed all open query windows, opened a new one and then re-tested my simple/test query using

OPENROWSET

and everything worked like a charm.

Happy cross-platform querying.
Chris.

Here are the step by step instructions to enable this:

1. Expand Linked Servers | Providers.
2. Right click on the Ora provider and go to Properties.
3. Find the “Allow InProcess” option and tick it.
4. Then make a new connection window and run your query (I had to close my open connection windows).

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner, Andreas Scherbaum says, “Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem . . .  This . . . is uneloquent, error prone and does not scale well.  . . .  PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.”

David Fetter looks into WITH (so much drama in the CTE): ” By now, you’ve probably seen that PostgreSQL 8.4 can produce Mandelbrot sets . . . but what are Common Table Expressions really about? [They] are just views you create at run time. Oh, and they can refer to themselves. That latter capability is powerful and flexible (read confusing), so I’ll go over that one later. Run-time views are pretty handy, though.”

Andrew’s PostgreSQL blog introduces parallel pg_restore for PostgreSQL 8.4: “I try to complete at least one significant feature item per PostgreSQL release. This time the feature is making pg_restore run in parallel. This is quite important for many users, particularly some large enterprise users.”

SQL Server

Eric Johnson introduces SSIS 2008 and the new lookup: “SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved.”

Kimberly L. Tripp was thinking about looking for security vulnerabilities in database code. “I’ve always been concerned with security and I’ve always stressed the importance of auditing the REAL user context not just the current user . . .  So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters . . .  Having said that, what if I’m looking at a database for the first time . . .  I’ve come up with a quick query… And, while it’s not going to “solve” your problem . . . or even truly verify if you’re vulnerable, it gives you a ‘quick list’ of where you should look first! ”

Adam Machanic exposed the hidden costs of INSERT EXEC, beginning, “INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision–seemingly purely aesthetic–can bring to the fore.”

Martin Bell offered the reminder, after disabling TDE you still requires the certificate to restore the database.

Data Management has a first-rate HOWTO on dynamic column names and fields in SSRS. “I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense [sic] this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.  . . .  So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT.”

MySQL

Here on the Pythian Blog, Sheeri Cabral expressed some concerns and what does not work in XtraDB backup.

On xaprb, Baron Schwartz has a review of MySQL Administrator’s Bible. “I think that MySQL Administrator’s Bible is a very good complement to High Performance MySQL, 2nd Edition. The former is useful to people who have varying levels of knowledge, while the latter assumes a lot of experience and doesn’t cover introductory material much.  . . .  So if you’re new to databases, or if you’re new to MySQL, you might do well to start with Sheeri’s book . . . ”

Over on the MySQL Performance Blog, Baron looked into gathering queries from a server with Maatkit and tcpdump: “For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.”

Ronald Bradford looked at verifying MySQL replication in action, with “ . . . a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.”

The MySQL Workbench Team Blog says, Time To Upgrade - MySQL Workbench 5.1 Is Here on Win, Mac & Linux. Perty pictures follow.

Oracle

H. Tonguç Yilmaz asserted, Instrumented Code is Better: An Example on How to Generate Session Level ASH Reports. ” After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on a specific session, the filter options at ASH report screen at EM can be used for this purpose or below is an example to monitor the waits from v$active_session_history view.”

Doug Burns responded with his item on session-level ASH reports: “I think [Tonguç's] post is really showing two different things, one more successfully than the other.”

Randolf Geist reports a Dynamic sampling and set current_schema anomaly: “If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . .  This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn’t do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.”

On the Oramoss Blog, Jeff Moss looks at the case of no pruning for MIN/MAX of partition key column: “Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.” Jeff and his readers examine the evidence and discuss some workarounds to the problem.

Oracle, MySQL, PostgreSQL, SQL Server. How’bout . . . 

NoSQL

(No SQL?!? Edwards, you’re mad!) Well, it’s not me. Here’s Curt Monash on NoSQL: “Eric Lai emailed today to ask what I thought about the NoSQL folks, and especially whether I thought their ideas were useful for enterprises in general, as opposed to just Web 2.0 companies. That was the first I heard of NoSQL, which seems to be a community discussing SQL alternatives popular among the cloud/big-web-company set, such as BigTable, Hadoop, Cassandra and so on.”

PostgreSQL stuff also had some thoughts on those who say No to SQL. “Every time I see something or hear something like this I sigh a little bit. Not only when it’s related to SQL but in the world of computer professionals in general. ‘The right tool for the job’ seems to be a hard concept to understand sometimes. I wonder why?”

Back to Oracle for a moment. Tanel Poder has a secret preview of Oracle 12g CBO leaked from Oracle labs. Would you like fries with your cost-based optimizer?

That’s all for now. See you in a week’s time!

As you know, Oracle Database Standard Edition One—as well as Standard Edition—does not have the Data Guard feature. Therefore, I had to do everything manually.

The whole process took less than 15 minutes. This includes less than five minutes of full downtime to restart the database in READ-ONLY mode, and less than 10 minutes of READ-ONLY downtime.

Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.

Here is what I had. The primary database and one physical standby database:

• OS - SUSE Linux ES10 (SP2) x86_64
• Oracle - Release 11.1.0.7.0 64bit SE1

First of all, I switched the standby database to the primary role.

Step 1. Shutdown the primary database

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

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

Step 2. Make a copy of the control file, the spfile, and the redo logs:

SQL> !cp control01.ctl copy/control01.ctl.primary
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.primary
SQL> !cp *.log copy/

Step 3. Startup the primary database in READ-ONLY mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Step 4. Copy all needed archive logs, the copy of the control file, the spfile, and the redo logs to the standby server ora2:

oracle@ora1 /u01/app/oracle/testdb/oradata> scp ../archivelogs/1_152_333215132.dbf oracle@ora2:/u01/app/oracle/testdb/archivelogs/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/control01.ctl.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/spfilepb.ora.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo01.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo02.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo03.log oracle@ora2:/u01/app/oracle/testdb/oradata/

Step 5. Apply all needed archive logs on the standby database:

oracle@ora2 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> recover standby database until cancel;
ORA-00279: change 2244877 generated at 10/07/2008 14:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf
ORA-00280: change 2244877 for thread 1 is in sequence #152

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2245162 generated at 10/07/2008 14:53:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_153_333215132.dbf
ORA-00280: change 2245162 for thread 1 is in sequence #153
ORA-00278: log file '/u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Step 6. Shutdown the standby database:

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Step 7. Make a copy of the controlfile and the spfile.

To make it easier I just switched spfiles.

SQL> !cp control01.ctl copy/control01.ctl.stndby
SQL> !scp copy/control01.ctl.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.stndby
SQL> !scp copy/spfilepb.ora.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/

Step 8. Replace the standby controlfile and spfile with the copy of the primary control file and spfile:

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/spfiletestdb.ora.primary /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.primary control01.ctl
SQL> !cp copy/control01.ctl.primary control02.ctl
SQL> !cp copy/control01.ctl.primary control03.ctl

Step 9. Switch the standby database to the primary role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' size 100m reuse autoextend on next 100m maxsize 2048m;

Tablespace altered.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE, STANDBY_BECAME_PRIMARY_SCN from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE    STANDBY_BECAME_PRIMARY_SCN
------- ---------- ---------------- --------------------------
CURRENT READ WRITE PRIMARY                             2244877

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

And finally, I switched the old primary database to the standby role.

Step 10. Shutdown the old primary database:

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

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

Step 11. Replace the old primary control file and spfile with the copy of the standby control file and spfile

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !rm temp01.dbf
SQL> !rm *.log
SQL> !cp copy/spfiletestdb.ora.stndby /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.stndby control01.ctl
SQL> !cp copy/control01.ctl.stndby control02.ctl
SQL> !cp copy/control01.ctl.stndby control03.ctl

Step 12. Switch the old primary database to the standby role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE
------- ---------- ----------------
STANDBY MOUNTED    PHYSICAL STANDBY

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

So you can see that Oracle 11g SE1 (SE) switchover of primary database and one physical database located on different servers is a very simple process. But if you have the physical standby on the same server and use OMF, you should consider two things.

1. How to change the path to the datafiles and redo logs in the control files;

   Solution:In Step 9, after the database is mounted and before it is opened, all datafiles and redo logs should be renamed using the ALTER DATABASE RENAME FILE command.

2. How not to remove the old datafiles. OMF will remove the old files after the RENAME command.

   Workaround: Move the files to a temporary directory and move them back after the RENAME command. The old primary database should be down.

The same process can be done with a RAC database.

And do not forget to make a database backup in case something goes wrong.

The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.

However, we did figure out why Xtrabackup had to be run as the mysql user:

Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:

xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.

InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.

When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]

On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.

Regarding the complaint Dan R commented on the previous post that innobackupex could stream the backup to gzip, and helpfully gave the syntax. Shlomi Noach also pointed out streaming as a feature of Xtrabackup that ibbackup does not have. However, Gerry Narvaja, a co-worker noted (and commented):

I’ve been trying to install xtrabackup/innobackupex for a customer and I’m finding a few glitches, especially w/ streaming:

We use innoback(ex) wrapped in our own scripts to allow for rotation and other operations. We also use ZRM for some installations, so this would apply to integration with this tool as well. These are the glitches I found:

1. Using streaming by piping it into ‘gzip’ masks the return code from innobackupex.pl. Since gzip will almost always return 0, you can’t rely on it to determine backup success.

2. The next alternative would be to review the innobackupex.pl’s output for the OK at the end. But since it redirects the output to ’stderr’ to allow for streaming, you need to add “2> innobackupex.log” before piping and grep for the “OK” at the end.

and noted that there were some limitations:

innobackupex script is limited in the type of options you can specify compared to what the xtrabackup executable supports. I find this annoying since it limits the directories where you can have the backups, data directories and logs.

Xtrabackup doesn’t work for MySQL v4.1. In the Percona forums there was a suggestion that the 5.0 patch should work. This is true, but xtrabackup.c has other dependencies on 5.x definitions and structures I didn’t have time to review. Baron Schwartz correctly suggested that these dependencies might be trivial in a tweet directed to myself. I’ll post my findings to the Percona forums and hopefully we can soon have a patched version.

This year, the International Free Software Forum celebrated its 10^th anniversary. It happened last week in Porto Alegre.

Pythian presented a session on Thursday called 8 Rules for Designing More Secure Applications with MySQL.

As promised, here are the slides we used on that session: 8 Simple Rules to Design Secure Apps with MySQL (PDF).

Cheers!

Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”

Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”

Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers.  . . .  Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”

Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation  . . .  so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later  . . .  and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”

Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation.  . . .  So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”

In MySQL blogs, the MySQL Performance Blog announced Percona.tv. Ryan Loew writes, “We’ll be uploading technical screencasts, conference video, and anything else cool we can think up.”

The Open Query blog published Good Practice/Bad Practice: Table Aliases. “When writing queries, try making a habit out of using short table aliases, no matter how small the query is.” An example using the World DB follows, as does a worthwhile discussion.

Roland Bouman examined MySQL stored functions: the impact of DECLARE HANDLER on performance, responding to Get the error return value in a variable by Peter Gulutzan.

Ronald Bradford looked into using statpack with SHOW STATUS. He writes: “Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack.  . . .  Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings.  . . .  This post is more about detailing those little annoyances that I’d like to improve, or see improved.”

Morgan Tocker writes, “I think 5.1 gets some bad press for not being a compelling upgrade. It’s not the big features that make the difference, but the subtle ones. I wanted to highlight some of the these that may make your life easier when it’s time to upgrade . . . ” The post is Hidden gems in 5.1.

Let’s turn to SQL Server now, starting with a post from Aaron Alton, The HOBT. He writes, “If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command.  . . .  Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.” Click through for Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server.

Louis Davidson looked into the question of read/write ratio versus read/write ratio. That’s what it says, really.

Alexander Kuznetsov posted one of those skill-testing quizzes—calculating third Wednesday of the month with inline UDFs. Alexander begins, “Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as ‘third Wednesday of the month’, and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.”

Linchi Shea also was working the paradoxes: Performance impact: Logical scan fragmentation — 100% may not be as bad as 85%. “In [a] previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans.  . . .  The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.”

Reading is cool. I learned this from the Fonz. But look at this, Jeremiah Peschka says there are circumstances when less reading is a good thing. I’m confused! What else does he say? “Less reading is a good thing when you’re reading from a physical disk.” Ahhh. “During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called.  . . .  It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?” I bet he hits his server machine in just the right way.

In PostgreSQL blogs, David Wheeler and his readers discussed the pgTAP Set-Testing Update.

The Postgres OnLine Journal exposed a restore of functional indexes gotcha.

In the DB2 world, Craig Mullins advised: know your ISOLATION levels. Craig writes, “Did you know that DB2 provides a way to change the way that a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement.”

Willie Favero published the second of a couple articles collecting resources for both DB2 z/OS and DB2 LUW.

That’s all for now. Please add your favourite blogs from this week to the comments. And of course, don’t forget that you can publish an edition of Log Buffer on your own blog. Just send me an email to get started.

Till next time!

What: Sydney MySQL User Group meetup #7 - The Reboot

When: July 16, 2009 5:30 PM (please don’t forget to RSVP yes/no/maybe)

Where: Sydney, CBD - join the meetup for exact location.

We are back! After 3 years of being silent, SMUG (can I call it so? I know there are conflicts with other acronyms) resurrects the meetings.

The logistic of the meetup is the following:

• 5:30pm — the gathering starts and we have pizza and beers and talking your peers
• 6:00pm — we start the presentation
• 7:00pm — break and peer networking consuming the leftovers
• 7:30pm — we have an open (but slightly moderated) discussion. Topic is TBC. Perhaps, we can talk about future of MySQL now that Oracle’s bought Sun?

The schedule is very approximate and we will be quite flexible. We should finish about 8:30pm.

Presentation: Migrating 1 Terabyte Database to MySQL — Case Study by Alex Gorbachev (well, that’s me).

This presentation is based on the real project migrating from RDB on OpenVMS to MySQL on Linux. We will review the migration of the data itself and what infrastructure has been setup — high availability cluster with Heartbeat, DR site replication, organizing backup for 1 TB database. The system has been successfully put in production few months ago.

The attendance is completely free but of course someone need to cover the venue and that great stuff we are going to eat and drink so…

My company, The Pythian Group, is sponsoring the SMUG for now. You probably heard of Pythian, the leading provider of database services for MySQL as well as Oracle and SQL Server.

Nice people from Navicat, kindly offered to join us sponsoring the pizza and drinks. If you happen to manage MySQL databases you probably heard for Navicat, an award-winning database administration tool. More details - http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html

Those of you in Sydney — looking forward to see you all there.

The slides are brilliant even without seeing Theo talk and I highly recommend the time it takes to flip through them, for anyone who is interested in systems performance. If anyone took an mp3 of this talk I’m dying to hear it, please let me know.

For those of you unfamiliar with OmniTI, Theo is the CEO of this rather remarkable company specializing in Internet-scale architecture consulting. They generalize on Internet-scale architecture, not on one specific dimension the way Pythian specializes on the database tier. This allows them to see Internet-scale workloads from a unique systemic, multidisciplinary point of view; from the user experience all the way up the stack, through the load balancer (or not), the front-end cache, the application server, the database server, the operating system, the storage, and so on. This approach lets them build Internet architectures and solve scalability problems in a unique and powerful, wholistic way.

Pythian first collaborated with OmniTI in 2001, and they deserve all of their success and profile that they’ve built since then. Trivia: both Pythian and OmniTI were founded in September 1997 and both companies continue to be majority-owned and controlled by founders (in Pythian’s case, yours truly).

Here’s the slide deck. Let me know your thoughts.

This time, I’ve used VirtualBox to run a virtual machine (VM) to perform our work. (Virtualization has a number of advantages; in this case, I made several trial installs, trying different combinations and configurations. Having a pristine, basic set-up accelerated the whole process, since I didn’t had to reinstall from scratch on every new attempt.)

You might want to review the previous editions of this series, as there are technical references on this text fully detailed on previous posts. See these HOWTOs for Ubuntu:

• 8.10 - Intrepid Ibex
• 8.04 - Hardy Heron
• 7.10 - Gutsy Gibbon
• 7.04 - Feisty Fawn

Since we’re installing on a VM, we’ll be using Ubuntu 9.04 32-bit Server edition, , so let’s download it and check the MD5sum:

user@jackalope:/media/trezentos/downloads$ md5sum ubuntu-9.04-server-i386.iso
20480057590ff8b80ad9094f40698030  ubuntu-9.04-server-i386.iso
user@jackalope:/media/trezentos/downloads$

Download Oracle Database 11g Release 1 (11.1.0.6.0), and verify the provided cksum:

user@jackalope:/media/trezentos/downloads$ cksum linux_11gR1_database_1013.zip
1044354138 1844527800 linux_11gR1_database_1013.zip
user@jackalope:/media/trezentos/downloads$

Also, do not unzip this file on a Windows system or on a network share—unpack it on the target box or another Linux box, as there might issues with permissions on the filesystem (mostly related to the execution bit, relevant only on *NIX systems). After installing Ubuntu 9.04 Jaunty Jackalope from scratch, let’s update our software sources:

user@jackalope:~$ sudo su -
[sudo] password for user:
root@jackalope:~# apt-get update
Hit http://us.archive.ubuntu.com jaunty Release.gpg
Ign http://us.archive.ubuntu.com jaunty/main Translation-en_US
Hit http://security.ubuntu.com jaunty-security Release.gpg
Ign http://security.ubuntu.com jaunty-security/main Translation-en_US
Ign http://security.ubuntu.com jaunty-security/restricted Translation-en_US
(...)
Hit http://us.archive.ubuntu.com jaunty-updates/universe Sources
Hit http://us.archive.ubuntu.com jaunty-updates/multiverse Packages
Hit http://us.archive.ubuntu.com jaunty-updates/multiverse Sources
Reading package lists... Done
root@jackalope:~#

Then, upgrade everything:

root@jackalope:~# apt-get dist-upgrade
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following packages will be upgraded:
  acpid consolekit libck-connector0 libmpfr1ldbl libpam-ck-connector
  libvolume-id1 ntpdate screen-profiles ubuntu-serverguide udev
  update-manager-core
11 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Need to get 1565kB of archives.
After this operation, 24.6kB disk space will be freed.
Do you want to continue [Y/n]? y
Get:1 http://us.archive.ubuntu.com jaunty-updates/main ubuntu-serverguide 9.04.10 [284kB]
Get:2 http://us.archive.ubuntu.com jaunty-updates/main libvolume-id1 141-1.1 [102kB]
(...)
Processing triggers for initramfs-tools ...
update-initramfs: Generating /boot/initrd.img-2.6.28-11-generic
Processing triggers for libc6 ...
ldconfig deferred processing now taking place
root@jackalope:~#

It might be not necessary, but I do suggest you reboot now so that we’re on the same page. After logging in again, let’s install a few packages:

user@jackalope:~$ sudo apt-get install unzip build-essential x11-utils \
> rpm ksh lsb-rpm libaio1
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  binutils defoma dpkg-dev fontconfig-config g++ g++-4.3 gcc gcc-4.3
  libbeecrypt6 libc6-dev libdrm2 libfontconfig1 libfontenc1 libfreetype6
  libgl1-mesa-glx libgomp1 libice6 libneon27 librpm4.4 libsm6
  libstdc++6-4.3-dev libxaw7 libxdamage1 libxfixes3 libxft2 libxi6
  libxinerama1 libxmu6 libxpm4 libxrender1 libxt6 libxtst6 libxv1 libxxf86dga1
  libxxf86vm1 linux-libc-dev make ttf-dejavu ttf-dejavu-core ttf-dejavu-extra
Suggested packages:
  binutils-doc defoma-doc psfontmgr x-ttcidfont-conf dfontmgr libft-perl
  debian-keyring g++-multilib g++-4.3-multilib gcc-4.3-doc libstdc++6-4.3-dbg
  gcc-multilib manpages-dev autoconf automake1.9 libtool flex bison gdb
  gcc-doc gcc-4.3-multilib libmudflap0-4.3-dev gcc-4.3-locales libgcc1-dbg
  libgomp1-dbg libmudflap0-dbg glibc-doc libstdc++6-4.3-doc make-doc alien zip
  mesa-utils
The following NEW packages will be installed:
  binutils build-essential defoma dpkg-dev fontconfig-config g++ g++-4.3 gcc
  gcc-4.3 ksh libaio1 libbeecrypt6 libc6-dev libdrm2 libfontconfig1
  libfontenc1 libfreetype6 libgl1-mesa-glx libgomp1 libice6 libneon27
  librpm4.4 libsm6 libstdc++6-4.3-dev libxaw7 libxdamage1 libxfixes3 libxft2
  libxi6 libxinerama1 libxmu6 libxpm4 libxrender1 libxt6 libxtst6 libxv1
  libxxf86dga1 libxxf86vm1 linux-libc-dev lsb-rpm make rpm ttf-dejavu
  ttf-dejavu-core ttf-dejavu-extra unzip x11-utils
0 upgraded, 47 newly installed, 0 to remove and 0 not upgraded.
Need to get 26.6MB of archives.
After this operation, 81.9MB of additional disk space will be used.
Do you want to continue [Y/n]?
Get:1 http://us.archive.ubuntu.com jaunty/main libdrm2 2.4.5-0ubuntu4 [377kB]
Get:2 http://us.archive.ubuntu.com jaunty/main libxfixes3 1:4.0.3-2 [9634B]
(...)
Setting up g++ (4:4.3.3-1ubuntu1) ...

Setting up build-essential (11.4) ...
Processing triggers for libc6 ...
ldconfig deferred processing now taking place
user@jackalope:~$

Time to do a few changes on the OS. Since the Oracle Universal Installer prefers bash, let’s get rid of dash :

user@jackalope:~$ sudo su -
root@jackalope:~# cd /bin
root@jackalope:/bin# ls -l /bin/sh
lrwxrwxrwx 1 root root 4 2009-05-26 12:58 /bin/sh -> dash
root@jackalope:/bin# ln -sf /bin/bash /bin/sh
root@jackalope:/bin# ls -l /bin/sh
lrwxrwxrwx 1 root root 9 2009-05-26 13:27 /bin/sh -> /bin/bash
root@jackalope:/bin#

Let’s add a user and a couple of groups, as well as do some adjustments on our Ubuntu system so that it has a more Red Hat-ish layout:

root@jackalope:/bin# cd
root@jackalope:~# addgroup oinstall
Adding group `oinstall' (GID 1001) ...
Done.
root@jackalope:~# addgroup dba
Adding group `dba' (GID 1002) ...
Done.
root@jackalope:~# addgroup nobody
Adding group `nobody' (GID 1003) ...
Done.
root@jackalope:~# usermod -g nobody nobody
root@jackalope:~# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
root@jackalope:~# mkdir /home/oracle
root@jackalope:~# chown -R oracle:dba /home/oracle
root@jackalope:~# ln -s /usr/bin/awk /bin/awk
root@jackalope:~# ln -s /usr/bin/rpm /bin/rpm
root@jackalope:~# ln -s /usr/bin/basename /bin/basename
root@jackalope:~# mkdir /etc/rc.d
root@jackalope:~# for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
root@jackalope:~# mkdir -p /u01/app/oracle
root@jackalope:~# chown -R oracle:dba /u01
root@jackalope:~#

Let’s change some system-wide configuration files, starting with /etc/sysctl.conf:

root@jackalope:~# cd /etc
root@jackalope:/etc# cp sysctl.conf sysctl.conf.original
root@jackalope:/etc#

Edit it, adding the following lines to the bottom of the file:

fs.file-max = 65535
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65535
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Now let’s modify /etc/security/limits.conf:

root@jackalope:/etc# cd /etc/security/
root@jackalope:/etc/security# cp limits.conf limits.conf.original
root@jackalope:/etc/security# vi limits.conf
root@jackalope:/etc/security#

Add the following lines to the bottom of the file:

oracle soft nproc 2047
oracle hard nproc 16383
oracle soft nofile 1023
oracle hard nofile 65535

There is no need to change /etc/pam.d/login as on the previous versions of this HOWTO. You should reload all those sysctl changes, log out and log in again (I’m hitting ^d instead of typing exit):

root@jackalope:/etc/pam.d# sysctl -p
fs.file-max = 65535
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65535
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
root@jackalope:/etc/pam.d# logout
user@jackalope:~$ logout
Connection to 192.168.254.118 closed.
user@jackalope:~$

Time to unpack our ZIP file containing the database software on our target machine:

user@jackalope:~$ unzip linux_11gR1_database_1013.zip
Archive:  linux_11gR1_database_1013.zip
   creating: database/
   creating: database/doc/
   creating: database/doc/dcommon/
   creating: database/doc/dcommon/css/
  inflating: database/doc/dcommon/css/blafdoc.css
  inflating: database/doc/dcommon/css/bp_layout.css
(...)
  inflating: database/stage/properties/oracle.server_Custom.properties
  inflating: database/stage/properties/oracle.server_PE.properties
  inflating: database/welcome.html
user@jackalope:~$

And now it’s time to install Oracle:

user@jackalope:~$ whoami
user
user@jackalope:~$ sudo su -
root@jackalope:~# su - oracle
oracle@jackalope:~$ export DISPLAY=192.168.254.12:0.0
oracle@jackalope:~$ cd /home/user/database
oracle@jackalope:/home/user/database$ ./runInstaller -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 10236 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 729 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-05-04_01-51-00PM. Please wait ...oracle@jackalope:/home/user/database$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.
(...)

And this screen should appear for you:

It might spit some errors like this during the installation—just ignore these, as we’re not supposed to have a package DB anyway:

rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm

We leave it as it is:

And we’re going with a full-blown Enterprise Edition:

Now, here’s the part where we choose our ORACLE_HOME

It will check a few system requirements:

I just clicked on all the pending issues so that the status would read “User Verified”, then hit “Next”:

We’re not creating a database now:

And we set all groups to be dba:

It will show you a summary. This time, we’ll hit “Install”:

Go get yourself a good cup of tea, as it might take a while, but eventually you’ll be presented with a request to run two scripts as root.

For your reference, here’s a sample output from my box:

root@jackalope:~# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete
root@jackalope:~# /u01/app/oracle/product/11.1.0/jaunty11/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.1.0/jaunty11

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
root@jackalope:~#

After hitting OK, we have nothing else to do but to exit the installer.

And this concludes the database software installation.

Now, let’s create a listener so we can accept incoming TCP connections from other boxes. Notice that /u01/app/oracle/product/11.1.0/jaunty11 is our ORACLE_HOME. This tool might spit out some errors (see below):

oracle@jackalope:/home/user/database$ export ORACLE_HOME=/u01/app/oracle/product/11.1.0/jaunty11
oracle@jackalope:/home/user/database$ export PATH=$PATH:$ORACLE_HOME/bin
oracle@jackalope:/home/user/database$ netca

Oracle Net Services Configuration:
Xlib:  extension "Generic Event Extension" missing on display "192.168.254.12:0.0".
Xlib:  extension "Generic Event Extension" missing on display "192.168.254.12:0.0".
Xlib:  extension "Generic Event Extension" missing on display "192.168.254.12:0.0".

The messages above are safely ignorable, but the first screen may take a couple of minutes to show up. We’re setting up a listener:

That’s right, we’re adding a listener, as we have none configured so far:

Choose a name for your listener (it’s okay to leave the default if you’re configuring only one listener):

And we want to be able to accept connections via TCP:

On the standard port:

And we’re good with one listener:

Now we want to get back to the starting screen:

To then quit (by hittting Finish):

You’ll notice on the terminal window you fired up netca that it started the listener we just created (but the system ins’t ready yet—keep reading!)

Configuring Listener:LISTENER
Listener configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/11.1.0/jaunty11/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0

Time to create our database with dbca. On the same session, we call dbca. Just as with netca, the first screen will take some time to show:

A general purpose database fits our needs for now:

We’re calling ours ORA11DEV:

We’re not configuring Enterprise Manager this time:

Set a password (or different passwords, if you prefer):

We’re using a classic filesystem for this setup (no ASM or RAW devices:

We’ll be using the suggested template:

And we’re not configuring the Flash Recovery Area or Archiving on this setup:

We don’t want the sample schemas nor to run any custom scripts:

I’ve specified 250MB of RAM be used:

And on low-memory machines, it might be a good idea to limit the maximum number of running processes to a lower value:

I’ve gone with UTF . . . 

 . . . and running the database in dedicated server mode:

We’re going with the default security settings for Oracle 11g:

You may want to enable automatic maintenance tasks (I haven’t):

It will show you a preview of the what is about to be created:

If you want, you might want to ask the database creation assistant to save the scripts to review later. At this point, we hit “Finish”:

After asking for a final review . . . 

. . . your database will be created and started in a few moments:

When you see this, you’re database has been created and is running (but we’re not done yet).

Cool! So far, we have:

• the Oracle 11gR1 database software installed
• a listener configured and started
• an empty Oracle 11g database configured and started

It’s time to make all work we did so far start on every boot of this box, automatically. Before proceeding, what we want is to make sure everything works (you will notice I’m not using “exit” to exit SQLPLUS, but rather control+d):

oracle@jackalope:~$ export ORACLE_SID=ORA11DEV
oracle@jackalope:~$ . oraenv
ORACLE_SID = [ORA11DEV] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/jaunty11 is /u01/app/oracle
oracle@jackalope:~$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 4 19:17:46 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT INSTANCE_NAME, VERSION, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 VERSION	   STATUS
---------------- ----------------- ------------
ORA11DEV	 11.1.0.6.0	   OPEN

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

Total System Global Area  196681728 bytes
Fixed Size		    1298836 bytes
Variable Size		  130027116 bytes
Database Buffers	   62914560 bytes
Redo Buffers		    2441216 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@jackalope:~$

Next, we make it start on boot by editing /etc/oratab:

root@jackalope:~# cat /etc/oratab | tail -1
ORA11DEV:/u01/app/oracle/product/11.1.0/jaunty11:N
root@jackalope:~#

See that N on the line starting with ORA11DEV? Change it to Y — that will make it start on boot with the script we’re about to deploy.

Create a file called /etc/init.d/oracledb (as root). Inside, put this:

#!/bin/bash
#
# /etc/init.d/oracledb
#
# Run-level Startup script for the Oracle Listener and Instances
# It relies on the information on /etc/oratab

# these are the paths for our base installation
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/jaunty11
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
    echo "Oracle startup: cannot start"
    exit 1
fi

case "$1" in
    start)
        # Oracle listener and instance startup
        echo -n "Starting Oracle: "
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        touch /var/lock/oracle
        echo "OK"
        ;;
    stop)
        # Oracle listener and instance shutdown
        echo -n "Shutdown Oracle: "
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        rm -f /var/lock/oracle
        echo "OK"
        ;;
    reload|restart)
        $0 stop
        $0 start
        ;;
    *)
        echo "Usage: `basename $0` start|stop|restart|reload"
        exit 1
esac

exit 0

Do not forget to make it executable:

root@jackalope:~# chmod a+x /etc/init.d/oracledb
root@jackalope:~#

That script will take care of startup for us, as soon as you run update-rc.d:

root@jackalope:~# update-rc.d oracledb defaults
update-rc.d: warning: /etc/init.d/oracledb missing LSB information
update-rc.d: see http://wiki.debian.org/LSBInitScripts
 Adding system startup for /etc/init.d/oracledb ...
   /etc/rc0.d/K20oracledb -> ../init.d/oracledb
   /etc/rc1.d/K20oracledb -> ../init.d/oracledb
   /etc/rc6.d/K20oracledb -> ../init.d/oracledb
   /etc/rc2.d/S20oracledb -> ../init.d/oracledb
   /etc/rc3.d/S20oracledb -> ../init.d/oracledb
   /etc/rc4.d/S20oracledb -> ../init.d/oracledb
   /etc/rc5.d/S20oracledb -> ../init.d/oracledb
root@jackalope:~#

Also, you might want to add yourself to the dba group and change ownership of the inventory (we chose group oinstall above, remember?):

root@jackalope:~# usermod -G dba -a user
root@jackalope:~# chgrp -R dba /u01/app/oracle/product/11.1.0/jaunty11/inventory
root@jackalope:~#

There’s one last thing to do—reboot and make sure everything starts at boot time. So, on your next boot, you should try:

user@jackalope:~$ export ORACLE_SID=ORA11DEV
user@jackalope:~$ . oraenv
ORACLE_SID = [ORA11DEV] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/jaunty11 is /u01/app/oracle
user@jackalope:~$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 28 17:12:13 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT INSTANCE_NAME, VERSION, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 VERSION	   STATUS
---------------- ----------------- ------------
ORA11DEV	 11.1.0.6.0	   OPEN

SQL>

And we’re done.
Cheers!

Welcome to our meetup #7! This meetup will be focused on Oracle Advanced Queuing (AQ) feature and its usage in Oracle E-Business Suite.

For inexperienced SOM members, we are starting with the meet & greet and pizza+drinks at 5:30 pm and move to smart things at 6:00 pm. We will be there until about 8:30pm (some are sticking around a bit longer while some might take off a bit earlier) and will have a break in the middle. The second half is generally more open-ended as most of you already know.

So what are the goodies at this meetup?

We will start with an overview of the AQ technology in Oracle database - what it is and what it let you do. Then we will see how it’s used in Oracle E-Business Suite and how to manage it.

Our speakers for the evening are your fellow SOM members - Jurijs Velikanovs and Warwick Sands. TBC.

Warwick has been working with AQ quite extensively and doing quite a bit of development with AQ recently and will be glad to share his experience. Jurijs has been working with AQ a lot as part of Apps DBA administering OEBS and he will have a lot to say for both AQ in general and in relation to E-Business Suite. If we come to that, I would be glad to add few points from my own experience and what happened as we tried to scale on using AQ technology with transaction rate of thousands per second.