Sunday, May 17, 2009

Understanding JDBC Connections From the eBusiness Middle Tier

In this article I will describe the basics of configuring and monitoring JDBC connections between the eBusiness Web Tier and the Database, then cover some common issues to help identify root causes for both Release 11i and Release 12 of eBusiness Suite.

11i Architecture:

Brief overview

In general, whenever a functional page requires data from the database, it makes a call to a lower code level (the Java layer in the Application Object Library, also known as AOL/J) which handles the database connectivity. The AOL/J code provides a JDBC connections to the database through the Database Connection Pool.

You should note that the Java Connection Pool mechanism for eBiz is completely provided by eBiz code and does not use the mechanisms provided to pool connections through Java language directly, nor through the Application Server configuration.

Configuring JDBC connection pooling

The JDBC connection pool is created using the settings in the DBC file. The name, location and contents of this DBC file is controlled through AutoConfig. To modify any of the JDBC connection pool parameters, you should therefore use the techniques described in the following document to ensure changes are maintained in a supportable and consistent way:

The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/<>_ directory.

JDBC connection pool parameters and their use are covered in the following documents:

When considering any changes, you should also take into account that every JVM has its own JDBC connection pool. For example, if you have one Web Node with three OACoreGroup JVMs plus one XmlSvcsGrp JVM configured, then you will have a total of four JDBC connection pools with connections to your eBiz database

Monitoring the JDBC connection pool

It is always a good idea to understand how your environment looks when things are going well, to give you a baseline to compare against if you need to investigate any issues.

You will most certainly need to review the JDBC connection data if you are experiencing issues.

Monitoring JDBC Connections through Oracle Applications Manager (OAM)

Login to OAM directly or via the "System Administration" responsibility.
  1. Select the "JServ Usage" under the Monitoring section in OAM
  2. Click the "Expand all" link to list the Servers and all the JServ processes for OACoregroup. This shows memory usage, connections (including "Potentially Leaked") and Application Module information. You can click the "Add to support cart" to capture this page if Oracle Support are assisting your investigation.
  3. If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
  4. Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
  5. Click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections

mz_jdbc_oam:

The "old" way of gathering this data was to use the URL http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp but this will only give data for the one JVM you happen to connect to, so may not be so useful in multi-JVM environments.

Run SQL scripts to monitor database connections

Using SQL scripts will not give so much information as OAM, but can be used to provide useful summary information on a periodic basis. For example you may wish to include the information from the SQL below as part of your baseline data:

REM
REM START OF SQL
REM
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM END OF SQL
REM

NOTE - when looking at V$SESSION, the STATUS of JDBC connections tend to show as INACTIVE, this is normal and does not indicate a specific problem

Where Could It Go Wrong?

Issues with the JDBC connection pool tend to be of a nature whereby the number of database connections increase over time and do not seem to be released again. If this continues unchecked, then you may find the database will run out of sessions/processes and/or the Middle Tier JVM will run out of connections or memory.

A "quick fix" would normally be to restart Apache, but the underlying cause would need to be investigated further.

Issues can potentially occur at five different levels:

  1. Core Java Code
  2. AOL/J JDBC Code
  3. OA Framework
  4. Functional Code
  5. External Influences

I'll discuss each of these areas next.

Core Java code

Although eBiz does not use native Java connection pooling methods, we do rely on the underlying Java APIs generally. Any issues at this level will generally require the latest Java version to be installed

If you need to upgrade your Java version, see:

AOL/J JDBC code

As this is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.

Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1) describes a known issue with Apps 11i.

It is prudent to be on the latest JDBC driver patch, but should have at least applied one of the following patches:-

The latest JDBC patch can be found in:

You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier. For example, the latest version of JDBC drivers provided by patch 4899697 (9.2.0.8) is the same patch for all RDBMS versions.

OA Framework (OAF)

OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool). This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.

The AM pool is controlled by profile options, which are described in :-

Issues at this code level would tend to be either:

1. Issue with AM Pooling

You can test the effect of disabling AM pooling by setting the profile option "FND: Application Module Pool Enabled" to "No". Use this technique with caution if considering this for a production environment.

2. Specific bug where database connection is not released.

This would generally require patching.

Functional Code

Issues at this code level would tend to be a specific bug where a connection is not released.

External influences

Firewall timeouts are known to potentially cause an issue for JDBC connections. For details, see:

