Oracle DBA - (Apps / Core) blog: 2007

Latest News from Oracle

Hi Viewers,

Here are few of the latest news published by Oracle Corporation.

Oracle Database 11g: A Giant Step for Database Professionals
In a recent report examining the impact of the release of Oracle Database 11g on the market in Japan, analysts at IDC called Oracle Database 11g an epoch-making product that represents a giant step toward next-generation systems.
Learn more.

Announcing Oracle VM: Up to Three Times More Efficient Than the Competition
Oracle VM offers scalable, low-cost server virtualization that is up to three times more efficient than existing products from other vendors. Consisting of open source server software and an integrated Web browser-based management console, Oracle VM provides an easy-to-use, rich, graphical interface for creating and managing virtual server pools across an enterprise.
Learn more.

Free Data from NAVTEQ Makes It Easy to Map-Enable Your Business
Customers can now add value to existing Oracle products by integrating location-based business data from NAVTEQ, making it easy to perform spatial analysis and quickly integrate maps and location intelligence into enterprise applications.
Read the article.

IBM and Sun Join Dell/EMC in Delivering Oracle Optimized Warehouses
IBM and Sun have joined the Oracle Optimized Warehouse Initiative and will now deliver Oracle’s data warehousing configurations validated and tested on their leading servers, it was announced at Oracle OpenWorld 2007. The Oracle Optimized Warehouse is also currently available on Dell/EMC.
Learn more.

Addressing the Information Management Challenge with Oracle Database 11g
"This is a real dilemma for IT: How can you keep changing things and deliver 24/7 availability?" said Andy Mendelsohn, Oracle's senior vice president of server technologies, during his Oracle OpenWorld 2007 keynote address, describing how Oracle Database 11g addresses these challenges and helps control cost.
Read the article.

Record Performance for Two-Processor x86-Based System
Oracle Database running Oracle Enterprise Linux on an HP ProLiant server set a record with the TPC-C benchmark by delivering the best two-processor performance for an x86-based system.
Learn more.

Oracle to Demo "Next-Generation Retail" at January's BIG Show

At this year's NRF BIG Show—to be held in New York City January 13–16—Oracle will unveil a combination of current offerings and forward–looking strategy that make up what is being called "Next–Generation Retail."
Learn more about the event.

New Release of Oracle Retail Advanced Inventory Planning Could Give Big Boost to Margins
With consumer expectations rising and Wall Street scrutiny intensifying, a series of planned new features in Oracle Retail Advanced Inventory Planning will enable retailers to maximize both service levels and inventory margins with powerful alternatives to traditional safety stock strategies.
Learn more.

Retail Today Is the Perfect Storm of Risk, Change, and Opportunity, Says Oracle's Duncan Angove
In his keynote address at November's Oracle OpenWorld conference, Oracle Senior Vice President Duncan Angove presented a compelling case for the role of technology in expanding profitability even though retail sales appear to be cooling.
Read the article.

The End of the Supply Chain as We Know It?
An innovative new approach to a venerable old business process—the supply chain—is gaining momentum as increasing numbers of global companies are defying convention in favor of a new way of thinking—the value chain.
Read more.

New Customer Briefs and Data Sheets Now Available on Oracle.com
Learn how Oracle Retail Solutions enable you to dramatically boost the efficiency and profitability of your business. Managing at the Speed of Reality covers the impending transformation from inventory-based to information-driven supply chains, and a new data sheet describes the benefits of transforming existing inventory planning processes with Oracle Retail Advanced Inventory Planning.

Please click on each link to visit the related area.

Happy Reading
/shakir

R12- Ora-01461 Can Bind A Long Value Only For Insert Into A Long Column

R12 EBS --> HRMS BUG:
Bug Details: Ora-01461 Can Bind A Long Value Only For Insert Into A Long Column

Solution:
Apply this patch:5394728 / Relink / Regerate form as mentioned below.

