FNDCPASS erroring out with APP-FND-02704: Unable to alter user XXC to change password

Issue Description

When running FNDCPASS to change password of Product Schema we got the below error in the FNDCPASS log.

bash $> FNDCPASS apps/LPG 0 Y system/CNG ORACLE XXC K3r

Log filename : L7339701.log

Report filename : O7339701.out

bash $> cat L7339701.log

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:

+---------------------------------------------------------------------------+

Current system time is 09-JUL-2011 04:58:10

+---------------------------------------------------------------------------+

APP-FND-02704: Unable to alter user XXC to change password.

APP-FND-01564: ORACLE error 1403 in changepassword

Cause: changepassword failed due to ORA-01403: no data found.

The SQL statement being executed at the time of the error was: and was executed from the file &ERRFILE.

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 09-JUL-2011 04:58:10

+---------------------------------------------------------------------------+

Troubleshooting and Solution

Initially looking at the error, we concentrated our efforts in the error ‘ORA-01403: no data found’ but could not find any significant lead.

Then we checked if the schema XXC was a oracle product schema or a database user only. The below query confirmed that it was a product schema and user had not expired nor was it disabled.

Select oracle_id,oracle_username,creation_date,last_update_date from apps.fnd_oracle_userid =’XXC’;

Select user_id,username, account_status,profile,created, ,password,expiry_date from dba_users where username = 'XXC';

As the user was present in the apps.fnd_oracle_userid table it was confirmed that we must use the FNDCPASS to change password and not alter user command, so we had a issue with FNDCPASS.

We then concentrated on the error ‘APP-FND-02704: Unable to alter user XXC to change password’.

We found the profile of the user XXC from the dba_users was DEFAULT.

When we checked the PASSWORD_VERIFY_FUNCTION of the value for profile the DEFAULT it was set to PASSWORD_DO_NOT_CHANGE.

We temporarily changed the PASSWORD_VERIFY_FUNCTION of the value for profile the DEFAULT to NULL and ran the FNDCPASS and it went through successfully. After that we changed back the PASSWORD_VERIFY_FUNCTION to the previous value

SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION NULL;

bash $> FNDCPASS apps/LPG 0 Y system/CNG ORACLE XXC K3r

Log filename : L7339702.log

Report filename : O7339702.out

bash $> cat L7339702.log

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:

+---------------------------------------------------------------------------+

Current system time is 09-JUL-2011 06:01:35

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

Concurrent request completed successfully

Current system time is 09-JUL-2011 06:01:35

+---------------------------------------------------------------------------+

SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION PASSWORD_DO_NOT_CHANGE;

Background of FNDCPASS

FNDCPASS command is recommended to be used in e-Business Suite to change the password of the product schemas.

You can use the FNDCPASS utility to change password for below users

1. APPS and APPLSYS

FNDCPASS apps/ 0 Y system/ SYSTEM APPLSYS

Using above syntax APPLSYS password can be change. The change in APPLSYS password change changes the APPS password also.

When you issue the above command below things take place internally

i. Validations for SYSTEM and APPS password

ii. Re-registers password in Oracle Applications

iii. Changes the password for APPS and APPLSYS in FND_ORACLE_USERID table and stores it in encrypted format

iv. Runs the ALTER user command for APPS and APPLSYS to change the password in the DBA_USERS table

2. Product Schema like AP, PA, GL, etc .,

FNDCPASS apps/ 0 Y system/ ORACLE

Using above syntax we can change the password for Product Schema.

When you issue the above command below things take place internally

i. Validations of SYSTEM and APPS password

ii. Updates the new password for the product schema in the FND_ORACLE_USERID table and stores it in encrypted format

iii. Runs the ALTER user command for product schema to change password in the DBA_USERS table

3. Application User like SYSADMIN

FNDCPASS apps/ 0 Y system/ USER

Using above syntax Application User passwords can be changed.

When you issue the above command below things take place internally

i. Validations of APPS Password

ii. Updates password in the FND_USER table and stores it in encrypted format.

PS: Note that FNDCPASS when used for changing Application users does not validate SYSTEM password for changing the password.

You can give dummy password for SYSTEM and still use this command successfully.

-- Tanveer Madan

Steps to drop a RAC Database

Issue Description:

Recently in one of our projects we had to drop a RAC database and then recreate the database.

The normal steps to drop a database are as below

SQL> shutdown immediate;

SQL> startup mount restrict exclusive;

SQL> drop database;

When we issued these statements we got the below error

SQL> drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

Background of the DROP DATABASE command:

Prior to Oracle 10g in order to drop a database it was required to manually remove all the data files, control files, redo logfiles and init.ora, password file etc but with oracle 10g dropping a database is a single command.

Once the drop database command is executed successfully the Oracle Database drops the database and deletes all control files and data files listed in the control file. If the database used a server parameter file (spfile), it is also deleted.

Solution:

Our environment was a 4 node RAC. TSCP1, TSCP2, TSCP3 and TSCP4 were the instances and TSCP was the database

Below are the steps to drop the database cleanly otherwise you can just follow the steps marked in asterisk.

1. * Shut down database and listeners cleanly

2. If it is registered in the CRS in our case 11g Grid, first we had to de-register it from CRS

i. De-register the listener

                        srvctl remove listener –l Listener_TSCP1_node1

                        De-register all the listeners configured for that database on all the nodes

ii.      De-register the instances

srvctl remove instance –d TSCP -i TSCP1

De-register all the instances configured for that database.

iii.         De-register the database 

srvctl remove database –d TSCP

3. Make the below changes in the init.ora of the instance 1 and start the database

cluster_database=false

Now then disable the redo threads

SQL> startup

SQL> alter database disable thread 2;

SQL> alter database disable thread 4;

SQL> alter database disable thread 4;

SQL> shutdown immediate

4. During RAC creation we add redo thread and undo tablespace for each instance. So for clean drop we need to drop them as well.

Start the instance 1 only in mount state

Drop the undo tablespaces for all the other instance except the first.

SQL> startup mount

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

SQL> drop tablespace UNDOTBS3 including contents and datafiles;

SQL> drop tablespace UNDOTBS4 including contents and datafiles;

Then drop the redo groups

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database drop logfile group 4;

Then shutdown the database cleanly and do not do a abort.

SQL> shut immediate;

5. Drop the database

SQL> startup mount restrict exclusive;

SQL> drop database;

Tip:

If your system is cooked file system then the drop database statement will drop the control files and data files listed in the control file physically. Then the database cannot be recovered at all.

But if your system is raw file system, and if you have taken a backup of the control file just before dropping your database, you can recover the database.

If you have taken backup of control file to trace then you can use that file and start the database as we do in cloning.

If you have taken backup of control file in binary format then you can use the dd command and copy it to the raw volumes of the control file and start the database.

You will not loose the data since the drop database command does not drop or delete contents of the raw volumes.

Please do not try this production, you can try and validate this on your test systems first.

--Tanveer Madan