Saturday, December 20, 2014

Why was my abstract (not) accepted?

You know, this is my blog, but I am aware of my role in our user group. The opinions here are mine; I haven't stolen them. Selecting abstracts for our conference is done by many in our board, but I wanted to clarify how I look at the task, what speakers I look for to our conference.

Thursday this week we sent out emails to 71 people who will present on our next conference that starts March 12. Another group received a more disappointing email. Some have questioned why they did not make it (again).

As a user group I think we have some community duties. These came to my mind now:

  • Be there for our members, independent from any vendor, but still cooperating with Oracle and others.
  • Enlighten and show new stuff to our members.
  • Help them finding solutions to problems they may have at work - share experiences.
  • Networking; remind DBAs and other professionals that may be alone in their day job that they are not alone in the community.
  • Also network related: There are a lot of experts inside and outside Oracle worldwide that believe in what they are doing, and are willing to help. The community needs to know about them.
  • Assist in recruiting new people into our fields of technology; more members and new colleagues. 
  • Help new speakers into giving presentations, develop the community with even more good speakers.
This post is mostly about that last bullet point.

I have my heroes among the speakers. First I discovered Tom Kyte, and when he started blogging I discovered the Oaktable Network. From that group I can mention Cary Millsap, Jonathan Lewis, Christian Antognini, Kellyn Pot'Vin-Gorman and Douglas Burns, to name a few.  They always attract a lot of people. And I really enjoy the company of these, really smart, yet levels with you every time. The community is even more than the Oaktable, there are plenty of people who just love what they are doing without carrying any reward for it.

Many of these speakers have been rewarded for their community contributions through the Oracle Ace Program.  OUGN could never have staged such a good conference without the support from Oracle through the ACE program,  we have several ACEs every year and the ACE program covers a substantial amount of expenses. So if someone from OTN is reading this: Thank you!

In this program the best (according to some metric) are rewarded with status ACE Director (ACED). Before they got that far some started as ACE, this year the new entry level is ACE Associate. Now, according to someone's logic we should go through the list of submitted abstracts and pick all the ACEDs first, right?

Not according to mine. I think we have a duty in helping new speakers to the stage. One of the most uplifting experience I have in the community is to discover (new) people that spend time on becoming good at what they are doing through hard work, lots of lab testing, and blogging. The presentations that gave me most came from people who really care about what they talk about (call it passion, if you insist), not from a very slick and polished presentation.

This is one way we in OUGN can give back to the ACE program and the rest of the community: help the not so famous or people who aspire to become an ACE to get better at presenting their stuff. 

I like to think that some of those new ACEs became an ACE because they presented at our conference. One of our Norwegian members came to me after a conference was finished and said one certain presentation made the whole conference for him. It was delivered by one I invited to speak, he was not an ACE then, but is now. It is a lot of work to become an ACE, more then one conference experience, of course, but still I think we can contribute to it.

In addition to this group of speakers who I personally care much about, here is a list of others I look for:
  • Product managers and others in Oracle who shares and cares about the community. PMs for SQL Developer, APEX, among others, are the best ambassadors for Oracle.
  • People who are in the front with new trends and technologies.
  • Famous people who attract attendees, because we need them.
  • Really hard-core nerds that may not appeal to many, but makes the conference for the ones that go to the deep-dive session.
So, if your abstract did not make it, it means among other things:
  • We were blessed with lots of submitted abstracts (over 250 this year, not counting people who said they was going to submit, but forgot about it.)
  • We have to choose from different groups of speakers.
  • Something else happened. (This is the miscellaneous bucket including faults on our side.) 
If you made it, it means that we literally want you on board and that you will succeed. You may have the best experience after your presentation from introverts who want  to know more about what you are doing. And the last night while sailing back to Oslo and you will have no presentation to worry about, you will understand why quite a few want to come back to the OUGN Spring Seminar, aka The Boat Conference :-)

By the way, the worst thing about abstract selections is not to say no to the pros (ACEDs), they can handle it, I assume, but there are some less experienced people that deserves to be speaking, but we couldn't find slots for them.

Cheers to everybody.

Monday, November 10, 2014

Use Domains in SQL Developer Data Modeler

How long is an email address? 30 characters should be enough, I thought. And I was right for about three weeks or so until one of the smartest Oracle experts in Europe tried to submit an abstract at our call for paper site. His email address had 32 chars and therefore failed already at registration. A bit embarrassing.

The data model was created in SQL Developer Data Modeler (SDDM) and I had done something right. In the data model EMAIL was defined as a domain and not simply as VARCHAR2(30) in different places in the data model.

What I needed to do was to update the properties of this domain. Before you change a domain  you may want to know where the domain has been used so you get an idea of the consequences of the change. Just go to the Browser and expand the stuff under Domains, and by right clicking on the domain you select Properties and in the window that pops up you can see where the domain has been used by clicking on Used In:

You can change the definition of the domain in one place, of course, under Tools -> Domain Administration:

Simply select the domain, you have to click on the Modify button before you can update the Domain Properties on the right. 

The fix in the schema was to perform a few ALTER TABLE statements on the involved tables followed by some statements to compile objects that became INVALID afterwards. 

Friday, October 24, 2014

Restore XE database from an AWS instance

