RSS

Category Archives: Oracle

High Accuracy in Week (18 February 2012) in PL/SQL Challenge

Last updated on November 26th, 2014 at 07:07 am

I am delighted to share with you all that I am awarded “High Accuracy in Week” by PL/SQL Challenge.
The award is for the week 18th February 2012. The prize I received is Amazon.com US$25 Gift Card 🙂

Visit my public profile on PL/SQL Challenge for other achievements

Cheers!

 
Leave a comment

Posted by on February 20, 2012 in Oracle, PL/SQL

 

Tags: , , ,

Webinar Recording on Oracle Analytical Functions now available

Last updated on November 26th, 2014 at 07:07 am

Thanks for attending the webinar on Oracle Analytical Functions.
Those who had missed the webinar can watch the recorded version below.

Hope you all find it useful.

Keep looking for this space for more updates on upcoming webinars.

 
Leave a comment

Posted by on February 18, 2012 in Oracle

 

Tags: , , , , , , , , ,

Webinar: 18 Feb 2012 – 3:00 p.m to 4:00 p.m IST on Oracle Analytical Functions

Last updated on November 26th, 2014 at 07:07 am

Dear Friends,

Join me for a Webinar on February 18, 2012 (Saturday) from 3:00 p.m to 4:00 p.m IST

Oracle Analytical Function by Vijay Mahawar

Register for the webinarSpace is limited.
Reserve your Webinar seat now at:
https://www4.gotomeeting.com/register/995979855

Looking forward to see you all for the webinar.

Keep watching this space for more updates.

Cheers!


About the presenter:
I am Vijay Mahawar, an Oracle Technologist. I am a member of AIOUG, ODTUG and OTN. I am certified in Oracle and hold OCP in Oracle 11g DBA and OCP in Oracle 11g Advanced Developer Track. I am also OPN Certified Specialist. To know more about me please visit the know me section of the blog.

 
Leave a comment

Posted by on February 15, 2012 in Oracle

 

Tags: , , , , , , , , ,

Now I am OCP in Database Administration 11g

Last updated on November 26th, 2014 at 07:07 am

OCP DBA in 11gOPN Certified Specialist

Its gives me immense pleasure to inform you that now I am Oracle Certified Professional in Database Administration 11g.
I am also issued the OPN Certified Specialist certificate along with this.

I have successfully upgraded my OCP 10g in DBA to OCP 11g DBA. I had appeared for the bridge paper 1Z0-050 (Oracle Database 11g: New Features for Administrators) and passed by securing 70% in the exam. The pass mark for paper 1Z0-050 is 61%. There were 77 questions which had to be answered in 105 minutes.

Exam topics in 1Z0-050 is depicted in following image

1Z0-050: Exam Topics

My success mantra for clearing the paper:
1) Undergone one 5 day course from Oracle University.
2) Referred Oracle-base website for latest and new features in 11g
3) Download the software from oracle software and practice the new features.
4) Join forums and visit blogs of Oracle Guru’s (shared in the right hand panel of my blog)
5) Attend OTN events to get to know the latest in Oracle Technologies.

I heartily thank my faculty at Oracle University and friends and family who had been instrumental in this.

 
Leave a comment

Posted by on January 24, 2012 in Oracle

 

Tags: , , , , , ,

Diary on OTN – Developer Day Event (The Claridges, New Delhi)

Last updated on November 26th, 2014 at 07:07 am

17th January 2012
“The Claridges”, New Delhi

Today I happen to attend another OTN(Oracle Technology Network) – Developer Day Event. This time the event was held in Hotel “The Claridges”, New Delhi.

For those of who are not aware of OTN

What is OTN?

Oracle Technology Network is the world’s largest community of application developers, database admins, system admins/developers, and architects using industry-standard technologies in combination with Oracle products.

Millions of members collaborate online to share real-world expertise and best practices about how to best design, build, deploy, manage and optimize applications. OTN is also a provider of free developer workshops across the globe, as well as a sponsor of third-party conferences, user group meetings, and events.

How to register for the event?

