DBA Blogs

Here's another question that was submitted during the OpenWorld Optimizer Roundtable. It's a common question that we've discussed a little bit in a couple other posts, but we wanted to summarize everything in one place.

First, let’s quickly review what the different values for the cursor_sharing parameter mean. We discussed this behavior in some detail in an earlier post about cursor_sharing. Below is a summary of the behavior of the different values in different cases (copied from the earlier post):

CURSOR_SHARING VALUESPACE USED IN SHARED POOLQUERY PERFORMANCE EXACT (No literal replacement)Worst possible case - each stmt issued has its own parent cursorBest possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt FORCEBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan SIMILAR without histogram presentBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that planSIMILAR with histogram presentNot quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space)Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt
Adaptive cursor sharing (ACS) is another feature we've blogged about before, which allows the optimizer to generate a set of plans that are optimal for different sets of bind values. A common question is how the two interact, and whether users should consider changing the value of cursor_sharing when upgrading to 11g to take advantage of ACS. The simplest way to think about the interaction between the two features for a given query is to first consider whether literal replacement will take place for a query. Consider a query containing a literal:

select * from employees where job = 'Clerk'

As we see from the table above, the treatment of this query by literal replacement will depend on the value of the cursor_sharing parameter and whether there is a histogram on the job column. Here are the interesting cases:

1. Cursor_sharing = exact. No literal replacement will take place, and the optimizer will see the query as is.


2. Cursor_sharing = force. Whether there is a histogram or not, literal replacement will take place, and the optimizer will optimize the query as if it were:
   
   select * from employees where job = :b
   
   Bind peeking will take place, so that the value "Clerk" is used to generate cardinality estimates for the query. Subsequent executions of this query differing only in the literal value will share the same plan.
   


3. Cursor_sharing = similar. There are two different cases for this:
   
   
   
   1. There is a histogram on the job column. In this case, literal replacement will not take place. The presence of a histogram indicates that the column is skewed, and the optimal plan may depend on the literal value. Hence, the optimizer sees the query as:
      
      select * from employees where job = 'Clerk'
      
      and subsequent executions with a different literal will not necessarily use the same plan.
   
   
   2. There is no histogram on the job column. This indicates that the column is not skewed, and the optimizer is likely to choose the same plan no matter the literal, so literal replacement takes place.
   
   

Now that we know when literal replacement will take place, and what the query looks like to the optimizer, we can consider adaptive cursor sharing. If literal replacement takes place, and the query that the optimizer optimizes contains a bind, then adaptive cursor sharing can take place. To adaptive cursor sharing, a bind variable is a bind variable, whether it comes from the user query or is inserted by literal replacement. On the other hand, if the query contains only literals (no binds), adaptive cursor sharing will not take place. In our example above, adaptive cursor sharing can be considered for cases 2 and 3.2. For case 3.2, it is likely that the optimizer will choose the same plan for different values of the literal. In case 2, if there is a histogram, then the optimizer may choose different plans depending on how popular the literal value is.

This example shows that if you use histograms, and want the optimizer to choose an optimal plan for different literal values using ACS, then you should set cursor_sharing to force. If it is set to similar, then literal replacement will not take place, and a child cursor will be created for each value of the literal. Setting cursor_sharing to similar effectively disables ACS for these kinds of queries. By setting cursor_sharing to force and letting adaptive cursor sharing kick in, the optimizer can choose optimal plans for different values, but if the same plan is appropriate for several values, they will share a single child cursor. Historically, cursor_sharing=similar has been recommended as a middle ground between no literal replacement (which causes a lot of cursors to be generated) and forced literal replacement (which causes a potentially sub-optimal plan to be shared for all literals). We now recommend using adaptive cursor sharing along with cursor_sharing=force instead.

So far we have only discussed cursor sharing in the presence of histograms. There are other cases where the optimizer's choice of plan can depend on the specific literal that appears in the query, for instance when binds appear in range predicates or when a bind value falls outside of a column's range (according to the optimizer statistics). Binds appearing in these kinds of predicates are also considered by adaptive cursor sharing, whereas they are not considered by cursor_sharing=similar.

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. Doug’s Oracle Blog has a series of posts about Hotsos. If all this talk about conferences has gotten you excited, Joshua Drake notes that 14 days and the hotel is almost full for postgresql conference east which is March 25th-28th in Philadelphia. And the Oracle database insider notes that the Oracle OpenWorld call for papers is now open.

According to Susan Visser this week (ending tomorrow) is also read an e-book week. So if you have not already done so, read an e-book! She links a coupon for an e-book in the post.

Craig Mullins notes that the mainframe is a good career choice in Mainframes: The Safe IT Career Choice. He notes that the mainframe is still not dead:

People having been predicting the death of the mainframe since the advent of client/server in the late 1980s. That is more than 20 years! Think of all the things that have died in that timespan while the mainframe keeps on chugging away: IBM’s PC business, Circuit City, Koogle peanut butter, public pay phones, Johnny Cash… the list is endless.

In other career-related news, Antonio Cangiano is looking for [2] top-notch student hackers for a 16-month internship at IBM in Toronto starting in May. All the details, including how to apply, are in Cangiano’s blog post.

Willie Favero wants to know how you “solve the batch dilemma” for issues like “shrinking your batch window, designing your batch to play nicely with … OLTP” in how’s your batch workload doing? Perhaps Favero should read the updated batch best practices posted by Anthony Shorten.

Bryan Smith surveys a more personal question by asking if you go both ways and “manage both DB2 for Linux, UNIX, and Windows and DB2 for z/OS” in don’t ask, don’t tell, bi-platform DBAs. This week’s Log Buffer editor admits to being a tri-platform DBA — she has tried many platforms, and in fact, many databases (MySQL, Oracle, DB2, SQL Server, Sybase, Postgres and Ingres)!

Hari Prasanna Srinivasan promotes a patching survey in Oracle really wants to hear from you! Patching Survey.

Henrik Loeser explains what a deadlock and a hot spot are by using a real life analogy taken from a police report in deadlock and hot spot in real life.

Jamie Thomson asks why do you abbreviate schema names?. Shlomi Noach tries to solve the issue that “there is no consistent convention as for how to write [about table aliases in] an SQL query” in proper sql table alias use conventions. Noach also gives us a tip: faster than truncate.

Leons Petrazickis reminds us that “rulesets are chains” and it is important to have your rulesets in the proper order in iptables firewall pitfall.

Anyone interested in the history of MySQL AB will be informed after reading Dries Buytaert’s article.
Gavin Towey shares his software that helps centrally manage 120 MySQL servers in qsh.pl: distributed query tool For those who want to learn more about column-oriented databases, particularly in MySQL, Robin Schumacher of the InfiniDB blog announces that there is a MySQL University session recording on MySQL column databases now available. MySQL join-fu expert Jay Pipes has moved his blog to www.joinfu.com and starts with An SQL Puzzle and of course a follow up on the sql puzzle.

Ivan Zoratti is happy that finally, slides posted for the MySQL DW breakfast. Venu Anuganti gives you tips on one of the most common MySQL frustrations: optimizing subqueries in how to improve subqueries derived tables performance. Justin Swanhart posts the way in which he Gets Linux performance information from your MySQL database without shell access and emulates a ‘top’ CPU summary using /proc/stat and MySQL using the same method.

The Oracle Apps blog has an introduction to Oracle user productivity kit (UPK). Even though in this editor’s opinion the article is very sales-pitchy, it has valuable information, and does indeed live up to its promise:

UPK is a software tool that can capture all the steps in a system process. It records every keystroke, every click of the mouse, each menu option chosen and each button pressed. All this is done in the UPK Recorder by going through the transaction and pressing “printscreen” after every user action. From this, without any further effort from the developer, UPK builds a number of valuable outputs.

Allen White gives a great tip on how to optimize queries in keep your data clean.

Mike Dietrich reminds you to remove “old” parameters and events from your init.ora when upgrading, “as keeping them will definitely slow down the database performance in the new release.” He shows evidence of slowness when this is not done. Dietrich also shows how you can be gathering workload statistics “to give the optimizer some good knowledge about how powerful your IO-system might be”, especially “a few days after upgrading to the new release…while a real workload is running.”

Brian Aker shows the exciting features coming soon in Drizzle in Drizzle, Cherry, Roadmap for our Next Release.

Maybe you are thinking of migrating, not upgrading…..The O’Reilly Radar shows how to asses an Oracle to MySQL migration in MySQL migration and risk management. Actually, that article interviews Ronald Bradford on the subject — Bradford has been prolific lately, updating free my.cnf advice series and “Don’t Assume”: MySQL for the Oracle DBA series. Nick Quarmby also talks about migrating Oracle, but not to a new database, just to a new platform, in his primer on migrating Oracle Applications to new platforms. And the big news comes from Carlos of dataprix that Twitter will migrate from MySQL to Cassandra DB.

Paul S. Randal explains his way of benchmarking: 1 Tb table population on SQL Server.

Pete Finnigan shares his slides from a webinar on how to secure oracle, and Denis Pilipchuk shares his approaches for discovering security vulnerabilities in software applications.

