Upgrade from 11.5.10.2 to R12 Upgrade Oracle Applications from 11.5.10.2 to R12
System Overview

Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux update 5
Type: Single Node instance
Applications OS User: applmgrupd
Installed directory: /d01/oracle/singlenode
Database 9i OS User : oracleupd
9i Database Home = /d01/oracle/singlenode/testdb/9.2.0
10g Database Home = /d01/oracle/singlenode/testdb/10.2.0
Instance SID: TEST
Host: linux1

Task – To Upgrade Oracle Applications 11.5.10.2 to R12
Note: Please use vision instance as it is already Multi-Org, Multi-Org is mandatory in R12

Note: The below upgrade is only for testing purposes, not to be implemented in production servers, please do refer metalink for more information.




Upgrade path
• Applications R11.5.6 and below requires to first upgrade to R11.5.10.2/10gR2
• Applications R11.5.7 and up can be directly upgraded to R12
• Database in all releases must have been upgraded to 10gR2 (11.5.9.2 / 11.5.10.2) or should be done during upgrade to R12 (11.5.9.1/11.5.10.1 and below



Here are steps to upgrade Oracle Application 11.5.10.2 to R12, let’s split the activity into 6 steps
1> Please do upgrade the database from 9.2.0.6 to 10.2.0.3, the steps are available in the below link

2> Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

3> Run rapidwiz to create R12 code tree

4> Apply the below patches in R12 environment
4502962 – R12 Minipack

5> Configuration Phase – R12

6> Apply the online help

2> Apply the below patches in your existing 11.5.10.2 environment
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
*5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

Enable the maintenance mode

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log
menu_option=ENABLE_MAINT_MODE workers=4

-- Apply Patch 4712852
unzip p4712852_11i_LINUX.zip
cd 4712852

-- Takes 5 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=4712852.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/4712852 driver=u4712852.drv workers=4
-- Apply patch u5753359.drv
unzip p5753359_11i_LINUX.zip

-- Takes 2 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5753359.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5753359 driver=u5753359.drv workers=4
-- Apply patch u5467526.drv

unzip p5467526_11i_LINUX.zip
cd 5467526
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5467526.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5467526 driver=u5467526.drv workers=4

-- Apply patch u5120936.drv
unzip p5120936_11i_GENERIC.zip
cd 5120936
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5120936.log
patchtop=/software/upgrades/11.5.10.2toR12/r12/5120936 driver=u5120936.drv workers=4
cd $AD_TOP/patch/115/sql

sqlplus apps/apps @adtums.sql /usr/tmp

-- Review TUMS report in /usr/tmp
-- Apply patch 5726010
unzip p5726010_11i_GENERIC.zip

cd 5726010/

sqlplus apps/apps @adgncons.sql apps apps
# modify the adcrtbsp.sql to specify the path for new tablespaces

sqlplus system/manager @adcrtbsp.sql

su - oracleupd

mkdir -p $ORACLE_HOME/appsutil/admin
cd $ORACLE_HOME/appsutil/admin
cp /software/upgrades/11.5.10.2toR12/r12/5726010/adgrants.sql .

sqlplus '/ as sysdba' @adgrants.sql applsys
-- Run rapidwiz to create R12 code tree
The installation will install R12 applications components and the database home
Select upgrade to Oracle Applications R12
Select upgrade actions screen
select create upgrade file system

enter the parameters required to set up your new environment run rapid install Here are the screen shots for the same













The Installation creates APPL_TOP, COMMON_TOP, INST_TOP and 10g Oracle Home
Move the new environment file into .bash_profile
su - applmgrupd
cd /d01/oracle/apps/apps_st/appl
mv .bash_profile .bash_profile_11.5.10
cat APPSVIS_linux1.env >> /home/applmgrupd/.bash_profile
Now onwards, you are connected to R12 application
-- Apply patch AD.A 4502962
-- takes 6 minutes
unzip p4502962_R12_LINUX.zip
cd 4502962/
adpatch

Please enter the name of your AutoPatch driver file: u4502962.drv
-- Run the American English upgrade patch driver
-- Takes 40 hours
cd $AU_TOP/patch/115/driver

adpatch options=nocopyportion,nogenerateportion

Please enter the name of your AutoPatch driver file: u4440000.drv
Run the NLS upgrade patch driver (conditional)
• Download the NLS Release 12 patch (4440000) for each active language in the system.
• Run each driver (u4440000.drv) with adpatch
• NLS patch driver has the same name as the American English patch driver.
Synchronize NLS and American English product patches (conditional)
• Generate manifest using perl $AD_TOP/bin/adgennls.pl
• Backup manifest file $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
• http://updates.oracle.com/TransSync
• Don’t check Translation Level Updates
• Upload manifest
• Download and apply patch
-- Configuration Phase
Disable maintenance mode
Run autoconfig

Run rapidwiz to configure and start processes
Sign on to R12 applications check concurrent managers

Run autoconfig
su - applmgrupd
perl $AD_TOP/bin/admkappsutil.pl
su - oracleupd
cd $ORACLE_HOME
cp /d01/oracle/inst/apps/TEST_linux1/admin/out/appsutil.zip .
unzip -o appsutil.zip
cd $ORACLE_HOME/appsutil/scripts/TEST_linux1
sh adautocfg.sh

Run rapidwiz to configure and start processes
-----------------------------------------
rapidwiz - Upgrade to Oracle Applications Release 12 -> Upgrade Action -> Select Configure Upgraded

Release 12 instance
$INST_TOP/admin/VIS_linux1.xml
/d01/oracle/inst/apps/TEST_linux1/appl/admin/TEST_linux1.xml








Note: I got the below error, but all services and the application is running.



Sign on to R12 applications check concurrent managers



Connect as sysadmin/sysadmin




-- Apply online help
cd $AU_TOP/patch/115/driver/
adpatch options=hotpatch,nocopyportion,nogenerateportion
Please enter the name of your AutoPatch driver file : u5051400.drv
-- Gather schema statistics for CBO
Release 12 employs cost-based optimization, which examines FND table statistics to determine the most
efficient access paths and join
methods for executing SQL statements. These statistics are gathered by the FND_STATS process, which
you initiate by running the
Gather Schema Statistics concurrent program.
From your Release 11i APPL_TOP, complete the following steps:
1. Log in to Oracle Applications with the System Administrator responsibility.
2. Navigate to the Submit Request window (Request > Run).
3. Submit the Gather Statistics program. (Schema)

References
1. Upgrading Oracle Applications Note# 289788.1
2. Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite R12 Note#403339.1
3. Oracle applications R11.5.10.2 Maintenance pack Installation Note#316365.1
4. Oracle Applications Upgrade Guide: Release 11i to Release 12 Part# B31566-01
5. Database Initialization Parameters for Oracle Applications Release 12 Note#396009.1
6. Oracle Applications Release Notes Release 12 Note# 405293.1
7. E-Business Suite Release 12 Upgrade Sizing and Best Practices Note# 399362.1
8. Upgrading to R12 Note# 414710.1
9. Maintenance Wizard Note# 215527.1

ORACLE PARALLEL DML

ORACLE PARALLEL DML

ORACLE PARALLEL DML
There is a myth that if the table is not partitioned, then parallel DML will not work. This is no
longer true. Several years ago I had updated 35 million rows table (size 40GB) using parallel
DML option in 8i. The parallel update ran 6X faster than procedural update using PL/SQL.

In this paper, you will learn how to update a BIG Table using parallel DML option.

All the tests given below are conducted in 10gR2.

First, I created a table, T1 using ALL_OBJECTS. The table has 10,000 rows.

One of the quickest way to find out the effectiveness (throughput) of parallelism in your system
is to run a query (SELECT) using parallel option, and measure the throughput by querying
V$PQ_TQSTAT table. (Note: V$PQ_TQSTAT will be renamed to V$PX_TQSTAT in 11g).
alter session set db_file_multiblock_read_count = 128;

select /*+ FULL(t1) parallel(t1,8) */ * from tamil.t1 ;

select process, num_rows, bytes from v$pq_tqstat order by process;

PROCESS NUM_ROWS BYTES
---------- ---------- ----------
P000 1300 664452
P001 1300 664551
P002 1300 664551
P003 1300 664551
P004 1200 613428
P005 1200 613428
P006 1200 613428
P007 1200 613428
QC 10000 5111817 -- Query coordinator
If you see the number of rows evenly distributed among the parallel slave processes, then it
indicates that your system is well configured for parallel processing.
Case # 1 - Simple update on NON-PARTITIONED Table
I have built a table, T1 that has 3.11 Million rows.

SQL> desc t1
Name Null? Type
-------------------------- -------- ------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from t1;

COUNT(*)
----------
3111872

SQL> select table_name, num_rows, blocks, empty_blocks,
last_analyzed from user_tables
where table_name= 'T1' ;

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZ
------------ ---------- ---------- ------------ -----------
T1 3116183 43085 0 05-MAR-2007
[I used DBMS_STATS.GATHER_TABLE_STATS procedure to gather table statistics with estimate
percent NULL(meaning 100 percent), but it reported more number of rows as you see in the
USER_TABLES].
Let me do the first test.

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

Elapsed: 00:00:00.01

SQL> update tamil.t1 set object_name = lower(object_name);
3111872 rows updated.

Elapsed: 00:09:34.33
SQL> commit;

Commit complete.

Elapsed: 00:00:00.13

A single process ran for 9 Min and 34 seconds.
Case # 2 Parallel Update on NON-PARTITIONED TABLE
I have shutdown the instance and restared.

SQL> alter session enable parallel dml ;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session force parallel dml parallel 8;

Session altered.

Elapsed: 00:00:00.01

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

Elapsed: 00:00:00.00

SQL> update /*+ full(t1) parallel(t1,8) */
2 tamil.t1 set object_name = lower(object_name);
3111872 rows updated.

Elapsed: 00:00:28.03
SQL> commit;

Commit complete.

Elapsed: 00:00:01.12
Conclusion:
The parallel update took just 28 seconds versus 9 min 34 seconds in a single process update.
By setting highest value to DB_FILE_MULTIBLOCK_READ_COUNT parameter, "PARALLEL DML"
and "FORCE PARALLEL DML" with correct degree of parallelism at the session level, you can
update a big table in a shorter time.

Do not attempt to process in parallel on a small table. In fact, it will run longer than what a single
process will take.

Agent Configuration Assistant Failed

Agent Configuration Assistant Failed

Today I hit the following error while trying to perform a standalone Grid Control Agent install:




There could be a number of reasons as to why the agent configuration assistant would fail while you are trying to install the Grid Control Agent. The commands which failed are listed in the $AGENT_HOME/cfgtoollogs/configToolsFailedCommands file.


[oracle@myserver agent10g] cat cfgtoollogs/configToolFailedCommands
# Copyright © 1999, 2009, Oracle. All rights reserved.
oracle.sysman.emcp.agent.AgentPlugIn


If you search metalink with “oracle.sysman.emcp.agent.AgentPlugIn” you will get a number of hits but the 2 most useful ones I have looked at are:

Subject:
Troubleshooting the error oracle.sysman.emcp.agent.AgentPlugIn raised by the Agent Configuration Assistant (AgentCA)
Doc ID:
740628.1


Subject:
Troubleshooting the 'oracle.sysman.emcp.agent.AgentPlugIn has failed' error
Doc ID:
734981.1


In my particular case, the agent failed because I entered an incorrect agent password during the install. Unfortunately there is no way to re-enter the password within OUI at this point so login to your server and follow the steps below:

1. cd $AGENT_HOME/bin
2. ./emctl stop agent
3. ./emctl unsecure agent
4. ./emctl secure agent
   
   • At this point you will be prompted for the agent registration password. If you input the incorrect password it will fail. Run this again until you enter the correct password.
   
   
5. ./emctl start agent

Now you should be able to login to your Grid Control console and see this target.

Setting EM Blackouts from the GUI and Command Line

Setting EM Blackouts from the GUI and Command Line

Oracle Enterprise Manager provides you with the ability to monitor your environments and alert you once specified thresholds have been reached. Blackouts allow you to suspend monitoring so you do not get notified. This is useful for scheduled maintenance windows, such as cold backups, where the application and/or database may not be available.

As well, blackouts also suspends data collection for the given targets. This means that certain metrics such as availability will not be affected.

To create a blackout from the GUI, login to Enterprise Manager, navigate to the target you would like to blackout and at the bottom of the page under Related Links you will see a Blackouts link. You will be brought to the following page:





On the screen above you can view any blackouts that may currently be in effect as well as create new ones. To create a new blackout click on the create button.



On this page you can create a name for the blackout, with the default being “Blackout-”. You can also select the targets you wish to set the blackout for. As you can see from the screenshot I am going to set a blackout for an infrastructure application server (infra10g).

You can also provide a reason for the blackout by click on the Reason drop down list. Quite a few are available, from Server Bounce to Security Patch. Jobs can be disabled by deselecting the Run jobs during the blackout checkbox. If your applying a security patch then you may not want a scheduled backup to run as it will either error or cause problems.



The next screen allows you to select which components within the target will be blacked out. I selected a full blackout but you can select certain members if the outage will only affect specific components.



This screen allows you to schedule the blackout. It can either start immediately or you can choose a date along with a duration. Blackouts can be repeating as well, so you only have to create one for that monthly maintenance window for example.



The last screen provides a summary and once you have finished reviewing click on the Finish button.

You can also set blackouts from the command line, which is useful if you have some maintenance scripts which are not executed from Enterprise Managers job system. I’ve only tested this on linux but it should be the same for windows.

To set a blackout for a list of targets:

emctl start blackout [[:]]…. [-d Duration]

To set a blackout for all targets on a host:

emctl start blackout [-nodeLevel] [-d ]

-nodeLevel tells the agent to stop monitoring all targets on the server.

-d Duration allows you to set a duration in the format of [days] hh:mm. ex. 1 02:05 means the blackout will last for 1 day, 2 hours and 5 minutes.

For example, to use this in a script in which all targets will be unavailable you would start a blackout at the beginning of the script and stop it at the end:

cd $AGENT_HOME/bin
./emctl start blackout alltargets-myserver –nodeLevel

cd $AGENT_HOME/bin
./emctl stop blackout alltargets-myserver

Troubleshooting

In case you hit issues with blackouts take a look at the following notes:

Subject: Agent Blackout Initiated By Emctl Command Not Ending Doc ID: 559577.1

Subject: EMDiagkit Download and Master Index Doc ID: 421053.1

Subject: How to Troubleshoot the EM 10gR1 Blackout Sub-system Doc ID: 284024.1

Subject: Troubleshooting Blackouts in EM 10g Grid Control using EMDiag Kit Doc ID: 300671.1

They provide alot of information and solutions to different scenarios. I hit an issue over the weekend in which the blackout didn’t end properly. When I tried to stop it from the command line:

[oracle@myserver ~]$ /u01/app/oracle/product/agent10g/bin/emctl stop blackout alltargets-myserver
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Blackout stop Error : Blackout name alltargets-myserver is invalid

When trying to end the blackout via Enterprise Manager:

Error stopping the blackout on "infra10g": ORA-20710: Agent-side blackouts cannot be edited or stopped ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 501 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 3262 ORA-06512: at "SYSMAN.MGMT_BLACKOUT", line 74 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_UI", line 1167 ORA-06512: at line 1 .

To fix this problem I performed the following:
1. Shutdown the agent on the target server myserver
2. Removed the blackouts.xml file under $AGENT_HOME/sysman/emd
3. Used note 421053.1 to install the EMDiag kit
4. Logged in as sysman on the Enterprise Managers repository database and executed the following query:

select blackout_guid, blackout_name
from mgmt_blackouts;

BLACKOUT_GUID BLACKOUT_NAME
-------------------------------- ----------------------
30E2956CA329F0E59FBDF50951F2578E alltargets-myserver

5. Then executed:

exec mgmt_diag.KillBlackout(HEXTORAW(‘30E2956CA329F0E59FBDF50951F2578E’));

6. Restarted the agent on myserver and when I looked in Enterprise manager the blackout had cleared.

I have seen the command used above for some other scenarios but not this one specifically. Before executing any commands in your environment please test first.

Oracle Applications Manager has some very handy sql script you can use when monitoring your EBS environment.

Custom sql scripts in OAM

I noticed Oracle Applications Manager has some very handy sql script you can use when monitoring your EBS environment.

When starting up OAM, navigate to Site Map ----> Monitoring ----> SQL Extensions
The following scripts are available...











There is also a way to add your own personal sql scripts to this page. When you follow the steps mentioned in Metalink note 216816.1, you will find your own scripts on this page.

So, here is what you should do.

1. create a custom directory in the $APPL_TOP/admin directory


2. now create a directory called 'sql' in this custom directory

/appl/oatestappl/admin/custom/sql

[oracle@vamisux32 sql]$ ls

oa_info.sql patchlevel_ebs.sql


3. Now place your custom sql scripts in this directory


4. Bounce Apache to see the scripts in OAM


I have tested it with two custom scripts, and after bouncing Apache,
the two scripts are visible under 'Custom Extensions'...














You can run the custom scripts by clicking the 'run report' button.

10g Enterprise Manager Grid Control and its advisors

Life after 10g Enterprise Manager Grid Control and its advisors

Filed under: Oracle 10g New Features,Oracle 11g New Features,Oracle Best Practices,Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 7:35 am

In my opinion history will show that, life of an Oracle DBA is divided into two stages; before and after 10g Enterprise Manager Grid Control :) I still remember some old days after paying to Oracle every possible extra licensing you may pay, everybody were paying lots of extra money to some bunch of other companies’ tools just to manage Oracle effectively. But somehow, someday Oracle decided to end its customers’ pain; Grid Control, Automatic Storage Management(ASM) and SQL Developer are examples of this strategy, so those were now the “good” old days just for some companies I guess.