While Update Oracle Payroll Forms (Required for Oracle Payroll customers only)

1. Download 10.1.0.5 patch 5394728 and apply it to the 10.1.2 Application Server Oracle Home according to the instructions in the patch README.

If OPatch presents either or both of the following messages, type "Y" and continue applying the patch.

Set path using this command

$export path=$PATH:$ORACLE_HOME?OPatch

Apply patch using this command
$opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc

1. "The fixes for Patch 5394728, are included in the patch currently being installed (5394728).Â If you continue, they will be rolled back and the new patch (5394728) will be installed. Do you want to continue? Please respond Y|N"
2. "The following problems were encountered when backing-up the static archive files: 1. <10.1.2>/lib32/libpls10.a [ object: phb.o ]. Do you want to continue? Please respond Y|N "

2. Relink the forms, reports and Procdure Builder components using the following commands.
1. Set the OBJECT_MODE variable :

$ export OBJECT_MODE=32

2. Relink forms components:

$ cd $ORACLE_HOME/forms/lib32
$ make -f cus_forms.mk install
$ make -f cus_forms.mk sharedlib

3. Relink Procedure Builder components:

$ cd $ORACLE_HOME/procbuilder/lib32
$ make -f cus_procbuilder.mk sharedlibs

4. Relink reports components:

$ cd $ORACLE_HOME/reports/lib32
$ make -f cus_reports.mk install

3. Regenerate the form FFXWSMNG.fmx using AD Administration.

Once this is done on R12 of EBS it works fine and the bug is fixed.

Please take backup of your system before applying.

Thank you

Happy Reading

/Syed Shakir Jamal

Oracle® Database 11g Running on HP with Windows Sets New World Record for Price/Performance with TPC-C Benchmark

Oracle Leads Competition with Top Two Price/Performance Results in the TPC-C Top 10

REDWOOD SHORES, Calif., 13-SEP-2007 05:30 AM Today, Oracle announced a new world record price/performance result with the TPC-C benchmark running Oracle® Database 11g on Windows(1). With this result, Oracle now holds the top two record benchmark positions in the coveted Top Ten TPC-C price/performance category(2). Optimized for small, medium and growing businesses (SMBs), the combination of Oracle Database 11g and Windows on an HP ProLiant server provides an ideal platform to meet these companies' individual business needs. This benchmark result further demonstrates Oracle's commitment to providing customers of all sizes unmatched price/performance and scalability.

Achieving 102,454 transactions per minute with a price/performance of $.73/tpmC, Oracle Database 11g, Standard Edition One delivered 47 percent more performance at 20 percent less cost than its nearest competitor in the price/performance category(3). The record setting benchmark was achieved with Oracle Database 11g running on a Windows-based HP ProLiant ML350 server with one Intel Quad-Core Xeon 2.66 GHz processor and an HP StorageWorks 70 Modular Smart Array. Together, Oracle and HP delivered the best price per transaction per minute ever achieved for any TPC-C benchmark.

"Out of the gate, Oracle Database 11g sets a new price/performance bar for the industry and it will continue to excel as it matures," said Juan Loaiza, senior vice president Systems Technology, Oracle. "This benchmark result furthers Oracle's commitment to SMBs, and our continuing promise to deliver on a long tradition of database excellence. SMBs that once thought Oracle was only for large enterprises need to take another look at Oracle Database."

About TPC-C
TPC-C is an OLTP (online transaction processing) benchmark developed by the Transaction Processing Performance Council (TPC). The TPC-C benchmark defines a rigorous standard for calculating performance and price/performance measured by transactions per minute (tpmC) and $/tpmC, respectively. More information is available at http://www.tpc.org.

Top Ten TPC-C by Price/Performance: http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp.

About Oracle Database 11g
Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g's unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.
***************************************************************

Happy reading.

/Syed Shakir Jamal

Now Available: Oracle E-Business Suite 12.0.3 Release Update Pack (RUP3)

Dear All,