Although this note is for Apps 11i, the technical issue can also apply to Release 12 as well

Configuring eBiz to minimize JDBC connections

If investigating issues with JDBC connections increasing, it may be useful to minimise the database connections as much as possible by de-tuning the JDBC pool. This may reduce end user performance, so should be used with caution if considering this for a production environment.

To do so, you need to do both these steps:

1. Disable Application Module (AM) Pooling

This is necessary as the AM objects hold a JDBC connection whilst they are alive, even if they are not currently used.

Set the profile option "FND: Application Module Pool Enabled" (AMPOOL_ENABLED) at SITE level to a value of "No"

2. Set the JDBC connection pool parameters to release connections:

FND_JDBC_BUFFER_DECAY_INTERVAL=180
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_JDBC_BUFFER_DECAY_SIZE=50

Conclusion

Identifying issues with JDBC connections can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article has given you a better understanding of JDBC Pooling and where to start looking for issues.

Happy Learning !

Wednesday, May 6, 2009

Oracle Upgrade DB to 10g in oracle apps 11.5.10.2


10g (10.2.0.3) Database Upgrade

Note: upgrade from 9.2.0.7 to 10.2.0
OS: Sun 5. 10
Node: Multi node


10g Upgrade Information
Software Requirements
Software
Patches
Documents
Prerequisite
Upgrade
Before the Database Installation
Database Installation:
Database Upgrade
Post Upgrade Step (Conditional)
HR EEO Patch
Appendix
Initialization parameters set for 10G database (TAG) on 11i
Before 10g upgrade
After 10g Upgrade

10g Upgrade Information

Server: oadevdb03

SID: TAG

Port: (1547)

Location: /u26/oracle/oraTAG/TAGdb/10.2.0

Oracle DB version: 10.2.0.3.0

Time taken: 5 hours (approx)


Software Requirements

Software

� Oracle Database 10g (10.2.0.1) DVD.

Patches

� 4653225 (11.5.10 INTEROP PATCH FOR 10GR2)
� 5337014 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)
� 6880880 (OPatch 10.2)
� 5892355 (ASSERT IN KOPEPIC THROWING 22635 INTERNAL ERROR NEEDS TO BE REMOVED)
� 5871314 (PICKLER DOES ADD NULL VALUE IN IMAGE FOR KOTT* WHEN THE POINTER IS NULL)
� 5753621 (ONE-OFF REQUEST ON TOP OF 11.5.9 CU2 FOR BUG 5525015 Product Receivables)
� 5511810 (ETHNIC CODE AND JOB CATEGORY CHANGES FOR 2007)

Documents

� Metalink Note# 362203.1 (Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0))
� Oracle Database Upgrade Guide 10g Release 2
� Metalink Note# 353150.1 (OPatch Failing Validation Phase When Archiving Really Did Not Fail)
� Metalink Note# 216205.1 (Database Initialization Parameters for Oracle Applications Release 11i)
� Metalink Note# 438049.1 (How To Find RDBMS patchsets on Metalink)
� Metalink Note# 165195.1 (Using AutoConfig to Manage System Configurations with Oracle Applications 11i)



Prerequisite

� Cold Backup of entire instance.
Unix system parameter
Create magic ticket for following
Add set noexec_user_stack=1 in the /etc/system file (will be done by sysadmin)
Then reboot the system.
Perform this step a week before in Production.


Upgrade

We are following section 1 of Note # 362203.1.
Before the Database Installation

1. Verify software version (15 minutes)
� Apply patch 4653225 interoperability patch for 10gR2.
� Download patch 4653225 for both Us, Japanese Language.
� Make sure only database and listener are running.
� Count number of invalids objects
� Enable maintenance mode using adadmin utility.
� Perform following under dbnode (oadevdb03)
Unzip p4653225_11i_SOLARIS.zip
cd 4653225
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p4653225_11i_SOLARIS.zip
cd 4653225
use adpatch utility to apply patch
Perform following under dbnode (oadevdb03)
Unzip p4653225_11i_ja.zip
cd 4653225_JA
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p4653225_11i_ja.zip
cd 4653225_JA
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
Shutdown Database and listener.

2. Migrate to Oracle Portal 10g
� Skip as we are not using the Oracle Portal.

3. Deregister the current database server
� Skip as we are not planning to change database port, host, SID

4. Update application tier context file with new database listener port number
� Skip

5. Export OLAP analytical workspaces
� Skip

Database Installation:

6. Prepare to create the 10.2.0 Oracle home (10 minutes)
� Please follow Chapter 2 of 10g Upgrade guide also.
� Create following directory
cd /u26/oracle/oraTAG/TAGdb
mkdir 10.2.0
Change orainventory location in oadevdb03
cd /var/opt/oracle
vi oraInst.loc
Add
inventory_loc=/u26/oracle/oraTAG/TAGdb/10.2.0/oraInventory
and comment all other locations
Change .profile of oracle user (oraTAG)
Add
export ORACLE_HOME=/u26/oracle/oraTAG/TAGdb/10.2.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3
export PERL5LIB
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
export ORA_NLS10
export ORACLE_SID=TAG
Comment
export DBDIR=/u26/oracle/oraTAG/TAGdb/9.2.0
PATH=/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/bin:/usr/j2sdk1.4.2_06/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/ucb:/etc/vx/bin:/usr/ccs/bin:/etc:/usr/lib:/usr/openwin/bin:/home/oraTAG/edge:.
export PATH
PERL5LIB=/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/lib/5.00503:/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/lib/site_perl/5.005
Export PERL5LIB
. /u26/oracle/oraTAG/TAGappl/APPSORA.env

7. Install the base 10.2.0 software
� Install the base 10.2.0 software. (20 minutes)
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft
� ./runInstaller
� Need root access to run root.sh (especially in Production)


8. Install Oracle Database 10g Products from the 10g Companion CD
� Skip. Not Required.

9. Perform 10.2.0.3 patch set pre-installation tasks
� Download Patchset 3 (5337014)
� Unzip patch under /u26/oracle/oraTAG/soft/patchset

10. Perform 10.2.0.3 patch set installation tasks (15 minutes)
� Install the patchset
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft/patchset/Disk1
� ./runInstaller
� Need root access to run root.sh (especially in Production)

11. Create nls/data/9idata directory (5 minutes)
� cd $ORACLE_HOME (10g Oracle Home)
� check perl location ( it should be /usr/bin/perl)
� cd nls/data/old
� perl cr9idata.sql(pl)
� It will create $ORACLE_HOME/nls/data/9idata directory
� After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.
20th � 27th November 2008Take cold bacup of entry system.

Cd $ORACLE_HOME/OPatch
./opatch apply /u26/oracle/oraTAG/soft/patchset/5871314

opatch lsinventory
% cd 5892355
% opatch rollback -id 5892355

1) echo $ORACLE_HOME(10.2.0.3)
2) check the oracle_home in .profile
3)/var/opt/oracle below the files
oratab and oraInst.loc
4) $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_08-b03"
5) check perl version and location
/usr/bin/perl
6) setenv OBJECT_MODE 32_64 before applying opatch
Ensure environment variable OBJECT_MODE is set to 32_64
# % setenv OBJECT_MODE 32_64 or $ export OBJECT_MODE=32_64

7) Please get the confom the Opatch under $ORACLE_HOME
8) read care full README.txt
9) incase of failures.



12. Apply additional 10.2.0.3.0 RDBMS patches (20 minutes-> opatch_apply.sh)
� Download all patches (5892355,5871314,5755471,5752399 and 6880880)
� Unzip the patch 6880880 under $ORACLE_HOME for latest OPATCH version.
� Unzip patch 5892355
� cd 5892355
� opatch apply
� Verify the patch application. (./opatch lsinventory)
� Download in the /u26/oracle/oraTAG/TAGdb/10.2.0
� Copy the old version Opatch folder.
� Please ignore following error message for all the Patches
The following warnings have occurred during OPatch execution:
1) OUI-67124:Verification of 'ar' actions failed. This is a known issue for this platform. Please check the note 353150.1 on Metalink for the process to manually verify the files.
OPatch Session completed with warnings.
� Unzip patch 5871314
� cd 5871314
� opatch apply
� Unzip patch 5755471
� cd 5755471
� opatch apply
� Apply patch 5752399 to avoid the problem in step 15
� Unzip patch 5752399
� cd 5752399
� opatch apply OPATCH.SKIP_VERIFY=true
6880880
Step1 cd /u26/oracle/oraTAG/TAGdb/10.2.0/Opatch

(Folder copy)
cp-Rp/u26/oracle/oraTAG/TAGdb/10.2.0/OPatch /u26/oracle/oraTAG/TAGdb/10.2.0/OPatch_old

(File copy)
cpp6880880_102000_SOLARIS64.zip /u26/oracle/oraTAG/TAGdb/10.2.0/p6880880_102000_SOLARIS64.zip
Database Upgrade

13. Shutdown Applications server processes and database listener
� On each application tier server node, shut down all server processes or services. Also shutdown database and listener.
� Already done.

14. Prepare to upgrade
� Done.
28 Nov 2008 18:30 Hours (Take cold bacup of entry system.)

15. Upgrade the database instance. (2 hours)
� Please follow Chapter 3 and 4 of 10g Upgrade guide.
� Run the Pre-Upgrade Information Tool
� Start separate session with old 9i oracle home setting and start database
� Copy the utlu102i.sql file from the ORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to old 9i oracle home.
� Login to sqlplus with sys user (10 minutes)
� spool 10gupg.log
� @ utlu102i.sql
� spool off;
� copy the 2 file utlu102i.sql and utltzuv2.sql (/tmp/utlu102i.sql , utltzuv2.sql)
� In Production, copy orapwPROD from old_oracle_home to new_oracle_home.
� Modify the initTAG.ora in new Oracle home with following changes

**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "large_pool_size" needs to be increased to at least 8388608
WARNING: --> "session_max_open_files" needs to be increased to at least 20

**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"


Issues Requiring Further Analysis Prior to Upgrading

� Deprecated CONNECT Role
skip
Database Links With Passwords
TIMESTAMP WITH TIMEZONE Datatype
No effect
Release 8.1.7 National Character Set
skip
Optimizer Statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(�SYS�); (20 minutes)
Shutdown database.
Copy the configuration file from old_home to new_home
Ifile
cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/admin /u26/oracle/oraTAG/TAGdb/10.2.0/admin
Please verify following variables are set(New oracle1og Home)
ORACLE_SID
ORACLE_HOME
ORA_NLS10
LD_LIBRARY_PATH
cd $ORACLE_HOME/rdbms/admin(New Oracle Home)
connect to sqlplus as sysdba (10 minutes)
startup upgrade
CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;(/u26/oracle/oraTAG/TAGdata/sysaux01.dbf)
Default $ORACLE_HOME/dbs/sysaux01.dbf Move to above the location.
Gather the invalid objects( Count and object name)
spool upgrade.log 29th nov 2008 (15.30 � 16.50)Hours
@/u26/oracle/oraTAG/TAGdb/10.2.0/rdbms/admin/catupgrd.sql
(1 hour)
spool off;
Next shutdown immediate, restart for normal operation, and then
Run utlrp.sql to recompile any invalid application objects.
@utlu102s.sql TEXT
@/u26/oracle/oraTAG/TAGdb/10.2.0/rdbms/admin/utlrp.sql
At time of process you can the status of invalid objects.
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
(to compile the invalids) (30 minutes) 29th nov 2008 16.52 hours
It does not compile all invalids. Recompile later thru adadmin.(43 Invalid)
Gather the invalid objects( Count and object name)

Select substr(owner,1,12) owner,substr(object_name,1,30) object,
substr(object_type,1,30) type, status from dba_objects where status <> 'VALID';

Note: before upgrade (225) after upgrade 42000 invalid objects

16. Modify Initialization parameters
� Follow Note # 216205.1 to change Database initialization parameter in Oracle Application 11i.
� Please refer the Appendix
16A shutdown
Strtaup pfile= /u26/oracle/oraTAG/TAGdb/10.2.0/dbs/initTAG.ora
Spfile create spfile from pfile;


17. Additional database configuration
� Done

18. Perform 10.2.0.3.0 patch set post installation tasks
� Skip.

19. Install Data Mining and OLAP
� Skip.

20. Natively compile PL/SQL code
� Skip.

After the Database Upgrade

21. Fix Korean Lexers
� Skip as we didn�t find the script drkorean.sql under @$ORACLE_HOME/ctx/sample/script

22. Apply RDBMS patches for Daily Business Intelligence
� Skip.

23. Import OLAP analytical workspaces
� Skip.

24. Start the new database listener (5 minutes)
� cp tnsnames.ora and listener.ora from OLD_ORACLE_HOME/network/admin/TAG to ORACLE_HOME/network/admin
cp -Rp /u26/oracle/oraTAG/TAGdb/9.2.0/network/admin /u26/oracle/oraTAG/TAGdb/10.2.0/network/admin
� edit listener.ora and make following changes
change the location of Oracle home and Log file
� lsnrctl start TAG
Export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
25. Run adgrants.sql (2 minutes)
� Copy $APPL_TOP/admin/adgrants.sql to ORACLE_HOME
� sqlplus "/ as sysdba" @adgrants.sql APPLSYS

26. Grant create procedure privilege on CTXSYS (2 minutes)
� Copy $AD_TOP/patch/115/sql/adctxprv.sql to ORACLE_HOME
� sqlplus apps/ @adctxprv.sql CTXSYS

27. Implement and run AutoConfig (30 minutes) -- > 19 Nov 2008 20:20 Hours
� Follow Section 8 from Note# 165195.1
� Copy AutoConfig to the RDBMS ORACLE_HOME
� cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil
On the Application Tier (OADEVAPP03):
Log in to the APPL_TOP environment
Create appsutil.zip file
perl /bin/admkappsutil.pl
This will create appsutil.zip in $APPL_TOP/admin/out

On the Database Tier:
FTP the appsutil.zip file to the ORACLE_HOME
cd ORACLE_HOME
unzip �o appsutil.zip

cd $ORACLE_HOME
ls *.env
vi TAG_oadevdb03.env ( change the oracle_home path and unset APPL_TOP)
Generate your Database Context File (1st Dec 2008 1.pm IST)
cd ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=APPS
Enter Hostname of Database server: oadevdb03
Enter Port of Database server: 1551(1547)
Enter SID of Database server: TAG
Enter the value for Display Variable: oadevdb03:1.0
The context file has been created at:
/u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/TAG_oadevdb03.xml
Generate and Apply AutoConfig Configuration files
cd $ORACLE_HOME/appsutil/bin
adconfig.sh contextfile=/u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/TAG_oadevdb03.xml
run autoconfig.sh in appstier (both dbnode and appsnode)
DB node :
� /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/scripts/TAG_oadevdb03 >./adautocfg.sh
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./adautocfg.sh
� Apps Node :
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./adautocfg.sh


28. Gather statistics for SYS schema (10 minutes) 1st Dec 2008 5.30pm
� Copy $APPL_TOP/admin/adstats.sql to ORACLE_HOME
� sqlplus "/ as sysdba"
� shutdown immediate;
� startup restrict;
� @adstats.sql;
� shutdown normal;
� startup;
� exit;

29. Re-create custom database links
� Not required.

PLATFORM=SunOS; export PLATFORM (NOTE:293441.1)

30. Re-create grants and synonyms(10 minutes) 3rd Dec 2008 8.30pm
Adadmin
Recreate grants and synonyms option
Choose option 4 from Main menu then choose option 2.
Compile APPS schema.
Choose option 3 from Main menu then choose option 1.
Make sure the number of invalids objects are 14.
New invalid objects
PER_STC_BUS (Package Body)
DEBUG_CARTX (Synonym)

31. Apply Oracle Receivables patch(5 minutes)
� Enable maintenance mode using adadmin utility.
Perform following under dbnode (oadevdb03)
Unzip p5753621_11i_SOLARIS.zip
cd 5753621
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p5753621_11i_SOLARIS.zip
cd 5753621
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
/u26/oracle/oraTAG/soft/patchset/5753621
/u26/oracle/oraTAG/soft/patchset/5753621/u5753621.drv
Post Upgrade Step (Conditional)

� Please test the adstpall.sh in oadevdb03. (10 minutes)
� Check if the entire concurrent managers are shutting down.
� If not please run the cmclean.sql script (134007.1)
� Make sure all the FNDLIBR are stopped.
� Login to sqlplus as apps user
o @cmclean.sql
o Commit;
� Test the adstpall.sh again
� Check the number of invalids after the 10g upgrade.

HR EEO Patch
� Apply HR EEO patch(30 minutes)
� Enable maintenance mode using adadmin utility.
� Gather invalid object again
� Perform following under dbnode (oadevdb03)
o Unzip p5511810_11i_SOLARIS.zip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip p5511810_11i_SOLARIS.zip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under dbnode (oadevdb03)
o Unzip p5511810_11i_ja.zip
o cd 5511810_JA
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip p5511810_11i_ja.zip
o cd 5511810_JA
o use adpatch utility to apply patch
� Disable maintenance mode using adadmin utility.
� Gather invalid object again
� Take Cold Backup of system.


Appendix

Initialization parameters set for 10G database (TAG) on 11i

Before 10g upgrade

db_name = TAG
control_files = /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/
oracle/oraTAG/TAGdata/cntrl02.dbf,/u26/oracle/oraTAG/TAGdata/cntrl03.dbf

db_block_size = 8192
compatible = 9.2.0
_system_trig_enabled = true

O7_DICTIONARY_ACCESSIBILITY = TRUE

#row_locking = always

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting


#audit_trail = true # if you want auditing

#max_enabled_roles = 100 # Some modules depend on this feature.
user_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/udump
background_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/bdump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/cdump
max_dump_file_size = 20480 # trace file size
timed_statistics = true

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200
#enqueue_resources = 32000 # Max. no of concurrent database locks
.
db_cache_size = 163577856
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE


undo_management=AUTO

undo_retention=1800

#undo_suppress_errors=FALSE

undo_tablespace=APPS_UNDOTS1

workarea_size_policy=AUTO

pga_aggregate_target=1G

olap_page_pool_size = 4194304
shared_pool_size = 300000000
shared_pool_reserved_size = 30000000
_shared_pool_reserved_min_alloc = 4100

java_pool_size = 52428800

#utl_file_dir = /usr/tmp,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/s
it1db/9.2.0/appsutil/outbound/SIT1_oadevdb03,/usr/tmp
utl_file_dir= /home/bicepsftp/files,/u26/oracle/oraTAG/sit1comn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/SIT1_oadevdb03,/usr/tmp
aq_tm_processes = 1
job_queue_processes = 2

# log_archive_start = true # if you want automatic archiving

parallel_max_servers = 8
parallel_min_servers = 0

optimizer_features_enable = 9.2.0

db_file_multiblock_read_count = 8


#optimizer_max_permutations = 2000 # Required for 11i setting

_like_with_bind_as_equality = TRUE

_sort_elimination_cost_ratio=5

_fast_full_scan_enabled = FALSE

query_rewrite_enabled = true

_sqlexec_progression_cost=2147483647

_index_join_enabled = FALSE

_b_tree_bitmap_plans = FALSE

streams_pool_size=50331648
large_pool_size=8388608
session_max_open_files=20

After 10g Upgrade

db_name = TAG
control_files= /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/oracle/oraTAG/TAGdata/cntrl02.dbf,/u26/oracle/oraTAG/TAGdata/cntrl03.dbf

db_block_size = 8192

compatible = 10.2.0

_system_trig_enabled = true

O7_DICTIONARY_ACCESSIBILITY = TRUE

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting

user_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/udump
background_dump_dest=/u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/bdump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/cdump

max_dump_file_size = 20480 # trace file size

timed_statistics = true

_trace_files_public = TRUE

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200

sga_target= 1G
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE

undo_management=AUTO

undo_tablespace=APPS_UNDOTS1

workarea_size_policy=AUTO

pga_aggregate_target=1G

olap_page_pool_size = 4194304

shared_pool_size = 300000000
shared_pool_reserved_size = 30000000

utl_file_dir = /home/bicepsftp/files,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/TAG_oadevdb03,/usr/tmp

aq_tm_processes = 1
job_queue_processes = 2

parallel_max_servers = 8
parallel_min_servers = 0

db_file_multiblock_read_count = 8

_like_with_bind_as_equality = TRUE

_sort_elimination_cost_ratio=5

_fast_full_scan_enabled = FALSE

_sqlexec_progression_cost=2147483647

_b_tree_bitmap_plans = FALSE

streams_pool_size=50331648
session_max_open_files=20


-----------------------------------------------------------------------------------------------------------------------
issues
PLATFORM=SunOS; export PLATFORM (add in the .profile in DBTier of DB node)
export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
unset NLS_LANG (in DB env file)

Happy Learning !



Understanding the SCN(System Change Number) in Oracle

Understanding the SCN

In order to understand how Oracle performs recovery, it’s first necessary to understand Oracle’s SCN in terms of the various places where it can be stored and how it’s used for instance and media recovery.

The SCN is an internal number maintained by the database management system (DBMS) to log changes made to a database. The SCN increases over time as changes are made to the database by Structured Query Language (SQL). By understanding how the SCN is used, you can understand how Oracle recovery works. Oracle9i enables you to examine the current SCN using the following SQL:

SQL> select dbms_flashback.get_system_change_number from dual;