Everybody even presidents needs, loves advisors. It is the same with Oracle, after 10g and with 11g Oracle is continuing to invest on its advisories. Memory, SQL Access and Tuning, Segment advisors have important parts in our lives now. But with this post I planned to discuss on some less popular advisors(I believe) like Undo, Redo and Automatic Checkpoint Tuning Advisors with 10g and also some 11g delights like Partitioning and Data Recovery advisors.

The Undo Advisor assists in correctly sizing the undo tablespace. The Undo Advisor can also be used to set the low threshold value of the undo retention period for any Oracle Flashback requirements. And to access the Undo Advisor in the Database Control you may follow; Administration > Undo Management > Undo Advisor

Also after 10g Rollback Monitoring improved; when a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, if the process takes more than six seconds. This view enables you to estimate when the monitored rollback process will finish.

SELECT TIME_REMAINING, SOFAR/TOTALWORK*100 PCT
FROM V$SESSION_LONGOPS
WHERE SID = 9
AND OPNAME =’Transaction Rollback’

Redo Log Tuning Advisory and Automatic Checkpoint Tuning are also new features introduced with 10G. The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance, however it must balanced out with the expected recovery time, as rule of thumb switching logs at most once every fifteen-twenty minutes. Undersized log files increase checkpoint activity and increase CPU usage.

Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary. Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files.

