9.2.0.4 databases to 9.2.0.6, 9.2.0.7 or wait for testing?

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1157663,00.html?track=sy40

My name is Steve and I'm a Lead Oracle DBA. My question revolves around the quarterly security patches and the best 9i release of Oracle to be on. I have over 300 9.2.0.4 databases. We just started creating 9.2.0.6 databases with an eye toward going to 10.2 in the first quarter next year. My question/issue is this: Oracle released 9.2.0.7 recently, and my concern is that they will stop patching 9.2.0.6 in the near future (not sure of that date). I'd like to be on the terminal release of 9i but that's been a moving target. Anyway, should I upgrade my existing 9.2.0.4 databases to 9.2.0.6, 9.2.0.7 or wait for testing, etc. of 10.2 and just move them all to that release? Any advice would be helpful. Thanks.
This question posed on 10 January 2006


>This is a subject I have quite an interest in so I could probably spend hours discussing it. This patching issue is still relatively new to most DBAs and it can be especially painful if there are a large number of databases to support, as is the case here. I work for an IT consulting company, but I just spent the last two years at a large client and we had to tackle this exact problem. This client had more than 200 Oracle databases that had to be patched on a regular basis. With Sarbanes-Oxley (and other) regulatory compliance legislation, patching databases is no longer an option, but a necessity.

Your concern that patches will no longer be available for Oracle 9.2.0.6 is quite valid -- this, in fact, will happen one day soon. So, in my opinion, you need to develop a strategy that balances the practicality of patching but can tolerate some risk. For example, you can apply the appropriate CPU patches once per year (say, in late spring) and then plan to upgrade databases to the next release in the fall. In theory, the latest release will contain the latest CPUs. It's nearly impossible to upgrade 300 databases more than once per year; as well, it would be impossible to apply all four CPUs to these databases considering you would require outages which can be very difficult to obtain on production systems.

Whatever strategy you choose, make sure that it works for your organization and that you can justify it. Also, document the strategy and your rationale for choosing it.

How to find obsolete parameters in Oracle

By querying V$OBSOLETE_PARAMETER.

If the value in column ISSPECIFIED is 'TRUE' then it is specified in the init.ora file.

select * from V$OBSOLETE_PARAMETER
where ISSPECIFIED = 'TRUE'
/

A Comparison of Oracle's DATE and TIMESTAMP Datatypes

http://blogs.ittoolbox.com/database/solutions/archives/006681.asp

Salesforce "failover"?

CNET reported on December 21, 2005: A Salesforce.com outage lasting nearly a day cut off access to critical business data for many of the company's customers on Tuesday in what appears to be Salesforce's most severe service disruption to date.

Salesforce, which has been growing rapidly, has undertaken efforts to bolster its computing infrastructure. For instance, it has configured its database to run on four different computers so if a machine fails, others will pick up the slack, Francis said. But the "failover" feature didn't prevent Tuesday's problems.

Salesforce's database supplier helped to restore service, Francis said. While he declined to identify who that supplier was, he did identify Oracle as Salesforce's biggest database supplier.

To see the detail, go to http://news.com.com/Salesforce+outage+angers+customers/2100-1012_3-6004625.html?tag=nefd.top

Manually Resolving In-Doubt Transactions: Different Scenarios

NOTE1: If using Oracle 9i and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use the following workaround
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

select * from dba_2pc_pending
/

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 prepared no

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/ch4e.htm

COMMIT FORCE '3.7.99084';

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 forced commit no

SQL> select * from dba_pending_transactions;

FORMATID
----------
GLOBALID
--------------------------------------------------------------------------------
BRANCHID
--------------------------------------------------------------------------------
48801
34A257C2BC134A007FFD
73616D705841436F6E6E506F6F6C

alter session set "_smu_debug_mode" = 4;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.7.99084');

SQL> select * from dba_pending_transactions;

no rows selected

SQL> select * from dba_pending_transactions;

no rows selected

Oracle Export Utility

http://www.dba-oracle.com/tips_oracle_export_utility.htm

http://www.orafaq.com/faqiexp.htm

http://builder.com.com/5100-6388-5054021.html#Listing%20B

Developing Signatures for Data Buffers

Remember, change occurs in the data buffers rapidly, and sometimes a long-term analysis will provide clues that point to processing problems within the database. Almost every Oracle database exhibits patterns that are linked to regular processing schedules, called signatures.

To solve this problem, the DBA might schedule a dynamic adjustment to add more RAM to db_cache_size every day.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/burleson_auto_pt2.html

Fast guide to finding and keeping an Oracle job

This guide provides tips and advice to help you stand out from the hundreds of other job seekers.

Frequently asked questions and myths about indexes

[SearchOracle.com]

Popular author Tom Kyte tackles some of the most common questions about Oracle indexes and debunks some myths in the process.

44% of database devs use MySQL

44% of database devs use MySQL by ZDNet's ZDNet Research -- Open source database deployments are up more than 20% in the last six months, according to Evans Data. MySQL use, has increased by more than 25% in six months and is approaching a majority in the database space, with 44% of developers using the open source database. More than 60% of database developers say their [...]

Oracle Statspack Survival Guide

http://www.akadia.com/services/ora_statspack_survival_guide.html

Fast guide to finding and keeping an Oracle job

Elisa Gabbert, Assistant Site Editor
03.16.2005


Looking for your first Oracle DBA or developer job? Concerned about the future of your current position? You're not alone! With the IT market becoming increasingly competitive, it can be very difficult to find a job or keep the one you've got. We know how time-consuming it can be to search the Web for relevant career information, so we've gathered some valuable Oracle-related resources for you. This guide provides tips and advice to help you stand out from the hundreds of other job seekers. If you're interested in brushing up on the latest interviewing techniques, checking out Oracle salaries or simply searching for open positions, you've come to the right place.

Essential performance forecasting, part 2: I/O

Craig Shallahamer

SearchOracle.com

As I wrote in the first part of this series, forecasting Oracle performance is absolutely essential for every DBA to understand and perform. When performance begins to degrade, it's the DBA who hears about it, and it's the DBA who's supposed to fix it. Fortunately, low precision forecasting can be done very quickly and it is a great way to get started forecasting Oracle performance. This time, I'll focus on I/O performance forecasting.

The key metrics we want to forecast are utilization, queue time, and response time. With only these three metrics, as a DBA you can perform all sorts of low precision what-if scenarios. To derive the values, you essentially need 3 things:

a few simple formulas
some basic operating system statistics
some basic Oracle statistics
Modern I/O subsystems can be extremely difficult to forecast. Just as with Oracle, there is batching, caching, and a host of complex algorithms centered around optimizing performance. While these features are great for performance, they make intricate forecast models very complex. This may seem like a problem, but actually it's not. I have found that by keeping the level of detail and complexity at a consistently lower level (i.e., less detail), overall system I/O forecasts are typically more than adequate.

At a very basic level, an I/O subsystem is modeled differently than a CPU subsystem. A CPU subsystem routes all transactions into a single queue. All CPUs feed off of this single queue. This is why, with a mature operating system, any one CPU should be just as busy as the next. If you have had I/O performance problems you know the situation is very different.

In contrast to a CPU subsystem, each I/O device has its own queue. A transaction cannot simply be routed to any device. It must go specifically where the data it needs resides or where it has been told to write a specific piece of data. This is why each device needs its own queue and why some I/O queues are longer than others. This is also why balancing IO between devices is still the number one I/O subsystem bottleneck solution.

Today an I/O device can mean just about anything. It could be a single physical disk, a disk partition, a raid array, or some combination of these. The key when forecasting I/O is whatever you call a "device" is a device throughout the entire forecast. If a device is a 5 disk raid array, then make sure whenever a device is referenced, everyone involved understands the two devices are actually two raid arrays, each with five physical disks. If your device definition is consistant, you'll avoid many problems.

The forecasting formulas we'll use below assume the I/O load is perfectly distributed across all devices. While today's I/O subsystems do a fantastic job at distributing I/O activity, many DBAs do not. I have found that while an array's disk activity is nearly perfectly balanced, the activity from one array to the next may not be very well balanced. Hint: If an I/O device is not very active (utilization less than 5%), do not count it as a device. It is better to be conservative then aggressive when forecasting.

Before you are inundated with formulas, it's important to understand some definitions and recognize their symbols.

S : Time to service one workload unit. This is known as the service time or service demand. It is how long it takes a device to service a single transaction. For example, 1.5 seconds per transaction or 1.5 sec/trx. For simplicity sake, this value will be derived.

U : Utilization or device busyness. Commonly shown as a percentage and that's how it works in our formulas. For example, in the formula it should be something like 75% or 0.75, but not 75. This value can be gathered from both sar or iostat.

λ : Workload arrival rate. This is how many transactions enter the system per unit of time. For example, 150 transactions each second or 150 trx/sec. When working with Oracle, there are many possible statistics that can be used for the "transaction" arrival rate. For simplicity sake, this value will be derived and will refer to the general workload.

M : Number of devices. You can get this from the sar or iostat report. Be careful not to count both a disk and a disk's partition, resulting in a double count.

W : Wait time or more commonly called queue time. This is how long a transaction must wait before it begins to be serviced. For simplicity sake, this value will be derived.

R : Response time. This is how long it takes for a single transaction to complete. This includes both the service time and any queue/wait time. This will be gathered from the sar and iostat command (details below).

The IO formulas for calculating averages are as follows:

U = ( S λ ) / M (1)

R = S / (1 - U) (2)

R = S + W (3)

Before we dive into real-life examples, let's check these formulas out by doing some thought experiments.

Thought experiment 1. Using formula (1), if the arrival rate doubles, so will the utilization.

Thought experiment 2. Using formula (1), if we used slower disks, the service time (S) would increase, and therefore the utilization would also increase.

Thought experiment 3. Using formula (2), if we used faster devices, the service time would decrease, then the response time would also decrease.

Thought experiment 4. Using formula (2), if the device utilization decreased, the denominator would increase, which would cause the response time to decrease.

Thought experiment 5. Using formula (3), if we used a faster devices, service time would decrease, then the response time would also decrease.

While gathering I/O subsystem data is simple, the actual meaning of the data and how to apply it to our formulas is not so trivial. One approach, which is fine for low precision forecasting like this, is to gather only the response time, the utilization, and the number of devices. From these values, we can derive the arrival rate and service time.

Gathering device utilization is very simple as both sar –d and iostat clearly label these columns. However, gathering response time is not that simple. What iostat labels as service time is more appropriately the response time. Response time from sar –d is what you would expect, the service time plus the wait time. (For details, see "System Performance Tuning" by Musumeci and Loukides.)

There are many different ways we can forecast I/O subsystem activity. We could forecast at the device level or perhaps at the summary level. While detail level forecasting provides a plethora of numerical data, forecasting at the summary level allows us to easily communicate different configuration scenarios both numerically and graphically. For this article, I will present one way to consolidate all devices into a single representative device.

Capacity Planners like to call this process of consolidating or summarizing aggregation. While there are many ways to aggregate, the better the aggregation, the more precise and reliable your forecasts will be. For this example, our aggregation objective is to derive a single service time representing all devices and also the total system arrival rate. The total system arrival rate is trivial; it's just the sum of all the arrivals. Based upon the table below, the total arrival rate is 0.34 trx/ms.

To aggregate the service time, we should weight the average device service time based upon each respective device's arrival rate. But for simplicity and space, we will simply use the average service time across all devices. Based upon the table below, the average service time is 4.84 ms/trx.



Armed with the number of devices 5, the average service time 4.84 ms, and the system arrival rate of 0.34 trx/ms, we are ready to forecast!

Example 1. Let's say the I/O workload is expected to increase 20% each quarter and you need to now when the I/O subsystem will need to be upgraded. To answer the classic question, "When will we run out of gas?", we will forecast the average queue/wait time, response time, and utilization. The table below shows the raw forecast values.



Here's an example of the calculations with the arrival rate increased by 80% (arrival rate 0.71 trx/ms).

U = ( S λ ) / M = ( 4.84*0.71 ) / 5 = 0.69

R = S / (1 - U) = 4.84 / ( 1 – 0.69 ) = 15.46

W = R – S = 15.46 – 4.84 = 10.62

So what's the answer to our question? Technically speaking the system will operate with a 120% workload increase. But stating that in front of management is what I would call a "career decision." Looking closely at the forecasted utilization, the wait time, and the response time, you can see that once the utilization goes over 57%, the wait time skyrockets! Take a look at the resulting classic response time graph below.

SQL Formatter

A good sql formatter which helps to change the outlook of sql that captured from SQLAREA.

http://www.sqlinform.com

Oracle Monitoring/Tuning on Solaris

A good article from sun.com regarding oracle/database monitoring and tuning on sun platform.This document is published by sun.com itself.This document is containing....
1) Monitoring Memory
2) Monitoring Disks
3) Monitoring CPU
4) Monitoring Networks
5) Tuning Buffer Cache
6) Checking ISM (Intimate Shared Memory)


http://www.sun.com/blueprints/0602/816-7191-10.pdf

甲骨文中国区高层

《世界计算机》ICXO.COM ( 日期：2005-09-29)

昨日（28日），《第一财经日报》从甲骨文（中国）公司获悉，公司华东、华西区董事总经理李绍唐将于10月16日正式离开甲骨文公司。这是自去年甲骨文大中华区原总经理陆纯初离职之后，再一次的公司高层变动。

甲骨文亚太区区域高级副总裁Keith Budge在负责其本职工作同时，将担任过渡时期甲骨文华东、华西区董事总经理。

“李绍唐将继续在中国一家非竞争性的公司追求他的事业。”甲骨文公司相关负责人表示，将会在晚些时候公布正式继任者名单，不过他没有透露，李绍唐具体会去哪家公司。

