Monday, June 25, 2012

ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 66758. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 84 ORA-06512: at line 1

Changed Workflow Administrator role from SYSADMIN to a user ABC. After this change the users started getting the following error in workflow notification emails.

ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 66758. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 84 ORA-06512: at line 1

  • To resolve the issue I changed the Workflow Administrator role from ABC to 'Workflow Administrator Web (New)' responsibility.
  • Assingned this responsibility to SYSADMIN and user ABC.
  • Then run the request “Synchronize WF LOCAL tables” with “ALL” in parameter.

FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oXXXXXXXX.tmp in the directory, /usr/tmp


A TEST instance was already up and running and I created another production clone on the same server/machine. Once the services of both the instances  

The concurrent programs were completing in error and when checked the log files, the following error message was there:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o0031866.tmp in the directory, /usr/tmp.

You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417

When investigated, I found that both the instnaces were creating .tmp files in /usr/tmp directory with the same name. This error was being thrown when one instance was trying to create .tmp file and a file with the same name was already created by the other instance.

  • To resolve the issue I shutdown both the apps and db services of one instance.
  • Created a directory 'temp' in '/usr/tmp' and changed the ownership of this dir to user owner of this instance 
  • Logon to database as sysdba
  • Create pfile from spfile
  • modified UTL_FILE_DIR parameter's first entry from '/usr/tmp' to '/usr/tmp/temp'
  • Created spfile from pfile
  • Brought up the db and listener
  • Now modified the $APPLPTMP variable in TEST_oratest.xml file from '/usr/tmp' to '/usr/tmp/temp'
  • Run the autoconfig on apps tier/node
  • Brought up the apps services
  • Retested the issue and it was resolved 

Sunday, May 20, 2012

11gR1 Data Guard: Logs apply on standby is stoped due to currept or incomplete log

Check the logs apply status
Login to standby database (Open read only if not already opened)

SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;


Last Seq RecievedLast Seq Applied
64246400

Check the alert_<SID>.log and note that the apply process is stuck while trying to apply log seq. 6441.

Copy log seq. 6441 from primary database to standby and manually try to register the log.

SQL> alter database register physical logfile '<fullpath/filename>';

OR

SQL> alter database register or replace physical logfile '<fullpath/filename>';

If this does not resolve the issue then do the following:

SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover automatic standby database;

Check the alert_<SID>.log and note that the logs apply has been started.
Wait for the recovery to finish and then cancel.

SQL> shutdown immediate
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect from session;

R12 Web ADI: Make it work with MS Office 2010

In Excel 2010
Go to File > Options. Following screen will open
Select Trust center




Click on Trust center settings
Select ActiveX Settings and make sure setting match following screen
Then click on Macro Settings and make sure settings match as following
Click on Protected View and make sure setting match as following
Click on External Contents and make sure setting match as following
Press Ok. Then again Press OK.

Now in Internet explorer
Go to Tools > Internet Options
Click on Security
Select Internet and then on Custom Level
Scroll Down to Download portion and match settings with following
Then Scroll down to miscellaneous section and match setting with following
Then Scroll Down to Scripting section and match setting with following
Press OK. Then again press OK.

ORA-02303: cannot drop or replace a type with type or table dependents

Try to modify a type and CREATE OR REPLACE TYPE gives the error:

ORA-02303: cannot drop or replace a type with type or table dependents

If you need to modify a type you need to drop all the dependent (child) objects first or use FORCE option (Not recommended)

Dependent objects may be found using:

BEGIN
 DBMS_UTILITY.get_dependency (  type => 'TYPE',
                                                         schema => 'APPS',
                                                            name => 'COST_OBJ_TYPE1');
END;
 

Check how many archive logs have been recieved and applied by standby database

Login to standby database (Open read only if not already opned)

SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;

Last Seq Recieved Last Seq Applied
6424 6424

 

Delete N number of lines from a file on Linux

$ sed -i 'starting_line_no, end_line_no d' file_name

$ sed -i '1,5d' test.txt

The above command will delete first 5 lines from 'test.txt' file.