Girlgeek's Oracle Blog

Composite Interval Partitioning isn't as advertised.

2010-05-26T16:37:00.000-07:00

Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1) Part Number B32024-01 says:

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition.

You can create single-level interval partitioned tables as well as the following composite partitioned tables:

* Interval-range

* Interval-hash

* Interval-list

Sure, I can create these composite partitions, but the results aren't particularly useful. When I tried. Oracle spread my results nicely between the two hash subpartitions for the manually defined partition, but put everything in the same subpartition for the interval generated partition. Notice that these are identical sets of rows. The only difference is the key to force them into the manually specified partition or the generated partition. I assume that there is a metalink note on this somewhere.

I got equivalent results for interval-list composite partitioning. I won't bore the reader with the step-by-step for that test since the results are also that all rows in the generated partitions are forced into one subpartition.

Here are my results for the interval hash test:

SQL> create table interval_hash (
N number,
N2 number
)
partition by range(N) interval (2)
SUBPARTITION BY HASH (N2)
(partition p1 values less than (2)
(SUBPARTITION p_1 ,
SUBPARTITION p_2
));

Table created.

SQL> BEGIN

FOR i IN 1 .. 15 LOOP

INSERT INTO interval_hash VALUES (5, i);
INSERT INTO interval_hash VALUES (0, i);

END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_HASH', granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
INTERVAL_HASH P1..................P_1........................... 6
INTERVAL_HASH P1..................P_2........................... 9
INTERVAL_HASH SYS_P138......SYS_SUBP137............15

(I am having tabbing problems in blogger. I hope that my added lines of dots don't confuse too much)

SQL> select * from interval_hash subpartition(p_2) order by n2;

N N2
---------- ----------
0 1
0 3
0 4
0 7
0 9
0 10
0 12
0 14
0 15

9 rows selected.

SQL> select * from interval_hash subpartition(p_1) order by n2;

N N2
---------- ----------
0 2
0 5
0 6
0 8
0 11
0 13

6 rows selected.

SQL> select * from interval_hash subpartition(SYS_SUBP137) ORDER BY N2;

N N2
---------- ----------
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
5 10
5 11
5 12
5 13
5 14
5 15

15 rows selected.

That's a whole lot of partitions!

2010-05-24T17:26:00.000-07:00

Playing with interval partitioning...
I create the simplest table possible and insert 3 rows - generating 3 partitions.

SQL> create table d1 (dt date)
2 partition by range (dt) interval (numtoyminterval(1,'MONTH'))
3 (PARTITION P1 VALUES LESS THAN (TO_DATE('08/01/1776', 'MM/DD/YYYY')));

Table created.

SQL> insert into d1 values (to_date('07/04/1776', 'MM/DD/YYYY'));

1 row created.

SQL> insert into d1 values (to_date('09/22/1862', 'MM/DD/YYYY'));

1 row created.

SQL> insert into d1 values (to_date('08/18/1920', 'MM/DD/YYYY'));

1 row created.

SQL> select * from d1;

DT
---------
04-JUL-76
22-SEP-62
18-AUG-20

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'D1';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
D1 P1
D1 SYS_P62
D1 SYS_P63

But when I look at the partition_count in user_part_tables...

SQL> select table_name, partition_count from user_PART_TABLES where table_name = 'D1';

TABLE_NAME PARTITION_COUNT
------------------------------ ---------------
D1 1048575

That's a whole lot of partitions! Clearly that is the maximum possible partitions. It's odd that the developers at Oracle chose to store that value there rather than the actual count of partitions created. They obviously have it available. Ah, the mysteries of the Oracle.

It's wonderful, but will I survive?

2009-10-12T23:58:00.000-07:00

It's Open World! Sunday a full day of IOUG lectures. Today I heard Jonathan Lewis on "Performance Tuning - being an expert"; Greg Rahm on Data Warehousing and Exedata; Cary Millsap on Performance and Chen Shapira on the uses of charts. I had an introduction to desktop widgets from two experts, I was the only attendee. And I had a nice long introduction to Apex at the Demo Grounds.

It is all Wonderful. Just one little question. How am I going to survive 3 more days? I'm going to bed!

A trivial index demo

2009-09-23T14:20:00.000-07:00

Today I was sitting in a public library, minding my own business when a man who I had never seen before, leaned over to me and asked me, "Is it true that you can drop and index created explicitly, but not one created implicitly as part of a constraint? Initially the reaction was implicit/explicit? Please speak English. When I sorted that out there were 2 further reactions, also unspoken, "Duh, that's pretty obvious", and "Why me?"

"It is an Ancient DBA and he stoppeth one of three."

I demonstrated the matter a bit to him.

Logged on as scott, I create a play table:
SQL> create table tmp(f1 number(4), f2 number(4), f3 number(4));

Table created.

SQL> insert into tmp values (1, 2, 3);

1 row created.

SQL> create index tmp_f2 on tmp(f2);

Index created.

SQL> drop index tmp_f2;

Index dropped.

All straight forward. The index was created 'explicitly' and there is no constraint that it affects if it is dropped, so I can drop it without problem.

Now to add a constraint, and an 'implicit' index.

SQL> alter table tmp modify f2 unique;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';

CONSTRAINT_NAME
------------------------------
SYS_C0011482

SQL> select index_name from user_indexes where table_name = 'TMP';

INDEX_NAME
------------------------------
SYS_C0011482

The constraint has been created as has an index

SQL> drop index SYS_C0011482;
drop index SYS_C0011482
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

and as expected, the index cannot be dropped since it is used by the constraint.

It is all very trivial.

What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null.

And the real question that was never answered

"Now wherefore stopp'st thou me?"

OOW any free Discover tickets around?

2009-08-22T18:05:00.000-07:00

A quick question.

I see that Oracle Open World Discover ticket costs $50. I remember that it cost something in prior years, but that there was some way to get it for free if you signed up by the early bird date. Is there such a thing again this year?

I have seen the 'chance for a full registration', and have entered, but I haven't seen the free 'discover' pass. Is it around?

TYIA

Making problems for myself

2009-05-19T23:23:00.000-07:00

Playing around with my toy database I asked myself, "What happens if DUAL has more than 1 row?" I found out.

SQL> insert into dual values ('Y');

1 row created.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*) ---------- 1

I tried it again. Same result. "Oh, I guess I can't insert into DUAL", says I, and I went about my business.

Later I logged on as SCOTT and tried to drop a table. Playing I have more EMP tables than employees.

SQL> DROP TABLE EMP4;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

WHAT??!!?

Yes it is there and there is only 1 table called EMP4.

SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP4'

OWNER OBJECT_NAME

------------------------------ ------------------------------

SCOTT EMP4

I looked the matter up at orafaq. and followed the instructions.

