Developing SQL for automated execution

We have a team of developers who have very good knowledge of our applications. Sometimes the business wants the app changed but cannot make the changes through the application itself. The developers figure out which tables and columns need to be updated, test in non-production and then pass the script to the DBA team for execution. Often these scripts require inspecting the results of queries or other SQL statements and making a decision on whether to proceed with the next step or stop and roll back the changes. Not a big deal except when the business also requires the changes go into effect late at night or on the weekend.

I have been modifying the SQL for those off-hour execution times and then scheduling them to run at the expected time. I decided to train the development team so they could write the SQL themselves. They are new to Oracle but know SQL Server and SQL in general. I showed them how to write SQL that checks the actual results and rolls back or commits depending on expected results.

This works great and saves the DBA team a lot of late night work.

Here’s an example :

drop table SCHEMANAME.TABLENAME_BKP_changenum_B4;
 create table SCHEMANAME.TABLENAME_BKP_changenum_B4 as select * from SCHEMANAME.TABLENAME
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 ) or (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 ) or (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 ) or (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 210 ROWs or more is okay to proceed

declare
 exp_rows number;
 u_rows number;
 rowcount_fail exception;
 BEGIN
 dbms_session.set_identifier('changenumber');
 exp_rows:=41;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#1 - DELETE #1

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 );
 -- >>>> 41 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=27;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #2 - update #1

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 );
 -- >>>> 27 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=129;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #3 - update #2

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 );
 -- >>>> 129 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=13;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#4 - DELETE #2

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 13 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

--rollback;
 commit;

exception
 when rowcount_fail then
 dbms_output.put_line('Rows updated not >= expected row count, rolling back the change');
 rollback;
 when others then
 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 rollback;
 END;
 /

Advertisements

Oracle Application Express 5.1.1 on Exadata cluster

Our DBA team really likes using APEX to develop DBA applications. We are a pretty small team and we like to automate and standardize everything we do. We make a habit of using database tables to store our configuration information and we write scripts that read those DBA tables in order to support our business users.

We recently took control of a new Exadata cluster and of course we created a DBA repository database as we were creating the business databases. Since APEX 5.1.1 was just released, we decided to replace our old APEX system. That system was was a single instance running on a virtual machine and controlled by an outsourced administration team. We are a lot more comfortable having control over installation and maintenance of a system we depend on to get our work done.

The new Exadata is a four node RAC and our new DBA repository has an instance running on each node. We did not want to install software on the Exadata so that ruled out using Rest Data Services (aka APEX Listener). It also ruled out using Oracle HTTP Server with mod_plsql.

