Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

The All India Oracl Users Group (AIOUG) presented Sangam 2010, a two-day(3-4-Sep-2010) Oracle Users Conference held in Hyderabad. Sangam 2010 hosted multiple sessions by international and Indian experts like Jonathan Lewis , Rittman , Iggy Fernandez etc.

Jonathan Lewis & Virag Sharma

The event was very good and opportunity to learn from experts. Sangam 2010 started with Murali welcome note followed by Presentation, by Roland Slee, Vice-President, Database Product Management, Oracle Asia Pacific & Japan.

First technical session was from Jonathan Lewis on "Writing Optimal SQL". This One day Presentation was divided in to two , 1/2 day sessions. Conference room packed for his sessions, Jonathan Lewis Presentation was main attraction for Sangam 2010. After Lunch , there were 2 Conference room for different sessions and audience can choose session as per there interest.

Sponsors "OSI Consulting" presentation on "Cross Platform migration challenges and time reduction techniques" was pretty good then expected. After "OSI Consulting" presentation, RAC SIG meeting held. In this meeting Satyendra Kumar , explained things about RAC SIG. In the end of "RAC SIG" meeting Satyendra looking leader for various city , for Delhi/NCR, I proposed Aman Sharma’s name and latter learned that Aman recently become Oracle ACE.

Session “Tips and Best Practices for DBA’s” from Francisco , was in soo much demand that Conference room 2 overflowed and finally session held in Conference room 1.

Rittman and Vivek took one session in Sangam 2010 and unfortunately , Not able to attend any of them.In the end of Sangam 2010 Oracle Users Conference, Jonathan Lewis took 1 Hr question and Answer session, that was pretty good.

Certain things in this world

"In this world nothing can be said to be certain but death and taxes."
--Benjamin Franklin

Module name for logon trigger in 11g R2 AWR report

Today while working on Production performance tuning, We came across one difference in awr report of 11g R1 and 11R2

In oracle 11g R1 AWR report logon trigger modelue name usually come like
sqlplus / perl / Mid Tier@server_name_from_it_login

But in 11g R2 AWR report logon trigger modelue name usually come like oraagent.bin@DB_SERVER_WHERE_IT_RUN

New change looks more logical because DB server running logon trigger code ,
not the user.

# Excerpt from 11g R1 AWR report # In below given example app678 is server name from where # user logged in to database using sqlplus Module: sqlplus@app678utl (TNS V1-V3) UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

#Excerpt from 11g R2 AWR report #In below given example apps001 is database server Module: oraagent.bin@apps001 (TNS V1-V3) UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

Oracle Database 11g Release 2 New Features : Edition based redefination

Oracle Database 11g Release 2 New Features : Edition based redefination
Virag Sharma

Every release has some Major changes , which we usually says New Features.Some of these features dominate the version, For example 11g R1 has SPA , DB Replay Active standby etc. Same this Oracle Release ( Oracle Database 11g Release 2 ) has some New features for which this release will be known in feature. These features are "Edition based redefination"


Most likely these features designed to give big support to APPS upgrade ( ie Oracle E-Business suite upgrade). When you upgrade APPS database , it need lots of down time , hope, using these new features APPS upgrade will take less time in future.


This feature will allow application upgrade( AS DBA , i would prefer to say Online Database object upgrade) with Minimum down time or may be zero down time. I consider this feature as one step toward ZERO DOWN time for application upgrade.


In 10g statistics collected on table published immediatly, That usually cause lots of performance issue. In oracle 11g r1 there is feature, for collecting stats on tables and publishing stats , as per need to avoid performance issue due to stats collection.

Taking similar feature to next step , 11g R2 has feature "REDEFINITION" , which upgrades objects , but not published immediately, also database can have multiple Editions of objects definition. Of-course there are, some limitation



Check Default Edition

SQL> 1 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES 2* Where PROPERTY_NAME = 'DEFAULT_EDITION' SQL> / PROPERTY_VALUE -------------------------------------------------------------------------------- ORA$BASE

Changing Edition at session or Database level

SQL> ALTER SESSION SET EDITION=ora$base; Session altered. SQL> ALTER DATABASE DEFAULT EDITION =ora$base; Database altered.

Grant create or drop edition to user

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to virag; Grant succeeded.

Enable Edition on schema / User

SQL> ALTER USER virag ENABLE EDITIONS force;

Of-course there are, some limitation......

Will add more ......soon , for How to...


Reference

19 Edition-Based Redefinition

More Post on Oracle RDBMS Database 11g R2 ( Release 2 ) 

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name

Oracle 11g R2 (11.2 ) New Features : SCAN - Single Client Access Name
Virag Sharma

Once you decided to add/remove node from RAC database/cluster. We need to change tns
entry. Oracle 11g R2 introduced new concept called Single Client Access Name (SCAN).
Which eliminate the need to change tnsnetry when nodes are added to or removed from
the Cluster.

RAC Instances register to SCAN listeners as remote listeners. SCAN is fully qulified name.
Oracle recommends to assign 3 address to SCAN , which create three SCAN listeners.

$ srvctl status scan_listener SCAN Listener LISTENER_SCAN1 is enabled SCAN listener LISTENER_SCAN1 is running on node apps001 SCAN Listener LISTENER_SCAN2 is enabled SCAN listener LISTENER_SCAN2 is running on node apps002 SCAN Listener LISTENER_SCAN3 is enabled SCAN listener LISTENER_SCAN3 is running on node apps002

Running following command on Node 2 (apps002)

$ ps -aef |grep -i SCAN oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN2 -inherit oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN3 -inherit oracle 9993 7114 0 09:57 pts/3 00:00:00 grep -i crs

From above output, it is clear that SCAN listener is running from CRS_HOME

$ srvctl config scan_listener SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521 $ srvctl config scan SCAN name: apps-scan, Network: 1/192.168.182.0/255.255.255.0/ SCAN VIP name: scan1, IP: /apps-scan.us.oracle.com/192.168.182.109 SCAN VIP name: scan2, IP: /apps-scan.us.oracle.com/192.168.182.110 SCAN VIP name: scan3, IP: /apps-scan.us.oracle.com/192.168.182.108

tns entry can use single address ( SCAN Name ) in tnsentry , instead os using entry for all Node


tns entry configured to use VIP addresses for Database will work without any issue. using
SCANs is not Medatory ( May be to support backward compatibility )

# # TNS ENTRY with SCAN # test.world = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST==apps-scan.world)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=R1211.world)) ) # # TNS Entry without SCAN ( Old way) # test.world = (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=apps001-vip.world)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=apps002-vip.world)(PORT=1521)) ) (CONNECT_DATA=(SERVICE_NAME=R1211.world))) ) )

Clients Can connect to a particular instance of the database using SCAN. Entry will looks like

test.world = (description= (address=(protocol=tcp)(host=apps-scan.world)(port=1521)) (connect_data= (service_name=R1211.world) (instance_name=apps1cl1)))

tns entry configured to use VIP addresses for Database will work without any issue. using

SCANs is not Medatory ( May be to support backward compatibility )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 ) 

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination