Friday 30 March 2012

Oracle: Cannot open or remove a file warning when applying patch

I faced with strange warning yesterday when I tried to upgrade Oracle application server from 10.1.2.0.2 to 10.1.2.2. Oracle Universal Installer, all of sudden stopped and showed me the following error :

Error in writing to file $ORACLE_HOME/lib32/libnjssl10.so
Cannot open or remove a file containing a running program


Patch was running on AIX platform and as Oracle recommendation before starting patchset, I ran /usr/sbin/slibclean as root which is supposed to clean box from program which got file descriptor.
This experience showed me that slibclean is not always perfect and there may be some odd situations which other remedies are required.
Here is what I did to get over the situation.
- cp $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
- cp $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Copy did not fix the situation

- mv $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
-mv $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Move fixed the situation

As the result :
- On AIX , first try /usr/sbin/slibclean as root.
- On all platforms, if file exists try to move it to new file and then move it back to original
.

Thursday 29 March 2012

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options


mount -o hard,rw,noac,rsize=32768,wsize=32768,proto=tcp,vers=3 nim_server1:/mksysb /mksysb
instead of
mount nim_server1:/mksysb /mksysb 

Oracle: Connect /as sysdba or Startup Fails With ORA-09925 When Instance Is Down [ID 392643.1]



Connect /as sysdba or Startup Fails With ORA-09925 When Instance Is Down [ID 392643.1]

  Modified 07-MAR-2012     Type PROBLEM     Status PUBLISHED  

In this Document
  Symptoms
  Cause
  Solution
  References




Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.7 - Release: 8.1.7 to 11.1
Information in this document applies to any platform.
Checked for relevance on 05-Oct-2010

Symptoms

When the database instance is down, trying to connect or startup the database fails with:

ERROR:
ORA-09925: Unable to create audit trail file
SVR4 Error: 13: Permission denied
Additional information: 9925


Cause

On Unix systems, the investigation should be done using the OS tracing utility, ie: truss (AIX, Solaris), strace (Linux) or tusc (HP-UX).

truss -aefo sqlplus.trc sqlplus "/ as sysdba"

shows that the problem is caused by a missing directory, insufficient permissions or not enough space on that particular directory:

open("/opt/oracle/admin/test102/adump/ora_31704.aud", O_RDWR|O_CREAT|O_APPEND|O_LARGEFILE, 0660) = -1 EACCES (Permission denied)

From 10gR2, the $ORACLE_BASE/admin//adump directory is the new default directory for audit files (instead of previous releases location, $ORACLE_HOME/rdbms/audit).

Solution

1. Starting with 10gR2, check if the
$ORACLE_BASE/admin/$ORACLE_SID/adump

directory exists and create it if not.

2. If the directory exists (in previous releases check: $ORACLE_HOME/rdbms/audit), make sure that permissions on it are set to 775.

3. If the directory exists and permissions are correctly set, make sure that there is enough space on the file system to accommodate the newly created audit files.

Starting with 10gR2: Note that when the database instance is down and the first connect and the startup command is run, the audit_file_dest parameter is not yet initialized, hence unknown to the shadow process, as such the audit files for these commands can only be written to the default destination. Writing these files is a mandatory requirement for NCSC C2 security evaluation criteria and therefore this behavior cannot be turned off.

4. The above assumes that ORACLE_BASE is set, as recommended by the documentation. If ORACLE_BASE is not set, then the default audit location becomes: $ORACLE_HOME/admin//adump. This is important to mention especially on RAC, where the environment variables are set in the OCR and should be updated, as documented, using srvctl, eg:
srvctl setenv database -d -t ORACLE_BASE=

5. A very rare scenario that was found is when ORACLE_PATH was set and the Oracle software transparently appended it to the default value: $ORACLE_BASE/admin/$ORACLE_SID/adump. ORACLE_PATH has to be unset to solve the problem. This is being investigated in:

Bug 13584524 Bug 13584524 - strange audit destination if oracle_path is set: ora-09925

References

NOTE:103964.1 - How to Audit Connect AS SYSDBA Using Oracle Server
BUG:13584524 - STRANGE AUDIT DESTINATION IF ORACLE_PATH IS SET: ORA-09925

Tuesday 27 March 2012

Sql server: change the object owner

to change owner of table xx_table to be dbo
sp_changeobjectowner 'xx_table', 'dbo'

--- Dynamic sql for multiple tables
SELECT 'sp_changeobjectowner ''' + TABLE_name +''',''mantech'''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'

Monday 26 March 2012

Oracle Apps 11i: Internet explorer has closed this webpage to help protect your computer

Many users face issue running Oracle forms on Microsoft Internet Explorer 8 (IE8) which causes the page to redirect to following url
res://ieframe.dll/acr_depnx_error.htm#,http://:/forms/frmservlet?config=
It displays following error.
Internet explorer has closed this webpage to help protect your computer
A malfunctioning or malicious add-on has caused Internet Explorer to close this webpage.


Solution:
Goto Internet Explorer -> Tools -> Internet Options -> Advanced -> Scroll down to Security -> Uncheck “Enable memory protection to help mitigate online attacks*”
Close all browser windows and restart the browser. The issue should have been fixed :-)

Edit: Alternatively you can use either of following 2 alternate solutions.
Alternate 2:
Open registry using start->Run->regedit->ok
Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\Main
On the right hand side you will see a DWORD key called DEPOff. IF this is set to value 0, just change it to 1 and close registry editor.

Restart Internet Explorer and check if the problem has been fixed or not.
Alternate 3:
Right click on My Computer->Properties (or start->Run->sysdm.cpl->ok)
Click on “Advanced” Tab. Click on “Settings” button next to Performance.
Click on last tab “Data Execution Prevention
Select Second option and then select “Internet Explorer” from the bottom pan.
(If “Internet Explorer” is not already present there then just click on “Add” and then select “c:\Program Files\Internet Explorer\iexplore.exe”)

Click OK and restart internet explorer. The problem should have been fixed.


If the above not working for you download the jvm.dll and replace with the one under:
C:\Program Files\Oracle\JInitiator 1.3.1.21\bin\hotspot
 

Oracle Apps: change DISPLAY and custom top environment after clone

change the custom top environment path after clone in file
$APPL_TOP/admin/adovars.env

In our case:
/fmstest/upgr/upgrappl/admin/adovars.env


For display:
DISPLAY=bahix06:1.0
export DISPLAY

Tuesday 20 March 2012

Oracle - Get IP address & host with

The utl_inaddr procedure:

SET serveroutput on
BEGIN
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
END;
/

The sys_context procedure

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

Discoverer Desktop/Administration Edition Invalid username/password; Logon Denied

Unable to connet to: user@database
Failed to connect to database Ora-01017: Invalid username/password; Logon Denied


Discoverer Desktop/Administration Edition

Depending on your discoverer version, follow the appropriate steps in this table:

Discoverer Version Step Actions
4.1 1 Ensure that required patches have been applied for Discoverer Desktop/Administrator.
The latest patch information is listed in Note 139516.1, Discoverer 4i with Oracle Applications 11i under Section 5: Installation Tasks.

2 Identify the Oracle Home directory on the Windows PC. You can search for dis4usr.exe.
For example: The dis4usr.exe may be in a directory like C:\Orant\bin.
Thus the Oracle Home is C:\Orant.

3 Create a folder named "secure" in the ORACLE_HOME directory identified in step 2 (i.e. C:\Orant\Secure).

4 Download the .dbc files from the Oracle Applications Database server and place them in the "secure" directory you created (above).
The .dbc files are located in the $FND_TOP/secure directory.
The default naming for the .dbc file is described in Note 353605.1, DBC File Naming Conventions for Discoverer Connections to Oracle Applications 11i and 12i.

5 Discoverer requires Windows Environment variables to dynamically retrieve the location of the .dbc file on the PC.

6 Open Windows Control Panel and double click on the System icon.

7 Select the Environment tab and create two new System Variables

FND_SECURE =\secure
FND_TOP=

NOTE: The Oracle Home is the one identified in step 2.

8 Restart the PC and you should be able to connect to Discoverer in an Oracle Applications mode EUL in secure mode.



10gR1 (9.0.4) 1 Ensure that you have applied the most recent certified patch as listed in  Note 257798.1, Discoverer 10g (9.0.4) with Oracle Applications 11i , Section 5 Installation Tasks.

2 Identify the Oracle Home for Discoverer by searching for the dis51usr.exe file.
For example:
You could find dis51usr.exe under the directory D:\IDS 904\bin.
Thus the Oracle Home is D:\IDS 904

3 Now follow step 3 through 8 as described above under Discoverer 4.1 section.



10gR2 (10.1.2)
and 11g
(11.1.1)
1 Ensure that you are using the Oracle Business Intelligence Tools 10.1.2 or later.

See Applicable document based on your Oracle Applications and Discoverer versions:

 Note 313418.1, Using Discoverer 10.1.2 with Oracle E-Business Suite 11i, Section 5: Installation Tasks for latest patch recommendations

 Note 373634.1, Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12,  Section 5: Installation Tasks for latest patch recommendations

Note 1073963.1, Using Discoverer 11.1.1 with Oracle E-Business Suite Release 11i, Section 5: Installation Tasks

Note 1074326.1, Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12, Section 5: Installation Tasks



2 Identify the Oracle Home for Discoverer.
You can search for dis51usr.exe.

For example: You could find dis51usr.exe under the directory D:\oracle\BIToolsHome_1\bin.
Thus the Oracle Home is D:\oracle\BIToolsHome_1.

3 Now follow step 3 through 8 as described above under Discoverer 4.1 section.

Monday 19 March 2012

Installing Discoverer Administration Edition on Windows PC


Requirement: A Windows PC and establishing connection to the EBusiness Suite Edition.

Software : Download Discoverer 10.1.2 Administration Edition from the below site http://www.oracle.com/technology/software/products/ids/index.html

Installation Steps:

1. Run the Setup and Install the Desktop Edition.

2. Apply the latest certified Discoverer Administration Edition Patch

3. Set Windows Registry Settings as per Note 373634.1

4. Create a Secure Directory in the Discoverer Home. mkdir %ORACLE_HOME%/secure ; eg:ORACLE_HOME=D:\Discoverer10g\

5. Copy the DBC File from the EBusiness Suite Instance and place it in the secure directory.

6. Create a Windows System Variable FND_SECURE and with value D:\Discoverer10g\secure

7. Update %ORACLE_HOME%/network/admin/tnsnames.ora and include the EBusiness Suite Instance. The database name must match the two_task entry in the dbc file.

8. Make sure that the TNSPING Works.

Thursday 15 March 2012

dataguard gap: How to Resolve Error in Remote Archiving

REFERENCE : How to Resolve Error in Remote Archiving [ID 799353.1]

Problem :

ORA-12541: TNS:no listener
LNS: Failed to archive log 5 thread 2 sequence 1101 (12541)
Sun May 22 13:05:55 2011
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Solution :
Step 1. Check the status of remote archive destination on primary database
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR                 ERROR     ORA-12541: TNS:no listener

Step 2. Defer and enable the remote archive destination:
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Step 3. Perform 2 log switches on the Primary/Source database:
SQL>ALTER SYSTEM SWITCH LOGFILE;
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 4. Check the status of remote archive destination again:
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR                 ERROR     ORA-12541: TNS:no listener


If the status of remote archive destination is error, then check the error code under Error.
Resolve the error in the archiving and perform step 1 to 4 again.

If there is no error in the remote archiving than status of remote archive destination will be "VALID"
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR             VALID

Step 5. Check whether redo logs are getting received on the standby using:
FROM STANDBY
SQL> SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1         1087
2         1101

Perform 2 log switches on primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
Check whether SEQUENCE# changes
SQL> SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1         1087
2         1103
Remote archiving is working fine if the sequence# is moving.
Proceed to step 6 if sequence# is not moving:

Step 6. Check the value of log_archive_max_processes on primary:
SQL> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES

NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
log_archive_max_processes       integer     2
Increase the value of log_archive_max_processes to 10 (From 10.2 you can increase value up to 30).
It is highly recommended to increase this value to avoid any issues in case of a GAP.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
Repeat step 5 and check whether redo is getting received on the standby.
Proceed to step 7 if issue does not get resolved.

Step 7. Spawn new ARC processes for primary database:  This is required because ARC processes get stuck with old error messages

a. Check the OS PID of the ARC processes on the primary database using:
For Unix

$ ps -ef|grep -i arc| grep $ORACLE_SID

For windows

You can get the os pid from alertlog or from this query output

select p.pid,p.spid,p.serial#,bgp.name from v$process p, v$bgprocess bgp
where p.addr=bgp.paddr and bgp.name like 'ARC%';

for Unix

$kill -9 ...


Either kill the arc processes or restart the instance.

Number of Visitors