Unable to open Oracle Report Manager outputs using MS Excel

Issue:

The user comes with a complain that he is not able to view report output in Excel format, the below error appears on the User's browser:

 

Cause:

IE security rules prevent the creation of an object using ActiveX Component.

Solution:

1- Add your application URL to the trusted sites (Internet Explorer > Tools > Internet Options > Security tab > click on the trusted sites sign)

Click on Sites button:

Click "Add" then close.

2- Change the security options for the trusted sites by clicking on "Custom Level" button then change the value of "Initialize and script ActiveX controls not marked as safe for scripting" to "Enable":

3- Change the value of "Enable XSS filter" to "Disable":

4- Open MS Excel and do the following:

File > Options > Trust Center > Trust Center Settings > Macro Settings

Ensure that "Trust access to the VBA project object model" is checked.

How to add a link to Oracle applications 12.1 home page using personalization

To start with this activity you need to change profile option “Personalize Self-Service Defn” for the desired user to Yes.

Login to the application using the user who have the personalization enabled.

Click on "Personalize Page" link.

Change the Personalization Level to "Function: Applications Configurable Home Page" then click Go.

Select "Content" tab as shown in the below snap:

Select the region that you want to include the link inside it, the red arrow shows the region I have selected to put my Link:

The next step we will define the link details:

Item Style: Link

ID: link1

Destination URI: http://erpmigrate02.rsteel.com:8010/OA_HTML/HR_Policy.pdf

Text: *** HR Policy Manual ***

Click Apply button.

The newly added link will be placed by default at the bottom, we need to use the "reorder" link to put it in the requested place:

Click Apply button.

 At the end it will look like:

On the OS level, login as the MT owner, in my case "apfintest" and put the .pdf file in the correct location:

[apfintest@erpmigrate02 ~]$ cd $OA_HTML
[apfintest@erpmigrate02 html]$ pwd
/Test/FINTEST/apps/apps_st/comn/webapps/oacore/html
[apfintest@erpmigrate02 html]$ ls -l HR_Policy.pdf
-rw-r--r-- 1 apfintest oinstall 7096969 Jan 29  2013 HR_Policy.pdf
[apfintest@erpmigrate02 html]$

Click on the "home" link again, and you will see your link.

How to dorp/recreate your Undo Tablespaces

> It is better to put the database in the mount mode while doing these activities.
> If the database is up and running you may face "ORA-30013: undo tablespace '%s' is currently in use." error while dropping the old Undo tablespace, this error indicates that you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transactions to be committed or rolled back. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

1- Collect the required data about your Undo Tablespaces:

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='UNDO';

TABLESPACE_NAME
------------------------------
APPS_UNDOTS1

 SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS1
SQL>

SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

FILE_NAME                 FILE_ID TABLESPACE_NAME            BYTES      BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES    MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/PROD/db/apps_st/data/undo01.dbf    379 APPS_UNDOTS1           4194304000      512000 AVAILABLE        379 YES 3.4360E+10      4194302     12800 4194238464      511992 ONLINE
/oracle/PROD/db/apps_st/data/undo02.dbf     19 APPS_UNDOTS1           4404019200      537600 AVAILABLE         19 YES 3.4360E+10      4194302     12800 4402970624      537472 ONLINE

SQL>

- Total size of the Undo Tablespace:

SQL> SELECT sum(bytes)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

SUM(BYTES)/1024/1024/1024
-------------------------
        8.0078125

SQL> !ls -ltrh /oracle/PROD/db/apps_st/data/undo0*
-rw-r----- 1 orstage dba 4.2G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo02.dbf
-rw-r----- 1 orstage dba 4.0G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo01.dbf

SQL>

2- Create a new undo tablespace:

SQL> create undo tablespace APPS_UNDOTS2 datafile '/oracle/PROD/db/apps_st/data/undoNew.dbf' size 1000M;

3- Set the new tablespace as the undo tablespace to be used:

SQL> alter system set undo_tablespace=APPS_UNDOTS2 scope=spfile;

SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS2
SQL>   

4- Drop the old undo tablespace:

    SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;

5- Recreate the old undo tablespace with a smaller size:

    SQL> create undo tablespace APPS_UNDOTS1 datafile '/oracle/PROD/db/apps_st/data/undo01.dbf' size 10240M '/oracle/PROD/db/apps_st/data/undo02.dbf' size 10240M;

6- Set the old tablespace as the undo tablespace to be used:

    SQL> alter system set undo_tablespace=APPS_UNDOTS1 scope=spfile;

 7- Drop the temp undo tablespace:

    SQL> drop tablespace APPS_UNDOTS2 including contents and datafiles;

Regards,
Mohamed

Troubleshooting Concurrent Request ORA-20100 error

When running "GMFRLAYR module: GMF Recreate Batch / Period Layers" Concurrent request we got the below error:

ORACLE error 20100 in FDPSTP Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o1152922.tmp in the directory, /usr/tmp. You will find more information in the request log. ORA-06512: at "APPS.FND_FILE", line 417 

- All configurations seems to be OK:

As the DB owner:

SQL> sho parameter utl_file_dir

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string     /usr/tmp, /usr/tmp, /Test/FINT
                         EST/db/tech_st/11.1.0/appsutil
                         /outbound/FINTEST_erpmigrate02
                         , /usr/tmp
SQL> 

As the MT owner:

[apfintest@erpmigrate02 ~]$ echo $APPLPTMP
/usr/tmp
[apfintest@erpmigrate02 ~]$ ls -l /usr/|grep tmp
lrwxrwxrwx   1 root root     10 Nov  4 13:01 tmp -> ../var/tmp
[apfintest@erpmigrate02 ~]$ ls -l /var|grep tmp
drwxrwxrwx  3 root root  4096 Nov 13 11:31 tmp
[apfintest@erpmigrate02 ~]$ 

- We have enough space for new logs:

[apfintest@erpmigrate02 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             254G  5.7G  235G   3% /
/dev/sdb2             943G  348G  547G  39% /Oracle
/dev/sdb1             946G  348G  551G  39% /Test
/dev/sda1             289M   32M  243M  12% /boot
tmpfs                 7.9G   17M  7.9G   1% /dev/shm
[apfintest@erpmigrate02 ~]$

- Trying to use the package used by the application to write the file:

SQL> sho user
USER is "APPS"
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'testing /usr/tmp folder');

PL/SQL procedure successfully completed.

SQL> !ls -ltr|tail
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 o1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 l1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152916.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152916.tmp
-rw-r--r-- 1 orfintest oinstall      0 Nov 13 11:41 o1154916.tmp
-rw-r--r-- 1 orfintest oinstall     24 Nov 13 11:41 l1154916.tmp

SQL> !cat l1154916.tmp
testing /usr/tmp folder

SQL>

- At last I found that the /usr/tmp folder is shared between two instances! and the same file name got generated by both instances!

- At this point you have two options:

a)

1. Stop all MT services.
2. Change $APPLPTMP value to another directory "ensure it is the first value listed by utl_file_dir DB parameter"

3. Run autoconfig on the MT nodes

4. Restart all services
5. Re-test the failed process

b)

As a quick solution "should not be applied unless you really need an urgent solution"

[root@erpmigrate02 tmp]# chmod 777 /usr/tmp/*
[root@erpmigrate02 tmp]# 

re-run your CR again...

Regards,

Mohamed