E-Business Suite 12.2

There are lots of updates coming in this release, but from a technology stack perspective, EBS 12.2 will be notable for two things:

1) Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
2) Online Patching support via 11gR2 Edition-Based Redefinition

1. Replacing OC4J with WebLogic Server

EBS 12.2 will use WebLogic Server from Fusion Middleware 11g in place of OC4J 10g as part of the release’s internal technology stack. Other additional new Fusion Middleware 11g components used will include WebLogic JSP and UIX 11g.

2. Online Patching support via 11gR2 Edition-Based Redefinition

EBS 12.2 will use the 11gR2 Database’s Edition-Based Redefinition features to provide support for Online Patching. Edition-Based Redefinition is really exciting new technology. From the 11gR2 Application Developer’s Guide:

“Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

“To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.”

Online Patching means that patches that change EBS database objects can be applied while the E-Business Suite production instance is fully operational. Only a short downtime will be required to complete the patching process. This will make your patching downtimes more predictable.

Read more on Steven Chan Blog

SQL to get details of Concurrent Program (FND_CONC_REQ_SUMMARY_V)

Hi Friends,

I have framed a sql query with help of my technical friends to get below details

1. Request id

2. Concurrent program full name

3. Concurrent program short name

4. Start time, and End time.

5. Runtime

6. Parameters passed to the request

7. Submitted by

8. Status code and Phase code

SELECT request_id,
user_concurrent_program_name Concurrent_Program_Full_Name,
program_short_name Concurrent_Program_Short_Name,
actual_start_date,
actual_completion_date,
FLOOR( ( (actual_completion_date – actual_start_date) * 24 * 60 * 60)
/ 3600)
|| ‘ HOURS ‘
|| FLOOR( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
|| ‘ MINUTES ‘
|| ROUND( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600
– (FLOOR( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date
– actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
* 60)))
|| ‘ SECS ‘
time_difference,
argument_text Parameter,
requestor submited_by,
status_code,
phase_code
FROM FND_CONC_REQ_SUMMARY_V

Concurrent Manager showing status “System Hold, Fix Manager before resetting counters”.

Hi friends,

Recently after EBS upgrade to 12.1.3 CM were not functional and was showing status “System Hold, Fix Manager before resetting counters”.

Issue:
Concurrent Manager showing status “System Hold, Fix Manager before resetting counters”.

Solution:
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Go to cd $FND_TOP/bin
$ adrelink.sh force=y link_debug=y “fnd FNDLIBR”
$ adrelink.sh force=y link_debug=y “fnd FNDFS”
$ adrelink.sh force=y link_debug=y “fnd FNDCRM”
$ adrelink.sh force=y link_debug=y “fnd FNDSM”

3. Run the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).
Note 134007.1 CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables

4. Start the concurrent manager.

5. Retest the issue.

Reference :
SCHEDULE/PRERELEASER MANAGER STATUS : SYSTEM HOLD, FIX MANAGER BEFORE RESETTING [ID 985835.1]

Command to remove ^M character from a file on Unix

perl -p -i -e ‘s/\r//’ *

SQL to get details of Scheduled Concurrent Program

Hi Friends,

I have found a interesting sql query which gives details of scheduled concurrent requests in EBS.

/*SQL 1*/

SELECT r.request_id
, CASE
WHEN pt.user_concurrent_program_name = ‘Report Set’
THEN DECODE(
r.description
, NULL, pt.user_concurrent_program_name
, r.description
|| ‘ (‘
|| pt.user_concurrent_program_name
|| ‘)’
)
ELSE pt.user_concurrent_program_name
END job_name
, u.user_name requestor
, u.description requestor_description
, u.email_address
, frt.responsibility_name requested_by_resp
, r.request_date
, r.requested_start_date
, DECODE(
r.hold_flag
, ‘Y’, ‘Yes’
, ‘N’, ‘No’
) on_hold
, CASE
WHEN r.hold_flag = ‘Y’
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN r.hold_flag = ‘Y’
THEN r.last_update_date
END last_update_date
, r.argument_text PARAMETERS
, NVL2(
r.resubmit_interval
, ‘Periodically’
, NVL2(
r.release_class_id
, ‘On specific days’
, ‘Once’
)
) AS schedule_type
, r.resubmit_interval resubmit_every
, r.resubmit_interval_unit_code resubmit_time_period
, DECODE(
r.resubmit_interval_type_code
, ‘START’, ‘From the start of the prior run’
, ‘END’, ‘From the Completion of the prior run’
) apply_the_update_option
, r.increment_dates
, TO_CHAR((r.requested_start_date), ‘HH24:MI:SS’) start_time
FROM applsys.fnd_concurrent_programs_tl pt
, applsys.fnd_concurrent_programs pb
, applsys.fnd_user u
, applsys.fnd_user u2
, applsys.fnd_printer_styles_tl s
, applsys.fnd_concurrent_requests r
, applsys.fnd_responsibility_tl frt
WHERE pb.application_id = r.program_application_id
AND pb.concurrent_program_id = r.concurrent_program_id
AND pb.application_id = pt.application_id
AND r.responsibility_id = frt.responsibility_id
AND pb.concurrent_program_id = pt.concurrent_program_id
AND u.user_id = r.requested_by
AND u2.user_id = r.last_updated_by
AND s.printer_style_name(+) = r.print_style
AND r.phase_code = ‘P’
AND 1 = 1;

/*SQL 2*/

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,’ (‘||r.description||’)',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
‘P’, ‘Periodic’,
‘S’, ‘On Specific Days’,
‘X’, ‘Advanced’,
c.class_type) schedule_type,
case
when c.class_type = ‘P’ then
‘Repeat every ‘ ||
substr(c.class_info, 1, instr(c.class_info, ‘:’) – 1) ||
decode(substr(c.class_info, instr(c.class_info, ‘:’, 1, 1) + 1, 1),
‘N’, ‘ minutes’,
‘M’, ‘ months’,
‘H’, ‘ hours’,
‘D’, ‘ days’) ||
decode(substr(c.class_info, instr(c.class_info, ‘:’, 1, 2) + 1, 1),
‘S’, ‘ from the start of the prior run’,
‘C’, ‘ from the completion of the prior run’)
when c.class_type = ‘S’ then
nvl2(dates.dates, ‘Dates: ‘ || dates.dates || ‘. ‘, null) ||
decode(substr(c.class_info, 32, 1), ’1′, ‘Last day of month ‘) ||
decode(sign(to_number(substr(c.class_info, 33))),
’1′, ‘Days of week: ‘ ||
decode(substr(c.class_info, 33, 1), ’1′, ‘Su ‘) ||
decode(substr(c.class_info, 34, 1), ’1′, ‘Mo ‘) ||
decode(substr(c.class_info, 35, 1), ’1′, ‘Tu ‘) ||
decode(substr(c.class_info, 36, 1), ’1′, ‘We ‘) ||
decode(substr(c.class_info, 37, 1), ’1′, ‘Th ‘) ||
decode(substr(c.class_info, 38, 1), ’1′, ‘Fr ‘) ||
decode(substr(c.class_info, 39, 1), ’1′, ‘Sa ‘))
end as schedule,
c.date1 start_date,
c.date2 end_date
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), ’1′, to_char(l)) s
from (select level l from dual connect by level 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ‘ ‘)), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a – 1
group by release_class_id) dates
where r.phase_code = ‘P’
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = ‘US’
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ‘ (‘ || fcr.description || ‘)’, NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text “PARAMETERS”
, ‘——>’ dates
, TO_CHAR(fcr.request_date, ‘DD-MON-YYYY HH24:MI:SS’) requested
, TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’) requested_start
, TO_CHAR((fcr.requested_start_date), ‘HH24:MI:SS’) start_time
, ‘——>’ holds
, DECODE(fcr.hold_flag, ‘Y’, ‘Yes’, ‘N’, ‘No’) on_hold
, CASE
WHEN fcr.hold_flag = ‘Y’
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = ‘Y’
THEN fcr.last_update_date
END last_update_date
, ‘——>’ prints
, fcr.number_of_copies print_count
, fcr.printer
, fcr.print_style
, ‘——>’ schedule
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
‘Yes: ‘ || TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’)
ELSE
‘n/a’
END run_once
, CASE WHEN fcrc.class_type = ‘P’ THEN
‘Repeat every ‘ ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ‘:’) – 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 1) + 1, 1),
‘N’, ‘ minutes’,
‘M’, ‘ months’,
‘H’, ‘ hours’,
‘D’, ‘ days’) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 2) + 1, 1),
‘S’, ‘ from the start of the prior run’,
‘C’, ‘ from the completion of the prior run’)
ELSE
‘n/a’
END set_days_of_week
, CASE WHEN fcrc.class_type = ‘S’ AND instr(substr(fcrc.class_info, 33),’1′,1) > 0 THEN
‘Days of week: ‘ ||
decode(substr(fcrc.class_info, 33, 1), ’1′, ‘Sun, ‘) ||
decode(substr(fcrc.class_info, 34, 1), ’1′, ‘Mon, ‘) ||
decode(substr(fcrc.class_info, 35, 1), ’1′, ‘Tue, ‘) ||
decode(substr(fcrc.class_info, 36, 1), ’1′, ‘Wed, ‘) ||
decode(substr(fcrc.class_info, 37, 1), ’1′, ‘Thu, ‘) ||
decode(substr(fcrc.class_info, 38, 1), ’1′, ‘Fri, ‘) ||
decode(substr(fcrc.class_info, 39, 1), ’1′, ‘Sat ‘)
ELSE
‘n/a’
end days_of_week
, CASE WHEN fcrc.class_type = ‘S’ AND instr(substr(fcrc.class_info, 1, 31),’1′,1) > 0 THEN
‘Set Days of Month: ‘ ||
decode(substr(fcrc.class_info, 1, 1), ’1′, ’1st, ‘) ||
decode(substr(fcrc.class_info, 2, 1), ’1′, ’2nd, ‘) ||
decode(substr(fcrc.class_info, 3, 1), ’1′, ’3rd, ‘) ||
decode(substr(fcrc.class_info, 4, 1), ’1′, ’4th, ‘) ||
decode(substr(fcrc.class_info, 5, 1), ’1′, ’5th, ‘) ||
decode(substr(fcrc.class_info, 6, 1), ’1′, ’6th, ‘) ||
decode(substr(fcrc.class_info, 7, 1), ’1′, ’7th, ‘) ||
decode(substr(fcrc.class_info, 8, 1), ’1′, ’8th, ‘) ||
decode(substr(fcrc.class_info, 9, 1), ’1′, ’9th, ‘) ||
decode(substr(fcrc.class_info, 10, 1), ’1′, ’10th, ‘) ||
decode(substr(fcrc.class_info, 11, 1), ’1′, ’11th, ‘) ||
decode(substr(fcrc.class_info, 12, 1), ’1′, ’12th, ‘) ||
decode(substr(fcrc.class_info, 13, 1), ’1′, ’13th, ‘) ||
decode(substr(fcrc.class_info, 14, 1), ’1′, ’14th, ‘) ||
decode(substr(fcrc.class_info, 15, 1), ’1′, ’15th, ‘) ||
decode(substr(fcrc.class_info, 16, 1), ’1′, ’16th, ‘) ||
decode(substr(fcrc.class_info, 17, 1), ’1′, ’17th, ‘) ||
decode(substr(fcrc.class_info, 18, 1), ’1′, ’18th, ‘) ||
decode(substr(fcrc.class_info, 19, 1), ’1′, ’19th, ‘) ||
decode(substr(fcrc.class_info, 20, 1), ’1′, ’20th, ‘) ||
decode(substr(fcrc.class_info, 21, 1), ’1′, ’21st, ‘) ||
decode(substr(fcrc.class_info, 22, 1), ’1′, ’22nd, ‘) ||
decode(substr(fcrc.class_info, 23, 1), ’1′, ’23rd,’ ) ||
decode(substr(fcrc.class_info, 24, 1), ’1′, ’24th, ‘) ||
decode(substr(fcrc.class_info, 25, 1), ’1′, ’25th, ‘) ||
decode(substr(fcrc.class_info, 26, 1), ’1′, ’26th, ‘) ||
decode(substr(fcrc.class_info, 27, 1), ’1′, ’27th, ‘) ||
decode(substr(fcrc.class_info, 28, 1), ’1′, ’28th, ‘) ||
decode(substr(fcrc.class_info, 29, 1), ’1′, ’29th, ‘) ||
decode(substr(fcrc.class_info, 30, 1), ’1′, ’30th, ‘) ||
decode(substr(fcrc.class_info, 31, 1), ’1′, ’31st. ‘)
ELSE
‘n/a’
END days_of_month
, CASE WHEN fcrc.class_type = ‘S’ AND substr(fcrc.class_info, 32, 1) = ’1′ THEN
‘Yes’
ELSE
‘n/a’
END last_day_of_month_ticked
, fcrc.CLASS_INFO
FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_user fu
, applsys.fnd_user u2
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
, applsys.fnd_printer_styles_tl fpst
, applsys.fnd_conc_release_classes fcrc
, applsys.fnd_responsibility_tl frt
, apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = ‘CP_STATUS_CODE’
AND fcr.phase_code = ‘P’
AND 1=1
ORDER BY fu.description, fcr.requested_start_date asc;

