A few basic notes on Oracle Database Administration.

Wednesday, May 14, 2008

Northern California Oracle Users' Group Conference

If you are in Northern California you may be interested in the Spring Conference of the Northern California Oracle Users' Group. Among the speakers is Rich Niemiec. Here is the info, also available at the NoCOUG web site


SPRING CONFERENCE 2008

May 15, 2008

at the Crowne Plaza Hotel in Foster City, CA



See directions to the conference location near the bottom of this page.


If you will be coming, then please RSVP online now. Remember, conference attendance is free for NoCOUG members and $40 for non-members.

8:00 - 9:00 Registration and Continental Breakfast - Refreshments Served
9:00 - 9:30 General Session and Welcome - Roger Schrag, NoCOUG President
9:30 - 10:30 Keynote: How Oracle Came To Rule the Database World - Rich Niemiec, TUSC
10:30 - 11:00 Break
Columbus Room Drake I Drake II
11:00 - 12:00
Session 1
The Best Oracle Database 11g New Features by Rich Niemiec, TUSC Building the Technology Stack for Modern Applications by Caleb Small, Caleb.com, Camosun College Natural Data Clustering: Why Nested Loops Win So Often by Dan Tow, SingingSql.com
12:00 - 1:00 Lunch in Marco Polo Room
1:00 - 2:00
Session 2
Oracle Archiving Best Practices by Dave Moore, NEON Enterprise Software Poor Man's Auditing with Oracle LogMiner by Caleb Small, Caleb.com, Camosun College Getting SQL Right the First Try by Dan Tow, SingingSql.com
2:00 - 2:30 Break and Refreshments
2:30 - 3:30
Session 3
Oracle Database Security in a Nutshell by Daniel Liu, Oracle Corporation Web 2.0 Ajax-Based User Interfaces Development Made Simple by Shay Shmeltzer, Oracle Corporation RAC 11g, Virtualization and More... by Vijay Ragunathan, Guidewire
3:30 - 4:00 Raffle
4:00 - 5:00
Session 4
How Independent Software Companies are Leveraging Oracle Embedded Products in Their Software Applications by Gabe Stanek, Oracle Corporation Hacking and Defending Databases by Todd DeSantis, Sentrigo The Evolving Web UI by Avrom Roy-Faderman, Quovera
5:00 - ??? NoCOUG Networking and No Host Happy Hour in the conference hotel at the Club House Bistro

Mark your calendar for NoCOUG's Summer Conference:
August 21, 2008 at Chevron in San Ramon.


Speaker Abstracts for Spring Conference

Keynote
“How Oracle Came To Rule the Database World” - Rich Niemiec, TUSC


This is a break from the technical presentations, but not too much of a break. Sit back and listen to the history of the relational database go by. Find out the crucial moves that Oracle made at critical junctures of their history. See what drove the product from inception, over the rocky road and eventual to the top of the mountain. Learn what made Oracle the product a success, but also find out the attributes that made Oracle the company a font of technological wizardry. This talk will reveal several seldom heard facts and some unknown secrets of Oracle's success.

I. The Paper that started it all - E. F. Codd
II. System-R & Ingres
III. Oracle is Founded as SDL
IV. Version 1 to Version 10g
V. Why did Oracle win?
VI. Future market direction
VII. Summary

Columbus Room
“The Best Oracle Database 11g New Features” - Rich Niemiec, TUSC


This presentation will look at which 11g new features should be investigated for use. Most of the features that will be covered will be related to the DBA, but there will also be a few outside that realm. There will be simple examples to show the basic functionality of the following new features:
Memory Target
Partition Advisor
Security Enhancements
DDL Lock Timeout
The Invisible Index
Automatic Diagnostics Repository
SQL Plan Management
Real Application Testing (Workload Capture and Replay)
SQL Repair Advisor
ADDM Enhancements
Interval Partitioning
Optimizer Enhancements

“Oracle Archiving Best Practices” - Dave Moore, NEON Enterprise Software


Data volumes are growing ... Space is at a premium... Backups take forever ... Query response is dragging... Unused data can be moved out of the active database ...

