October 2012

Database Specialists

The Specialist

Vol. 12 No. 3

Database News You Can Use

Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 · Toll-Free: 1-888-648-0500
In This Issue
The Myth of Database-Independent development
Implementing Enterprise User Security
Deferred Segment Creation
Using Large Memory Pages with v11.2
RMAN made easy
Intro to Regression Analysis
Quick Links
Join Our List
Join Our Mailing List
October, 2012
Welcome to the October, 2012 issue of the Database Specialists newsletter!
In this issue of The Specialist, we’ve gathered some great points from our staff consultants Ian Jones, Gary Sadler, Mike Dean, and Jay Stanley regarding a variety of Oracle-related topics.
As always, we hope that you enjoy this issue of The Specialist!

The Myth of Database-Independent Development

Jay Stanley, Sr. Staff Consultant

Keeping Current with Oracle Releases

The job of an Oracle DBA or developer requires that you never stop learning new things. Each version of Oracle has historically had quite a few new features and changes from previous versions, and the number of new things to learn as each version comes out has only increased with time. When you really consider how big the Oracle RDBMS has become, it’s frankly rather staggering.

One way to try to keep up, besides reading the ~10,000 pages of documentation and playing with the database, is to review books written by authors who have gained the respect over time of the Oracle user community.

 

For this reason, I recently picked up Tom Kyte’s Expert Oracle Database Architecture, 2nd Edition, even though I’ve been very actively working with Oracle for about 17 years. As many of you are aware, Tom Kyte is behind the very helpful Ask Tom Oracle website at http://asktom.oracle.com/ – I’ve long had a very high respect for his evidence- and test-based approach to understanding Oracle, and in my opinion there are very few people who match or exceed his expertise. I would very highly recommend this book for anyone working with Oracle databases. Whether you’re a beginner or an expert; you will certainly save effort and anguish over the long run.

 

In the book, he very convincingly claims that today it is nearly impossible to do database-independent development.

 

The allure of doing development that can be considered database-independent is that you can start on a small/cheap database, and as your requirements grow, you can move up to the big, expensive databases like Oracle.

SQL standards are not used

The first thing I thought was Are you serious? SQL has been an ANSI standard for decades!

Not so fast, according to Tom:

  • ANSI standards for databases were ratified in 1989, 1992, 1999, and 2008; and they’re different,
  • Just because a database says that they are ‘ANSI compliant’ does not mean that they support 100% of the standard (as he says, “not by a long shot”)
  • The standard itself is full of ambiguities, backward compatibility issues and more

Looking at the SQL92 standard, which many databases claim to follow:

  • There are 4 levels; Entry level, Transitional level, Intermediate level, and Full.
  • No database vendors have been certified higher” than the Entry-level!
  • In fact, the National Institiute of Standards and Technology (NIST) who manages SQL certification does not even certify any more – apparently since 1993
  • An entry-level database for SQL92 “has a feature set that is a subset of Oracle 7.0’s capabilities”
  • The entry-level standard doesn’t even include outer joins, the new inner join syntax, and a whole lot of common features in-use today
  • For the newer SQL99 standard which has only the Core and Enhanced levels of conformance, “I know of no vendor who is even claiming his product is fully compliant with either level of conformance”.

So – the standards are a mess, but aren’t in fact modern relational databases similar?

As soon as you begin to develop a real-world application, modern relational databases from different vendors are not at all similar.

Tom goes on to prove that there are many, many very fundamental differences between different modern relational databases. These differences include the jaw-dropping facts that:

  • nulls are treated differently when comparing Oracle to Microsoft SQL Server; for example, a NULL=NULL in Microsoft SQL server, and this is not true in Oracle,
  • transactional read consistency is handled very, very differently between Oracle & MS SQL Server,
  • most databases have completely different ways of generating and handling sequences,
  • most databases have very different ways of overcoming concurrency issues, and concurrency issues are frequently encountered when migrating between databases,
  • modern relational databases have very different syntactic differences, even when just using SQL
  • the differences in transaction isolation levels and concurrency controls between vendors make them in the real world very different pieces of software