OTN events happen round the year at different venues across the globe. All Oracle Certified Professional, who have registered at Oracle website (http://events.oracle.com) may receive the email invite at their email with which they have registered (Usually the SSO id)

The nomination/invite for this event is sent from Oracle. After the invite is received the candidate has to register for the event at Oracle website or the link mentioned in the mail. Oracle confirms the registration on the basis of the role of the Oracle Professional and seats available for the event.
On successful confirmation candidate should receive an email at their email id.

About the Event

The event kicked-off by filling up registration form and distribution of badge and agenda followed by breakfast.

Agenda
09:30 a.m. – 10:00 a.m. Welcome and opening remarks
10:00 a.m. – 10:45 a.m. Keynote: What’s New In Oracle Application Development
10:45 a.m. – 11:30 a.m. Migrating from non-Oracle Databases to Oracle Database with Oracle SQL Developer
11:30 a.m. – 11:45 a.m. Tea/coffee Break
11:45 a.m. – 12:45 p.m. Updating Your Database Design with Oracle SQL Developer Data Modeler
12:45 p.m. – 01:30 p.m. Lunch Break
1:30 p.m. – 2:15 p.m. Data Subsetting and Masking: Advanced Techniques for A Secure Test Data Management
2:15 p.m. – 3:00 p.m. Worry-Free Application Upgrade with the Change Management Capabilities in Oracle Database Lifecycle Management Pack
3:00 p.m. – 3:45 p.m. Big Data – Are You Ready?

Session 1: Keynote: What’s New In Oracle Application Development
This was the first session and focused on new features and enhancements in various oracle tools and products viz. Oracle SQL Developer 3.1, Apex 4.1, Oracle PL/SQL Result Cache, Times Ten in-memory Database Cache, Oracle 11g,
In the interest of the time last few topics were discussed only in brief.

Session 2: Migrating from non-Oracle Databases to Oracle Database with Oracle SQL Developer
This session showcased the migration feature of the SQL Developer tool shipped by Oracle. It discussed various kinds of migrations:
a) Windows to Linux migration
b) Sybase to Oracle 11g
c) VB to Java
d) MS SQL Server to Oracle 11g
e) MS Access to Apex
f) Teradata to Exadata

All this migration are done in 5 stages from 3rd party to Oracle. The 5 stages are:
Connect -> Capture -> Convert -> Generate -> Data Move
The Migration can be done online as well as offline.

Databases supported are:
1. Sybase
2. MS SQL Server
3. MS Access
4. MySQL (3.x, 4.x, 5.x)
5. IBM DB2 UDB(8 & 9)
6. Teradata (12,13)

The presenter with help of video demonstrated how drivers to different databases can be added to SQL Developer and configured.
Interesting URLs shared at the end: www.googlefight.com

Session 3: Updating Your Database Design with Oracle SQL Developer Data Modeler
This session was primarily of interest for the Database Architects and who are involved in designing and modelling of the database. It discussed three kinds of Models: Logical, Physical and Relational.
It also discussed Top Down Model (Business Info to Physical Model) and Bottom up Model (Reverse Engineer DB, Revise/modified model and Generate the modified DDL)
Interesting Visits: www.oracle.com/goto/oll (Oracle Learning Library link for more videos and tutorials on Data Modeler tool by Oracle)

Session 4: Data Subsetting and Masking: Advanced Techniques for A Secure Test Data Management
This session showed how production data can be made available on non-production environment like Development, UAT and Staging. Sensitive data can be masked and made available to non-production environment. Types of masking discussed were:
a) Compound Masking
b) Condition Masking
c) SQL expression Masking
d) Key based Reversible Masking

Intersting URLs: www.oracle.com/goto/oll (Oracle Learning Library)

Session 5: Worry-Free Application Upgrade with the Change Management Capabilities in Oracle Database Lifecycle Management Pack
The session showcased the features of Database Lifecycle Management Pack:
1. Discoverer (Auto-Discoverer – new in 12c)
2. DB Provisioning
3. Patch Management
4. Database Upgrade
5. Change Management
6. Dictionary Baseline
7. DBMS_COMPARISION package (new in 12c)
8. Configuration Management
9. Drift Management (Cell to Cell, RAC to RAC and Intance to Instance)

