NM.Mathan Oracle Apps DBA: February 2012

Monday, 20 February 2012

ORA-07445

ORA-07445

ORA-07445: exception encountered: core dump [kcbz_parse_keychain()+00d4] [SIGSEGV] [ADDR:0x4C69627261727918] [PC:0x101ECCA74] [Address not mapped to object]

That error is continuously come and then db is down

Solution:

Add the pfile prameter about trace

_kghdsidx_count=1

Tuesday, 14 February 2012

Troubleshooting

Troubleshooting

STEP 1: Try to Start the DB without ENV File:

[oracle@dev140 10.2.0]$ export ORACLE_SID=PROD

[oracle@dev140 10.2.0]$ export ORACLE_HOME=/oracle/PROD/db/tech_st/10.2.0/

[oracle@dev140 10.2.0]$ export PATH=$PATH:$ORACLE_HOME/bin

[oracle@dev140 10.2.0]$ echo $PATH

/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin:/ oracle/PROD/db/tech_st/10.2.0//bin

[oracle@dev140 10.2.0]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 20 14:57:22 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 1271540 bytes

Variable Size 427821324 bytes

Database Buffers 633339904 bytes

Redo Buffers 11309056 bytes

Database mounted.

Database opened.

SQL>

Step 2: Creating LISTENER / NET SERVICE NAME

Creating Listner using NETCA.

[oracle@dev140 bin]$ pwd

/oracle/PROD/db/tech_st/10.2.0/bin

[oracle@dev140 bin]$

[oracle@dev140 bin]$ ./netca

Step:3 creating context file and Inst_top

oracle@dev140 bin]$ pwd

/oracle/PROD/apps/apps_st/comn/clone/bin

[oracle@dev140 bin]$

set the CLASSPATH using Following Command.

[oracle@dev140 bin]$ ls

adaddnode.pl adcfgclone.pl adchkutl.sh adclonectx.pl adclone.pl

[oracle@dev140 bin]$ export CLASSPATH=/oracle/PROD/apps/apps_st/comn/java/classes

[oracle@dev140 bin]$ ./adclonectx.pl

CREATING CONTEXT FILE IN APPS TIER USING FOLLOWING COMMAND

[oracle@dev140 bin]$ ./adclonectx.pl retrieve

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adclonectx Version 120.23.12010000.1

Running:

/oracle/PROD/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -classpath /oracle/PROD/apps/apps_st/comn/clone/bin/../jlib/ojdbc14.jar:/oracle/PROD/apps/apps_st/comn/clone/bin/../jlib/xmlparserv2.jar:/oracle/PROD/apps/apps_st/comn/clone/bin/../jlib/java oracle.apps.ad.context.RetrieveContext

Log file located at /oracle/PROD/apps/apps_st/comn/clone/bin/RetrieveContext_12201730.log

Database Host [dev140]:

Database Host Domain [chainsys.com]:

Database Port [1521]:

Database SID:PROD

APPS Schema Name [APPS]:

APPS Schema Password:apps

The following context files are available:

[1] PROD_dev140 (APPL_TOP Context)

[2] PROD_dev140 (Database Context)

Select the context to retrieve [1]:1

Destination file path [/oracle/PROD/inst/apps/PROD_dev140/appl/admin/PROD_dev140.xml]:/oracle/PROD_dev140.xml---------------------------------->inew context file created and stored in this path.

Retrieved context file to /oracle/PROD_dev140.xml

Log file location: /oracle/PROD/apps/apps_st/comn/clone/bin/RetrieveContext_12201730.log

[oracle@dev140 bin]$

CREATING INST_TOP

[oracle@dev140 bin]$ ./adclonectx.pl contextfile=/oracle/PROD_dev140.xml

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adclonectx Version 120.23.12010000.1

Running:

Enter the APPS password : apps

Log file located at /oracle/PROD/apps/apps_st/comn/clone/bin/CloneContext_1220173221.log

Provide the values required for creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [dev140] :

It is recommended that your inputs are validated by the program.

However you might choose not to validate them under following circumstances:

-If cloning a context on source system for a remote system.

-If cloning a context on a machine where the ports are taken and

you do not want to shutdown the services at this point.

-If cloning a context but the database it needs to connect is not available.

Do you want the inputs to be validated (y/n) [n] ? : y----> IF WE GIVE YES INST_TOP WILL BE CREATED

Target System Database SID : PROD

Target System Database Server Node [dev140] :

Target System Base Directory : /oracle/PROD

Target System Tools ORACLE_HOME Directory [/oracle/PROD/apps/tech_st/10.1.2] :

Target System Web ORACLE_HOME Directory [/oracle/PROD/apps/tech_st/10.1.3] :

Target System APPL_TOP Directory [/oracle/PROD/apps/apps_st/appl] :

Target System COMMON_TOP Directory [/oracle/PROD/apps/apps_st/comn] :

Target System Instance Home Directory [/oracle/PROD/inst] :

Username for the Applications File System Owner [oracle] :

Group for the Applications File System Owner [dba] :

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [disabled] :

Do you want to preserve the Display [dev140:0.0] (y/n) ? : n

Target System Display [dev140:0.0] :

Target System Port Pool [0-99] : 0

Checking the port pool 0

done: Port Pool 0 is free

Report file located at /oracle/PROD/inst/apps/PROD_dev140/admin/out/portpool.lst

Complete port information available at /oracle/PROD/inst/apps/PROD_dev140/admin/out/portpool.lst

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp

2. /usr/tmp

3. /oracle/PROD/db/tech_st/10.2.0/appsutil/outbound/PROD_dev140

4. /usr/tmp

Choose a value which will be set as APPLPTMP value on the target node [1] : 1

Creating the new APPL_TOP Context file from :

/oracle/PROD/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp

The new APPL_TOP context file has been created :

/oracle/PROD/inst/apps/PROD_dev140/appl/admin/PROD_dev140.xml

Log file located at /oracle/PROD/apps/apps_st/comn/clone/bin/CloneContext_1220173221.log

contextfile=/oracle/PROD/inst/apps/PROD_dev140/appl/admin/PROD_dev140.xml

Check Clone Context logfile /oracle/PROD/apps/apps_st/comn/clone/bin/CloneContext_1220173221.log for details.

[oracle@dev140 bin]$ pwd

/oracle/PROD/apps/apps_st/comn/clone/bin

[oracle@dev140 bin]$

RUN ADCONFIG TO CREATING .ENV FILE.

[oracle@dev140 bin]$ pwd

/oracle/PROD/apps/apps_st/appl/ad/12.0.0/bin

[oracle@dev140 bin]$

[oracle@dev140 bin]$ ./adconfig.pl contextfile=/oracle/PROD/inst/apps/PROD_dev140/appl/admin/PROD_dev140.xml

Enter the APPS user password:

The log file for this session is located at: /oracle/PROD/inst/apps/PROD_dev140/admin/log/12201824/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.

Using CONFIG_HOME location : /oracle/PROD/inst/apps/PROD_dev140

Classpath : /oracle/PROD/apps/apps_st/comn/java/lib/appsborg2.zip:/oracle/PROD/apps/apps_st/comn/java/classes

Using Context file : /oracle/PROD/inst/apps/PROD_dev140/appl/admin/PROD_dev140.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...

Configuring AD_TOP........COMPLETED

Configuring FND_TOP.......COMPLETED

Configuring ICX_TOP.......COMPLETED

Configuring MSC_TOP.......COMPLETED

Configuring IEO_TOP.......COMPLETED

Configuring BIS_TOP.......COMPLETED

Configuring AMS_TOP.......COMPLETED

Configuring CCT_TOP.......COMPLETED

Configuring WSH_TOP.......COMPLETED

Configuring CLN_TOP.......COMPLETED

Configuring OKE_TOP.......COMPLETED

Configuring OKL_TOP.......COMPLETED

Configuring OKS_TOP.......COMPLETED

Configuring CSF_TOP.......COMPLETED

Configuring IGS_TOP.......COMPLETED

Configuring IBY_TOP.......COMPLETED

Configuring JTF_TOP.......COMPLETED

