HOW TO RETRIEVE APPS PASSWORD in R12

HOW TO RETRIEVE APPS PASSWORD – R12

Steps to find apps password in R12:

1. 1)    Connect as SYSTEM or SYS USER
2. 2)    Create Function to decrypt the encrypt password
3. 3)    Query for the encrypted password
4. 4)    Query for decrypt the password
5. 5)    validate the apps password  

STEP 1:

[oracle@localhost]$sqlplus [system or sys]/<password>

STEP 2:

$SQL>create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)

RETURN VARCHAR2 AS LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;

/

Function created.

STEP 3:

$SQL>select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

ENCRYPTED_FOUNDATION_PASSWORD

——————————————————————————–

FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS

STEP 4:

$SQL>SELECT apps.decrypt_get_pwd(‘GUEST/ORACLE’,FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS) from dual;

Output:

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS')

..........................................................................................................

welcome

STEP 5:

$SQL> conn apps/welcome

Connected.

Wait Events in Oracle Database and RAC

Most common wait events in RAC environment:

Wait Events:

1.  Library Cache Wait Events: LC load lock , Lock and pin
2.  Mutex
3.  Row Cache Locks
4.  Buffer Cache Wait Events
5.  Transaction Wait Events

• Locks manage concurrency
• Pins ensures Cache coherency
• Lock is acquired on handle

Pinning: load object info on memory by reading from disk , if object or data block is not loaded.

• Request for locks an pins will wait until granted 

Library Cache Load Lock:
Load an object which is not present in memory.
Exclusive lock , no parallel lock on same object.


Library Cache Lock:
Once object is loaded into the memory / cache, that object acquires lock for further processing and no other session is able to modify it or make any changes over it.
This lock happens during parsing or compilation of body, packages function etc.

Library Cache Pin:

• This event manages library cache concurrency.
• When an object is used it acquires the lock. It object is pinned to use and other will wait till it is released.
• If it is waiting for long in that case we have to look into it.

Causes of Library Cache wait Events:

• High hard parse ( reading from disk)
• Shared pool is too small , which causes reloading. 

Troubleshooting Library cache wait vents:

• Reduce the reloads by increasing shared pool size.
• change the parameter cursor_sharing to force, before doing this - it has to be tested as it may change the execution plan.
• Increase session cached cursors.
• collect statistics in regular period to reduce invalids.
• Use literals for SQL statement to avoid hard parse.

OC4J Components exiting with status 204,3 and 150

R12-oafm, forms and oacore exiting with status 204, status 3 and status 150 after IP address change:

Recently, I had gone through a problem while, I changed the server IP and started the oracle application with adstrtal.sh script.

adstrtal.sh: Exiting with status 3 and adoafmctl.sh: exiting with status 204

Solution to fix this issue:

Problem: IP address is present in OC4J lock files, Once IP address is changed - lock files contain the wrong IP address which causes the problem to fail OC4J.

Steps to be followed to exit with 0:

1. Open a new session and set the environment with apps user.
2. Go to $ADMIN_SCRIPTS_HOME ( cd $INST_TOP/ADMIN/SCRIPTS)
3. adopmnctl.sh stop
4. Verify the staus once: ps -ef grep grep opmn
5. Delete the persistence directory with below commands:

• rm -r $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
• rm -r $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
• rm -r $INST_TOP/ora/10.1.3/j2ee/forms/persistence/* 

6. adopmnctl.sh start
7. Check if the issue has been resolved: adapcctl.sh status

Change or switch UNDO tablespace

Change or switch UNDO tablespace in Oracle database and issues of switching UNDO Tablespace (How to increase size of UNDO tablespace?):

Before I proceed with the topic, I would like to have an overview first. The Undo tablespace is used for several purposes: ROLLBACK, READ CONSISTENCY and FLASHBACK technology.

I have already discussed about Undo Tablespace in my earlier post and if you want to flashback, here is the URL:

http://appsdbaraz.blogspot.com/2013/12/tablespaces.html

It is very important to know, how to switch Undo Tablesspaces as it is required when we need to increase the size of undo tablespace. But sometimes, we face issues if we try to drop the old undo tablespace after switching to new undo tablespace and it says "Tablespace is in use". This is because of  pending transactions in your old undo tablespace and get "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.

How to switch to a new UNDO tablespace and drop the old one in oracle database?

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS01

SQL>

We can see the current undo tablespace as UNDOTBS01 and its displayed by initialization parameter undo_tablespace. Leave this session as its and cosider it as session1.

Start a new session (session2) and log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

SQL>update emp1 set salary = salary + 1000 where empno=83795;

1 row updated.

We have started a transaction by updating on emp1 table and undo data is written to a segment in the UNDOTBS01 tablespace. Now leave session2 as it is and go to session2 as sysdba console. Just to notice, We have not issued COMMIT or ROLLBACK,

UNDOTBS02 is a new UNDO tablespace created:

SQL> CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/u01/apps/oracle/db/UNDOTBS02.dbf'

         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

Switching the database to the new UNDO tablespace UNDOTBS02:

SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH;

System altered.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

-- Try to drop the tablespace but failed.

SQL>

We have chaged the undo tablespace to UNDOTBS02 with alter system set undo_tablespace=UNDOTBS02 and any new transaction will go into new undo tablespace i.e. UNDOTBS02. But undo transaction for already pending transaction(with session2 - commit or rollback was not issued) is still in the old undo tablespace with status as PENDING OFFLINE. Until or unless those data are there, it is not possible to drop the existing tablespace.

We can get the status of undo segment and name of UNDOTBS01 tablespace from rollname, rollstat , dba_segments tables.

We can get the SID and SERIAL# and go to the user and request to end the transaction by issuing ROLLBACK or COMMIT. If in case this is not possible (user will not be available for long time), we may go ahead and kill the session to release the old UNDO segment in UNDOTBS01 tablespace.

SQL> alter system kill session '$SID,$SERIAL#';

Now we can go ahead and drop UNDOTBS01 tablespace.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

If UNDO_RETENTION time is not exceeded, then we will get above error and need to wait till the UNDO_RETENTION time is over. After that we can drop this UNDO tablespace easily. By default UNDO_RETENTION is 15 minutes or 900 seconds.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Control File in Database

Control file in oracle database:

As I have already discussed about the database startup process and steps involved for database to open. You can also get the same from below URL:

http://appsdbaraz.blogspot.com/2013/12/database-startup-options.html

While we start database, first it uses the parameter file and nomount the database, then using control file mount the database and after checking if recovery is required and performs the required step and then opens the database.

Control file is compulsory for every database to mount. Control file is a binary file which records the physical structure of database. While we think of control file one question arises in our mind i.e.

What control file contains?

Control file contains information like:

• The Database name.
• Names and locations of data files and redo log files.
• The timestamp of database creation.
• The current log sequence number.
• Checkpoint information.
• Backup set and backup pieces information.

Control file is the critical file, which is required for database to startup. So to avoid loss of control file, we manage to have multiple copies of control file.

When we create database at least one control file is created with default values and for some operating systems, by default multiple copies of control files are created.

How to get the location of control file?

SQL> Show Parameter Control_Files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string     /u02/oracle/prod/control01.ctl
                                                      ,  /u02/oracle/prod/control02.ctl
                                                      ,  /u02/oracle/prod/control03.ctl

             
Create control file:

Creating Additional Copies, Renaming, and Relocating Control Files:

Multiplexing copy of control file or renaming a control file steps:

1.     Shut down the database.

2.     Using operating system command, copy the existing control file to new location.

3.     Edit the CONTROL_FILES parameter in the initialization parameter file to add new control file name or to change the existing control file name.

4.     Restart the database.

Manually Creating New Control Files:

Create Control file statement can be used to create a new control file for a database and also we can use the parameter values as per our requirement. We can give the database name whatever we want.

CREATE CONTROLFILE
 SET DATABASE prod
 LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
 '/u01/oracle/prod/redo01_02.log'),
 GROUP 2 ('/u01/oracle/prod/redo02_01.log',
 '/u01/oracle/prod/redo02_02.log'),
 GROUP 3 ('/u01/oracle/prod/redo03_01.log',
 '/u01/oracle/prod/redo03_02.log')
 RESETLOGS
 DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
 '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
 '/u01/oracle/prod/users01.dbs' SIZE 5M,
 '/u01/oracle/prod/temp01.dbs' SIZE 5M
 MAXLOGFILES 50
 MAXLOGMEMBERS 3
 MAXLOGHISTORY 400
 MAXDATAFILES 200
 MAXINSTANCES 6
 ARCHIVELOG;

  

Back Up Control Files:

Back up of control file is very important and It happens by default when you change your physical structure of your database. Some examples of structure changes are:

1.     Adding, dropping, or renaming datafiles.

2.     Adding or dropping a tablespace.

3.     Altering the read-write state of the tablespace.

4.     Adding or dropping redo log files or groups.

Back up control file using the below command from SQL prompt and stores it into binary format:
SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

We can also backup our control file to trace location:

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

When to create control file?

1. When you have lost your all control files.
2. When you want to rename your database.

Dropping Control Files:

Sometimes the location of cotrol file is changed, in such situation need to drop the existing control file and use the correct one. Control file can be dropped or renamed very easily as below steps.

1.     Edit the CONTROL_FILES parameter in the initialization parameter file to delete old control file name.

2.     Shut down the database.

3.     Start the database.

Reusable section of control file (control_file_record_keep_time) :

Control file has reusable section and it tracks ARCHIVE logs and BACKUP sets. control_file_record_keep_time is the initialization parameter which determines the reussable data to be stored for number of days. Default value of control_file_record_keep_time is 7 days. It can also be increased to any value whatever we want.

We can check the reusable period from SQL prompt as below:
SQL> show parameter Keep_time;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

Changing the value of control_file_record_keep_time using SQL command:
SQL> alter system set control_file_record_keep_time=14 scope=spfile;

System altered

Now RMAN can keep records of BACKUPS and ARCHIVE logs for 14 days.

Also need to configure control file autobackup on.
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Adpatch Issues - Network failure, Worker failed and Pre-requisite patch missing

Common Adpatch issues:

While we apply patch get multiple issues like

1. Worker failed patching
2. Network failure
3. Pre-Requisite patch is missing

Whenever adpatch gives error and stops patching because some reason. First check the status of worker with ADCTRL  utility and then go through your adpatch log and get some hint why patch is failed, then try to fix accordingly. Each issue has specific approach for fixing it.

Worker failed patching:

While we check the adpacth log at location PPL_TOP/ADMIN/SID/LOG/adpatch.log and we notice that adpatch failed because of worker has failed patching. We must immediately check the worker log at location APPL_TOP/ADMIN/SID/LOG/adworkxxx.log and check for the issue and fix it.

Once issue is fixed then follow below steps:

1. Login with APPLMGR user
2. Source the environment
3. Start adpatch session again and will ask for continuation of previous session
4. Say yes
5. It will continue patching with previous session only where it was left.

.rf9 is the file which helps the adpatch to determine where patch was left patching. Patching information is held into tables AD_DEFERRED_JOBS and FND_INSTALLED_PROCESSES.

Network Issue:

While we are applying application patch and network is failed in between. In this case we have to apply the patch again with below steps:

1. Login with APPLMGR user
2. Source the environment
3. adpacth - It will ask for continue to previous session
4. Say yes

If still patching is not working, Please check the log at location $APPL_TOP/ADMIN/SID/LOG/adpatch.log and sometimes it shows some autopatch error. In this case, we must check the worker status with ADCTRL utility with option 1. It shows as worker is still under running.

This is the actual problem and will stop the worker with ADCTRL utility selecting option 3 as below:

AD Controller Menu

---------------------------------------------------

1. Show worker status

2. Tell worker to restart a failed job

3. Tell worker to quit

4. Tell manager that a worker failed its job

5. Tell manager that a worker acknowledges quit

6. Restart a worker on the current machine

7. Exit

But if you check the status of worker again with ADCTRL utility, It shows as worker is still running. Problem is still not resolved and for resolving the issues to continue with patching worker status should change to failed.

So in this case will get into ADCTRL utility menu and opt for option 4: Tell manager that a worker failed its job. Now if you check the worker status, it will change to failed.

You can start adpatch again and continue with session to patch where patch had stopped working.

Pre-Requisite patch is missing:

if patch is failed because of pre-requisite patch missing. In this case , we need to take the backup of AD_DEFERRED_JOBS, FND_INSTALLED_PROCESSES and restart directory(containing .rf9 file). Location of restart directory is $APPL_TOP/ADMIN/SID/restart.

Drop these tables and start with new session and apply the pre-requisite patch and check the log.

One pre-requisite patch is complete, recover the backed up tables and restart directory start adpatch session and continue with previous session.

Steps to apply pre-requisite patch:

1.      Stop the worker with ADCTRL utility.

2.      Backup FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.

3.      Backup the .rf9 file located at $APPL_TOP/admin/<SID>/restart directory.

4.      Drop FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.

5.       Apply the pre-requisite patch.

6.      Restore the backed up .rf9 file or restart directory.

7.      Restore the FND_INSTALL_PROCESSES table.

8.      Restore the AD_DEFERRED_JOBS table.

9.      Start adpatch, it will resume where it stopped previously.

What are the tables created while we start adpatch session?

AD_DEFERRED_JOBS and FND_INSTALLED_PROCESSES tables are created.

What does AD_DEFERRED_JOBS and FND_INSTALLED_PROCESSES tables contain?

FND_INSTALLED_PROCESSES: adpacth creates FND_INSTALL_PROCESSES table, assigns each worker unique ID with new row for each worker. This table is used to store the information about the job for each worker. Once all jobs are completed, worker shuts down and drops FND_INSTALL_PROCESSES table.

AD_DEFERRED_JOBS: This table holds the information of failed (workers which has failed its job). This table is created on starting the session of adpatch utility and drops once it is complete.

From which table ADCTRL gathers information?

FND_INSTALLED_PROCESSES

Adpatch Modes and Options

Adpatch Modes and Options:

I have already discussed steps to apply adpatch and brief about maintenance mode in my other post and you can get the same from below.
How to apply adpatch?

http://appsdbaraz.blogspot.com/2013/12/applying-adpatch-or-autopatch-and.html

My main focus on this post would be discussing all the options of adpatch and modes of adpatch.

Modes of adpatch:  

1. Interactive mode
2. Non-Interactive mode
3. Test mode
4. Pre-Install mode

Interactive Mode:  

It is the default mode of adpatch when we don’t use anything. We can apply patch in this mode by going into patch directory and simply type adpatch in command prompt and hit enter.

Non-Interactive:

We can apply in this mode as:

$adpatch Interactive=no

It uses default files to store prompt values and can be applied from patch directory.

Test Mode:

This mode is purposely used to check the what exactly patch is doing and will do everything without actually changing anything.

We can apply patch in this mode as:

$adpatch apply=no

By default apply=yes

PreInstall Mode:

This mode is used to apply adpatch for any upgrade or consolidated update of utilities itself. So, it would be suggested to apply pre-install patch first.

When we apply patch in preinstall mode, it updates all AD utilities befre upgrade or update.

We can apply adpatch in this mode as:

$adpatch preinstall=y

Adpatch Options:

We have multiple adpatch options and based on the requirement we can use specific options. Here, I will try to cover most often used adpatch options.

Noautoconfig:

$adpatch options=noautoconfig

This options is used to skip the autoconfig execution as by default it executes the autoconfig and updates the configuration file if any template file is added. This option is helpful when we apply multiple adpatch and they are not merged.

Nocompiledb(without compiling database):

While we apply adpatch, It may create invalid objects and by default adpatch compiles the invalid objects after patch application. But in case you do not want to compile invalid objects to ensure the minimal autopatch time, you can apply adpatch as below:

$adpatch options=nocompiledb

Nocopyportion:

This option is used to apply adpatch without copying the files present in copy driver. This can be applies as:

$adpatch options=nocopyportion

Nocompilejsp:

Adpatch also compiles the java server pages(JSP) after completion of patch application and can be skipped using the option nocompilejsp as below:

$adpatch options=nocompilejsp

Nodatabaseportion:

Nodatabaseportion option is used to skip the adpatch the activity of database driver. It can be used as:

$adpatch options=nodatabaseportion

Nogenerateportion:

Nogenerateportion option is used to skip the adpatch activity of generate driver portion of the patch  and you can use the option nogenerateportion as below:

$adpatch options=nogenerateportion

Maintenance Mode disabled:

If you want to apply your adpatch without enabling maintenance mode, you can apply using the option as hotpatch as below:

$adpatch options=hotpatch

Nolink:

Nolink option is used to skip the relinking and can be used as:

$adpatch options=nolink

Nogenform:

Nogenform option is used to skip the generate form file and can be used as:

$adpatch options=nogenform

Maintainmrc:

Maintainmrc is used for adpatch to execute the maintain MRC schema as part of patch. By default maintain MRC is done for standard patch and is disabled for documentation and translation patches.

$adpatch options=nomaintainmrc

Noprereq:

This option is used to skip the checking of pre-requisite patches and by default it checks for pre-requisite patch. This option can be used as below:

$adpatch options=noprereq

Applying Adpatch or autopatch and maintenance mode

Application Patch – Adpatch or Autopatch:

Why do we need patch?

• For fixing a bug.
• Bug: Customization or by release of new product, bug is encountered.
• Adding new functionality / new module e.g. supplier management functionality.
• Applying new product enhancement.
• Upgrade to higher point release or release (like 12.X.1 to 12.X.2 or 12.1.X to 12.2.X)
• Upgrading to higher family pack
• It may contain bulk of patches.
• Bugs reported by customer.

Patch can be downloaded from metalink in zip format and while we unzip any patch, It contains driver file- which directs adpatch for its activity, readme.txt file which is useful for DBA to apply patch and do analysis with preinstall task and post install tasks.

Patch Utilities:

1. Command line utility
2. Web based patch utility – Oracle application manager(OAM)

Command line patch utility are adpatch which is used for applying patch in application tier, admrgpatch which is used to merge multiple patch into single and can be applied the merged patch alone instead of applying all patches individually.

Admsi.pl at AD_TOP/bin :

Script for generating customized installation instruction for a patch that helps in tracking and performing manual steps during patch.

It can be executed in CLI (Command Line Interface) or GUI (Graphical User Interface).

We must do some check before we apply patch as:

• Check the patch number in table ad_bugs and ad_applied_patches (SQL> select * from AD_BUGS where bug_number=’<patch number>’;)
• Which module it belongs to and what would be the impact.

Step to apply adpatch:

1. Download patch from metalink.
2. Go to patch directory and unzip the patch.
3. Go through readme.txt file and check for any pre-requisite and post requisite and take care accordingly.
4. Shutdown the application services. (keep in mind database and listener should be up and running).
5. Bring the application into maintenance mode using adadmin utility (discussed on later part of this post).
6. Source the environment ( at App_Base/ apps/ apps_st/ appl/ APPS[SID]_[hostname].env).
7. Apply the patch with adpatch
8. Monitor the patch from log file at APPL_TOP/admin/SID/log
9. Once patch is complete, check both adpatch.log and adpatch.lgi
10. Disable the maintenance mode.
11. Start all the application services and check for the patch applied.

How do we apply patch without enabling maintenance mode?

I would say use option=hotpatch and It is used when we apply patch when maintenance mode is disabled.

You can also know more about adpatch modes and options from below link:
http://appsdbaraz.blogspot.com/2013/12/adpatch-modes-and-options.html

Do we need to run autoconfig after patching?

Adpatch takes care of it to update configuration files, if any template files are introduced by the patch.

Do we have to apply patch on all nodes for multimode system?

It depends. In a shared APPL_TOP system, changes made during patching session on one node are immediately available on all nodes.

If APPL_TOP is not shared , then have to apply patch individually to all nodes.

Adpatch.log : main autopatch log file.

Adpatch,lgi : autopatch informational messages.

adworkerXXX.log : for database operations run in parallel and have the worker log details.

Maintenance mode:

Why enable maintenance mode?

Maintenance mode is enabled to ensure optimal performance and reduce downtime during patching session. It shuts down the workflow business event system and set up function security so that oracle applications functions are unavailable to users. This provides a clear separation between normal runtime operation and system downtime for patching.

Enabling Maintenance Mode:

• Set the environment App_Base/ apps/ apps_st/ appl/ APPS[SID]_[hostname].env.
• Run the ad administration utility by typing adadmin on unix/linux console.

It will ask a series of questions with application and databse related information along with context file.Then it will show following options:

1. Generate applications file menu.
2. Maintain applications file menu.
3. Compile/Reload Applications Database Entities Menu.
4. Maintain Applications Database Entities Menu.
5. Change Maintenance Mode.
6. Exit ad Administration.

Select option 5. Now it will show the below option:

1. Enable Maintenance mode.
2. Disable Maintenance mode.
3. Return to Main Menu.

Select option 1. Then return to console.

When we enable maintenance mode with adadmin utility, it calls setmmd.sql script located at FND_TOP/sql location. We can also enable / disable maintenance mode with this script.

Oracle Apps DBA: Form Modes

Oracle Apps DBA: Form Modes: Form Modes in Oracle application: Socket mode Servlet mode Oracle Applications Release12 by default configures Forms 10g stack in...

Oracle Database Patch

Oracle Database Patches:

Patch is required for any bug fix or some additional features. Databse patches are mainly applied for security fixes, Priority fixes and patchset is used for upgradation like 10.2.0.1 to 10.2.0.4.

Critical Patch Update(CPU): These are the patches for security fixes and released each quarter.

Patch Set Update(PSU): These are cumulative patches that include both security fixes and priority fixes. PSU are the minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).

Once PSU is applied, PSU has to be applied each quarter till we upgrade our database to new base version.

How do we apply Oracle database patches?

Patches are downloaded from metalink (My Oracle Support) and it has readme.txt file with the patching instructions and we can follow as suggested by oracle with read me file.

Basic steps to follow for applying database patch:

• Check the patch you are going to apply with opatch lsinventry.
• Download the patch from metalink.
• Unzip the patch and read the instructions from read me file.
• Shutdown database and listner.
• Go to patch directory : Opatch apply
• Start listener and database.
• Run sql script catcpu.sql (from $ORACLE_HOME/rdbms/admin)
• Verify the status of patch applied 
• Run SQL script utlrp.sql (compile invalid objects if any generated)

Log location of Opatch:

$ORACLE_HOME/cfgtoollogs/opatch

Significance of utlrp.sql and catcpu.sql:

Utlrp.sql :- location of utlrp.sql is $ORACLE_HOME/rdbms/admin. This script is used to compile the invalid objects.

Catcpuu.sql :- catcpu.sql calls a script called catbundle.sql, which is located at $ORACLE_HOME/rdbms/admin.

This script will load sql file in database by looking into bundledata_cpu.xml for file information for a particular patch. It will be creating a dynamic apply sql file and run it.

Application R12 Cloning Issues

R12 Cloning Issues - Very common post Clone Issues:

As I have already discussed about database and application cloning in my previous post and also you can look into below link for cloning:

http://appsdbaraz.blogspot.in/2013/12/cloning-databse-and-application.html

Now I would like to discuss about some common post clone issues of application

     1)      Getting no manager and Standard manager not up:

     ·         First check custom.env at appl_top and if not present create custome.env

     ·         source env. Again

     ·         restart all  the services of application

     ·         test the issue by checking the status of concurrent manager

      2)       Oracle APPS R12 Post Cloning issue - Form not launching :

Introduction:

After the successful cloning of R12.1.1, when opening the form, it was not launching.

After troubleshooting with the following steps, form started working fine.

1- Stopped the APPS Tier services.

2- rename the directory "tldcache" under following directories.

a- $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/tldcache

b- $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/tldcache

c- $ORA_CONFIG_HOME/10.1.3/j2ee/forms/tldcache

3- create the emplty directory with the name "tldcache" under the above directories.

4- restart the APPS Tier services.

5- test the issue with starting some forms.

     3)       log/out files are not getting generated:

For fixing this issue, need to do the following steps:

     ·         stop all concurrent services

     ·         login with functional administrator responsibility.

     ·         Navigate to profile/system

     ·         search for %RRA Enabled%

     ·         set the value to “yes” and save it

     ·         start the concurrent services

     ·         test the issue