The redo logfile sizing advisory is specified by column optimal_logfile_size of v$instance_recovery. This feature require setting the parameter “fast_start_mttr_target” for the advisory to take effect and populate the column optimal_logfile_size.

You can try this with different settings of “FAST_START_MTTR_TARGET” but -
* If FAST_START_MTTR_TARGET is set to such a small value that it is impossible to do a recovery within its time frame then target_mttr field of v$instance_recovery contains the effective MTTR target which is larger than FAST_START_MTTR_TARGET.
* If FAST_START_MTTR_TARGET is set to such a high value that even in worst case entire buffer cache is dirty) recovery would not take that long, then target_mrrt field contains the estimated mttr in worst-case scnerios.

Code Listing 117 – Redo Log Tuning Advisory Demo

And to access the Redo Logfile Size Advisor you may follow; Administration > Storage > Redo Log Groups > Sizing Advice

10g also supports automatic checkpoint tuning. But by default, this feature is not enabled, because FAST_START_MTTR_TARGET has a default value of 0. It is an advancement over the MTTR related parameter introduced in earlier versions. The idea is to use the periods of low I/O usage to advance checkpoints and therefore improve availability.

To enable automatic checkpoint tuning, unset FAST_START_MTTR_TARGET or set it to a nonzero value(This is measured in seconds). If you set this parameter to zero this feature will be disabled. When you enable fast-start checkpointing, remove or disable(set to 0) the following initialization parameters:
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- FAST_START_IO_TARGET
Enabling fast-start checkpointing can be done statically using the initialization files or dynamically using -

SQL> alter system set FAST_START_MTTR_TARGET=10;

Enabling fast-start checkpointing increases the average number of writes per transaction that DBWn issues for a given workload. However, if the system is not already near or at its maximum I/O capacity, then fast-start checkpointing has a negligible impact on performance. View V$MTTR_TARGET_ADVICE will give information on the additional I/O’s on various values of FAST_START_MTTR_TARGET. But if -
- FAST_START_MTTR_TARGET is set to a low value – Fast-start checkpointing is more aggressive. The average number of writes per transaction that DBWn issues is higher in order to keep the thread checkpoint sufficiently advanced to meet the requested MTTR.
- FAST_START_MTTR_TARGET is set to a high value – Fast-start checkpointing in less aggressive, and the average number of writes per transaction that DBWn issues is lower.
- FAST_START_MTTR_TARGET is unset – automatic checkpoint tuning is in effect. Average number of writes per transaction is reduced but at the same time MTTR is highest.

So what about future releases and Oracle’s advisory strategy, I advise :) you to check 11g Manageability presentation’s fifth slide for some hints. After 11g a new advisor called Partition Advisor which analyzes the data access patterns and suggests the decision to choose the partitioning scheme and the partitioning column(s) will be assisting us especially during development and testing stages. By the way before 10g if you drop a partitioned table Oracle removed all the partitions at once, so time and resource consuming process. After 10g Release 2 when you drop a partitioned table, partitions are dropped one by one, maybe you already felt the change :)