Since this APEX system is only used by our DBA team and is not exposed to outside networks we felt comfortable using the Embedded PL/SQL Gateway. However, the Oracle documentation contains this ominous note:”Oracle recommends that you do not select the Embedded PL/SQL Gateway option for Oracle RAC installations. Because the Embedded PL/SQL Gateway uses an HTTP Server built into the database instance, it does not take advantage of the Oracle RAC shared architecture.” (https://docs.oracle.com/database/apex-5.1/HTMIG/choosing-web-listener.htm#HTMIG370)

What to do? We decided to move forward with the Embedded PL/SQL Gateway and see how useable it would be. We could always spin up another virtual machine later if we needed to go the Rest Data Services route. I went through the steps in the APEX documentation to set up the Embedded PL/SQL Gateway. I could not get a response when I hit the database machine APEX URL that I had configured. I poked around on the servers and noticed that the XML db listeners were not running. I had forgotten that when I created the DBA database I intentionally did not install any options like Java and XML db. After installing XML db and restarting the database APEX started working. (https://docs.oracle.com/database/121/ADXDB/appaman.htm#ADXDB2700)

But that’s not all – here’s the real reason I wanted to blog about this. This APEX system is reachable using the scan listener! So that scary quote in the documentation should be ignored. We can connect using a URL like this: http://corpexa-scan.corp.com:8080/apex . No server names are required. So we CAN take advantage of RAC functionality.

This is just what we wanted so we are very satisfied with our new APEX system.

Oracle GoldenGate 12c: Fundamentals for Oracle Ed 1 LVC

I am glad that I had already done the Golden Gate tutorials before I attended this class. The labs were great and fun to do so I think the class itself was OK. However the instructor did not know the material and could not understand the students questions. It was a very large Live Virtual Class across multiple time zones. The instructor was in India so the class ran till 2:30AM for her. I would recommend the class but not the instructor.

 

 

Use cx-Oracle in Python to bulk load arbitrary tables using csv files

I recently got a request to load hundreds of database tables in a day and a half using csv files our developers created. I considered using SQL Loader but I didn’t want to spend the time understanding the table structure and creating the control file for each table. Another alternative was to use SQL Developer to import the csv files. That goes against my nature because it would be a manual, repetitive and error prone process.

The csv files were fairly small so plain inserts would work. I looked at writing a BASH script to generate the insert statements and it became very complex very quickly. PL/SQL was an option too but I ruled it out because of the requirement to read flat files. I have been using the Python cx-Oracle module recently so I decided to write a Python script for this task.

Python turned out to be the right choice and the script was very easy to write. The only tricky part was recognizing the date fields. Those are unquoted like a numeric field but contain literals like JAN,FEB, etc. Python and cx-Oracle handled everything with a minimum of effort on my part.

Create the csv files in a dedicated subdirectory like : tablename.csv. The first row of each csv file must contain the column names. The script truncates the target table before inserting the csv records. Any double quoted field is considered a literal and unquoted fields are numeric except date fields which must have the format 01-JAN-16.

Use these GitHub links for more information : ReadmeSource code

Oracle ZFS Storage Appliance Administration Ed 1 LVC

This was a four day course delivered online. The Oracle labs were inaccessible from my work laptop and from my work network so I used my personal laptop from home. This turned out to be a good thing because work interruptions were minimal and I could focus on the class.

The instructor Barry Isaacman has many years of experience with Sun hardware and has done many ZFS Appliance installations. So he had lots of tips on how to set up things optimally. He also provided documents outside the course materials to support further learning. I was really interested in the API and Python integration and he was very helpful.

The labs are excellent and they utilize real ZFS Appliance hardware so performance is pretty good. There is also a ZFS Appliance simulator here.The hardest part was interpreting the class specific instructions for student IP address assignments. After getting the initial settings correct everything worked fine.

I was able to immediately apply my new knowledge as soon as I returned to work this week. We have recently installed ZFS appliances for database backups over Infiniband in two data centers and the installer set up replication between them. I reviewed the configuration and was pleased that the best practices in the class were implemented in the new installation.

I’m just starting to experiment with the API using Python. Here are some links that are promising:

http://www.oracle.com/technetwork/server-storage/sun-unified-storage/documentation/restfulapi-zfssa-0914-2284451.pdf

https://blogs.oracle.com/jkremer/entry/oracle_zfs_storage_appliance_restful

 

 

 

Oracle In Memory Column Store

I attended the Greater Cincinnati Oracle User’s Group meeting yesterday at the local Oracle office. I was expecting a good presentation because Maria Colgan (@sqlmaria) was presenting.

I was not disappointed and this was an excellent presentation and conversation with Maria who used to head up the optimizer team and now runs the In Memory team.  She went into deep dives of a lot of technical innovations they have used for performance and reliability. I took lots of notes.

All of the code is embedded in Oracle database software (12.1) so it’s just a parameter change to turn it on. The only other step is to alter table INMEMORY. Some of the inmemory stuff can be offloaded to Exadata but you have to pay the license when you turn it on.

In Oracle 12.2 the whole thing can run in exadata flash instead of in the db server dram. Oracle 12.2 will be out “sometime this year”.

Setting up Ansible and cx_Oracle

I have been automating and centralizing dba and system administration processes using Python recently. One of the problems I solved was how to create Bash and SQL scripts to run on the remote database servers, copy them to the database servers and then execute them as another Linux account. After I got my Python scripts working perfectly, I discovered that I had re-invented Ansible 🙂

My scripts would have been much less complex and easier to write if I had done them with Ansible in mind. So that’s how I will write them going forward. I also wanted to implement a DBA Data Warehouse so I added cx_Oracle to the mix and used it to store the admin data in a database. I have been using a local virtual machine for Python development and the database was just a local installation on the VM.

Before moving to production I needed to figure out an easy way to get this stuff installed on the production Linux box. Setting it up on my VM was an adventure in Googling and trial and error. So here’s the documentation on how to set it up. You need to define the shell variables first.

export http_proxy=http://$my_userid:$my_password@$proxy_server:$port
export https_proxy=https://$my_userid:$my_password@$proxy_server:$port
wget https://bootstrap.pypa.io/ez_setup.py -O - | python
easy_install pip
export ORACLE_HOME=/base/oracle/product/12102
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
yum install python-devel
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port cx_Oracle
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port ansible

			

How to tell if your guest VM is vulnerable to Venom

When I first heard about the virtual floppy bug called Venom I wondered if all Xen guests were affected. I quickly discovered that paravirtualized X86 guests are NOT affected. I was pretty sure the Linux guests running on our Exalogic were paravirtualized so I didn’t worry about it. Over the weekend I noticed more publicity about Venom and I decided I should make sure. I don’t have access to Dom0 so I needed to see the virtualization mode from within the guest OS. After a little research I found that if you are using the PVHVM drivers (xen-blkfront for disk, and xen-netfront for network)  you are paravirtualized on Xen. I checked lsmod and verified my initial assumption was correct.

> lsmod | grep -i xen
xen_netfront 16420 0
xen_blkfront 13602 7

References:

http://venom.crowdstrike.com/

http://arstechnica.com/security/2015/05/extremely-serious-virtual-machine-bug-threatens-cloud-providers-everywhere/

http://wiki.xen.org/wiki/Xen_Linux_PV_on_HVM_drivers

https://ervikrant06.wordpress.com/2014/10/17/how-to-determine-the-type-of-vm-from-inside-the-vm-in-ovm-x86/

Data Masking using SQL Loader

I use Enterprise Manager Cloud Control to mask Oracle databases so they can be shared with our ERP vendor. I intended to do the same with our legacy Informix database but I found that we did not have the Database Gateway licenses required for using Enterprise Manager Data Masking Pack on Informix. We had a requirement to replace customer names and addresses with a set of twenty arbitrary values. Due to referential integrity, the replacement values had to be deterministic/repeatable – not random.

The Informix DBA sent me flat file exports from the Informix tables containing the customer records. I mounted these as external tables and wrote SQL to select from them and write to Oracle tables. This was so slow that I abandoned that idea and decided to load the files into Oracle tables using SQL Loader. It’s been so long since I have used SQL Loader that I had to relearn how to build control files. I was amazed at how fast the data loaded. I had the idea of using SQL Loader to mask the data during the load.

I decided to use the oracle_hash function inside a case statement in the SQL Loader control file specification for the sensitive columns. This let me replace the names and addresses with new values in a repeatable way. I forced the names to uppercase and then produced a hash value which I associated with the fictional name. That fictional name got loaded into the Oracle table.

I am very satisfied with the results and happy I had the opportunity to re-familiarize myself with SQL Loader.

Here’s the content of the control file:

options
(
direct=true, rows=200000, multithreading=true
)
unrecoverable
load data
infile customers.unl badfile customers.bad discardfile customers.dis
into table customers
fields terminated by ‘|’
(
cust_num,
first_name “case
when (ora_hash(upper(:first_name),20,0) = 0) then ‘Andy’
when (ora_hash(upper(:first_name),20,0) = 1) then ‘Brenda’
when (ora_hash(upper(:first_name),20,0) = 2) then ‘Jim’
when (ora_hash(upper(:first_name),20,0) = 3) then ‘Cathy’
when (ora_hash(upper(:first_name),20,0) = 4) then ‘Bob’
when (ora_hash(upper(:first_name),20,0) = 5) then ‘Sue’
when (ora_hash(upper(:first_name),20,0) = 6) then ‘Joe’
when (ora_hash(upper(:first_name),20,0) = 7) then ‘Carmen’
when (ora_hash(upper(:first_name),20,0) = 8) then ‘Jose’
when (ora_hash(upper(:first_name),20,0) = 9) then ‘Claire’
when (ora_hash(upper(:first_name),20,0) = 10) then ‘Ron’
when (ora_hash(upper(:first_name),20,0) = 11) then ‘Misty’
when (ora_hash(upper(:first_name),20,0) = 12) then ‘Jason’
when (ora_hash(upper(:first_name),20,0) = 13) then ‘Glenda’
when (ora_hash(upper(:first_name),20,0) = 14) then ‘Rick’
when (ora_hash(upper(:first_name),20,0) = 15) then ‘Suzy’
when (ora_hash(upper(:first_name),20,0) = 16) then ‘Peter’
when (ora_hash(upper(:first_name),20,0) = 17) then ‘Sara’
when (ora_hash(upper(:first_name),20,0) = 18) then ‘Isaac’
when (ora_hash(upper(:first_name),20,0) = 19) then ‘Helen’
when (ora_hash(upper(:first_name),20,0) = 20) then ‘Nathan’
else ‘ERROR’ end”,
last_name “case
when (ora_hash(upper(:last_name),20,0) = 0) then ‘Jones’
when (ora_hash(upper(:last_name),20,0) = 1) then ‘Hay’
when (ora_hash(upper(:last_name),20,0) = 2) then ‘Smith’
when (ora_hash(upper(:last_name),20,0) = 3) then ‘Gonzales’
when (ora_hash(upper(:last_name),20,0) = 4) then ‘Wu’
when (ora_hash(upper(:last_name),20,0) = 5) then ‘Cho’
when (ora_hash(upper(:last_name),20,0) = 6) then ‘Einstein’
when (ora_hash(upper(:last_name),20,0) = 7) then ‘Ward’
when (ora_hash(upper(:last_name),20,0) = 8) then ‘Magee’
when (ora_hash(upper(:last_name),20,0) = 9) then ‘Morgan’
when (ora_hash(upper(:last_name),20,0) = 10) then ‘Tenkiller’
when (ora_hash(upper(:last_name),20,0) = 11) then ‘Rice’
when (ora_hash(upper(:last_name),20,0) = 12) then ‘Namath’
when (ora_hash(upper(:last_name),20,0) = 13) then ‘DeMarco’
when (ora_hash(upper(:last_name),20,0) = 14) then ‘Obama’
when (ora_hash(upper(:last_name),20,0) = 15) then ‘Bush’
when (ora_hash(upper(:last_name),20,0) = 16) then ‘Grant’
when (ora_hash(upper(:last_name),20,0) = 17) then ‘Lee’
when (ora_hash(upper(:last_name),20,0) = 18) then ‘Blount’
when (ora_hash(upper(:last_name),20,0) = 19) then ‘Bain’
when (ora_hash(upper(:last_name),20,0) = 20) then ‘Adams’
else ‘ERROR’ end”,
address1 “case
when (ora_hash(upper(:address1),20,0) = 0) then ’10 Main’
when (ora_hash(upper(:address1),20,0) = 1) then ’11 1st’
when (ora_hash(upper(:address1),20,0) = 2) then ’22 2nd’
when (ora_hash(upper(:address1),20,0) = 3) then ’33 3rd’
when (ora_hash(upper(:address1),20,0) = 4) then ’44 4th’
when (ora_hash(upper(:address1),20,0) = 5) then ’55 5th’
when (ora_hash(upper(:address1),20,0) = 6) then ’66 6th’
when (ora_hash(upper(:address1),20,0) = 7) then ’77 7th’
when (ora_hash(upper(:address1),20,0) = 8) then ’88 8th’
when (ora_hash(upper(:address1),20,0) = 9) then ’99 9th’
when (ora_hash(upper(:address1),20,0) = 10) then ‘100 10th’
when (ora_hash(upper(:address1),20,0) = 11) then ‘111 11th’
when (ora_hash(upper(:address1),20,0) = 12) then ‘222 22nd’
when (ora_hash(upper(:address1),20,0) = 13) then ‘333 33rd’
when (ora_hash(upper(:address1),20,0) = 14) then ‘444 44th’
when (ora_hash(upper(:address1),20,0) = 15) then ‘555 55th’
when (ora_hash(upper(:address1),20,0) = 16) then ‘666 66th’
when (ora_hash(upper(:address1),20,0) = 17) then ‘777 77th’
when (ora_hash(upper(:address1),20,0) = 18) then ‘888 88th’
when (ora_hash(upper(:address1),20,0) = 19) then ‘999 99th’
when (ora_hash(upper(:address1),20,0) = 20) then ‘2000 20th’
else ‘ERROR’ end”,
city,
state,
zip,
country_code,
phone “upper(‘8005551212’)”,
email “upper(‘me\@mydomain.com’)” ,
create_date date ‘mm/dd/yyyy’,
)

Have you been asked to bring food for an event? I have some suggestions!

I am occasionally invited to bring food for office, church or other social gatherings. I used to bring a bag of chips or a dozen donuts. That’s minimal effort, but nowadays I don’t eat that stuff and I don’t want to encourage others to eat it. So I have come up with a few options I feel good about sharing with others.

Brunch Recipes

I like casseroles for brunch. I make two types of breakfast casseroles, zero carb and no-meat. This covers most people’s dietary requirements. Both of them are gluten free. The zero carb casserole is a good choice for people with egg allergies because it does not contain the egg whites which are the major allergy irritant in eggs.

Zero Carb Casserole

Lots of people on a zero carb diet complain they can never find anything to eat at these events. I’m one of them. So I always bring something I want to eat. For brunch, this is it:

Ingredients

  • 2 lb Hot Spicy Bob Evans Sausage
  • 1 lb Aged White Cheddar Cheese ***
  • 24 Large Egg Yolks
  • 1/2 Pint Heavy Cream

Preparation:

  • Preheat the oven to 400 degrees.
  • Cook the 2 lb of sausage over medium heat until you see no pink meat. The meat should be broken up into very small chunks. Do not overcook.
  • Scoop the meat into a casserole dish. I use a standard glass casserole dish, approximately 14″x10″.
  • Shred the 1 lb of cheddar using a grater with large holes producing large long shreds of cheddar.
  • Spread the shredded cheddar evenly over the sausage meat.
  • Separate the whites from the yolks of 24 large eggs, discarding the whites and saving the yolks in a mixing bowl.
  • Add 1/2 pint Heavy Cream to the mixing bowl with the egg yolks (This adds back some liquid to replace the egg whites. You can experiment with more cream but don’t go lower than 1/2 pint because the yolks will get too stiff)
  • Stir the egg yolks and cream until all of the yolks are broken and the cream is evenly mixed.
  • Pour the egg yolk mixture evenly over the cheese, covering every square inch of cheese.
  • Put the dish into the preheated oven and bake for 35 minutes
  • Check the casserole, it should be lightly browned on top and a knife inserted into the middle should come out clean. Otherwise cook 5 more minutes.

*** you can use any extra sharp cheddar, but the orange ones leave a lot of oil on the bottom of the casserole. I don’t mind it but some people do.

No-Meat Casserole

A lot of vegetarians DO eat eggs so this casserole will work for them. Some of the low carb folks will enjoy a bite of this too although it is not zero carb. This casserole has a “tighter” texture than the zero carb casserole due to the lack of meat and the addition of ricotta cheese.

Ingredients:

  • 9-12 Whole Large Eggs (don’t use less than 9 eggs)
  • 1 lb Aged White Cheddar Cheese ***
  • 2 huge overflowing tablespoons of Ricotta cheese (use the regular, not the low fat Ricotta)
  • 1/2 loaf of gluten free bread
  • dill weed
  • onion powder
  • hot sauce

Preparation:

  • Preheat the oven to 400 degrees.
  • Cut the 1/2 loaf of gluten free bread into 1/2″ cubes.
  • Spread the cubed bread into a casserole dish. I use a standard glass casserole dish, approximately 14″x10″.
  • Shred the 1 lb of cheddar using a grater with large holes producing large long shreds of cheddar.
  • Spread the shredded cheddar evenly over the bread.
  • Break the large eggs into a mixing bowl, (yolks and whites together)
  • Add dill, onion powder and hot sauce to taste (heavier on the onion powder)
  • Add the Ricotta cheese
  • Stir until the Ricotta is well mixed with the eggs.
  • Pour the egg mixture evenly over the cheese, covering every square inch of cheese.
  • Put the dish into the preheated oven and bake for 30 minutes
  • Check the casserole, it should be lightly browned on top and a knife inserted into the middle should come out clean. Otherwise cook 5 more minutes.

*** you can use any extra sharp cheddar, but the orange ones leave a lot of oil on the bottom of the casserole. I don’t mind it but some people do.

Lunch/Dinner Recipes

Nothing beats beef brisket barbecue for lunch and dinner. I recommend calling while you are driving to the event and ordering 3-5 pounds at City Barbecue. Use this link to find the nearest City BBQ location. Trust me, YOU CANNOT make it better or cheaper and everyone will love it! 🙂

brisket

WebLogic Server 11g: WLS Administration Essentials

I used Weblogic version 6 and 7 in the early 2000’s for a SAP web front end before Oracle bought Weblogic. I had never attended an administrator course though, so there were a lot of gaps in my knowledge. We are using Weblogic here for Oracle Fusion Middleware SOA, Enterprise Manager Cloud Control and APEX (Oracle Application Express).

I had a limited time window for training and the only class available was in Belmont, CA on the Oracle campus. This worked out pretty well and I had the opportunity to explore the campus and the surrounding area. Our instructor Takyiu Lui spent a lot of time making sure we really understood the material and supplemented the material with diagrams on the whiteboard as well as pdfs of all of the whiteboard diagrams for each day of the 5 day class. Here are the course topics. Here’s an architecture diagram.

I came away from the training with a new appreciation of what is possible with Weblogic. I think most people are not utilizing much of it’s functionality. I also got some good information on sizing garbage collection and tuning in general. So I can recommend this as a valuable class for anyone managing or configuring Weblogic Server.

Oracle VM Server for x86: OVM Administration Ed 1

In order to provide good coverage at work I needed to take this class between the time we hired a new DBA and the time our contract DBA rolled off our project. That narrowed the choice down to classes scheduled for September. The only class available was in San Jose CA. I had never visited any of the towns south of San Francisco so this was a good chance to experience the Silicon Valley culture. My hotel was walking distance from the classroom and there are several great places to eat along the way.

At work we are using Exalogic to host virtual machines on OVM. I had already dabbled in OVM and had set up some RAC databases on OVM running under Virtualbox on my Toshiba Qosmio before class (I bought the Qosmio with 32G specifically to run database VMs). I think this was good preparation for the class since there was a similar setup in our labs. We used remote OVM servers that hosted our lab OVM manager and our lab OVM servers. So we were running two levels of virtualization. We installed  OVM manager and OVM server in the first few labs (one OVM server was pre-built). There were other labs for configuring storage, networks, guest OS creation, templates, etc. I enjoyed the labs a lot.

The last virtualization class I had attended was for VMware back in 2008. I have used VMware ESX and Workstation over the past several years. I was pleasantly surprised with the performance of our lab systems.

We had an excellent instructor, Hans Forbrich (aka Fuzzy Graybeard). I knew of Hans because he’s an Oracle Ace director and Daniel Morgan, another Ace director had presented to our local Oracle User’s Group in July. One of Dan’s slides is about Hans. The name was also familiar because Hans has contributed to the Oracle-l mail group for several years and before that was a frequent contributor to the Usenet group comp.databases.oracle.  If you attend  “RAC Attack” at Oracle Open World you will probably see him. He is very knowledgeable about Oracle Virtual Machines and was able to answer most questions immediately. If he couldn’t answer, he would research during our lab time and was always able to provide a satisfactory answer.

I would recommend this class to anyone who wants to get started administering an Oracle Virtual Machine environment.

Class Link

Automating Redo Log Maintenance

We asked a consultant to review our production database and one of the recommendations was to switch our redo logs from 2GB to 4GB. This surprised me at first but after checking the 11.2 documentation, I saw that 4GB is the current recommendation.  This is a 4 node RAC cluster and we had previously added several additional redo logs based on the application vendor’s  recommendations :-).  I decided I would take the opportunity to try out the PL/SQL debugger in SQL Developer 4.0.1.  Doing the development entirely in SQL Developer instead of vi turned out to be a good decision and saved me a lot of time.  In addition to changing the redo sizes, I wanted to move them from our original Exadata X2 ASM disk groups to our new Exadata X3 disk groups.

Manual process:

  • Create a new group like the original group
  • If the old group is inactive and has been archived, drop the old group
  • If the old group is active, current or has not been archived, force a log switch on all RAC nodes, wait until it’s inactive, then drop the old group
  • Repeat until all of the old log groups are gone (I’m using Oracle Managed Files so I don’t need to log on to asmcmd and manually delete the files)

I automated these manual steps in the sys.REDO_MAINT package:

CREATE OR REPLACE PACKAGE REDO_MAINT AS
type log_rec_type is record (log_group number,log_thread number,is_archived varchar2(3),log_status varchar2(10) ,log_size number);
cursor log_records return log_rec_type;
procedure manage_logs (
  new_size      varchar2,
  new_location  varchar2,
  new_location2 varchar2);
function create_new_group (
  newgroup    number,
  newthread   number,
  newfilespec varchar2,
  newfilespec2 varchar2,
  newfilesize varchar2)
    return boolean;
function drop_old_group (
  oldgroup  number)
    return boolean;
function redo_in_use (
  groupnum  number)
    return boolean;
function redo_not_archived (
  groupnum number)
    return boolean;
END REDO_MAINT;
/

CREATE OR REPLACE PACKAGE BODY REDO_MAINT AS

  cursor log_records return log_rec_type IS
    SELECT group# ,thread#,archived,status,BYTES from v$log;

  procedure manage_logs (
  new_size      varchar2,
  new_location  varchar2,
  new_location2 varchar2) AS
  BEGIN
    for existing_logs in log_records
    loop
      dbms_output.put_line('Group '||existing_logs.log_group 
                        || ' Thread ' || existing_logs.log_thread
                        || ' Archived ' || existing_logs.is_archived
                        || ' Status ' || existing_logs.log_status
                        || ' Size ' || existing_logs.log_size);

      if create_new_group(existing_logs.log_group + 100, existing_logs.log_thread,new_location,new_location2,new_size) 
      then
        dbms_output.put_line('create new group success');
        loop
          if drop_old_group(existing_logs.log_group)
          then
            dbms_output.put_line('Dropped old group ' || existing_logs.log_group);
            exit;
          end if;
          dbms_output.put_line('old group was not dropped - retrying ' || existing_logs.log_group);
        end loop;
      else
        dbms_output.put_line('create new group FAILED');
        exit;
      end if;
    end loop;
    NULL;
  END manage_logs;

  function create_new_group (
  newgroup    number,
  newthread   number,
  newfilespec varchar2,
  newfilespec2 varchar2,
  newfilesize varchar2)
    return boolean AS
  create_group varchar(200);
  BEGIN
    create_group:='ALTER DATABASE ADD LOGFILE THREAD ' || newthread 
    || ' GROUP ' || newgroup 
    || ' ( ' || '''' || newfilespec || '''' || ' , ' || '''' || newfilespec2 || '''' 
    || ' ) size ' || newfilesize ;
    dbms_output.put_line(create_group);
    execute immediate create_group;
    RETURN TRUE;
    exception
    when others then 
     dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
     return FALSE;
  END create_new_group;

  function drop_old_group (
  oldgroup  number)
    return boolean AS
  drop_group varchar(200);
  BEGIN
    drop_group:='alter database drop logfile group ' || oldgroup ;
     if redo_in_use(oldgroup) or redo_not_archived(oldgroup)
     then
      execute immediate 'ALTER SYSTEM ARCHIVE LOG CURRENT' ;
      -- wait until target redo log becomes inactive
      loop
        if redo_in_use(oldgroup) or redo_not_archived(oldgroup)
        then
          dbms_output.put_line('Sleeping - group ' || oldgroup);
          DBMS_LOCK.SLEEP(60);
        else
          -- was active - now inactive 
          dbms_output.put_line(drop_group);
          execute immediate drop_group;
          RETURN TRUE;
          exit;
        end if;
      end loop;
      return FALSE;
    end if;
    -- was not active - no wait required
    dbms_output.put_line(drop_group);
    execute immediate drop_group;
    RETURN TRUE;
        exception
    when others then 
     dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
     return FALSE;
  END drop_old_group;

  function redo_in_use (
  groupnum  number)
    return boolean AS
  current_status varchar2(10);  
  BEGIN
  select status into current_status from v$log where group# = groupnum;
  if current_status in ( 'ACTIVE','CURRENT') 
  then
    RETURN TRUE;
  else
    return FALSE;
  END IF;
        exception
    when others then 
     dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
     return FALSE;
  END redo_in_use;

  function redo_not_archived (
  groupnum number)
    return boolean AS
  is_it_archived varchar(3);
  BEGIN
    select archived into is_it_archived from v$log where group# = groupnum;
    if is_it_archived = 'NO'
    then
    -- redo not archived
       RETURN TRUE;
    else
      return FALSE;
    END IF;
      exception
    when others then 
     dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
     return FALSE;
  END redo_not_archived;

END REDO_MAINT;
/

Example:
SYS>select group#,thread#,bytes,archived,status from v$log order by  group#, thread#;

    GROUP#    THREAD#            BYTES ARC STATUS
---------- ---------- ---------------- --- ----------------
         1          1    2,147,483,648 YES INACTIVE
         2          1    2,147,483,648 YES INACTIVE
         3          2    2,147,483,648 YES INACTIVE
         4          2    2,147,483,648 YES INACTIVE
         5          1    2,147,483,648 YES INACTIVE
         6          2    2,147,483,648 YES INACTIVE
         7          3    2,147,483,648 YES INACTIVE
         8          3    2,147,483,648 YES ACTIVE
         9          3    2,147,483,648 NO  CURRENT
        10          4    2,147,483,648 YES INACTIVE
        11          4    2,147,483,648 YES INACTIVE
        12          4    2,147,483,648 YES INACTIVE
        13          1    2,147,483,648 YES INACTIVE
        14          1    2,147,483,648 YES INACTIVE
        15          1    2,147,483,648 YES INACTIVE
        16          1    2,147,483,648 NO  CURRENT
        17          1    2,147,483,648 YES INACTIVE
        20          2    2,147,483,648 YES ACTIVE
        21          2    2,147,483,648 NO  CURRENT
        22          2    2,147,483,648 YES INACTIVE
        23          2    2,147,483,648 YES INACTIVE
        24          2    2,147,483,648 YES INACTIVE
        30          3    2,147,483,648 YES INACTIVE
        31          3    2,147,483,648 YES INACTIVE
        32          3    2,147,483,648 YES INACTIVE
        33          3    2,147,483,648 YES INACTIVE
        34          3    2,147,483,648 YES INACTIVE
        40          4    2,147,483,648 YES INACTIVE
        41          4    2,147,483,648 YES INACTIVE
        42          4    2,147,483,648 YES INACTIVE
        43          4    2,147,483,648 NO  CURRENT
        44          4    2,147,483,648 YES INACTIVE

32 rows selected.
Kick it off:
BEGIN
  REDO_MAINT.MANAGE_LOGS( NEW_SIZE => '4g', NEW_LOCATION => '+RECO_EDP2', NEW_LOCATION2 => '+DATA_EDP2');
END;
/

on another session:
SYS>select group#,thread#,bytes,archived,status from v$log order by  group#, thread#;

    GROUP#    THREAD#            BYTES ARC STATUS
---------- ---------- ---------------- --- ----------------
         3          2    2,147,483,648 YES INACTIVE
         4          2    2,147,483,648 YES INACTIVE
         5          1    2,147,483,648 YES INACTIVE
         6          2    2,147,483,648 YES INACTIVE
         7          3    2,147,483,648 YES INACTIVE
         8          3    2,147,483,648 YES INACTIVE
         9          3    2,147,483,648 YES INACTIVE
        10          4    2,147,483,648 YES INACTIVE
        11          4    2,147,483,648 NO  CURRENT
        12          4    2,147,483,648 YES INACTIVE
        13          1    2,147,483,648 YES INACTIVE
        14          1    2,147,483,648 YES INACTIVE
        15          1    2,147,483,648 YES INACTIVE
        16          1    2,147,483,648 YES INACTIVE
        17          1    2,147,483,648 YES INACTIVE
        20          2    2,147,483,648 YES INACTIVE
        21          2    2,147,483,648 YES INACTIVE
        22          2    2,147,483,648 YES INACTIVE
        23          2    2,147,483,648 YES INACTIVE
        24          2    2,147,483,648 NO  CURRENT
        30          3    2,147,483,648 YES INACTIVE
        31          3    2,147,483,648 YES INACTIVE
        32          3    2,147,483,648 NO  CURRENT
        33          3    2,147,483,648 YES INACTIVE
        34          3    2,147,483,648 YES INACTIVE
        40          4    2,147,483,648 YES INACTIVE
        41          4    2,147,483,648 YES INACTIVE
        42          4    2,147,483,648 YES INACTIVE
        43          4    2,147,483,648 YES INACTIVE
        44          4    2,147,483,648 YES INACTIVE
       101          1    4,294,967,296 NO  CURRENT
       102          1    4,294,967,296 YES UNUSED

32 rows selected.

Output from PL/SQL:

Group 1 Thread 1 Archived NO Status CURRENT Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 101 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
Sleeping - group 1
Sleeping - group 1
Sleeping - group 1
Sleeping - group 1
Sleeping - group 1
alter database drop logfile group 1
Dropped old group 1
Group 2 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 102 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 2
Dropped old group 2
Group 3 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 103 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 3
Dropped old group 3
Group 4 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 104 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 4
Dropped old group 4
Group 5 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 105 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 5
Dropped old group 5
Group 6 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 106 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 6
Dropped old group 6
Group 7 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 107 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 7
Dropped old group 7
Group 8 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 108 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 8
Dropped old group 8
Group 9 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 109 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 9
Dropped old group 9
Group 10 Thread 4 Archived NO Status CURRENT Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 110 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 10
Dropped old group 10
Group 11 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 111 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
Sleeping - group 11
Sleeping - group 11
Sleeping - group 11
Sleeping - group 11
alter database drop logfile group 11
Dropped old group 11
Group 12 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 112 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 12
Dropped old group 12
Group 13 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 113 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 13
Dropped old group 13
Group 14 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 114 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 14
Dropped old group 14
Group 15 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 115 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 15
Dropped old group 15
Group 16 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 116 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 16
Dropped old group 16
Group 17 Thread 1 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 117 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 17
Dropped old group 17
Group 20 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 120 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 20
Dropped old group 20
Group 21 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 121 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 21
Dropped old group 21
Group 22 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 122 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 22
Dropped old group 22
Group 23 Thread 2 Archived NO Status CURRENT Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 123 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 23
Dropped old group 23
Group 24 Thread 2 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 124 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 24
Dropped old group 24
Group 30 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 130 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 30
Dropped old group 30
Group 31 Thread 3 Archived NO Status CURRENT Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 131 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 31
Dropped old group 31
Group 32 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 132 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 32
Dropped old group 32
Group 33 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 133 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 33
Dropped old group 33
Group 34 Thread 3 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 134 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 34
Dropped old group 34
Group 40 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 140 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 40
Dropped old group 40
Group 41 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 141 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 41
Dropped old group 41
Group 42 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 142 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 42
Dropped old group 42
Group 43 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 143 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 43
Dropped old group 43
Group 44 Thread 4 Archived YES Status INACTIVE Size 2147483648
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 144 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g
create new group success
alter database drop logfile group 44
Dropped old group 44

PL/SQL procedure successfully completed.

Elapsed: 00:15:12.47

SYS>select group#,thread#,bytes,archived,status from v$log order by  group#, thread#;

    GROUP#    THREAD#            BYTES ARC STATUS
---------- ---------- ---------------- --- ----------------
       101          1    4,294,967,296 YES INACTIVE
       102          1    4,294,967,296 NO  CURRENT
       103          2    4,294,967,296 NO  CURRENT
       104          2    4,294,967,296 YES UNUSED
       105          1    4,294,967,296 YES UNUSED
       106          2    4,294,967,296 YES UNUSED
       107          3    4,294,967,296 NO  CURRENT
       108          3    4,294,967,296 YES UNUSED
       109          3    4,294,967,296 YES UNUSED
       110          4    4,294,967,296 NO  CURRENT
       111          4    4,294,967,296 YES UNUSED
       112          4    4,294,967,296 YES UNUSED
       113          1    4,294,967,296 YES UNUSED
       114          1    4,294,967,296 YES UNUSED
       115          1    4,294,967,296 YES UNUSED
       116          1    4,294,967,296 YES UNUSED
       117          1    4,294,967,296 YES UNUSED
       120          2    4,294,967,296 YES UNUSED
       121          2    4,294,967,296 YES UNUSED
       122          2    4,294,967,296 YES UNUSED
       123          2    4,294,967,296 YES UNUSED
       124          2    4,294,967,296 YES UNUSED
       130          3    4,294,967,296 YES UNUSED
       131          3    4,294,967,296 YES UNUSED
       132          3    4,294,967,296 YES UNUSED
       133          3    4,294,967,296 YES UNUSED
       134          3    4,294,967,296 YES UNUSED
       140          4    4,294,967,296 YES UNUSED
       141          4    4,294,967,296 YES UNUSED
       142          4    4,294,967,296 YES UNUSED
       143          4    4,294,967,296 YES UNUSED
       144          4    4,294,967,296 YES UNUSED

32 rows selected.

Getting ORA-15204 on full database restore

Last week I was testing our backups and got this error: ORA-15204: database version 11.2.0.0.0 is incompatible with diskgroup DATA_EDT1. This surprised me because all of our databases and Oracle homes on this Exadata are at version 11.2.0.3. I was testing full restore/recovery without a control file or an spfile. The first step was to restore spfile and controlfile from autobackup to their ASM locations.
I queried V$ASM_DISKGROUP in ASM and saw that all of our diskgroups have compatibility set to 11.2.0.2.0. In order to start RMAN restore I had created a minimal pfile. I didn’t bother setting the compatible parameter. I edited the pfile and set compatible=”11.2.0.3.0″ – that fixed it and the restore/recovery worked fine.

 

 

.rman

High load average, high IO wait, slow process startup, slow RAC communications

We have been having performance problems on our test Exadata for several months. I have opened five Oracle service requests for multiple symptoms. While the cpu utilization was fairly low, Oracle background processes would hang, the OEM 12c agent would hang, backup jobs would hang, we would experience slow communications between RAC nodes, half of the cpus would be in 100% IO wait state, the system load average would exceed 6000, etc. We noticed one of the NFS mounts was unreachable and it happened to be the mount point where we keep our DBA scripts. Processes would be in IO wait state “D” and we noticed several were the DBA scripts running from NFS. We could resolve the problem by killing these scripts. So I moved the backup scripts to local drives and eliminated some issues.
The problem kept returning though so I kept opening more SRs with no solution. Yesterday I escalated and had one of my SRs reopened. I was finally able to get to the correct person in Oracle support who gave me two things to try. The first was to add the “noac” option for the NFS mounts. The idea was that this would resolve issues where synchronous writes are induced. Since we are backing up to NFS using RMAN and tar this seemed a good bet. And it did help a lot. But we were still able to bring the problem back by tarring to NFS.
These NFS mounts are across Infiniband to an Exalogic ZFS storage system. The second fix was based on the fact that the new OEL kernel 11.2.3.2.1 update has memory management changes that may result in high TCP/IP traffic causing memory starvation for contiguous memory free space. See Knowledge base article 1546861.1 System hung with large numbers of page allocation failures with “order:5” : <Future Exadata releases will be changing the MTU size on the InfiniBand Interfaces to 7000 (down from 65520) for new installations, so the 7000 MTU for Exadata environments is known to be appropriate>  So I changed the Infiniband MTU from 65520 to 7000 and restarted the network service. That finally fixed the issue.

Oracle Data Integrator 11g: Integration and Administration Ed 2

I was in St. Charles, Mo all last week attending this class. It was taught at Quilogy Services and the instructor Doug Wallin was excellent. We only had two other students in the class and both of them were Oracle employees. I had no ODI experience so it was a great learning experience for me. I finished all the hands on labs and I have referred back to some of them since returning to work this week. It is a fast paced course with a lot of information. I think retention would be low if you couldn’t immediately start applying the learning on the job. My goal is to start using as much of the knowledge possible. Since we had such a small class we were able to get through all the material on time and also have some interesting discussions about “best practices”. I stayed at a hotel less than a mile from the class location so I was able to ride a bike to class every day. The Katy Trail runs alongside the Missouri River and a lot of non-chain restaurants are near the class location. So I biked about 12-14 miles every day and ate well 🙂 It was a great week and I returned to work with a lot of energy and enthusiasm.
Class description.

RAC and Grid Infrastructure Administration Accelerated Ed 3

This is a combination of two courses, “Oracle Grid Infrastructure 11g: Manage Clusterware” and “ASM  and Oracle Database 11g: RAC Administration”. The two courses are normally taught in five days and three days respectively.  This combination was taught in five days by extending the classroom time (8:30AM – 6:30PM). Knowledge  Services instructor Pat Frey taught the class in Columbus/Dublin OH. There is some overlap in the two courses so it’s not as bad as it sounds 🙂

There are lots of good labs in these courses and we did all of them except the overlapping parts. I was surprised that the virtual machines for our three node clusters worked so well. There were some performance issues and one student’s cluster became unresponsive on the last day.  Mine stayed up and I always finished the labs in about half the time other students took.

I couldn’t understand why it took the other students so long to work through the labs until I realized how much more experience I have with Oracle. For some of them it was their first exposure to Clusterware and RAC. I got the training materials the week before and one of the exercises actually solved an issue I was having on our Exadata RAC systems. Also, I have been using RAC since it first came out on 9i. Here’s a review of the RAC class I took back in 2003. So I think my experience and knowledge of Oracle in general made a huge difference in my ability to perform the lab work efficiently and quickly. I also asked a lot more questions for clarification than anyone else because I was able to see how the different features were relevant to production uptime and performance.

This was a very good course for me and I recommend it to anyone who already has a basic grounding in single-instance Oracle.

Exadata Database Machine Administration Workshop

I spent last week in downtown Chicago attending this class. It was a very good class and the instructor, Andy Fortunak was excellent. We had lots of labs to work through. The computers we used ran virtual database servers and virtual storage servers. This setup caused some problems, mainly slowness. My virtual database machine’s clusterware failed towards the end of the week but the virtual storage servers continued to work fine.

The main message was: invest the time needed to set up services and Database Resource Manager.  IO Resource Manager and Instance Caging depends on it.

We also discussed consolidation and using RAC one-node. We are consolidating scores of databases on Exadata. Most of them do not require multiple RAC nodes for workload but we do want high availability and load balancing. This week I tested manually moving one-node databases between nodes and it works great. The client does not get disconnected but does get an error message. Srvctl actually makes it a two node RAC and then stops the original node. I tested automatic failover of rac one-node and compared it with a full rac two-node with one instance down. They both work the way I wanted, a second instance starts up. I can’t see any technical reason for going with one-node.

Automating and delegating datapump imports with PL/SQL

We recently bought another company and we are in the process of integrating our systems with theirs. One requirement was to import their daily business data into our Business Intelligence system for reporting. We agreed they would provide an Oracle datapump export file for us to import. We wanted to automate this and have a non-privileged application owner perform the import. I created a stored procedure that could run the import as the system user and granted execute on the procedure to the application owner. The dbms_datapump package has lots of options and it was fun exploring them to find the best way of doing this.

Setup –

Run as sys :

 grant execute on dbms_lock to system;

Run as system:

CREATE OR REPLACE DIRECTORY SOURCE AS '/nfsmount/exp/incoming';
CREATE OR REPLACE PROCEDURE SYSTEM.SCHEMA_IMPORT (
 dumpfilename IN VARCHAR2)
IS
 h1 NUMBER; -- data pump job handle
 job_state VARCHAR2 (30);
 status ku$_Status; -- data pump status
 job_not_exist EXCEPTION;
 PRAGMA EXCEPTION_INIT (job_not_exist, -31626);
BEGIN
 h1 :=
 DBMS_DATAPUMP.open (operation => 'IMPORT',
   job_mode => 'SCHEMA',
   job_name => NULL);
 DBMS_DATAPUMP.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'TRUNCATE');
 DBMS_DATAPUMP.add_file (h1, dumpfilename, 'SOURCE');
 DBMS_DATAPUMP.add_file (h1,
   dumpfilename || '.log',
   'SOURCE',
   NULL,
   DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 DBMS_DATAPUMP.metadata_remap (h1,
   'REMAP_SCHEMA',
   'FROMSCHEMA',
   'TOSCHEMA');
 DBMS_DATAPUMP.start_job (h1);
 job_state := 'UNDEFINED';
BEGIN
 WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED')
 LOOP
 status :=
   DBMS_DATAPUMP.get_status (
   handle => h1,
   mask => DBMS_DATAPUMP.ku$_status_job_error
   + DBMS_DATAPUMP.ku$_status_job_status
   + DBMS_DATAPUMP.ku$_status_wip,
   timeout => -1);
 job_state := status.job_status.state;
 DBMS_LOCK.sleep (10);
 END LOOP;
 EXCEPTION
 WHEN job_not_exist
 THEN
 DBMS_OUTPUT.put_line ('job finished');
 END;
COMMIT;
END;
/
grant execute on schema_import to APP_OWNER;

Execution –

Run as APP_OWNER:

EXECUTE SYSTEM.SCHEMA_IMPORT('FILENAME.DMP')