The subject of data archiving is never addressed until the data retention problem is serious. Requirements for archiving range from legal obligations for data retention (Sarbanes Oxley, HIPAA, and so on), to operational performance, to internal business requirements. What data should you archive? How often? How should you do it? And what needs to be done inside of Oracle to regain operational efficiency? Whatever needs are driving your business archive initiatives, join Dave Moore to determine archiving best practices. This informative session will describe how you can meet your Oracle database archiving requirements while simultaneously optimizing database performance.

“Oracle Database Security in a Nutshell” - Daniel Liu, Oracle


In this Seminar, the students will learn how they can use Oracle database features to meet the security and compliance requirements of their organization. The current regulatory environment of the Sarbanes-Oxley Act, HIPPA, the UK Data Protection Act, and others requires better security at the database level. The session provides suggested architectures for common problems. It covers the following security features of the database: auditing, column and file encryption, virtual private database, label security, enterprise user security, and more.

“How Independent Software Companies are Leveraging Oracle Embedded Products in Their Software Applications” - Gabe Stanek, Oracle


The market for embedded database software continues to experience significant growth, based on a highly diverse range of use cases, including consumer and mobile devices, desktop and enterprise software, large-scale networking and storage equipment and appliances. Oracle offers the industry's broadest portfolio of world-class embeddable database products that ranges from: -- Oracle TimesTen - for real-time, in-memory relational data management and caching; -- Oracle Berkeley DB - for high performance, non-relational data management; and -- Oracle Database Lite - for online/offline mobile data management.

Requiring virtually no human administration, these products are ideal for developers in industries such as telecommunications and high technology, which have demanding requirements for intelligent edge devices and services.

"The increase in customer adoption for Oracle's embedded databases is a testament to our commitment to embedded developers building the next generation of applications that need to run in always-on, zero administration environments," said Rex Wang, vice president Embedded Systems Marketing, Oracle. "Our goal is to offer developers a broad range of choices, so they can pick the right solution for their specific application. We continue to innovate and integrate our products, so that our customers can avoid the time, cost and risk of building it themselves."

Organizations spanning multiple industries are lauding the one-stop support, lower TCO, and comprehensive, unattended operation as key benefits of Oracle's Embedded product lines. One of those is to be determined (would like to involve one of our customers from the local area, but I need to confirm).

Drake I
“Building the Technology Stack for Modern Applications” - Caleb Small, Caleb.com, Camosun College


Overview of building clustered Oracle Application Servers, clustered RAC Database Servers, clustered NetApp Storage Arrays, and clustered Load Balancers to deploy java or forms & reports applications in a high availability environment.

“Poor Man's Auditing with Oracle LogMiner” - Caleb Small, Caleb.com, Camosun College


The need for database auditing is a topic that most DBAs have heard loud and clear. Oracle offers many auditing solutions, and the specific requirements of each individual application must be assessed before choosing. One of the simplest, yet most powerful solutions is the time tested LogMiner. Oracle LogMiner allows reconstruction of past SQL statements from the on-line and archived redo logs, provided that some simple configuration requirements are met. While there is some additional overhead imposed on the database, for the most part all the necessary mechanisms are already in place and no additional audit trails or log files need to be maintained. This presentation steps through the decision criteria for selecting this option, and the actual database implementation steps based on an actual production system.

“Web 2.0 Ajax-Based User Interfaces Development Made Simple” - Shay Shmeltzer, Oracle


The Web 2.0 generation of applications has brought a new user experience to Web-based applications. Ajax-based UIs are much more dynamic and offer better usability. This session explains how the new Oracle Application Development Framework (Oracle ADF) Faces rich-client components drastically simplify the development of such advanced UIs. Learn about the new components, how they work, and how they can influence the design of your user interface.

“Hacking and Defending Databases” - Todd DeSantis, Sentrigo


