Book Review – Oracle Information Integration Migration And Consolidation

I recently had the chance to look over the book “Oracle Information Integration Migration and Consolidation” by Jason Williamson, published by PackT Publishing.

The book deals with all aspects of migration to Oracle from various Oracle and non-Oracle sources. In a soup-to-nuts manner, the book covers the major tools to move data into and out of Oracle including OWB, SQLPLUS, EXPDP, IMPDP, RMAN, SQL*DEVELOPER, GoldenGate, and various ETL type tools including some form 3rd party companies.

The book includes many examples and use cases and numerous charts showing what tools should be used for which tasks. The book is written in a down-to-earth style and was easy to read.

I would recommend this book for anyone facing a data migration, platform change or complete system re-write into Oracle from another source.

The book is available here:

http://www.packtpub.com/oracle-information-integration-migration-and-consolidation/book
http://www.amazon.com/Oracle-Information-Integration-Migration-Consolidation/dp/1849682208/ref=sr_1_fkmr0_1?ie=UTF8&qid=1321543957&sr=8-1-fkmr0

Minimizing Rebuild Time with Benchmark Factory from Quest

In my job as Oracle Guru for Teas memory Systems I perform a multitude of bechmarks to showcase the performance of our SSD products. One tool I have found invaluable while doing these benchmark tests is the Benchmark Factory from Quest Software, Inc. as it allows TPC-C, TPC-E and TPC-H as well as custom and other recognized benchmarks to be easily performed and provides decent reporting of the results. However, there is one fly in the ointment.

Benchmark factory does great when the benchmark size is small, however, its time to reload the database grows exponentially as the size of the database increases, for example, the creation of a 3000 warehouse TPC-C takes over 4 hours using a 16-CPU server with plenty of memory and SSD for storage, I can only imagine how long it would take with smaller servers using normal hard drive based SAN or JBOD storage. So, how can this obstacle be overcome?

First, I had Quest provide some parallel load scripts which allow me to load all 9 tables independently. Using the standard load utility it loaded them serially and any errors resulted in having to restart the load process from the beginning. With the independent load scripts you can load any combination of the tables, restarting any that may fail due to space or undo problems as needed. These are the load scripts that reduced the time to 4 hours mentioned above. They are available on the Quest website. However, spending 4 hours to refresh a database to perform a 45 minute test seems rather much, is there anything else that can be done to reduce this time further?

The http://www.tpc.org website has the dbgen utility that can be used to create load tables, then you create a set of SQL Loader files to map them into the database and you can load in parallel, this reduces the load time to about an hour, you can also map the external files as external tables. Of course, rather than deal with learning a second utility (which you have to download and link as a C routine) I just loaded the tables using BMF then before messing with them, created a set of external table using simple CTAS and the external table clauses as required. Now my load time is down to about an hour. I could further reduce the load time by parallelizing specific table loads. Note that to use the ORACLE_DATAPUMP as a source to insert from you must set the FILESYSTEMIO_OPTIONS parameter to NONE. Of course what about statistics? Statistics on multi-million row tables can take a couple of hours to generate as well.

Command to create a set of external table build scripts:
(you must use the “create directory” command to create the ext_tab directory entry first)

select 'create table ext_'||table_name||'
organization external (
type oracle_datapump
default directory ext_tab
location ('||chr(39)||'ext_'||table_name||'.ext'||chr(39)||')) AS
select * from '||table_name||';' from user_tables
/

Example result script:

create table ext_C_HISTORY
organization external (
type oracle_datapump
default directory ext_tab
location ('ext_C_HISTORY.ext')) AS
select * from C_HISTORY;

Following the data load and index build I did a statistics collection using DBMS_STATS.GATHER_SCHEMA_STATS then I stored the statistics into a statistics export table. Since I have the statistics stored it is simply a matter of importing the statistics at the conclusion of the reload.

Commands to export statistics:

EXEC DBMS_STATS.CREATE_STAT_TABLE('TPCC', 'STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('TPCC','STATS_TABLE',NULL,'TPCC');

Command to import statistics:

EXEC DBMS_STATS.import_schema_stats('TPCC','STATS_TABLE',NULL,'TPCC');

So now my data reload simply becomes a set of truncate table commands followed by an insert with the APPEND hint using a SELECT * from the external tables followed by a statistics import. This cuts the turn around time from around 6-8 hours to less than 2 for a 3000 warehouse TPC-C benchmark.

Example INSERT command:

insert /*+append*/ into c_customer select * from ext_c_customer;

Fun with External Tables

During recent testing, I came upon an interesting error. This is on Oracle Linux in an 11.2.0.2 enterprise database. I am doing TPCC testing and wanted a faster way to reload the tables between the tests rather than using the Benchmark Factory loader, which takes forever! I decided to use a datapump format external table using a CTAS to create it for each table after reloading the data then simply do an insert using an APPEND hint to reload the tables following a test and subsequent table recreation (since some are single table clusters and hash clusters truncate doesn’t work so they must be dropped and rebuilt.)

I used the following to create an external table:

create table ext_C_STOCK
organization external (
type oracle_datapump
default directory ext_tab
location ('ext_C_STOCK.ext')) AS
select * from C_STOCK;

The C_STOCK table is part of a single table cluster.

After a test run I dropped and recreated the C_STOCK cluster and table as an empty table then I tried to reload it from the external table:

insert /*+append*/ into C_STOCK select * from ext_C_STOCK;

To which I received:

insert /*+append*/ into C_STOCK select * from ext_C_STOCK
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid dump file "/mnt/exttables/ext_C_STOCK.ext"
ORA-27072: File I/O error
Linux-x86_64 Error: 22: Invalid argument
Additional information: 4
Additional information: 1
Additional information: -1

The problem turned out to be that the initialization parameter filesystemio_options was set to setall, changing it’s value to NONE fixed the issue.

What Are We Waiting For?

It seems everything but the temperatures are in a slump. The economy, hiring, employment, purchasing, proper government action, all seem to be missing. My question is what are we waiting for? We aren’t the ineffective government, we aren’t the ones making poor decisions, at least for the most part.

Perhaps we need to step forward and start movement. We need to do something positive, each of us. Perhaps it would just be cleaning up our yard, giving a bit more at church or working just a bit harder at our jobs. If everyone ignored Washington and just did a little more for each other I think we could positively effect not only our lives, but everyone else’s as well. If all of us treat each other honestly and fairly maybe it will trickle up.

Maybe instead of charging as much as we can, we just need to charge as much as we need to for goods and services. I am tired of seeing ten cents worth of plastic being sold for several dollars. How many employers could let their employees work from home? Do you really need to watch your employees like hawks to make sure they work? Can’t you tell by results if they are working? I submit that if you can’t trust your employees to do their work if you let them work form home, you can’t trust them to do their work at work either and perhaps you should find new employees!

I guess we need to lead from the bottom, if the entire base of the pyramid moves in the correct direction, the rest has to follow.

The Times, they are a Changing

For my presentations at VirtaThon least! Here is the latest schedule of my presentations:

Best of Both Worlds: Using SSd and Disks via ASM
Presenter(s):
Michael Ault
Time slot: 17 July 10:00 - 10:55

Room:
Virtual Room # 100
Infrastructure Technologies - Oracle
Target Audience:
All




VirtaThon GoldStar Session!!! 4-Hour Deep-Dive: Got Performance? Statspack and AWR Analysis
Presenter(s):
Michael Ault
Time slot: 18 July 11:00 - 15:25

Room:
Virtual Room # 100
Database - Oracle
Target Audience:
All




The Need for Speed: Using SSDs with Oracle
Presenter(s):
Michael Ault
Time slot: 19 July 13:30 - 14:25

Room:
Virtual Room # 200
Infrastructure Technologies - Oracle
Target Audience:
All





VirtaThon Keynote: Tuning on the Cheap: Oracle tuning Utilities Everyone Has
Presenter(s):
Michael Ault
Time slot: 20 July 12:30 - 13:25

Room:
Virtual Room # 100
Database - Oracle
Target Audience:
All

Looking Forward

Well, looks like I am booked out for the month of July! Starting Saturday (16 July, 2011) I am doing a 4 hour presentation on AWR analysis for VirtaThon http://www.brainsurface.com/virtathon here is the rest of my VirtaThon schedule (all times EDT):

Sunday 17 July, 2011 10:00-11:00 am "Best of Both Worlds: Using SSd and Disks With ASM"
Tuesday 19 July, 2011 4:30-5:30 pm "The Future is Now: A Fully SSD Database System"
Wednesday 20 July, 2011 12:30-1:30 pm " Tuning on the Cheap: Oracle Tuning Utilities Everyone Has"

On Thursday I present at the Twin Cities Oracle User Group in Minnesota: "Data Warehouse Development Techniques and New PL/SQL Features"

On Tuesday July 26 ,2011 12:00-01:00pm "Oracle Exadata X2-8: A Critical Review" (webcast)Check out http://www.ramsan.com/company/events for more information and a link.

On Wednesday, July 27, 2011 I will be doing a joint presentation with Tariq Farooq on "360 Degrees: Everything to know about Virtualization for Oracle DBAs"

Should be a quick end of the month! Hope you can all make it to at least one of the virtual or live events!

To see any of my past Webinars look at http://www.ramsan.com/company/events and http://www.ramsan.com/resources/videos

A Bitter Nostalgia

I remember in grade school and middle school when I was growing up they would roll TVs into the classroom and we would all watch in wonder as the Mercury and then the Saturn V launch vehicles would, with a rumble and roar lift our astronauts into space. With this technology we put the first men on the moon, sent probes into deep space and around the planets in this solar system. They also lifted up young hearts and minds to believe that maybe they could someday walk on the surface of a different world and stare back at the star that gave them birth with wonder.

Today, the last space shuttle mission launched. After the shuttle returns it will be mothballed and with that action, the USA will no longer has the capability to put people into space. Now, with the demise of the space shuttle we will become a beggar nation when it comes to space, just like the third world countries we will have to go begging with hat (and money) in hand to the Russians and Chinese to use their rockets, vehicles and launch facilities. John F. Kennedy will be spinning in his grave.

We have record unemployment, the lowest new job creation numbers and the dollar is at one of its lowest points in relation to other currencies that it has ever been. I don’t know what the future holds but I am worried. I worry that my grandchildren will be burdened with an immense load of debt that they can’t possibly repay. I worry that they will have no jobs and become dependent on the government for their sustenance, not because of lack of desire or ability, but because of the incompetence of our leaders. For the first time the future looks worse than the past.

Perhaps my memories just aren’t correct. I recall affordable housing, cars, gas and food. I recall a time when one income would feed a family and mothers weren’t expected to work outside of the home. I recall when a majority of time children were respectful to their elders, God was allowed in courts, in schools in lives. Many intellectuals wanted change, wanted God out of society, because after all, God was an outmoded concept that modern, thinking, scientific man didn’t need. How is a Godless world working out for you folks? Personally I find it a disturbing and quite a bit a scary place and getting worse as we get further from the basic rules, handed down by God that this country was based on.

Disgusting, violent video games that desensitize our youth to violence and death are authorized by the highest court in the land. Movies you are afraid to attend because you never know what they will contain fill the movie theaters. The basic rights that the country was founded on are perverted because our highest courts are filled with people with no common sense, no moral courage and nothing but empty academic theories instead of wisdom.

I am sorry if I am a bit morose today. The 10 year old with dreams of being an astronaut just died in my soul.

JavOraThon Conference

Imagine a conference with top Oracle, Java and related topic presenters that you can attend in your PJs, from home, from your office, or even on your commute! A conference where you don't need to put up with TSA touching things that even your spouse won't touch and possibly having your image as the next TSA pinup of the week. Not to mention saving all those travel expense dollars in this tight economy! Well, imagine no more! The JavOraThon conference is a virtual conference with industry recognized speakers and as long as you have internet connectivity you can attend!

Here is the information for being a presenter if you are interested, I know I will be giving several presentations (yet to be announced) and know of several other experts who will be doing the same. The Conference dates are July 16-21 2011, coming to a computer near you!

Call-for-Papers URLS:

Speaker Registration: http://www.brainsurface.com/javorathon/node.add.speakers/
Propose a Session: http://www.brainsurface.com/javorathon/node.add.session/
View your proposed Sessions: http://www.brainsurface.com/javorathon/program.mysessions.proposed/

Hope to (virtually) see all you fellow Oracle users and Gurus there!

Class Action Suite

I think there needs to be a class action suite. It seems there are a bunch of folks who took money from a guaranteed fund and replaced it with completely worthless IOUs. Millions of people put money into the fund, in fact, they were forced to. The class action suite would be on behalf of these millions of folks and levied against at least 643 folks who were trusted with the fund.

In case you haven’t guessed, I am talking about the ponzi scheme known as social security. A ponzi scheme is when you take money from investors and then pay back past investors with money from future investors without investing a dime to make more money. It works as long as your new investors exceed the numbers of your old investors. Supposedly the Social Security system was supposed to be a safe investment into a safety net retirement fund it grew into the mandated ponzi scheme it is today because in the 1980’s the government was allowed to borrow from it and pay back using federal bonds supposedly earning 5%/ However, the only way to pay these bonds was through additional tax revenue. Needless to say, any additional tax revenue was just as quickly spent as the money borrowed from Social Security. Shall we stop using euphemisms? The Congress and Senate, aided by the Presidents and Vice Presidents as well as the Fed stole from Social Security with no intention of ever paying it back.

I think that any currently sitting house of senate member and any past or present Presidents or Vice Presidents, in office or not, who ever voted at anytime to approve this thievery should have their pensions stripped from them and they should be jailed just like Bernie Madoff. To deliberately steal money from your constituents knowing you can never pay it back is criminal.

Unfortunately this elected royalty will never have to be punished for what they have done and continue to do. The trillion plus dollars they have stolen will never be paid back and many of the baby boomers who paid more in Social Security than any of their predecessors will end up in soup lines and shelters sleeping on cots when they reach their 90’s. Sometime around 2037 if things stay as they are the Social Security fund will run out of money and it is the fault of the greedy politicians in Washington who couldn’t keep their hands off of the money contributed to Social Security. By then I will be 80 years old, assuming they haven’t confiscated all the guns by then I hope I can still shoot straight.

Self Sufficiency

As many of you know I am a staunch supporter of the USA 2nd amendment right to bear arms. In support of the 2nd amendment I own and usually carry a firearm and have the needed permit to do so legally for the state of Georgia and the states that practice reciprocity with Georgia about concealed carry permits. Part of the responsibility of being a firearm owner and user is maintaining proficiency with your weapon. Having a weapon and not knowing how to properly use it usually results in you, or someone you love, getting hurt or killed. Part of knowing how to use your weapon is to practice with it to acquire and retain control and accuracy.

Practicing with your weapon involves going to a safe place to shoot, either a secluded, isolated place where you have permission to shoot, or, a public or private shooting range, either indoors or outdoors. Near my home I have an indoor shooting range I go to when I need to practice with my hand guns or 9mm carbine as the indoor range is limited to a 50 yard distance. I have a public, outdoor range I use when I practice with my hunting rifle that is about 40 miles or so away. Of course to practice shooting you must expend ammunition. I usually shoot about 100 rounds of 9mm and 200 or so of .22 caliber when I go to the range. Sometimes maybe a few rounds of .38 special as well. All of these rounds cost money.

For full metal jacket 9mm 115 grain Remington practice rounds the cost runs about $13.00 per 50 rounds if you buy them online and have them sent to you, of course this also involves shipping costs so let’s say the 50 rounds costs you about $24.00. If you buy the rounds in a mortar and brick store they will be over $24.00 in most places. So we are talking about 48 cents a round, minimum. So, anything I can do to bring a cost of less than 48 cents a round will be like putting money in my pocket over the long haul. Thus, I thought about reloading my spent ammo.

Back when I was a poor sailor, I had a 8mm Argentine Mauser I picked up in a pawn shop for $75.00. I gloomed onto this reloading thing with that right away and picked up a really inexpensive Lee-Loader, simply a set of sizing and bullet seating dies, a de-capper and a primer seater. You used a hammer as your press and could load anywhere you wanted to. Unfortunately it wasn’t suited to doing hundreds of rounds at a time.

Flash forward 35 years. I decided to reload my handgun ammo and try to save a little dough as well as be more self sufficient, in the coming times that could be quite useful. Thus began an epic journey to obtain a reloading setup to call my own. After checking reviews, prices and forum entries about various reloading setups I decided I wanted a simple to use press that would allow me to reload with minimal change out of dies and such. I decided on a turret style press that would allow you to put all the needed sizer, decapper, bullet seater and post sizing dies on a turret for a single caliber and all you had to do was pull the lever on the press to rotate the turret as needed. The capability to turn off the “indexing” (movement of the turret) and allow for use as a fixed position press was also a plus. I decided on the Lee Deluxe 4 Place Turret reloading kit which included the turret press, the Lee Autodisk powder measure, a beam type powder scale and various other tools such as a primer pocket cleaner, a casing trimmer and a casing chamfer cutting tool. I added to that dies for 9mm, .38 special and 8mm Mauser, a digital caliper, and the Lee autoprimer. Of course since my loading knowledge was 30+ years out of date, I also picked up a couple of loading manuals like “Modern Reloading – 2nd Edition” by Richard Lee. Of course you also need a bench to hold all this stuff so I picked up a do-it-yourself reloading bench kit where all you add is a 4X8X3/4 sheet of plywood, 7-2X4s and some elbow grease to build a nice bench.

Of course you also need primers, powder, brass and bullets. I picked up some once fired brass for the 9mm (about 2000 casings), 500-124 grain jacketed hollow points, 1000 Winchester small pistol primers and 2 pounds of HP-38 pistol powder. All told the reloading equipment, bench and supplies cost me about $600.00. Add to that a bullet chronograph to measure the results of your reloading for consistency and safety and you get about $700.00. Now, doing the math, if all I ever did was reload the 2000 rounds I purchased the casings for, they would work out to 35 cents a round (even with buying 1500 more bullets and 1000 more primers). So, even with buying the reloading equipment all I need do is reload and shoot 5385 rounds (40 times to the range, about 12 months worth) to break even. Of course, soon my wife and daughter will also be going to the range with me, add in their rounds and I should see payback in 6 months or less.

Of course the other benefit to doing your own loading of ammo is that you can work up an exact load that gives you the best performance from your pistol or rifle. You can also figure out a minimal load to give you a cheap range ammo to practice with. Of course I am a firm believer in practicing with what you will be using, a load that barely punches a hole in paper at 50 yards won’t behave the same, have the same trajectory as or deliver the same kick as a 1200 FPS screamer. If you are going to just detune to reduce cost and recoil, .22 long rifle are cheap and guns that simulate your handgun, or kits that allow your handgun to fire .22 LR are readily available for fairly cheap prices.

After a few false starts and a few mess-ups as I got the various parts adjusted properly and I loaded my first 92 rounds last night. Only 5273 to go!