Good news from oracle for Release update patch (RUP3)...

We are pleased to announce the availability of Oracle Applications E-Business Suite 12.0.3, Release Update Pack (RUP3). RUP3 combines system updates made available after the initial release of Oracle E-Business Suite 12.0 into a single, thoroughly tested, high-quality patch. The updates included in RUP3 are predominantly error corrections and statutory/regulatory updates. For certain products, enhancements to selected functionality are also included. Any new functionality introduced in RUP3 is described in the Oracle E-Business Suite 12.0.3 Release Content Document (Oracle MetaLink document 404152.1).

The Oracle Applications E-Business Suite 12.0.3 Release Update Pack is available for download from Oracle MetaLink as Patch 6141000. The Oracle E-Business Suite 12.0.3 Release Update Pack Readme (Oracle MetaLink document 445192.1) includes instructions for applying the 12.0.3 Release Update Pack, as well as the list of included patches.

For more information about R12 Release Update Packs, please review the Oracle E-Business Suite R12 Release Update Pack (RUP) Schedule (Oracle MetaLink document 423541.1) and the Oracle E-Business Suite R12 Maintenance Strategy (Oracle MetaLink document 433461.1).

Sincerely,
Oracle

Happy Reading

Critical Patch Update - October 2007

Dear Viewers,
Here is a latest patch from oracle .

Critical Patch Update - October 2007

The Critical Patch Update for October 2007 is now available. Oracle strongly recommends applying the patches as soon as possible.

The Critical Patch Update Advisory is the starting point for relevant information. It includes a list of products affected, pointers to obtain the patches, a summary of the security vulnerabilities, and links to other important documents. Supported Products that are not listed in the "Supported Products and Components Affected" Section of the advisory do not require new patches to be applied.

Also, it is essential to review the Critical Patch Update supporting documentation referenced in the Advisory before applying patches, as this is where you can find important pertinent information.

The Critical Patch Update Advisory is available at any of the following locations:

Oracle Technology Network

Oracle, PeopleSoft and JD Edwards products

The next four Critical Patch Update release dates are:

January 15, 2008
April 15, 2008
July 15, 2008
October 14, 2008

Sincerely,
Oracle Security Alerts

Thank you for your time to update

Oracle Database 11g New Features for Administrators Beta Exam Available

Why take a Beta Exam?

Significantly reduced cost! $50 USD (savings of $75 USD)
If you pass the beta exam, your record will indicate a passing score and you will receive credit toward certification for this exam. Therefore, you will not need to retake the final released version of the exam.
The beta exam is a great way to identify your strengths and weaknesses in the exam content being tested.

Oracle University is pleased to announce the availability of the Oracle Database 11g: New Features for Administrators (1Z0-050) in the beta version.

This exam is an excellent opportunity to upgrade your certification and skills to Oracle Database 11g. Oracle Database 11g extends Oracle's unique ability to deliver the benefits of grid computing with increased self-management and automation. Oracle Database 11g Administrator Certification combines training, experience, and testing to ensure that you have a strong foundation and expertise in the industry’s most advanced database management system.

By participating in beta exams, you have the opportunity to provide the Oracle Certification program with feedback about exam content, which is integral to the process of exam development. We depend on the contributions of experienced professionals and developers as we continually improve exam content and maintain the value of Oracle certifications.

Pre-Registration begins:
September 29, 2007

You may register for all Oracle certification exams with Thomson Prometric.

Experience Linux Innovation at Oracle OpenWorld 2007

Join us at Oracle OpenWorld 2007 in San Francisco, November 11–15, and discover how your organization can realize the full potential of Linux with Oracle Unbreakable Linux. Meet Oracle experts, see product demonstrations, visit our Linux Partner Pavilion, find out how you can deploy Linux faster at a lower cost with Oracle Validated Configurations, and much more! Oracle Develop, the premier developer program, returns to Oracle OpenWorld in 2007 and is a great way to hear from world-leading experts on simplifying development using popular, emerging technologies, including Linux and open source. Oracle OpenWorld features a Linux and open source solutions track, as well as a variety of sessions of interest to all.