Session 6: Big Data – Are You Ready?
Data is classified on basis of Volume, Velocity, Variety and Value.

This session stressed on Hadoop architecture, Oracle Exadata, Exalogic and Exalytics with help of following video

Overall the event was very informative and gave first hand info on latest technologies in Oracle 11g  and Oracle Enterprise Manager 12c from developer and DBA perspective. I have tried to highlight most of the features showcased in the event.

Hope you find it useful!

 
Leave a comment

Posted by on January 18, 2012 in Oracle

 

Tags: , , , , , , ,

Tom Kyte’s Resolutions for DBAs for 2012

Last updated on November 26th, 2014 at 07:07 am

Oracle Database Insider checked in with Oracle database guru Tom Kyte, of AskTom fame, on his New Year’s resolutions. “For 2012, I have some evergreen resolutions, and some that reflect the changing DBA world today,” says Kyte. I hope they inspire other DBAs to make some resolutions of their own.”

Keep Learning
I started by looking back at my previous annual resolutions to see what should migrate to the new year. For example, practicing a restore is one of the simplest ways to make your own life easier by reducing the risk of recovery mistakes—I always have that on my to-do list. The same goes for ideas around learning, such as participating in a user group, the OTN Database forum, or to mentor and be mentored. Assume there is always more to be learned, and that your knowledge doesn’t constitute the latest and best thinking. The more you stay engaged with your profession, the more you’ll bring to the table as a DBA professional.

Add Skills to Your Toolbox
Develop a new skill, whatever way you learn best. I learn best by playing with the technology, reading documentation, and then asking questions. Others prefer instructor-led training, and others like to Google around and figure stuff out. It doesn’t really matter how you do it, just that you realize that everybody can use another means in their skills toolbox. My personal goals this year are skill improvements for big data and the model clause in SQL.

No More “I Can’t”
Starting today, remove the word “can’t” from your vocabulary, because there really isn’t any such thing. Instead, for whatever reason—fear, doubt, lack of confidence—realize you’ve purposely decided not to do what’s been asked. If you start saying “let’s figure out a way to accomplish this,” you’ll quickly start finding out that you can. You’ll be surprised to learn from now on, there’s nothing you can’t do.

Software Is Not the New Hardware
Don’t fall into the “software is the new hardware” trap. (Kudos to my colleague Andrew Holdsworth, Oracle senior director, Real World Performance, for that catchphrase.) Back in the day, computers were huge and required lots of metal and other “hard” objects. We called it computer hardware because it was unchangeable, as opposed to the changeability of software. These days, the opposite seems true. People are willing to change the hardware at the drop of a hat, but try suggesting a software change and chances are you’ll run into a multitude of “we can’t.” If you want an order of magnitude or more change in performance, changing the software is going to be mandatory.

Source: http://www.oracle.com/us/dm/kyteresolutions-1440386.html

 
Leave a comment

Posted by on January 5, 2012 in Oracle

 

Tags: , , ,

User Authentication(Part 2/4): Strong Authentication

Last updated on November 26th, 2014 at 07:07 am

SOS to SSO !

Authentication methods can be classified as:

1. Something user know (password)
2. Something user is (biometric)
3. Something user have (smart card)

Strong user authentication is more than password authentication. It can be combination of any two of the above authentication type to gain high confidence on the user signed in to the centralized server. This server may also be SSO (Single Sign-on).

Strong authentication is supported by the following technologies:

1. Certificates, public key infrastructure (PKI).

a) Public Key Infrastructure (PKI) in oracle can be achieved through Oracle Wallet component which in turn can be managed using Oracle Wallet Manager. Oracle PKI (orapki) utility is a command-line utility given by oracle to manage certificates in Oracle.

b) Certificates are digital documents that provide proof of user identity. Certificates can be stored in Oracle in Oracle Wallet or Oracle Internet Directory.

2. RADIUS, token and smart cards:

a) RADIUS is Remote Authentication Dial-In User Service. Its a client/server security protocol that is widely used to enable remote authentication and access.

3. Kerberos:

Kerberos server needs to be installed and configured to use for authentication with Oracle Database.

Note: All strong authentication techniques requires Oracle Advanced Security (OAS).

I will soon add a demo on configuring Oracle Wallet Manager in this space.

 
Leave a comment

Posted by on September 11, 2011 in Oracle

 

Tags: , , ,

User Authentication(Part 1/4): Create users in oracle and authenticate by OS

Last updated on November 26th, 2014 at 07:07 am

Password! Password! Password!

Today with so many accounts and passwords for each of those accounts. Password management has become the primary focus for any database management team. DBA are burdened with additional responsibility which can be minimized and done with reduced time and cost.

Oracle provides some really good features for authentication, which are as follows:

1. Basic Authentication:
a. Database user authenticated by password
b. Database user authenticated by OS.
2. Strong Authentication
3. Enterprise User Security
4. Proxy Authentication

This post is Part 1 of 4 in the series on User Authentication in Oracle.

DBA are most needed when the user accounts get locked due to incorrect login attempts. When the user is a privileged user then it becomes utmost important to safeguard them against this unplanned lockout and mitigate the possibility of unproductive business hours.
In large organizations you can imagine the number of calls received by helpdesk and support team for resetting the password and request for unlocking accounts.

We are all familiar with basic user authentication in oracle which is mostly by password.
Following screen shows the user “whizdba” created and authenticated by password.

Basic Authentication - By Password

Database user authenticated by password

This type of authentication is very common and used widely. The advantage with this type of authentication is that database user can be audited, each user has a schema associated to them. On the other side the disadvantage is that the user can connect to only database where the account is created. The user is bind to one database server and any time there is need for the user to connect to different server, location the account has to be migrated or new account has to be created.

Another type of basic authentication provided by oracle is authentication by OS. The steps for setting up the user for this type is:

Step 1: Set the parameter os_authent_prefix. The default parameter value is ops$
Step 2: Create database user ops$whizdba with ‘identified externally’
Step 3: Create os user whizdba on the server where database resides.
Step 4: Set environment variables for the whizdba user in .bash_profile file
Step 5: Test and Connect to the database using OS authentication.

I have tried to demonstrate the above step with an example user “whizdba” as shown below

Step 1: Setting the parameter “os_authent_prefix” to ops$. In my case its already set. You can set it to different value using ALTER SYSTEM command.

Basic Authentication by OS

Step 1: Set parameter os_authent_prefix

Step 2: Create database user ops$whizdba with ‘identified externally’

Create database user "whizdba" identified externally

Step 2: Create database user "whizdba" identified externally

Step 3: Create os user whizdba on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 4: Set environment variables for the whizdba user in .bash_profile file. Basic variables which need to be set are ORACLE_BASE, ORACLE_HOME, ORACLE_SID and PATH to ORACLE bin directory.

Step 4: Set environment variable in .bash_profile file of whizdba

Step 4: Set environment variable in .bash_profile file of whizdba

Step 5: Test and Connect to the database using OS authentication. Externally authenticated user connect to the database as : sqlplus / (as shown in the below screenshot)

Step 5: Test and Connect to user whizdba using OS Authentication

Step 5: Test and Connect to database using OS Authentication

Authenticating oracle database user externally by OS can be used in situation where scripting and batch job is performed by a user heavily. The advantage and disadvantage of this user remains same as that of basic password protected database user. The security concern here is that anyone can connect to the database from remote machine by creating an OS user with the same name as the externally authenticated database user. Hence getting unintended access to the database server. 

This calls for the need for stronger authentication techniques which I would discuss in my next post: User Authentication(Part 2/4): Strong Authentication

 
Leave a comment

Posted by on September 3, 2011 in Oracle

 

Tags: , , ,

My first step towards OCM

Last updated on November 26th, 2014 at 07:07 am

Recently, I had attended hands on course from Oracle University for Oracle 11g Security DBA.
I take this opportunity to share my experience and knowledge gained out of this course.

About the course:
In Oracle Database 11g: Security course is a 5 days course. In this students learn how they can use Oracle database features to meet the security, privacy and compliance requirements of their organization. The current regulatory environment of the Sarbanes-Oxley Act, HIPAA, the UK Data Protection Act, and others requires better security at the database level. Students learn how to secure their database and how to use the database features that enhance security. The course provides suggested architectures for common problems.
This course covers the following security features of the database:

  • auditing, virtual private database, label security
  • encryption for Payment Card Industry Data Security Standard (PCI DSS ) including encryption at the column, tablespace and file levels,
  • enterprise user security.
  • Some of the Oracle Network security topics covered are: securing the listener and restricting connections by IP address.

How to register for the course from Oracle University (http://education.oracle.com)
Oracle offers courses in different streams on Oracle products through class room training at Oracle University and authorized Oracle Education Center.
Below is the list of courses offered in Oracle 11g:

Learning-Paths-Oracle11g Source: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getlppage?page_id=212&path=DBAN

Oracle class room trainings are pretty expensive and prospective candidates are advised to select the course carefully, depending on their career stage and past experience. Oracle 11g Security is an advanced course in Oracle and costed me 65,000 INR (including service tax).

Interested candidate can check the schedule for upcoming Oracle courses at Oracle Education website. On finding the course of interest they can then register for the course and complete the registration form. The candidate receives the e-kit for the course 2-3 days in advance before the start of the training at their email id with which they have registered.

Why I had opted for Training in Oracle 11g Security?
I had undertaken this course in pursuit my to become OCM (Oracle Certified Master). Having come from a PL/SQL development experience for 6+ years with 2 years of DBA, I found security to be the perfect mix of both the worlds. Moreover, working in finance domain made more sense for attending this course.

Security in Oracle has to be handled both at administrative level and developer level.

Oracle 11g has really impressive security features which I have tried to highlight in the later section of this document. I am sure you will appreciate the security features offered by Oracle 11g once you have read this document.

What I learnt in the training?

Defence-in-Depth

1. Prevent access by non-database users
2. Increase database user identity assurance
3. Control access to data within database
4. Audit database activity
5. Monitor database traffic and prevent threats from reaching the database
6. Ensure database production environment is secure and prevent drift
7. Remove sensitive data from non-production environments

The student guide of Oracle 11g Security consists of 21 chapters and 5 appendices demonstrating examples for practice in lab. This being advanced Oracle training, Its a plus if you have a experienced trainer. The trainer prioritizes the course content depending on the participants past experience and career level.
Oracle-11g-Security-CourseContent

References and Sources:
Those who are interested in delving deep into the security track may visit following sources:

Website: http://www.petefinnigan.com – Pete Finnigan is the author of the SANS book Oracle security step-by-step – a survival guide for Oracle security. Pete also has written many papers about Oracle security. petefinnigan.com is the place for free Oracle security information, white papers, links to other resources, free scripts tools and products and professional Oracle security audit services.

Book: Effective Oracle Database 10g Security by Design by David Knox

 
1 Comment

Posted by on August 26, 2011 in Oracle

 

Tags: , ,

Diary on OTN (Oracle Technology Network) – Developer Day Event

Last updated on November 26th, 2014 at 07:07 am

Diary on OTN (Oracle Technology Network) – Developer Day Event held on 14th July2011, New Delhi

Today I happen to attend a OTN(Oracle Technology Network) – Developer Day Event. The event was held in Hotel Intercontinental Eros, New Delhi. It was one day event.

For those of who are not aware of OTN

What is OTN?

Oracle Technology Network is the world’s largest community of application developers, database admins, system admins/developers, and architects using industry-standard technologies in combination with Oracle products.

Millions of members collaborate online to share real-world expertise and best practices about how to best design, build, deploy, manage and optimize applications. OTN is also a provider of free developer workshops across the globe, as well as a sponsor of third-party conferences, user group meetings, and events.

How to register for the event?

OTN events happen round the year at different venues across the globe. All Oracle Certified Professional, who have registered at Oracle website (http://events.oracle.com) may receive the email invite at their email with which they have registered (Usually the SSO id)

The nomination/invite for this event is sent from Oracle. After the invite is received the candidate has to register for the event at Oracle website or the link mentioned in the mail. Oracle confirms the registration on the basis of the role of the Oracle Professional and seats available for the event.
On successful confirmation candidate should receive an email at their email id.

About the Event

The event kicked-off by filling up registration form and distribution of agenda followed by breakfast.

Session 1: Development with Oracle DB 11g R2 and Oracle DB 11g XE

This was the first session on latest version of oracle database – Oracle DB 11gR2 and Oracle DB 11g XE.

The session was very exhaustive and gave overview of new features of Oracle DB 11g version from the developer and architect perspective.

Session 2: Oracle SQL Developer 3.0

This session focused on the Oracle query tool – Oracle SQL Developer 3.0 and the new features added to it after version 2.1. It’s a free tool from Oracle with extensive features. It can be downloaded directly from the oracle website without paying any license fee.

Oracle SQL Developer 3.0 is shipped with Oracle DB 11g release.

Some salient features of Oracle SQL Developer 3.0 highlighted in the session were:
1. PLSQL Unit Tester
2. Extended Tuning support
3. DBA Navigator
4. DBMS Scheduler
5. Database Migration
6. Data Miner
7. Schema Browser
8. Query Builder

This session was followed by a well needed tea/coffee break.

Session 3: Oracle Enterprise Manager 11g Database Management Packs for Developers

This session focused on OEM new features in Oracle 11g for developers. The presenter was innovative and mixed the presentation with demo – showing the OEM interface wherever required. The salient features touched were:
1. Real Time PL/SQL Monitoring.
2. SQL Monitoring in real time.

This was followed by one hour lunch break.

Post-Lunch session focused mainly on APEX 4.0, PLSQL enhancement in Oracle Database 11g and Interpreting explain plan.

Session 4: Oracle Application Express 4.0

This session dealt with the Oracle Application Express version 4.0. Oracle Application Express is a database centric web application development tool. It’s a browser based development and deployment tool and supports web 2.0.

The presenter was principal product manager of APEX 4.0 at Oracle and was keen on giving demo of all the features of APEX 4.0. He highlighted features like

1. Websheets
2. Dynamic Actions
3. Plug-ins
4. Team Development
5. Improved charting including Gantts and Maps.
6. Enhanced Interactive Reports

Oracle Application Express 4.0 is shipped with Oracle DB 11g release.

Session 5: PLSQL enhancement in Oracle Database 11g

This session highlighted the new features in Oracle DB 11g for PLSQL perspective.

The features which were presented were:
1. SIMPLE_INTEGER
2. In-lining
3. SQL Query Result Cache – Hints
4. PLSQL Function Based Result Cache
5. Compound Triggers
6. Security Level in DBMS_SQL
7. Fine Grained Audit Control for UTL_TCP and its cousins – Access Control Lists
8. Regexp_Count
9. Creating Disabled Trigger
10. Firing Order of Trigger
11. WHEN OTHERS THEN NULL – PLSQL Warning
12. Sequence.nextval can directly be assigned to a variable in 11g
13. CONTINUE statement
14. Named notation in SQL select is possible in 11g
15. DBMS_Parallel_Execute – Updating huge table with parallel execution
16. PLSQL Hierarchical Profiler

Session 6: Explaining the Explain Plan: Interpreting the execution of Explain Plan

This being the last session there were very few attendees left in the hall. I held the fort and tried to be attentive. The presenter was solution architect at Oracle and covered the topic well in given time slot.

The features highlighted were:
1. Different ways to see explain plan in Oracle
a. Explain Plan command in SQL Plus
b. V$SQL_PLAN
c. DBMS_XPLAN package
2. Criteria for plan to be good
a. Serial execution – Less cost is good.
b. Parallel execution – Faster performance is good.
3. What is cost? Parameters on which we can tune:
a. Cardinality – No. of distinct rows
b. Access Path – Best way
c. Join Order – Correct Data
d. Join Type – Right type of Join
e. Partitioning Pruning
f. Parallelism
4. Updating the statistics using DBMS_STATS and DBMS_STATS.CREATE_EXTENDED_STATS
5. It discussed various kinds of scans viz. Full Table scan, Index unique scan, Index range scan etc.

Overall the event was very informative and gave first hand info on latest technologies in Oracle 11g from developer perspective. I have tried to highlight most of the features showcased in the event.

Hope you find it useful!

 
Leave a comment

Posted by on July 14, 2011 in Oracle

 

Tags: , , , ,

 
snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake