List of Scheduled Concurrent Requests

One of concurrent programs stopped to run. I am not sure if it is not scheduled to run any more or it is just cancelled temporarily.

Below code (Note 170107.1) helps me to get the answer. There is another Note (602162.1) on the same subject.

select b.concurrent_program_name, a.*
from apps.fnd_concurrent_requests a , apps.fnd_concurrent_programs b
where a.concurrent_program_id = b.concurrent_program_id
and status_code in ('Q','I')
and requested_start_date > SYSDATE
and hold_flag = 'N'
order by b.concurrent_program_name;
-- 'I' indicates a status of Normal

Change EBS 11i password

In Oracle E-business Suite, the commands for changing APPS passwors and for changing SYSADMIN password are different. It is very important to use the right syntax when making the change.

Make sure you can log onto the database as APPS and SYSTEM by using Sql*plus on the apps server BEFORE you run below lines.

- To change APPS password
$FND_TOP/bin/FNDCPASS apps/old_apps_pwd 0 Y system/system_pwd SYSTEM APPLSYS new_apps_pwd

- To change SYSADMIN password
$FND_TOP/bin/FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd USER SYSADMIN new_sysadmin_pwd

- to change schema owner password
FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd ORACLE OE new_oe_pwd

UPDATE on R12:

Below statement by APPS will change SYSADMIN password:
SQL> select fnd_web_sec.change_password('sysadmin','newPwd4U') from dual;

Below line to check if a user's password is right or not:
SQL> select fnd_web_sec.validate_login('ebs_userID', 'userPWD') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('EBS_USERID','USERPWD')
--------------------------------------------------------------------------------
Y

Enable remote SYSDBA login

UNIX OS user who is in the same Group as the user who installed Oracle on the server can login as SYS from the server locally. The key to log onto Oracle as SYS from a remote client is to set init.ora parameter REMOTE_LOGIN_PASSWORDFILE not equal to NONE (the default?).

The steps to make it work:

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 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: 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.

datapump export / import tables

Scripts use datapump to export and import data on table level.

~~~~~~~~~~~~~~~~~ export data from source db ~~~~~~~~~~~~~~~~
-- exp_init.par to export data with filters on tables (for Golden Gate initial load)
DIRECTORY=EXP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=exp_init.log
PARALLEL=8
EXCLUDE=INDEX
EXCLUDE=CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=TRIGGER
EXCLUDE=SYNONYM
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
QUERY=BUS_UNIT_COST:"WHERE BE_ID <> 250681 and BE_ID <> 251013",
   CLAIM:"WHERE BE_ID <> 250681 and BE_ID <> 251013"

-- Shell script to call the .par file. Use "nohup" to run it.
export ORACLE_SID=SOURCEDB
PWD=`/path/XXXX`
$ORACLE_HOME/bin/expdp userID/$PWD parfile=exp_init.par

~~~~~~~~~~~~~~~~~~~~ import ~~~~~~~~~~~~~~~~~~~~~
-- optional: drop indexes first (and disable triggers if any)
SQL> alter table userid.CLAIM drop PRIMARY KEY drop INDEX;
SQL> drop INDEX userid.clm_INDX1;

-- imp_init.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=imp_init.log
PARALLEL=8
TABLE_EXISTS_ACTION=TRUNCATE
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM

-- Shell script for importing
export ORACLE_SID=TARGETDB
echo $ORACLE_SID
PWD=`/path/XXXX`
$ORACLE_HOME/bin/impdp userID/${PWD} parfile=imp_init.par

-- after import, re-create indexes, such as
SQL> CREATE UNIQUE INDEX userid.CLM_PK ON userid.CLAIM
  (CLM_ID, BE_ID)
  parallel 4 nologging
  TABLESPACE CLM_INDEX01;
SQL> ALTER TABLE userid.CLAIM ADD (
  CONSTRAINT CLM_PK
  PRIMARY KEY (CLM_ID, BE_ID)
  USING INDEX userid.CLM_PK
  ENABLE VALIDATE);

SQL> grant select on userid.CLAIM to READONLY_RO;
... ... ...

How to find conflicting patches with opatch

You can use OPatch from the $ORACLE_HOME/OPatch folder (%ORACLE_HOME%/OPatch for Windows), to find out if installing a certain patch will conflict or not. The steps are as follows:

1. Download the patch which you intend to apply

2. Unzip the patch file

3. Change to the directory where the patch has been downloaded

4. Run the following command:

> opatch query -all

This will simulate the patch application procedure and you will find out if it is going to conflict with any of the existing patches.

You can read on more such OPatch options from the ../OPatch/docs/Users_Guide.txt file.

Note 459556.1

307706.1 (How To Determine Patch Is Superset/Subset Patch)