Sessions

Oracle Unbreakable Linux Support: Everything You Need to Know
How Activision and Yahoo! Succeed with Oracle Unbreakable Linux Support
Deploy Linux Faster with Oracle Validated Configurations
Implementing the Database Grid at Dell with Oracle Unbreakable Linux, Oracle Database 11g, and Dell PowerEdge Servers
Executive Panel: Oracle and Open Source
Unbreakable Linux Network (ULN) Tutorial
How Qpass Migrated to Oracle Unbreakable Linux Support
Linux Configuration and Diagnostic Best Practices
Security Essentials: Hardening Your Oracle Systems on Linux
Inside Oracle Management Pack for Linux
Using Oracle Authentication Services for Operating Systems with Linux

For more information and to register, go to the Oracle OpenWorld 2007 site today .

Please download Unbreakable Linux HERE

Happy Reading
Syed Shakir Jamal

Backup tables before changing passwords using FNDCPASS

Hi All,

Please backup tables before using the FNDCPASS utility on changing default passwords - EBS
a. fnd_user
b. fnd_oracle_userid

steps:

SQL> connect system/manager

SQL> create table [backup_tab_fnd_oracle_userid] as select * from apps.fnd_oracle_userid;
SQL> commit;
SQL> connect apps/apps
SQL> create table [backup_tab_fnd_user] as select * from fnd_user;
SQL> commit;
SQL> exit

Now execute the FNDCPASS APPLSYS schema

Need to apply this on application tier:
$ FNDCPASS apps/[apps_pwd] 0 Y system/manager SYSTEM applsys [apps_new_pwd]

follow the required steps to finish the remaining.

NOTE: all changes will affect only after restarting the apps tier services (apache)

Thank you for your time to read this post!

/shakir
Apps DBA

How to Collect index Statistics

INDEX_STATS figures for multiple indexes, because a normal 'analyze index ... validate structure' always replaces the content of index_stats when it is run. Script below also adds the number of columns in the index to assist when looking at the compression statistics

create table IDXSTAT
as select * from index_stats;

alter table idxstat add colcnt number;

begin
for i in ( select index_name from dba_indexes
      where owner = '...') loop
execute immediate 'analyze index iips.'||i.index_name||'

validate structure';
insert into idxstat
select x.*, cnt from index_stats x,
( select count(*) cnt from dba_ind_columns
where index_owner = '...'
and index_name = i.index_name );
commit;
end loop;
end;
/

select name, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE, COLCNT
from idxstat

How To Backup All Tablespaces In Parallel With a Single Command

It is no longer necessary to issue a separate command to place each tablespace in hot backup mode.

You can now use the "ALTER DATABASE" statement to place all tablespaces in backup mode.

The database must be in mounted or open mode when you issue this command.

Also, the BEGIN BACKUP command now runs faster than before.

Earlier DBAs have used the prepare all online tablespaces for backup by issuing
all necessary ALTER TABLESPACE statements at once.

For example, put tablespaces users, tools, and indx in backup mode as follows:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> ALTER TABLESPACE tools BEGIN BACKUP;
SQL> ALTER TABLESPACE indx BEGIN BACKUP;

Now you can Use ALTER DATABASE BEGIN BACKUP to put all tablespaces in backup mode simultaneously,

Hence If you are backing up all tablespaces, you might want to use this command:

SQL> ALTER DATABASE BEGIN BACKUP;

Example 1:
--------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace users begin backup;
alter tablespace users begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'USERS'

SQL> alter database begin backup;

Database altered.

Example 2:
------------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace tools offline;

Tablespace altered.

SQL> alter tablespace tools begin backup;
alter tablespace tools begin backup
*
ERROR at line 1:
ORA-01128: cannot start online backup - file 5 is offline
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\SAMPLE\tools .DBF'