Configuring MWA_TOP.......COMPLETED

Configuring CN_TOP........COMPLETED

Configuring CSI_TOP.......COMPLETED

Configuring WIP_TOP.......COMPLETED

Configuring CSE_TOP.......COMPLETED

Configuring EAM_TOP.......COMPLETED

Configuring FTE_TOP.......COMPLETED

Configuring ONT_TOP.......COMPLETED

Configuring AR_TOP........COMPLETED

Configuring AHL_TOP.......COMPLETED

Configuring OZF_TOP.......COMPLETED

Configuring IES_TOP.......COMPLETED

Configuring CSD_TOP.......COMPLETED

Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

[oracle@dev140 bin]$

Step 3: Creating Appsutil using admkappsutil.

Source the new APPS .ENV file

[oracle@dev140 bin]$ pwd

/oracle/PROD/apps/apps_st/appl/ad/12.0.0/bin

[oracle@dev140 bin]$

[oracle@dev140 bin]$ ./admkappsutil.pl

OUTPUT OF THIS SCRIPT

Starting the generation of appsutil.zip

Log file located at /oracle/PROD/inst/apps/PROD_dev140/admin/log/MakeAppsUtil_12201842.log

output located at /oracle/PROD/inst/apps/PROD_dev140/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

[oracle@dev140 bin]$

MOVE THE APPSUTIL ZIP FILE TO RDBMS HOME LOCATION USING FOLLOWING COMMAND

[oracle@dev140 bin]$cp -pr /oracle/PROD/inst/apps/PROD_dev140/admin/out/appsutil.zip /oracle/PROD/db/tech_st/10.2.0/

UNZIP THIS FILE IN RDBMS HOME LOCATION

[oracle@dev140 10.2.0]$ unzip -o appsutil.zip

STEP :4 NO JRE FILE IN APPSUTIL RDBMS HOME.SO COPYING FROM THE COMMONTOP JRE DIR .USING THE FOLLOWING STEP.

[oracle@dev140 clone]$ pwd

/oracle/PROD/apps/apps_st/comn/clone

[oracle@dev140 clone]$ cp -pr jre /oracle/PROD/db/tech_st/10.2.0/appsutil

OR THIS COMMAND

[oracle@dev140 clone]$ cp -vR jre /oracle/PROD/db/tech_st/10.2.0/appsutil

STEP:5

CREATING CONTEXT FILE IN DBTIER USING FOLLOWING STEPS

SET YOUR OWN PROFILE

[oracle@dev140 10.2.0]$ . profile

[oracle@dev140 10.2.0]$ pwd

/oracle/PROD/db/tech_st/10.2.0

[oracle@dev140 10.2.0]$ cd appsutil/bin/

[oracle@dev140 bin]$ ./adbldxml.pl

OUTPUT:

Starting context file generation for db tier..

Using JVM from /oracle/PROD/db/tech_st/10.2.0/appsutil/jre/bin/java to execute java programs..

APPS Password: apps

The log file for this adbldxml session is located at:

/oracle/PROD/db/tech_st/10.2.0/appsutil/log/adbldxml_12201900.log

AC-00013: Error: File: /oracle/PROD/db/tech_st/10.2.0/network/admin/listener.ora is not a valid file as database SID could not be found for connect string: PROD

Could not Connect to the Database with the above parameters, Please answer the Questions below

Enter Hostname of Database server: dev140

Enter Port of Database server: 1521

Enter SID of Database server: PROD

The context file has been created at:

/oracle/PROD/db/tech_st/10.2.0/appsutil/PROD_dev140.xml

[oracle@dev140 bin]$

CREATING .ENV FILE BY USING ADCONFIG.PL

[oracle@dev140 bin]$ pwd

/oracle/PROD/db/tech_st/10.2.0/bin

[oracle@dev140 bin]$

[oracle@dev140 bin]$ ./adconfig.pl contextfile=/oracle/PROD/db/tech_st/10.2.0/appsutil/PROD_dev140.xml

OUTPUT:

Enter the APPS user password:

The log file for this session is located at: /oracle/PROD/db/tech_st/10.2.0/appsutil/log/PROD_dev140/12201906/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

Using ORACLE_HOME location : /oracle/PROD/db/tech_st/10.2.0

Classpath : :/oracle/PROD/db/tech_st/10.2.0/jdbc/lib/ojdbc14.jar:/oracle/PROD/db/tech_st/10.2.0/appsutil/java/xmlparserv2.jar:/oracle/PROD/db/tech_st/10.2.0/appsutil/java:/oracle/PROD/db/tech_st/10.2.0/jlib/netcfg.jar:/oracle/PROD/db/tech_st/10.2.0/jlib/ldapjclnt10.jar

Using Context file : /oracle/PROD/db/tech_st/10.2.0/appsutil/PROD_dev140.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db102

Updating rdbms type in Context file to 32 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

[oracle@dev140 bin]$ pwd

/oracle/PROD/db/tech_st/10.2.0/appsutil/bin

[oracle@dev140 bin]$

SET THE APPS ENV FILE AND START THE APPLICATION

Monday, 13 February 2012

Upgrade 11.5.10 to R 12.1.1 / DB 9.2.0.5.0 to 10.2.0.4.0

Upgrade 11.5.10 to R 12.1.1 / DB 9.2.0.5.0 to 10.2.0.4.0

STEP1: Apply Patch 7429271 (Application side)

1.[oracle@track admin]$ pwd

/home/oracle/Desktop/11ipatch/7429271/admin

[oracle@track admin]$

2.Copy the adgrants.sql from patch path to ORACLE_HOME

[oracle@track admin]$ cp adgrants.sql /oracle/APPS11/prod11idb/9.2.0/appsutil/admin/

3.Run the adgrants.sql script in sqlplus

SQL> @/oracle/APPS11/prod11idb/9.2.0/appsutil/admin/adgrants.sql apps

STEP2: Apply Patch 7705743 (Application side)

DB Side

Upgrade database Steps:

STEP 1: Down the DB/APPS /Listener

STEP 2:Install the 10g software & companion & upgrade patch

Step2.1:Install 10g(10.2.0.1) software

[oracle@track oracle]$ cd /192.168.3.142/Database10g/database10g/

[oracle@track database10g]$ ls

doc install response runInstaller stage welcome.html

[oracle@track database10g]$ ./runInstaller

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-08_09-50-58AM. Please wait ...[oracle@track database10g]$ Oracle Universal Installer, Version 10.2.0.1.0 Production

You have new mail in /var/spool/mail/oracle

Step2.2:Install 10g companion(select the 2 option(oracle 10g database product))

[oracle@track database10g]$ cd ..

[oracle@track Database10g]$ ls

10204Patchset companion database10g

[oracle@track Database10g]$ cd companion/

[oracle@track companion]$ ls

doc install response runInstaller stage welcome.html

[oracle@track companion]$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2

Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-08_10-08-00AM. Please wait ...[oracle@track companion]$ Oracle Universal Installer, Version 10.2.0.1.0 Production

[oracle@track companion]$

Step2.3:Install 10g Upgrade Patch(10.2.0.4).

[oracle@track Database10g]$ cd 10204Patchset/

[oracle@track 10204Patchset]$ ls

10204_buglist.htm install patch_note.htm response runInstaller stage

[oracle@track 10204Patchset]$

[oracle@track 10204Patchset]$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3

Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-08_12-23-09PM. Please wait ...[oracle@track 10204Patchset]$ Oracle Universal Installer, Version 10.2.0.4.0 Production

You have new mail in /var/spool/mail/oracle

[oracle@track 10204Patchset]$

STEP 3: # Apply additional 10.2.0.4 RDBMS patches

Apply the following patches:

* 6880880 or the latest OPatch version.

* 6521934

* 6600051

* 7496636

* 7497678

STEP 4: Soure the 9i database environment file

[oracle@track 10.2.0]$ cd /oracle/APPS11/prod11idb/9.2.0/

[oracle@track 9.2.0]$ . PROD11i_track.env

[oracle@track 9.2.0]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Feb 8 10:34:46 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 581506616 bytes

Fixed Size 452152 bytes

Variable Size 402653184 bytes

Database Buffers 167772160 bytes

Redo Buffers 10629120 bytes

Database mounted.

Database opened.

SQL> @/oracle/9ito10g/db/tech_st/10.2.0/rdbms/admin/utlu102i.sql ==> (This script takes from 10G home path)

Oracle Database 10.2 Upgrade Information Utility 02-08-2012 10:37:04

**********************************************************************

Database:

**********************************************************************

--> name: PROD11I

--> version: 9.2.0.5.0

--> compatible: 9.2.0

**********************************************************************

Logfiles: [make adjustments in the current environment]

**********************************************************************

--> The existing log files are adequate. No changes are required.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 7674 MB

--> ODM tablespace is adequate for the upgrade.

.... minimum required size: 10 MB

--> APPS_TS_QUEUES tablespace is adequate for the upgrade.

.... minimum required size: 72 MB

--> OLAP tablespace is adequate for the upgrade.

.... minimum required size: 15 MB

**********************************************************************

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

**********************************************************************

Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No deprecated parameters found. No changes are required.

**********************************************************************

Obsolete 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"

**********************************************************************

Components: [The following database components will be upgraded or installed]

**********************************************************************

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

--> JServer JAVA Virtual Machine [upgrade] VALID

...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)

...is required to be installed from the 10g Companion CD.

--> Oracle XDK for Java [upgrade] VALID

--> Oracle Java Packages [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [install]

--> Real Application Clusters [upgrade] INVALID

--> Oracle Data Mining [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] LOADED

--> OLAP Catalog [upgrade] VALID

--> Oracle OLAP API [upgrade] LOADED

--> Oracle interMedia [upgrade] VALID

...The 'Oracle interMedia Image Accelerator' is

...required to be installed from the 10g Companion CD.

--> Spatial [upgrade] VALID

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --> Passwords exist in some database links.

.... Passwords will be encrypted during the upgrade.

.... Downgrade of database links with passwords is not supported.

WARNING: --> Deprecated CONNECT role granted to some user/roles.

.... CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... ODM

.... OLAPSYS

.... MDSYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER APPLSYS has 1 INVALID objects.

.... USER APPS has 258 INVALID objects.

.... USER CTXSYS has 8 INVALID objects.

.... USER SYS has 261 INVALID objects.

**********************************************************************

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

**********************************************************************

--> New "SYSAUX" tablespace

.... minimum required size for database upgrade: 500 MB

PL/SQL procedure successfully completed.

SQL>

Create SYSAUX tablespace

SQL> create tablespace SYSAUX datafile '/oracle/APPS11/prod11idata/sysaux001.dbf' size 600m reuse

2 extent management local

3 segment space management auto

4 online;

Tablespace created.

SQL>

Check the INVALID Objects

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

275

SQL>

Compile the INVALID Objects

SQL> @?/rdbms/admin/utlrp.sql

STEP 5: Down the db

STEP 6: Move the 9i PFILE to 10G home/dbs

STEP 7: Source the 10G profile(environment)

[oracle@track admin]$ cd /oracle/9ito10g/db/tech_st/10.2.0/

You have new mail in /var/spool/mail/oracle

[oracle@track 10.2.0]$ vi profile

export ORACLE_HOME=/oracle/9ito10g/db/tech_st/10.2.0

export ORACLE_SID=PROD11i

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_TERM=xterm

export ORACLE_OWNER=oracle

export TNS_ADMIN=$ORACLE_HOME/network/admin

export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes12.zip

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

STEP 8: Startup the Database Upgrade mode

SQL> startup upgrade

STEP 9: Run the Script /oracle/9ito10g/db/tech_st/10.2.0/rdbms/admin/catupgrd.sql

Total Upgrade Time: 01:20:48

STEP 10: DOWN the DB and strtup the normal mode

STEP 11:Compile the INVALID Objects

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

111543

1 row selected.

SQL> @?/rdbms/admin/utlrp.sql

STEP 12: Create Listener

Copy the 9i listener/tns paste on 10g HOME/network/admin and change the path

[oracle@track PROD11i_track]$ pwd

/oracle/APPS11/prod11idb/9.2.0/network/admin/PROD11i_track

[oracle@track PROD11i_track]$

[oracle@track PROD11i_track]$ cp -pr *.ora /oracle/9ito10g/db/tech_st/10.2.0/network/admin/

STEP 13: Create appsutil folder using /AD_TOP/bin/admkappsutil.pl and paste in 10g HOME

STEP 14:Create XML file using /appsutil/bin/adbldxml.sh

[oracle@track 10.2.0]$ cd appsutil/bin/

[oracle@track bin]$ perl adbldxml.sh tier=db appsuser=apps appspass=apps jtop=/oracle/9ito10g/db/tech_st/10.2.0/jre/1.4.2

Using JVM from /oracle/9ito10g/db/tech_st/10.2.0/jre/1.4.2/bin/java ...

The context file has been created at:

/oracle/9ito10g/db/tech_st/10.2.0/appsutil/PROD11i_track.xml

The log file for this adbldxml session is located at:

/oracle/9ito10g/db/tech_st/10.2.0/appsutil/log/PROD11i_track/02101223.log

[oracle@track bin]$

STEP 15: Run the adconfig and give the newly created XML file

[oracle@track bin]$ perl adconfig.sh

Enter the full path to the Context file:

[oracle@track bin]$ perl adconfig.sh

Enter the full path to the Context file: /oracle/9ito10g/db/tech_st/10.2.0/appsutil/PROD11i_track.xml

Enter the APPS user password:

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

Using ORACLE_HOME location : /oracle/9ito10g/db/tech_st/10.2.0

Using java command : /oracle/9ito10g/db/tech_st/10.2.0/jre/1.4.2/bin/java

Java version : 1.4.2_14

Classpath : /oracle/9ito10g/db/tech_st/10.2.0/jre/1.4.2/lib/rt.jar:/oracle/9ito10g/db/tech_st/10.2.0/jdbc/lib/ojdbc14.jar:/oracle/9ito10g/db/tech_st/10.2.0/appsutil/java/xmlparserv2.zip:/oracle/9ito10g/db/tech_st/10.2.0/appsutil/java

Using Context file : /oracle/9ito10g/db/tech_st/10.2.0/appsutil/PROD11i_track.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db817

Updating rdbms type in Context file to 32 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

The log file for this session is located at: /oracle/9ito10g/db/tech_st/10.2.0/appsutil/log/PROD11i_track/02101235/adconfig.log

[oracle@track bin]$

DB Side is Finshed.

Application -Side Upgrade.

STEP1: Enable Multi Organization

==> Source the 11i environment file.

==> Run the adadmin and select 4^th option(Maintain Applications Database Entities menu) and select 6^th option(Convert to Multi-Org)

STEP2: Run the R12.1.1 stage in upgrade mode(select second option)

STEP3: db node configure on the page type the unknown Node name (only installing application)

Installation completed with out errors.

STEP4: Error

by mistake R12.1.1 installation on the time given the wrong DATABASE(PROD) name.

but my upgrade db name is PROD11i. so application is not connect to database.

Solution:

==>Run the COMM_TOP/clone/bin/adclonectx.pl

[oracle@track bin]$ perl adclonectx.pl contextfile=/oracle/9ito10g/inst/apps/PROD11i_track/appl/admin/PROD_track.xml

==> source the APPL_TOP environment file.

==>sqlplus apps/apps at the time no error means continue the next step.

STEP5: Apply the Patch of 7461070 (AD 12.1.1 upgrade driver)

STEP6: Apply the driver patch 6678700

==> Go to $AU_TOP/patch/115/driver

==> adpatch options=nocopyportion,nogenerateportion

Monday, 6 February 2012

Repair the Block Corruption

Repair the Block Corruption

ERROR1:

Corrupt Block Found
         TSN = 381, TSNAME = APPS_TS_TX_DATA
         RFN = 402, BLK = 160267, RDBA = 1686270475
         OBJN = 158628, OBJD = 262427, OBJECT = AD_SNAPSHOT_FILES, SUBOBJECT =
         SEGMENT OWNER = APPLSYS, SEGMENT TYPE = Table Segment

ERROR2:
Corrupt Block Found
         TSN = 383, TSNAME = APPS_TS_SEED
         RFN = 407, BLK = 138012, RDBA = 1707219740
         OBJN = 240862, OBJD = 261266, OBJECT = JDR_PATHS_N1, SUBOBJECT =
         SEGMENT OWNER = APPLSYS, SEGMENT TYPE = Index Segment

SOLUTION ERROR-1:

STEP1: Create Scripts of block1.sql,block2.sql,block3.sql,block4.sql,block5.sql,block6.sql,block7.sql

Script-1:
[oracle@ebs1 block]$ vi block1.sql
BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'APPS_TS_SEED'
);
END;
/