The difficulty of understanding the complexity of any modern RDBMS is very great

Tom continues “finding a single developer (let alone a team of developers) who is savvy enough to understand the nuances of the differences between Oracle, SQL Server, and DB2 (…) is virtually impossible.”

Tom has worked with Oracle for the last 16 years, and he (as I do) learns something new about Oracle every single day. The rate of change for any modern database is far higher than any single person can handle, much less keeping up-to-date for multiple databases.

I don’t care; I’m going to develop a database-independent application; how should I do that?

First, understand that it will be very expensive, both in development and testing resources, to succeed. Tom recommends that all database access be encapsulated in an API that the application can use. These APIs within each database should fully use the features of each database in order to work correctly and be performant.

Conclusion

  • I’d highly recommend Tom Kyte’s Expert Oracle Database Architecture, 2nd Edition – even if you are an expert on Oracle,
  • Modern relational databases are so different on very fundamental levels that it’s impossible to easily develop a database-independent application,
  • If you must, use APIs to hide how each database works.

Implementing Enterprise User Security (abstract)

Mike Dean, Sr Staff Consultant

 

Managing database user accounts in a large enterprise can be very challenging. Creating individual accounts for every person in every database can be very time-consuming for DBAs and managing different passwords in every database is time-consuming for both DBAs and users. When people leave the enterprise, it is critical to remove their access immediately but this can be difficult if you have many databases. However, the cost of not properly managing these accounts is that your databases are more vulnerable to security breaches. Oracle offers a solution to this in the form of Enterprise User Security (EUS) which is part of the Oracle Identity Management product. With EUS, user accounts are created and managed in an LDAP repository called Oracle Internet Directory (OID). OID is responsible for authenticating the users as well as managing the access rights of each user.

In this paper, I will give step-by-step instructions on how to install the software components required to run Oracle Identity Management and also how to implement Enterprise User Security to handle a typical real-world scenario in which different people need different levels of access to a number of databases.

 

You can find this paper at: http://dbspecialists.wpengine.com/presentations.html#implementing_oracle_11g_enterprise_user_security

Deferred Segment Creation
Ian Jones, Sr. Staff Consultant

Recently I have come across a few problems with a new 11gR2 feature known as ‘deferred segment creation’ that are worth sharing. Deferred Segment Creation is a new feature added to 11gR2 that cleverly avoids creating an empty segment until it is actually used. The documentation explains that this feature, which is the default in 11gR2, (potentially) saves on disk space and allows quicker installations. Certainly some large third party products (Peoplesoft HR springs to mind) create lots of tables many of which may not be required by a particular client, so by deferring the creation of the segments until they are actually used the database can certainly save space and installation time.

 

However, deferring the creation of the segment until it is used can cause issues; here are three that I have encountered recently.

 

1.  It is now possible to get quota errors at DML time that would usually be generated at create (DDL) time. I’ll illustrate this below but essentially instead of getting errors during a ‘build deployment’ they happen later when the users are back on the system.

 

2.  In 11.2.0.1 conventional export (i.e. ‘exp’ not ‘expdp’) silently skips these tables, this is a bug that is fixed in 11.2.0.2 but is something to be aware of in the base release. Conventional export is “desupported for general use” in 11g so this may not affect many people these days although clearly bugs are still getting fixed.

 

3.  It is now possible to drop tablespaces that *will* contain segments. This seems to affect all versions of 11g currently.

 

If you encounter these (or other) issues with this feature you can disable it for the database by setting the value of DEFERRED_SEGMENT_CREATION=FALSE in the init.ora (spfile) or by using the ‘SEGMENT CREATION IMMEDIATE’ clause during table / index creation.

 

Here is some sample code to illustrate issues 1 & 3.

