Not able to create new database connection: FND^@SECURITY_APPL_LOGIN_FAILED^@ when starting Concurrent Managers

Hi,

Recently we faced a issue after cloning R12 environment

Issue Description:

-----------------

The concurrent managers were not coming up after cloning

Workflow Agent Listener Service

Workflow Mailer Service

Workflow Document Web Service

Email Center Download Processor

Output Post Processor

When checking the manager logs for each of the managers I found the below error

Not able to create new database connection: FND^@SECURITY_APPL_LOGIN_FAILED^@

Troubleshooting:

---------------

Quick list of managers which were not started showed us that the mangers using the
framework were the ones affected.

Error pointed issue with the GUEST user.

Hence we checked the GUEST password in the dbc file $FND_SECURE/CDEV.dbc on CM node
and found the below entry

GUEST_USER_PWD=GUEST/CPRD

Also when we checked the context file $CONTEXT_FILE on CM node we found the parameter
s_guest_pass set to CPRD

Now we wanted to validate GUEST password at database base level

1. Conn to database as apps

2. Find the password at database level

SQL> select fnd_profile.value('GUEST_USER_PWD') from dual;

FND_PROFILE.VALUE('GUEST_USER_PWD')

——————————————————————————

GUEST/CDEV

Here we found that in the database the password for GUEST was different

3. Then we checked if the guest user was working fine with the password at database level

SQL> select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','CDEV') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','CDEV')

——————————————————————————–-----

Y

4. We also found that mismatch in database and the CM node was because, as this is a multi
node architecture the autoconfig was not run last on the CM node but some other node which
had the value for s_guest_pass as CDEV. Hence we inferred that the database was updated
with the password for GUEST user with the value of s_guest_pass in the context file of the
node on which the autoconfig was run last

Solution:

--------

We can resolve this issue in two ways

Method 1

-----------

The traditional way of changing the GUEST password is to edit the parameter s_guest_pass in
the context file and running autoconfig

In our case as only the context file on the CM node had the wrong entry, we just need to make
the change in the context file on CM node and run autoconfig to fix the issue.

If you have more nodes where the context file is having the wrong password, then you need to correct the entry and run autoconfig on all those nodes

Method 2

-----------

In this method we can avoid running the autoconfig. We followed this method to fix the issue

i. Open the adgendbc.sh at $INST_TOP/admin/install and change the GUEST password to the
right password

ii. Take backup of the dbc file at location $FND_SECURE

iii. Run adgendbc.sh from location $INST_TOP/admin/install. It will prompt for Apps
username and password

iv. Check the new dbc file generated at location $FND_SECURE and see if the correct GUEST
password is generated

v. Do steps i to iv on all nodes where the dbc file has the wrong GUEST password

vi. Restart the concurrent managers

vii. Make the change in the conext file on the all nodes were the above steps i to iv were done,
so that the next autoconfig will generate the right dbc file

Regards,

Tanveer

ORA-01620 When mounting a standby database

Hi,

Recently we had task to setup a DG for a RAC database.

The primary and the DG were 11g.

After copying the primary database to the DG server, we tried to mount the database on the
DG server before starting the recovery. But we got the below error

SQL> alter database mount standby database;

alter database mount standby database

*

ERROR at line 1:

ORA-01620: no public threads are available for mounting

The alert log had only the below entry and also no trace file was generated for the error.

ORA-1620 signalled during: alter database mount

From the error it was clear that it was issue with the redo log threads. But for building a DG we need not add redo threads at the standby database and also as we were not going to do a DG
Broker setup hence standby redo log file addition was also not necessary. Hence it became sure to us that the issue was not on the standby database side but on the primary database

TROUBLESHOOTING:

On the Primary Database when we ran the below query we found that the threads were
enabled for the RAC instances in PRIVATE mode and not in PUBLIC mode which is needed for the DG to work.

SQL> select thread#,status,enabled from v$thread;

THREAD# STATUS ENABLED

---------- ------ --------

1 OPEN PRIVATE

2 OPEN PRIVATE

3 OPEN PRIVATE

4 OPEN PRIVATE

In the RAC system, each instance has to have its own redo log groups. The redo log file groups of an instance are collectively called a thread, or more appropriately, a redo log thread. The redo threads can be private or public. If the redo thread is public then any instance
can acquire it and if it is private then only a specific instance can acquire it as mentioned in the
initialization file.