SQL> select * from dual;

SQL> create table temp_dual as select * from dual;

Table created.

SQL> select * from temp_dual;

Yes, I plead guilty. I DID succeed in inserting those rows into DUAL.

SQL> delete from dual where dummy = 'Y';

1 row deleted.

Strange. It deleted 1 row even though I had put 2 in.

SQL> drop table temp_dual;

drop table temp_dual

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

I deleted the second excess row:

SQL> delete from dual where dummy = 'Y';

1 row deleted.

and I had a functioning database back.

SQL> DROP TABLE TMP_DUAL;

Table dropped.

and then as SCOTT

SQL> DROP TABLE EMP4;

Table dropped.

OK, I get it, Oracle consults DUAL in the drop process. And don't go messing up a database of any importance. But it is odd how the fact that I was succeeding to mess things up was hidden from me. Yes it told me that I had inserted the row, but then it didn't display it with a select. It was an interesting bit of play.

Lazy Log Writer - Vindication & thank you Fitaloon

2008-10-18T22:26:00.000-07:00

The final chapter: The database continued plodding along wretchedly until last Friday night at midnight when I got a call, "The production database is down."
I crawled out of bed, bleary eyed, signed in, and tried to start it. I got:

ALTER DATABASE MOUNT
Sat Oct 11 00:35:05 2008
ORA-00202: control file: '/u01/control02.ctl'
ORA-27037: unable to obtain file status

That woke me up. OK, Who/what messed with the control file?
I tried to look at the file and got a message that told me that not only was the control file inaccessable, the entire drive /u01 was inaccessable.

I called across the great divide to the company who controls the hardware and eventually got, "The backup battery for the write cache needs replacing in the SAN".

Monday morning I resent my email to management from a week and a half before. I highlighted my paraphrasing of Fitaloon's comment to my first posting on this subject, "Could our problem be caused by the write cache on the disk having some sort of problem? For example, could it be something as simple as failed backup batteries for the write cache?" Thank you, Fitaloon. You hit the nail on the head.

Since the replacement of the battery, our log writer has been zipping right along, good performance, at last!

Delete vs. Truncate - graphically

2008-09-28T02:05:00.000-07:00

This is too funny to not share. Of course, everyone knows that a truncate is much more efficient than a full table delete. On my system, still suffering from a slow log writer, but absolutely quiet at the moment, I ran an insert of a million rows (ctas from tbl1 to tbl2), a delete of those million rows (from tbl2) , and then a truncate of them (from tbl1). I looked over at Enterprise Manager and this is what I saw: (I added the tags for benefit of blog readers)

I had not been expecting such a graphical re-enforcement of the rule. I had found the full table delete in some executing code and was curious how much redo it was generating. I ran René Nyffenegger's script 'how_much_redo' .
Assuming that it is giving accurate results, here is what I got for the 3 operations.

SQL> INSERT INTO T1_DEL (SELECT * FROM T1);

1161874 rows created.

SQL> exec how_much_redo;
New Redo Bytes Written: 408985600 (390 MB of redo TO INSERT)

SQL> delete from T1_DEL;

1161874 rows deleted.

Elapsed: 00:06:30.90 (6 MINUTES TO DELETE)
New Redo Bytes Written: 661496320 (630 MB of redo TO DELETE)

SQL> truncate table T1;

Table truncated.

Elapsed: 00:00:06.80 (6 SECONDS TO TRUNCATE)
New Redo Bytes Written: 815616 (less than 1Mb of redo to TRUNCATE)

Lazy Log Writer - The non Resolution

2008-09-27T13:46:00.000-07:00

OK, after the huge emergency, "You can't attend the last day of open world because our problems are too big." After my debugging efforts, how did the problem of the slow writes to the redo log files resolve?

1. Management asked that the entire database be moved to the NAS disks because they initially seemed faster. I had to let them know, that, no, the writes there are not faster.

2. The efforts to cross the great divide and get the storage manager in the other company to actually look at his configuration resulted in, "Since the writes are slow on two different pieces of our machinery, it can't be our fault - it must be oracle. I'm debugging nothing."

3. The other company informed us, "By the way, we will be installing new hardware in a couple of weeks at the same time that you are making a major application upgrade." How does that sound for a prospect of a smooth transition? This was followed by an email, "Claudia, are you working with the other company on this?" Not only am I not working with them, I never heard of it!

4. Management informed me, "Since there will be a hardware change soon, don't bother to follow up on this problem."

So they will install the hardware, we will deploy the new application version, and there will be storm and drama about the excessive waits for redo log writes. I think that I should change my name to Cassandra.

A lazy log writer

2008-09-25T22:11:00.000-07:00

I've been laughing because I live 1 block from Moscone Center. It was closer for me to walk between the conference and my house than it was to walk between some of the sessions. Today, I saw the other side of that coin. I got ordered back to work and missed the last day of the conference, (and Chen Shapira's presentation!!!)

What was going on at work? Not much - as in not what should have been. I like looking at the performance monitor on Enterprise Manager for a quick glance at what is going on. It wasn't a pretty picture.

Clicking on the 'Blocking Sessions' tab I saw that that the log writer session was blocking various other sessions.

I went into the alert log and was pointed to a log writer trace file. Inside the trace file I found

*** 2008-09-25 15:28:24.239
Warning: log write time 15460ms, size 6999KB

*** 2008-09-25 15:28:24.836
Warning: log write time 590ms, size 6898KB

*** 2008-09-25 15:28:29.852
Warning: log write time 5020ms, size 6999KB

I looked at metalink and got

" The above warning messages has been introduced in 10.2.0.4 patchset. This warning message will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file .

"These messages are very much expected in 10.2.0.4 database in case the log write is more than 500 ms. This is a warning which means that the write process is not as fast as it intented to be . So probably you need to check if the disk is slow or not or for any potential OS causes. "

We just upgraded to 10.2.0.4. Our storage is across the great divide at another company. We are often short of answers other than, "Everything is configured correctly". With quite a bit of work we have gotten a pair of LUNS allocated for redo logs.

As a test, I moved the redo logs from the SAN to a NSF drive - NOT one that should be allocated to redo. Here was the immediate result:

The log writer waits stopped. Compliments from management. A request from management to storage management to move the entire database to this kind of storage, everyone is happy

almost.

Tonight I looked at the trace file:

*** 2008-09-25 22:53:34.154
Warning: log write time 750ms, size 0KB
*** 2008-09-25 22:53:35.943
Warning: log write time 1770ms, size 28KB
*** 2008-09-25 22:53:39.889
Warning: log write time 940ms, size 0KB

Log writer is taking forever, and it isn't even doing anything!

To be continued..... and detective suggestions welcome!

OOW - Day 2 - Simple things

