Applied Mathematics for Database Professionals – Chapter 3

Some More Logic

This chapter is slightly shorter than the other chapters in the Mathematics section of the book, that however does not make it any easier. It continues on from where Chapter 1 finished. You can have a look at it, again via google books.

Starts off with a discussion of the various properties of the logical connectives. Then moves on to talking about Quantifiers. One way of turning a predicate into a proposition is by the use of quantification to say for how many possible values a parameter of a predicate actually meet a specific requirement.

The two fundamental quantifiers are universal quantification (for all) and exitenstial quantification (there exists). There follows some examples of these in action. An example might help clarify this:

The above can be read as “For all x that are members of the set N of natural numbers 2 times that value is also a member of the set of natural numbers”

Interesting point that when working with finite sets can be treated as an iterated OR while the can be treated as an iterated AND.

Quantification over the empty set is discussed. I fear that universal quantification over the empty set being always TRUE completely and utterly baffles me. Interestingly, wikipedia have this as a vacuous truth.

Moves on to discuss various properties of quantifiers, nesting, negation, distributive and various rewrite rules. I found this really quite challenging.

Finishes the chapter talking about normal forms (canonical form) is just a standard way of representing an object. In logic normal forms allow you to more easily compare two predicates. conjunctive normal form (CNF) is a conjunction (and) of clauses where a clause is a disjunction (or) of literals. disjunctive normal form (DNF).

As often seems to be the case as the mathematics section is building up a toolset for practical application later, normal forms are utilised later in the book in relation to data integrity constraints.

Fixing an Oracle 11gR2 Grid Infrastructure Upgrade Bug

Finally, after 4 weeks of getting nowhere with Oracle Support, I’ve eventually managed to install the 11.2 Grid Infrastructure and upgrade clusterware on my 2 node RAC cluster from 10.2.0.4 to 11.2.0.1.

First of, I’ve got to point out this is a very old Linux installation. Almost certainly your linux version will be much more recent than this one:

oracle@linuxrac1:DBA -> uname -r
2.6.9-22.ELsmp

I confess and admit this is an ancient kernel. I’m banged to rights. What I tend to do, is install the servers, install oracle and then never upgrade the underlying OS. Once the hardware has been deemed end of life, we will migrate the database off to new hardware, which will have been installed with the latest O/S version. What this means is that you will probably never have to attempt an 11.2 clusterware install on such an old kernel, unlike me. Which is fortunate for you, for out the box it does not work.

If you find yourself in that unfortunate situation and you get the following error:

Cannot get node network interfaces (/var/opt/grid/11.2.0/bin/oifcfg iflist -p -n failed.) at /var/opt/grid/11.2.0/crs/install/crsconfig_lib.pm line 1786.

Then here is what I did to get the clusterware upgraded from 10.2.0.4 to the 11.2.0.1 version.

Edit $GRID_HOME/crs/install/crsconfig_lib.pm

Change line 1785 to:

($rc,@iflist_out) = get_oifcfg_iflist($CFG->OLD_CRS_HOME);

Change line 9192 to:

my $oifcfg = catfile($CFG->params('OLD_CRS_HOME'), 'bin', 'oifcfg');

Basically in both those lines you are change the variable to become the OLD_CRS_HOME, essentially for some of the upgrade, the executables have to be run from the 10.2 CRS Home.

I also needed to change the file $GRID_HOME/crs/install/crsconfig_params to include the definition of the OLD_CRS_HOME:

Edit $GRID_HOME/crs/install/crsconfig_params

Insert at line 55

OLD_CRS_HOME=/opt/oracle/product/crs

Of course your 10.2 CRS install may be located in a different location.

Once these are in place, you can run the rootupgrade.sh script once more and this time it should complete successfully.

It was such a relief to see the following:

oracle@linuxrac1:DBA ~> crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]

And have the clusterware running from the new Grid Infrastructure location:

oracle@linuxrac1:+ASM1 ~> ps -ef|grep grid
root     25845     1  0 Jan19 ?        00:00:08 /var/opt/grid/11.2.0/bin/ohasd.bin reboot
oracle   26312     1  0 Jan19 ?        00:00:15 /var/opt/grid/11.2.0/bin/oraagent.bin
oracle   26328     1  0 Jan19 ?        00:00:00 /var/opt/grid/11.2.0/bin/mdnsd.bin
oracle   26340     1  0 Jan19 ?        00:00:00 /var/opt/grid/11.2.0/bin/gipcd.bin
oracle   26351     1  0 Jan19 ?        00:00:03 /var/opt/grid/11.2.0/bin/gpnpd.bin
root     26374     1  0 Jan19 ?        00:00:03 /var/opt/grid/11.2.0/bin/cssdmonitor
root     26391     1  0 Jan19 ?        00:00:03 /var/opt/grid/11.2.0/bin/cssdagent
root     26393     1  0 Jan19 ?        00:00:16 /var/opt/grid/11.2.0/bin/orarootagent.bin
oracle   26411     1  0 Jan19 ?        00:00:03 /var/opt/grid/11.2.0/bin/diskmon.bin -d -f
oracle   26427     1  0 Jan19 ?        00:00:41 /var/opt/grid/11.2.0/bin/ocssd.bin
root     26495     1  0 Jan19 ?        00:00:03 /var/opt/grid/11.2.0/bin/octssd.bin
root     26512     1  0 Jan19 ?        00:00:22 /var/opt/grid/11.2.0/bin/crsd.bin reboot
oracle   26523     1  0 Jan19 ?        00:00:06 /var/opt/grid/11.2.0/bin/evmd.bin
root     26544     1  0 Jan19 ?        00:00:02 /var/opt/grid/11.2.0/bin/oclskd.bin
oracle   26601 26523  0 Jan19 ?        00:00:00 /var/opt/grid/11.2.0/bin/evmlogger.bin -o /var/opt/grid/11.2.0/evm/log/evmlogger.info -l /var/opt/grid/11.2.0/evm/log/evmlogger.log
oracle   27044     1  0 Jan19 ?        00:00:00 /var/opt/grid/11.2.0/opmn/bin/ons -d
oracle   27045 27044  0 Jan19 ?        00:00:00 /var/opt/grid/11.2.0/opmn/bin/ons -d
oracle   30867     1  0 Jan19 ?        00:00:25 /var/opt/grid/11.2.0/bin/oraagent.bin
root     30869     1  0 Jan19 ?        00:05:18 /var/opt/grid/11.2.0/bin/orarootagent.bin
oracle   31413     1  0 Jan19 ?        00:01:17 /var/opt/grid/11.2.0/jdk/jre//bin/java -Doracle.supercluster.cluster.server=eonsd -Djava.net.preferIPv4Stack=true -Djava.util.logging.config.file=/var/opt/grid/11.2.0/srvm/admin/logging.properties -classpath /var/opt/grid/11.2.0/jdk/jre//lib/rt.jar:/var/opt/grid/11.2.0/jlib/srvm.jar:/var/opt/grid/11.2.0/jlib/srvmhas.jar:/var/opt/grid/11.2.0/jlib/supercluster.jar:/var/opt/grid/11.2.0/jlib/supercluster-common.jar:/var/opt/grid/11.2.0/ons/lib/ons.jar oracle.supercluster.impl.cluster.EONSServerImpl
oracle   31511     1  0 Jan19 ?        00:00:01 /var/opt/grid/11.2.0/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle   31521     1  0 Jan19 ?        00:00:01 /var/opt/grid/11.2.0/bin/tnslsnr LISTENER_SCAN3 -inherit

I should point out that I was able to upgrade a 2 node RAC cluster running with a 2.6.9-34 kernel without having to hack around with any files.

Applied Mathematics for Database Professionals – Chapter 2

Set Theory

I think this chapter starts off less intimidating than the one on logic. Sets are quite a familiar concept from school and Tom Kyte is always extolling the virtue of “thinking in sets”. This chapter is available to peruse via google books.

Starts off with definition of a set as a collection of distinct objects each of which is a member (element) of the collection (set). The set is fully characterised by these elements. Two sets are the same if each element of one set is a member of the other, and vice versa.

Discussion of how to specify the elements of a set.

Enummerative: This is naming each element of the set individually.

Predicative: An element is a member of the set when the predicate with that element is true.

Substitutive: This uses an expression and a set from which you substitute values into the expression

Hybrid: This is a combination of the predicative and substitutive method.

In mathematics sets can be finite or infinite but with databases all the sets are finite. The cardinality of a (finite) set is the number of elements in the set.

Discussion of subsets, supersets, and powersets. Basic operations on sets are described including the familiar union, intersection and difference (minus), the properties (commutivity, associativity, distributivity) of the operators are also described.

Cartesian product is defined as the set of all ordered pairs of the elements of the two sets you are creating the cartesian product of, e.g.

A:={1,2} and B:={3,4}
then
AxB = {{1,3}, {1,4}, {2,3}, {2,4}}

update
One of the hard parts of mathematics is that things need to be exact, so thanks again to Toon Koppelaars for pointing out that the above example is not the correct cartesian product!

To try again:

A:={1,2} and B:={3,4}
then
AxB = {(1;3), (1;4), (2;3), (2;4)}

Ordered pairs in Applied Mathematics for Database Professionals use the ; to separate the coordinates of an ordered pair.

Note that AxB does not equal BxA. It is non-commutative.

The concept of ordered pairs is quite a crucial one in this book and operators and are introduced for choosing the first and second parts of an ordered pair. Note unlike the elements of a set the ordering of an ordered pair is important.

Finally, the chapter ends with a series of exercises, which I’ve been finding useful to gauge my understanding of the chapters. As I say I found this chapter less hard going than the first chapter, but having read further on, one of the great things of the book is how you can see in later chapters the theory in these chapters being put to good use.

Oracle 11gR2 Grid Infrastructure Upgrade Bug

I’ve still been working away on migrating a 10gR2 RAC cluster running on a RHEL4 2 node cluster up to 11gR2. However I seem to have hit an install issue right at the first hurdle.

The system involved is an x86-64 system running a 2.6.9-22 kernel. The grid infrastructure install seemed to be passing by without incident, I had chosen the “Upgrade Grid Infrastructure” option at the Installation Type dialog box during the install, and had ran through the install, until the very end when a box pops up asking for the rootupgrade.sh script to be run:

This was not a worry, so I went off and attempted to run it, but then the following occurred:

[root@linuxrac1 11.2.0]# ./rootupgrade.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /var/opt/grid/11.2.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: 

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2009-12-21 15:45:59: Parsing the host name
2009-12-21 15:45:59: Checking for super user privileges
2009-12-21 15:45:59: User has super user privileges
Using configuration parameter file: /var/opt/grid/11.2.0/crs/install/crsconfig_params
Creating trace directory
Cannot get node network interfaces (/var/opt/grid/11.2.0/bin/oifcfg iflist -p -n failed.) at /var/opt/grid/11.2.0/crs/install/crsconfig_lib.pm line 1786.

Running the oifcfg command by itself, seem to actually produce some sensible output:

[root@linuxrac1 11.2.0]# /var/opt/grid/11.2.0/bin/oifcfg iflist -p -n
eth0  213.248.202.0  UNKNOWN  255.255.255.0
eth1  10.0.0.0  PRIVATE  255.0.0.0

This is the same output if you run oifcfg from the 10gR2 install:

[root@linuxrac1 11.2.0]# /var/opt/oracle/product/crs/bin/oifcfg iflist -p -n
eth0  213.248.202.0  UNKNOWN  255.255.255.0
eth1  10.0.0.0  PRIVATE  255.0.0.0

However, it seems like some commands may be running, incorrectly, from the 11gR2 when they should be running from the old 10gR2 home:

The 11.2 command produces the following

[root@linuxrac1 log]# /var/opt/grid/11.2.0/bin/oifcfg getif -global

Failed to initialize GPnP

While the 10.2 command produces following output:

[root@linuxrac1 log]# /var/opt/oracle/product/crs/bin/oifcfg getif -global
eth0 213.248.202.0 global public
eth1 10.0.0.0 global cluster_interconnect

I took out a Service Request with Oracle and after a couple of weeks, they are conceding the possibility that this might be a bug, 9126737 which is as yet unpublished. They are muttering about needing a backport for this fix. This of course will take an as yet indeterminate quantity of time.

This service request has been on the go for 3 weeks, and I don’t feel any closer to a solution.

I can’t believe that such a mainstream platform can have a fundamental bug, that is preventing from installing the 11.2 Grid Infrastructure.

I’d very much like to hear from anyone if you have managed to upgrade a 10gR2 RAC system to the new 11.2 Grid Infrastructure, particularly on Linux.

Applied Mathematics for Database Professionals – Chapter 1

Logic: Introduction

“Everything should be made as simple as possible, but not simpler” – Albert Einstein

The first chapter of Applied Mathematics for Database Professionals is on logic. This chapter is actually available to download from Apress as a free sample chapter. I think this is one of the harder chapters in part 1: the mathematics, so if you can understand this one, I think you’ll be fine with this book. I studied logic in a pure mathematics course in first year at University, and a lot of these terms rung some bells. But this was nearly 2 decades ago, so those bells were quite distant and ringing quite softly.

I also really like how each chapter starts with a page outlining what is going to be covered within the chapter, and each chapter finishes with a series of bullet points highlighting the key features that were covered. This technique really adds to the pedagogic nature of the book.

Chapter 1 begins with a brief introduction to Logic, which obviously goes way back to Ancient Greece. Explains that the relational model is based on logic and that:

“one of the goals of the book is to explain the mathematical concepts on which relational data management is based”

Some definitions of values, variables and types.

A value is a constant, cannot be changed.

A variable is a holder for a value, variables can change over time.

variables (and values) are always of a particular type. A type is the set of values from which a variable is allowed to hold its values, e.g. integers, characters, etc.

Discussion of propositional logic. A proposition is a declarative statement that is either TRUE or FALSE. Examples of things that are not propositions, including equations that can be true or false depending on the values of the variables.

Found the discussion on predicate logic quite hard going, states that a predicate is something having the form of a declarative statement that has variables that once the values of which are known turns the statement into a proposition. Essentially a predicate only can be evaluated (as TRUE or FALSE) when it is invoked with a set of parameters.

Logical connectives (operators) are introduced, they take one or more predicate and return another predicate. Predicates without connectives are known as simple predicates, while those with connectives are compound predicates. The logical operators have precendence rules.

The going got a bit easier with the discussion of truth tables.

Finishes with discussion of logical equivalence and rewrite rules, and I’m afraid the going gets hard again. A rewrite rule allows a proposition in one form to be replaced by another proposition such that is has the same truth values. Includes a discussion on De Morgans Laws.

This becomes mathematically challenging very quickly, and while this chapter felt very far removed from my life as a database professional, I’ve had a sneak peak at chapter 2 and that seems more familiar, so I’m going to carry on with thisl.

New Year Project – Applied Mathematics for Database Professionals

I have felt my blogging has been somewhat lacking in 2009. There are probably a couple of reasons for this, 2009 was a year where I think I slept less than any other year in my life, mostly due to my (then) one year old. Towards the middle of year there were a good few months, where I was barely touching anything Oracle related as I was working on a storage related project and learning as much about VMware as I could.

So I was racking my brains for an idea to get 2010 off to a decent start on the blogging front, and then my eyes alighted on Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars. This is a book I’ve had since it was published, around 18 months ago but judging by the suspiciously uncreased spine, you can tell I’ve not really read it in all that time.

I thought as a bit of a challenge for 2010, I would attempt to read the book and document how I was getting on with it here on the blog. I’d also use the blog as a bit of an aide-mémoire and summarise my understanding (or lack thereof) of what the book is saying.

First thing to say, and I think this is fair, this book is not an easy read. You can’t just pick it up and open to a random chapter and read it. The book does need the reader to put in the work. In particular the first 4 chapters are all on the mathematical theory that underpins the practical application to database design in later chapters, these chapters are not an easy read, but you can’t really skip them either as you then will not understand the later chapters. I suspect these 4 chapters probably have put off a lot of potential readers.

The book is split into 4 parts, one of which is appendixes.

Part 1: The Mathematics

This gives the mathematical underpinnings of the technique used for database design later in the book. There are chapters on logic, set theory, more logic and finally relations and functions.

Part 2: The Application

This is applying the mathematics to databases.

there are chapters on tables, database designs, state transition constraints data retrieval and database manipulation.

Part 3: The Implementation

This has chapters on database design in Oracle and then a summary and conclusion chapter.

Part 4: Appendixes

This includes the formal definition of an example database, symbols, bibliography, nulls and three valued logic.

Foreword

The foreword is written by Hugh Darwen and Chris Date. I think this reads quite oddly as it jumps pretty quickly into technical/academic debate. This foreword gives the impression that this is more of an academic textbook, than practical manual for the working DBA.

Introduction

The book states that it tries to fill a space that is not yet covered and show you how to use mathematics as a database professional. It states that no mathematical knowledge is presumed and that the set-theory (and logic) concepts will be delivered that are necessary to define a relational database from the ground upwards.

“The books main focus is on specifying a relational database design in general and specifying the data integrity constraints involved in such a design”

The methodology deployed in the book uses elementary set theory in conjunction with logic. This methodology detailed in the book was invented by Frans Remmen and Bert de Brock.

The concept of orthogonality is also introduced at this stage, essentially that each component of a system should be independent of each other and changing one component should not impact on the other components. They then state that SQL language that most Database Management Systems are based on are not like that and while based on ISO standards all vendors have veered from these in their products.

In the next blog posting I will detail how I get on reading part 1, the first four chapters on the mathematical underpinnings and attempt to summarise these chapters. I’d also be interested in hearing from other people who have read this book.

UKOUG 2009 – Wednesday

Not too late an evening for me on Tuesday, though had a really informative discussion with Bryn Llewellyn on Edition Based Redefinition. He made the point that if you have two versions of the application running, you have to be careful where you users are pointing to, if they have information in their sessions. You may need to have the control over which users connect to where.

This morning I found out I had managed to pack only 1 sock. I hope no one noticed me going sockless in a freezing cold December day in Birmingham.

The first talks of the morning started quite late, but I was quite glad of it. It was a tough choice between Larry Carpenter, Bryn, and Tanel Poder. In the end I went with Tanel.

Pratical Oracle Capacity Planning – Tanel Poder

Tanel was doing essentially 4 hours of presenting today, which is amazing in itself, but when you consider the detail of the information he provides it’s just staggering.

This was a 2 hour masterclass, and it was outstanding.

Tanel is making the point about if you have many different queries going on, on your system it’s very difficult to make a mathematical model to predict the response time using queuing theory. Much easier if you have 1 unique query – queuing theory is much more practical for this.

Data collection

statspack

awr – enable longer retention period see retention in dba_hist_wr_control

change retention with dbms_workload_repository.modify_snapshot_settings (retention => minutes)

services can be useful for finding out which application is consuming the resourcex, this can allow you to characterise your workload.

Can use V$service_stats to obtain statisics on the resources used by the various services since instance startup time.

v$servicemetric – basic metrics per service, including cpu per call, elapsed time per call, also in ASH.

Advising using dbms_application_info so you can obtain what resources each part of your application is consuming.

workload characterisation

performance/resource consumption analysis

basic utilisation forecasting

data visualisation

Tanel spent quite a large part on various examples of visualising data.

Tanel demoed his excel persheet, which allows you to automatically graph in excel various different metrics. Demo showing disk usage with a linear regression to predict futrue usage.

Tanel stating for cpu usage you might want to plot the usage at a particular time each week, as cpu flcutuates so much. You can then run linear regression on these times to make a prediction.

Gathering cpu usage data from V$osstat, and dba_hist_osstat.

Right at the start Tanel asked how many people were using queuing theory to do capacity planning. No hands went up, but I wonder how many people are doing any form of capacity planning. I found this to be a really illuminating presentation, and I’ll certainly be applying some of the ideas covered when I’m back in the office.

The lunch box was getting quite a lot of derisory comments. In particular, you could spot the Scottish person avoiding the fruit and heading straight for the KitKat. Though Pythians Paul Vallee was also rather disdainful of the fare on offer. In fact it was very interesting hearing some of the Pythian backstory. It’s an impressive thing Paul has built there (some 75 DBAs) and a fascinating story of how they have got to where they are.

The Oracle Wait Interface is useless (sometimes) – James Morle and Tanel Poder

Here they were showing that sometimes, there are problems where the Oracle wait interface can’t help. They emphasised this is the first place to look, but there are places that are not instrumented and there can be situations where Oracle itself can’t tell you what is going on, so you have to look outside the database.

Tanel showed pstack which can show which Oracle functions the process is using, while james was doing some demos with DTrace.

That was it from me, it’s been a lot shorter sojourn in Birmingham than the last few years, but it’s been great to see lots of familiar faces.

UKOUG 2009 – Tuesday

It always seems cold when it’s time for the UKOUG annual conference in Birmingham, and duly it turned out to be the coldest day of the winter so far. It seems somewhat eerily quiet at the conference today, but maybe that is to do with the ukoug holding multiple conferences throughout the year for the varios apps, unlike last year.

Tuesday was my first day this year, so with the conference being down to the 3 days, it’s going to feel especially brief.

Evaluating and Testing Storage Performance – Luca Canali

This was about how to do performance testing and ensuring the stability of new storage hardware. Luca seemed pretty excited that the LHC is finally coming back on stream, I guess it means floods of data to store.

When testing for new hw test for performance and stability often critical metric is small-read random IOPS

CERN have around 2000 disk drives and this gives them typically 1 disk drive failure per week. You have to design your systems to cope with failure.

using Oracle’s ORION for testing storage. Uses ASYNC i/O direct to storage (as ASM would do)

uses 8KB i/o sizes for random i/o

ORION does not use as much cpu as testing using oracle itself

graph showing fc and iscsi random iops fc scales linerally while the iscsi does not

also graph with sequential large i/o fc scaling well while the iscsi does not

this was 1 Gb ethernet

also tested 10 Gb (though the storage was “CERN made” rather than vendor puchased)

tested upto 42 drives. Finding ISCSI performance is variable from different linux versions

again have to test using real-world oracle workload

For capacity planning the view v$sysmetric_summary gives read/write I/O requests.

Oracle Advanced Compression in 11gR2 – Dan Morgan

Sees customers with databases in the 400-800 TB size.

Often as data volumes expand performance declines

compression can give a way out of that, and it is a trade off between cpu and disk i/o

Advanced Compression in 11g gives you:

dataguard network compression

data pump compression

rman compression

OLTP table compression

secure files 11gR2 deduplication (dan is using for storing email in DBFS

Interesting discussion about Hybrid Columnar Compression, it offers far greater compression ratios but is restricted to running on exadata storage. It appears this was pulled from the normal Enterprise Edition release at the very last minute. Dan was saying, it may be because the exadata boxes may have enough cpu to handle the compression but that having to do this on your server would be painful.

Still seemed like it may have been a bit of marketing decision.

11g for developers – Connor McDonald

This was an outstanding presentation. Connor managed to rattle through some 300+ slides many of which were extremely funny.

using a gapminder like anmiation to show database code v application code

querying from a blob/clob via sql

set errorlogging on

gives an audit trail of what has failed

Vital Statistics – Julian Dyke

In 11.1 there are 107 routines within the DBMS_STATS package

DBMS_STATS can collect in parallel, but it cannot do a validate structure.

Incremental stats gathering in 11g allows you to just collect statistics on new partitions will using the “synopsis” from older partitions that have not changed.

Compressing very large data sets – Luca canali

This was Luca’s second presentation of the day, and this was a superb one.

compressing data can mean less physical i/o, less logical i/o but will consume more cpu.

Can be useful for data that has an active part with older data made read-only

CERN tested an exadata machine for a couple weeks, particulary interested in the hybrid columnar compression (archive) . Interesting graphs showing the advantages of various levels of compression on various CERN datasets, and several levels of compression.

For basic and OLTP compression the format of the data block is the similar to a “normal” block but uses a symbol table. Luca showing some dumps of rows with compression enabled on them.

OLTP compression is not limited to direct load operations. Allows normal inserts into the table. Block is compressed when it reaches the pctfree vailue.

Hybrid columnar compression uses a completely new block layout. Utilises a compression unit (CU) and data from the same column within the compression unit is stored together. This basically increases how much compression is possible.

Doing DML on hybrid columnar compressed data, means a lock effects the whole CU.

Rows not identifiable in a block dump when compressed with hybrid columnar compresssion

index lookups use more consistent gets than with no compression.

Compression factors vary very much with the data. He seems to like both oltp and hybrid columnar compression.

Luca even mentioned the possibility of turning hybrid columnar compression on when using non-exadata storage, but emphasised that was just for playing with, not production usage.

Active Dataguard Best Practices – Larry Carpenter

This was another outstanding presentation.

apple iTunes replacing their logical standby infrastructure with an active dataguard install.

Control how much lag is acceptable to an application

have active dataguard fix corrupt blocks this works for corruptions on either primary or standby (though an application has to access the corrupt block)

need to use services to determine how your application connects to the standby/primary

note on running statspack on your standby (standby statspack) no64 454848.1

broker in 11.2 allows one command to turn on active dataguard, also when you switchover to an active dataguard instance, it will start active dataguard on the now old primary!

Query lag in 11.2

v$standby_event_histogram view

allows you to define an SLA for an application using session setting STANDBY_MAX_DATA_DELAY

use a logon trigger to set this for an application.

A good day of presentations.

After the last talk of the day, when I turned my mobile phone on, I got a message saying my hotel room had been cancelled at the copthorne, and another booked at jury’s inn. Now, I’d actually checked into the copthorne earlier, so I’m either going to end up with 2 rooms or none. If I do have to share my room at the copthorne, I do hope they don’t snore.

Increasing the Dataguard Protection Level

I thought I understood testing. Before I run anything in my production environment, I’m utterly strict that I test in a non-production environment first. Does not matter where that change comes from, it is always run into test first. This naturally includes any changes at the database level, rather than just inside a particular schema.

When I have a set of instructions or steps to take the database from one particular environment, or to install a particular feature, I don’t tend to test just 1/2 the steps, but generally, if I have a sequence of steps I tend to test the entire sequence.

Recently, I’ve been working on a project to increase the protection level of a dataguard environment from Maximum Performance to Maximum Availability. This is a 10gR2 environment, so I pull up the 10gR2 dataguard documentation. To me, the steps seem pretty clear. Let me highlight step 1:

Step 1 If you are upgrading the protection mode, perform this step.

Perform this step only if you are upgrading the protection mode (for example, from maximum performance to maximum availability mode). Otherwise, go to Step 3.

Assume this example is upgrading the Data Guard configuration from the maximum performance mode to the maximum availability mode. Shut down the primary database and restart it in mounted mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

It’s clear right? To upgrade the protection level, you have got to shutdown the instance and have it in the mount mode. I would not just run this in production, I’d always want to test these steps in my test infrastructure that was the same environment as my production setup. Question is, would anyone test upgrading the protection level, but just skip this step? Would it really occur to someone, oh, I wonder if I can just skip this first step and keep my instance up and running?

It did not occur to me, but then I read the (excellent) Oracle Data Guard 11g Handbook by Larry Carpenter, et. al. It’s pretty explicit that you don’t need to shutdown your instance!

The above was run on a 10.2.0.4 instance. I’d already set the log_archive_dest_n to a LGWR SYNC mode. One thing to note, you must explicitly set AFFIRM here as well, it’s not good enough just using LGWR SYNC, as NOAFFIRM is the default and this leads to the protection_level being in continual resynchronization.

Resynchronization occurs when you first increase the protection_mode or when there is a network outage. It means your configuration is effectively at that point running in maximum performance, and while the protection_level is not at maximum availability the potential exists for data loss.

This really does contradict the documentation so this has been a really useful find for me, as just following the documentation would have led to me having to take downtime on my RAC cluster. It has always been the case that you can drop the protection level without incurring downtime. Note to go all the way to MAXIMUM PROTECTION still requires the database to be in the mounted state.

Netbackup 6.5.4 and Oracle 11gR2

I’ve been involved in testing for upgrading a database to 11gR2 for a while, and I was quite intrigued to see this posting from Mark Bobak. This site uses Netbackup 6.5.4 and the Netbackup database agent to do the backups so this becomes a bit of a showstopper if I can’t backup the database. When you try and run a Netbackup backup against an 11gR2 instance you find the following happens:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x14] [PC:0x3BA6B70D00, strcpy()+16] [flags: 0x0, count: 1]
Errors in file /opt/oracle/diag/rdbms/test11g/TEST11g/trace/TEST11g_ora_2778.trc (incident=29067):
ORA-07445: exception encountered: core dump [strcpy()+16] [SIGSEGV] [ADDR:0x14] [PC:0x3BA6B70D00] [Address not mapped to object] []
Incident details in: /opt/oracle/diag/rdbms/test11g/TEST11g/incident/incdir_29067/TEST11g_ora_2778_i29067.trc

Searching in Metalink for this ORA-07445 error produces document 959015.1. This basically says that yep, the latest version of Netbackup is not compatible with 11gR2.

So do these guys get on the Beta programme and test their software?

Symantec have stated it will be might be fixed in 6.5.5 and this will have “first availability” from first week of November. However General Availability won’t be until second week of December.

Symantec stated it will be fixed from the next major Netbackup release which is scheduled for first week of February.
update
6.5.5 Did not contain any new Oracle agent, the agent to be used with 6.5.5 release was still the 6.5.4 agent. I’ve managed to get aboard the Symantec “First Availability” programme and hope to obtain 7.0 on 11th January 2010. Will update on whether 7.0 fixes the issue.