Thursday 25 February 2016

Downtime Mode feature in ADOP Patching

Downtime mode is introduced in E-Business Suite Release 12.2, to minimize the duration of 12.2 upgrade process.This feature is available from AD.Delta.5 Release Update Pack.

Before applying patch in downtime mode, all Application services must be shutdown.Downtime mode does not use online patching cycle phases,hence it applied patches quickly compared to online patching. However, EBS application will be down for longer time in downtime mode.Make sure that no adop cycle is in progress while applying patch in downtime mode, Otherwise it will give error.

To apply patch in downtime mode. Follow the below steps

1. Shut down Application Services

2. Apply patch using the below command

 $ adop phase=apply patches=<patch_number> apply_mode=downtime

3. Startup Application Services

Following Scenarios require to apply patch in downtime mode in R12.2

1. All Patching that is part of 12.2 upgrade process. Once the upgrade is complete and users are online, all subsequent patching on a production system should use online mode, unless stated in patch ReadMe.

2. Single-node development or test environments, where production support and high availability are not required.

Limitations of downtime mode

1. We cannot test the patch application prior to cutover as in online patching cycle.
2. We cannot rollback patch applied using downtime mode.
3. Downtime mode is not applicable for Multi-node environments

Tuesday 23 February 2016

How to Unlock Objects Statistics?

While running Concurrent Program, Gather Schema Statitics the request failed with error
20005: object statistics are locked (stattype = ALL)***


start of log messages from FND_FILE



In GATHER_SCHEMA_STATS , schema_name= APPS percent= 10 degree = 2 

internal_flag= NOBACKUP



20005: object statistics are locked (stattype = ALL)***



object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



object statistics are locked (stattype = ALL)***



object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



object statistics are locked (stattype = ALL)***



20005: object statistics are locked (stattype = ALL)***



End of log messages from FND_FILE




This error comes when analyzing tables in oracle. Basic issue in this error is  when you
import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:

select table_name, stattype_locked 
from dba_tab_statistics 
where owner = ‘APPS’ and stattype_locked is not null;

Then how to unlock them, following query will help us to sort this issue:

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name 
||”’);’ from dba_tab_statistics where owner = ‘MBS’ and stattype_locked is not null;

or you can do that for every single table:

exec DBMS_STATS.UNLOCK_TABLE_STATS(‘owner’,'table name’);

Sunday 21 February 2016

What Happens during adop prepare Phase?

The online patching cycle in R12.2  which uses ADOP  utility applies patch in 5 Phases
The phases are



1)Prepares the Filesystem

During Prepare phase,RUN and PATCH filesystems are synchronized.By Default, Synchronization is done in incremental mode.
Full Synchronization is also available, if needed.

SQL Tuning Advisor (10g and 11g)

SQL Tuning Advisor is used for tuning SQL statements.
This is run to implement a ADDM performance finding.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.

To run the SQL Tuning Advisor do the following:

1. On the Home Page, under Related Links, click Advisor Central, then click SQL Tuning Advisor. The SQL Tuning Advisor Links page appears.
2. The advisor can be run on one of the following sources:
Top SQL—These consist of recently active top SQL statements from the cursor cache (Spot SQL) or historical top SQL from the AWR (Period SQL).
SQL Tuning Sets—These consist of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from any SQL workload.
3. For example, you can select Top SQL. The Top SQL page appears. This page has two tabs, Spot SQL and Period SQL. Spot lists recent top SQL from the cursor cache, while Period SQL lists historical top SQL captured in the AWR. You must select an interval to analyze by dragging the shaded box over the period. You then select one or more SQL statements to analyze during the selected period.
4. Click Run SQL Tuning Advisor. The SQL Tuning Options page appears showing the SQL statements in the interval. Give your task a name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the task. Click OK.
5. Navigate back to the Advisor Central page. The status of Advisor Tasks is listed under this heading in the results section. You must wait until your task status is COMPLETED. You can check the status by clicking your browser's Refresh button. Then, select your task and click View Result.
6. The SQL Tuning Result page appears. To view recommendations, select the SQL statement and click View Recommendations. The recommendation can include one or more of the following:
Create an index to offer alternate, faster access paths to the query optimizer.
Accept SQL profile, which contains additional SQL statistics specific to the statement that enables the query optimizer to generate a significantly better execution plan.
Gather optimizer statistics on objects with stale or no statistics.
Advice on how to rewrite a query for better performance.

SQL Advisor in Oracle 10g

Another great feature of Oracle 10G that allow you to tune SQL. Now you don't need to tune SQL statement manually. This new feature
does it for you.

SQL Tuning Advisor using DBMS_SQLTUNE package and very simple to use.

The example below shows how to use SQL advisor.

1. Grant following access to the user that is going to run this new tool. In the example below SCOTT is the owner of the schema.

2. Create the tuning task

task_name_var VARCHAR2(30);
sqltext_var CLOB;
sqltext_var := 'SELECT * from TAB1 where empno = 1200';
sql_text => sqltext_var,
user_name => 'USER1',
time_limit => 60,
task_name => 'sql_tuning_task_test1',
description => 'This is a  tuning task on TAB1 table');
Some time you may have queries that might take longer than the time that you have specified in the "time_limit" parameter. If this is the case then remove this parameter.

NOTE: You can not create more than one task with the same name. If this is the case then drop the existing task or use a different name.

2.1 To view the existing task for the user run the following statement.

select task_name from dba_advisor_log where owner = 'SCOTT';
3. Execute the tuning task

Execute dbms_sqltune.Execute_tuning_task (task_name => 'sql_tuning_task_test1');
3.1 You can check the status of the task using following query.

select status from dba_advisor_log where task_name='sql_tuning_task_test1';

4. Now view the Recommendation

set linesize 100
set long 1000
set longchunksize 1000

SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1') from dual;

Tuning Task Name : sql_tuning_task_test1
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/22/2006 15:33:13
Completed at : 06/22/2006 15:33:14

SQL ID : ad1489724nqpn
SQL Text: SELECT * from TAB1 where VALUE=18902;


1- Statistics Finding
Table "USER1"."TAB1" was not analyzed.

Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'USER1', tabname =>'TAB1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Based on this information, you can decide what actions are necessary to tune the SQL.


As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.
-- Enable
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);

-- Disable
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.

The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
ACCEPT_SQL_PROFILES - Automatically accept SQL profiles (default FALSE).
MAX_SQL_PROFILES_PER_EXEC - The maximum number of SQL profiles automatically implemented per run (default 20).
MAX_AUTO_SQL_PROFILES - The maximum number of automatic SQL profiles allowed on the system (default 10000).
The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
AND    parameter_name IN ('ACCEPT_SQL_PROFILES',

------------------------------ ------------------------------
MAX_AUTO_SQL_PROFILES          10000

3 rows selected.

The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES',
    value     => 'TRUE');
The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
  :l_report := DBMS_SQLTUNE.report_auto_tuning_task(
    begin_exec   => NULL,
    end_exec     => NULL,
    type         => DBMS_SQLTUNE.type_text,     -- 'TEXT'
    level        => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
    section      => DBMS_SQLTUNE.section_all,   -- 'ALL'
    object_id    => NULL,
    result_limit => NULL);

SET LONG 1000000
PRINT :l_report

Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 31
Current Execution                       : EXEC_1_25
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200
Completion Status                       : COMPLETED
Started at                              : 01/16/2008 22:00:06
Completed at                            : 01/16/2008 22:00:46
Number of Candidate SQLs                : 0
Cumulative Elapsed Time of SQL (s)      : 0


11g RAC Performance Analysis

Monitoring Performance by Analyzing GCS and GES Statistics

In order to determine the amount of work and cost related to inter-instance messaging and contention, examine block transfer rates, remote requests made by each transaction, the number and time waited for global cache events as described under the following headings:

Analyzing Cache Fusion Impact in Real Application Clusters
Analyzing Performance Using GCS and GES Statistics
Analyzing Cache Fusion Impact in Real Application Clusters
The effect of accessing blocks in the global cache and maintaining coherency is represented by
The Global Cache Service statistics for current and cr blocks, for example, gc current blocks received, gc cr blocks received, and so on)
The Global Cache Service wait events, for gc current block 3-way, gc cr grant 2-way, and so on.
The response time for cache fusion transfers is determined by the messaging and processing times imposed by the physical interconnect components, the IPC protocol and the GCS protocol. It is not affected by disk I/O factors other than occasional log writes. The cache fusion protocol does not require I/O to data files in order to guarantee cache coherency and RAC inherently does not cause any more I/O to disk than a non-clustered instance.