2008-09-22T22:49:00.000-07:00

I spent most of the day in hands on classes. I learned to use the Configuration Management Pack of Grid Control to install patches. It looked really interesting. Now I have to go back to work and figure out how to install Grid Control so I can use it. I assume there will be a fair amount of kicking, screaming, and tearing out my hair in that little task which the Oracle demonstrator waved his hand at, as if to say, "It's nothing". Oh those little nothings.

I then went to a hands on on Oracle Advanced Compression in 11g. The class really was just working through a couple of OBE examples with the comfort of people who actually know the a LOT about Oracle Compression there to help with the silly little problems that people have working through examples. I wish I had had some sort of deep questions to ask about compression. All I learned was, it works. There is some time, and some space savings. It will be useful when we move up to 11g.

When I say that the Advanced Compression was 'Hands On', I mean it literally. Braille would have been more accurate. For some reason, the whole demo was available in a box slightly larger than a youtube video. I get annoyed with myself when I have difficulties with the parts that I think should be the simple parts.. Here were a whole group of Oracle employee experts who couldn't make the VM window expand to the whole screen. There was more compression in the demo than anyone intended!

OOW - Day 1 - Blogger's perks.

2008-09-21T23:05:00.000-07:00

OOW'08 - Day 1. I spent the day in the 11g New Features Exam Cram. A couple of people encouraged me to try the exam. I have never touched 11g. I took the course as an intro to 11g, not as a refresher before the exam. If I passed the certification after just this, then the OCP really is meaningless. I prefer to actually know what I'm being tested on when taking an exam.

I happened to be seated next to Fuad Arshad, who apparently does work with Oracle 11g and is ready for the exam. I wish him all the best on the exam.

Fuad's conference badge had a large 'Blogger' written in bright orange on the case. I know that I signed up for one. I asked at the registration desk, and they gave me a 'Blogger' badge too. I lined up for the Keynote Address. Immediately, the ushers pushed me out of the main line and into a smaller line for press & bloggers. I was led to a special area of the auditiorium saved for bloggers - a long table set up so that we could record every golden world of Mary Matalin & James Carville. For Pete's sake! I think this 'Blogger' badge is going to have some strange side effects.

Then this evening I was able to get together with all of the other Oracle Bloggers who I have been following for a year. That was really special. What a nice bunch of people!

travelling trace file

2008-08-28T13:23:00.000-07:00

I just noticed a detail that I don't like.

We had an error logged in the alert log. It informed me that details were in
db_ckpt_1234.log. Fine, but there was no such log in the bdump directory.

The dba who was here before me wrote a nice script to run every Sunday to clean up old trace files. What he didn't think of is that the trace files seem to be written per session, and that the checkpoint 'session' continues from instance start up.
His script came along and happily moved the trace file to a backup_bdump directory. Oracle couldn't find it and didn't write the details.

One more fix to make.

NoCOUG conference 8/21/08

2008-08-20T06:09:00.000-07:00

For those in the San Francisco Bay Area. The NoCoug Congerence is tomorrow Thursday 8/21. Cary Millsap is the keynote speaker. All are invited.

	Summer Conference 2008 Hosted by Chevron August 21, 2008 at Chevron, San Ramon, 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: “Millsap's Grand Unified Theory of "Tuning"” - Cary Millsap, Method-R.com
10:30 - 11:00	Break
	Room 1220	Room 1240	Room 1130
11:00 - 12:00 Session 1	“Case Studies in Performance Problem Diagnosis and Repair” by Cary Millsap, Method-R.com	“Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros” by Greg Pike, Piocon	“Architecting Data Systems for Compliance” by John Weathington, Excellent Management Systems
12:00 - 1:00	Lunch
1:00 - 2:00 Session 2	“Getting Coherence: Introduction to Data Grids” by Raanan Dagan, Oracle	“Data Warehousing with Oracle 11g” by George Lumpkin, Oracle Corporation	“Building a Web-based Application Using Application Express” by Willie Albino, Lockheed Martin
2:00 - 2:30	Break and Refreshments
2:30 - 3:30 Session 3	“Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager” by John Sheaffer, Oracle Corporation	“Introduction to Java-PL/SQL Developers Take Heart” by Peter Koletzke, Quovera	“Oracle Fusion Middleware Roadmap & Strategy” by Margaret Lee, Oracle Corporation
3:30 - 4:00	Raffle In the vendor area.
4:00 - 5:00 Session 4	“A Tour of the AWR Tables” by Dave Abercrombie, Convio	“What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester” by Arnie Weinstein, Lawrence Livermore National Laboratory	“Oracle Analytical Functions - the hidden treasure” by Ron Warshawsky, DBA Infopower
5:00 - ???	NoCOUG networking and happy hour at San Ramon Marriott, 2600 Bishop Dr., San Ramon, CA 94583 -- (925) 244-6171 Directions: Leaving the conference, go straight across Bollinger onto Camino Ramon. Turn left onto Bishop Drive. The Marriott is less than half a mile, on the left. * Walk through the bar to the outside patio. *

Mark your calendar for NoCOUG's Fall Conference:
November 13, 2008 at CarrAmerica Conference Center in Pleasanton.

Speaker Abstracts for Spring Conference

Keynote

“Millsap's Grand Unified Theory of "Tuning"” - Cary Millsap, Method-R.com

First there were the v$ and x$ views. And tkprof. Then came bstat/estat, Statspack, ASH, ADDM, OEM, and plenty of third-party tools that look kind of like those tools. There are loads of OS tools too that have been around a long time, like top and sar and strace and pstack and gprof. And then there are the methods: OPIM, and YAPP, and Method R to name three. In this presentation, Cary Millsap gives a brief tour of the moving parts and reveals his own perspective about how all these parts should fit together.

Room 1220

“Case Studies in Performance Problem Diagnosis and Repair” - Cary Millsap, Method-R.com

While we certainly learn from our own experiences, we can save ourselves plenty of time and frustration if we can learn from the experiences of others. This presentation reviews the real life experiences of professionals like you who faced some nasty performance problems and lived to tell the tale. Come experience their journeys as a spectator so that next time, when you're faced with a similar problem, you may have just the information you need to handle it with greater ease and efficiency.

“Getting Coherence: Introduction to Data Grids” - Raanan Dagan, Oracle

Grid-based infrastructures are being developed, deployed and used to achieve unlimited application scalability and continuous availability across multiple datacenters. Understanding the additional capabilities of these infrastructures and how they can be improved with the use of Data Grid technology to solve increasingly difficult and complex problems ensures that your organization is getting the maximum utility from Grid Computing.

This session focuses on how Oracle Coherence Data Grid can easily help you achieve all of these goals and more!

“Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager” - John Sheaffer, Oracle Corporation