Of course there is nothing special about restoring a database from the cloud on your own down-on-earth server, but it helps making this post cool and cloudy.

When I deployed XE to an AWS instance I made a small script to backup the database once pr day (scheduled in crontab), and another to fetch the backupset to my server here on earth. Backup script looks like this:

#!/bin/sh

ORACLE_SID=XE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

rman target / << EOF > $HOME/script/rman.$$
backup database plus archivelog delete input;
delete noprompt obsolete;
list backup of database summary;
EOF

By default the XE is installed without archiving so you need to change that when you install your production XE database. I also turned on auto backup of the controlfile. The RMAN configuration for the XE database looks like this:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/xe/dbs/snapcf_XE.f'; # default

It is mostly default values.

A script that runs on my own box simply does a recursive secure copy (scp):

scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/backupset
scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/autobackup

This fetches today's backup and stores it locally.

I installed XE on my own box and executed the following in RMAN to restore the database. (If the instance is up, take it down with shutdown abort first):

rman target /
startup nomount
restore controlfile from '/home/alone/RMAN/autobackup/2014_10_21/o1_mf_s_861554053_b4dvq5pl_.bkp';
alter database mount;
catalog start with '/home/alone/RMAN/autbackup/2014_10_21';
restore database;
recover database;
alter database open resetlogs;

That's it. 

But don't trust me on this procedure without trying it yourself. Remember a DBA can make mistakes, except when it comes to backup AND recovery.

Wednesday, October 22, 2014

Oracle VM: Templates and Assemblies

One of the cool things with Oracle VM is that Oracle offers many templates you can download from edelivery.oracle.com and import into your repository with Oracle VM Manager (OVMM). Well, since I did the same mistake that you did, that is, not reading the entire README file, I confused templates with assemblies.

Say you want to download a template for Oracle Linux 6, you have to select product pack Oracle VM Templates. The downloaded zip contains one file with extension .ova (does not mean Oracle Virtual Assembly, but Open Virtualization Format Archive). Since it is written "VM Templates" several places on the web site, it is easy to think it should be imported under VM Templates, but you need to select Assemblies as shown here and do the import from there:



Trying to import the file with extension .ova under VM Templates will fail and throw an error message at you similar to:
No VM configure file found
This is obvious if you pay attention to section 2 in the README file, that states:

2. Creating an Oracle VM Virtual Machine from the Template
======================================================
The OVF based Oracle VM Templates should be imported as Assemblies.
Please review the Assemblies section of Oracle VM User's Guide for details
about importing an assembly, and create a virtual machine template from the assembly. 

Assemblies are explained in 7.5.4 in the User Guide (for version 3.2 at least). 
You can create one or many VM Templates from an assembly and from there your new and shiny VMs.

By the way, one easy way to import them is to use a local HTTP server that comes with Python.

Saturday, October 18, 2014

Oracle database XE and APEX on Amazon Web Services

In our user group OUGN we used to collect abstracts for our annual conference with a simple Google Form.

Advantage:
  • Easy to set up 
Disadvantages
  • No possibility for users to reconnect and correct what they had already submitted
  • Lots of manual work
  • Difficult to work with in the process later (selection, agenda, late submissions, special guests)
  • Not really cool or cloudy
Google Forms is really just a shared spreadsheet with a form interface wrapped around it. And like any other spread sheet it is easy to work with in the beginning, but as the number of people involved increases and data grows it soon gets cumbersome to work with.

This year we moved it to Oracle Application Express (APEX). We should have done that long time ago, especially since we are an Oracle User Group...

I figured out that the Express Edition (XE) would be sufficient, even 300 abstracts with speaker info won't take all of the 11GB of space available. Next issue was to figure out where to host it. I looked around and decided that a micro-instance at Amazon Web Services (AWS) should work. It is actually located in Ireland; Hi, Brendan!

The rest of this post is about experiences I made with running APEX in XE on AWS.

OS One of the default Red Hat Linux 6.5 images from AWS works well. You can then download the rpm-file for XE from OTN. The rpm-file is quite complete, no need to install a lot of extra packages or install the oracle-rdbms-server-11gR2-preinstall package.

Swap space During installation of XE the installer may complain about not enough or missing swap space. The default installation that I used did not come with much swap space, and since you don't want your database server to start swapping you shouldn't need any either. But, I like to get rid of warning messages so I created temporary swap space like this to stop the complaints from runInstaller:

dd if=/dev/zero of=/tmp/swap bs=1024 count=1300000 
mkswap /tmp/swap 
swapon /tmp/swap

Upgrade APEX in db The version of APEX that comes with XE 11g is old, but it is easy to upgrade. Download a new version from OTN. Start with 3.5.2 in the Installation Guide

Installing full version of APEX Though strictly not necessary I find it easier to have the full version of APEX installed, not only the run-time version. Running the script apexins.sql took 3.5 hours on this micro-instance, so plan ahead for some other work to do during installation.

URL not found If you start getting messages like The requested URL /apex/f was not found on this server then check the alert log, you may find ORA-04031 errors there, see next bullet.