SQL> alter database begin backup;

Database altered.

The two examples above demonstrate that when you issue the ALTER DATABASE BEGIN BACKUP command, any read-only and offline data files are simply skipped, and processing continues.

SQL> ALTER DATABASE END BACKUP;

ALTER DATABASE END BACKUP command when you have multiple tablespaces still in backup mode. You can issue the statement to take all data files currently in backup mode out of backup mode. However, you can use this statement only when the database is mounted, and not open in Oracle9i.

If the database is open, you can only use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.

In Oracle 10g, you can issue the ALTER DATABASE END BACKUP command when the database is open.

If you issue the command while one of the data files is offline or in read-only mode, a warning message will return:

SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode

Note: However, that by putting all tablespaces in online mode at once, you can generate
large redo logs if there is heavy update activitiy on the affected tablespaces,
because the redo must contain a copy of each changed data block in each changed
datafile.

Be sure to consider the size of the likely redo before using the procedure outlined here..

References

Oracle 10G Documentation / Metalink -Note:270531.1

Errors

ORA-1260 { Message text depends on version }
ORA-1642 begin backup not needed for read-only tablespace
ORA-1110 datafile :
ORA-1128 cannot start online backup - file is offline

Difference of CURSOR_SHARING / Dynamic SQL

You need to take extreme care when using cursor sharing and dynamic SQL. You might think that opening a dynamic ref cursor would be treated the same as that same SQL using 'execute immediate'...but check the example below

SQL> drop table t;

Table dropped.

SQL>create table T (x number primary key,y date,z varchar2(10));

Table created.

SQL> insert into t
2  select rownum, sysdate+rownum,rownum
3  from all_objects
4  where rownum <= 5000;  5000 rows created.  SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = exact;

Session altered.

SQL>
SQL> DECLARE
2     rc SYS_REFCURSOR;
3  BEGIN
4    for i in 1 .. 5000 loop
5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
      5002  <== as expected, no sharing yet

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         1

SQL> DECLARE
2     rc SYS_REFCURSOR;
3  BEGIN
4    for i in 1 .. 5000 loop
5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
      5002  <== HEY! NO SHARING!

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         1

SQL> DECLARE
2  r number;
3  BEGIN
4    for i in 1 .. 5000 loop
5 execute immediate 'SELECT /*+XXX*/1 from T where x
                                        ='||i into r;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         3    <== This time we got sharing!

SQL>

Who is Active Users on Database ?

Most people are aware of the STATUS column in V$SESSION. If its 'ACTIVE' then that connection is in the process of consuming database resources (running an SQL etc).

However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. Unfortunately, the documentation is hardly explanatory giving

LAST_CALL_ET        NUMBER         The last call

but a more useful description is that its the the number of seconds since the last call.

Thus use this to determine how "idle" a connection has been. The following SQL may be of use

set verify off
select s.sid||','||s.serial# session,
      s.USERNAME,
      s.last_call_et seconds_since_active,
      s.status,
      s.sql_address,
      s.program
from v$session s
where s.sid = nvl(to_number('&sid'),s.sid);

If you add a column with a default, Oracle dutifully goes back and updates every single existing row. which Includes nice trip to row migration Heaven

Can we avoid this ? Yes, just don't do it "all at once"...

SQL> create table T
2  as select * from all_objects;

Table created.

SQL> alter table T add NEW_COL1 number default 10;

Table altered.

Elapsed: 00:00:02.08
SQL> alter table T add NEW_COL2 number;

Table altered.

Elapsed: 00:00:00.02
SQL> alter table T modify NEW_COL2 default 10;

Table altered.

Elapsed: 00:00:00.03
SQL> select count(new_col1), count(new_col2)
2  from T;

COUNT(NEW_COL1) COUNT(NEW_COL2)
--------------- ---------------
        50199               0

Elapsed: 00:00:00.05

Oracle DBA - (Apps / Core) blog