Data Recovery Advisor will be another friend which automatically diagnose data failures and recommend repairs. You can repair failures manually or request that they be repaired automatically. Of course Enterprise Manager includes interfaces for these new advisors with 11g.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
Note:265831.1 – Automatic Checkpoint Tuning in 10g
Note:274264.1 – REDO LOGS SIZING ADVISORY
Note 180894.1 – “V$INSTANCE_RECOVERY”
Note 151062.1 – Init.ora Parameter “FAST_START_MTTR_TARGET” Reference Note
Note 30754.1 – Init.ora Parameter “LOG_CHECKPOINT_INTERVAL” Reference Note
Note 30755.1 – Init.ora Parameter “LOG_CHECKPOINT_TIMEOUT” Reference Note
Note 68931.1 – Init.ora Parameter “FAST_START_IO_TARGET” Reference Note
http://psoug.org/reference/dbms_advisor.html

Comments (2)

August 16, 2007

Using Automatic Database Diagnostic Monitor Manually

Filed under: Oracle 10g New Features,Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 1:36 pm

For Oracle after 10g the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository(AWR). The Automatic Database Diagnostic Monitor(ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event.

This decision tree Oracle developed in years under ADDM improves root-cause analysis and after this amount of research and development you pay additional cost for this feature even you are using Enterprise Edition.

The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

The background process Manageability Monitor Process(MMON) schedules the automatic running of the ADDM. You only need to make sure that the initialization parameters STATISTICS_LEVEL is set to TYPICAL or ALL, in order for the AWR to gather its cache of performance statistics. MMON schedules the ADDM to run every time the AWR collects its most recent snapshot. To view the ADDM’s findings:
* Use the OEM Database Control, the primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control.
* Run the Oracle-provided script addmrpt.sql, very similar to statspack or awr reports.
* Run DBMS_ADVISOR APIs
Note: The DBMS_ADVISOR package requires the ADVISOR privilege.

Code Listing 112 – Manual ADDM Demo

ADDM analysis finding consists of the following four components:
* The definition of the problem itself
* The root cause of the performance problem
* Recommendation(s) to fix the problem
* The rationale for the proposed recommendations

Example ADDM Report

FINDING 1: 31% impact (7798 seconds)
————————————
SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals. Alternatively, you may set the parameter “cursor_sharing” to “force”.
RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be using literals. Look in V$SQL for examples of such SQL statements.

In order to determining optimal I/O performance, ADDM uses the default value of the parameter DBIO_EXPECTED, which is 10 milliseconds.

SELECT PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE ADVISOR_NAME=’ADDM’
AND PARAMETER_NAME=’DBIO_EXPECTED’

Today’s hardwares can be significantly different, so you may set the parameter value one time for all subsequent ADDM executions of course for better advices. Since this is not an init.ora parameter, the parameter can be changed again with DBMS_ADVISOR supplied package;

DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ADDM’,'DBIO_EXPECTED’, 8000);

There are two important views you will be checking for the results of the ADDM analysis:

1. DBA_ADVISOR_RECOMMENDATIONS: This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

2. DBA_ADVISOR_FINDINGS: This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g at oracle-base.com
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 6 Automatic Performance Diagnostics

Nid revert

NID-00111: Oracle error reported from target database while executing

begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged, :nmchged); end;

ORA-01116: error in opening database file /gp_utyeb05/d001/temp01.dbf

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6185

ORA-06512: at line 1

Change of database ID failed.

Must finish change or REVERT changes before attempting any database operation.

DBNEWID - Completed with errors.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
This problem can occur on any platform.

Symptoms

NID failing due to missing tempfiles:
ORA-01116: error in opening database file /ora/u10/oradata/eu0289p/temp01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1

Cause

Bug 5861994

Issue reported matches those of bug 5861994, thus justifies cause. This bug is fixed in Oracle 11.2.

Solution

To implement the solution, please execute the following steps:

1. While database is mounted, drop the tempfiles:
SQL> alter database tempfile 1 drop;

NOTE: this workaround may give error ora-19951 "cannot modify control file until DBNEWID is completed"

2. Recreate the controlfile:
- SQL> alter database backup controlfile to trace resetlogs;

NOTE: Depending on how much NID had accomplished before failing, it may be required to recreate the controlfile with "set database" to new database name.

- SQL> startup nomount;
- SQL> create controlfile.....
- SQL> alter database open resetlogs;

OR

You can also request the backport patch for this bug to prevent this error again.

nid TARGET=SYS/oracle REVERT=YES LOGFILE=$HOME/nid.log