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.

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.)

Semafore? Cannot mount in Exclusive.

As Chen says, the challenges always come after 5PM.

I had a Daylight Savings Patch to apply to 2 databases. I think that someone else, was blogging about installing this just recently. The patch is trivial - bring down the database, replace 2 files deep within ORACLE_HOME and bring the database back up. The first install went went off uneventfully, so I figured that I had an idea what I was doing. Then I moved to the second DB and the fun started. The DB wouldn't restart. How can I NOT startup a database? What can be simpler than a STARTUP?

ORA-01102:  cannot mount database in EXCLUSIVE mode

Now that I have a solution it all seems simple. 

The problem  according to Metalink...

- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs"
 directory
nope

- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist
no, they are gone..

- shared memory segments and semaphores still exist even though the
 database has been shutdown

I got to learn about the unix command ipcs -b, but
nothing owned by oracle, 
T        ID     KEY        MODE       OWNER    GROUP     SEGSZ
Shared Memory:
m   1048576 0x7800000a --rw-rw-rw-     root   system  16777216
m   1048577 0x0d001213 --rw-rw----     root   system      1440
m         3 0xffffffff --rw-rw----     root   system      4096
T        ID     KEY        MODE       OWNER    GROUP NSEMS
Semaphores:
s   3145728 0x010000af --ra-------     root   system     1
s         1 0x6200105e --ra-r--r--     root   system     1

- there is a "ORACLE_HOME/dbs/lk" file

and indeed
sculkget: failed to lock /home/oracle/orabase/product/10.2.0/dbs/lkSID exclusive
sculkget: lock held by PID: 299506

though I have yet to figure out what a sculkget is I did find a file that was 
skulking around and didn't belong there.  

With trepidation I killed the process holding the lock file

kill -9 299506

and removed the lock file

rm /home/oracle/orabase/product/10.2.0/dbs/lkSID 

and magic!  no more lock, Database starts up normally! 

Locks in their place and all is well with the world.

Meanwhile, if anyone else knows what category of animal a 
sculkget: message is - I would love to know. There is a singular lack of comment
about it on the web, except as part of this specific problem. 

Exposed Password in Data Pump

I was just looking at a production Data Pump today with a text exposed 'system/password'. I was startled.

Then I remembered that in the last shop Data Pumps involved

1. Temporarly change the system user password
2. Run the Data Pump with the temporary password in plain text
3. Changing the password back.
   

Now that I think of it, the one involving all that changing is only slightly better than what I saw today.

Does anyone have a suggestion for running Data Pump with a modicum of security? I had no say in the last shop. Here I may not prevail, but at least I'm in a position to make a suggestion if I have one.

Does the solution involve something with a password protected parameter file? Or? Thanks in advance for any suggestions offered.

When all else fails, follow instructions.

1. Today I tried to open my test database and got:

ORA-16038: log 3 sequence# 729 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'C:\ORACLE\ORA10\ORADATA\DB10\REDO03.LOG'

Researching the problem I found:

• Maybe your Oracle instance has reached the maximum db recovery file destination file size assigned to it? If that is so, probably your oracle instance will stop working and you will get the 3 error messages listed above while trying to start it.
• The relevant parameter is: DB_RECOVERY_FILE_DEST_SIZE - specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the flash recovery area.
• It works in concert with DB_RECOVERY_FILE_DEST which specifies the default location for the flash recovery area. The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

1. 
   
2. What is the maximum db recovery file destination file size assigned in my DB?

SQL> show parameter db_recovery_file_dest_size
db_recovery_file_dest_size 2G

At least this wasn't a surprise.

2. 
   

3. I removed excess files

I shutdown the database. I then removed the archivelog files and the flashback files from the file system – even though they didn't amount to the 2gb in total that the system is complaining about. (This is a play database so I can do things like kill these files.)

4. Still had a problem.

I brought the database up to mount state, but immediately there was a trace file message:

ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.85% used, and has 3320320 remaining bytes available.

*** 2008-03-03 10:03:19.635

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

You have following choices to free up space from flash recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.

5. The files were still being seen by RMAN! When all else fails, follow instructions, in this case #4 above. “4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.”

RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK
validation failed for archived log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_733_3WRQ14DY_.ARC recid=70 stamp=648389669

validation failed for archived log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_734_3WRQ4V7R_.ARC recid=71 stamp=648389787

What nice list of all the files that aren't there, and all the locations where they aren't; 68 files that don't exist! [apologies to any reader of English as a second language]

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
70 1 733 X 02-MAR-08 C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2
008_03_03\O1_MF_1_733_3WRQ14DY_.ARC

71 1 734 X 03-MAR-08 C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2
008_03_03\O1_MF_1_734_3WRQ4V7R_.ARC

(etc)

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archive log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_733_3WRQ14DY_.ARC recid=70 stamp=648389669

deleted archive log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_734_3WRQ4V7R_.ARC recid=71 stamp=648389787

(etc)

Deleted 68 EXPIRED objects

5. 
   

6. Now all is well. (with the database if not with this blog-entry's numbers). I bounced the database and the alert log shows:
   

db_recovery_file_dest_size of 2048 MB is 0.38% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.

Trying to lose a datafile.

Chen Shapira wrote me a suggestion regarding losing a data file.

“Try moving a data file while the DB is up and running, and try to make Oracle "notice"
that it is gone.  On Linux, Oracle does not notice anything! You can move a datafile
and all selects, updates, inserts, will work. The OS prevents the DB from seeing
the change. But I noticed that your test system is on windows, so I'm curious
whether it is the same.”

Once my company’s  operator TARed the rest of the database into the system tablespace datafile.
Oracle ‘noticed’!  Are you sure that you really lost the file? Was there some sort of mirroring
of the file?

Anyway, here is what happened when I tried to lose a data file while the DB is up on a Windows based DB…

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\SYSTEM01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\UNDOTBS01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\SYSAUX01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF users.dbf
The process cannot access the file because it is being used by another process.

SQL> host del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF

The process cannot access the file because it is being used by another process.

SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF example02.dbf
The process cannot access the file because it is being used by another process.

Also a straight O/S effort:
C:\Windows\System32>del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF

C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
The process cannot access the file because it is being used by another process.

Moving files in Mount State

On Feb 3, I posted “Startup steps review” . Chen Shapira commented

And just for completeness -
You can move data files when in "alter database mount"
Recovery happens on "alter database open"

Which prompts me to try it out. I hadn’t thought about this. Here is how I worked through Chen’s information about moving data files while in the mount stage in my toy database.

I start off with the datafile EXAMPLE01.DBF

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

I 'lose' the file - by shutting down the database, renaming the file in the OS and
bringing the database back to mount.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host ren c:\Oracle\ora10\oradata\db10\EXAMPLE01.DBF EXAMPLE01_X.DBF

SQL> host dir c:\Oracle\ora10\oradata\db10\E*

Directory of c:\Oracle\ora10\oradata\db10

02/11/2008 11:36 AM 104,865,792 EXAMPLE01_X.DBF

1 File(s) 104,865,792 bytes

SQL> startup mount

ORACLE instance started.

Total System Global Area 201326592 bytes
Fixed Size 1289700 bytes
Variable Size 150995484 bytes
Database Buffers 46137344 bytes
Redo Buffers 2904064 bytes
Database mounted.

I was surprised to see, that in the mount state Oracle takes the file names from the control file, but doesn’t check the operating system to see if they really exist.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

It is on the open that Oracle checks reality.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF'

And as Chen says – you can move files when in mount phase - so I correct the problem that I made when I 'lost' the file:

SQL> alter database rename file 'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF' to 'c:\Oracle\ora10\oradata\db10\EXAMPLE01_X.DBF';

Database altered.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF

And just to look at everything since I am demonstrating:

SQL> alter database backup controlfile to trace;
Database altered.

Now the control file knows where the lost file is. The abbreviated contents of the trace are:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB10" NORESETLOGS NOARCHIVELOG
...
LOGFILE
......

DATAFILE
...
'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF'

Now I can open the database and all is well:

SQL> alter database open;

Database altered.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF