oracle » OraExplorer

Aug 15 2011

Book Review: Oracle 11g R1/R2 Real Application Clusters Essentials by Ben Prusinski and Syed Jaffer Hussain (Packt Publishing)

Category: 11g,Database — ittichai @ 9:31 pm

Oracle Real Application Cluster (RAC) becomes the technology of choice for highly available and highly scalable deployment of enterprise Oracle database environment. With its innovative technology it comes with no surprise its complexity. The complexity is not only with the underlying database technology itself, but also how well you design and implement it to work with other components including operating system, storage subsystem, etc. In order to setup and maintain a successful RAC environment, it not only requires technical knowledge of database administrator (DBA), but also well collaboration and extended planning between all IT and business partners.

Few years back, there has been a movement of DBA 2.0 which says that, in addition to database knowledge, an extended understanding of operating system, storage and/or networking is encouraged due to more tightly integrated of Oracle database technologies such as Clusterware, RAC, ASM with the underlying subsystems. I agree with this notion but not to the extent that DBAs should perform all works in those areas. (Yes, I’m aware that some DBAs do it all.) However, I think at least they should have enough understandings of all-related technologies to collaborate with those who are responsible for in order to achieve the desired setup. In many cases especially in the large companies where traditional organization division of the job description of administrators is clear cut and no overlapping, being well-rounded with all-related technologies will allow DBA to better discuss the requirements and designs and even assist with the implementation details.

Most Oracle RAC books in the market today tend to focus only on the detailed technical aspects of this database technology. This mainly is good for those Oracle professionals who at least have worked with RAC before. Few books really provide a good foundation for those who are new to this technology. One of those few which I’ve just read recently is Oracle 11g R1/R2 Real Application Clusters Essentials authored by Ben Prusinski and Syed Jaffer Hussain published by Packt Publishing.

What I really like about this book is the fact that it does not jump into the nuts and bolts of this Oracle technology right away but instead it is trying to build up good foundation starting from concept (especially high availability), architecture (of all components not just of database), design, implementation and then even some of the real-world samples. This stepping-stone approach helps readers to understand what is involved and how they should deploy this technology for the high available database.

In the chapter 1, the emphasis of the high availability concept is crucial. The authors precisely points out that high availability should be looked from users’ perspective. Many DBAs or system administrators especially novice ones have a tendency to focus solely on their respective areas. The provided sample of the availability percentage in the “high availability interpretations” section is a good reminder for a need to have an end-to-end high availability design to avoid a single point of failure (SPOF). The chapter goes on to give the definitions of many acronyms (BIA, BCP, DRP, etc.) commonly-used during the planning phase. It is quite useful to be aware of them especially when discussing with business end-users. The chapter ends with providing a whole picture of the high availability solutions (RAC, Data Guard, Streams, Application Server Clustering) from Oracle as a part of the Maximum Availability Architecture (MAA). It is worth to point out that RAC is just one (not the only) of key components for a complete high availability solution.

Chapter 2 looks into the hardware components of Oracle RAC architecture including network, storage, etc. Again, I think the authors reinforce a need to understand all these underlying technologies (at least at high level) even though DBAs might not involve in the actual implementation. But it is crucial to have this knowledge under their belts in order to have the productive discussions of the requirements and design with system administrators.

Chapter 3 goes into the clusterware installation of 11g R1/R2. Even though readers may to be able to find similar information on many web sites and blogs, this book has some extra information which makes an attempt to explain what each action does, for example, what happen when the orainstRoot.sh and root.sh run?

Chapter 4 dedicates to the Automatic Storage Management (ASM) which becomes the only clear choice for storage management in the 11g deployment. The chapter covers all aspects of ASM in both R1 and R2 including new features, configurations, management, etc.

Chapter 5 covers Clusterware management and (some) troubleshooting samples. Instead of showing all possible troubleshooting scenarios (which is impossible to include everything in a chapter or even in a book), the authors did an excellent job of giving an overview of all necessary Clusterware utilities in order to troubleshoot the issues.

Chapter 6 turns our attention to RAC database administration. The structure is similar to that of Clusterware in the chapter 3. The latter part of the chapter also covers the automatic workload management including the topic of the service which is highly recommended to utilize it (not using the default database service.)

Chapter 7 covers all aspects of backup and recovery of Oracle database including the best practices. In addition, new features of RMAN in Oracle 11g R1 and 11g R2 along with OCR and voting disk backup and recovery are discussed.

Chapter 8 tries to give an overview of performance tuning. The intention here seems to just give an overview of the concept and methods of performance tuning as well as new 11g performance features, for example, Real Application Testing (RAT) which has the Database Replay and SQL Performance Analyzer (SPA). This high-level overview focus is understandable because the scope of performance tuning is generally vast. Some of tuning metrics especially relate to the wait events are explained here.

Chapter 9 focuses on the upgrade scenarios. As we all know it is impossible to cover all possible scenarios. But the authors gives sufficient information about overall upgrade processes from 10g R2 to 11g R1 and then to 11g R2 in this chapter. Definitely consulting with Oracle support for more details on individual upgrade case is still highly recommended.

Chapter 10 is very informative on the node addition and removal. The chapter steam-lines the steps pretty well (comparing to the official Oracle document which is very confusing at the first read with the flow of information). (We just went through adding two nodes into the existing two nodes of Oracle 11g R1 two months ago. That’s why I can say that.)

Chapter 11 shows the readers the importance of the end-to-end high availability implementation. The high availability should not stop at the Oracle RAC database, but it must extend to application layer on top of it. In addition to an overview of Oracle EBS (E-Business Suite), this chapter shows the readers how to take full advantage of high availability and performance features in Oracle RAC database.

The last chapter wraps up nicely with information about other tools in the MAA tool suite namely Streams and Data Guard. It covers all various topics of thes tools including concepts, new features, best practices, and overview configuration.

The last section of this book on the additional resources and tools is a nice read and very informative. It is just like a nice dessert at the end of a good meal.

In conclusion, if you’re new to Oracle RAC technology, this is THE book for you. It should give the readers enough understanding to jump on Oracle RAC bandwagon. Advanced DBAs may find that most of the contents in the book are too familiar, but they might be attracted to the new features, comparisons between R1 and R1 as well as some of real-world samples mentioned in this book. Overall I appreciate a very well effort of Ben ad Syed for one of the most complete informative books on Oracle RAC in the market today. Well-deserved kudos to them.

Thanks to Packt Publishing for the opportunity to review this book.

Tags: 11g, 11gR1, 11gR2, Ben Prusinski, Book, oracle, Packt, R1 and R2, RAC, Real Application Clusters, Review, Syed Jaffer Hussain

Comments (0)

Mar 20 2011

Oracle 11g SQL Error Logging

Category: 11g,Database,SQLPlus,Tool — ittichai @ 8:00 am

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search ORA- for any errors. This would work fine if there are only a few scripts but it becomes cumbersome when multiple scripts are involved. In addition we’d like to be able to run the scripts on either Windows and Unix platforms, handling OS file paths for multiple platforms using spool adds unnecessary layer.

The SQL Error Logging is a new feature in 11g. This simplifies the way we capture and locate error messages as information now will be stored in database table instead of OS files.

To check whether or not the Error Logging is enabled.

SQL> show errorlogging

errorlogging is OFF

To enable the Error Logging. The default table SPERRORLOG is created.

SQL> set errorlogging on

By default, the SPERRORLOG will be created under current user. In this sample, the current schema is TEST1_USER.

SQL> show errorlogging

errorlogging is ON TABLE TEST1_USER.SPERRORLOG

Here is the structure of this table SPERRORLOG.

SQL> desc SPERRORLOG
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

To enable the Error Logging to an user defined table instead of the default SPERRORLOG table.

SQL> set errorlogging on table [schema].[table]

If non-default, the table has to be created in advance, otherwise you will get an error.

SQL> set errorlogging on table my_sperrorlog;

SP2-1507: Errorlogging table, role or privilege is missing or not accessible

Here is the syntax to create an user-defined table. If this table is created on a different schema, an insert grant to is needed for the current user.

SQL> CREATE TABLE my_sperrorlog (
username     VARCHAR(256),
timestamp    TIMESTAMP,
script       VARCHAR(1024),
identifier   VARCHAR(256),
message      CLOB,
statement    CLOB
);

SQL> set errorlogging on table my_sperrorlog

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.my_sperrorlog

Demo
Generate error #1.

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

Generate error #2.

SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1)                     option

Without commit, other sessions won’t see this information.

SQL> commit;

Commit complete.

Set an unique identifier to make it easier to identify the logging record. In sample here, the identifier is set to REL1.

SQL> set errorlogging on identifier 'REL1'

Generate error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

Check the SQL Error Logging with the identifier=’REL1′.

SQL> SELECT username, statement, message, IDENTIFIER
FROM sperrorlog where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
TEST1_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL                          tifier