Jeff Davis shares his thoughts about scalability and the relational model. Robert Treat responds actually, the relational model doesn’t scale and Baron Schwartz counters with NoSQL doesn’t mean non-relational.

Buck Woody explains “whenever you want to know something about SQL Server’s configuration, whether that’s the Instance itself or a database, you have a few options” — and of course what those options are — in system variables, stored procedures or functions for meta data.

This week’s T-SQL Tuesday topic was I/O. There are many links to great blog posts in the comments; three random posts I chose to highlight: Michael Zilberstein talks about IO capacity planning, while Kalen Delaney talks about using STATISTICS IO in I/O, you know, and Merrill Aldrich chimes in with information on real world SSD’s. Aldrich also begs folks not to waste resources and make more work for developers and DBAs in dear ISV, you’re keeping me awake nights with your VARCHAR() dates.

And we end with a bit of fin: Paul Nielsen wants us all to have a bit of fun; he has posted an SQL limerick and asks readers to create there own in there once was in Dublin a query.

TEST @dummy1> insert into test1 values (1);

1 row created.

TEST @dummy1> insert into test1 values (2);

1 row created.

TEST @dummy1> select rowid as therowid, id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from test1 where id <= 2;

THEROWID ID OBJID FILENUM BLOCKNUM ROWSLOT
------------------ ---------- ---------- ---------- ---------- ----------
AAAM2dAAEAAAABIAAA 1 52637 4 72 0
AAAM2dAAEAAAABIAAB 2 52637 4 72 1