Whenever an application commits a transaction, the log writer process (LGWR) writes records from the redo log buffers in the System Global Area (SGA) to the online redo logs on disk. LGWR also writes the transaction’s SCN to the online redo log file. The success of this atomic write event determines whether your transaction succeeds, and it requires a synchronous (wait-until-completed) write to disk.


Note: The need for a synchronous write upon commit is one of the reasons why the online redo log can become a bottleneck for applications and why you should commit as infrequently as is practical. In general, Oracle writes asynchronously to the database datafiles for performance reasons, but commits require a synchronous write because they must be guaranteed at the time they occur.

SCN and Checkpoints:

A checkpoint occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. The checkpoint process (CKPT) updates all datafiles and control files with the SCN at the time of the checkpoint and signals DBWn to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during recovery. Checkpoints occur automatically as follows:

  • Whenever a redo log switch takes place
  • Whenever the time set by the LOG_CHECKPOINT_TIMEOUT initialization parameter is reached
  • Whenever the amount of redo written reaches the number of bytes associated with the LOG_CHECKPOINT_INTERVAL

Typically, LOG_CHECKPOINT_INTERVAL is chosen so that checkpoints only occur on log switches. Oracle stores the SCN associated with the checkpoint in four places: three of them in the control file and one in the datafile header for each datafile.

The System Checkpoint SCN:

After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. You can access the checkpoint SCN using the following SQL:

SQL> select checkpoint_change# from v$database;


CHECKPOINT_CHANGE#
--------------------
292767

The Datafile Checkpoint SCN:

After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a single datafile in the control file:

SQL> select name,checkpoint_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767

The Start SCN:

Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile:

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767

The Stop SCN:

The stop SCN is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use:

SQL> select name,last_change# from v$datafile where name like '%users01%';

NAME LAST_CHANGE#
----------------------------------- ------------
/u02/oradata/OMFD1/users01.dbf

During normal database operation, the stop SCN is NULL for all datafiles that are online in read-write mode. SCN Values while the Database Is Up Following a checkpoint while the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match. The stop SCN for each datafile in the control file is NULL. SCN after a Clean Shutdown After a clean database shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293184

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%user%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293184 293184


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293184

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

SCN after an Instance Crash

The previous example showed the behavior of the SCN after a clean shutdown. To demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:
create table x(x number) tablespace users;

insert into x values(100);

If the instance is crashed by using SHUTDOWN ABORT, the previous queries on v$database and v$datafile return the following after the database is started up in mount mode:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293185

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293185


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293185

In this case, the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.

Recovery from an Instance Crash

Upon the next instance startup that takes place after SHUTDOWN ABORT or a DBMS crash, the Oracle DBMS detects that the stop SCN for datafiles is not set in the control file during startup. Oracle then performs crash recovery. During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back. In our example, the following transaction was active but not committed at the time of the SHUTDOWN ABORT, so it needs to be rolled back:

SQL> insert into x values(100);

After instance startup, the X table exists, but remains empty. Instance recovery happens automatically at database startup without database administrator (DBA) intervention. It may take a while because of the need to apply large amounts of outstanding redo changes to data blocks for transactions that completed and those that didn’t complete and require roll back.

Recovery from a Media Failure

Up until this point, the checkpoint start SCN in the datafile header has always matched the datafile checkpoint SCN number held in the control file. This is reasonable because during a checkpoint, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are both updated, along with the system checkpoint SCN. The following SQL shows the start SCN from the datafile header and datafile checkpoint SCN from the control file for the same file:

SQL> select 'controlfile' "SCN location",name,checkpoint_change# from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 293188
file header /u02/oradata/OMFD1/users01.dbf 293188

Unlike the v$datafile view, there is no stop SCN column in the v$datafile_header view because v$datafile_header is not used at instance startup time to indicate that an instance crash occurred. However, the v$datafile_header does provide the Oracle DBMS with the information it requires to perform media recovery. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is
required.

For example, media recovery is required if a media failure has occurred and the original datafile has been replaced with a backup copy. In this case, the start SCN in the backup copy is less than the checkpoint SCN value in the control file, and Oracle requests archived redo logs—generated at the time of previous log switches—in order to reapply the changes required to bring the datafile up to the current point in time.