SOLUTION:

Inorder to get the DG running, we must convert all the redo threads to PUBLIC in primary
database, create the standby control file, and mount the standby database with this standby
control file.

Below are the steps followed by us, but there are can be deviations.

Note: To enable or disable threads the database has to be in open mode

1. Shut down all instances other than the instance 1

2. Disable the threads as you cannot change the mode from PRIVATE to PUBLIC directly.

SQL> alter database enable public thread 1;

alter database enable public thread 1

*

ERROR at line 1:

ORA-01612: instance TSEP (thread 1) is already enabled

Hence we have disable it and then enable in PUBLIC mode

alter database disable thread 2;

alter database disable thread 3;

alter database disable thread 4;

Also you cannot disable thread 1 at this point, as the instance 1 will be using it

SQL> alter database disable thread 1;

alter database disable thread 1

*

ERROR at line 1:

ORA-01615: instance TSEP (thread 1) is mounted - cannot disable

3. Enable the threads in PUBLIC mode

alter database enable public thread 2;

alter database enable public thread 3;

alter database enable public thread 4;

4. Shut down instance 1 and open the database in instance 2.

Repeat the disable thread and enable thread in PUBLIC mode for instance 1

alter database disable thread 2;

alter database enable public thread 2;

5. Start all the other instances of the RAC including instance 1

6. Create the standby control file again on the primary database

7. Copy the standby control file to the standby database and mount the database

SQL> alter database mount standby database;

Database mounted

8. Start the recovery process

-- Tanveer Madan

Issue creating FND_CTX_LOBS with missing DEFAULT_DATASORE

Your autoconfig/adadmin may fail saying that the index APPS.FND_CTX_LOBS is not found and
While trying to create the index APPS.FND_CTX_LOBS you may get the below error:

SQL> @aflobbld.sql applsys apps
declare
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_DATASTORE
ORA-06512: at line 277

Solution:
Run the below commands in sequence

sqlplus ctxsys/ctxsys
spool ctxdef.log
@?/ctx/admin/ctxdef.sql

sqlplus ctxsys/ctxsys
@?/ctx/admin/defaults/dr0defin.sql "AMERICAN";


While running dr0defin.sql if you get the below error, please ignore and proceed further
Creating default policy...
begin
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10507: duplicate index name: DEFAULT_POLICY_ORACONTAINS
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 694
ORA-06512: at line 2


Then run aflobbld.sql it will error out with following error 1st time:

SQL> @aflobbld.sql applsys apps
declare
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at line 277

Ignore the error and rerun the same script to create the index apps.fnd_ctx_lobs


- Saptha

Changes made in context file not reflecting in the configuration files

You made few changes to context file and ran autoconfig. But the changes are not propagated to the configuration files. Also, the changes you made to the xml file itself got wiped out.

Here is the deal:
In the autoconfig log, you will see the following:

-------------------ADX Database Utility Finished---------------
OAM Context editing support feature: Enabled
OAM Customization support feature : Enabled
File system template : /apps/OECPRD/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp
Checking for customizations to Context template
Customizations found : None
----------------------------------------------------------------
File system Context file :/apps/local/OECPRD/inst/apps/OECPRD_c7-a1-06/appl/admin/OECPRD_c7-a1-06.xml

Checking the Context file for possible updates from the Database
Comparing the Context file stored in database with the Context file in the file system

Result : File system Context is below par with respect to the data base Context
Action to be taken : Copy the Data Base Context onto the file system

Result : Context file successfully copied

As seen, the autoconfig actually copied the context file from the database to the file system. This explains why the changes were not propagated and also explains why the context file changes are wiped out.

So why did this happen?
This happened because autoconfig determined that the version of the xml in the database is greater than the one on the filesystem.

So, what can cause this situation?
One thing that I can think of is that the context file was copied from another node of the same system and only host names were changed and autoconfig was run.

So regardless of what caused this issue, how do you fix it?
FIX:
====
1. select name,version,last_synchronized from apps.fnd_oam_context_files where node_name='c7-a1-06' order by serial_number desc

2. Copy back the xml with which you intended to run autoconfig to the $CONTEX_FILE location and edit the xml tag (basically it should be any value bigger than what was returned in step 1). If the value returned in step 1 was 347, update the s_contextserial in the xml to 348. Following is the code block which needs to be updated:

oa_context version="$Revision: 120.217.12000000.48 $"

oa_context_name oa_var="s_contextname" OECPRD_c7-a1-06 oa_context_name
oa_context_serial oa_var="s_contextserial" 348 oa_context_serial

3. Run autoconfig.
4. Validate that the xml is fine and you don’t see entries in the log that xml was copied from the database to filesystem.

- Aravind Kamath Posral

Of Analyzing Situations Objectively and Emotionally

Alright, this is my third post which is not technical, after "my introduction" and "Musings of the mind". This is again more of pondering over situations that life presents - mental grind of the sorts - and nothing technical. So you have been warned sufficiently that this is not a tech post and proceed to read the rest of the post at your own risk! I am also expecting quite a few flames!

Recently I spent some time with a old friend of mine and he is working through some issues. Talking to him made me think on this subject and hence this post.

I have often seen people telling others to think objectively in a structured manner and not emotionally/unorganized way to solve issues. The best part about this thought process is that most of us think objectively and in a structured logical fashion when we are dealing with problems at work or extraneous situations. Be it an Oracle apps issue, talking about promotion to your boss, changing jobs etc.. However, whenever presented with a problem associated to anything dear to ones heart, suddenly the objectivity, logic and structure in thinking seems to vanish. Most of us start thinking with our hearts - emotionally. The person starts addressing the problems in ways which defy "logic" to others.

In my life's experience, I have learnt that it is easy to ask someone to think objectively to address an issue close to heart - but the actual person who goes through it - will seldom be able to do it.

I think this is so because nothing is perfect in this world - not the thing/person close to your heart included. We have all the while focused on the positives/amiable aspects and held something/someone close to our heart and ignored everything else. Mind you, life is dynamic and when negatives start becoming predominant, we are no longer able to digest this. On many occasions, we hate to address such issues and procastinate acting on them because of a multitude of reasons - insecurity, societal issues, turbulence, grief... Mind just wants to believe that everything is ok. Howeverm when it reaches a breaking point, the issue can no longer be ignored, the person is stuck emotionally and loses all reasoning.

So it is easy to ask someone to think objectively, but when matters of heart hits us, we ourself lose our ability to think objectively/logically.

I dont have any solutions/suggestions here, but I think it certainly helps to "step into ones shoes" before being judgemental about others resolve, grit, ability to think rationally, deal with issues and so on..

Finally, everyone has something very very dear to heart - just that it differs from person to person. There may be "gifted" people with ability to think rationally and objectively in matters of heart, but from what I have seen in my life so far, they are a minority!

- Aravind Kamath Posral

Resizing the TEMP Tablespace

The TEMP tablespace can be resized with or without outage on the database. But resizing tablespace without outage may require few extra steps.

Example considering with outage

---------------------------------------

Considering we have a single temporary tablespace in the database, and the requirement is to resize it and add it into

temporary tablespace groups

1. Check the default temporary tablespace, this is just for validation

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

Also if you check the TEMPORARY_TABLESPACE column in dba_users table, all the users will have TEMP as temporary tablespace

2. Check the size of the temporary tablespace

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

--------------- -------------

TEMP 40

3. To make effective use of the volumes available, we can create another temporary tablespace with smaller size and make

note of the volumes already being used by the temporary tablespace and use the same volumes to create our new resized temporary

tablespace

SQL> create temporary tablespace TEMP3

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

You cannot drop the temporary tablespace TEMP as it is still the default temporary tablespace. If you try then you will get

the below error

Hence let us change the default temporary tablespace as TEMP3

SQL> alter database default temporary tablespace TEMP3;

Database altered

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP3 Name of default temporary tablespace

4. Now we can drop and recreate the TEMP tablespace as per new sizes

SQL> drop tablespace TEMP;

Tablespace dropped

As we are going to also add the temporary tablespaces to group we can create as below

SQL> create temporary tablespace TEMP1

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

SQL> create temporary tablespace TEMP2

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

--------------- -------------

TEMP1 16

TEMP2 16

5. Now we can add these into temporary tablespace group

SQL> alter tablespace TEMP1 tablespace group TEMP;

Tablespace altered

SQL> alter tablespace TEMP2 tablespace group TEMP;

Tablespace altered

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------- -------------------

TEMP TEMP1

TEMP TEMP2

6. Now change the default temporary tablespace to TEMP and drop TEMP3