Analyzing Performance Using GCS and GES Statistics

Monitor Global Cache Service performance by identifying data blocks and objects which are frequently used ("hot") by all instances. High concurrency on certain blocks may be identified by Global Cache Service wait events and times.

The following wait events indicate that the access to cached data blocks was held up because they were busy either in the remote or the local cache, respectively:
gc current block busy
gc current block 2-way busy
gc current block 3-way busy
gc cr block 2-way busy
gc cr block 3-way busy
This means that the blocks were pinned or held up by sessions or delayed by a log write on a remote instance (for example, gc current, cr 2-way busy, or cr 3-way busy), or that a session on the same instance is already accessing a block which is in transition between instances and the current session needs to wait behind it (for example, gc current block busy).
The V$SESSION_WAIT view to identify objects and data blocks with contention. The gc wait events contain the file and block number for a block request in p1 and p2, respectively.
An additional segment statistic, gc buffer busy, has been added to quickly determine the "busy" objects without recourse to the query on V$SESSION_WAIT mentioned earlier.
The AWR infrastructure provides a view of active session history which can also be used to trace recent wait events and their arguments. It is therefore useful for hot block analysis.
Most of the reporting facilities used by AWR and Statspack contain the object statistics and cluster wait class category, so that sampling of the views mentioned earlier is largely unnecessary.
It is advisable to run ADDM on the snapshot data collected by the AWR infrastructure to obtain an overall evaluation of the impact of the global cache. The advisory will also identify the busy objects and SQL highest cluster wait time.

Analyzing Cache Fusion Transfer Impact Using GCS Statistics

Monitor Global Cache Service performance by identifying objects read and modified frequently and the service times imposed by the remote access. Waiting for blocks to arrive may constitute a significant portion of the response time, in the same way that reading from disk could increase the block access delays, only that cache fusion transfers in most cases are faster than disk access latencies.
The following wait events indicate that the remotely cached blocks were shipped to the local instance without having been busy, pinned or requiring a log flush:
gc current block 2-way
gc current block 3-way
gc cr block 2-way
gc cr block 3-way
The object statistics for gc current blocks received and gc cr blocks received enable quick identification of the indexes and tables which are shared by the active instances. As mentioned earlier, creating an ADDM analysis will, in most cases, point you to the SQL statements and database objects that could be impacted by inter-instance contention.
You must run Statspack at level 7 to collect statistics related to block contention and segment block waits.
Any increases in the average wait times for the events mentioned earlier could be caused by the following:
High load: CPU shortages, long run queues, scheduling delays
Misconfiguration: using public instead of private interconnect for message and block traffic
If the average wait times are acceptable and no interconnect or load issues can be diagnosed, then the accumulated time waited can usually be attributed to a few SQL statements which need to be tuned to minimize the number of blocks accessed.
The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL statements for global cache events and will identify the SQL which may need to be tuned.

Analyzing Response Times Based on Wait Events

Most global cache wait events that show a high total time as reported in the AWR and Statspack reports or in the dynamic performance views are normal and may present themselves as the top database time consumers without actually indicating a problem. This section describes the most important and frequent wait events that you should be aware of when interpreting performance data.
If user response times increases and a high proportion of time waited is for global cache (gc), then the cause should be determined. Most reports include a breakdown of events sorted by percentage of the total time.
It is useful to start with an ADDM report, which would analyze the routinely collected performance statistics with respect to their impact and point to the objects and SQL contributing most to the time waited, and then move on to the more detailed reports produced by AWR and Statspack.
The most important wait events for RAC include various categories, such as:
gc current block 2-way
gc current block 3-way
gc cr block 2-way
gc cr block 3-way
gc current grant 2-way
gc cr grant 2-way
gc current block busy
gc cr block busy
gc current buffer busy
gc current block congested
gc cr block congested
The block-oriented wait event statistics indicate that a block was received as either the result of a 2-way or a 3-way message, that is, the block was sent from either the resource master requiring 1 message and 1 transfer, or was forwarded to a third node from which it was sent, requiring 2 messages and 1 block transfer.

These events are usually the most frequent in the absence of block contention and the length of the wait is determined by the time it takes on the physical network, the time to process the request in the serving instances and the time it takes for the requesting process to wake up after the block arrives.
The average wait time and the total wait time should be considered when being alerted to performance issues where these particular waits have a high impact. Usually, either interconnect or load issues or SQL execution against a large shared working set can be found to be the root cause.
The message-oriented wait event statistics indicate that no block was received because it was not cached in any instance. Instead a global grant was given, allowing the requesting instance to read the block from disk or modify it.

If the time consumed by these events is high, then it may be assumed that the frequently executed SQL causes a lot of disk I/O (in the event of the cr grant) or that the workload inserts a lot of data and needs to find and format new blocks frequently (in the event of the current grant).
The contention-oriented wait event statistics indicate that a block was received which was pinned by a session on another node, was deferred because a change had not yet been flushed to disk or because of high concurrency, and therefore could not be shipped immediately. A buffer may also be busy locally when a session has already initiated a cache fusion operation and is waiting for its completion when another session on the same node is trying to read or modify the same data. High service times for blocks exchanged in the global cache may exacerbate the contention, which can be caused by frequent concurrent read and write accesses to the same data.
The load-oriented wait events indicate that a delay in processing has occurred in the GCS, which is usually caused by high load, CPU saturation and would have to be solved by additional CPUs, load-balancing, offloading processing to different times or a new cluster node.For the events mentioned, the wait time encompasses the entire round trip from the time a session starts to wait after initiating a block request until the block arrives.

Changing APPS Password in R12.2

APPS Password in R12.2 can be changed either using AFPASSWD or FNDCPASSS utility. AFPASSWD is an enhancement to FNDCPASS.
To change APPS password , follow the below steps

1. Shut down MT Services

2.Use the syntax below to change password.


Sample Output: Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.  Working…
Enter new password for user:
Verify new password for user:
AFPASSWD completed successfully.

Note that, to change APPS password we need to give APPLSYS username in AFPASSWD syntax.
APPLSYS and APPS share the same password. Changing APPLSYS password using AFPASSWD utility changes APPS Password also.

3. After the password is changed, start only adminserver on RUN filesystem  using the script

Update the “apps” password in WLS Datasource as follows:
Log in to WLS Administration Console.
Click Lock & Edit in Change Center.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select the Connection Pool tab.
Enter the new password in the “Password” field.
Enter the new password in the “Confirm Password” field.
Click Save.
Click Activate Changes in Change Center.

4. Start all the application services using

Saturday 20 February 2016

12C New Feature: Invisible Columns

Starting from 12c, Oracle allows columns to be hidden from application.
Columns can be made invisible in the CREATE TABLE statement or  using an ALTER TABLE statement.By default columns are visible. Invisible columns can be made visible again using an ALTER TABLE statement.Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.

General characteristics for Invisible Columns:

 1.  Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.
 2.  The database usually stores columns in the order in which they were listed in the CREATE TABLE statement. If you add a new column to a table, then the new column becomes the last column in the table's column order. When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table.
3.Invisible columns are not seen unless specified explicitly in the SELECT list.
Create Table with INVISIBLE column


Table created.

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------

SQL> select COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN from user_tab_cols where table_name ='TEST1';

--------------- ---------- ---------------
C               2          NO
B                          YES
A               1          NO

 The invisible column wont be displayed in a DESCRIBE statement. The column id for an invisible (or hidden ) column will be NULL.

Changing the column to visible / invisible using the ALTER TABLE statement


Table altered.

While inserting data into table with INVISIBLE Columns, proper care need to be taken.
Otherwise,data will be inserted to unexpected columns.

Troubleshooting "Nodemanager Associated with the machine is not reachable"

While trying to start Managed servers from the Administration Console, you may receive the below error

For server soa_server1, the Node Manager associated with machine Machine1 is not reachable. 
All of the servers selected are currently in a state which is incompatible with this operation or are not associated with a running Node Manager or you are not authorized to perform the action requested. No action will be performed.

To fix this issue.

1) Check is the nodemanager is running at OS level using command

$ps -ef|grep -i nodemanager 

2) Verify the nodemanager logfile has no errors

3)Check if the managed server is properly associated with the machine