Shared pool size too small The XE instance is configured during installation with a small shared pool. Try to increase it in small steps (to avoid swapping), our instance is now running with 250MB shared pool. We'll see how it works out as people connects to it. You may well reduce the java pool to a minimum too; set it to 0 and Oracle will allocate 1 granule for it, which is 4MB on this instance. As always, make a pfile copy of the spfile before you do any changes to the spfile that requires a restart of the instance. Certain combinations of memory parameters are not supported and I had to revert the changes a couple of times to get the instance up.

Deploying new versions in APEX I developed the application on a local database and for each round of fixes I exported the application to a file and then uploaded it to the AWS instance with build status Run Application Only. With a good separation of data and application it was easy to deploy new versions as feedback from the user group board members came in. I also learned that in order to deploy a new version you have to change the build status to Run and build application before you can delete or replace it. That is obvious when you've done it once...

Disable access to workspace login With the full installation installed the database will by default allow you to login to the APEX workspace, e.g. c4p.ougn.no/apex. This is typically not something you want in this case, but can be turned off easily with the following executed as SYS or other privileged user:

alter session set current_schema=APEX_040200;
BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('DISABLE_WORKSPACE_LOGIN','Y');
  commit;
END;
/
When you need to redeploy you turn it on again replacing Y with N.

Using standard http port 80 You need to change the ownership of the listener to root and start it as root:

tnslsnr LISTENER -user 0 -group 0 

You will to need to run something similar to this as SYSTEM:

exec DBMS_XDB.SETLISTENERENDPOINT(DBMS_XDB.XDB_ENDPOINT_HTTP, '10.212.42.42', 80, DBMS_XDB.XDB_PROTOCOL_TCP);

The IP address is the internal IP address for the network interface. Check with ifconfig eth0 to see what address is being used.

Not using SSL / HTTPS Of course we could have bought an SSL certificate to protect the usernames and passwords from being sent in clear text. But there is really not much sensitive material here, if you disagree strongly I would like to hear your opinion. Most of the work was actually spent on login and account administration. I can say that the passwords are MD5 hashed before being stored in the database.

Start early with a good data model This has nothing to do with APEX, XE or AWS. It is just that you need to be reminded that everything goes smoother when you start modelling the whole thing in SQL Developer Data Modeller (SDDM) before you start developing code and APEX-application. Keep future features in mind a little without actually including them in the current data model, meaning imagine how you can expand it later for other usage like a membership administration tool. Me being not too much experienced with modelling think that a good understanding of entities and the relationship between them brings you a long way in the right direction. When Heli has finished her book we all will become experts.


Wednesday, October 8, 2014

Include roles and synonyms in DataPump export

For schema level exports it may be useful to include roles and public synonyms relevant to the schemas exported. Instead of generating them manually they can be included in the DataPump export. The following example of a parameter file shows how this can be done:

FULL=YES
INCLUDE=SCHEMA:"IN (SELECT 'PUBLIC' FROM DUAL UNION SELECT username FROM dba_users WHERE username in ('APEX','SCOTT'))"
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner in ('APEX','SCOTT'))"
DIRECTORY=DB_EXPORT
DUMPFILE=apex_expdp.dmp
LOGFILE=apex_expdp.log
INCLUDE=ROLE:"IN (select role from dba_roles where role in (select grantee from dba_tab_privs where grantor in ('APEX','SCOTT')))"  

When deciding what to include in an export you may want to check the table DATABASE_EXPORT_OBJECTS to find the correct path as in this example:

select * 
from DATABASE_EXPORT_OBJECTS 
where object_path like '%ROLE%';

Wednesday, September 24, 2014

OOW 2014

There is one night every year I can stay up as long as I can manage without fearing the next day. Because, on that particular day I can sleep as much as I want on the plane, trying to fool my body into GMT-8, better known as PDT or  Pacific Daylight Time. Yes, it is Oracle Open World again, in the second nicest city in the world, only beaten by Rio de Janeiro.

It is also an opportunity to blog at an unusual hour (at least for me). Though it is not like many have inquired about my travel plans, but it feels good to put it down in writing that I am happy to see Oracle friends again, some I only see in SFO.

Since last OOW, I became an Oracle ACE Associate, and more than ever I am looking forward to meet friends in the OTN Lounge, and that party, you know.

What else... OUGN is paying for most of my expenses and my employer is letting me go, so I'll try to give them both some satisfaction back. Mostly I will try to target and convince some speakers to come to our conference next year, increasingly known as the Boat Conference. If you are reading this and are curious, please interrupt me whatever I'm doing (mostly).

I've learned a few lessons and so far not overloaded my agenda. This week I will go to the global user group leaders meeting (IOUC) on Thursday and Friday. Wednesday morning until 2 pm I'll be at the EMEA user group stand. I will go to different sessions at the Oaktable World, hang out on different social events and of course a bunch of sessions.

Somewhat related, Tim Hall, aka Oracle-Base blogged this today. Surely he will feel reinvigorated after the Bloggers' Meetup and the small talk with Oracle Nerds from all over the world looking for freebies in the OTN Lounge.

The easiest way to get in touch during OOW next week, is probably on Twitter.

Yours,
@OyvindIsene

PS. I did not know how to spell reinvigorated, the spell checker helped me.
PS2. Come to think about it, there are probably a few more late nights without anxiety the following day. But I can't find a better opening right now.

Wednesday, August 27, 2014

Revoke privileges from PUBLIC may cause ORA-600

Oracle Enterprise Manager (EM) reports on many security policies when they are violated. One of them are about execute privileges on standard PL/SQL packages granted to PUBLIC. One a day this summer with nothing better to do I went on to revoke these privileges. The wages for my efforts was several  ORA-600 errors:
ORA-00600: internal error code, arguments: [qmxqtmChkXQAtomMapSQL:2], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
           exceptions.     This indicates that a process has encountered an
           exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number
A quick research on My Oracle Support indicated that some standard packages depends on these privileges granted to PUBLIC. The solution is to grant back to PUBLIC if you are in a hurry, or grant the privilege to the owners of the individual packages (like XDB). The errors continued after the granting was done, but stopped after I bounced the database instance.

Just because EM reports something and the solution seems obvious, it is not alway wise to apply remedy immediately, especially in production. It surprised me a little that EM suggests a security policy that Oracle's own packages do not adhere to.

Sunday, August 24, 2014

Correlation between restore time and used space in a datafile

Recently during restore of a large database (over 10 TB) we monitored the alert log looking for messages like  «Full restore complete of datafile 42 to datafile copy … Elapsed time: 0:39:32»

We saw that the time to restore a 32GB file varied a lot, from around 20 minutes to around 75 minutes. This made me wonder why. We noticed that some of the files belonging to the UNDO tablespace  took usually little time, and then I found a couple of more data files that took little time to restore. Then I thought that maybe the UNDO tablespace at the time of the backup had many unused blocks (because there weren’t many active transactions at the time). So I checked the other two files that did not take long time to restore and found out they had lots of free space in them too. 

So I decided to take the file id and the restore time from all the messages in the alert log and load it into Oracle, in the source database (this all happened when we were making a clone for a RAT session). Then I converted the elapsed time to seconds, found  file size from DBA_DATA_FILES and free space for each file from DBA_FREE_SPACE. Calculating the correlation between restore time and (file size - free space) resulted in 0.96, but for the correlation between restore time and file size, resulted in 0.38. (I used the CORR function.)

By the way the backup was stored on a file system created on a logical volume from a group whose   LUNs where stored on an EMC VMAX 20, but the data files were restored to ASM on LUNs in an EMC XtremIO. 

 As pointed out on Twitter:

it could be caused by the RMAN compression of unused blocks. Next step would be a test with creating a database with several files with different degree of free space, and doing two RMAN backups, one with and another without compression and compare restore time.

This may sounds like a lot of fuzz about nothing, but what we had in mind was the deduplication that the XtremIO does; wondering if the deduplication of empty blocks could  take less time than blocks with real data in it.

Wednesday, July 23, 2014

DBMS_LDAP and ORA-12703

In case you are using the DBMS_LDAP package from a reasonable modern environment you may run into the error:
ORA-12703 this character set conversion is not supported
The simple solution to this is to run:
begin
  DBMS_LDAP.UTF8_CONVERSION := false;
end;
/
or if you are using SQL*Plus or SQL Developer, just:
exec DBMS_LDAP.UTF8_CONVERSION := false

Monday, July 7, 2014

Connecting to a guest console in Oracle VM

I struggled to connect to the console of a newly created VM. The VM was created using Oracle VM Manager (OVMM) and despite efforts to install the required packages I could not use the console button in the Manager interface.


I noticed that the VNC server built into the VM was listening on 127.0.0.1 only (on the OVM server), you can see this if you check the file vm.cfg for the guest. Without analysing how the OVMM would try to connect to the VNC server on the other host I decided to create an ssh tunnel from my Mac to the OVM server:

ssh -L 5901:localhost:5901 root@fu.bar.com

(replace fu.bar.com with IP address or host name of your OVM server).

Now, I think I have found a reason why Oracle specifically recommends TightVNC as a VNC viewer; I tried two other VNC viewers on my Mac, but I could not make them work with the console on the VM, sometimes it connected, but later it would hang, another just aborted. I downloaded the java version of the VNC viewer from http://www.tightvnc.com/download.php and connected without any problems to localhost and port 5901 (using the tunnel created above).

Friday, July 4, 2014

Importing ISOs to Oracle VM using OVM Manager

Just a simple trick to import ISO files to the repository using Oracle VM Manager (OVMM). The import function supports imports through protocols http, https and ftp. If you want to do this in the supported way you can use a simple web server to serve the ISO files for you.

Python comes with a simple http server built in. If you have python installed (which is the case on both the OVM server and the OVMM host) you can start it with:
python -m SimpleHTTPServer

To make it easy for you, go to directory where the ISO file is located, start the http server and verify that you can see the file listed in a web browser. Then do the import in Oracle VM Manager.


In this case I copied the ISO file to OVM server (dom0) and started the http server there, and the URL for the import becomes as shown in the capture below (or use the IP address of the server where the ISO file is located and remember to use port 8000):

The name of the imported ISO is the same as the file name, rename to a say Oracle Linux 6 U5 and put in the origin of the file in description.

Wednesday, July 2, 2014

Real Application Testing - some notes

This post is a mixture of experiences from a recent project and an overview on how to use RAT in a project.

