Getting Biblical

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!!

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

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.

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

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

Copyright © 2008 NoCOUG. All rights reserved.

Five things about Data Pump.

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

1. Data Pump is a utility that moves data and metadata.

2. To the user it looks like export/import, but they are separate utilities and their files are not compatible.

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

1. Export dumps are a bunch of DDL and INSERT statements.

2. DataPump dump files in binary format - very similar to the format stored in Oracle database datafiles inside of tablespaces.

3. Usage

1. You may export/import tables, schema, or the whole database

2. You may export/import just metadata, or metadata and data

3. You may run Data Dump export in Estimate Only mode to determine the amount of space that will be required for the dump file.

4. You need to create a directory on the server to receive the dump files.

5. The schema that will be running DataPump needs read/write privileges on the directory.

6. You can disconnect/ reconnect from/to a data pump job without stopping it.

7. You can connect and check the status of a running Data Pump job

8. A data pump job can be restarted if it fails.

4. Views

1. You can check the existence of a directory in the dba_directories view.

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

1. 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!

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.