Archive for April, 2008
How to send email from 10g Oracle Database (UTL_MAIL)
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.
Installing / Configuring APEX (Application Express / HTMLDB) in 10gR2 on Windows XP
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
Snlinaddrlocalhost Error while connecting to 10gR2 Database on Windows XP
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
How to change the default banner / logo / image on the Apps Login Page?
Here are the steps to change the default Oracle Logo to a Custom Corporate Logo.
Step – 1: Create cusom image (gif/jpeg)Ā (eg. aaa.gif)
Step – 2: Copy custom logo (aaa.gif) to $OA_MEDIA
Step – 3: Check & update Profile Option nameĀ “FND_CORPORATE_BRANDING_IMAGE” to point to aaa.gif
Step – 4: Backup existing image files under $OA_MEDIA
mv FNDSSCORP.gif FNDSSCORP.gif.old
Step – 5: Replace the default logo with custom image
cd $OA_MEDIA
cp aaa.gif FNDSSCORP.gif
Step – 6: Access the login page, you should be able to see the custom image on top left.
cheers,
OraclePitStop
DISPLAY variable in 11i and R12
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
Where are the log files located in R12?
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
Staging files for Oracle Apps installation
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
OraclePitStop - Lets share some knowledge 