SQL> alter database default temporary tablespace TEMP;

Database altered

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

SQL> drop tablespace temp3;

7. As we have retained the name of the tablespace group as TEMP which was same previously, we need not alter the users in the dba_users table.

If you change the name of the tablespace group as TEMP_A, then you need to change the temporary tablespace for all the already created users.

SQL> alter user XABC default temporary tablespace TEMP_A;

New users who will be created after this resize exercise will by default get TEMP_A as temporary tablespace.

Example considering without outage

-------------------------------------------

The steps remain the same as above but with few changes

A. Follow steps from 1 to 3

B. Now as the database is in use, the users will use the temporary tablespace TEMP though we have changed the default temporary tablespace to TEMP3.

This is because all the already created users have TEMPORARY_TABLESPACE column in dba_users table updated as TEMP.

So before dropping tablespace TEMP. we need to run alter user commands and change the temporary tablespace to TEMP3 to all the users in dba_users

Once this change is made, any future sessions from this users will us TEMP3 for their operartions.

Now you can drop the TEMP tablespace. Before dropping check that no sessions are using TEMP tablespace.

C. Follow steps 4 and 5

D. As in step B, we now need to change the temporary tablespace of the users back to TEMP if you retained the same previous name, if not then change it

to the new temporary tablespace name.

E. Follow step 6

TEMPORARY TABLESPACE GROUPS

Hi,

Just in addition to the last post I would like to share this post as well.

TEMP tablespaces can be added into groups called the TEMPORARY TABLESPACE GROUPS

You can create multiple temporary tablespaces and can add it to temporary tablespace groups.

Then assign these temporary groups as default temporary tablespace to users.

The temporary tablespace groups are logical groups, but the temporary tablespaces which are part of the groups are physical in nature

The advantages of having temporary tablespaces are

* User session can use multiple temporary tablespaces for sorting

* We can set multiple default temporary tablespaces for users

Below examples is considering the database is a two node RAC.

Let us see how we can implement the TEMPORARY TABLESPACE GROUPS.

---------------------------------------------------------------

In database we can see that few users may do a lot of sorting operations and require more of temporary tablespace and few users may not.

If we have single temporary tablespace then if a particular user uses majority of temp tablespace then others will face a crunch for it.

Hence we can create multiple temporary tablespaces with varied sizes and add them to groups and assign the groups based on the usage as default tablespaces to users.

As example, we can create three temporary tablespaces

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

--------------- -------------

TEMP1 25

TEMP2 25

TEMP3 15

We can add TEMP1 and TEMP2 to a group called TEMP_A. This group can be assigned to all the users that use majority of the temporary tablespaces

We can add TEMP3 to a group called TEMP_B and assign it all other users

SQL> alter tablespace TEMP1 tablespace groups TEMP_A;

Tablespace altered

SQL> alter tablespace TEMP2 tablespace groups TEMP_A;

Tablespace altered

SQL> alter tablespace TEMP3 tablespace groups TEMP_B;

Tablespace altered

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------- -------------------

TEMP_A TEMP1

TEMP_A TEMP2

TEMP_B TEMP3

SQL> alter user XXA default temporary tablespace TEMP_A;

The advantage of this is that the user XXA may be running multiple sorting operations at any given point of time. By setting the temporary tablespace

group, the XXA can use TEMP1 or TEMP2 for the operations instead of one single temporary tablespace for the sorting operations.

Also you can set default temporary tablespace at database level to TEMP_B, so that any new users created will use TEMP_B as default temporary tablespace

SQL> alter database set default temporary tablespace TEMP_B;

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP_B Name of default temporary tablespace

-- Tanveer Madan

How to Resize the UNDO tablespace

Hi,

This is fairly very familiar to everyone but nevertheless I wanted to share this.

UNDO tablespace size can be changed by taking a outage on the database or without it.

Below examples is considering the database is a two node RAC

The database has two UNDO tablespaces

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

--------------- -------------

UNDOTS2 25

UNDOTS1 25

With Outage

----------

It is fairly straight forward.

1. Shut down the database cleanly

conn / as sysdba

shut immediate;

2. From one of the database nodes you can do the below steps

SQL> sqlplus / as sysdba

SQL> startup mount;

SQL> drop tablespace UNDOTBS1;

SQL> drop tablespace UNDOTBS2;

SQL> create undo tablespace UNDOTBS1

