Working with CONCSUB

CONCSUB is a utility which allows you to submit a concurrent program to the concurrent manager from the operating system level without having to log on to Oracle Applications.

The CONCSUB executable is located at $FND_TOP/bin/CONCSUB.

The functionality of the CONCSUB can be categorized into the following

• Submitting Concurrent Requests
• Controlling Concurrent Managers

Submitting Concurrent Requests
You can use the CONCSUB to execute both seeded and custom programs in Oracle Applications. In case of custom programs they must first be registered in Oracle Applications before you can execute them with CONCSUB.

The following can be used in Oracle Applications to run the active users report from the command line without logging in the applications

CONCSUB APPS/APPS SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDSCURS PROGRAM_NAME=’”Active Users”‘
Submitted request 2866136 for CONCURRENT FND FNDSCURS PROGRAM_NAME=”Active Users”

The log and out file for this program is also created at the location defined by your $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT respectively.

The WAIT=Y/N is used to specify weather to wait for the first concurrent request to be completed before the second is submitted or not.

You can also use various printing parameters with the COCNCSUB to directly print the output of your concurrent request.

PRINTER=
NUMBER_OF_COPIES=
PRINT_STYLE=
LANGUAGE=

Also you could specify the start date and completion options along with CONCSUB by using the following parameters

START=
REPEAT_DAYS=
REPEAT_END=

Controlling Concurrent Managers
Apart from submitting concurrent request the CONCSUB can also be used to shutdown your concurrent managers

CONCSUB apps/apps_password SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN

Sometimes the shutdown of the concurrent managers via the CONCSUB utility using the SHUTDOWN clause hangs and you may want to terminate your concurrent managers, in such a case you can use the ABORT clause with CONCSUB to do a force shutdown of your concurrent managers.

CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND ABORT

In this case a concurrent request to terminate the concurrent managers is fired with a -75 priority. In case of the shutdown the priority is 0 and default priority is of a concurrent request 50, by assigning a -75 priority the CONCSUB ensures abort is executed before shutdown.

Needless to say that the shutdown would fail in case the SYSADMN user or the System Administrator responsibility is inactive.

However to start the concurrent managers the CONCSUB is not used instead the startmgr executable is used.(Though possible)
This is located at $FND_TOP/bin/startmgr.

$startmgr sysmgr=apps/apps@sam
Starting icm@sam Internal Concurrent Manager
Default printer is

To use CONCSUB to start the concurrent managers the STARTUP clause is used

$ CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND STARTUP
Submitted request 2849496 for CONCURRENT FND STARTUP

Repair broken jobs in dba_jobs

Query dba_jobs for jobs that are broken :

sqlplus '/as sysdba'

SQL> select job, what from dba_jobs where broken='Y';

JOB OWNER WHAT
--- ----- -----------------
2 SCOTT Job1
3 SCOTT Job2

Connect as owner of the job :

sqlplus scott/tiger