– Create two tablespaces
CREATE TABLESPACE DSC_TABLES DATAFILE … size 1024M
CREATE TABLESPACE DSC_INDEXES DATAFILE … size 1024M
(in the above obviously customize the paths depending upon your environment)

 

– Now create a user but fail to properly assign the quota on the DSC_INDEXES tablespace
CREATE USER DSC IDENTIFIED BY dsc DEFAULT TABLESPACE DSC_TABLES TEMPORARY TABLESPACE TEMP
GRANT RESOURCE, CONNECT TO DSC
REVOKE UNLIMITED TABLESPACE FROM DSC
ALTER USER DSC QUOTA UNLIMITED ON DSC_TABLES
REM ALTER USER DSC QUOTA UNLIMITED ON DSC_INDEXES

 

-Now as user DSC create our table
create table test ( test_id integer not null, test_text varchar2(400),
constraint test_pk primary key (test_id) using index tablespace dsc_indexes);

at this point we have a table but no segment

 

SQL> select table_name from user_tables where table_name = ‘TEST’;

returns a row but

SQL> select segment_name from user_segments where segment_name = ‘TEST’

does not.

 

When we attempt to insert a row

 

SQL> insert into dsc.test values (1,’test’)

 

Oracle realizes that the quotas are not right generating the following error “ORA-01950: no privileges on tablespace ‘DSC_INDEXES'”. Note, that you get this error even if the user running the insert has a quota on the DSC_INDEXES tablespace – even SYS gets the error – because the error relates back to the segment owner rather than the user attempting the insert.

Of course, you can say “well your code was screwed up” and that is true but wouldn’t it be safer to find that out during a deployment or build rather than when people start using the system?

 

3.  Here is how to drop a tablespace that *will* contain the segment, basically the same as above except this time we can correctly assign the quota (it doesn’t matter).

– Create two tablespaces
CREATE TABLESPACE DSC_TABLES DATAFILE … size 1024M
CREATE TABLESPACE DSC_INDEXES DATAFILE … size 1024M
(in the above obviously customize the paths depending upon your environment)

– Now create a user and assign tablespace quotas
CREATE USER DSC IDENTIFIED BY dsc DEFAULT TABLESPACE DSC_TABLES TEMPORARY TABLESPACE TEMP
GRANT RESOURCE, CONNECT TO DSC
REVOKE UNLIMITED TABLESPACE FROM DSC
ALTER USER DSC QUOTA UNLIMITED ON DSC_TABLES
ALTER USER DSC QUOTA UNLIMITED ON DSC_INDEXES

 

-Now as user DSC create our table
create table test ( test_id integer not null, test_text varchar2(400),
constraint test_pk primary key (test_id) using index tablespace dsc_indexes);

Same as before, at this point we have a table with no segments. However, we can now drop both the tablespaces

SQL> drop tablespace DSC_TABLES;
SQL> drop tablespace DSC_INDEXES;

note these commands would error out if these tablespaces actually contained segments but because they do not yet they happily go away. Attempts to insert a row into the test table now generate the following error

SQL> INSERT INTO DSC.TEST VALUES (1,’THIS IS A TEST’)

ORA-00959: tablespace ‘DSC_TABLES’ does not exist

 

and of course this makes sense – the tablespace doesn’t exist – but as with the previous example, it may not be initially obvious what is causing the error. Unfortunately attempts to move the table to a now existing tablespace also throw the same error, although you can re-create the missing tablespace to fix it.

Using Large Memory Pages with Oracle 11.2

Gary Sadler, Sr. Staff Consultant

 

A recent late-night incident at a client site involved a database with extreme performance problems following a storage system failure and subsequent database restart. The client observed excessive swapping and paging with the run queue length breaking triple digits and the server and database becoming virtually unresponsive.  The server had not been restarted and the database instance parameters were confirmed to be identical to the settings prior to the database bounce. The SGA was on the order of 50 GB with the buffer cache consuming most of that. After a period of speculation about what might be causing the problem we noticed that the huge pages usage didn’t look quite right.  The operating system was RHEL 5.4.

 

