Monday, 26 May 2014

Online Patching




Download Required Security Patch from oracle support

Copy zip file to server and unzip the patch as below example


It will create a directory with patch number.

Then Read README.txt file thoroughly follow the steps given in the readme file..

First of all If the patch is online patch-able  then we need to decide whether to apply
online or offline.

Here I am giving example for online patching and patch is 17258083

From the patch directory where we unzipped the patch

Suppose If  I unzipped patch in /u01/patches directory then

cd /u01/patches/17258083/

Determine whether any currently installed interim patches conflict with this patch 17258083
as shown below



Ensure that you maintain adequate memory on your system to apply this online patch

Formula to calculate required memory is

Memory Consumed = (Number of Oracle Processes + 1) X (Size of Patched .pch File)

in my example

Memory Comsumed=(150+1)*(122554)=17MB


my one instance max process is 150



then apply patch

opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:




for RAC environment

opatch apply online -connectString <SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>

opatch apply online -connectString TESTDB1:sys:sys123:Node1,TESTDB2:sys:sys123:Node2



If in readme.txt file If mentioned to run catbundle.sql then run it ..

@?/rdbms/admin/catbundle.sql cpu apply

Then You Can check in the registry$history view that patch is loaded into database or not..


To apply this same patch for other sid s




To Rollback Installed Patch










Saturday, 24 May 2014



Oracle 11g Access Control List Configuration for utl_Smtp and utl_mail



You Must Have XDB Component installed in Your Database..




If not install using below script

@?/rdbms/admin/catqm.sql  xdb sysaux temp YES

create acl as below example


Add SMTP server(Mail Relay) details in Create ACL as below


Give access of Created ACL to USERS


After Creating Once Again Give Grant to execute below procedures
UTL_SMTP
UTL_TCP
UTL_HTTP
UTL_MAIL





We Can send mail using both UTL_SMTP and UTL_MAIL packages.
If you want UTL_MAIL Package also
Run below Command to Create UTL_MAIL Package

@?/rdbms/admin/utlmail.sql

and

@?/rdbms/admin/prvtmail.plb

Then


Set "SMTP_OUT_SERVER" to Mail Relay Host as Below

alter system set smtp_out_server='xx.xx.xx.xx' scope=both;

XX.XX.XX.XX is Mail Relay Server IP address.

After Doing all this Now You can send mail from database using both packages UTL_MAIL and UTL_SMTP..

Create Required Procedure to Send a Mail.

For Example:







Execute The Procedure To send a test mail