Archive for April, 2008

CLUVFY utility will fail if ssh banner is enabled

Posted on April 30, 2008. Filed under: RAC | Tags: , , , , , |

The cluster verification utility will fail in User Equivalence check between RAC nodes, if ssh banner is enabled on the boxes. According to Oracle (Note ID: 4692561.8 – Bug ID: 4692561) this issue is fixed in 10.2.0.4 and 11.1.0.6.

Whereas in our case, we did download the latest cluvfy utility (11g) but we still faced this issue.

11g cluvfy utility can be run against 10g databases, by adding the option -r 10gR1 or 10gR2.

Sample cluster pre-install verification command

runcluvfy.sh stage -pre crsinst -n node1,node2 -r 10gR2 -verbose
— No space between node1, and node2 (node1,node2 holds good – node1, node2 doesnt)

Before running runcluvfy.sh make sure you had set CV_HOME and CV_JDKHOME.

Workaround

Disable the ssh login banner and re-run the cluvfy utility.

cheers,
Ram.

Read Full Post | Make a Comment ( None so far )

How to send email from 10g Oracle Database (UTL_MAIL)

Posted on April 24, 2008. Filed under: Configuration | Tags: , , , , , |

Heres a simple solution to send out emails from 10g Database sql prompt.

This solution will be really helpful if the OS utility (mailx, sendmail) is restricted for end users.

Steps to enable Mailing from Database

1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port

If instance had been started with spfile

eg: alter system set smtp_out_server = ‘172.25.90.165:25’ scope=both;

If instance had been started with pfile
alter system set smtp_out_server = ‘172.25.90.165:25’;
Also make below entry in your initSID.ora

smtp_out_server = ‘172.25.90.165:25’

Thats It, your database is configured to send emails ….

How to send an email

1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
3. Check the inbox of the email id, to verify the email receipt.

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

eg: grant execute on utl_mail to apps;

Happy Mailing !!!

cheers,
OraclePitStop.

Read Full Post | Make a Comment ( 14 so far )

Installing / Configuring APEX (Application Express / HTMLDB) in 10gR2 on Windows XP

Posted on April 16, 2008. Filed under: APEX / HTMLDB, Installation | Tags: , , , , , |

Apex 3.1 works with all versions above 9203, (including Oracle 11g). In this article the steps are defined for Installing apex_3.1 on 10gR2 (10.2.0.1)

Step 1: Download 10gR2 software (http://www.oracle.com/technology/software/products/database/index.html)
Step 2: Download 10gR2 Companion CD (for HTTP Server) (http://www.oracle.com/technology/software/products/database/index.html)
Step 3: Download apex_3.1 from http://apex.oracle.com or http://www.oracle.com/technology/products/database/application_express/download.html
Step 4: Install 10gR2 software and create a general database as part of installation
Step 5: Install 10gR2 companion CD – you will have two options (HTMLDB + HTTP Server or only HTTP Server) (I selected HTMLDB + HTTP Server, not a must though, since we will be installing apex_3.1)
Step 6: Stop both oracle DB and Apache server just installed.
Step 7: Start the DB and let apache be down.
Step 8: Unzip apex_3.1.zip (hereinafter referred as APEX_LOC)
Step 9: Pre-install requirements for APEX.
1. DB Requirement – Should be greater than 9203.
2. JVM Requirement – Need to be installed separetly if DB is less than 10gR1
3. Shared Pool Size of the database – ignore if sga_target is used, else it has to be atleast 100M;
4. HTTP Server (with mod_plsql) Requirements – Oracle9iR2 (9.2) or later / Oracle9iAS release 1 (1.0.2.2) or later / Oracle Database 10g Companion CD release 1 or 2 / Oracle Database 11g release 1
5. Space Requirement
For APEX Software Files – 450MB
For APEX objects (tablespace) – 125MB
SYSTEM Tablesapce – 85MB
6. Oracle XML DB Requirement – XML DB should be installed in the database
7. Oracle Text Requirement – for Searchable online help in APEX
8. PL/SQL Web toolkit – required Version 10.1.2.0.6 (10gR2 brings in 10.1.2.0)

In our approach, (10gR2 DB + 10gR2 Companion CD 1) we had satisfied 1,2,3,4 & 6 requirements. Perform below steps to satisfy 5, 7 & 8.

For 5: We can create a separate tablespace for APEX objects or we can use SYSAUX tables. Add appropriate datafiles to the tablespaces to meet the required free space.
For 7: Connect to db as ctxsys user and execute @ORACLE_HOME\ctx\admin\defaults\drdefus.sql (substitute ORACLE_HOME with actual path)
For 8: Go to the location where you had extracted apex_3.1 zip (APEX_LOC). Connect to db as sys user and execute owainst.sql (under apex\owa directory)
Execute “select owa_util.get_version from dual;” to verify the pl/sql web toolkit version.

Step 10: Install APEX 3.1
– Goto the location where you had extracted apex_3.1 zip. change location to apex directory under APEX_LOC.
– Connect to database as sys user and execute below command
– @apexins TS1 TS2 TEMP_TS /i/

TS1 => SYSAUX or (tablespace created for APEX)
TS2 => SYSAUX or (tablespace created for APEX)
TEMP_TS => Temporary tablesapce
/i/ => Location to store APEX Images (no need to mention any path, just leave it as /i/)
eg:(@apexins APEX_DATA APEX_DATA TEMP /i/)

Installation logfile will be located under APEX_LOC as installYYYY-MM-DD_HH24-MI-SS.log.

Step 11: Change Password for ADMIN Account
– Change directory to apex
– Connect to DB as sys user and execute
– @apxchpwd

(ADMIN user is the super user account simillar to SYS for DB)

Step 12: Configure HTTP Server
Step 12.1: Unlock APEX_PUBLIC_USER Account & change password

– Connect to db as sys user
– alter user apex_public_user account unlock;
– alter user apex_public_user identified by NEW_PASSWORD;

Step 12.2: Copy APEX Images
– Open APACHE_ORACLE_HOME/Apache/modplsql/conf/marvel.con to identify default location set for images (/i/) (Hereinafter referred as IMAGE_LOC)
– Goto the location identified above (eg.C:/oracle/product/as/1020/htmldb/images/)
– Rename the images directory to images_old
– Copy APEX_LOC/apex/images to C:/oracle/product/as/1020/htmldb/

Step 12.3: Adding DAD entry to connect to APEX
– Open APACHE_ORACLE_HOME/Apache/modplsql/dad.conf
– Past below entry

Alias /i/ “[ Put the IMAGE_LOC Value] ”
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString hostname:port:SID
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword APEX_PASSWORD
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

Make sure these values are properly set before saving this file (marvel.conf)

1. IMAGE_LOC Value
2. hostname:port:SID => eg: localhost:1521:ORCL
3. APEX_PASSWORD => eg: password_you_had_selected_on_step_12.1

IF all the above values are set, save the file as marvel.conf

Step 13: Start HTTP Server (Start -> Programs -> Oracle)

Step 14: Access the admin page of APEX

http://hostname:port/pls/apex/apex_admin

Username: ADMIN
Password: PASSWORD_SELECTED_WHILE_RUNNING_apxchpwd

 Sample Screen: https://oraclepitstop.files.wordpress.com/2008/04/apex_admin_screen1.jpeg

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 22 so far )

Snlinaddrlocalhost Error while connecting to 10gR2 Database on Windows XP

Posted on April 16, 2008. Filed under: TroubleShooting | Tags: , , , |

I encountered this strange error on one of my testing database when i tried to connect to the database using sqlplus.exe (command line) and not through sqlplusw.exe.

Error Message Popup “Procedure Entry Point Snlinaddrlocalhost Could Not Be Located In The Oranl10.Dll”

Environment Details

10gR2 DB Oracle Home : C:\oracle\product\1020
10gAS Oracle Home: C:\product\as\1020

To troubleshoot this further i opened windows explorer and launched sqlplus.exe from the ORACLE_HOME/bin. (sqlplus version -> 10.2.0.1) This time i didnt get the error.

I verified all the necessary environemnt variables (under User Variables for the logged in user) viz., ORACLE_SID, ORACLE_HOME, PATH, TNS_ADMIN, but still i encountered this error when i tried to connect to db using command line sqlplus.exe.
(Start -> Run -> sqlplus)

But this time i noticed that the command line is launching the sqlplus.exe from 10gAS Oracle Home and not from 10gR2 Oracle Home. This is kinda strange since my PATH variable has 10gR2 Oracle Home/bin as the first entry.

My actual settings for user variables

ORACLE_HOME = C:\oracle\product\1020\
ORACLE_SID = INST
PATH = C:\oracle\product\1020\BIN;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\oracle\product\as\1020\bin;C:\oracle\product\as\1020\jlib;
TNS_ADMIN = c:\oracle\product\1020\network\admin

Even though my user variables are set properly, the PATH variable value under System Variables has the 10gAS Oracle Home/bin as the first entry. That is the culprit. So i changed the PATH variable to have 10gR2 Oracle Home as the first entry.

I launched sqlplus.exe again from command prompt and this time it picked up the correct oracle home (10gR2 Oracle Home) and the not so popular error “Procedure Entry Point Snlinaddrlocalhost Could Not Be Located In The Oranl10.Dll” vanished.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 5 so far )