Real Application Testing (RAT) is a licensed feature for the Enterprise Edition (EE) of the Oracle database. It was released on 11g, but can be used to test migration of databases from 9i and 10g to 11g (assuming that the source databases have a required version/patch). The term Real is, I reckon, used to hint at a more authentic testing process. The tests are executed based on a capture from a real production system.

RAT can be used to:

  • Evaluate new hardware, including migration from one platform to something completely different.
  • Verify execution of SQL from today’s production on a newer version of the database
  • Analyze the effect of changes in configuration
  • Scaling up, see how current system works with higher load; this works best for queries / reporting and not so well for data manipulation (DML).

A project involving RAT can be divided into these activities:

  1. Capture workload from a production system
  2. Create a clone of production system with flashback database enabled and a restore point created.
  3. Prepare for replay on test database and with agents on application servers or other client machines.
  4. Execute replay
  5. Generate reports (RAT and AWR)
  6. Flashback database
  7. If you haven’t reached a conclusion yet, repeat from 4.

In two projects I was asked to do step 2 - 6 and figured out that I should write down what I learned for posterity. (The fact that I have a friend in Belgium that reminded me about blogging more is a mere coincidence.) I will not put many details on step 1 and 2 here.

Some think it is a good idea with a long capture, but keep the replay part in mind and the time it takes to actually execute the replay. Though the flashback of the database is impressively fast, a longer replay (with much updates) will lead to more time spent on setting the database back with flashback. If you can limit the capture to say two hours that is a time saver later, but if you need to confirm say scalability for one night's ETL jobs, you may need to capture several hours. Before you start with capture, make sure you have all the backups you need to duplicate the database with point in time recovery as close to the starting point as possible. This should not be an issue since all productions system (IMHO) should run in archiving mode and being backed up. This is an opportunity to verify your backups.

For large databases it may take quite some time to transport the backup (and capture files) to new platform and have it restored to a clone. Flashback database is a wonderful feature since it takes litle time to flashback a database compared to a full restore for large databases. Flashback is turned on with command:
ALTER DATABASE FLASHBACK ON;

A restore point is created with:

CREATE RESTORE POINT B4_REPLAY GUARANTEE FLASHBACK DATABASE;

The name of the restore point is your decision (within limits). You will need some space for flashback logs, depending of the amount of changes to the database during replay.

In a bypass; yes, there are as usual two ways to do this. Enterprise Manager for the young and aspiring DBAs, or you can use the PL/SQL packages. When I have the routines ready for using the API I tend to favour them, so I did most of the work with the packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY together with the packages for creating AWR reports DBMS_WORKLOAD_REPOSITORY. Sure, I had some looks at nice graphs and reports in EM too.

Going into more details on step 3; the result of the capture is a collection of many files that needs to be transported to the new platform. On the test database create a database DIRECTORY object that points to the file directory where they are stored. First step is to process the capture with:

exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('REPLAY')

where REPLAY is the name for the directory. The result of this is a new directory with a name that starts with pp followed by a version number. Copy it all to the machine(s) that will host the replay agents (actually replay the workload and send it to database.)  If you are doing this on a RAC-database, copy the same stuff to other nodes with same path for the directory object.

In each installation of the Oracle database and client you have the workload replay client (wrc) included under $ORACLE_HOME/bin. It’s task is to send SQL from the captured workload to the database.  Before you start the actual replay a calibration should be run first. With ORACLE_HOME and PATH set, execute:


wrc mode=calibrate replaydir=/tmp/replay

This will report the recommended minimum number of clients. Too few means that the replay clients cannot replay the workload fast enough. The same will happen if the servers where the wrc binaries are overloaded, or the network between them and the database have any issues.

So if you have to execute 10 clients, put 10 copies of this in a script:

#!/bin/bash
wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay 
sleep 1
wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay
sleep 1

wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay 
sleep 1

Don’t run it yet.

Now we’re onto step 4. You repeat the following sequence if you decide to repeat a replay, but the preparation hitherto is only done once. Initialize the replay with the following command, using a distinct name for the replay each time:

exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name=>’run_1’, replay_dir=>’REPLAY’)

In the middle of this we have to do some remapping... The connections to the database used during production capture are not the same as used during replay. After the previous command has completed you can query the view DBA_WORKLOAD_CONNECTION_MAP and see what was used during capture. This view is empty before the initialize_replay procedure is executed, after it has values in the column CAPTURE_CONN, but REPLAY_CONN is empty. First time you have to look at the output and see if you have many different connections to the database and figure out how you will remap them. Often, only one connection string is used (connection string as the key in TNSNAMES.ORA) and the mapping is simply replacing it with the one you are now using to reach the new clone database. In that case you can use the following PL/SQL block to remap:

begin
  for i in (select conn_id, capture_conn 
    from dba_workload_connection_map m, dba_workload_replays r
    where replay_id = id
    and name = ‘run_1’)
  loop
    dbms_workload_replay.remap_connection(connection_id=>i.conn_id, replay_connection=>'cloneserver:1521/clonedb');
  end loop;
  commit;
end;
The name => ‘run_1’ refers to the name you gave to the replay. You can either use an entry from tnsnames.ora or connection string as shown above when setting the new connection. If you have some load balancing or application partitioning where you use different connections to the database they have to be remapped accordingly. Though you have to execute this for every replay, you only spend time on this first time and keep the PL/SQL ready for next time you do a replay.

Now after initialize, next step is prepare (!) In this step you set various parameters that how the replay is executed. In it simpliest way:

exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => TRUE, think_time_scale=>100, scale_up_multiplier=>1)
synchronization set to ‘TRUE’ is equal to ‘SCN’, another more advanced (according to manual) is ‘OBJECT_ID’. This sometimes causes problems for the replay and testing several times with synchronization to ‘OFF’, ‘SCN’ or ‘OBJECT_ID’ may be necessary.

Later on when a normal replay has been executed it is always interesting to see what happens when you reduce think time or scale up the load. Note, that DML, when scaled up won’t actually update the same way, so this needs some testing from you. For reporting usage scaling up should work, however, note if you double the number of users, they are in realilty not likely to send the same SQL as today twice as many times, that is, users usually will run slightly different queries. But if this is a problem you have to find out by analysing the reports - if an a few statements are popular in an OLTP system you can assume they will remain so after you scale up.

Now you can add filter for what will be actually replayed, but I am going to leave it out. After you have exuted the prepare_replay procedure above, you can kick off the script on the clients, that is the script that includes the wrc commands. They will start and write out a message that they are waiting for replay to start.

You can start the replay with:

exec DBMS_WORKLOAD_REPLAY.START_REPLAY()
When this is executed the replay is running and the agents (wrc) writes out a message that replay has started.

EM has a nice page for monitoring a replay. A few times we have seen that the replay lags behind compared to capture, but catches up later on, or sometimes takes longer time than the capture. Which sometimes means an error with RAT itself and not necessarily a problem with the new platform.

In step 5, after replay is finished, you generate a RAT report and also AWR reports. In DBA_WORKLOAD_REPLAYS you’ll find the replay_id of the replay and the RAT report can be generated with:

select dbms_workload_replay.report(42,’HTML’) 
from dual;

Generate the AWR reports as usual. If you want to compare AWR report from capture with replay, you will have to import the AWR data (AWR data is automatically exported after the capture and included in the same directory). First verify that the capture is visible in DBA_WORKLOAD_CAPTURE. If not you can import all the data about captures found in the directory with:

exec dbms_workload_capture.get_info(‘REPLAY’)
where ‘REPLAY’ is the name of the DIRECTORY-object where capture is stored. The capture(s) should now be visible in DBA_WORKLOAD_CAPTURES. The AWR-data belonging to it can be imported with

select dbms_workload_capture.import_awr(42,’LUSER’) 
from dual;

Take a note of the number returned. This is a generated DBID that you will use later to refer to the database of the capture (this means that this is a made up DBID that is different than the DBID of the database where you executed the capture). 42 above is the capture_id from DBA_WORKLOAD_CAPTURES and LUSER is the name of a schema used for staging. Just create on temporary if you don’t have one ready.

The import can take some time. When it is finished you can see in DBA_HIST_SNAPSHOT what snapshots are available, check for the DBID that was returned in last command. You can now create comparsion reports with:

select * 
from table(dbms_workoad_repository.awr_diff_report_html(123456789,1,120,130,734501234,1,10,20));

The first three parameters are DBID, start snap id, end snap id; all from the first database (source/capture) and the last three the same respectively for the second database where replay took place. Creating these reports work pretty well in SQL Developer, use Ctrl-A and Ctrl-C to copy the html code and paste it into an editor and save it. You can actually create more reports than I have shown here, see the documentation for the mentioned packages to get an idea. AWR data is automatically exported to the same directory after a replay. This means you can later be import them into another incarnation of the database to compare different replays.

Step 6, flashback database is pretty easy. Shutdown the database, start it in mount, execute:

flashback database to restore point b4_replay;

Then open it with:

alter database open resetlogs;

If you are on RAC you have to shutdown the other nodes and start them after the previous command is completed.

There used to be some bugs that caused the database to crash spectacularly with an ORA-600 during flashback leaving the database useless and forcing you to do a new restore/duplicate from backup. Since I’m stressed during a project like this, and therefore I don’t have time to waste, I tend to avoid structural changes (like moving tablespaces around after creation of the restore point) that can challenge the flashback. I am not sure if the bugs are still there, though.

Step 7, are you done yet? I usually have to run the replay over and over several times trying out with different parameters for procedure dbms_workload_replay.prepare_replay(...). This takes a lot of time, don’t underestimate the project. Also remember that people will question your results.

In conclusion, try to figure out before you start what you want to prove. Are you comparing two systems, you need to agree on a common set of parameters; or are you trying to prove that a system is good enough (without comparing it to the old?) Try to lower you ambitions or scope, the time it takes with restore, replay, generating reports, looking over them, and possibly analyze further with extended trace, starting over,... it may feel like a walk in the desert sometimes.


Sunday, June 22, 2014

Some thoughts from last PoC

For the second time I have been involved with a proof of concept (PoC) where testing of an alternative hardware platform was the goal. Common for both PoCs were:


  1. The other option was Exadata
  2. Real Application Testing (RAT) was used to replay production recordings on the new hardware.


And for the second time I have concluded that sales people take this too easy. It is not easy and assuming you will outperform previous platform just because you have a bunch of less expensive flash disks, cache cards, and more is arrogant at best.


A decent PoC requires lots of preparation and you need to have done some planning for capacity and load. If you have no idea of throughput, expected load, the nature of the applications and more, you are likely to fail.


Exadata is not the optimal solution for everybody, that is not why Oracle charges lots of money for it; it is not like they are thinking “Everybody will want this, so we can double the costs”. Not that I know from the inside, but some in Oracle probably thought they needed to create a solution for processing large amount of data fast. In order to do that a congestion of the CPU has to be avoided by filtering out data that is not needed before it reaches the database. As an added benefit of not shipping useless data the system will perform less I/O. If you don’t need that, Exadata may not be for you. On the other hand, if a customer needs this for a BI solution and you are selling what you like to call “standard commodity solution”, be prepared to have something that scales and can replace the Exadata features (if Exadata is what you are competing against.)


If you sell a solution to the 10% of the costs, it will not succeed simply because your bidding price is low; without performance it will fail. Without careful planning and analysis you will fail. Cheap food is nice, but if it makes people sick, they are not happy to spend the money on hospital bills. And they will go somewhere else.


There is no good substitute for proper understanding. DBAs have heard for many years that we will become redundant because modern systems are self-managing or something like that. Well, I gladly take up brewing if that becomes a reality, but what has changed is the amount of decibels from the CIO screaming when new and more expensive hardware does not perform after all.


The fun with Exadata does not all come from the fact that it is expensive and state of the art. Rather we have had these experiences where you combine your understanding of the platform with old wisdom like “filter early” and end up with incredible performance improvements. Like one day I asked a domain expert about the data we were querying, and after adding one redundant predicate to the SQL statement, the response time went from 90 seconds to less than 1 second. The whole improvement was due to smart scan taking place.

Back to the PoC; we spent weeks waiting for proper integration between servers (running Linux) and the storage. Wrong interface was used for the interconnect (this was RAC), RAID 5 was used because after all, they hadn’t provided enough disks. The devices that would provide the caching features was not used, but the low-level devices was discovered by ASM instead due to wrong configuration (parameter ASM_DISKSTRING was unchanged leading to the sd* devices being discovered). After a long time the RAT replay showed that for a small number of users the performance was acceptable, but as load increased the throughput stalled and we concluded that the new solution was not dimensioned properly. The vendor suggested a new and even better storage solution with even more flash…


Probably without seeing it themselves, the vendors in both cases demonstrated the benefit with an engineered solution. When the customer sees that you need weeks to have the storage play with your server, they start to think that maybe Oracle has a point with their preconfigured and tested solution, ready to plug in. (Yes, we know that reality is not that simple, search Twitter for #patchmadness for an illustration.)


There are a few takeaways from the RAT testing itself, which is material for the next post. What I can say now is that restore of database, performing replays and flashback and start over again takes a long time. Keep it simple and try to keep the capture as short as possible without losing value for what you are testing.
The goal with a PoC is to qualify or disqualify a new solution. If a new suggested solution is disqualified, the PoC should be deemed a success; this was actually stated by the project manager and not by me. PoCs should not be taken too easy. Think about all the problems you can avoid by stopping the wrong solution to enter your data center.

Friday, March 28, 2014

42 Reasons to join me at #OUGN14






  1. Oracle User Group Norway (OUGN) organizes the biggest Oracle user conference in Scandinavia.
  2. As of March 28, 341 DBAs, developers, managers, sales people, and more have figured out why. Read on for more reasons if you have not registered yet.
  3. This year we are breaking many of our own records, for example we have 66 speakers from 15 different countries.
  4. We have added another track with a total of 8 different tracks.
  5. Our conference, also known as the Boat Conference is getting famous worldwide. The call for paper this time resulted in more than 200 submitted abstracts, from more speakers than any time.
  6. We  start even earlier to cram in more sessions - it is hard to say no to all good speakers out there, we have never had so many sessions, more than 100 in total.
  7. This year we have two roundtable sessions - meet the experts and join the conversations.
  8. Something completely new: Women in Technology - WIT. See the abstract here http://bit.ly/OVrwgR If you have been to conferences before you know why this is important.
  9. Meet Oracle’s coolest product manager, for SQL Developer Jeff Smith, check his blog here http://www.thatjeffsmith.com/ and you get the idea.
  10. Are you a DBA and wonder why Java? Do you code in Java and wonder “What now, Oracle?” Meet Oracle’s PM on Java, Bruno Borges. Coming all the way from São Paulo, Brazil to talk about Java and more.
  11. The Java track has never been better, with more Norwegian speakers, including Aslak Knutsen from Red Hat. Markus Eisele (who knows everybody in the Java community) will be there too.
  12. We can’t of think any better place in Norway to mend the sometimes broken relationship between Java coders and DBAs.
  13. How is your system performance? Learn optimization from the expert Cary Millsap.
  14. DBAs blame developers for bad design, developers blame the database for being old and not scalable. Who is right? Learn how these groups can work together from the same man.
  15. Have you been to an Oracle presentation and been so entertained that you lost sense of time? You will after going to one of Connor McDonnald’s presentations. Last year his presentations received record high scores on content and delivery.
  16. Still confused about fusion? The middleware track will help you and your future will look green.
  17. How often have you googled for a solution and ended up with a nice write-up at oracle-base.com ? Now you can meet the man who wrote all of it, Tim Hall aka Oracle Base, look for a man in an Oraclenerd T-shirt.
  18. Not sure if Norway is big enough for Big Data? Surely Big Data Solutions Director Luis Campos from Oracle PT will tell us.
  19. Software projects are difficult, get off with a good start and a good data model in this presentation with Heli (the world has only one Heli like Heli, so first name is OK, we think).
  20. Do you like Scottish mood and complicated humour? You’ll be able to read 10053 trace files backwards before you understand all his jokes, which make his presentations never boring. Meet OUGN best supporter and a long-time friend, Doug Burns.
  21. Do you usually wait three years after release of R2 before you upgrade? You will have no reason to wait after you’ve got all the details in the Hitchhiker’s Guide to  Oracle Database Upgrades
  22. With 12c PL/SQL is stronger than ever, according to Bryn in his presentation P133.
  23. Some worried that Oracle would kill MySQL once they bought it. They were wrong, our MySQL track is full of good content from Oracle’s own developers and users out there. Meet attendees and Oracle staff and ask about MySQL (Care for an meetup btw? We’ve got an open slot for you.)
  24. Has everybody moved to Linux? Check out this presentation on Sparc and LDOMS.
  25. The apps track is back!
  26. You too are late learning APEX? Don’t worry, check out this.
  27. Several managers have signed up. If you manage DBAs and other techies this is your opportunity to see them in their preferred habitat, and you can exchange experience with other managers.
  28. Do you have challenges in your job? Alone with a new design, or maybe you have learned something unusual? Share your experience and discuss it with other techies. Learn what is not in the documentation.
  29. Introverts and control freaks have no better place to socialize then on a user conference. Not tired of discussing RMAN configuration? You will not be alone on this ship.
  30. Have a question for Oracle sale, pre-sale, technologists or someone from the C-level? Oracle Norway, US, DE and more are sending several to support us, and they don’t mind sitting down for a talk, be it in a bar or in the Boardroom. Who knows what can happen in international waters. Just don’t mention licenses on the dance floor, that is bad mojo.
  31. This year we have a new native app for smartphones. It will work even when you are offline. Make your own agenda and share it on Twitter.
  32. One of two frequent feedback from speakers and attendees is that the conference is well organized. Thanks to Color Line and the venue the conference is usually a smooth sailing. Actually we can’t think of any better venue, so we will probably use the same setup for many years.
  33. The other feedback is that the food is quite good. Breakfast, lunch and dinner are served in a restaurant with excellent standard. More info about the ship here.
  34. Being on a ship, people stick around and socialize with other speakers and attendees. Not too big and not to small. This is a fantastic networking opportunity; you’ll always find someone, when you’ve had enough you can always retreat to your cabin or a calm restaurant.
  35. The weather forecast looks quite promising, with sun, no rain and 4º C the Oslofjord will be a nice scenery going and returning.
  36. Tax free...
  37. Though we have wifi in the conference area, you may actually enjoy being offline sometimes. It is a good excuse to not read email and check whatever addiction you have when we are going through Skagerrak.
  38. If you come from abroad and want to know more about the Norwegian market you get to meet a variety of large and small customers.
  39. Actually more attendees from outside Norway has registered than any time before.
  40. Forgot to mention the cloud. Surely, someone will mention it on the conference. There will probably be more clouds in the sessions than in the sky, because we will have no-cloud weather during the conference.
  41. We also have many presentations with tons of no-hype stuff, proven work from experienced people.
  42. Seeing first-time speakers freak out when the ship starts moving is priceless.


If you are still reading this, it may be to late. Ticket sale ends Monday March 31, 09 hs CET. Mail us if you are desperate.

Saturday, January 18, 2014

Kurs med Cary Millsap i Oslo dagen før #OUGN14

Vårens store høydepunkt er OUGN sitt vårseminar. Her er programmet og her kan du melde deg på. Drar du til Oslo for å delta på konferansen anbefaler jeg at du flyr inn dagen før for å bli med på et annet stort høydepunkt, nemlig et dagskurs med Cary Millsap i Oslo.

Dette er et kurs både for utviklere som integrerer mot Oracle database og for Oracle DBA-er. En bedre presentasjon av kurset finner du på påmeldingssiden.

Personlig så ser jeg på det som et skille i karrieren min, mellom før og etter at jeg lærte metodene til Cary. "Tuning" som det ofte kalles er ofte forbundet med myter og råd som baserer seg på prøving og feiling, sjelden effektivt i komplekse systemer. Ved å bruke SQL trace og de riktige verktøyene til å finne frem til hva som tar tid, kommer man raskere frem til målet. Målet er altså å optimalisere responsen til en transaksjon mot databasen, en tung rapport, interaktiv bruk fra en web-side, osv.

Kurset begynner kl 10 og holder til i Bjørvika, det blir dermed mulig å fly inn til Oslo på onsdag morgen før kurset.

Har du spørsmål rundt kurset ta gjerne kontakt på Twitter, email eller telefon. Kurset arrangeres av Method R.