datafile

'/dv/pper/ora_SCOTT_p1/' size 8600M,

'/dv/pper/ora_SCOTT_p2/' size 8600M

/

SQL> create undo tablespace UNDOTBS2

datafile

'/dv/pper/ora_SCOTT_p3/' size 8600M,

'/dv/pper/ora_SCOTT_p4/' size 8600M

/

Without Outage

--------------

When the database is up and running there will be sessions using the undo tablespace and hence you cannot drop and recreate the tablespace.

Hence we will have to switch the UNDO Tablespace

1. Create two undo tablespaces with smaller size just to handle the load until the activity is finished

create undo tablespace UNDOTBS3

datafile

'/dv/pper/ora_SCOTT_p6/' size 8600M

/

create undo tablespace UNDOTBS4

datafile

'/dv/pper/ora_SCOTT_p7/' size 8600M

/

2. Now alter the instances to use the new UNDO tablspaces

------------------------------------------------------

On Node 1

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS1

SQL> alter system set undo_tablespace = 'UNDOTBS3';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS3

On Node 2

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS2

SQL> alter system set undo_tablespace = 'UNDOTBS4';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS4

When these steps are performed already existing connections will continue to use the UNDOTS1 and UNDOTS2 but all the new sessions will start using

UNDOTBS3 and UNDOTBS4.

3. Drop tablespace and Recreate

----------------------------

You cannot drop the tablespace UNDOTBS1 and UNDOTBS2 until the sessions using them release the undo segments, if not you will get the error

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Use the below query to check if any UNDO segments are in use in the tablespaces UNDOTBS1 and UNDOTBS2 and clear them or wait till they get released

set lines 10000

column name format a10

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE a.usn = b.usn

AND a.usn = c.xidusn

AND c.ses_addr = d.saddr

AND a.name IN (

SELECT segment_name

FROM dba_segments

WHERE tablespace_name IN ('UNDOTBS1','UNDOTBS2')

);

You can also reduce the undo_retention so that the sessions releases the UNDO segments

On Node 1

---------

* sqlplus / as sysdba

SQL> drop tablespace UNDOTBS1; (Include "including contents and datafiles" if it is not RAW filesystem)

SQL> drop tablespace UNDOTBS2; (Include "including contents and datafiles" if it is not RAW filesystem)

SQL> create undo tablespace UNDOTBS3

datafile

'/dv/pper/ora_SCOTT_p1/' size 8600M,

'/dv/pper/ora_SCOTT_p2/' size 8600M

/

Tablespace created

SQL> create undo tablespace UNDOTBS4

datafile

'/dv/pper/ora_SCOTT_p3/' size 8600M,

'/dv/pper/ora_SCOTT_p4/' size 8600M

/

Tablespace created

4. Now alter the instances to use the RESIZED UNDO tablspaces

On Node 1

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS3

SQL> alter system set undo_tablespace = 'UNDOTBS4';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS4

On Node 2

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS4

SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

---------------- ----------- -----------

undo_tablespace string UNDOTBS2

5. Check if any UNDO segments are in use in the tablespaces UNDOTBS3 and UNDOTBS4 and clear them or wait till they get released.

Then drop the tablespaces

SQL> drop tablespace UNDOTBS3;

Tablespace dropped

SQL> drop tablespace UNDOTBS4;

Tablespace dropped

--- Tanveer Madan

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

adoacorectl.sh: exiting with status 152

Today we faced an issue where the oacore jvms were coming up to init state but not alive and then would show down. No errors logged anywhere. Enabling debugging did not give out any information.

Here is how i did the troubleshooting:
1. Validate the connect string (apps_jdbc_url) provided in dbc file by connecting using sqlplus apps/@" apps jdbc url connect string". This was working.

2. When the oacore jservs are in init stage, connect to database and query v$session:

select distinct machine from v$session;
This showed that oacore was indeed making connection to database. This also means that dbc file is NOT the suspect.

3. After clearing persistence folder etc which are usual suspects, all the efforts went in vain.

4. The problem had to be with the configuration because the database connections were going through fine.

5. I analyzed the applications deployed to this jvm and each of the applications were using a custom schema name to connect to the database.

6. This node is an external node in DMZ. This implies this is protected by firewall.

7. For the external nodes, we use OCM to route the connections to the database.

