January 2010

January 2010

THE LIGHTER SIDE
Hi Koo To You Too

A  haiku is originally a short Japanese poem of three lines with five, seven, and five syllables respectively. A good haiku creates a vivid picture in the reader’s mind. Here is an English haiku by Richard Wright.

Whitecaps on the bay:
A broken signboard banging
In the April wind.
Paul Drake from the New York Oracle Users Group imagined the Oracle database giving us haikus instead of error messages.
Instead of “ORA-00904: invalid column name,” we might get:
The column you seek
In this schema exists not
Library too new.
Instead of “ORA-01034: Oracle not available,” we might get:
The database is
Within itself currently
A patch, cold backup?
Here are some more of Paul Drake’s Oracle haikus. A longer list was published in the December 2001 newsletter of the New York Oracle Users Group.
Enjoy!

STUMP THE SPECIALISTS
Making Your Database Secure

This month’s question came to us from a friend:

“What should I do to make our databases more secure?”

Senior Staff Consultant Iggy Fernandez thoughtfully responds:

  1. Install only what is required.
  2. Lock and expire default user accounts.
  3. Change default user passwords.
    • Change default passwords of administrative users.
    • Change default passwords of all users.
    • Enforce password management.
  4. Enable data dictionary protection.
  5. Practice the principle of least privilege.
    • Grant necessary privileges only.
    • Revoke unnecessary privileges from the PUBLIC user group.
    • Grant a role to users only if they need all privileges of the role.
    • Restrict permissions on run-time facilities.
  6. Enforce access controls effectively and authenticate clients stringently.
  7. Restrict operating system access.
  8. Restrict network access.
    • Use a firewall.
    • Never poke a hole through a firewall.
    • Protect the Oracle listener.
    • Monitor who accesses your systems.
    • Check network IP addresses.
    • Encrypt network traffic.
    • Harden the operating system.
  9. Apply all security patches and workarounds.
  10. Contact Oracle Security Products if you come across a vulnerability in Oracle.
A more detailed security checklist can be found in the Oracle Database 11g Security Guide. Also, Oracle Enterprise Manager ships with a policy library which covers database security among other things. Policy violations can be viewed in Database Control and Grid Control.
I hope this short answer helps you. Best of luck.

SQL CORNER
Fancy SQL Tricks Part I
Pivoting is well known to Excel power users; it converts rows of data into a two-dimensional matrix. For example, here are some rows of data from the STATS$SYSSTAT table:
Snap Id
Name
Value
41566
physical reads
57,151,567
41566
session logical reads
4,272,750,011
41566
user commits
1,006,064
41566
user rollbacks
54,483
41576
physical reads
61,038,074
41576
session logical reads
4,711,935,207
41576
user commits
1,082,359
41576
user rollbacks
58,524
41586
physical reads
65,144,663
41586
session logical reads
5,158,057,003
41586
user commits
1,159,213
41586
user rollbacks
62,607
You may want to convert the above data into the following two-dimensional format:
Snap ID
Logical Reads
Physical Reads
Transactions
41566
4,272,750,011
57,151,567
1,060,547
41576
4,711,935,207
61,038,074
1,140,883
41586
5,158,057,003
65,144,663
1,221,820
You can do this with the PIVOT operator, introduced in Oracle Database 11gR1, as follows:

SELECT
 snap_id,
 logical_reads AS logical_reads,
 physical_reads AS physical_reads,
 user_rollbacks + user_commits AS transactions
FROM
 (
    SELECT *
    FROM sysstat
 )
 PIVOT
 (
    SUM(value)
    FOR NAME IN
    (
      ‘session logical reads’ AS logical_reads,
      ‘physical reads’ AS physical_reads,
      ‘user rollbacks’ AS user_rollbacks,
      ‘user commits’ AS user_commits
    )
 );

Here is the Oracle Database 10g version of the above code. It requires a very unintuitive use of the DECODE function.

SELECT
 snap_id,
 sum(decode(name,’session logical reads’,value,0))
   AS logical_reads,
 sum(decode(name,’physical reads’,value,0))
   AS physical_reads,
 sum(decode(name,’user rollbacks’,value,’user commits’,value,0))
   AS transactions
FROM sysstat
GROUP BY snap_id;

More information on the PIVOT operator and its sister operator, UNPIVOT, can be found in article by Arup Nanda.

   

BULLETIN BOARD
The Oracle conference season is in full swing.
RMOUG Training Days: February 16-18 in Denver, Colorado
Collaborate10: April 18-22 in Las Vegas, Nevada
Hotsos Symposium 2010: March 7-11 in Dallas, Texas

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

Leave a Reply

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