Truncate the Error Logging to clear all existing rows in the error log table. This will clear out all records regardless of who creates them.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;
No rows selected

There is no set errorlogging truncate only a specified identifier. Doing below is the same set errorlogging on truncate. So basically the identifier is ignored.

SQL> set errorlogging on truncate identifier 'REL1'

But you can just delete records as the regular table.

SQL> delete sperrorlog where IDENTIFIER='REL1';

SQL> commit;

Disable Error Logging. (Log off will automatically disable it.)

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

Will it work with previous versions of Oracle database?

Since the SQL Error Logging is the feature on client, I have no reason to believe that it would not work with pre-11g databases.

The 11g SQL Plus Client to 10.2.0.1 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 10.1.0.4 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 9i Database
Unfortunately I don’t have any Oracle 9i databases.

The 11g SQL Plus Client to 8i Database

Surprisingly, I still have one 8i database left. But, well, the 11g client no longer supports the 8i database.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Install 11g Release 2 Grid Infrastructure for Standalone Server on Windows 7 for Sandbox

Category: 11g,Database — ittichai @ 9:08 pm

Oracle 11g Release 2 for Windows was just released this month. With the availability of the grid infrastructure in this version, I plan to install it on my Windows 7 desktop to see what it can do even if it is just on stand alone environment.

In order for database to use Automatic Storage Management (ASM), it requires the Grid Infrastructure. In addition to ASM, Grid Infrastructure will also provide Oracle Restart to manage the Oracle processes (database, listener, and ASM).

One of the first issues I’ve encountered is the new requirement that the clusterware files (OCR & Voting) must be on ASM. I have to admit even though I’ve done ASM on Solaris and Linux before, but never on Windows. Since this is mandatory, I will give it a try. And since I will use ASM for clusterware files, I plan to use it for database data files as well.

In order to use ASM, I’m required to provide the unformatted (raw) basic disks. I plan to use the existing disks without adding new physical ones. Fortunately in Windows 7, I can use the disk management (diskmgmt.msc) tool to shrink volume and create a new logical disk from claimed space. Note that you may have multiple physical disks on your machine, but ASM supports and recognizes only logical drives on the Basic disk (not Dynamic disk). Click here if you’re interested in differences between Basic and Dynamic disks.

Once data volume is shrunk, I can create a new volume and then a logical drive. The new drive must not be formatted or having a drive letter assigned to it. Here is the guidelines from Oracle document on “create disk partitions”.

To use ASM with direct attached storage or SAN, the disks must be stamped with a header. This can be accomplished by using either asmtool (command-line version) or asmtoolg (GUI version). Since we will install Oracle grid infrastructure in interactive mode, the asmtoolg will be called during the configuration. Somehow, if I tried to launch the asmtoolg outside Oracle grid infrastructure installation, I always encountered error with no disks found. However, within the Oracle grid infrastructure installation, there is no issue.

In general, the installation went well. I’ve encountered few issues which I’ve documented them in the documents below. The snapshots of steps here are for educational purpose only.

Windows 7 – Disk Preparation for ASM

Oracle 11g R2 Grid Infrastructure for Standalone Server Installation on Windows

Oracle 11g R2 Software Installation for Single Instance Database on Windows

Oracle 11g R2 Database Creation using ASM on Windows

Tags: 11g, ASM, asmtoolg, Grid, Infratructure, oracle, R2, Release 2, Standalone, Windows, windows 7

Comments (14)

Mar 07 2010

Book Review: Oracle SQL Developer 2.1 by Sue Harper

Category: APEX,Books,SQL Developer,Tool — ittichai @ 6:31 pm

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

Tags: Book, developer, oracle, Review, sql, SQL Developer

Comments (0)

Feb 13 2009

Oracle lifetime support policy

Category: Database — ittichai @ 4:58 pm

Our organization just started looking into upgrading all older versions of database including 9i. Definitely one of many business justifications for upgrade is to avoid de-supporting from vendor. I recently looked into Oracle’s support policy and did not realize that it offers the lifetime support policy on its entire technology stack, from database to middleware to applications.

Three levels of supports are

Premier Support covers the first five years after general availability date
Extended Support provides an extra three years of support for specific releases for an additional fee.
Sustaining Support provides lifetime support with only limited features.

The sustaining support lacks our major needed components such as critical patch, new updates/fixes, etc, but it seems to be a good option for those who really could not upgrade with whatever their business reason is.

These are the support dates for Oracle database releases -