How To Enable/Disable “Submit Another Request ?”

Profile Option “Concurrent: Show Requests Summary After Each Request” controls the behaviour of the application after request submission:

Sysdamin: Profile> System > Query for “Concurrent: Show Requests Summary After Each Request”

If set to Yes, the View Requests form displayed.

If set to No, the Decision Window displayed.

Enjoy Reading ….

Important Views when using Oracle RMAN

RC_ARCHIVED_LOG information about all archivelogs.

RC_BACKUP_CONTROLFILE backup control files in backup sets.

RC_BACKUP_CORRUPTION corrupt blocks in datafile backups and copies.

RC_BACKUP_DATAFILE datafile backups (in backup sets).

RC_BACKUP_PIECE backup pieces.

RC_BACKUP_REDOLOG redo log backups (in backup sets).

RC_BACKUP_SET backup sets.

RC_CHECKPOINT rc_checkpoint is replaced by rc_resync, but is still used by some tests.

RC_CONTROLFILE_COPY controlfile copies.

RC_COPY_CORRUPTION corrupt block ranges in datafile copies for all database incarnations.

RC_DATABASE information about databases and their current incarnations.

RC_DATABASE_INCARNATION information about all incarnations registered in recovery catalog.

RC_DATAFILE information about all datafiles registered in recovery catalog.

RC_DATAFILE_COPY datafile copies (on disk).

RC_LOG_HISTORY information about redo log history.

RC_OFFLINE_RANGE offline ranges for datafiles.

RC_REDO_LOG information about online redo logs.

RC_REDO_THREAD information about redo threads.

RC_RESYNC information about recovery catalog resyncs (checkpoints).

RC_STORED_SCRIPT stored scripts.

RC_STORED_SCRIPT_LINE each line of each stored script.

RC_TABLESPACE information about all tablespaces registered in recovery catalog.

How to Turn Archiving ON and OFF

Turning Archiving On and Off
You set a database’s initial archiving mode as part of database creation. Usually, you can use the default of NOARCHIVELOG mode at database creation
because there is no need to archive the redo information generated at that time. After creating the database, decide whether to change from the initial archiving mode.

After a database has been created, you can switch the database’s archiving mode on demand. However, you should generally not switch the database between archiving modes.

NOTE: If a database is automatically created during Oracle installation, the initial archiving mode of the database is operating system specific. See your operating system-specific Oracle documentation.

ARCHIVELOG mode is necessary for creating on-line backups and for certain types of database recovery. Configuring the database to operate in
ARCHIVELOG mode allows the user to perform complete and point-in-time recovery from media (disk) failures using off-line or on-line backups. If
ARCHIVELOG mode is disabled, the database can be restored from a backup in case of failure, but it cannot be rolled forward from that to a point when failure occurred.

Oracle recommends ARCHIVELOG mode for all production databases

Setting the Initial Database Archiving Mode
When you create the database, you set the initial archiving mode of the redo log in the CREATE DATABASE statement. If you do not specify either ARCHIVELOG or NOARCHIVELOG, NOARCHIVELOG is the default. To verify database mode, execute following statement:

SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASENAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#——— ——— ———— —————— —————O112 19-MAR-09 NOARCHIVELOG 1438426 135961

Changing the Database Archiving Mode
There are “init.ora” parameters you need to modify in order to properly handle your database being in archive log mode. They are:

LOG_ARCHIVE_DEST LOG_ARCHIVE_FORMAT LOG_ARCHIVE_DEST:
This parameter specifies the directory where your archive logs will be placed.

LOG_ARCHIVE_FORMAT:
This parameter names the archive logs in this format. For example, if your format is: arch%s.arc

Your log files will be called: arch1.arc, arch2.arc, arch3.arc where the ’1′, ’2′, ’3′, etc is the sequence number.

To Prepare to Switch Database Archiving Mode
1. Shut down the database instance.

SQL> shutdown immediateAn open database must be closed and dismounted and any associated instances shut down before the database’s archiving mode can be switched. Archiving cannot be disabled if any datafiles need media recovery.

2. Backup the database.

This backup can be used with the archive logs that you will generate.

3. Perform any operating system specific steps (optional).

4. Start up a new instance and mount, but do not open the database.

SQL> startup mountNOTE: If you are using the Oracle Parallel Server, you must mount the database exclusively using one instance to switch the database’s archiving mode.

5. Put the database into archivelog mode

SQL> alter database archivelog; NOTE: You can also use

alter database noarchivelog to take the database out of archivelog mode

6. Open the database.

SQL> alter database open; 7. Verify your database is now in archivelog mode.

SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 22Next log sequence to archive 24Current log sequence 248. Archive all your redo logs at this point.

SQL> archive log all; 9. Ensure these newly created Archive log files are added to the backup process.

Ref: Metalink ID 69739.1

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode

To perform online/hot backup we have to put the tablespace in begin backup mode followed by copying the datafiles and then putting the tablespace to end backup.

In 8i, 9i we have to put each tablespace individually in begin/end backup mode to perform the online backup. From 10g onwards the entire database can be put in begin/end backup mode.

Make sure that the database is in archivelog mode

Example :

Performing a single tablespace backup

+ sql>alter tablespace system begin backup;
+ Copy the corresponding datafiles using appropriate O/S commands.
+ sql>alter tablespace system end backup;

Performing a full database backup (starting from 10g)

+ sql> alter database begin backup;
+ Copy all the datafiles using appropriate O/S commands.
+ sql> alter database end backup;

One danger in making online backups is the possibility of inconsistent data within a block. For example, assume that you are backing up block 100 in datafile users.dbf. Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block. In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block. The result is called a fractured block, meaning that the data contained in this block is not consistent. at a given SCN.

Therefore oracle internally manages the consistency as below :