"Presenting a new angle on a popular attack vector on databases: SQL Injection." We will describe types and techniques of SQL Injection attacks on both Oracle-based web applications and built-in database stored program units, and show how simple SQL Injection can be used to own the database server through the means of privilege escalation.------ We will also list ways of preventing SQL Injection attacks - ranging from secure coding practices to various external tools that will alert and prevent SQL Injection attempts, and demonstrate how hacker techniques of evasion can be used to subvert them. Finally, we will introduce new deep inspection tools for Oracle 9i/10g that can prevent SQL injection, even in zero-day scenarios.
Take away points:

  • How SQL Injection attacks work
  • Secure coding practices
  • Existing tools for SQL Injection prevention and techniques to evade them
  • New resilient technologies used to solve entirely SQL injections, even those exploiting zero-day vulnerabilities
Drake II
“Natural Data Clustering: Why Nested Loops Win So Often” - Dan Tow, SingingSql.com


I'll present a mix of empirical data and theoretical explanation to demonstrate why optimizers tend to favor hash joins, while experienced SQL tuners so often find they can do better by forcing nested-loops joins. The key is co-clustering - the tendency of well-clustered rows in one table to join to well-clustered rows in another table, a tendency that favors nested-loops joins.

“Getting SQL Right the First Try” - Dan Tow, SingingSql.com


Most SQL tuning material addresses the question of how to fix performance of slow SQL. However, there are little-known rules that enable developers to avoid most tuning problems proactively, while also avoiding common functional mistakes and writing SQL that is far easier to understand and to maintain when it does need later work. This presentation proposes rules and processes to write correct and fast SQL from the beginning.

“RAC 11g, Virtualization and More...” - Vijay Ragunathan, Guidewire


Virtualization software like VMware and Xen are changing the commodity hardware world. One of the benefits of this virtualization software is to be able to run Oracle RAC with more than one node with limited cost for development and testing environments. We will talk about how to set up RAC 11g with this software. In addition, we will also talk about some 11g features related to RAC, ASM, and AWR/ADDM and cache fusion tracing.

“The Evolving Web UI” - Avrom Roy-Faderman, Quovera


Applications on the web have come full circle--from highly interactive applets, through request/response-cycle based JSP applications, through richer applications through asynchronous server calls, back to highly interactive Web 2.0 applications. This talk covers the current state of the art in web applications, with a focus on developing these applications in Oracle JDeveloper 11g.


If you have suggestions for future meetings or would like to offer feedback on previous conferences, then please complete our online survey or send us an email.

Directions to the Crowne Plaza Hotel in Foster City:

Address:
1221 Chess Drive, Foster City, CA 94404 (888) 444-0401

From Highway 92 East:
Take exit 14B toward Foster City Blvd (0.2 mi) Turn left at Metro Center Blvd (495 ft) Turn left at Foster City Blvd (0.2 mi) Turn left at Chess Dr (285 ft) To: 1221 Chess Drive, Foster City, CA 94404

From Highway 92 West:
Take exit 14B toward E Hillsdale Blvd/Foster City Blvd (0.3 mi) To: 1221 Chess Drive, Foster City, CA 94404

Map

View Larger Map

Copyright © 2008 NoCOUG. All rights reserved.

Friday, May 2, 2008

Moving back to a lost spfile

We had an unexpected outage last saturday morning, and the person who restarted the database inadvertantly did so with an old pfile instead of the spfile - which was lost (not because of the restart). How to get back to the correct set of parameters. EM to the rescue. Looking at all the parameters in EM it shows which have been changed and when. I could then alter the parameters back to where they should be. The dynamic parameters were easy - i.e.


ALTER SYSTEM SET parallel_max_servers = 280;

I then created an spfile from the pfile with only the dynamic parameters corrected.

create pfile from spfile;

But I was looking at an out of date article which put the had an error about the default location of the spfile.

SELECT name, value FROM v$parameter WHERE name = 'spfile';

gave me the actual location of my spfile, which being in the default location was in
$ORACLE_HOME/dbs/spfileSID.ora



Then bounced the database and brought it up with the spfile.

But, of course, the static parameters need a bounce to the database.

I altered the parameters scope = spfile eg:
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;

and then rebounced the database to apply the dynamic changes also to the current instance.

THEN I BACKED UP THE SPFILE!




(now if only I could control the fonts on this blog, all would be well.)