4) Check the nodemanager configuration(credentials/ Hostname/Port number) details are correctly updated in Console

In the Change Center of the Administration Console, click Lock & Edit (see Use the Change Center).
In the left pane of the Console, expand Environment and select Machines.
Select the machine for which you want to configure Node Manager.
Select Configuration > Node Manager.
In the Type field, select the Node Manager type from the drop-down list.
For more information on Node Manager types, see Node Manager Administrator's Guide.
Note: The values you provide here must correspond to the values you used to configured the Node Manager instance.
In the Listen Address field, enter the DNS name or IP address upon which Node Manager listens.
If you identify the Listen Address by IP address, you must disable Host Name Verification on Administration Servers that will access Node Manager. For more information and instructions, see Using Host Name Verification in Managing WebLogic Security.
Enter a value in the Listen Port field. This is the port where Node Manager listens for incoming requests.
If you have set the Type field to SSH or RSH, you should specify values in the Node Manager Home and Shell Command fields.
For more information on configuring Node Manager using SSH or RSH, see Node Manager Administrator's Guide.
Click Save.
To activate these changes, in the Change Center of the Administration Console, click Activate Changes. 
Not all changes take effect immediately—some require a restart (see Use the Change Center).

Fusion Middleware FAQ's

1.What is the use of nodemanager in Weblogic?

Nodemanager enables us to startup/shutdown/maintain Managed Servers from console.
It is a Java utility that runs as separate process from WebLogic Server.
It is recommended to enable nodemanager in Clustered domains & High Availability Environments.

• Monitors server availability and can restart failed servers
• Can be used to migrate servers on a failed machine to Can be used to migrate servers on a failed machine to another machine

2.How to Check if nodemanager is running?

$ps -ef|grep -i nodemanager

3.How to Start/Stop Nodemanager?

Use the script to startup Nodemanager located in $WL_HOME\server\bin



$nohup ./

To stop nodemanager process, get the process id using the below command

$ps -ef|grep -i nodemanager

Kill the java process related to nodemanager at OS level

or Simple close the command shell in which nodemanager is started

Wednesday 17 February 2016

Online Patching Steps in R12.2 (ADOP)

Basics of online Patching in R12.2

Online patching uses the latest feature of the Oracle database 11gR2 which is called “Edition Based
Redefinition” and also uses multiple file systems on the application side.
While online Patching with adop is in progress, users can use the application and database on RUN Filesystem.
Patch is applied on the alternate filesystem, which is an exact copy of RUN filesystem,called PATCH filesystem.
Users are switched to PATCH filesystem after the patching is complete.
Application is only offline during the Cutover phase.Downtime is now redefined as Cutover.

ADOP Patching Steps
1. Download the patch and unzip in PATCH_TOP directory.

2. Prepare the system for Patching

Source the RUN environment file
cd /test12/applmgr
. ./EBSapps.env RUN

adop phase=prepare

3. Source the PATCH Environment & apply patches:
cd /test12/applmgr
. ./EBSapps.env PATCH
==> Apply patch 19697098

Patch Location: /test12/applmgr/patches
adop phase=apply patchtop=/test12/applmgr/patches  patches=19697098

4..Run finalize.
adop phase=finalize

5. Cutover Phase

    $ adop phase=cutover
6. Cleanup old editions

    $ adop phase=cleanup
7. Synchronize RUN and PATCH filesystems.Start Fs_clone

adop phase=fs_clone

Friday 12 February 2016

How to Change Oracle Database User Password?

Scenarios which require password Change:

1. Its recommended to change passwords periodically for security concerns.

2. User has forgotten/lost the password and unable to login to the system.

3. User wants to change password for any operational Purpose

Oracle database allows user to change database user's password using the below queries

+Logging in as the user


+Logging in as SYS/SYSTEM User


There can be some cases when the DBA need to change password and revert back to older value after the dba action is completed.
Below test case helps in understanding how to change db user password and restore to older password value in Oracle 10g.

1. Change oracle user password using alter command

 SQL> conn system/manager

SQL>  alter user TESTUSER identified by password1 
 User altered. 

2. Test the password is working

SQL> conn TESTUSER/password1;

3.Retreive the encrypted password for user

SQL> conn system/manager; 

SQL> alter user TESTUSER identified by values '6057000499B128C3'; 
User altered. 

select username, password from dba_users where username = 'TESTUSER'; 
USERNAME                       PASSWORD 
------------------------------ ------------------------------ 
TESTUSER                            DB78866145D4E1C3 

4. Change the value of TESTUSER password to a new value

SQL> conn system/manager
SQL>  alter user TESTUSER identified by password2; 

User altered. 

 5. Verify the new password is working

SQL> conn TESTUSER/password2; 

6. Restore the older password using the encrypted password which we retreived from dba_users;
SQL> conn system/manager@dev; 
SQL> alter user TESTUSER identified by values 'DB78866145D4E1C3'; 

User altered. 

7. Verify that you are able to connect using old password.

SQL> conn TESTUSER/password2@dev;

ORA-1017 invalid username/password ;logon denied

SQL> conn TESTUSER/password1@dev;

Monday 8 February 2016

How to Purge Workflow Notifications from Mailer Queue, so that Email is not sent to Users?

What to do when Workflow Notification  Mailer was not running properly for a certain period of time and accumulated all the emails in Workflow Queue.
We can Purge the workflow Mails which are not required by updating mail_status='SENT' for unwanted notifications in wf_notifications table.

Here are a couple of reasons to Purge Workflow Notifications

a)The end user may not want to receive outdated email.
b)The Workflow Queue has accumulated a lot of emails during the issue period, and processing has become very slow.

Below are the steps to Purge Workflow Notifications which are not required

1.First Take a Backup of wf_notifications table  and stop the Workflow Notification Mailer

2. Verify notifications from WF_NOTIFICATIONS table that has potential for being sent

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     where status in ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     order by notification_id;
3. Narrow down the emails which are not to be sent by the Mailer from a specific date range.

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     Where Status In ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     and begin_date < sysdate-30              -- List only emails older than 30 days ago
     order by notification_id;

4. Set Mail_status='SENT' in WF_NOTIFICATIONS

SQL>update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-30;      


This will update  notifications ,which are older than 30 days and waiting to be sent by the mailer to SENT.
Therefore the notifications will not get emailed when the Mailer is restarted.

5.Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :
$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

Example Syntax:
$ sqlplus apps/***@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps ***** applsys

6.Now start the Workflow Notification Mailer.

Friday 5 February 2016

Resolving ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

I was working on Database upgrade from to on a 2 node RAC instance.
After Installation of software and connecting sqlplus in the new environment, Got the below error when trying to startup the database in upgrade mode

SQL> Startup upgrade
ORACLE instance started.

Total System Global Area 1.3262E+11 bytes
Fixed Size                  2304584 bytes
Variable Size            2.2481E+10 bytes
Database Buffers         1.1006E+11 bytes
Redo Buffers               74080256 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 29206
Session ID: 2002 Serial number: 3

Issue Fix

I observed from the initialization parameter file, the below values are set for cluster parameters

cluster_database         = TRUE
cluster_database_instances= 2

We need to set the value of cluster_database to FALSE using the below command

SQL> alter system set cluster_database=FALSE scope=spfile sid='*' ;

Reboot the Database for the changes to be effective.
Now you will see that value of cluster_database_instances parameter is automatically changed to 1,after setting cluster_database to FALSE.

Now, the database can be started in upgrade mode without any errors.
Revert back the parameter cluster_database to TRUE, after the upgrade is completed.
The following command can be used to set cluster_database to TRUE

SQL> alter system set cluster_database=TRUE scope=spfile sid='*' ;

Restart the database.

Thursday 4 February 2016


While Troubleshooting workflow relates issues in EBS, we may need to clear PROCESS and DISCARD folders under workflow user.
The below commands are useful to move folders, connecting to imap

a)Shutdown Workflow Components
b) login to imap host(e.g as applmgr user

  $ telnet 143
    You will get below messages:


Trying **.**.***.**

Connected to (**.**.***.**).

Escape character is '^]'.

* OK Dovecot ready.

  i login wfmgr *****

  i rename PROCESS process_<sys_date>

 i rename DISCARD discard_<sys_date>

 i create PROCESS

i create DISCARD

i subscribe PROCESS

 i subscribe DISCARD

 i unsubscribe process_<sys_date>

 i unsubscribe discard_<sys_date>


c) Startup Workflow Components