Script-2:
[oracle@ebs1 block]$ vi block2.sql

BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'APPS_TS_SEED'
);
END;
/

Script-3:
[oracle@ebs1 block]$ vi block3.sql

SET SERVEROUTPUT ON
DECLARE
vCorruptBlocks INT := 0;
BEGIN
DBMS_REPAIR.CHECK_OBJECT
(
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME => 'AD_SNAPSHOT_FILES',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT => vCorruptBlocks
);
DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
END;
/
~

Script-4:
[oracle@ebs1 block]$ vi block4.sql

column object_name format a10
column repair_description format a28
set lines 10000
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;

Script-5:
[oracle@ebs1 block]$ vi block5.sql

SET SERVEROUTPUT ON
DECLARE
vBlocksFixed INT := 0;
BEGIN
DBMS_REPAIR.FIX_CORRUPT_BLOCKS
(
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME=> 'AD_SNAPSHOT_FILES',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT => vBlocksFixed
);
DBMS_OUTPUT.PUT_LINE('Number of blocks fixed: ' || TO_CHAR(vBlocksFixed));
END;
/
~
~

Script-6:
[oracle@ebs1 block]$ vi block6.sql

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
(
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME => 'AD_SNAPSHOT_FILES',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     FLAGS => DBMS_REPAIR.SKIP_FLAG
);
END;
/

Script-7:
[oracle@ebs1 block]$ vi block7.sql

select owner , table_name , skip_corrupt
from   dba_tables
where table_name = 'AD_SNAPSHOT_FILES';

STEP 2: Execute the Scripts

SQL> @/oracle/block/block1.sql

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block2.sql

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block3.sql
Number of blocks corrupted: 1

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block4.sql

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ----------------------------
AD_SNAPSHO     160267         6148 TRUE       mark block software corrupt
T_FILES

SQL> @/oracle/block/block5.sql
Number of blocks fixed: 0

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block6.sql

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block7.sql

OWNER                          TABLE_NAME                     SKIP_COR
------------------------------ ------------------------------ --------
APPLSYS                        AD_SNAPSHOT_FILES              ENABLED

SQL> conn applsys/apps
Connected.
SQL> create table AD_SNAPSHOT_FILES_BK as select * from AD_SNAPSHOT_FILES;

Table created.

SQL> select count(*) from AD_SNAPSHOT_FILES_BK;

COUNT(*)
----------
   1735127

SQL> drop table AD_SNAPSHOT_FILES;

Table dropped.

SQL> create table AD_SNAPSHOT_FILES as select * from AD_SNAPSHOT_FILES_BK;

Table created.

SQL> select count(*) from AD_SNAPSHOT_FILES;

COUNT(*)
----------
   1735127

SQL> conn sys/sys as sysdba
Connected.
SQL> @/oracle/block/block3.sql
Number of blocks corrupted: 0

PL/SQL procedure successfully completed.

SOLUTION ERROR-2:

SQL> conn applsys/apps
Connected.
SQL> alter index JDR_PATHS_N1 rebuild online;

Index altered.

Tuning:Finding SQL_TEXT based on the Concurrent Request

Finding SQL_TEXT(Query) based on the Concurrent Request

STEP1:

SQL> select REQUEST_ID,ORACLE_SESSION_ID,ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=363254;

REQUEST_ID ORACLE_SESSION_ID ORACLE_PROCESS_ID
-----------------------------------------------
363254     1135502           22933

STEP2:

SQL> select SPID,PID from v$process where spid=22933;

SPID                                        PID
------------------------------------ ----------
22933                                        70

STEP3:

SQL> SELECT b.sid,a.spid, b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=22933;

SID SPID SQL_ID
--------------------
999 22933 39c3gyvun83f9

SQL> select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id='39c3gyvun83f9';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname ,
estimate_percent => :estimate_percent , degree => :degree , method_opt => :meth
od_opt , block_sample => FALSE , partname => :partname , granularity => :granula
rity , stattab => :stattab , statown => :statown,                no_invalidate =
> FALSE ,               cascade => TRUE               ); end;
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname
,

SQL> select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=363254));

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname ,
estimate_percent => :estimate_percent , degree => :degree , method_opt => :meth
od_opt , block_sample => FALSE , partname => :partname , granularity => :granula
rity , stattab => :stattab , statown => :statown,                no_invalidate =
> FALSE ,               cascade => TRUE               ); end;
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname
,

(OR)

--------------------
Only Enter the REQUEST_ID

select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=&CONCURRENT_REQUEST_ID));

Enter value for concurrent_request_id: 366200
old   1: select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=&CONCURRENT_REQUEST_ID))
new   1: select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=366200))

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
SELECT TABLE_NAME , PARTITIONED FROM DBA_TABLES DT WHERE OWNER = UPPER(:B1 ) AND
( IOT_TYPE <> 'IOT_OVERFLOW' OR IOT_TYPE IS NULL ) AND TEMPORARY <> 'Y' AND NOT
EXISTS (SELECT NULL FROM FND_STATS_HIST FSH WHERE DT.OWNER =FSH.SCHEMA_NAME AND
FSH.REQUEST_ID = :B3 AND FSH.OBJECT_TYPE ='CASCADE' AND FSH.HISTORY_MODE=:B2 AN
D DT.TABLE_NAME = FSH.OBJECT_NAME AND LAST_GATHER_END_TIME IS NOT NULL ) AND NOT
EXISTS (SELECT NULL FROM FND_EXCLUDE_TABLE_STATS FETS, FND_ORACLE_USERID FOU ,
FND_PRODUCT_INSTALLATIONS FPI WHERE FOU.ORACLE_USERNAME=UPPER(:B1 ) AND FOU.ORAC
LE_ID =FPI.ORACLE_ID AND FPI.APPLICATION_ID = FETS.APPLICATION_ID AND DT.TABLE_N
AME = FETS.TABLE_NAME ) AND NOT EXISTS ( SELECT NULL FROM DBA_TAB_STATISTICS DTS

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
WHERE DTS.STATTYPE_LOCKED IS NOT NULL AND DTS.TABLE_NAME=DT.TABLE_NAME AND DTS.
OWNER =DT.OWNER ) ORDER BY TABLE_NAME
SELECT TABLE_NAME , PARTITIONED FROM DBA_TABLES DT WHERE OWNER = UPPER(:B1 ) A
ND

Wednesday, 1 February 2012

Enable India Localization

Enable India Localization:

STEP1:

Click System Administrator ---> Oracle Applications Manager -->License Manager ---> Country-specific Functionalities ----> Select Singapore ----> Click Next ----> Click Submit ----> Click Ok.

STEP2: Check the India Localization licensed or Not

Click Syatem Administrator ---> Oracle Applications Manager --> Licensed Products ---> check Status of JA-Asia/Pacific Localizations licensed or Not If status is licensed means India localization is enabled

STEP3:

Go to $JA_TOP/patch/115/driver/

[oracle@track driver]$ pwd

/oracle/PROD/apps/apps_st/appl/ja/12.0.0/patch/115/driver

[oracle@track driver]$ ls

ja12axdo.drvx jai5188052.drv

[oracle@track driver]$

STEP4: Using adadmin utility and enable the maintenance mode

STEP5: Apply the patch(jai5188052.drv)

Go to $JA_TOP/patch/115/driver/

Using adpatch utility and give the driver file of jai5188052.drv

is used to enable triggers of india localization.