据了解，李绍唐于2000年5月加入甲骨文，担任甲骨文台湾区董事总经理，并于2003年7月被任命为甲骨文华东、华西区董事总经理。

去年中，甲骨文调整了大中华区的组织结构，甲骨文大中华区原总经理陆纯初离职。这让李绍唐、李翰璋（甲骨文中国公司北方区董事总经理及大中国区电信行业总经理）和潘应麟（甲骨文中国公司华南和香港区董事总经理）三人组成了甲骨文中国最高层管理团队。李绍唐的离职则意味着三人团队的开始松动。

根据甲骨文2005财年报告，中国新许可证销售收入第一次在公司所在亚太市场中名列第一，全球名次也从3年前的第10位上升为第6位。作为甲骨文在中国最重要的三个区域之一，华东、华西地区对甲骨文中国整体业绩的增长作出了重要贡献。

Interview questions for aspiring Oracle apps DBAs

Naveen Nahata

08.28.2005



[With the IT job market so tight, every available position is typically met with an avalanche of applicants. Naveen Nahata offers this list of technical interview questions for Oracle E-Business Suite DBA applicants that helps him quickly weed out the poseurs. If you hiring managers have any similar questions, email me and I'll add them to the list. --Ed.]

Questions

1. What happens if the ICM goes down?
2. How will you speed up the patching process?
3. How will you handle an error during patching?
4. Provide a high-level overview of the cloning process and post-clone manual steps.
5. Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?
6. Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?
7. What could be wrong if you are unable to view concurrent manager log and output files?
8. How will you change the location of concurrent manager log and output files?
9. If the user is experiencing performance issues, how will you go about finding the cause?
10. How will you change the apps password?
11. Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.

Answers

1. All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.


2. You can merge multiple patches.
You can create a response file for non-interactive patching.
You can apply patches with options (nocompiledb, nomaintainmrc, nocompilejsp) and run these once after applying all the patches.

3. Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.

4. Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
Manual steps (there can be many more):

Change all non-site profile option values (RapidClone only changes site-level profile options).
Modify workflow and concurrent manager tables.
Change printers.

5. AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is the centralized repository.
When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files.

For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.

6. Check guest user/password in the DBC file, profile option guest user/password, the DB.
Check whether apache/jserv is up.
Run IsItWorking, FND_WEB.PING, aoljtest, etc.

7. Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_ entry in tnsnames.ora.

8. The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.

9. Trace his session (with waits) and use tkprof to analyze the trace file.
Take a statspack report and analyze it.
O/s monitoring using top/iostat/sar/vmstat.
Check for any network bottleneck by using basic tests like ping results.


10. Use FNDCPASS to change APPS password.
Manually modify wdbsvr.app/cgiCMD.dat files.
Change any DB links pointing from other instances.


11. Location: $FND_TOP/secure directory.
Significance: Points to the DB server amongst other things.
The application knows the name of the DBC file by using profile option "Applications Database Id."

Tracking the progress of long-running queries

Manish Upadhyay

09.14.2005 SearchOracle.com


Sometimes there are batch jobs or long-running queries in the database that may take a while to complete. This query will show the status of the query -- how much of it is completed. In other words, this may be viewed as a "progress bar" for the query. It has been tested on v. 9.2.0.4 on Tru64 and Windows. (Note: This tip is a modified version of a tip from Oracle documentation.)

SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE "% Complete" != 100
/

Resource-intensive SQL

Vasan Srinivasan

09.14.2005 SearchOracle.com


Here is a simple script to find the most resource-intensive SQL in the database. It has been of immense help to me several times. It has been used on 8.1.7.4 and 9.2.0.5. However, there may be a better way of doing this in 9i that I have yet to learn.

In the SQL below, I am ordering the results by the descending number of executions, but by changing the order to refer to the dre or bge columns, you can find the SQLs with the most disk reads or buffer gets respectively.

select a.executions,
a.disk_reads,
a.disk_reads/a.executions dre,
a.buffer_gets,
a.buffer_gets/a.executions bge,
b.username,
a.first_load_time,
a.sql_text
from v$sql a, all_users b
where a.executions > 0
and a.parsing_user_id = b.user_id
order by 1 desc;

Monitoring rollback progress

QIANGHUA MA
09.14.2005 SearchOracle.com

When a large transaction takes a long time to rollback, it is good to know how much of the rollback is done and estimate how long it is going to take. Given the session's sid, it can be done with the simple statement below, tested on Oracle9i. When a transaction is rolling back, the t.used_ublk and t.used_urec will decrease until they become 0. By sampling the two measures at different points of time, you can calculate how fast the rollback is and when it is going to complete.

SELECT t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr=t.addr
and s.SID =:sid;