1. The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.

Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile — DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block — the head and tail are from two points in time.

We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.

2. The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Ref : Metalink ID 469950.1

Query to find number of archive logs getting generated every day

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,’YYYY-MM-DD’) DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,’YYYY-MM-DD’)
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;

What Tables Are Involved In Using The System Profile ‘Sign-On:Audit Level’

Applies to:

Oracle Application Object Library – Version: 11.5.0 to 11.5.10.2 – Release: 11.5 to 11.5
Information in this document applies to any platform.
Checked for relevance on 29-MAY-2010

Goal

What tables are involved in using the system profile ‘Sign-On:Audit Level’?

Solution

There are three primary tables:

FND_LOGINS
FND_LOGIN_RESPONSIBILITIES
FND_LOGIN_RESP_FORMS

1. Least detailed level: User.
When the profile is set to User, the only table that gets updated is the table FND_LOGINS and only one record per user session.

2. Next level: Responsibility.
When the profile is set to Responsbility both FND_LOGINS and FND_LOGIN_RESPONSIBILITIES will be updated.
FND_LOGINS gets only one record per user session.
FND_LOGIN_RESPONSIBILITIES will be updated with one record for each
responsibility selected during the session.

3. Most detailed level: Form.
When the profile is set to Form all three tables are involved.
FND_LOGINS gets only one record per user session.
FND_LOGIN_RESPONSIBILITIES will be updated with one record for each
responsibility selected during the session.
FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session.

Reference: Metalink Note 368260.1

Helpful SQL for Users Monitoring

– Current Forms Based Users in ERP

SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v ORDER BY 1;

– Users Count Who Logged in to ERP

select count(distinct d.user_name)
from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid and a.spid = b.process
and d.user_id = a.user_id and (d.user_name = ‘USER_NAME’ OR 1=1);

– Users Who Logged in to ERP

select distinct d.user_name
from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = ‘USER_NAME’ OR 1=1);

Query to list of Responsibilities attached to a particular Oracle Application User

Hi Friends,

Kindly run below sql query as apps user for listing all the responsibilities attached to a particular user (prompted by query).

set pages 300 lines 300
col USER_NAME for a40
col RESPONSIBILITY_NAME for a50

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = ‘&user_name’
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’);

Happy Reading !!

Farhan Shaikh

Trace a Concurrent Request And Generate TKPROF File

Enable Tracing For The Concurrent Manager Program

Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace

Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id

SELECT ’Request id: ’||request_id , ‘Trace id: ’||oracle_Process_id, ‘Trace Flag: ’||req.enable_trace, ‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ’||prog.user_concurrent_program_name, ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name , ‘Status : ’||decode(phase_code,’R’,’Running’) ||’-’||decode(status_code,’R’,’Normal’), ‘SID Serial: ’||ses.sid||’,’|| ses.serial#, ‘Module : ’||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’ and dbnm.name=’db_name’ and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;

TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/ sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his provides the sort criteria in which all sql statements will be sorted. This will bring the bad sql at the top of the outputfile.
sys=no:Disables sql statements issued by user SYS

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries

$ tkprof raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10

Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

Enjoy Reading

Farhan Shaikh

Cloning Multi Node to Single Node

Description :
With Oracle Applications 11.5.10 the ability to clone from a multi node to a single node system using rapid clone has now been certified.
This is accomplished by the Shared APPL_TOP and Merging APPL_TOP features.
The information mentioned here is the steps which i have used and found them to be successful for a more elaborate explanation you might want to refer to the Metalink notes mentioned at the end of this document.

Source System here refers to the multi node Production system to be cloned Target System refers to the newly to be created T&D single node system
Applications Version 11.5.10.2
Database Version 10.2.0.4.0
Operating System RedHat Enterprise Linux Advance Server 5 update 4

Source Setup Information:

192.168.200.2 Database
192.168.200.2 Concurrent Manager & Report (Admin Node)
192.168.200.3 Forms & Apache (Web Node)

Step 1: Check OS configuration and utilities
Red Hat Enterprise Linux 5* (base and Advanced Platform) Update 1 (RHEL 5.1) or higher is
required.
The following packages are not part of the OEL 5 or RHEL 5 distribution media and must be installed
manually:
• compat-libstdc++-egcs-1.1.2-11
• compat-libcwait-2.1-11
• compat-oracle-el5-1.0-51
• openmotif21-2.1.30-11.EL51
• binutils-2.152
The following packages must be installed from the OEL 5 or RHEL 5 distribution media:
• libXp-1.0.0-8.1.el5
• compat-libgcc-296-2.96-138
• compat-libstdc++-33-3.2.3-61
• compat-db-4.2.52-5.1
Note :
1: Download from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/ (for both OEL 5 and RHEL 5)
2: GNU linker (ld) version 2.15 is required for relinking the modules in Advanced Planning &
Scheduling (MSC, MSO, MSR) – download binutils-2.15 from http://oss.oracle.com/projects/compat oracle/files/Enterprise_Linux/ (for both OEL 5 and RHEL 5)
glibc Requirements
Oracle Enterprise Linux 5 2.3.4-2.26

Domain Name System (DNS) Resolver Parameters
Two Domain Name System (DNS) resolver parameters (timeout and attempts) are set by default to low values when the operating system is installed. These low values may cause attempted network connections to an Oracle database to fail. If this happens, add or update the following entries to these
minimum settings in the /etc/resolv.conf file on each server node:
options attempts:5
options timeout:15

Net Service Listeners in Multi-user Installations
Give all users in a multi-user installation write privileges to the .oracle directory:
$ chmod 777 /var/tmp/.oracle

Verifying Host Names
Use the following to verify host name settings:
For Oracle Enterprise Linux 4 and 5, Red Hat Enterprise Linux 3, 4 and 5:
1. Verify that the /etc/hosts file is formatted as follows:
127.0.0.1 localhost.localdomain localhost
.
2. Verify that the /etc/sysconfig/network file is formatted as follows:
HOSTNAME=.
3. If the /etc/sysconfig/networking/profiles/default/network file exists, remove it.
4. If you changed any files in the previous steps, restart the system.

Modifying the Number of Open File Descriptors
Open the /etc/security/limits.conf file and change the existing values for “hard” and “soft” parameters
as follows. Restart the system after making changes.
For Oracle Enterprise Linux 4 and 5, Red Hat Enterprise Linux 3, 4 and 5 and SUSE Linux Enterprise
Server 9 and 10:
* hard nofile 65535
* soft nofile 4096
Modifying the Port Range Values
Open the /etc/sysctl.conf file and change the value of net.ipv4.ip_local_port_range as follows. Restart the system after making changes.
net.ipv4.ip_local_port_range = 10000 65000
Please note that this range is a recommended range, and may need to be adjusted according to the specific needs of the user’s environment in order to avoid port conflicts.

Setting the Environment
Perform the following tasks to set the environment before you run Rapid Install.
1. Create symbolic links For Oracle Enterprise Linux 5 and Red Hat Enterprise Linux 5 only :
# ln -s /usr/bin/ld215 /usr/bin/ld
Download and apply the OS library patch 6078836 from Oracle MetaLink and create the following symbolic link:
# ln -s /usr/lib/libdb.so.2 /usr/lib/libdb.so.3
2. Verify that the host name setting is correct. The command should return a fully qualified hostname.
For example: ..
# hostname -f

For Oracle Enterprise Linux 5, Red Hat Enterprise Linux 5 and SUSE Linux Enterprise Server 10 customers, the LD_ASSUME_KERNEL environment variable should be unset before starting the installation. The installation fails when the LD_ASSUME_KERNEL variable is set by the adgetlnxver.sh file during the course of the installation. Patch 6365595 contains the fix for adgetlnxver.sh file. The procedure below is a guideline for replacing the adgetlnxver.sh file in the
Oracle Applications 11i ship home.

Merging existing APPL_TOPs into a single APPL_TOP
In preparation for sharing a file system in an existing system with multiple APPL_TOPs, you can use the Rapid Clone utility to merge the multiple APPL_TOP (and COMMON_TOP) file systems into a single APPL_TOP file system. See Oracle MetaLink Doc ID: 230672.1 for more information. Oracle
recommends that the different APPL_TOPs should be merged into a completely new APPL_TOP to facilitate rollback incase of merge failure.
Note : We have considered Web Node ie 192.168.200.3 as our primary node (Node A mentioned in below steps)

Step 2: Maintain snapshot information
Log in to each application tier node as the APPLXXX user and run the maintain snapshot task in AD Administration.

Step 3: Merge existing APPL_TOPs
I) Prepare the source system application tier for merging
Choose one of the source system nodes to be the primary node. This document refers to it as “Node A”.
o Log in to Node A as the APPLXXX (applprod in our case) user and run:
$ cd /admin/scripts/
$ perl adpreclone.pl appsTier merge
This will create clone directory under $COMMON_TOP on 192.168.200.3

o Log in as the APPLMGR user to each of the secondary nodes being merged and run:
$ cd /admin/scripts/
$ perl adpreclone.pl appltop merge

This will create clone directory under $COMMON_TOP on 192.168.200.2

Step 4: Perform Backup of Source System
II) Copy the Source System to the Target System
Copy the application tier file system from the source Applications system to the target node by executing the following steps in the order listed. Ensure the application tier files copied to the target system are owned by the target APPLXXX user, and that the database tier files are owned by the ORAXXX user.

III) Create a copy of Node A (preferred)
If you want to place the merged APPL_TOPs in a new location instead of using an existing APPL_TOP location, create a copy of Node A (the other nodes in the system do not need to be copied). Include the following directories from Node A:

Step 5: Restore the backup on Target Node

IV) Copy the database tier file system
Log on to the source system database node as the ORACLE user.
• Perform a normal shutdown of the source system database
• Copy the database (DBF) files from the source to the target system
• Copy the source database ORACLE_HOME to the target system
• Start up the source Applications system database and application tier processes

V) Copy the required files for merging
Log in as the APPLMGR user to each source node and recursively copy:
directory /clone/appl
- to –
directory /clone/appl on Node A (or the copy of Node A).

VI) Configure target Database and Application
f. Configure the Target System
Execute the following commands to configure the target system.
You will be prompted for the target system specific values (SID, Paths, Ports, etc)
o Configure the target system database server
Log on to the target system as the ORACLE user and type the following commands
to configure and start the database:
cd /appsutil/clone/bin
perl adcfgclone.pl dbTier
o Configure the application tier server nodes
Log in to the merged APPL_TOP node as the APPLMGR user and execute the
following commands:
$ cd /clone/bin
$ perl adcfgclone.pl appsTier

Step 6: Finishing tasks
• Log in to the target system application tier node as the APPLMGR user.
• Run the following tasks in adadmin for all products:
o generate JAR files
o generate message files
o relink executables
o copy files to destination
• Depending on which tier you chose as the primary node, certain files may be missing. Run
adadmin to verify files required at runtime. If any files are listed as missing files, you must
manually copy them to the merged APPL_TOP.
• Remove the temporary directory /clone/appl to reduce disk space usage.
• Update Profile options
• Update Printer Settings
• Update the workflow configuration settings
• Verify the APPLCSF variable setting
• Update the session_cookie_domain value in icx_parameters

Reference Metalink Note :
316806.1 – Oracle Applications Installation Update Notes, Release 11i (11.5.10.2)
233428.1 – Sharing the Application Tier File System in Oracle Applications 11i
230672.1 – Cloning Oracle Applications Release 11i with Rapid Clone

How to reset or Unlock the orcladmin password

Recently while upgrading Discoverer to 10.1.2.3 CP4 , we got stuck at one of the step.
This step asks for orcladmin password which was not known to us.

So , we need to reset that password.Here are the steps which we followed ;

Error:
orcladmin password lost or account locked

Cause:
orcladmin password not known to us.

Solution :
1. Login as OAS user [ here testbi ] and
sqlplus “/as sysdba”
SQL>> alter user ODS identified by [new password]

2. Execute $ORACLE_HOME/bin/oidpasswd create_wallet=true

3. oidpasswd connect=[connect string] change_oiddb_pwd=true
This will prompt us for old password and new password.
old password you can give the same as ODS user and unique new password.

4. To unlock the orcladmin account :
oidpasswd connect=[connect string] unlock_su_acct=trueThis will prompt for password ; enter the ODS password.Which will inturn unlock your orcladmin account.

5. To reset the password :
oidpasswd connect=[connect string] reset_su_password=true
This will prompt for new password. [keep the password same as ODS user.] and your are done.

You have successfully changed the orcladmin password.
Now, to test the password :
cd $ORACLE_HOME/bin/oidadmin
The window will prompt for orcladmin password.

Refer : http://eldapo.blogspot.com/2007/08/forcing-orcladmin-password.html

How To Reset the oc4jadmin password in R12

While upgrading our Tech Stack to 10.1.3.4 on RHEL 5 for EBS 12.0.4 the oui prompts for oc4jadmin’s password, if you don’t know the password below steps can be followed to reset OC4JADMIN password

Steps:

1 : Login as Application User (applxxx)
2 : cd $IAS_ORACLE_HOME/j2ee/home/config
3 : cp -rp system-jazn-data.xml system-jazn-data.xml_orig
4 : vi system-jazn-data.xml
5 : edit the following section ::

>> the credentials part with “!” sign followed with the password

[user]
[name]oc4jadmin[/name]
[display-name]OC4J Administrator[/display-name]
[description]OC4J Administrator[/description]
[credentials]!password123[/credentials]
[/user]

Save this file and restart your OPMN Services “opmnctl startall”

Reference : Metalink Note 576587.1

Turn off Archive Logs in 10g RAC Database

In the RAC environment, all instances have to be in shutdown, before “alter database noarchivelog;” can be executed successfully in the mounted instance. Otherwise, you will get ORA-1126 error “database must be mounted exclusive and not open for this operation”.

Steps:

1. Make sure LOG_ARCHIVE_START: set to FALSE, CLUSTER_DATABASE: set to FALSE
2. Shut down all instances
3. Mount the database, but not open, by an exclusive instance
4. Enter “alter database noarchivelog;”
5. Set LOG_ARCHIVE_START=FALSE for each of the other instances
6. Shut down the exclusive instance and reset its CLUSTER_DATABASE to TRUE
7. Restart all instances using the modified parameters

CLUSTER_DATABASE is an identical setting in RAC and has to be restored back to TRUE so that there will not be problems starting the other RAC instances. “alter system set cluster_database=false scope=spfile” before shutdown will do it, if SPFILE shows up on “show parameter spfile;” command in Sql*Plus.

We spent hours in truning off archivelog mode because we did not learn there were guaranteed restore points in the database. If there are records in view v$restore_point, you need to run a DROP command to delete the guaranteed restore point. If you do not do that, you will hit error:
ORA-38781: cannot disable media recovery – have guaranteed restore points
or “ARCHIVELOG mode cannot be turned off because a guaranteed restore point exists.” in Grid Control.

Once archive log mode is turned off, all instances in the RAC will in noarchive log mode!

Enable Remote SYSDBA Login

The key to log onto Oracle as SYS or SYSDBA from a remote client is by setting init.ora parameter REMOTE_LOGIN_PASSWORDFILE .

Steps:

Step 1: Set the init parameter, and then bounce the database:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

EXCLUSIVE forces the password file to be tied exclusively to a single instance. In 10gR2 release, the value EXCLUSIVE is supported for backward compatibility and now has the same behavior as the value SHARED (Reference B14237-04).

Step 2: Create a passwor file:
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxx

ORAPWD utility has an option “entries=”, which is not mandatory. I do not know what is the default number. In most cases, just do not use this option.

Step 3: Bounce the DB or update spfile
SQL> alter system set remote_login_passwordfile=none scope=spfile; –> if using spfile else shutdown and start the database

Step 4: Verify below view returns at least one row (username SYS):
SQL> select * from v$pwfile_users;

If yes, then everything works and the new passwordfile is in use by the username on the list. Now, SYSDBA can logon to the database from a remote client.

Additional notes:
1. If “grant sysdba to user_A;” is run by SYS, the user_A will show up in view v$pwfile_users. And user_A can login as SYSDBA useing its own password (not the one in passwordfile).
2. Without Step 1, Step 2 really does not do anything.
3. The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and should not be used, it is for ‘normal’ users that use OS authentication (Note 50507.1).
4. If REMOTE_OS_AUTHENT is set to FALSE, OS_AUTHENT_PREFIX does not take any effect.

OPMN is failing to start. OPMN must be started in order to correctly install and configure the patchset

Recently we were upgrading Oracle E-Business Suite from 12.0.4 to 12.1.2 for which Web home needs to be upgraded from 10.1.3.0 to 10.1.3.5.

While running patch 8626084 (PLACEHOLDER BUG TO UPLOAD AS 10.1.3.5 PATCH-SET ON ARU) on IBM-AIX Power 64 System we have encountered an error stating “OPMN is failing to start. OPMN must be started in order to correctly install and configure the patchset“.

I was able to stat the OPMN services from backend but the oui was unable to recognise whether the services are up or now.

Then finally we have followed below steps which fixed the issue and we were able to upgrade the patchset to 10.1.3.5

1. Please confirm that patch 7359933 – contains the latest template changes for 10.1.3.5

2. Verify that upgrade to JDK 1.6 as per “Step 6 Upgrade Oracle E-Business Suite Release 12
JDK to Java 6.0 latest update has been performed.
- Reference Step 7 Upgrade Oracle E-Business Suite Release 12 JDK to Java 6.0 latest update (Recommended)
Oracle recommends you to upgrade Oracle E-Business Suite Release 12 Java with the latest Java 6.0 update.
Of Note Note 454811.1

3. Verify that the inventory is correct on the target instance. If any questions or concerns,
follow steps outlined in Metalink Note 742477.1 :
How to create or update the Central Inventory for Applications R12?

4. Review Section 7: Known Issues -> In Note 454811.1
and Appendix A: Additional IBM AIX-Specific 10.1.3.5 Patch Set Installation Instructions
related to AIX
Implement the suggested changes to $IAS_ORACLE_HOME/opmn/bin/opmctl as documented in the
README of patch 8461753.

5. Please perform the following:
a. Edit the $IAS_ORACLE_HOME/install/oc4jnames.txt: it should contain:
forms
oacore
b. Source the 10.1.3 ORACLE_HOME
# cd $INST_TOP/ora/10.1.3
# . ./_.env
c. Execute the following
# cd $IAS_ORACLE_HOME/install
# opmnctl status -app >ascontrol_status
# chmod u-w ascontrol_status

Reference the following notes:
Note 849738.1 R12 : Installation Issue with AIX OPMN Patch 10.1.3.4 Using Note : 454811.1
Note.864500.1 Patch 8461753 fails with 0403-057 Syntax error:

Kindly mail us on consult.appsdba@gmail.com if this help in fixing your issue.

- Farhan Shaikh