Friday, October 29, 2010

MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed.

Troubleshooting Details

If a PL/SQL Concurrent Program can't write to an external file, you will receive an error message similar to:

MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 378
ORA-06512: at "APPS.FND_FILE", line 473
ORA-06512: at "APPS.AP_TRIAL_BALANCE_PKG", line 192
REP-1419: 'beforereport': PL/SQL program aborted.

NOTE: Applications also produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. The APPLPTMP directory must be the same directory as specified by the utl_file_dir parameter in your database initialization file.
.
Rapid Install sets both APPLPTMP and the utl_file_dir parameter to the same default directory. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. During an upgrade with AutoUpgrade, you must provide the utl_file_dir parameter value for the APPLPTMP environment setting.


To isolate where the problem is, verify the following:

1) Make sure that the name of the file is valid (the file name should not include characters like "^")

2) Make sure that APPLPTMP is set to a valid directory and that BOTH the applmgr user and the database user have read and write permissions on that directory (normally, it can be set to the same directory as APPLTMP)

3) Make sure that the file does not exit on the directory pointed by APPLPTMP

4) Make sure the directory pointed by APPLPTMP is the first entry on the utl_file_dir. Also, verify that all the entries on the utl_file_dir are valid and that the applmgr has read/write permissions.

If using an spfile, verify the proper syntax to set utl_file_dir:

Eg:  ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;

5) If still having problems, check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:

    SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

This should dump a file on APPLPTMP.

If this test works, it would indicate that FND_FILE is ok and the problem is possibly with the Application.

You may want to leave only one entry on utl_file_dir for this test.

6) If still having problems, check if you can write a file using UTL_FILE, which is used by FND_FILE.

Run the PL/SQL below, changing to the first entry on utl_file_dir (you may want to leave just one entry on utl_file_dir for this test).

set serveroutput on
DECLARE
  file_location VARCHAR2(256) := '';
  file_name VARCHAR2(256) := 'utlfile1.lst';
  file_text VARCHAR2(256) := 'THIS IS A TEST';
  file_id UTL_FILE.file_type;
BEGIN
  file_id := UTL_FILE.fopen(file_Location, file_name, 'W');
  UTL_FILE.put_line(file_id, file_text);
  UTL_FILE.fclose(file_id);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH
  THEN dbms_output.put_line('Invalid path ' || SQLERRM);
    WHEN OTHERS
  THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END;
/

This program should dump a file on the requested directory. If the test fails, the problem is probably on the Database side.

If it works, the problem is probably on FND_FILE. In this scenario, check the versions of AFCPPIOS.pls and AFCPPIOB.pls.


Source : Troubleshooting ORA-20100 on Concurrent Processing [ID 261693.1] (suppot.oracle.com)

Thursday, August 19, 2010

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
                12.09375
               
2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

    Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
    Change undo_tablespace=UNDOTBS2
    Change undo_management=MANUAL

    Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
   
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.   
5. Startup the database
6. Confirm the new tablespace is in use:

    SQL> show parameter undo_tablespace

    NAME                 TYPE              VALUE
    -------------        -----------       ------------
    undo_tablespace      string            UNDOTBS2
   
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
    SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

    OWNER         SEGMENT_NAME         TABLESPACE_NAME         STATUS
    ------              --------------------------- ------------------------------ -----------
    PUBLIC         _SYSSMU3$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU2$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU19$                     UNDOTBS2                         OFFLINE

    ....etc.

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.

9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

Monday, August 16, 2010

"Error Occurred While Attempting to Establish an Applications File Server Connection"

Error:

" An error occurred while attempting to establish an Applications File Server connection. There may be a network configuration problem, or the TNS listener may not be running on "
Solutions to Check

1. Is this a Multinode Install or a Single Node Install.
- If this is a Multinode Install, you will need access to the Middle Tier and the Backend Tier (Concurrent Processor) 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora files

- If this is a Single Node Install, you need access to the Concurrent Processor 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora file

2. Run a report, is there actually a Report Output and Log file being produced at the Operating System level?
- Check the $APPLCSF/out and the $APPLCSF/log directories for the Request_ID out and log file
- If there are no log and output file being produces there Check the permissions on those directories (APPLMGR should have write)
- If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs

3. Confirm that there are 2 listener processes actively running for the Instance that you cannot view reports on -- 1 from 8.0.6 and from 8.1.6 directory structures
- type the following:
ps -ef | grep tnslsnr
- If there is only 1 listener running from the 8.1.6 directory structure
That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener
- To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env)
c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script:

adalnctl.sh start APPS_SID

(you can confirm the FNDFS Listener name from viewing the 8.0.6 ORACLE_HOME/network/admin/listener.ora file)

4. Check the actual machine name for the Concurrent Processor Server
- type the following:
uname -n

5. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen in the System Administrator Responsibility The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
- If it is the same then continue with next step
- If it is not the same,
Shutdown Concurrent Managers and update the FND table:
a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop
c. Connect to SQL*PLUS apps/apps

d. SQL > select target_node from FND_CONCURRENT_QUEUES;
--------------------
This may return the name of an old machine

e. SQL > update FND_CONCURRENT_QUEUES set target_node='' where target_node='';

f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start

6. In the 8.0.6 TNSNAMES.ora file, look at the FNDFS_ connection string entry.
Is the hostname value the same as is in questions 4?
- The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora
a. 1 with the FNDFS_
b. 1 with the FNDFS_
c. 1 with the FNDFS_
- The one that is the correct entry is FNDFS_ (hostname being what is returned from a "uname -n")
- If this file does not contain the correct Hostname value
Make a backup of it and edit it to change the FNDFS entry.

7. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install.
- At the Operating System Level
a. tnsping FNDFS_
- This should return the Hostname name and Port information identical to the FNDFS entry in the TNSNAMES.ora file

8. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility

CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
- Viewer: Text can Be set to "browser" to view with Netscape or IE

9. This step should be performed in a case where there might have been patches applied and possibly the FNDFS executable was not Relinked, or it may be missing from the file system or corrupted.
- Shutdown the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh stop APPS_SID
- Rename or Move the Current FNDFS executable before relinking
a. $FND_TOP/bin
mv FNDFS FNDFS.bak
b. As APPLMGR account user
adrelink.sh force=y "fnd FNDFS"
- Start the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh start APPS_SID

Metalink Note : ID 117012.1

Thursday, March 11, 2010

Backup verification Statements/Steps

sqlplus "/as sysdba"

startup mount;

prompt The following should return zero rows

select count(*) from v$recover_file;
select count(*) from v$recovery_log;
select count(*) from v$recovery_status;
select count(*) from v$recovery_file_status;
select name,status from v$datafile where (name like '%MISS%' or status not in ('ONLINE', 'SYSTEM'))
/

select count(*) from v$backup where status != 'NOT ACTIVE' ;

col checkpoint_change# format 999999999999999

prompt The following should return one distinct number

Select distinct checkpoint_change# from v$datafile  ;

select distinct to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH24:MI:SS') from v$datafile_header;

prompt This should return "0" and "8192" as output

select distinct fhsta from x$kcvfh;

Oracle Inventory Creationg/Attach/Dettach/Clone/Relink Code tree

1. Point the inventory to correct location

Make sure the file ./etc/oraInst.loc.,  ./var/opt/oracle/oraInst.loc. & $ORACLE_HOME/oraInst.loc. has the following entries.  If not modify the file and save.

inventory_loc=/oracle/product/oraInventory
inst_group=dba

2. Clone the code tree

+ cd $ORACLE_HOME/oui/bin
+ ./runInstaller -silent -clone -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq  ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST ORACLE_HOST_NAME=test-01


!!! Please wait until the command is successful.  Do not interrupt.  !!!
Please check for .cloning is successful. message and will display message to run
.root.sh..  Sometime it may wait for you see this message.  Press .enter. to get
the .command prompt..

Run the following ,
+ cp /oracle/product/10.2.0.4//root.sh /oracle/product/10.2.0.4

+sudo /oracle/product/10.2.0.4//root.sh

Accept the default options for the prompts and location of the files.

3. Detach the code tree from oracle central inventory

+ cd $ORACLE_HOME/oui/bin
+./runInstaller -silent -detachHome -local -noClusterEnabled -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST


4. Attach the code tree to oracle central inventory . This will add correct host to the inventory

+ cd $ORACLE_HOME/oui/bin
+./runInstaller -silent -attachHome -local -noClusterEnabled -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST CLUSTER_NODES=test-01


5. Relink the code tree

find . -type l -exec ls -l {} \; | grep -i [oldsid] | awk '{print "rm -f "$9";ln -s "$11" "$9}' 
 execute the above command to check any softlinks are pointing to the old env. If yes then edit the output of the above command and run them.
Then,
oraenv   ( enter SID at the prompt ).  Check the above variables are set.
Make sure correct values are set for ORACLE_HOME, PATH & LD_LIBRARY_PATH
Make sure .SHLIB_PATH. variable is NOT set.
+ cd $ORACLE_HOME/bin
+ relink all

Tuesday, February 23, 2010

FAQs - How do we know how many users are connected to Oracle Applications.


FAQs - How do we know how many users are connected to Oracle Applications.

1. Enable Profile Option "Sign-On Audit" at "Form" level.
2. Run "Purge Signon Audit" request.
3. Security:Users -> Monitor" option
4  or with the below sql query  mentioned below.

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;

5. Can use this SQL statement to count concurrent_users in Oracle apps:

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)


DMT v/s LMT

Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT)

Introduction:
The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the "extent management clause", that specifies how the extents of the tablespace are managed. This clause uses one of the following parameters:

- DICTIONARY:
Specifies that the tablespace is managed using dictionary tables. This is the default in Oracle8i.

- LOCAL:
Specifies that tablespace is locally managed. This is the default in Oracle9i. Exception for the SYSTEM tablespace

Locally Managed Tablespaces:
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).

When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file.

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.

Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):

1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required

Converting DMT to LMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.

Converting LMT to DMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');
PL/SQL procedure successfully completed.

Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database

have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type "permanent".


Source : dbapool.com

Tuesday, January 26, 2010

ORACLE RAC ON/RAC OFF

Oracle's RAC: ON and OFF

In some cases, you may want to disable the RAC options for testing purposes -- perhaps to run a benchmark or convert the RAC binaries to single instance binaries. In such a case, you can use the following procedure to convert the RAC installation to non-RAC. Disabling and enabling RAC options are available only for UNIX platforms. Windows installations do not support relinking binaries with RAC ON and OFF.

Use the following steps to disable RAC (known as RAC OFF):


1. Log in as the Oracle software owner (which is typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib: cd $ORACLE_HOME/rdbms/lib
4. Run the following make command to relink the Oracle binaries without the RAC option: make -f ins_rdbms.mk rac_off

This normally runs for few minutes and should not pose any errors.
5. . Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC OFF option. You may have to edit the init.ora or SPFILE parameters accordingly. If errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Use the following steps to enable RAC (known as RAC ON):

1. Log in as the Oracle software owner (typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib:
4. cd $ORACLE_HOME/rdbms/lib Run the following make command to relink the Oracle binaries without the RAC option:
make -f ins_rdbms.mk rac_on

This normally runs for a few minutes and should not pose any errors.
5. Now relink the Oracle binaries:
make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC ON option. You may need to edit the init.ora or SPFILE parameters accordingly. If any errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...