Today's DBA is increasingly tasked with providing greater support across the enterprise - in some cases, being stretched so thin that leveraging automated tools is your only survival mechanism. In this session, you will learn about how often overlooked features of Oracle Enterprise Manager will help you improve efficiency, lower your blood pressure, and get you home on time!

“A Tour of the AWR Tables” - Dave Abercrombie, Convio

Introduced in version 10g, Oracle's Automatic Workload Repository (AWR) provides diagnostic information for performance and scalability studies, automatically recording a rich variety of database performance statistics.

What's the best way to leverage this wealth of data? While you can run Oracle-supplied AWR reports, or use Oracle features such as the Automatic Database Diagnostic Monitor (ADDM), each Oracle database presents its own unique tuning challenges. In this session you'll learn how to work directly with AWR tables, using customized queries to improve insight into your own particular scalability issues.

Topics include:

Important AWR tables, their contents, how to join them, and their quirks and limitations.
Sample queries that can be easily adapted to focus on your own unique set of problems.
Estimating the "Average Active Session" metric.
Simple statistical techniques to find spikes and other types of anomalous behavior.
A comparison of techniques used for historical scalability studies with those used for real-time performance crisis resolution..
Use of DBMS_APPLICATION_INFO and JDBC end-to-end metrics.
Useful tips on configuring AWR.

Room 1240

“Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros” - Greg Pike, Piocon

Although seasoned professionals understand the benefits of solving business problems with efficient queries or PL/SQL, the volunteer experts and Oracle Ace contributors on OTN's SQL and PL/SQL discussion forums raise the query writing bar to an entirely new level. Oracle professionals at any skill level will find this forum packed with a treasure chest of tips, tricks and techniques. With over 60,000 topics and 325,000 posts, it's the mother lode of SQL and PL/SQL education from recognized experts worldwide.

In this session, the powerful techniques of advanced query authoring are explored by reviewing real-world forum threads and the unique solutions posted by the gurus. The resident experts from the OTN forums solve problems using a combination of analytic functions, hierarchical queries (CONNECT BY), collections (COLLECT ), XML functions/operators (SYS_XMLGEN, and XMLSEQUENCE), Pipelined Functions, the MODEL clause and more.

“Data Warehousing with Oracle 11g” - George Lumpkin, Oracle Corporation

Satisfying business intelligence requirements for all users throughout the enterprise requires a fast, reliable and scalable data warehouse to protect and maintain quality business information. Come learn why Oracle is the #1 database for data warehousing, why Oracle Warehouse Builder is the best tool for building data warehouses, how ground-breaking new features in Oracle Database 11g will dramatically speed query performance, and, how you can accelerate data warehousing deployments.

“Introduction to Java-PL/SQL Developers Take Heart” - Peter Koletzke, Quovera

Oracle's current focus on implementing database and development features based on the Java language may have you thinking that you need to learn Java. However, if you are familiar with PL/SQL, your first view of Java may be a bit discouraging because its object-oriented core makes it look very different. Also, you may be wonder about Java's strengths and weaknesses and where it fits in the industry.

This presentation explains to PL/SQL developers who have had little or no exposure to Java, the basic concepts of, and terms used in Java. The presentation provides an overview of the language and reviews the concepts of object orientation upon which Java is based. It also discusses the fundamental Java code structures -- classes and methods -- as well as control statements; exception handling; data types; and variables. This explanation will act as a springboard for further study. Level: Beginner

“What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester” - Arnie Weinstein, Lawrence Livermore National Laboratory

Software developers face constant pressure to produce highly complex PL/SQL code under tight deadlines. Without an efficient and reliable way to perform thorough code testing, software is released with defects that would otherwise be eliminated. Using an automated test tool carries risks that may be mitigated by certain practices. These practices greatly improved our ability to develop high quality and efficient testing software. In this paper, we will share some of these practices

Room 1130

“Architecting Data Systems for Compliance” - John Weathington, Excellent Management Systems

A gentle introduction to compliance for database system architects, and introductory concepts for the architecture that's optimal for supporting the growing need for your company's compliance.

“Building a Web-based Application Using Application Express” - Willie Albino, Lockheed Martin

Application Express is a free web application development environment that comes with the Oracle database (Oracle 9.0.3+). Using a simple web browser, you can create and deploy web-based, database-centric applications very quickly. In addition you can upload and create applications from MS Excel spreadsheets. In this session, an introduction to the Application Express environment will be provided. In addition, an application will be built from tables existing in the database, as well as data uploaded from some Excel spreadsheets.

“Oracle Fusion Middleware Roadmap & Strategy” - Margaret Lee, Oracle Corporation

With the recent acquisition of BEA, a number of best-in-class products have been added to Oracle's robust portfolio of middleware solutions. Customers are interested in knowing what Oracle's strategy and roadmap will be around protecting existing BEA customers' investments and integrating BEA products into existing Fusion Middleware solutions. This session will detail how Oracle will leverage best products from both companies to provide solutions in Java & Transaction Processing, SOA and Business Process Management, and User Interaction and Enterprise 2.0.

“Oracle Analytical Functions - the hidden treasure” - Ron Warshawsky, DBA Infopower

Oracle introduced them in 8i, but do we really use them to their full potential? Oracle analytical functional can save great deal of coding and provide amazing results very fast.

How about doing this - and this is only a beginning:

Ranking data within subsets of a data set.
Aggregating data within subsets of a data set.
Performing aggregations over moving windows.
Displaying and comparing aggregates to individual entries within a single query.
Comparing two or more rows within a given data set.

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 Chevron Park in San Ramon:

Address: 6101 Bollinger Canyon Road, San Ramon, CA 94583

Please note that the conference building is just after the Chevron Campus.
Upon arrival, enter the building at the East entrance where you'll find NoCOUG representatives ready to sign you in.

From Highway 680 South:
Exit at Bollinger Canyon Road. Turn left onto Bollinger Canyon Road (heading east over the freeway). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted.

From Highway 680 North:
Exit at Bollinger Canyon Road. Turn right onto Bollinger Canyon Road (heading east). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted.
Map

Make it go away! (rman delete)

2008-07-31T18:11:00.000-07:00

So much documentation tells you how to set up things for Oracle. It is harder to find information on how to get rid of it once you have it.

I am doing perfectly lovely incremental compressed backupsets of my database. Then one day, playing with EM, I in experimented with the 'Oracle recommended backup stategy'. I may have missed something in my set up, but I ended up with an un needed image copy backup of my database. Help! This thing is devouring my flashback area. So what is the proper way to remove it.

First step, of course, is to kill the Oracle job, so it doesn't do that again!

Then in rman:
RMAN> list copy;

gives me a nice long list - one line of which was:
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf

I double check on the file system:
$> ls o1_mf_users_48y8x123_.dbf
o1_mf_users_44y8x123_.dbf
Yes, the copy is there.

To delete it in RMAN I enter
RMAN> delete datafilecopy 30428;

and get:

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf

Do you really want to delete the above objects (enter YES or NO)? y
deleted datafile copy
datafile copy filename=/bkup/flashbk//datafile/o1_mf_users_44y8x123_.dbf recid=30428 stamp=1334123
Deleted 1 objects

I check in the file system and this time my ls returns
ls: 0653-341 The file o1_mf_users_44y8x123_.dbf does not exist.

Good bye, nusance.

Something is VERY busy

2008-07-10T23:20:00.000-07:00

I have been suspecting that something is causing excess connects/disconnects from our database. To see the number of connects I went in search of the listener.log. By default it is in $ORACLE_HOME/network/log. There was a listener.ora there , but according to it, there hasn't been ANY connections to our database in months!

This is a solaris box. I read that there is a 2G file limit on Solaris. (Maybe that means a limit on log files. Obviously, my data files are much larger than that.).

I issued the lsnrctl command:

lsnrctl >set log_file listener2.log

Quickly it resumed recording connections, now into listener2.log. It recorded 2 of them EVERY second!!! OK. my suspicion was right. Now to track down exactly WHAT is connecting and disconnecting so much.

I'm beginning to distrust Oracle

2008-07-06T23:35:00.000-07:00

I am learning that I have been trusting Oracle too much. I have figured that Oracle has top rate developers, so when they offer a facility like ASMM or GATHER_STATS_JOB. I assume that it will be done well.

Now I am learning that ASMM has been allocating our shared pool too large, and has been adjusting sizes not once an hour, but once a minute.

I am not happy with the statistics that the 'wonderful' automatic GATHER_STATS_JOB has been collecting. Also the job has been causing contention with some of our production jobs.

The one that seems really strange to me is Log_buffer. I allocated 28M to log_buffer. According to Oracle, LGWR should be writing, at a maximum, when log_buffer is 1M full. Instead, I see that it is writing 2M at a time. I am going to have to figure that one out.

I expect that when Oracle offers to automate these processes, that this is their own product, so the calculations will be well done. I expected, but I am wrong.

Q.E.D.

2008-06-19T15:36:00.000-07:00

User on test DB, "Response time is terrible."

DBA, "You are the only user on the DB, but you have a dozen sessions open. Can you close some sessions?"

User, "I can't see my sessions through the application. Bounce the database"

DBA, " I can see the sessions just fine. I'll kill your excess sessions."

User, "No, don't kill my sessions. Bounce the database. I'm bouncing the database."

We don't need no stinking controls around here.
Everyone can do everything.
He bounced the database.

User, "I bounced the database. My [one] session is running just fine."

Q.E.D. - bouncing the database improves performance.

==========================================================

An update to the post above:

The same user has informed me that I should always shutdown the database with "Shutdown abort" . "It works much better."

I have always assumed that everyone else knows more than I do.

Maybe that isn't true. Maybe I do understand more than some people...

Lessons Learned after a Hell Weekend

2008-06-16T20:55:00.000-07:00

A necessary part of any database project plan is a fall back plan. What is planned if there is a failure at any particular step.
Backup - this includes enough space allocated for additional backups as needed.
Part of any upgrade script should be rollback scripts.

I should have know that I was in trouble when on Friday afternoon when I was given a time line which was called 'optimistic' with no what-if forseen.

Northern California Oracle Users' Group Conference

2008-05-14T08:58:00.000-07:00

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

Moving back to a lost spfile

2008-05-02T17:38:00.000-07:00

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.

2008-03-29T09:26:00.000-07:00

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

2008-03-27T19:42:00.001-07:00

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

Temporarly change the system user password
Run the Data Pump with the temporary password in plain text
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.

2008-03-04T16:26:00.000-08:00

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.

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.

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

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.

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

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.

2008-02-11T22:22:00.000-08:00

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

2008-02-11T12:37:00.000-08:00

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

Startup steps review

2008-02-03T02:49:00.000-08:00

Do you remember what happens as an instance starts? I didn't. So, to review...

Assuming that you issue the start command in steps:

SQL> STARTUP NOMOUNT;

1 Oracle first opens and reads the initialization parameter file.

2 The memory areas associated with the database instance are allocated. Their size was specified in the parameter file. These allocated memory areas and parameters include the following:

SGA_TARGET parameter sets the amount of memory available to the SGA.
DB_CACHE_SIZE (default block size) &DB_nK_CACHE_SIZE (non-default block size)
SHARED_POOL_SIZE (contains the Library Cache & Data Dictionary Cache)
LOG_BUFFER
LARGE_POOL_SIZE ,
JAVA_POOL_SIZE, & STREAMS_POOL_SIZE
DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE

3 Oracle background processes are started. In most basic terms this includes the following Oracle background processes:

DBWR - Database Writer process writes dirty buffers from the database block cache to the data files.
CKPT - Writes checkpoint information to control files and data file headers.
LGWR - Log Writer process writes the log buffers out to the redo logs.
PMON - Process Monitor process recovers failed process resources.
SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents.
ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s).
MMON - The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).

Together, these processes and the associated allocated memory are called the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage.

SQL> alter database mount;

When the startup command enters the mount stage, it opens and reads the control file.
From the control file Oracle determines the location of the datafiles, but does not yet open them.

SQL> alter database open;

When Oracle opens the database, it

accesses all of the datafiles associated with the database. If a file is missing it is reported.
makes sure that all of the database datafiles are consistent.

And now the database is ready for use.

Getting Biblical

2008-01-26T03:08:00.000-08:00

My curiosity raised by Laurent Schneider's notes about maximum and minimum dates in Oracle, I learned something about the time of Christ.

Apparently, the day before Jan 1, 0 AD is Dec 31, 1 BC.
I didn't expect it. By the way, biblical historians say that this change of dates occurred neither at the birth nor the death, but when he was something like 4 years old.

Anyway, here is what the Oracle of Redwood City had to say on the topic.

alter session set nls_date_format='FMDay ddth Month YYYY B.C.';

SQL> select trunc(date '-0-1-1','yyyy') from dual;

TRUNC(DATE'-0-1-1','YYYY')
----------------------------------
Thursday 1st January 0 A.D.

SQL> select trunc(date '-0-1-1','yyyy')-1 from dual;

TRUNC(DATE'-0-1-1','YYYY')-1
----------------------------------
Wednesday 31st December 1 B.C.

Real Application Testing - RATs!!

2008-01-22T22:49:00.000-08:00