8. Someone had run autoconfig which had overwritten the customization in $IAS_ORACLE_HOME/j2ee/config/oacore/data-sources.xml and it was trying to connect "directly" to database.

9. Changed the connect string in data-sources.xml to route through OCM and bingo! the oacore came "alive"

- Aravind Kamath Posral

Clusterware exclusive mode start of Clusterware Ready Services failed at /oracle/product/grid_home/crs/install/crsconfig_lib.pm line 6488

I was installing the 11gR2 CRS on a two node cluster. While executing rootcrs.pl as root user, we encountered the following error:

CRS-2674: Start of 'ora.crsd' on 'lnx-ps-210' failed
CRS-2679: Attempting to clean 'ora.crsd' on 'lnx-ps-210'
CRS-2681: Clean of 'ora.crsd' on 'lnx-ps-210' succeeded
CRS-4000: Command Start failed, or completed with errors.
Clusterware exclusive mode start of Clusterware Ready Services failed at /oracle/product/grid_home/crs/install/crsconfig_lib.pm line 6488

Upon investigation, found the following error in $ORACLE_HOME/log/lnx-ps-210/crsd/crsd.log:

2011-06-14 23:16:15.424: [ OCRAPI][3917672944]clsu_get_private_ip_addresses: no ip addresses found.
[ OCRAPI][3917672944]a_init_clsss: failed to call clsu_get_private_ip_addr (7)
2011-06-14 23:16:15.630: [ OCRAPI][3917672944]a_init:13!: Clusterware init unsuccessful : [44] 2011-06-14 23:16:15.630: [ CRSOCR][3917672944] OCR context init failure. Error: PROC-44: Error in network address and interface operations Network address and interface operations error [7]
2011-06-14 23:16:15.630: [ CRSOCR][3917672944][PANIC] OCR Context is NULL(File: caaocr.cpp, line: 145)
2011-06-14 23:16:15.630: [ CRSD][3917672944][PANIC] CRSD Exiting. OCR Failed
2011-06-14 23:16:15.630: [ CRSD][3917672944] Done.


To overcome this issue we ran rootcrs.pl deconfig, cleared up the OCR and Voting disk info from failed installation, set the correct permissions for OCR disks and executed root.sh and rootcrs.pl again.

Same result with same set of errors.

I tracked the sequence of events that happen while executing the rootcrs.pl and here is what i found in ORACLE_HOME/cfgtoollogs/rootcrs_lnx-ps-210.log:

2011-06-14 23:16:15: Command output:
> clscfg: -install mode specified
> Successfully accumulated necessary OCR keys.
> Creating OCR keys for user 'root', privgrp 'root'..
> Operation successful.
>End Command output
2011-06-14 23:16:15: Oracle Cluster Registry initialization completed
2011-06-14 23:16:15: Executing cmd: /oracle/product/grid_home/bin/crsctl start resource ora.crsd -init
2011-06-14 23:26:20: Command output:
> CRS-2672: Attempting to start 'ora.crsd' on 'lnx-ps-210'
> CRS-5017: The resource action "ora.crsd start" encountered the following error:
> Start action for daemon aborted
> CRS-2674: Start of 'ora.crsd' on 'lnx-ps-210' failed
> CRS-2679: Attempting to clean 'ora.crsd' on 'lnx-ps-210'
> CRS-2681: Clean of 'ora.crsd' on 'lnx-ps-210' succeeded
> CRS-4000: Command Start failed, or completed with errors.

This means rootcrs.pl is calling crsctl to start crsd. I navigated through the logic used in $ORACLE_HOME/bin/crsctl which led me to crswrapexece.pl. This script gave out the location of the log (/oracle/product/grid_home/log/lnx-ps-210/client) to which this file writes which is were I got the various logs that were written as part of the startup sequence.

Upon digging deep, I found the following errors in crsctl_oracle.log under /oracle/product/grid_home/log/lnx-ps-210/client:

2011-06-14 21:11:01.100: [ OCRMSG][3356149456]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2011-06-14 21:11:01.100: [ OCRMSG][3356149456]GIPC error [29] msg [gipcretConnectionRefused]
2011-06-14 21:11:01.101: [ OCRMSG][3356149456]prom_connect: error while waiting for connection complete [24]
2011-06-14 21:11:01.985: [ OCRMSG][1049650896]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2011-06-14 21:11:01.985: [ OCRMSG][1049650896]GIPC error [29] msg [gipcretConnectionRefused]

This led me to check gpnp log (gpnptool_24455.log):

Oracle Database 11g Clusterware Release 11.2.0.2.0 - Production Copyright 1996, 2010 Oracle. All rights reserved.
2011-06-14 23:13:35.202: [ default][2728037296]

================================================================================
2011-06-14 23:13:35.202: [ default][2728037296]gpnptool client START pid=24443 Oracle Grid Plug-and-Play Tool
2011-06-14 23:13:35.203: [ default][2728037296]gpnptool Command line: /oracle/product/grid_home/bin/gpnptool.bin verify -
p=/oracle/product/grid_home/gpnp/lnx-ps-210/profiles/peer/profile.xml -w=file:/oracle/product/grid_home/gpnp/lnx-ps-210/wallets/peer -wu=peer
2011-06-14 23:13:35.220: [ GPNP][2728037296]Profile signature is valid.

When I checked the file /oracle/product/grid_home/gpnp/lnx-ps-210/profiles/peer/profile.xml, it contained wrong subnet entries which i had typed in erroneously and was not getting updated subsequently by rootcrs.pl. This was not cleaned up by deconfig command as well.

To fix this issue, i renamed the folder lnx-ps-210 under $ORACLE_HOME/gpnp and executed rootcrs.pl and it fixed the issue!

Here are the complete sequence:

1. Clean up failed installation with rootcrs deconfig command as root user.
2. Clean up the OCR and Voting disks which contain information from previous run.
3. Validate the permission for OCR disk to be correct.
4. Move the directory lnx-ps-210 under $ORACLE_HOME/gpnp
5. run $ORACLE_HOME/root.sh as root user
6. run rootcrs.pl script as root user.


- Aravind Kamath Posral

adoacorectl.sh: exiting with status 204

With 30 mins to golive, we encountered following error in production environment while trying to startup the Apache/Web services:

adoacorectl.sh: exiting with status 204
adoafmctl.sh: exiting with status 204

We have forms running in socket mode, so there were no issues with that. Apache came up fine. There were no issues with OPMN. No other errors were logged.

Subsequently, I figured out that the issue was due to /apps filesystem getting filled up. The processes were unable to write to the disk due to which the startup was not happening. During the course of troubleshooting, I figured out that oacorestd.out and oacorestd.err (predominantly stdout) logs were consuming all the space. We have 5 OACORE process and one oafm process. This means we have 6 logs which had grown to consume all the space.

To overcome this issue, we shutdown all the services and zipped the logs to create space.

Once this was done, I tried to start the services but still encountered the same error. However, this time around, I saw the following error in oacorestd.err:

11/06/12 09:14:05 Error initializing server: /apps/local/CEPROD/inst/apps/CEPROD_c7-iprd-54/ora/10.1.3/j2ee/oacore/config/server.xml, Fatal error at line 1 offset 1 in file:/apps/local/CEPROD/inst/apps/CEPROD_c7-iprd-54/ora/10.1.3/j2ee/oacore/config/server.xml: .:
XML-20108: (Fatal Error) Start of root element expected.

When i checked the server.xml, I found that it had been reduced to zero bytes. At this moment, one of the metalink notes suggested to run autoconfig to fix this issue.

However, we were already out of the outage window and autoconfig would be the last option.

As a workaround to this issue, I went to the logs of the last autoconfig run (we were sure that no patches that bring in new templates were applied
post that autoconfig run) and located the server.xml backup for both oacore and oafm and placed them back and started the services and everything came up as expected.

-Aravind Kamath Posral

Inviting topics/subject areas from my readers.

Hi to all readers of this blog. Over the past three years, I have written about some of the issues that we faced in our projects and have provided the solution and troubleshooting tips.

To keep this blog vibrant and healthy, let me know the topics on which you would like to see my perspective/understanding.

You can drop an email to me with your wishlist of topics/subject areas. I will try to come up with posts on those topics. However, if I think I am not qualified to post on a specific topic, i will certainly respond back to you by email saying so :)

You can reach me at aravind.posral@gmail.com

Hope to hear from you soon!

- Aravind Kamath Posral

The Boring Non-Core-DBA-Tasks in a project

To begin with, this is NOT a typical appsdbatechstuff blog which would talk about issues and solutions. I thought of penning this down as we have just completed a massive project where we migrated a two node 9i RAC/ 11.5.9 Oracle apps instance to 4 Node 11g RAC / R12.1.1 / OATM / 11g DataGuard on Linux.

During the course of the project, one of the sentiments which used to come to the fore on many occasions, was the dislike towards performing non - dba - technical activities. Now, the ambit of dba-technical activities would be the tasks which are part of the actual technical migration/upgrade and the non-core-dba activities would include anything to the tune of documentation, implementing standards, purging old crons, fixing perl issues, making custom old scripts work with new Oracle tech stack version, post-go live stabilization tasks, coordination with other teams on custom solutions, backup automation and so on....hope you get a sense of it..

From my experience of working in projects so far, i would say the actual upgrade or migration tasks which we perform constitutes only 30-40% of the actual project effort. The rest is environment specific stuff. Pretty much every environment specific custom solution has a business purpose behind it. That could be reducing the MTTR, preventing incidents, improving performance and thereby end user experience etc.

Hence, it is of paramount importance that at no point in time must we forget that we are executing the projects for achieving business results and not for the love of upgrade or migration. This implicitly means that our objective must be to make the database manageable from an operational perspective. An example could be setting up crons to alert the DBAs to an abnormal situation where more number of sessions are waiting on a event beyond the threshold, which may help DBAs act proactively and prevent an business outage.

One other aspect is that many of these non-core-dba-technical activities are aimed at making the environment stable and manageable. Further, as far as documentation is concerned, this makes sure that we dont carry mistakes from one iteration to another to finally production and make sure that all known issues are taken care of before we release the production.


I have only touched upon the need to work on the non-core-dba-technical tasks superficially in this blog.

However, one thing is for certain: I believe it needs to be treated with the same diligence and priority as core-dba-technical tasks because we should not forget the business objectives of the project.

-Aravind Kamath Posral

How to change the number of OACORE JVMs in R12 with minimal disruption

Goal: To change the number of OACORE JVMs from 1 to 5 in R12 with almost no outage.

Highlights:

- No need to run autoconfig
- Minimal outage required (probaly < 1 min)
- Preserve the changes even if autoconfig is run at a later time.

Steps
1. On the Apache node, cd $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
2. Backup the opmn.xml
3. Edit the following section of opmn.xml


< /process-type>
< process-type id="oacore" module-id="OC4J" status="enabled" working-dir="$ORACLE_HOME/j2ee/home">
< module-data>
< category id="start-parameters">
< data id="java-options" value="-server -verbose:gc –Xmx1024M –Xms1024M -XX:MaxPermSize=160M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Dcom.sun.management.jmxremote -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/ora/10.1.3/j2ee/oacore/config/jazn.xml">
< data id="java-bin" value="/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/admin/scripts/java.sh">
< data id="oc4j-options" value="-out /apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/logs/ora/10.1.3/opmn/oacorestd.out -err /apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/logs/ora/10.1.3/opmn/oacorestd.err">
< /category>
< category id="stop-parameters">
< data id="java-options" value="-server -verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=160M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false">
< /category>
< category id="security-parameters">
< data id="wallet-file" value="file:/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/certs/Apache">
< /category>
< /module-data>
< start retry="2" timeout="600">
< stop timeout="120">
< restart retry="2" timeout="720">
< port id="default-web-site" protocol="ajp" range="21585-21589">
< port id="rmi" range="20085-20089">
< port id="jms" range="23085-23089">
< process-set id="default_group" numprocs="1">
< /process-type>


In the above block, change the tag from < process-set id="default_group" numprocs="1">to < process-set id="default_group" numprocs="5">.

4. In the context file, change the following block from < oacore_nprocs oa_var="s_oacore_nprocs">1 to < oacore_nprocs oa_var="s_oacore_nprocs">5

5. Do NOT run autoconfig
6. adopmnctl.sh reload
7. Validate that 5 JVMs have been indeed started by adopmnctl.sh status

Risks and things to watch out for:

1. Make sure the port range for rmi and jms is 5.
2. Make sure those ports are not in use by using netstat command.
3. If the port range is not specifying 5 ports, assign 5 ports as per the port pool value.

For eg, if your port pool is 17, then rmi range will be 20085 - 20089 and jms range will be 23085- 23089.

4. Make similar change in context file so that your changes are not
5. You have a choice of customizing your template rather than directly changing your xml file as well.

- Aravind Kamath Posral