SQL> exec DBMS_JOB.BROKEN(2,FALSE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_JOB.BROKEN(3,FALSE);

PL/SQL procedure successfully completed.

sqlplus '/as sysdba'

SQL> select job, what from dba_jobs where broken='Y';

No rows selected.

Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i

1. Download the JRE 6 (JRE 1.6.0_X) Plug-in

Go to http://java.sun.com/products/archive/

Select the required release from the drop down menu under, Java 2 Platform Standard Edition (J2SE) -> JDK/JRE - 6 . Select “6 Update 12” and click on “Go”.

Select the Download JRE.

Select Platform -> Windows

Language -> Multi-language

And Continue

Right click on jre-6u12-windows-i586-p.exe and Save Target As…Save the file on your desktop.

Rename the downloaded JRE Native plug-in file from jre-6u12-windows-i586-p.exe to j2se16012.exe

Copy downloaded j2se16012.exe to

hostname:$COMMON_TOP/util/jinitiator

2. Apply following patches to 806 OH:

7362389

5884875

3. Apply the JRE plug-in Interoperability Patch 6863618 using adpatch.

4. Execute txkSetPlugin.sh

$ cd 6863618

$ cd fnd/bin

$ ./txkSetPlugin.sh 16012

5. Apply following patches as post fixes using adpatch on both nodes:

4994984

4469342

ADPATCH - Using Defaultsfile during patching

Using Defaults file with adpatch

-> Creating a defaults files :

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt

Keep passing the values for all the prompts till adpatch asks for patch directory. When it prompts for patch directory do a ctrl+c and abort the adpatch run.

This will create defaults file for you.

-> First adpatch run after creating defaults file should be without using defaults file

-> Using defaults file :

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt logfile=patch_name.log patchtop=patch_top driver=drv_file_name interactive=no workers=number_of_workers

ORA-39038 while running expdp

While running expdp, export fails to start with the following error :

ORA-39038: Object path "TABLE" is not supported for SCHEMA jobs
or
ORA-39038: Object path "SCHEMA" is not supported for FULL jobs

To get the name of the table/schema that needs to be exported to get populated, run the following scripts via SQL*Plus

SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catmet2.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Then verify with:
SQL> SELECT named, object_path, comments FROM schema_export_objects WHERE named='Y';
if the entries are there and if they are present, then retry with expdp

expdp runs fine after running above sqls.

RMAN Installation and Configuration

1. Install 10.2.0.3 Oracle Home and create starter Database (ORACLE_SID – XXXX, Port Pool - 0)

2. Create tablespace for RMAN Catalog:

CREATE TABLESPACE RMAN_CATALOG
DATAFILE '/usr/oraXXXX/XXXXdb/db/oradata/XXXX/RMAN_CATALOG.DBF'
SIZE 10240K AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO;

3. Create RMAN User:

CREATE USER RMAN IDENTIFIED BY rman
DEFAULT TABLESPACE RMAN_CATALOG
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

GRANT "CONNECT" TO RMAN;

GRANT "RESOURCE" TO RMAN;

GRANT "DBA" TO RMAN;

GRANT RECOVERY_CATALOG_OWNER TO RMAN;

GRANT UNLIMITED TABLESPACE TO RMAN;

4. Create RMAN Catalog:

$ rman catalog=rman/rman
RMAN> create catalog tablespace rman_catalog

5. Add following tns entry to TARGET tnsnames.ora:

XXXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = host.domain) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XXXX)
)
)

6. Test RMAN connection from TARGET server and register database:

$ $ORACLE_HOME/bin/rman target=sys/manager@TARGET catalog=rman/rman@XXXX
rman> register database;

7. Run a test backup:

$ $ORACLE_HOME/bin/rman target=sys/manager@TARGET catalog=rman/rman@XXXX
rman> run {
backup database include current controlfile;
backup archivelog all delete input;
}

CPC - Critical Patch Collections

What is CPC ? Is it something related to CPU (Critical Patch Update) or this is something related to RUP (RollUp Patches) ?

A Critical Patch Collection is a collection of critical patches that all R12 Financials customers must apply to ensure proper operation of their systems. More of what was called a Family Pack in 11i days.

If you follow the note to MetaLink Note: 739978.1, you’ll see that on September 26th, Oracle announced Critical Patch Collections (CPC) for the following products: Payables, Receivables, Payments, Subledger Accounting and Tax.

Diagnostics: Apps Check - Concurrent Request

I have been working a lot with Functional Consultants lately, updating their SRs.

Diagnostics: Apps Check - Concurrent Request is requested by Oracle Support in few cases.

This request is present under : Order Management Super User (Responsibility)

Login as SYSADMIN :> Order Management Super User -> Run Requests -> Run Diagnostics: Apps Check (Pass the parameters as specified in SR)

Change ias_admin password

Incase you know the old password, you can change ias_admin password using :

emctl set password

Follow these steps if you don't know old ias_admin password :

1. Stop the EM control.


./emctl stop iasconsole


2. Go to $ORACLE_HOME/sysman/j2ee/config .

3. Take a backup of jazn-data.xml file.

4. Open jazn.dat.xml file in editor.

vi jazn-data.xml


5. Search for an entry like below.



