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
Sunday, October 18, 2009
Monday, September 21, 2009
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.
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.
Thursday, September 10, 2009
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;
... ... ...
~~~~~~~~~~~~~~~~~ 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;
... ... ...
Wednesday, August 26, 2009
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)
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)
Thursday, August 20, 2009
Install standalone Discoverer for 11i
There are several installations involved to make Discoverer work with Oracle EBS 11i. Here are the steps I went through on a SuSe 10 server.
1. OS level
$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP2 (i586) - Kernel \r (\l).
$ uname -a
Linux ebsapp1d 2.6.16.60-0.21-bigsmp #1 SMP Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux
2. Download the right CDs from Oracle.
The Oracle 10.1.2.0.2 BI standalond is in two Zip files (for Linux). It does not install the Applications Server (AS) 10g Infrastructure tier!
3. Read the "Oracle BI Installation Guide" coming with the installation Zip files.
Make sure rpm OPENMOTIF release meets the requirement. These worked well on my Prod server:
$ rpm -aq | grep openmotif
openmotif21-libs-2.1.30MLI4-143.9
openmotif-libs-2.2.4-21.12
If it is not, Reports service will have issues (see Note 762394.1). I had to follow Note 726970.1 to stop growing 200+ Reports sessions on OS level on my Dev server.
4. Install Oracle Buiness Intelligence 10g (10.1.2.0.2)
During the installation, two warnings pop up. In the firsst one, go to the command line and manually run "opmnctl startall". In the second one, apply patch 6690831.
This installation puts Discoverer in version 10.1.2.48.18
5. Install 10gAS Upgrade (patch 5983622) to upgrade BI standalone from 10.1.2.0.2 to 10.1.2.3
This is reqiured to make Discoverer work on SuSe 10. It upgrades Discoverer to 10.1.2.55.26
6. Apply patch 7595032 (Discoverer CP4).
Discoverer will not work with EBS 11i without this CP. (I hit issue described in Note 761287.1). Before patch 7595032 installation worked, patche 680880 (for opatch) and patch 4398431(for JDBC) were applied.
1. OS level
$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP2 (i586) - Kernel \r (\l).
$ uname -a
Linux ebsapp1d 2.6.16.60-0.21-bigsmp #1 SMP Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux
2. Download the right CDs from Oracle.
The Oracle 10.1.2.0.2 BI standalond is in two Zip files (for Linux). It does not install the Applications Server (AS) 10g Infrastructure tier!
3. Read the "Oracle BI Installation Guide" coming with the installation Zip files.
Make sure rpm OPENMOTIF release meets the requirement. These worked well on my Prod server:
$ rpm -aq | grep openmotif
openmotif21-libs-2.1.30MLI4-143.9
openmotif-libs-2.2.4-21.12
If it is not, Reports service will have issues (see Note 762394.1). I had to follow Note 726970.1 to stop growing 200+ Reports sessions on OS level on my Dev server.
4. Install Oracle Buiness Intelligence 10g (10.1.2.0.2)
During the installation, two warnings pop up. In the firsst one, go to the command line and manually run "opmnctl startall". In the second one, apply patch 6690831.
This installation puts Discoverer in version 10.1.2.48.18
5. Install 10gAS Upgrade (patch 5983622) to upgrade BI standalone from 10.1.2.0.2 to 10.1.2.3
This is reqiured to make Discoverer work on SuSe 10. It upgrades Discoverer to 10.1.2.55.26
6. Apply patch 7595032 (Discoverer CP4).
Discoverer will not work with EBS 11i without this CP. (I hit issue described in Note 761287.1). Before patch 7595032 installation worked, patche 680880 (for opatch) and patch 4398431(for JDBC) were applied.
Subscribe to:
Comments (Atom)