In order to recover the database from a media failure, you must run the database in ARCHIVELOG mode to ensure that all database changes from the online redo logs are stored permanently in archived redo log files. In order to enable ARCHIVELOG mode, you must run the command ALTERDATABASE ARCHIVELOG when the database is in a mounted state.

You can identify files that need recovery after you have replaced a datafile with an older version by starting the instance in mount mode and running the following SQL:

SQL> select file#,change# from v$recover_file;

FILE# CHANGE#
---------- ----------
4 313401

In this example, file 4 is the datafile in the USERS tablespace. By reexecuting the previous SQL to display the datafile checkpoint SCN in the control file and the start SCN in the datafile header, you can see that the start SCN is older due to the restore of the backup datafile that has taken place:

SQL> select 'controlfile' "SCN location",name,checkpoint_change#
from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 313551
file header /u02/oradata/OMFD1/users01.dbf 313401
If you were to attempt to open the database, you would receive errors like the following:
ORA-01113: file 4 needs media recovery
ORA-01110: datafile 4: '/u02/oradata/OMFD1/users01.dbf'
You can recover the database by issuing RECOVER DATABASE from SQL*Plus while the database is in a mounted state. If the changes needed to recover the database to the point in time before the crash are in an archived redo log, then you will be prompted to accept the suggested name:
ORA-00279: change 313401 generated at 11/10/2001 18:50:23 needed for thread
ORA-00289: suggestion : /u02/oradata/OMFD1/arch/T0001S0000000072.ARC
ORA-00280: change 313401 for thread 1 is in sequence #72

Specify log: {=suggested | filename | AUTO | CANCEL}

If you respond to the prompt using AUTO, Oracle applies any archived redo logs it needs, followed by any necessary changes in the online redo logs, to bring the database right up to the last committed transaction before the media failure that caused the requirement for the restore.

So far, we’ve considered recovery scenarios where the goal is to recover the database to the most recent transaction. This is known as complete recovery. The RECOVER DATABASE command has several other options that enable you to recover from a backup to a point in time before the most recent transaction by rolling forward and then stopping the application of the redo log changes at a specified point. This is known as incomplete recovery. You can specify a time or an SCN as the recovery point. For example,

recover database until time '2001-11-10:18:52:00';
recover database until change 313459;

Before you perform incomplete recovery, it’s recommended that you restore a complete database backup first. After incomplete recovery, you must open the mounted database with ALTER DATABASE OPEN RESETLOGS. This creates a new incarnation of the database and clears the contents of the existing redo logs to make sure they can’t be applied.

Recovery from a Media Failure Using a Backup Control File

In the previous example, we had access to a current control file at the time of the media failure. This means that none of the start SCN values in the datafile headers exceeded the system checkpoint SCN number in the control file. To recap, the system checkpoint number is given by the following:

SQL> select checkpoint_change# from v$database;

You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this. The first is that you might have read-only tablespaces in your database. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace remains frozen in the control file.

The following SQL report output shows a database with a read-write tablespace (USERS) and read-only tablespace (TEST). The start SCN in the file header and the checkpoint SCN in the control file for TEST are less than the system checkpoint value. Once a tablespace is read only, checkpoints have no effect on the files in it. The other read-write tablespace has checkpoint values that match the system checkpoint:

SCN location NAME CHECKPOINT_CHANGE#
-------------------- ---------------------------------- ----------------
controlfile SYSTEM checkpoint 355390
file header /u02/oradata/OD2/users01.dbf 355390
file in controlfile /u02/oradata/OD2/users01.dbf 355390
file header /u02/oradata/OD2/test01.dbf 355383
file in controlfile /u02/oradata/OD2/test01.dbf 355383

The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers.

The following SQL shows an example where the system checkpoint SCN and datafile checkpoint SCN indicate an earlier change than the start SCN in the datafile header:

SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where name like 'users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
------------------- ------------------------------ ------------------
controlfile SYSTEM checkpoint 333765
file header /u02/oradata/OD2/users01.dbf 355253
file in controlfile /u02/oradata/OD2/users01.dbf 333765

If try you to recover a database in the usual way in this situation, Oracle detects that the control file is older than some of the datafiles, as indicated by the checkpoint SCN values in the datafile headers, and reports the following message:

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

If you want to proceed with recovery in this situation, you need to indicate to Oracle that a noncurrent control file—possibly containing mismatches in the SCN values identified by the previous error messages—is about to be specified for recovery by using the following command:
recover database using BACKUP CONTROLFILE;


Happy Learning !