$ cat /proc/meminfo | grep Huge

HugePages_Total: 25600

HugePages_Free:  25431

HugePages_Rsvd:    169

Hugepagesize:     2048 kB

 

With a running database featuring a 50 GB SGA there should be more huge pages reserved.  They won’t all get reserved upon startup but more than 169 should have been reserved at this point for sure.  As of 11.2.0.2 there is a new instance parameter, USE_LARGE_PAGES, which can be set to one of the following three values:

 

TRUE  – Use huge pages if they’re available (default). Some combination of large and small pages for the SGA is okay.

FALSE – Don’t use huge pages even if they’re available

ONLY  – Allocation of huge pages for the entire SGA is required for the database to start

 

Since the client database above was 11.2.0.3 if we had set USE_LARGE_PAGES=only then when we had tried to bounce the database it wouldn’t have started and we would have seen something like this in the alert log:

 

Fri Apr 20 15:16:11 2012

Starting ORACLE instance (normal)

****************** Large Pages Information *****************

Parameter use_large_pages = only

 

Large Pages unused system wide = 25431 (26041344 KB) (alloc incr 4096 KB)

Large Pages configured system wide = 25600 (51200 MB)

Large Page size = 2048 KB

 

ERROR:

Failed to allocate shared global region with large pages, unix errno = 12.

Aborting Instance startup.

ORA-27137: unable to allocate Large Pages to create a shared memory segment

 

ACTION:

Total Shared Global Region size is 51200 MB. Increase the number of

unused large pages to atleast 25600 (51200 MB) to allocate 100% Shared Global

Region with Large Pages.

***********************************************************

 

This would have flagged the problem immediately.  We would have noticed that there were shared memory segments stuck in memory that couldn’t be reallocated prompting a server reboot, which incidentally did solve the problem.

 

For questions about huge pages in general and how to configure them see My Oracle Support doc ID 361468.1 or give us a call.  It is important to note that use of huge pages is not compatible with Automatic Memory Management (AMM) configured through the instance parameters MEMORY_TARGET and MEMORY_MAX_TARGET.  You can use the legacy Automatic Shared Memory Management (ASMM) configured through the instance parameters SGA_TARGET, SGA_MAX_SIZE, and PGA_AGGREGATE_TARGET, however.

RMAN on LInux made easy with our downloadable scripts

Gary Sadler, Sr. Staff Consultant

 

RMAN is the obvious choice for performing hot backups and managing the flash recovery area but the plethora of options go well beyond a basic “backup database” command. Rather than starting from scratch with scripting, adding options one at a time, and going through several cycles of needless debugging, why not use our Linux bash script and accompanying cross-platform RMAN command file complete with error handling and comments.
The RMAN command file works like a template, making it easy to activate the options you want and remove the ones you don’t. The embedded comments help to clarify your backup strategy and serve as a training tool. If you’re not totally comfortable with RMAN yet, these scripts will get you there in a jiffy.

Introduction to Statistical Strraight-line Regression Analysis using Oracle SQL
Jay Stanley, Sr. Staff Consultant

Often relational databases are used to understand relationships about the data itself. Put simply, regression analysis involves looking at set of (x,y) samples, and estimating the relationships between the variables. This addresses questions like “as X increases in value, does Y increase in value, and if so by how much?”.

In this paper, Jay Stanley reviews how to use a set of very powerful features within the Oracle database to get statistically valid conclusions about the data. Using the REGR_INTERCEPT() and REGR_R2() functions in Oracle SQL, Jay shows how they work with all editions of Oracle from v10 or greater, and can provide some solid statistically relevant results.

To view this paper, check out http://dbspecialists.wpengine.com/presentations.html#intro_regression_analysis

 

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48
This email was sent to dwolff@dbspecialists.com by dwolff@dbspecialists.com |
Database Specialists, Inc. | 580 California Street | Suite 500 | San Francisco | CA | 94104

Leave a Reply

Your email address will not be published. Required fields are marked *