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