I attended Oracle Day San Francisco today. There were some really fine presentations. One was "Managing Change with Oracle Database 11g" which mainly talked about "Real Application Testing". My opinion was that it seems like a good idea, but that it isn't mature yet.

In Real Application Testing you capture the actual production workload for replay and comparison on a test system. Nice idea, but the proposed test system is the same size, and has a much machinery and disk space behind it as the production system. I don't know about you, but I have never seen such a test system as big as the production system.

The lecturer suggested that

You could discount any disparities that are to be blamed on the platform difference.
You could run the workload on the test system against the old application, and then run the workload against the new application (or database version) on the test system and make a non production apples to apples.

Either solution is a good idea, but not perfect.

There was a lot of emphasis given that a production workload is much faster to prepare than a test workload,(created by testers) complete with charts to prove the point. But a production workload is going to be full of social security numbers and credit card numbers, etc. The presenter suggested that you could mask sensitive data, but there was no component for this item in the time line that compared traditional testing with Real Application Testing. I have just read in Lutz Hartmann's blog that Oracle was announcing in November that the ability to mask 'was coming'. I don't know if it is here yet.

Overall, Real Application Testing seemed like a good idea that will be much better by Oracle 12 or 13.

MATERIALIZED VIEWS

2008-01-18T12:26:00.000-08:00

A materialized view provides access to table data by storing results of a query in a separate schema object.

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental, (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log keeps a record of changes to the master tables.

Materialized views can be refreshed either on demand or at regular time intervals. Normal replication occurs at the row level.

Setup involves

Configuring certain DB parameters
Configuring the replicated objects

Parameters that need to be something other than the default:

Job_queue_processes – at least 1
Open_links – one additional link for each DB that will be replicated to.
Shared_pool_size – add 20 Mg for basic replication.

SQL> CREATE MATERIALIZED VIEW my_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

REFRESH:

[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key | rowed}]]

The refresh option specifies:

The refresh method used by Oracle to refresh data in a materialized view.
Whether the view is primary key based or row id based.
The time and interval at which the view is to be refreshed.

Refresh Method:

FAST

You should create a materialized view log for the master tables if you specify REFRESH FAST.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

COMPLETE – recreates the entire materialized view.
FORCE – Performs a fast refresh if one is possible, or a complete refresh otherwise. FORCE is the default.

PRIMARY KEY / ROWID Clause

WITH PRIMARY KEY – Materialized view is based on the primary key of the master table. This is the default.
Primary Key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
ROWID materialized views should have a single master table and cannot contain:

Distinct or aggregate functions
Group by subqueries
Joins & Set operations

OTHER:

SQL> execute DBMS_REFRESH.DESTROY(name=> ‘my_group1’);

Removes all materialized views from the refresh group and deletes the refresh group.

SQL> select job, what, failures from user_jobs;

To see if any refreshes are failing.

References:

Materialized Views in Oracle By Ajay Gursahani (most of the material in this blog item is taken directly from this article)

Oracle Replication Expert Methods for Robust Data Sharing. By Karam, Garmany, Burleson. Rampant Press

The magical MIT drawing/motion simulator.

2008-01-18T09:30:00.000-08:00

It's not Oracle, but it is incredibly COOL! Check out this you tube demo of the
MIT system that allows the user to draw a design on a white board, and then see how it operates in motion just from the drawing! I haven't adequately described how amazing it is. Take a look.

http://www.youtube.com/watch?v=NZNTgglPbUA

Northern California - NoCOUG conference 2/19/08

2008-01-17T21:43:00.000-08:00

Those who live in the San Francisco Bay Area may be interested in the NoCOUG Winter Conference.

The NoCOUG site for this is http://www.nocoug.org/next.html

	Winter Conference 2008 Hosted by Oracle February 19, 2008 at Oracle Corporation, Redwood Shores, 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: “The Future of High Availability” - Juan Loaiza, Oracle Corporation
10:30 - 11:00	Break
	Auditorium	Room 102	Room 103
11:00 - 12:00 Session 1	“DB Time-based Oracle Performance Tuning: Theory and Practice” by Uri Shaft and Graham Wood, Oracle Corporation	“Oracle Gems” by Daniel Morgan, University of Washington	“Birds Do It: Migrating Forms to Java EE Web -- A Case Study” by Peter Koletzke, Quovera
12:00 - 1:00	Lunch
1:00 - 2:00 Session 2	“Simplified SQL Performance Management with Automatic SQL Tuning and Real-Time SQL Monitoring” by Pete Belknap, Oracle Corporation	“Web 2.0” by Bradley Brown, TUSC	“Whither Business Logic?” by Avrom Roy-Faderman, Quovera
2:00 - 2:30	Break and Refreshments Last chance to visit the vendors
2:30 - 3:30 Session 3	“SQL Performance Analyzer: Testing the Impact of System Changes on SQL Performance” by Khaled Yagoub, Oracle Corporation	“Resolving Conflict with the Arrow of Truth” by Bradley Brown, TUSC	“Oracle Adaptive Access Manager: What, Why, How” by Dan Norris, Piocon
3:30 - 4:00	Raffle
4:00 - 5:00 Session 4	“What is New in Oracle SOA Suite” by Joanna Schloss, Oracle Corporation	“Oracle Annoyances for Geeks: Deadlocks and Livelocks” by Iggy Fernandez, Verizon Business	“High Availability Options for Oracle Database” by Dan Norris, Piocon
5:00 - ???	NoCOUG networking and happy hour at Chris' New Harbor Bar, 150 Harbor Blvd, Belmont, CA 94002 Tel:(650)591-1881. (Directions: Head South on Oracle Pkwy toward Marine World Pkwy - 01. mi, Turn right at Marine World Pkwy - 0.4 mi, Continue on Ralston Ave. - 0.2 mi, Take 101 South toward San Jose, slight right at Harbor Blvd - 0.6 mi, Arrive at 150 Harbor Blvd, on the right.)

Mark your calendar for NoCOUG's Spring Conference:
Thursday, May 15, 2008 at the Crowne Plaza hotel in Foster City.

Speaker Abstracts for Winter Conference

Keynote

“The Future of High Availability” - Juan Loaiza, Oracle Corporation

Your business needs to be online 24x7, 365 days a year. If critical applications, servers, or data become unavailable, your entire business could be jeopardized. Lost revenue, dissatisfied customers, penalties, and negative press will have a lasting effect on your organization's reputation. Oracle Database 11g can protect your business from negative outcomes due to planned and unplanned downtime, including the most common cause of failure - human error. In addition, Oracle's Maximum Availability Architecture framework provides clear and concise guidance on implementing best practices using Oracle's proven high availability technologies. During this session, Juan will highlight Oracle's continuing evolution of technology for achieving high availability. He'll discuss recent development innovations and a roadmap to help your company invest in guarding against failures to meet your quality of service obligations at the lowest cost.

Auditorium

“DB Time-based Oracle Performance Tuning: Theory and Practice” - Uri Shaft and Graham Wood, Oracle Corporation

Oracle 10g formally introduced the fundamental concept of DB Time as part of the Server Manageability effort. This concept underlies or is significantly used by many of the manageability technologies of the Diagnostic and Tuning packs in both 10g and 11g, including ADDM, SQL Tuning Advisor, Access Advisor, and Enterprise Manager. Less prominently but no less importantly the concept of DB Time is intended to be used as the new lingua franca for Oracle performance tuning. This session will introduce the abstract theory of DB Time and its time-normalized sibling Average Active Sessions. The process of performance tuning using DB Time will be discussed and compared with other current methodologies including those based on wait-events and SQL trace. The session will discuss the Active Session History (ASH) technology and its critical relationship to quantifying the expenditure of DB Time in an active system across many dimensions of interest to performance analysts. The automatic analysis of DB Time by the Automatic Database Diagnostic Monitor (ADDM) is also discussed. Examples usages of Enterprise Manager's visualization of DB Time will also be presented.

“Simplified SQL Performance Management with Automatic SQL Tuning and Real-Time SQL Monitoring” - Pete Belknap, Oracle Corporation

SQL tuning is a critical aspect of database performance tuning. Unfortunately this has traditionally been an inherently complex activity, requiring a high level of expertise in multiple domains. Furthermore, SQL tuning is both time-consuming and repetitive due to the large volume and evolving nature of the SQL workload and the underlying data. Oracle 10g introduced the SQL Tuning Advisor to provide quick recommendations for solutions to common SQL performance problems, including the application of SQL profiles to transparently improve problematic execution plans without re-writing SQL. Oracle 11g automates this process through the Automatic SQL Tuning feature, wherein SQL Tuning Advisor runs automatically in system maintenance windows, tests its own recommendations, and can implement SQL profiles when they show a large performance improvement. The end result is that Oracle can now automatically recognize and fix many SQL performance problems with minimal or no human intervention. Monitoring long-running and complex SQL during execution and understanding where it spends execution time is another major challenge for the DBA. Simply finding long-running SQL executions can be difficult because statistics are typically aggregated across all executions. Parallel execution plans are particularly difficult to analyze as multiple sessions can simultaneously be working on them, possibly across multiple database instances in RAC environments. Drilling down to discover where time is spent at the plan operation level has also been almost impossible. Oracle 11g introduces the Real-Time SQL Monitoring feature to help the DBA by giving a complete and accurate picture of individual SQL executions by exposing the interesting new measurements at each stage of the execution pipeline. In this session we take a deep-dive into these two exciting new technologies and see specific examples where they help simplify SQL monitoring and tuning immensely. We will demonstrate how these features have lowered the barrier to entry for competent SQL tuning, introducing new approaches that are both more scientific and more accessible to the broad base of Oracle customers.

“SQL Performance Analyzer: Testing the Impact of System Changes on SQL Performance” - Khaled Yagoub, Oracle Corporation

SQL Performance Analyzer is a component of the Oracle 11g Real Application Testing option. This novel technology enables flexible testing of database change scenarios on SQL execution performance. System changes like database upgrade, initialization parameter settings, schema changes, and gathering optimizer statistics may affect many execution plans in a large workload. Today customers attempting to achieve positive benefits through system-level changes can sometimes end up with unanticipated side effects on the execution of some SQL or other due to the complexity of predicting all possible effects over workloads consisting of many SQL statements. SQL Performance Analyzer offers a comprehensive solution that enables users to forecast and analyze how a system change will impact SQL query plans over a large workload and assess the run time performance prior to production deployment. The technology identifies potential problems that may occur due to a change and provides solutions for avoiding degraded SQL performance that are predicted to occur under the change. As well, it measures performance benefits achievable by the change through quantitative estimates of the system's performance in the new environment with high confidence. This comparative analysis of SQL workload response time allows clear and easy assessment of the change. In this session we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end solution for managing SQL execution performance in the face of ever-changing system environments.

“What is New in Oracle SOA Suite” - Joanna Schloss, Oracle Corporation

This session provides an overview of the Service Component Architecture Assembly Model and how it simplifies the end-to-end lifecycle management of SOA composite applications. Application designers can now design, package, version, and manage their SOA composite applications (Web services, BPEL, Enterprise Service Bus flows, and the like) as one single entity. These capabilities will be illustrated with a demonstration of Oracle SOA Suite 11g.

Room 102

“Oracle Gems” - Daniel Morgan, University of Washington

Oracle Ace Director Daniel Morgan will present a variety of Oracle Gems ... 10g and 11g database features that have been hidden from view and that, with a little polish, may be of great value. The gems, some for developers, some for DBAs, will be presented live in SQL*Plus.

“Web 2.0” - Bradley Brown, TUSC

You've heard the term Web 2.0 and might be wondering exactly what it means to you and your business. You might be wondering what Web 2.0 includes. On the other hand, you might be right in the middle of it all, but noticing that more and more Oracle products seem to offer Web 2.0 entry points. Or maybe you're not using Oracle technology for anything but your backend database. You might be wondering which other products should be under consideration? Which should be avoided? What's a good entry point solution?

“Resolving Conflict with the Arrow of Truth” - Bradley Brown, TUSC

Have you ever run into a conflict at work or at home, but you had difficulty resolving the problem? By using the methods described in this presentation (the arrow of truth), you will be able to work through issues that otherwise may appear irresolvable. This method works not just at work, but at home too.

“Oracle Annoyances for Geeks: Deadlocks and Livelocks” - Iggy Fernandez, Verizon Business

Deadlocks are not well understood and the trace file generated by Oracle when a deadlock occurs suggests that a deadlock is a problem "due to user error in the design of an application or from issuing incorrect ad-hoc SQL." In fact, sessions can deadlock even if they are not contending for the same data and a session can even deadlock with itself! We discuss this and other misconceptions about deadlock and demonstrate multiple ways in which deadlock can occur.

Room 103

“Birds Do It: Migrating Forms to Java EE Web -- A Case Study” - Peter Koletzke, Quovera

This case study examines the migration of an enterprise-level, character-mode, Oracle Forms 6.0 application to Fusion ADF web technologies. It explains the decision points for choosing Fusion ADF and focuses on how ADF Business Components provided the core pivot point for an ADF user interface and PL/SQL back end business rules code. The presentation also describes and offers insight on the techniques used to migrate the skills of staff Forms developers to the new technologies. Finally, it lists successes and lessons learned along the way.

“Whither Business Logic?” - Avrom Roy-Faderman, Quovera