ias_admin
{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu



6. Remove the entire line that contains the property from jazn-data.xml.

7. Now reset the password of ias_admin like below.

$ORACLE_HOME/bin/emctl set password reset


8. Restart EM control as shown below.

$ORACLE_HOME/bin/emctl start iasconsole

Jinitiator 1.3.1.18 for Discoverer Plus 10.1.2

To use Jinitiator 1.3.1.18 for Discoverer Plus 10.1.2 implement the following steps:

1. Login to the Discoverer Middle Tier server machine.

2. Navigate to $ORACLE_HOME/discoverer/config.

3. Edit the configuration.xml file

4. Change the jinit plug section to the following:

plugin_setup="http://mybi.company.com:7780/jinitiator/jinit.exe" version="1.3.1.18"
versionie="1,3,1,18" type="application/x-jinit-applet" plugin_page="/PlusPluginPage.uix"
disco_archive="disco5i.jar" d4o_archive="d4o_double.jar"/>

5. In the Application Server Control( Enterprise manager) go the discoverer administration page
and set the plugin to be used as jinitiator.

You may also have to update the .exe file stored on the server.
If this is the case then:

1. Download jinit 1.3.1.18 from
   http://www.oracle.com/technology/software/products/developer/htdocs/jinit.htm
   
   
2. Rename the file on the Middle Tier server: $ORACLE_HOME/jinit/jinit.exe
   
   
3. Put the 1.3.1.18 .exe file on the server

Query for Resposibilities assigned to a User

select a.RESPONSIBILITY_NAME,b.START_DATE,b.END_DATE from FND_USER_RESP_GROUPS b,FND_RESPONSIBILITY_TL a
where b.USER_ID=(select USER_ID from fnd_user where USER_NAME='&1')
and a.RESPONSIBILITY_ID=b.RESPONSIBILITY_ID

After ATG RUP 5 Concurrent Managers Fail to Start

Concurrent Managers fail to come up and following error is reported in Concurrent Manager logfile :

Could not initialize the Service Manager FNDSM_[SERVER_NAME]_[ENV_NAME]. Verify that [SERVER_NAME] has been registered for concurrent processing. Routine AFPEIM encountered an error while starting concurrent manager....

The cause of this problem is the change of FNDSM trigger in ATG RUP 5.

====================================================
The new FNDSM trigger code is:

CREATE OR REPLACE TRIGGER fndsm
AFTER INSERT ON FND_NODES FOR EACH ROW
BEGIN
if ( :new.NODE_NAME <> 'AUTHENTICATION' ) then
if ( (:new.SUPPORT_CP='Y') or (:new.SUPPORT_FORMS='Y') or (:new.SUPPORT_WEB='Y') ) then
fnd_cp_fndsm.register_fndsm_fcq(:new.NODE_NAME);
end if;
if (:new.SUPPORT_CP = 'Y') then
fnd_cp_fndsm.register_fndim_fcq(:new.NODE_NAME);
end if;
end if;
END;
====================================================

When we run autoconfig on db tier, new row inserted into FND_NODES but FNDSM and FNDIM are not created since db tier not supported CP/FORMS/WEB.
When we run autoconfig on apps tier, this columns are updated to 'Y' but FNDSM trigger is not run because it is fired for insert and not for update.

The Solution is :
1) Recreate the FNDSM trigger same as before but add "AFTER INSERT OR UPDATE ON FND_NODES".
2) Run: exec FND_CONC_CLONE.setup_clean; (with apps)
3) Run autoconfig on db tier and apps tier (in this order)
4) Startup apps tier.

There is no patch at this time to fix this probem, but when ATG RUP 6 will be available it will contain a fix to FNDSM trigger.

Full details in Metalink note 434613.1.

Forgot your Password?

Almost every website that uses username & password have a "forget password" functionality to retrieve users passwords, and so also the Oracle E-Business Suite.

This is a very useful functionality since it reduces the number of SR's opened to the helpdesk team regarding login problems and moreover satisfying the customers which can get a new password in a very short time with no helpdesk intervention.

The implementation of this functionality is very simple and easy.
To enable it you should:

1. set the profile "Local Login Mask" to the current value plus 8 (e.g. current value is 32 -> set value to 40)
2. Bounce Apache

The "Local Login Mask" profile used to customize some attributes of the login page (AppsLocalLogin.jsp), one of them is the "forgot your password" link.
You should set the value of this profile to the sum of all attribute's mask values you are interested in.

The full attributes list is:

Attribute
Mask Value Binary value
Hint for Username 01 00000001
Hint for Password 02 00000010
Cancel button 04 00000100
Forgot Password link 08 00001000
Registration link 16 00010000
Language Images 32 00100000
Corporate Policy Message 64 01000000



Setting the Forgot Password link mask value will add the following TIP to the login page:

The reset password process:

- Click on "Forgot your password?" link will ask for a username to which reset the password.

- After typing the username and click OK, a new workflow process is started (Item type UMXUPWD) and you'll get this confirmation message:

- Shortly you'll get this email - "Password reset required approval" (expired after 4 hours).

- Click on "Approve" to confirm you are interested in a new password.

- Shortly you'll get an email with a temporary password which you have to change on first login.

Very nice and easy to implement functionality, which could be very beneficial.

Related Note 399766.1 - Reset Password Functionality FAQ

Is it possible to use EXPDP with GZIP ?

With the old EXPORT utility you can execute the export using exp command with gzip program as below:

mknod /u01/backup/exp/export_pipe p
nohup /usr/bin/gzip < /u01/backup/exp/export_pipe > /u01/backup/exp/dbaceh.dmp.gz 2>
/u01/backup/logs/gzip.log &
$ORACLE_HOME/bin/exp user/senha@dbaceh file=/u01/backup/exp/export_pipe buffer=40000000
log=/u01/backup/logs/dbaceh.log full=y >$ARQLOG 2>$ARQLOG

When trying to do the export using EXPDP (Data Pump) in the same way, the dump file doesn't work with the gzip program

Is it possible to use EXPDP with GZIP ? and if not, why?

Since Datapump Export is optimized to work from within the server, the export dumpfile information is no longer processed in a sequential manner.

As a result, sequential media, such as tapes and pipes, are no longer supported with Export DataPump (expdp) and Import DataPump (impdp).

So, There is no way to do this with DataPump, as you cannot use named pipes with datapump, so there is no way to pass the dumpfile to the gzip command through a pipe. You can only compress the dump file after the export is complete. It cannot be done during the export itself.

The only option would be to add disk space, or nfs mount a large enough filesystem to the server and use that as the export destination, do the DataPump export to that destination, then use GZIP to compress the output file.

In Oracle 10.2 release, Data Pump by default supports compressing the system metadata ( grants, schemas, roles, types, etc.) that's written to the dump file. This can be manually enabled/disabled using the compression parameter.

For example,
expdp userid/password DIRECTORY=dpump_dir DUMPFILE=scott SCHEMAS=scott
COMPRESSION=METADATA_ONLY

explicitly indicates that the system metadata be compressed. This is the default behavior. Using "COMPRESSION=NONE" would explicitly disable compression.

In 11.1 release of Oracle the feature was enhanced to allow for the compression for table data. The "compression" parameter now accepts the following values:
NONE
METADATA_ONLY
DATA_ONLY
ALL

For example,
expdp userid/password DIRECTORY=dpump_dir DUMPFILE=scott SCHEMAS=scott
COMPRESSION=ALL
will compress all system metadata and all table data in the dumpfile.

Validating and rebuilding large and fragmented indexes in 11i

There are various methods to identify large or fragmented indexes in 11i.

There are three possible ways to rebuild indexes :

i) Using ALTER INDEX ..REBULD ONLINE command
ii) Taking an export of database specifying ROWS=NO and then using the import with option INDEXFILE
iii) Using bde_rebuild.sql
You can download bde_rebuild.sql from metalink doc id 182699.1. Run this script in 11i instance using apps or system account

$sqlplus apps/apps @bde_rebuild.sql

This script will dynamically analyze and generates bde_rebuild_indexes.sql which rebuilds the required indexes.

This script has some optional parameters which script will ask when you run it:
– Owner of table (schema) : applsys (for example)
– Table name :
– Index name or Index Suffix
– Threshold between 20 and 80% : for this parameter default value is 50 which is sufficient for OLTP environment

The only care to be taken while running this script is : This script blocks DML commands on indexes being analyzed, including SELECT statements. Execute in Production during a low online user activity period. Blocking time lasts between few Sec to a few minutes, depending on index size.

Compress dumpfile while data pump export

With original export (exp) we can directly compress the dump file while exporting objects on unix machine by using named pipes (by mknod command). On windows machine for exp there is no such command. So after doing export operation use any compression utility in order to compress the dumpfile as a separate process.

With expdp (up to Oracle version 10.2) there is no such mechanism to compress the dumpfile directly. The COMPRESSION=METADATA_ONLY available on 10g merely compressed metadata being written to the dump file in compressed format. But for data compression directly there was no utility. Manually you would use any compression tool like zip, gzip, tar, compress or other after doing data pump export operation.

With the release of 11g the COMPRESSION parameter is enhanced and now you no need to use any compression utility. You can choose to compress the dumpfile directly while exporting.
The COMPRESSION parameter now supports following values.

COMPRESSION=ALL: It enables compression for the entire export operation.

COMPRESSION=DATA_ONLY: It results in all data being written to the dump file in compressed format.

COMPRESSION=METADATA_ONLY: It results in all metadata being written to the dump file in
compressed format. This is the default.

COMPRESSION=NONE: It disables compression for the entire export operation.

With adding COMPRESSION=ALL, while data pump export operation the dump file can now be reduced by 4/5 times or more than without COMPRESSION.

Use it as below to take a dump of all objects under schema arju and the dumpfile to be written in the directory location DATA_PUMP_DIR as compressed dump,

expdp userid=user/pass compression=all

Note that the COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option.

Forms and Reports Upgrade in Oracle Apps R12 (12.0.X) to 10.1.2.3

This post covers overview of Forms and Reports Upgrade in Oracle Apps R12 (12.0.X) to 10.1.2.3

Key Points for 10.1.2 upgrade in R12/12i (12.0.X)

1.There are two ORACLE_HOMEs on Application Tier
10.1.2.X for Forms & Reports
10.1.3.X for Web Server & J2EE application

2. By default, R12 uses Forms Servlet Architecturefor forms requests (to know more about Forms Servlet click here ); which uses J2EE container (JVM) in 10.1.3 ORACLE_HOME .
This means one HTTP Server (Apache) in 10.1.3 ORACLE_HOME serves all HTTP, Forms and Reports requests. (HTTP Server in 10.1.2 Oracle Home is not used)

You need to deploy new Forms Servlet (formsapp.ear) to 10.1.3 Oracle Home after 10.1.2 Oracle Home upgrade.

3.Deploying new Forms Servlet to 10.1.3 Oracle HOme means deploying formsapp.ear file to 10.1.3 ORACLE_HOME. Use txkrun.pl -script=CfgOC4JAppto deploy new forms servlet in 10.1.3 Home (Metalink Note 397174.1)

4.oc4jadmin password (for 10.1.3 HOME) is stored in xml file $INST_TOP/ ora/ 10.1.3/ j2ee/ forms/ config/ system-jazn-data.xml , If you don’t know oc4jadmin password or wish to change it use steps mentioned here

5. To check forms/reports current version in R12 use
$ $ORACLE_HOME/bin/frmcmp help=y
Forms 10.1 (Form Compiler) Version 10.1.2.2.0 (Production)

6. You need ias_admin password during forms and reports upgrade (default password is secret). Test that you can login as ias_admin/secret

$ emctl authenticate secret
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
success

__________

If you get error like
$ORACLE_HOME/bin/emctl: /d2/ R12/ ab/ apps/ RUP4XB6/ apps/ tech_st/ 10.1.2: does not exist

Fix

Open $ORACLE_HOME/bin/ emctl, emagentdeploy.pl, emdctl and change ORACLE_HOME to correct value

If you get error like
oracle.security.jazn.JAZNInitException: /d2/ R12/ ab/ apps/ RUP4XB6/ apps/ tech_st/ 10.1.2/ sysman/ j2ee/ config/ jazn-data.xml (No such file or directory)

Copy $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml to /d2/R12/ab/apps/RUP4XB6/apps/tech_st/10.1.2/sysman/j2ee/config

__________

.

Forms & Reports Upgrade steps in R12

1. Stop Middle Tier Services

2.Download & install pre-req patch (conditional)

3. Ensure that Inventory is pointing to right location

4.Install 10.1.2.3 (5983622 - This is latest Forms & Report Patchset) using runInstaller from Disk1 (inside patch)
4.1 Select “Oracle Application Server and Developer Suite 10g Rel 2 Software Update” on Select a Product to Install screen
4.2 When prompted for ias_admin password on Application Server Instance Password screen enter secret

5. Shutdown OPMN from 10.1.2 Home (Installer starts OPMN after upgrade)

6. Apply additional interoperability patches

7. Rebuild Forms & Regenerate Jar files

8. Deploy new Forms Servlet to 10.1.3 Oracle Home

9. Start Application

For Step 1-7 follow metalink note 437878.1
For Step 8 follow metalink note 397174.1
For Step 4 follow readme of patch 5983622

References

* 437878.1 Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
* 397174.1 Deploying a New Forms .ear File in Oracle Applications Release 12
* 387106.1 Unable to Apply OracleAS 10g Rel 2 Patchset: ias_admin - “Password Entered .. Is Incorrect”
* 761995.1 Error when following Note 437878.1 : “password you have entered is incorrect. Please enter the ias_admin password that is assigned for this instance
* 420649.1 DBUA from 10Gr2 Oracle Home comes with R12 RapidInstall is not working
* Read Me of Patch - 5983622
* 821253.1 Forms And Reports Standalone Upgrade To 10.1.2.3 Fails on Relinking ins_reports.mk