TEST @dummy1> select dbms_rowid.rowid_create(1,52637,4,72,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAM2dAAEAAAABIAAA

TEST @dummy1> select * from test1 where rowid = dbms_rowid.rowid_create(1,52637,4,72,0);

ID
----------
1

# create the password file with nosysdba=n
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=n

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:02:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy>

# create the password file with nosysdba=y
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=y

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:04:33 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

# test password file authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus sys/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Enter user-name:

# test dictionary authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus system/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYSTEM @dummy>

# test local OS authentication as sysdba

[dummy1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:58 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy1>

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:

• Denormalization
  
• Eventual Consistency
  
• Schema-Free
  
• Horizontal Scale
  

NoSQL tries to scale (more) simply, it is starting to go mainstream – NY Times, BBC, SourceForge, Digg, Sony, ShopWiki, Meebo, and more. But it’s not *entirely* mainstream, it’s still hard to sell due to compliance and other reasons.

NoSQL has gotten very popular, lots of blog posts about them, but they reach this hype peak and obviously it can’t do everything.

“NoSQL is a (growing) collection of tools, not a new way of life.”

What is NoSQL? Can be several things:

• Key-Value Stores
  
• Document Databases
  
• Column-oriented data stores
  
• Graph Databases
  

Key-Value Stores
memcached is a “big hash in the sky” – it is a key value store. Similarly, NoSQL key-value stores “add to that big hash in the sky” and store to disk.

Speaker’s favorite is Redis because it’s similar to memcached.

• key-value store + datatypes (list, sets, scored sets, soon hashes will be there)
  
• cache-like functions (like expiration)
  
• (Mostly) in-memory
  

Another interesting key-value store is Riak

• Combination of key-value store and document database
  
• heavy into HTTP REST
  
• You can create links between documents, and do “link walking” that you don’t normally get out of a key-value store
  
• built-in Map Reduce
  

Map Reduce:

• Massively parallel way to process large datasets
  
• First you scour data and “map” a new set of dataM
  
• Then you “reduce” the data down to a salient result — for example, map reduce function to make a tag cloud: map function makes an array with a tag name and a count of 1 for each instance of that tag, and the reduce tag goes through that array and counts them…
  
• http://en.wikipedia.org/wiki/MapReduce
  

Other key-value stores:

• Tokyo Cabinet
  
• Dynomite
  
• memcachedDB
  
• Voldemort
  

Document Databases
Some say that it’s the “closest” thing to real SQL.

• MongoDB – Document store that speaks BSON (Binary JSON, which is compact). This is the speaker’s favorite because it has a rich query syntax that makes it close to SQL. Can’t do joins, but can embed objects in other objects, so it’s a tradeoff


• Also has GridFS that can store large files efficiently, can scale to petabytes of data
  
• does have MapReduce but it’s deliberate and you run it every so often.
  
• CouchDB
  
  • Pure JSON Document Store – can query directly with nearly pure javascript (there are auth issues) but it’s an interesting paradigm to be able to run your app almost entirely through javascript.
    
  • HTTP REST interface
    
  • MapReduce only to see items in CouchDB. Incremental MapReduce, every time you add or modify a document, it dynamically changes the functions you’ve written. You can do really powerful queries as easy as you can do simple queries. However, some things are really complex, ie, pagination is almost impossible to do.
    
  • Intelligent Replication – CouchDB is designed to work with offline integration. Could be used instead of SQLite as the HTML5 data store, but you need CouchDB running locally to be doing offline stuff w/CouchDB
    
  
  

Column-oriented store
Columns are stored together (ie, names) instead of rows. Lets you be schema-less because you don’t care about a row’s consistency, you can just add a column to a table very easily.

• Cassandra – Built by Facebook, also used by Twitter
  
• BigTable
  
• Hypertable
  
• HBase
  

Graph Databases
speaker’s opinion – there aren’t enough of these.
Neo4J – can handle modeling complex relationships – “friends of friends of cousins” but it requires a license.

When should I use this stuff?
If you have:Use
Complex, slow joins for an “activity stream”Denormalize, use a key-value store.
Variable schema, vertical interactionDocument database or column store
Modeling multi-step relationships (linkedin, friends of friends, etc)Graph

Don’t look for a single tool that does every job. Use more than one if it’s appropriate, weigh the tradeoffs (ie, don’t have 7 different data stores either!)

NoSQL solves real scalability and data design issues. But financial transactions HAVE to be atomic, so don’t use NoSQL for those.

A good presentation is http://www.slideshare.net/bscofield/the-state-of-nosql.

Using SQL and NoSQL together
Why? Well, your data is already in an SQL database (most likely).

You can blend by hand, but the easy way is DataMapper:
Generic, relational ORM (adapters for many SQL dbs and many NoSQL stores)
Implements Identity Map
Module-based inclusion (instead of extending from a class, you just include into a class).

You can set up multiple data targets (default is MySQL, example sets up MongoDB too).
DataMapper is:

• Ultimate Polyglot ORM
  
• simple r’ships btween persistence engines are easy
  
• jack of all, master none
  
• Sometimes perpetuates false assumptions –
  
• If you’re in Ruby, your legacy stuff is in ActiveRecord, so you’re going to have to rewrite your code anyway.
  

Speaker’s idea to be less generic and better use of features of each data store – Gloo – “Gloo glues together different ORMs by providing relationship proxies.” this software is ALPHA ALPHA ALPHA.

The goal is to be able to define relationships on the terms of any ORM from any class, ORM or not
Right now – partially working activeRecord relationships
Is he doing it wrong? Is it a crazy/stupid idea? Maybe.

Example:
NeedUse
Assume you already have an auth systemit’s already in SQL, so leave it there.
Need users to be able to purchase items from the storefront – Can’t lose transactions, need full ACID complianceuse MySQL.
Social Graph – want to have activity streams and 1-way and 2-way relationships. Need speed, but not consistencyuse Redis
Product Listings — selling moves and books, both have different properties, products are pretty much non-relationaluse MongoDB

He wrote the example in about 3 hours, so integration of multiple data stores can be done quickly and work.

Most of this stuff is not PHP specific, and Python or Ruby or Java or .NET developers can use the tools in this talk.

The session on joind.in, with user comments/feedback, is at http://joind.in/talk/view/1320.

Slides are at http://talks.php.net/show/confoo10

“My name is Rasmus, I’ve been around for a long time. I’ve been doing this web stuff since 1992/1993.”

“Generally performance is not a PHP problem.” Webservers not config’d, no expire headers on images, no favicon.

Tools: Firefox/Firebug extension called YSlow (developed by yahoo) gives you a grade on your site.

Google has developed the Firefox/Firebug pagespeed tool.

Today Rasmus will pick on wordpress. He checks out the code, then uses Siege to do a baseline benchmark — see the slide for the results.

Before you do anything else install an opcode cache like APC. Wordpress really likes this type of caching, see this slide for the results. Set the timezone, to make sure conversions aren’t being done all the time.

Make sure you are cpu-bound, NOT I/O bound. Otherwise, speed up the I/O.

Then strace your webserver processs. There are common config issues that you can spot in your strace code. grep for ENOENT which shows you “No such file or directory” errors.

AllowOverride None to turn off .htaccess for every directory, just read settings once from your config file….(unless you’re an ISP).

Make sure DirectoryIndex is set appropriately, watch your include_path. All this low-hanging fruit has examples on the common config issues slide.

Install pecl/inclued and generate a graph – here is the graph image (I have linked it because you really want to zoom in to the graph…)

In strace output check the open() calls. Conditional includes, function calls that include files, etc. need runtime context before knowing what to open. In the example, every request checks to see if we have the config file, once we have config’d we can get rid of that stuff. Get rid of all the conditionals and hard-code “include wp-config.php”. Examples are on the slide.

His tips to change:
Conditional config include in wp-load.php (as just mentioned)
Conditional did-header check in wp-blog-header.php
Don’t call require_wp_db() from wp-settings.php
Remove conditional require logic from wp_start_object_cache

Then check strace again, now all Rasmus sees is theming and translations, which he decided to keep, because that’s the good benefit of Wordpress – Performance is all about costs vs. flexibility. You don’t want to get rid of all of your flexibility, but you want to be fast.

Set error_reporting(-1) in wp-settings.php to catch all warnings — warnings slow you down, so get rid of all errors. PHP error handling is very slow, so getting rid of errors will make you faster.

The slide of warnings that wordpress throws.

Look at all C-level calls made, using callgrind, which sits under valgrind, a CPU emulator used for debugging. See the image of what callgrind shows.

Now dive into the PHP executor, by installing XDebug.

Check xhprof – Facebook open sourced this about a year ago, it’s a PECL extension. The output is pretty cool, try it on your own site, Rasmus does show you how to use it. It shows you functions sorted by the most expensive to the least expensive.

For example, use $_SERVER[REQUEST_TIME] instead of time(). Use pconnect() if MySQL can handle the amount of webserver connections that will be persistent, etc.

After you have changed a lot of the stuff above, benchmark again with siege to see how much faster you are. In this case there is not much gained so far.

So keep going….the blogroll is very slow — Rasmus gets rid of it by commenting out in the sidebar.php file. I’d like to see something to make it “semi-dynamic” — that is, make it a static file that can be re-generated, since you might want the blogroll but links are not changed every second…..

At this point we’re out of low-hanging fruit.

HipHop is a PHP to C++ converter & compiler, including a threaded, event-driven server that replaces apache. Rasmus’ slide says “Wordpress is well-suited for HipHop because it doesn’t have a lot of dynamic runtime code. This is using the standard Wordpress-svn checkout with a few tweaks.”

Then, of course, benchmark again.

The first time you compile Wordpress with HipHop, you give it a list of files to add to the binary, it will complain about php code that generate file names, so you do have to fix that kind of stuff. There’s a huge mess of errors the first time you run it (”pages and pages”), and Rasmus had to patch HipHop (and Wordpress) but the changes in HipHop have been put back into HipHop, so you should be good for the most part.

Check out the errors, lots of them show logical errors like $foo.”bar” instead of $foo.=”bar” and $foo=”bar” instead of $foo==”bar” in an if statement. Which of course is nice for your own code, to find those logical errors.

(Wordpress takes in a $user_ID argument and immediately initializes a global $user_ID variable, which overwrites the argument passed in, so you can change the name of the argument passed in….)

You can also get rid of some code, things that check for existence of the same thing more than once. So it will take a bit of tweaking, but it’s worth it.

There are limitations to HipHop, for example:

• It doesn’t support any of the new PHP 5.3 language features
  
• Private properties don’t really exist under HipHop. They are treated as if they are protected instead.
  
• You can’t unset variables. unset will clear the variable, but it will still be in the symbol table.
  
• eval and create_function are limited
  
• Variable variables $$var are not supported
  
• Dynamic defines won’t work: define($name,$value)
  
• get_loaded_extensions(), get_extension_funcs(), phpinfo(), debug_backtrace() don’t work
  
• Conditional and dynamically created include filenames don’t work as you might expect
  
• Default unix-domain socket filename isn’t set for MySQL so connecting to localhost doesn’t work

and HipHop does not support all extensions — see the list Rasmus has of extensions HipHop supports.

Then Rasmus showed an example using Twit (which he wrote) including the benchmarks. He shows that you can see what’s going on, like 5 MySQL calls on the home page and what happens when you don’t have a favicon.ico (in yellow).

In summary, “performance is all about architecture”, “know your costs”.

Be careful, because some tools (like valgrind and xdebug) you don’t want to put it on production systems, you could capture production traffic and replay it on a dev/testing box, but “you just have to minimize the differences and do your best”.

ODTUG Kaleidoscope 2010, June 27 - July 1st Washington DC is a great conference for Oracle developers and architects, offering the best content by renowned experts. We will have the privilege of delivering two Optimizer sessions this year, 'Explaining the Explain plan' and 'Finally Plan Stability during Database Upgrade with SQL Plan Management'. In the Explain the Explain plan session we will discuss each aspect of an execution plan (from selectivity to parallel execution), explain what information you should be getting from the plan, and how it affects the execution. While in the SQL Plan Management session, we will give detailed instructions on how to capture your existing execution plans before you upgrade to 11g, as well as an in-depth discussion on what to expect from the Optimizer after you upgrade to 11g.
ODTUG is a great conference where you can learn lots in a fun and casual atmosphere. Looking forward to seeing some of you there!

subtitle: Monetizing Social Media

Why is social media and social networking essential to you and your business? (because it will drive sales, but there’s very few analytics for ROI on social networking and social media)

Relying on advertising is no longer working for print newspapers and television. So why do we think it will work on internet media?

Blogging — you must post 2-4 quality blog posts every week to maintain readership. This takes a lot of work! Content is king.

No matter how cool the technology/product/service is, people still buy more often and more easily from people they know and trust.

Social media is a way to show people that you are an industry expert, and that is how you should use them (not to spam and only say “buy my product”).

If you do not love your job and try to sell it (say, on social networking), you are going to fail, because you are not passionate about it.

Start small, do not promise a lot, it is better to have more to say than to have dead air time (radio analogy).

Social media is all about building good relationships by having good content that people trust.

Lots of people spend a lot of money on their website, but the website is just a vector to show people your content, and the content is the most important thing.

Cross-pollination – I think he means forward on information you learn (like, say, liveblogging!)

Get expert guest bloggers — he did not explain that you can leverage the relationships you form by asking them to blog. We do this with the Log Buffers….

How to make money:
sponsorships
white paper composition
paid articles
consulting
adjunct tie-ins to other related venues
branded blogs

I am personally disappointed because I wanted to learn more, and I feel as though Pythian already uses the knowledge presented — we have great exposure through our blog, and have started really using Twitter, Facebook and other social media sites, etc.

Hansen’s information was good, and absolutely 100% correct, but I felt that for me it was very basic. I would like to know some more advanced topics, like:
- How do you know when you have reached the tipping point?
- How do you convert anonymous readers/followers to people you know, without turning them away because they feel they’re being watched, spammed or don’t want to give out their info to you?
- When does copy/paste to send out your information start to bother people, how do you know how not to do too much?
- How do you convert readers/followers (anon or not) to paid customers without making them feel like you’re all about $$, what about if you have some free content and some paid content, how do you know how much to have?

What is confoo? It is the sequel to the PHP Quebéc Conference (2003 – 2009). This year PHP Quebec decided to team up with Montreal-Python, W3Quebéc and OWASP Montréal to produce confoo.

And now, on to Mark Pilgrim of Google speaking on HTML5.

Timeline
1991 – HTML 1
1994 – HTML 2
1995 – Netscape discovers web, ruins it
1996 – CSS1 + JavaScript
1996 – Microsoft discovers web, ruins it
1997 – HTML4 + EMCAScript1
1998 – CSS2 + EMCAScript2 + DOM1
2000 – XHTML1 + EMCAScript3 + DOM2
2001 – XHTML 1.1
[long break!]
2009 – HTML 5 + ECMA5 + CSS 2.1

HTML5 is not a spec, it’s a marketing term. It’s really HTML5 + CSS3 + JavaScript.

IsHTML5ReadyYet.com and IsHTML5Ready.com are both real websites that give different answers to the question “is HTML 5 ready?”

Semantics
HTML started as a semantic language (until Netscape came along).

New elements (html tags) that do not do anything – they are for semantic use only:

<header> <footer>
<section>
<article>
<nav>
<aside> (pull quotes and such)
<time> (datetime markup)
<mark> (marking up runs of text)
<figure> <figcaption>

Instead of “div class=_____” use these tags….for example:

<body>
  <header>
    <hgroup>
    <h2>page title</h2>
    <h3>page subtitle</h3>
    </hgroup>
   </header>

<nav>
  <ul> Navigation......
     .....
  </ul>
</nav>

<section>
  <article>
   <header>
    <h2>Title</h2>
   </header>
</section>

Caveat: This doesn’t work in IE but there is a workaround…..

This can help blind people navigate better….and bots too!

“Google is just another blind web user with 7 million friends”

Forms
Web forms 2.0
To make a slider from 0-50:

<input type='range' mix='0' max='50' value='0'></input>

To use autofocus:

<input autofocus>

(works in 3 browsers)

Talking about blind users again: “Focus tracking is VERY important if you can’t see. You really need to know where on the page you are, if you start typing what will happen.”

Placeholder text — in a text box, that light text that goes away when you click:

<input type='text' placeholder='click here and this will disappear'>

(works in 2 browsers)

New input types
These are semantic types, do different things in different browsers

<input type='email'> (on the iphone you get a different keyboard, by default you just get a textfield, so these things degrade gracefully if the browser does not support the feature)
<input type='url'> (a browser like <A HREF="http://www.opera.com">Opera</A> can validate a URL for you instead of you doing it yourself!)
<input type='datetime'> (and more...date pickers are tedious)
<input type='file' multiple> (multiple files without using flash!)

For all the inputs HTML5 supports and which browsers support them (Opera is leading the way) search for “HTML5 input support”

Accessibility
ARIA = “accessible rich internet applications”. Alt-text is technology that’s long behind. ARIA does stuff like making tree views accessible. For example, right now with a tree view you have to tab through each item, which is a pain. With code like this:

<ul id='tree1' role='tree' tabindex='0' aria-labelledby='label_1'>
  <li role='treeitem' tabindex='-1' aria-expanded='true'>Fruits </li>
    <li role='group'>
    <ul>
      <li role='treeitem' tabindex='-1'>Oranges</li>
      <li role='treeitem' tabindex='-1'>Pineapples</li>
    </ul>
   </li>
</ul>

….keyboard users can tab to the treeview itself, then use arrow keys to navigate and spacebar to select. This makes selecting an item at the end of a tree view much easier, and also makes it easy to move beyond the tree view without having to press Tab a million times.

Use your favorite search engine for “ARIA accessibility” to learn more.

CSS
Mark threw this image up on the screen:

(image from http://www.zazzle.com/stevenfrank – on that site you can buy this coffee mug or a T-shirt with the design)

Web fonts finally work in CSS3 – you can use more than Times, Courier, Arial, and occasionally Helvetica. This works EVERYWHERE – Chrome, IE, Firefox, Opera, Safari, etc. Well, it’s true that they all use it, but they all have different fonts they support. Read Bulletproof font face for tips on how to get the font you want no matter what browser is used (yes, even IE).

Opacity is easy [author's note - it's just the "opacity" element, see examples at http://www.css3.info/preview/opacity/].

Rounded corners are EASY – Mark’s slide passed too fast for me, so I grabbed an example from http://24ways.org/2006/rounded-corner-boxes-the-css3-way:

.box {
  border-radius: 1.6em;
}

Gradients are easy [author's note -- looks like you need webkit, there's examples at http://gradients.glrzad.com/]

To test CSS3 stuff, use www.css3please.com – “This element will receive inline changes as you edit the CSS rules on the left.”

[Author's note -- while searching I found http://www.webappers.com/2009/08/10/70-must-have-css3-and-html5-tutorials-and-resources/ which is definitely a "must have".]

Canvas
A canvas is a blank slate where you can draw whatever you want, use the canvas tag and id, width and height attributes, everything else is javascript. Pretty awesome. [Author's note -- Mark had examples but I did not have time to capture them. I did find a nice tutorial at https://developer.mozilla.org/en/Canvas_tutorial.]

Multimedia
Video with no flash! YouTube has HTML5 integration. Here’s sample code of how to do movies in HTML5:

<video src='movie.ogv' controls></video>
<video src='movie.ogv' loop></video>
<video src='movie.ogv' preload='none'></video>  -- don't preload the movie
<video src='movie.ogv' preload='auto'></video>
<video src='movie.ogv' autoplay></video> -- if you don't have this you don't do evil autoplay....

Multimedia is in the DOM and responds to CSS effects, such as reflection:

<video src='movie.ogv' loop style='webkit-box-reflect: below 1px;'></video>

(this code might be wrong, the slide flipped fast)

Of course the problem — codecs. Right now, .ogv and .mp4 (h264).

Audio inline too, same problem — only .oga and .mp3:

<audio src ='podcast.oga' controls></audio>

Geolocation
IsGeolocationPartofHTML5.com is a real site, go to it to get the answer.
Geolocation demos — very much the same, find your location and display it. Simple but cool.

Cache manifest
Get everything you need for offline usage…

<html manifest='another-sky.manifest'>

CACHE MANIFEST
/avatars/zoe.png
/avatars/tamara.png
/scripts/holoband.jpg

search for “google for mobile HTML5 series” – good series of articles on using this stuff.

HTML 5 has much more
Local storage
Web workers
Web sockets (2way connections, like raw tcp/ip cxns over the web)
3D canvas (webgl)
Microdata (enhanced semantics)
Desktop notifications
Drag and Drop

Learn more:
whatwg.org/html5
diveintohtml5.org

Here’s the bottom line to my last post It Was a Dark and Stormy Night (A Story About Deadlocks): Oracle Database handles deadlocks differently than other database engines such as SQL Server and DB2. The transaction of the selected victim is not rolled back; only its last statement is rolled back. All the other participants in the [...]

Video fragments of my session posted at the end — read on.

I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.

Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!

I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)

Doug has made my day — while he provided lots of ideas and feedback on few things that I was lucking, he generally approved the idea and confirmed that it wasn’t totally crazy. I guess that was all I needed back then and Doug knew how nervous I was about it. (Thanks mate!)

So I called Sunday a day very early and went to bed before midnight. I really needed some sleep. Woken up by the alarm at 5AM (I woke up few times during the night looking at the clock — making sure I didn’t sleep through) and slides were ready just before lunch. I even managed to do a test run and it took 65 minutes — a wee bit too long for one hour session. But it was good test and I knew I had to be just a bit more concise in few parts.

Mi morning was very productive. Unfortunately, I missed the opening keynote from Tom Kyte. Such a pity! If what Doug wrote is true, Tom was talking about the mistakes we make *because* of our experience and our assumptions. This was exactly one of the points I was making in my Battle Against Any Guess — experience is danger. I wish I could see Tom’s example. Oh well, maybe another time.

I managed to attend half of the Richard Foote’s session on indexes but my mind was far away — with my own slides. Though, I did manage to focus on bitmap indexes part and the myth of bitmap indexes not working well for columns with high cardinality. Very interesting conclusions. I’m still wondering how much overhead updates will do to such bitmap index.

After lunch, it was my turn. I ordered few copies of the latest OakTable book — Expert Oracle Practices: Oracle Database Administration from the Oak Table — that I co-authored with the bunch of other Oakies. I contributed chapter 1 in the book titled just like my presentation — Battle Against Any Guess. The plan was to give a copy away during the presentation and do a draw for another one at the end of the session. I was so nervous that I forgot about it until the end of the session so I just did a draw for two copies. The lucky winners were Lynn-Georgia Tesch and Surendra Anchula. Congratulations! For the rest of you who left the contact details — please stay tuned and we’ll organize few things online.

Now the main topic of this post — my presentation. What’s unusual about this session is that it’s not some technical stuff that I usually do but a more conceptual and motivational talk. Could I pull it off? Well, I think it went fairly well in general even though I did identify few rough places and my lack of English language mastering. Might need to work a little bit more on the flow of the presentation.

We had quite a few good laughs. Later, people in the next hall were asking about it and Dan was making the jokes on the stage so it must have been loud. Anyway, I think nobody fell asleep and I managed to get people thinking about the topic. I received many “thank you” notes yesterday and compliments on a good session so by the end of the day I was more and more pleased. Thanks everyone for attending and especially big thanks to those of you who brought to my attention examples from their own battles. If you have more to discuss — contact me by email (my last name) {at} pythian.com.

Thanks to Marco Gralike for recording some fragments and sharing them. I think he has more to come.

This is the introductory couple minutes. You can definitely notice how nervous I am starting on the stage:

Solving the wrong problem example:

That’s all for now. Stay tuned — more to come.

If you do not know what International Women’s Day is: http://www.internationalwomensday.com/

Start planning your blog posts for Ada Lovelace day now (March 24th, http://findingada.com/ Ada Lovelace Day is an international day of blogging (videologging, podcasting, comic drawing etc.!) to draw attention to the achievements of women in technology and science.)

To that end, I would like to point out all the women currently in science and tech fields that I admire and think are doing great things. I think it would be great if everyone, male or female, made a list like this:

The women that have taught me science/tech along the way:

High School:
Mary Lou Ciavarra (Physics)
Maria Petretti (Pre-Algebra, and Academic Decathlon)
Reneé Fishman (Biology)
Lisa Acquaire (Economics during Academic Decathlon)

College:
Professor Kalpana White (Biology), and in whose fruit fly lab I worked for 2 semesters.
Professor Eve Marder (Introductory Neuroscience)

Though Brandeis does have female faculty in the Computer Science department, I did not manage to have any classes with female Computer Science faculty members.

My current female DBA co-workers at Pythian: Isabel Pinarci (Oracle), Michelle Gutzait (SQL Server), Catherine Chow (Oracle) and Jasmine Wen (Oracle).

And to folks in the greater MySQL/tech community and tech co-workers past and present, especially those I have been inspired and helped by: Tracy Gangwer, Leslie Hawthorn, Selena Deckelmann (Postgres), Amy Rich, Anne Cross, and more (If I have forgotten you, I apologize!).

ORA-15477: cannot communicate with the volume driver
when add volume...
SQL> alter diskgroup data add volume 'asm_vol1' size 5G;
alter diskgroup data add volume 'asm_vol1' size 5G
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15477: cannot communicate with the volume driver

SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';
alter diskgroup data set attribute 'compatible.advm'='11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 11.2 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver# acfsutil registry -l
acfsutil registry: CLSU-00100: Operating System function: open64 (/dev/ofsctl) failed with error data: 2
acfsutil registry: CLSU-00101: Operating System error message: No such file or directory
acfsutil registry: CLSU-00103: error location: OOF_1
acfsutil registry: ACFS-00502: Failed to communicate with the ACFS driver. Verify the ACFS driver has been loaded.then load ACFS.
# /u01/app/grid/bin/acfsload start -s
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9322: done.
Try again.
SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';

Diskgroup altered.

SQL> alter diskgroup data add volume 'asm_vol1' size 5G;

Diskgroup altered.

SQL> !ls -l /dev/asm/asm_v*
brwxrwx--- 1 root oinstall 252, 86529 Mar 9 00:11 /dev/asm/asm_vol1-169Written By: Surachart Opun http://surachartopun.com

Use ASM Disk Group NORMAL redundancy (11gR2) and found error while create tablespace.
SQL> create tablespace test datafile '+DATA' size 5G;
create tablespace test datafile '+DATA' size 5G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhaustedWhat Wrong??? - I have free size on "DATA", Check ASM Disk Group.
SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 112233 23437 44398 NORMALCheck (ASM) alert log file.
kfdpDumpBg()
kfdpDumpBg() - DoneCheck on ASM Instance and Investigate the problem.
SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1017 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23211 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14819 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14829 23437 NORMAL
/dev/sdj 1 23437 NORMAL
/dev/sdd 13839 23437 NORMALDisk Group UNBALANCE, then
SQL> alter diskgroup data rebalance power 11;

Diskgroup altered.Check.
SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMALNothing to resolve... Check on v$operation.
SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041
Try to check & repair ASM Disk Group.
SQL> alter diskgroup data check all repair;

Diskgroup altered.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMALNothing to resolve... - "DATA" ASM Disk Group still unbalance, and v$asm_operation show error.
think & make Idea: Move some files or resize some files on "DATA" Disk Group, so login database and make something.
Example:
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.263.712603677' resize 1G;

Database altered.Check on ASM again.
SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1685 23437 NORMAL
/dev/sdb 8322 23437 NORMAL
/dev/sdk 8357 23437 NORMAL
/dev/sdq 22855 23437 NORMAL
/dev/sdf 8324 23437 NORMAL
/dev/sdg 15013 23437 NORMAL
/dev/sdc 14043 23437 NORMAL
/dev/sdi 7386 23437 NORMAL
/dev/sde 15011 23437 NORMAL
/dev/sdj 711 23437 NORMAL
/dev/sdd 14054 23437 NORMALCheck v$asm_operation.
SQL> select group_number, operation, state, power, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL RUNASM instance... "DATA" Disk Group rebalancing, So Wait... and check
SQL> select group_number, operation, state, power, error_code from v$asm_operation;

no rows selectedSQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 10524 23437 NORMAL
/dev/sdb 10524 23437 NORMAL
/dev/sdk 10528 23437 NORMAL
/dev/sdq 10532 23437 NORMAL
/dev/sdf 10521 23437 NORMAL
/dev/sdg 10523 23437 NORMAL
/dev/sdc 10522 23437 NORMAL
/dev/sdi 10526 23437 NORMAL
/dev/sde 10522 23437 NORMAL
/dev/sdj 10522 23437 NORMAL
/dev/sdd 10523 23437 NORMAL

SQL> alter diskgroup data rebalance power 1;

Diskgroup alteredDisks rebalanced, Try to create tablespace again.
SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 115767 12916 51425 NORMAL

SQL> create tablespace test datafile '+DATA' size 30G;

Tablespace created.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 4936 23437 NORMAL
/dev/sdb 4931 23437 NORMAL
/dev/sdk 4931 23437 NORMAL
/dev/sdq 4932 23437 NORMAL
/dev/sdf 4922 23437 NORMAL
/dev/sdg 4933 23437 NORMAL
/dev/sdc 4936 23437 NORMAL
/dev/sdi 4940 23437 NORMAL
/dev/sde 4933 23437 NORMAL
/dev/sdj 4928 23437 NORMAL
/dev/sdd 4938 23437 NORMALWhat this told me? Make Sure ASM Disk Group no problem (REBALANCE).Written By: Surachart Opun http://surachartopun.com

> CREATE CONTEXT my_context using my_context_proc;

Context created.

> create or replace procedure my_context_proc as
begin
dbms_session.set_context('MY_CONTEXT', 'APP_USER', 'ROBERT');
end;
/

Procedure created.

> exec my_context_proc

PL/SQL procedure successfully completed.

> select sys_context('MY_CONTEXT','APP_USER') from dual;

SYS_CONTEXT('MY_CONTEXT','APP_USER')
-------------------------------------
ROBERT

Guy Harrison is the author of the newly released Oracle Performance Survival Guide, MySql Stored Procedure Programming (with Steven Feuerstein, 2006), Oracle SQL High Performance Tuning (2001), and Oracle Desk Reference (1999). He is currently a director of development at Quest Software, where he created the popular Spotlight family of products. He leads a software [...]

I must admit that I rarely use the Oracle SQL Developer because I’m using SQL*Plus, Toad from Quest and PL/SQL Developer from Allround Automations as primary tools for database administration and PL/SQL programming. They’re already providing all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it, but I always find lame excuses not to. One of the excuses is that I thought this is just another new tool doing the same things other tools already could do. We’ll find out at the end of this post that I was wrong. Please read on.

Few weeks ago, I’ve received a non-compensated book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this opportunity to explore this tool by reading through this book. No more procrastination! I’m approaching this book as an eager learner who would like to know more the tool and plan for a long-term use.

Let’s get started.

This book is very well organized introducing Oracle SQL Developer from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concepts.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on the installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to connect and make queries to database. It ends with walking you through the navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e.g., data highlighting or filtering are interesting. It goes on explaining some of Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with demonstrating how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in detail with tool’s main interface, SQL Worksheet. It starts with comparing Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features such as drag-and-drop query builder, formatting code, code completion, etc., though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But if you look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries thus saving your time. Samples are as follow:

• Table with Unindexes Foreign Keys (under Quality Assurance)
• Search Source Code (under Data Dictionary Reports > PLSQL)
• ASH and AWR Reports (under Data Dictionary Reports)
• Grants and Privileges (under Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL code. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working with other tools before. But the book explains this topic very well for those you’ve not experienced working with them before. The feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very cool. The chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard database tuning tools such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler feature which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files
This is one of strengths of Oracle SQL Developer over others because it integrates with open source version control system. I’m really glad that author covers this topic in detail. The version control integration is very nice to have if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work in team development with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer can perform data export and import in various formats including CSV, Fixed, HTML, DML statements, and SQL Loader. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered to be topic-specific. This means that you can choose to read only what interests you.

Chapter 9 -  Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because Oracle SQL Developer can also connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendously beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into detail of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler within SQL Developer.

(More information about Oracle SQL Developer Data Modeler can be found on its site.)

Chapter 11 – Extending SQL Developer
Personally I think this feature sets this tool apart from others. With capability of supporting XML and Java extensions, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 -  Working with Application Express
As an APEX developer, the integration with APEX in Oracle SQL developer is a hidden gem. Now all APEX objects can be easily viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains clearly on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning application’s SQL and PL/SQL codes which are visible through the tool becomes much easier. One of the nice features is an ease of refracting PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing us how to manage applications including importing and exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of this tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for data migration. This includes migration repository preparation and database connection.

Summary
After reading through this book and following most of samples, I’m impressed with varieties of unique features (such as integration with APEX, version control, Data Modeler, etc.) this tool offers. As a matter of fact, I’m already using it in our current APEX development project, and plan to extend its usage more in other areas. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. The book covers some basic concepts filling up the gap for those who are new to accessing Oracle database. But in general, I agree with author that those who are already familiar with Oracle database concepts will get more out of this book.

Thanks to Packt Publishing for book review offer.

References:

Oracle SQL Developer 2.1 by Sue Harper on Packt Publishing site

Oracle SQL Developer page on Oracle site

I must admit that I rarely use the Oracle SQL Developer because the primary tools – Quest Toad and Automation PL/SQL Developer, I’m using for database administration and PL/SQL programming seem to provide all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it out. But I always find lame excuses not to start.

A month ago, I’ve received a book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this great opportunity to explore this tool by reading through this book. I’m approaching this book as an eager learner who would like to know the tool better for a long-term use.

Let’s get started.

This book is very well organized introducing the tool from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concept.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to make the connection and making queries to database. It ends with walking you through navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e,g, data highlighting or filtering are interesting. It goes on explaining some Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with showing how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in details with tool’s main interface, SQL Worksheet. It starts with compare Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features, for example, drag-and-drop query builder, formatting code, code completion, etc. even though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But you can look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries, thus saving your time. Sample are as follow:

Table with Unindexes Foreign Keys (Quality Assurance)

Search Source Code (from Data Dictionary Reports > PLSQL)

ASH and AWR Reports (from Data Dictionary Reports)

Grants and Privileges (from Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL codes. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working in other tools before. But the book explains well for those you’ve not experienced working with those objects before. Feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very interesting. This chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard tuning tool such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files

This is one of strengths of the tools over others because it integrates with open source version control system. I’m really glad the author covers this in details. This version control integration is very nice if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work as a team with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer provides many ways to perform data export and import in a various formats including CSV, Fixed, HTML, DML statements, SQL Loader, etc. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered topic-specific. This means that you can choose to read only what interests you.

Chapter 9 -  Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because it also can connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendous beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into details of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler with SQL Developer.

(more information about SQL Developer Data Modeler at http://www.oracle.com/technology/software/products/sql/datamodeler.html)

Chapter 11 – Extending SQL Developer
Personally I feel this feature might set this tool apart from others. With capability of supporting XML and Java extension, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 -  Working with Application Express
As a APEX developer, the integration with APEX in SQL developer is a gem. Now all APEX objects can be viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains well on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning SQL and PL/SQ which are visible through the tool becomes a lot easier. One of the nice features is an ease to refract PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing  to manage applications including importing/exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of the tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for migration including migration repository preparation and database connection.

After reading through this book and following most of samples, I’m impressed with varieties of features (APEX integration, version control, Data Modeler, etc.) this tool offers. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. Even though some basic concept sections are not brief, those who are new to accessing Oracle database will benefit from those sections. In general, I agree with author that you will get more out of this book if you’re already familiar with Oracle database concepts.

Detail about Oracle SQL Developer 2.1 by Sue Harper

http://www.packtpub.com/oracle-sql-developer-2-1/book

Oracle SQL Developer page on Oracle OTN site http://www.oracle.com/technology/software/products/sql/index.html

Thanks to Packt Publishing for book review offer.

www.PacktPub.com

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

http://www.oracleapplicationexpress.com/tutorials/65-jquery-datepicker

I remembered not quite a long time ago, in order to find out any technical information, it seems like you can to go through a bunch of technical books.

You must subscribe to

But now many technical blog sites pop

This is to programmatically generate a CSV file when a button is clicked
The custom export to CSV

http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html

Session timeout in release 3.1

http://andrew.tulley.co.uk/?p=50

jQuery Calendar Date Picker

http://jqueryui.com/demos/datepicker/

Good document on its configuration

http://docs.jquery.com/UI/Datepicker

How to replace the default APEX calendar with jQuery calendar…

http://roelhartman.blogspot.com/2008/12/how-to-replace-default-apex-calendar.html

jQuery Masked Input
This is a masked input plugin for the jQuery javascript library. It allows a user to more easily enter fixed width input where you would like them to enter the data in a certain format (dates,phone numbers, etc). I

http://digitalbush.com/projects/masked-input-plugin/

jQuery Impromptu is an extention to help provide a more pleasant way to spontaneously prompt a user for input. More or less this is a great replacement for an alert, prompt, and confirm. Not only does it replace these but it also allows for creating forms within these controls. This is not intended to be a modal replacement, just a quick tool to prompt user input in a fashionable way.

jQuery Impromptu

http://trentrichardson.com/Impromptu/index.php

—————————-

Oracle Universal Installer (OUI) did not start when installing OAS 10.1.3.1 on Windows 2003

Last week we installed the OAS 10.1.3.1 on multiple Windows 2003 servers on the VM farm. All went well except the last one. When double-clicking on the setup.exe file, the system verification window popped up. Next I expected the Oracle Universal Installer (OUI) window, but nothing happened. The same installation files and procedure have been used on different Windows systems and have worked fine before. The installation log doesn’t seem to be much help either. We’ve tried all tricks including using a local administrative account, rebooting, etc., but none worked.

After looking through the Oracle support site (using Internet Explorer because the some contents don’t display properly on Fire Fox 3.6), I found this solution in the document ID 308705.1 – OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 which says that the root cause is because the Application Experience Lookup” Service is not started. Once started, the installation proceeds without any issues. The strange thing is that those servers we’ve previously installed it successfully did not have this service up either.

(

OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 [ID 308705.1] Modified 04-JUN-2009     Type PROBLEM     Status PUBLISHED

In this Document
Symptoms
Cause
Solution

Applies to:

Oracle Application Server 10g Enterprise Edition – Version: 9.0.4.0.0 to 10.1.2.3.0
Microsoft Windows Server 2003
Microsoft Windows Server 2003 R2 (32-bit)
Checked for relevance on 04-Jun-2009

Symptoms

When attempting to install Oracle Application Server 10g on Windows 2003, the setup.exe appears to quit even before launching the Oracle Universal Installer (OUI).

Cause

The Oracle Universal Installer (OUI) is not able to start because the “Application Experience Lookup” Service is not started.

Solution

Start the “Application Experience Lookup Service” and then relaunch the Oracle Universal Installer (OUI)

<< OLE Object: Picture (Device Independent Bitmap) >> Related

Products

· Middleware > Application Servers > Oracle Application Server > Oracle Fusion Middleware

Keywords

OAS; ORACLE UNIVERSAL INSTALLER; SETUP.EXE; OUI

It was a dark and stormy night; a coven of database administrators was gathered at the local watering hole, trading “big fish” stories (interlaced, like a concoction of couscous sprinkled with parsley, with the occasional “How I Trashed the Database” story) and quaffing the generous libations poured by the master of the house. Suddenly the frantic [...]

The documentation on the RMAN command RESTORE ... VALIDATE (link to the 10.2 documentation) is likely to be misunderstood. It says "Lets RMAN decide which backup sets, datafile copies, and archived logs need to be restored, and then scans them to verify their contents. No files are restored. Use VALIDATE to verify that the backups required to restore the specified files are intact and usable."
However, the fact of the matter is that a RESTORE DATABASE VALIDATE only checks the BackupSets corresponding to a Database Backup. You must remember that BackupSets corresponding to ArchiveLog Backup(s) are *separate*. These are NOT validated by a RESTORE DATABASE VALIDATE command. Furthermore, ArchiveLogs are required at the RECOVER phase. Therefore, in principle, there is no reason for a RESTORE VALIDATE to check ArchiveLogs as they are not required in the RESTORE phase ! (I know : Many DBAs will not agree with the last sentence !. But it is mportant to realise the difference between RESTORE and RECOVER).

If you want to validate ArchiveLog backups, you must run a separate RESTORE ARCHIVELOG .... VALIDATE command (specifying ALL or FROM ... UNTIL ... clauses for the range of ArchiveLogs).


In this example, I show how a RESTORE DATABASE VALIDATE alone is not sufficient. The DBA may have assumed that it checks for ArchiveLogs as well.


Create an RMAN Backup

ora10204>NLS_DATE_FORMAT=DD_MON_HH24_MI_SS;export NLS_DATE_FORMAT
ora10204>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Mar 7 22:54:29 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORT24FS (DBID=4163910544)

RMAN> backup database plus archivelog;


Starting backup at 07_MAR_22_54_39
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=137 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=68 recid=643 stamp=713055279
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_54_42
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_54_43
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225441_5s7hklr9_.bkp tag=TAG20100307T225441 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07_MAR_22_54_43

Starting backup at 07_MAR_22_54_43
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00002 name=/oracle_fs/Databases/ORT24FS/undotbs01.dbf
input datafile fno=00010 name=/oracle_fs/Databases/ORT24FS/index_tbs_01.dbf
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_54_44
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00009 name=/oracle_fs/Databases/ORT24FS/table_tbs_01.dbf
channel ORA_DISK_2: starting piece 1 at 07_MAR_22_54_44
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_55_49
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp tag=TAG20100307T225444 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_2: finished piece 1 at 07_MAR_22_56_54
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp tag=TAG20100307T225444 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:02:10
Finished backup at 07_MAR_22_56_55

Starting backup at 07_MAR_22_56_55
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=69 recid=645 stamp=713055302
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_56_55
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=73 recid=653 stamp=713055402
input archive log thread=1 sequence=74 recid=655 stamp=713055415
channel ORA_DISK_2: starting piece 1 at 07_MAR_22_56_55
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_56_56
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp tag=TAG20100307T225655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 07_MAR_22_56_56
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvy_.bkp tag=TAG20100307T225655 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=70 recid=647 stamp=713055325
input archive log thread=1 sequence=71 recid=649 stamp=713055333
input archive log thread=1 sequence=72 recid=651 stamp=713055358
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_56_57
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_56_58
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hos0q_.bkp tag=TAG20100307T225655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07_MAR_22_56_58

Starting Control File and SPFILE Autobackup at 07_MAR_22_56_58
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2010_03_07/o1_mf_s_713055418_5s7hotwl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07_MAR_22_57_02

RMAN>  

We can see that the ArchiveLog Sequence# at the beginning of the backup was 68. By the time the backup completed, it had gone to 72. There had been some transactions and log switches (causing new ArchiveLogs) while the Database (i.e. Datafiles) Backup was running.

22:53:58 SQL> show user
USER is "HEMANT"
22:54:41 SQL> create table abc_1 as select * from dba_objects;
 
Table created.

22:54:53 SQL> alter system switch logfile;

System altered.

22:55:06 SQL> create table abc_4 as select * from abc;

Table created.

22:55:20 SQL> alter system switch logfile;

System altered.

22:55:24 SQL> alter system switch logfile;

System altered.

22:55:33 SQL> insert into abc_4 select * from abc;

0 rows created.

22:55:41 SQL> alter system switch logfile;

System altered.

22:55:57 SQL> insert into abc_1 select * from dba_objects;

50745 rows created.

22:56:21 SQL> commit;  
alter s
Commit complete.

22:56:36 SQL> ystem switch logfile;

System altered.

22:56:40 SQL> 
22:56:57 SQL> select l.sequence#, l.status from v$log l order by 1;

 SEQUENCE# STATUS
---------- ----------------
        73 ACTIVE
        74 ACTIVE
        75 CURRENT

22:57:12 SQL>   

To "be safe" we have run another ArchiveLog Backup :

RMAN> backup archivelog all;

Starting backup at 07_MAR_22_58_31
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=68 recid=643 stamp=713055279
input archive log thread=1 sequence=69 recid=645 stamp=713055302
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_58_34
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=70 recid=647 stamp=713055325
input archive log thread=1 sequence=71 recid=649 stamp=713055333
input archive log thread=1 sequence=72 recid=651 stamp=713055358
input archive log thread=1 sequence=73 recid=653 stamp=713055402
channel ORA_DISK_2: starting piece 1 at 07_MAR_22_58_34
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_58_35
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp tag=TAG20100307T225834 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 07_MAR_22_58_35
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp tag=TAG20100307T225834 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=74 recid=655 stamp=713055415
input archive log thread=1 sequence=75 recid=657 stamp=713055514
channel ORA_DISK_1: starting piece 1 at 07_MAR_22_58_36
channel ORA_DISK_1: finished piece 1 at 07_MAR_22_58_37
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp tag=TAG20100307T225834 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07_MAR_22_58_37

Starting Control File and SPFILE Autobackup at 07_MAR_22_58_37
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2010_03_07/o1_mf_s_713055517_5s7hrxjg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07_MAR_22_58_39

RMAN>  

The Log Sequence# has gone higher.

22:59:34 SQL> select l.sequence#, l.status from v$log l order by 1;

 SEQUENCE# STATUS
---------- ----------------
        74 ACTIVE
        75 ACTIVE
        76 CURRENT

22:59:39 SQL> alter system checkpoint;

System altered.

22:59:45 SQL> select l.sequence#, l.status from v$log l order by 1;

 SEQUENCE# STATUS
---------- ----------------
        74 INACTIVE
        75 INACTIVE
        76 CURRENT

22:59:55 SQL> 

We now shutdown the database.

RMAN> shutdown

database closed
database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.
ora10204> 

We delete database files to simulate loss of database.

ora10204>pwd
/oracle_fs/Databases/ORT24FS
ora10204>ls -l *.dbf
-rw-r----- 1 ora10204 dba  104865792 Mar  7 23:00 example01.dbf
-rw-r----- 1 ora10204 dba  104865792 Mar  7 23:00 index_tbs_01.dbf
-rw-r----- 1 ora10204 dba   52429312 Mar  7 23:00 redo01.dbf
-rw-r----- 1 ora10204 dba   52429312 Mar  7 22:56 redo02.dbf
-rw-r----- 1 ora10204 dba   52429312 Mar  7 22:58 redo03.dbf
-rw-r----- 1 ora10204 dba  304095232 Mar  7 23:00 sysaux01.dbf
-rw-r----- 1 ora10204 dba  985669632 Mar  7 23:00 system01.dbf
-rw-r----- 1 ora10204 dba  104865792 Mar  7 23:00 table_tbs_01.dbf
-rw-r----- 1 ora10204 dba  131080192 Mar  6 22:51 temp01.dbf
-rw-r----- 1 ora10204 dba  954212352 Mar  7 23:00 undotbs01.dbf
-rw-r----- 1 ora10204 dba 1343496192 Mar  7 23:00 users01.dbf
ora10204>rm e*.dbf i*.dbf s*.dbf t*.dbf u*.dbf
ora10204>ls -l
total 175176
-rw-r----- 1 ora10204 dba  7290880 Mar  7 23:00 control01.ctl
-rw-r----- 1 ora10204 dba  7290880 Mar  7 23:00 control02.ctl
-rw-r----- 1 ora10204 dba  7290880 Mar  7 23:00 control03.ctl
-rw-r----- 1 ora10204 dba 52429312 Mar  7 23:00 redo01.dbf
-rw-r----- 1 ora10204 dba 52429312 Mar  7 22:56 redo02.dbf
-rw-r----- 1 ora10204 dba 52429312 Mar  7 22:58 redo03.dbf
ora10204> 

We delete archivelogs to simulate loss of archivelogs from disk.

ora10204>pwd
/oracle_fs/ArchiveLogs/ORT24FS
ora10204>ls -l
total 12492
-rw-r----- 1 ora10204 dba  778752 Mar  7 22:54 1_68_709685307.dbf
-rw-r----- 1 ora10204 dba 6007296 Mar  7 22:55 1_69_709685307.dbf
-rw-r----- 1 ora10204 dba   19968 Mar  7 22:55 1_70_709685307.dbf
-rw-r----- 1 ora10204 dba    2048 Mar  7 22:55 1_71_709685307.dbf
-rw-r----- 1 ora10204 dba    1536 Mar  7 22:55 1_72_709685307.dbf
-rw-r----- 1 ora10204 dba 5901824 Mar  7 22:56 1_73_709685307.dbf
-rw-r----- 1 ora10204 dba    5120 Mar  7 22:56 1_74_709685307.dbf
-rw-r----- 1 ora10204 dba   31744 Mar  7 22:58 1_75_709685307.dbf
ora10204>rm *.dbf
ora10204> 

We delete ArchiveLog *BackupSets*. We want to see if the RECOVER DATABASE VALIDATE really checks them !

ora10204>pwd
/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07
ora10204>ls -ltr *
-rw-r----- 1 ora10204 dba     780288 Mar  7 22:54 o1_mf_annnn_TAG20100307T225441_5s7hklr9_.bkp
-rw-r----- 1 ora10204 dba  602611712 Mar  7 22:55 o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp
-rw-r----- 1 ora10204 dba 1579761664 Mar  7 22:56 o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp
-rw-r----- 1 ora10204 dba    5907968 Mar  7 22:56 o1_mf_annnn_TAG20100307T225655_5s7hoqvy_.bkp
-rw-r----- 1 ora10204 dba    6008832 Mar  7 22:56 o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp
-rw-r----- 1 ora10204 dba      24064 Mar  7 22:56 o1_mf_annnn_TAG20100307T225655_5s7hos0q_.bkp
-rw-r----- 1 ora10204 dba    5925376 Mar  7 22:58 o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp
-rw-r----- 1 ora10204 dba    6787072 Mar  7 22:58 o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp
-rw-r----- 1 ora10204 dba      37888 Mar  7 22:58 o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp
ora10204>rm o1_mf_a*.bkp
ora10204>ls -l
total 2133316
-rw-r----- 1 ora10204 dba  602611712 Mar  7 22:55 o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp
-rw-r----- 1 ora10204 dba 1579761664 Mar  7 22:56 o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp
ora10204> 

Now we use RMAN's RESTORE DATABASE VALIDATE command

ora10204>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Mar 7 23:06:00 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     880803840 bytes

Fixed Size                     2087992 bytes
Variable Size                205521864 bytes
Database Buffers             666894336 bytes
Redo Buffers                   6299648 bytes

RMAN> restore database validate;

Starting restore at 07_MAR_23_06_18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_2: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp tag=TAG20100307T225444
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
channel ORA_DISK_2: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp tag=TAG20100307T225444
channel ORA_DISK_2: validation complete, elapsed time: 00:00:25
Finished restore at 07_MAR_23_06_46

RMAN>  

Surprising isn't it ? It didn't warn us that ArchiveLog BackupSets are missing.

Let's try again

RMAN> restore database validate until time "to_date('2010-03-07 22:58:37','YYYY-MM-DD HH24:MI:SS')";

Starting restore at 07_MAR_23_09_13
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_2: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp tag=TAG20100307T225444
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
channel ORA_DISK_2: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp tag=TAG20100307T225444
channel ORA_DISK_2: validation complete, elapsed time: 00:00:05
Finished restore at 07_MAR_23_09_18

RMAN>  

Hmm. The RESTORE DATABASE VALIDATE doesn't really check if ArchiveLogs required to achieve consistent recovery (whether Complete or Incomplete) are available !



Run a RESTORE and RECOVER ?

RMAN> restore database until time "to_date('2010-03-07 22:58:37','YYYY-MM-DD HH24:MI:SS')";

Starting restore at 07_MAR_23_11_17
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle_fs/Databases/ORT24FS/system01.dbf
restoring datafile 00002 to /oracle_fs/Databases/ORT24FS/undotbs01.dbf
restoring datafile 00010 to /oracle_fs/Databases/ORT24FS/index_tbs_01.dbf
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oracle_fs/Databases/ORT24FS/sysaux01.dbf
restoring datafile 00004 to /oracle_fs/Databases/ORT24FS/users01.dbf
restoring datafile 00005 to /oracle_fs/Databases/ORT24FS/example01.dbf
restoring datafile 00009 to /oracle_fs/Databases/ORT24FS/table_tbs_01.dbf
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp tag=TAG20100307T225444
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
channel ORA_DISK_2: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp tag=TAG20100307T225444
channel ORA_DISK_2: restore complete, elapsed time: 00:02:10
Finished restore at 07_MAR_23_13_28

RMAN> recover database;

Starting recover at 07_MAR_23_13_41
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=70
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=69
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=71
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=72
ORA-19870: error reading backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp
ORA-19505: failed to identify file "/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=73
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp
ORA-19870: error reading backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp
ORA-19505: failed to identify file "/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=74
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=75
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp
ORA-19870: error reading backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp
ORA-19505: failed to identify file "/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=69
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp
ORA-19870: error reading backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp
ORA-19505: failed to identify file "/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2010 23:13:47
RMAN-20506: no backup of archivelog found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 69 lowscn 3064318 found to restore

RMAN>  

The RECOVER attempted to restore ArchiveLogs from BackupPieces that it assumed were present !!

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hknro_.bkp recid=474 stamp=713055284
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_nnndf_TAG20100307T225444_5s7hko8j_.bkp recid=475 stamp=713055285
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2010_03_07/o1_mf_s_713055418_5s7hotwl_.bkp recid=479 stamp=713055418
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2010_03_07/o1_mf_s_713055517_5s7hrxjg_.bkp recid=483 stamp=713055517
Crosschecked 10 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225441_5s7hklr9_.bkp recid=473 stamp=713055282
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvy_.bkp recid=476 stamp=713055415
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hoqvl_.bkp recid=477 stamp=713055415
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225655_5s7hos0q_.bkp recid=478 stamp=713055417
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtv9_.bkp recid=480 stamp=713055514
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrtr6_.bkp recid=481 stamp=713055514
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_03_07/o1_mf_annnn_TAG20100307T225834_5s7hrw0w_.bkp recid=482 stamp=713055516
Crosschecked 7 objects


RMAN>  

The CROSSCHECK now discovers that the BackupPieces for the ArchiveLog BackupSets are missing !


Bottom Line : The RESTORE DATABASE VALIDATE doesn't confirm that the restored database is recoverable. You must separately run RESTORE ARCHIVELOG ... VALIDATE !

RMAN> restore archivelog all validate;

Starting restore at 07_MAR_23_24_27
using channel ORA_DISK_1
using channel ORA_DISK_2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/07/2010 23:24:28
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 75 lowscn 3064629 found to restore
RMAN-06025: no backup of log thread 1 seq 74 lowscn 3064620 found to restore
RMAN-06025: no backup of log thread 1 seq 73 lowscn 3064476 found to restore
RMAN-06025: no backup of log thread 1 seq 72 lowscn 3064467 found to restore
RMAN-06025: no backup of log thread 1 seq 71 lowscn 3064463 found to restore
RMAN-06025: no backup of log thread 1 seq 70 lowscn 3064429 found to restore
RMAN-06025: no backup of log thread 1 seq 69 lowscn 3064318 found to restore
RMAN-06025: no backup of log thread 1 seq 68 lowscn 3063044 found to restore
RMAN-06025: no backup of log thread 1 seq 67 lowscn 3062407 found to restore
RMAN-06025: no backup of log thread 1 seq 66 lowscn 3062290 found to restore
RMAN-06025: no backup of log thread 1 seq 65 lowscn 3062155 found to restore
RMAN-06025: no backup of log thread 1 seq 64 lowscn 3061937 found to restore
RMAN-06025: no backup of log thread 1 seq 63 lowscn 3061572 found to restore
RMAN-06025: no backup of log thread 1 seq 62 lowscn 3057850 found to restore
RMAN-06025: no backup of log thread 1 seq 61 lowscn 2969959 found to restore
RMAN-06025: no backup of log thread 1 seq 60 lowscn 2954519 found to restore
RMAN-06025: no backup of log thread 1 seq 59 lowscn 2951005 found to restore
RMAN-06025: no backup of log thread 1 seq 58 lowscn 2947827 found to restore
RMAN-06025: no backup of log thread 1 seq 57 lowscn 2944286 found to restore
RMAN-06025: no backup of log thread 1 seq 56 lowscn 2939934 found to restore
RMAN-06025: no backup of log thread 1 seq 55 lowscn 2937195 found to restore
RMAN-06025: no backup of log thread 1 seq 54 lowscn 2882223 found to restore
RMAN-06025: no backup of log thread 1 seq 53 lowscn 2872012 found to restore
RMAN-06025: no backup of log thread 1 seq 52 lowscn 2865847 found to restore
RMAN-06025: no backup of log thread 1 seq 51 lowscn 2859351 found to restore
RMAN-06025: no backup of log thread 1 seq 50 lowscn 2852840 found to restore
RMAN-06025: no backup of log thread 1 seq 49 lowscn 2846285 found to restore
RMAN-06025: no backup of log thread 1 seq 48 lowscn 2839930 found to restore
RMAN-06025: no backup of log thread 1 seq 47 lowscn 2834297 found to restore
RMAN-06025: no backup of log thread 1 seq 46 lowscn 2828584 found to restore
RMAN-06025: no backup of log thread 1 seq 45 lowscn 2822885 found to restore
RMAN-06025: no backup of log thread 1 seq 44 lowscn 2815789 found to restore
RMAN-06025: no backup of log thread 1 seq 43 lowscn 2809530 found to restore
RMAN-06025: no backup of log thread 1 seq 42 lowscn 2803035 found to restore
RMAN-06025: no backup of log thread 1 seq 41 lowscn 2796478 found to restore
RMAN-06025: no backup of log thread 1 seq 40 lowscn 2789872 found to restore
RMAN-06025: no backup of log thread 1 seq 39 lowscn 2785329 found to restore
RMAN-06025: no backup of log thread 1 seq 38 lowscn 2785249 found to restore
RMAN-06025: no backup of log thread 1 seq 37 lowscn 2785175 found to restore
RMAN-06025: no backup of log thread 1 seq 36 lowscn 2785101 found to restore
RMAN-06025: no backup of log thread 1 seq 35 lowscn 2783479 found to restore
RMAN-06025: no backup of log thread 1 seq 34 lowscn 2777838 found to restore
RMAN-06025: no backup of log thread 1 seq 33 lowscn 2772060 found to restore
RMAN-06025: no backup of log thread 1 seq 32 lowscn 2766294 found to restore
RMAN-06025: no backup of log thread 1 seq 31 lowscn 2766115 found to restore
RMAN-06025: no backup of log thread 1 seq 30 lowscn 2766041 found to restore
RMAN-06025: no backup of log thread 1 seq 29 lowscn 2765896 found to restore
RMAN-06025: no backup of log thread 1 seq 28 lowscn 2765626 found to restore
RMAN-06025: no backup of log thread 1 seq 27 lowscn 2758329 found to restore
RMAN-06025: no backup of log thread 1 seq 26 lowscn 2752032 found to restore
RMAN-06025: no backup of log thread 1 seq 25 lowscn 2745302 found to restore
RMAN-06025: no backup of log thread 1 seq 24 lowscn 2738701 found to restore
RMAN-06025: no backup of log thread 1 seq 23 lowscn 2732175 found to restore
RMAN-06025: no backup of log thread 1 seq 22 lowscn 2729394 found to restore
RMAN-06025: no backup of log thread 1 seq 21 lowscn 2729288 found to restore
RMAN-06025: no backup of log thread 1 seq 20 lowscn 2729177 found to restore
RMAN-06025: no backup of log thread 1 seq 19 lowscn 2729066 found to restore
RMAN-06025: no backup of log thread 1 seq 18 lowscn 2725897 found to restore
RMAN-06025: no backup of log thread 1 seq 17 lowscn 2720244 found to restore
RMAN-06025: no backup of log thread 1 seq 16 lowscn 2714572 found to restore
MAN-06025: no backup of log thread 1 seq 15 lo
RMAN>  

OR

RMAN> restore archivelog from sequence 69 until sequence 73 validate;

Starting restore at 07_MAR_23_38_43
using channel ORA_DISK_1
using channel ORA_DISK_2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/07/2010 23:38:44
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 73 lowscn 3064476 found to restore
RMAN-06025: no backup of log thread 1 seq 72 lowscn 3064467 found to restore
RMAN-06025: no backup of log thread 1 seq 71 lowscn 3064463 found to restore
RMAN-06025: no backup of log thread 1 seq 70 lowscn 3064429 found to restore
RMAN-06025: no backup of log thread 1 seq 69 lowscn 3064318 found to restore

RMAN> 

Bottom Line : The RESTORE DATABASE VALIDATE doesn't confirm that the restored database is recoverable. You must separately run RESTORE ARCHIVELOG ... VALIDATE !
(The CROSSCHECK doesn't read the whole backuppiece to check for corruption, it only checks the header of each backuppiece, so the RESTORE ... VALIDATE is still preferred).

.
.
.

Databases have figured dozens of times in Dilbert cartoons. My favorite is Dogbert defeats the database guru (November 9, 1995). Here are some more Dilbert cartoons featuring databases: Asok rewrites the Elbonian database with just six keystrokes (December 21, 1996) Asok creates a database of serial killers (March 23, 1998) Asok learns the hard way (November 28, 2004) Asok [...]