Where should you put business logic in your web applications? Database people will tell you it should go in the database; J2EE types will say it belongs in the model layer of the application. But there's more to this than a holy war; the question of where to put your business logic can be approached rationally, depending on features of the logic and application. This talk covers principles for placement of business logic in the database or application, as well as the question of logic in the client tier. The talk will also cover Oracle JDeveloper techniques for accommodating different business logic placement.

“Oracle Adaptive Access Manager: What, Why, How” - Dan Norris, Piocon

You're a hacker. You want data. Shouldn't be too hard--just guess a password or two and you can find lots of confidential information. If you're lucky, you'll find a weakly-secured web application and watch network traffic as someone logs in to find their username and password. Now that you have the credentials, all you need to do is login. When you attempt login, you find that you're denied access or maybe you're asked to type in the PIN code from your login token (which you don't have). This scene was brought to you by Oracle Adaptive Access Manager (OAAM). Oracle acquired Bharosa in October 2007 to fill the need for a real time fraud detection capability in the Oracle Identity Management Suite. OAAM uses a database of heuristics and pattern matching to find the "bad" guys. What happens to them is up to you, the OAAM administrator. Come to this session to learn how OAAM protects applications from fraudulent use without any client-side installation.

“High Availability Options for Oracle Database” - Dan Norris, Piocon

From Oracle RAC to cold backups, this session will introduce the various Oracle and third party features, products, and options that provide high availability for Oracle Database. IT Managers, DBAs, and system architects will benefit from the information provided in this technical session. To help you determine the best choice for your environment, we'll focus on the advantages and disadvantages of each option and discuss the factors that commonly influence the decision-making process.

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 Oracle Conference Center

Address: 350 Oracle Parkway, Redwood Shores, California. Phone: 650-633-8300 Fax: 650-633-8399

Southbound- Take Highway 101 South (toward San Jose) to the Ralston Ave./Marine World Parkway exit. Take Marine World Parkway east which will be left at the light. Make a left onto Oracle Parkway. 350 Oracle Parkway will be on the right.

Northbound- Take Highway 101 North (toward San Francisco) to the Ralston Ave./Marine World Parkway exit. Take the first exit ramp onto Marine World Parkway. Make a left at the first light onto Oracle Parkway. 350 Oracle Parkway will be on the right.

Map

Five things about Data Pump.

2008-01-15T15:48:00.000-08:00

Nobody tagged me, but here are 5 things about Data Pump that it would be nice to have available in my brain when someone asks me.

1. Description

Data Pump is a utility that moves data and metadata.
To the user it looks like export/import, but they are separate utilities and their files are not compatible.
Data Pump runs on the server. This helps performance since there is no movement of data back and forth across the network between client and server.

2. Dump Files

Export dumps are a bunch of DDL and INSERT statements.
DataPump dump files in binary format - very similar to the format stored in Oracle database datafiles inside of tablespaces.

3. Usage

You may export/import tables, schema, or the whole database
You may export/import just metadata, or metadata and data
You may run Data Dump export in Estimate Only mode to determine the amount of space that will be required for the dump file.
You need to create a directory on the server to receive the dump files.
The schema that will be running DataPump needs read/write privileges on the directory.
You can disconnect/ reconnect from/to a data pump job without stopping it.
You can connect and check the status of a running Data Pump job
A data pump job can be restarted if it fails.

4. Views

You can check the existence of a directory in the dba_directories view.
You can check the privileges that a user has on the directory in the dba_tab_privs view. The directory name is found in the table_name field.

5. Architecture

The major components of Data Pump are:
- Master Table – holds the job info while the job is running.
- Processes – including:
  - Master process – controls execution of the job
  - Client process – the expdp & impdp
  - Shadow process – Creates the Master table & the AQ queues.
  - Worker processes – does the actual load & unload

There are 8 (or maybe 42) more things that I know about and would love to be able to talk about without stumbling and sounding like an idiot! More posts to follow.

I passed the 10g OCP exam. Three Cheers!

2008-01-06T23:45:00.000-08:00

Yesterday I passed the Oracle 10g New Features for DBA’s certification exam. Three cheers!!!

When I walked out of the exam center I found myself saying, “I have to hurry home to study for the exam.” Then I realized, I don’t have to study this information now. I passed the test. I can now start looking at all of the interesting things that have been passing by in the blogs. There are so many topics that I would like to know something about. Now I can begin to learn.

Getting Started

2007-12-27T23:48:00.000-08:00

One of the best ways to learn a subject is to teach the subject to someone else. To that end I shall write about the Oracle topics that I am attempting to learn. If I suceed in discussing a topic in a sufficiently coherent manner that I actually help someone else, then I shall be pleased.

Girlgeek's Oracle Blog

Composite Interval Partitioning isn't as advertised.

That's a whole lot of partitions!

It's wonderful, but will I survive?

A trivial index demo

OOW any free Discover tickets around?

Making problems for myself

Lazy Log Writer - Vindication & thank you Fitaloon

Delete vs. Truncate - graphically

Lazy Log Writer - The non Resolution

A lazy log writer

OOW - Day 2 - Simple things

OOW - Day 1 - Blogger's perks.

travelling trace file

NoCOUG conference 8/21/08

Summer Conference 2008

Hosted by Chevron

August 21, 2008

at Chevron, San Ramon, CA

Mark your calendar for NoCOUG's Fall Conference: November 13, 2008 at CarrAmerica Conference Center in Pleasanton.

Make it go away! (rman delete)

Something is VERY busy

I'm beginning to distrust Oracle

Q.E.D.

Lessons Learned after a Hell Weekend

Northern California Oracle Users' Group Conference

SPRING CONFERENCE 2008

May 15, 2008

at the Crowne Plaza Hotel in Foster City, CA

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

Moving back to a lost spfile

Semafore? Cannot mount in Exclusive.

Exposed Password in Data Pump

When all else fails, follow instructions.

Trying to lose a datafile.

Moving files in Mount State

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

Startup steps review

Getting Biblical

Real Application Testing - RATs!!

MATERIALIZED VIEWS

The magical MIT drawing/motion simulator.

Northern California - NoCOUG conference 2/19/08

Winter Conference 2008

Hosted by Oracle

February 19, 2008

at Oracle Corporation, Redwood Shores, CA

Mark your calendar for NoCOUG's Spring Conference: Thursday, May 15, 2008 at the Crowne Plaza hotel in Foster City.

Five things about Data Pump.

I passed the 10g OCP exam. Three Cheers!

Getting Started

Mark your calendar for NoCOUG's Fall Conference:
November 13, 2008 at CarrAmerica Conference Center in Pleasanton.

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

Mark your calendar for NoCOUG's Spring Conference:
Thursday, May 15, 2008 at the Crowne Plaza hotel in Foster City.