DISPLAY variable in 11i and R12

Posted on April 14, 2008. Filed under: R12 | Tags: , , , , , |

Is DISPLAY variable must in R12 to display chart/graphs and reports?

Before going into that, lets see whats the impact of setting a wrong value to the DISPLAY variable in 11i.

1) Charts & Graphs cant be viewed (it will be blank with x mark)
2) Reports cannot be viewed in browser (like concurrent request output etc.,)

Troubleshooting the display issue is another big task on non-autoconfig enabled 11i instance, because its set in so many places viz., adcmctl.sh, adrepctl.sh, adfrmctl.sh $APPL_TOP/SID.env $ORACLE_HOME/SID.env.

Coming back to the need for DISPLAY variable value in R12, the answer is partly yes, partly no, in the sense, DISPLAY variable is must but the value can be anything. Excuse Me, what did you just say. Yes, the value can be pointing to any server any value in the format of :. eg. myhost:0.0

In R12, as you all know the mid-tier architecture is totally different from 11i and due to the inclusion of Oracle 10g AS, DISPLAY variable value doesnt make much of difference. In Oracle 10gAS, the REPORTS_DEFAULT_DISPLAY variable determines the need for a DISPLAY variable.

Accepted values for REPORTS_DEFAULT_DISPLAY are YES / NO.

YES – Overrides the need for DISPLAY Variable value even if it is set.
NO – Makes use of the DISPLAY variable.

REPORTS_DEFAULT_DISPLAY achives this functionality (elimination of DISPLAY) by use of J2SE or JDK 1.5 and above. This functionality is available on all OS Platforms except AIX.

For more information on this topic you can refer to Metalink

Note ID: 260256.1 Removal Of Display For Headless Server And Printer Dependencies In Reports 10.1.2

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

Where are the log files located in R12?

Posted on April 6, 2008. Filed under: R12, TroubleShooting | Tags: , , , , |

For DBA’s who mostly worked on 11i environments, finding the log files (Concurrent manager log, apache log etc.,) on a R12 environment might pose a threat initially, because these log files no longer reside in their old location ie., $APPLCSF/$APPLLOG or $APACHE_TOP/Apache/logs.

In R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)

Concurrent Reqeust related logs

$LOG_HOME/appl/conc -> location for concurrent requests log and out files
$LOG_HOME/appl/admin -> location for mid tier startup scripts log files

Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home)

$LOG_HOME/ora/10.1.3/Apache -> Location for Apache Error and Access log files
$LOG_HOME/ora/10.1.3/j2ee -> location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn -> location for opmn related log files

Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)

$LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports

Related metalink notes to enable additional debugging

419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12
422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN

Happy troubleshooting !!!

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 2 so far )

Staging files for Oracle Apps installation

Posted on April 6, 2008. Filed under: Installation, R12 | Tags: , |

Hi,

Heres a single “for loop” (Unix) to stage the installation files (*.zip) under specific directories without creating the required directories.

$cd <location where .zip files are copied>

for i in ls -l *.zip
do
unzip -od <staging locaion> $i
done

It is always advisable to verify the zip files before extracting. You can use the same “for loop” with different unzip option

for i in ls -l *.zip
do
unzip -t  $i
done

Note: You can type all of the above commands in the shell prompt, no need to write a shell script to run the for loop.

Happy staging !!!

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...