I’m an Oracle ACE Award Winner now !

Dear All,

I’m pleased to inform you all that I have received an email from Oracle ACE Program Committee that I have been nominated for the prominent Oracle ACE Award. I’m honored to be the second person working in the Saudi Arabia to be an Oracle ACE.

Hope you are curious to know who the first person is working in the Saudi Arabia to be an Oracle ACE. Well, he is none other than my elder brother & mentor – Mr. Syed Jaffar Hussain . We are proud to be the Oracle ACE brothers from one family members and I can say, this kind of achievement is a unique combination, isn’t it?

Take a look at my Oracle ACE Profile – Sabdar Syed.

I would like to convey my sincere thanks to my elder brother, colleagues, ex-colleagues, blog viewers, friends and well-wishers for their support to aim this award.

Thanks

Thanks to Mr. Mohan Dutt, OCP Advisor, for publishing about my achievement, Oracle ACE Award, in his blog - OCP Blog Community.

Take a look at - Featured Expert Wins Oracle ACE Award!

Your precious comments are welcomed!

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com

Download Free Oracle Certification Dumps Here!!

This blog post is for them; those who are desperately looking for free Oracle Certification dumps.

Well, you might be surprised to see the message of the post is irrelevant to the subject. All my intension is to bring your attention towards “How bad it is? Cheating the Oracle Certifications by simply reading the exam dumps”.

Mr. Paul Sorensen, Director of Oracle Certification, and other certification team members have launched the Oracle Certification Blog , where they blog about everything on Oracle Certification. Interestingly, there are a couple of blog posts on “Cheating Hurts the Oracle Certification Program” and others.

For list of blog posts on Cheating, then take a look at – Cheating Oracle Certifications . Do not forget to read the comments of every post in the above link.

Quick Links to Prepare for Oracle Certification

• Previous Post: The books, guides, and materials used for my OCA/OCP/OCE Exam Preparation.
• On the Blog: My Other Blog Posts on Certifications.
• On the Web: Oracle Certification Program.

Keywords:

OCA, OCP, OCE, Dumps, Brain dumps, Practice Questions, Sample Questions, Cheat sheet, Test papers.

Regards,
Sabdar Syed.

Should I go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification?

Hello All,

Choosing whether to go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification is becoming complex to the Novice or Newbie DBAs. And also, I have seen a couple of posts asking such similar certification doubts more frequently in the OTN Forums, when they are not really sure or confused.

Well, this is ever been told by everyone that going for latest version certification is good and ideal. But, what I advise is, first go for Oracle 10g OCA and OCP, then upgrade to Oracle 11g OCP.

Following are my views on why to go for Oracle 10g OCA/OCP initially rather than 11g OCA/OCP directly.

• As we all know that newer version (11g) does include older version features plus new features and bug fixes of older version issues.
• Retirement date for Oracle 10g Certification has not yet been announced by Oracle. Moreover, Oracle Database 11g: Administration I (OCA) exam is only on production i.e. regular and Oracle Database 11g: Administration II (OCP) exam is not yet gone for Production i.e. still Beta Exam.
• Oracle Database 10g is still being used as Production for the business in all most all the organizations in the globe. But very less companies are using the Oracle Database 11g for their business, as Oracle 11g is still a base release 11.1.0.6 and yet to go for standard release 11g (11.2.X.X) shortly. This means that Oracle 11g is not fully deployed or used for Production purpose yet.
• Oracle Database 10g Release (10.2) still has Oracle primary and extended (Metalink) support for few more years from now, after that Oracle 10g will also be de-supported by Oracle.
• Both versioned (10g and 11g) certifications have two exams – Administration I (OCA) and Administration II (OCP). Each exam fees of them is $125 US i.e. there is no vary.
• It’s mandatory for the OCP candidates to undergo one approved course from the Approved list of Oracle University Courses for hands on course requirement. This applies to both Oracle 10g and 11g Certification takers.
• For Oracle 10g OCP Certification holders, there is only one exam 1Z0-050 - Oracle Database 11g: New Features for Administrators given to upgrade directly to the Oracle 11g OCP. No course or hands on course requirement form is to be submitted to achieve the Oracle 11g OCP (Upgrade) Certification.
• In this way, one will have both Oracle 10g and 11g Certifications in hand, and can show the same in their resume or CV. This also fulfills the requirement where the companies are looking for the candidates those are having enough experience with Oracle 10g and 11g, and holding multiple certifications in it.

One can go for direct Oracle 11g Certification under the following circumstances.

• If you are forced, by your company or manager, to undergo Oracle 11g Course and take Oracle 11g Certification Exams, for their specific requirement on Oracle Database 11g related projects.
• When there is no Oracle 10g course listed in the training institute in your city, instead only Oracle 11g Courses are available.
• When unable to afford to take Oracle 11g Upgrade exam.
• If my above views are not Okay with you -:)

Note: Folks, above are only my views, and need not to be the same with others. So, it’s left up you to decide whether to go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification. For any information or doubts, then refer the Oracle Certification Program link and navigate yourself to the options to know more about Beta Exams, Retirements, List of Exams, Upgrade your Certification, Hands on Course Requirement etc.,

Your comments are welcomed if this article information helps you.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Celebrating Second Year Anniversary of the Blog Launch

Dear My Blog Readers,

It's been two years that I have lanuched and updated my blog by today. The blog originally started on 12-Dec-2006.

During the last year, I wrote and published good articles. I would like to thank all of my blog viewrs who made my blog popular by reading the interesting posts in m blog.

I hope, I would write more and more good articles which my blog viwers definitely like them and definitely get benifit from them.

Feel free to give comments, suggestions, and advices to improve this blog with more articles.

Once again thank you all.

Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

Oracle Approved Training Centers and Certification Test Sites & Address - Kingdom of Saudi Arabia

Hello All,

Here is the list of Oracle University Approved Educational Training Partners/Centers, and the certification test taking sites in Saudi Arabia.

Oracle University Approved Education Partners - Kingdom of Saudi Arabia

New Horizons Jeddah - Oracle Approved Education Center

P.O. Box : 52171
Telephone : 026642277
Fax : 026642454
Address : Al Rawda St, Jeddah ,KSA

New Horizons Khobar - Oracle Approved Education Center

P.O. Box : 2060
Telephone : 038588882
Fax : 038584014
Address : AL Khaleej Blg, Khobar, KSA

Al-Khaleej Training and Education Co.(New Horizons Computer Learning Center,KSA)

Al Wallan Building,
Takhasusi Street,
Riyadh,Kingdom of Saudi Arabia

Contact: Louai Al-Amir Salem, PMP
Title: Platinum Center Manager
Tel: 009661 416 0123 Ext. 400

Prometric Test Sites and Address for taking Oracle Certifications OCA/OCP/OCE

Riyadh:
=======

NEW HORIZONS COMPUTER LEARNING

ABOVE AL WALAN SHOW ROOM
AL KHURAIS ROAD NEAR AL AZIZA
PANDA TAKAHASSUI PO 295300
RIYADH 11351
Phone: 14160123 Site Code: SU7

NEW HORIZONS COMPUTER LEARNING

Olya Main Street
Behind Jareer Bookstore
Al Khaleej Ladies Center
Newhorizons P O Box 295300
RIYADH 11351
Phone: 1462 8393 Site Code: SU7M1

ALFAISAL INTERNATIONAL ACADEMY

IMAM SAUD BIN ABDULAZIZ ROAD
KING FAHED DISTRICT
PO BOX 10202
RIYADH 11433
Phone: 14552444 Site Code: SU60

ExecuTrain of Riyadh

Zero Floor, South Bldg.
Khaledyah Business Centre ,
Olaya St .
Riyadh 11351
Phone: 14621118 Site Code: SU74

Jiddah:
======

NEW HORIZONS COMPUTER LEARNING CENTRE

AMIR ABDULLAH STREET
AL RAWDAH DISTRICT 1ST FLOOR
PO BOX 52171
JEDDAH 21563
Phone: 966 2 6642277 Site Code: SU5

ARABIAN COMPUTER PROJECTS CO.

Ibrahim Juffali Road
(P.O.Box 14730)
Jeddah 21434
Phone: 966 26678411 Site Code: SU59

Others Cities:
==========

New Horizons Computer Learning Centre

Al-Khaleej Training & Education Company
AL-FAYHA, NEAR CENTRAL MARKET
PO Box 10968
JUBAIL INDUSTRIAL CITY 31961
Phone: 966 3 348 1166 Site Code: SU52


New Horizons Computer Learning Center

Al Khaleej Training and Education
Dhahran Street
Mubaraz
Al Ahsa 31982
Phone: 966 3 5305007 Site Code: SU55

New Horizons Computer Learning Center

Khalid Bin Walid Street
Circle Masjid Qiblatein
Madinah 1875
Phone: 48223333 Site Code: SU48


Philippine International School Ruraydah

PISB
Information Technology Dept.
Faiziyah District
P.O. Box 27089
Buraydah, Al Qasim 51331
Phone: 966 63841975 Site Code: su120


Al-Khaleej Training and Edu. Mens Branch

New Horizons CLC
KSA - King Saud Street
Buridah, Al Qasim 51432
Phone: 966 6 3827999 Site Code: SU54


NEW HORIZONS

AL-AHSA 31982
DHARAN STREET MUBARAZ
PO BOX 5822
HOFUF
Phone: 966 530 5007 444 Site Code: SU17


AL-KHALEEJ TRAINING AND EDUCATION

NEW HORIZONS CLC
AL-DIYAFAH STREET
P.O BOX 50991
ETWID
KHAMIS MUSHAIT 61311
Phone: 72375051 Site Code: SU57

NEW HORIZONS TABUK BRANCH

50 STREET NEW HORIZONS BLD
TABUK
Phone: 966 4424 8613 Site Code: SU39

Note: Check the below URLs, if the details given above are found incorrect.

http://www.oracle.com/
http://www.prometric.com/

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

How to find the particular Data Dictionary Table or Dynamic Performance View (V$)?

Dear Blog Readers,

Even though we use some of the important Data Dictionary Tables and Dynamic Performance (V$) views regularly, sometimes we fall short to recall a particular view, i.e. It’s not easy to remember all the dictionary views.

And also, many times, it’s been asked by novice in the OTN Forums that

What is the view to find the information about data files, tablespaces, etc.,?

What are the list of Data Dictionary and Dynamic Performance Views in so and so Oracle Version?

Well, I hope all of we know that there is a data dictionary view called “DICTIONARY”, using this DICTIONARY view, we can find the particular or list of pertinent views of any object in the database.

What is the DICTIONARY view?

The DICTIONARY view is a data dictionary view used to find the list of Data Dictionary and Dynamic Performance (V$) views, and it contains description of data dictionary tables and views. It has two columns, one is ‘TABLE_NAME’ – Name of the object and another column is ‘COMMENTS’ – Text comment on the object.

Discription:

SQL> desc DICTIONARY;
Name Null? Type
--------------------- -------- --------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

Usage:

To find the list of data dictionary tables and dynamic performance views.

SELECT * FROM dict;

To find the particular data dictionary table or dynamic performance view.

SELECT * FROM dictionary
WHERE table_name LIKE ‘%Search String%’;

i.e.

SELECT * FROM dictionary
WHERE table_name LIKE ‘%TABLESP%’;

Example:

SQL> SELECT * FROM dictionary
WHERE table_name='DICTIONARY';

TABLE_NAME
------------------------------
COMMENTS
-----------------------------------------------
DICTIONARY
Description of data dictionary tables and views

Note: While querying, either the complete word DICTIONARY or first four characters DICT, which is a synonym for DICTIONARY, can be used.

Reference: Oracle® Database Reference 10g Release 2 (10.2)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

Regards,

Sabdar Syed

Received the Certification Kit of Oracle 10g RAC Expert Exam.

Dear Friends,

After a long wait, I have received the Oracle 10g RAC Expert Certification Kit in my hands today, as I have already taken this exam (1Z0-048) in the month of August-2008. More or less it took more than three months to reach in my hands.

Best of luck everyone who is preparing for the Oracle 10g RAC Expert Exam – 1Z0-048.

Check my other blog post on “How to prepare for 1Z0-048 - Oracle 10g RAC Certified Expert Exam.”

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

How to Get/Gain Real Time Experience/Exposure as a DBA!

Dear Blog Readers,

Every now and then I do receive e-mails from the novice DBAs saying that:

“We could not perform well at the interview, as the questions were too technically based on real time requirements”

“Due to not having real time experience, we failed at the interview to explain the steps/approach to resolve the issue asked”

…… and so on

When I asked them how they prepare and practice the DBA Activities, their response is as follows:

“We brought the Oracle DBA Books written by so and so authors for reference.”

“We only practiced the scenarios what was taught and given as drill in the training institute.”

“We installed the Oracle Software with starter database and get started practicing.”

…… and so on

Well, here are my opinions/suggestions/advices, to them those who are preparing and practicing as above, to gain the real time experience/exposure.

A lot of novice or junior DBAs are out there, who do not know that Oracle offers online documentations on Oracle Database Administration. Rather they simply purchase the available books in the market and get started preparing it. I would advice them to make a habit of reading the Oracle online documentations daily whenever they have time.

As far as I know, in the training institutes, the trainers will only cover the basic and required information up to some level, and discussed the scenarios which are common and basic. I suggest them to not to treat those scenarios as real time questions. The trainers will give you or left up to you to practice and do the drill at your own.

In my opinion, the real time issues are not different from the issues you face while practicing. i.e. fixing the ORA-Errors generated. My strong advice is not to create the database while installing the database, instead create the database manually with very minimal size of SGA and data files. Then, put more load i.e. Insert/Updates/Deletes and look forward for errors and the slowness of database, then fix them and tune the database accordingly. This will not be possible where you have a database with good size of SGA and enough size of data files.

As a real time requirement, install and set up the Oracle Server on Linux Operating System, this confidence will help you in stalling the same on any flavor of the Unix/Linux operating system i.e. Sun Solaris, IBM AIX, HP-UX, Red Hat, Fedora etc, All you need to know that the commands which are specific to OS during the installation.

Never just be satisfied by simply reading the solutions given over the web for such problem which are treated as real time scenarios, rather try to reproduce the problem in your database system, if you can, and do apply the different solutions for that issue.

Do not mug up the procedural steps of any major activity in the database i.e. Upgrade, Migration, Installation, etc.., Even though these are not regular tasks or not possible to do it in the practice systems, I still insist to feel the taste of doing such activities. I can say these are the real time scenarios asked in the interviews.

There are many a lot to say…but at the end what all I can say is…..

Instead of waiting for not having the proper resource to practice the database real time scenarios or activities, at least try to search over the web to get to know what are other real time scenarios, I would strongly advice one to make a habit of regularly visiting OTN Database General Forums, where all kind of basic, advanced, real time, scenarios are questioned and answered by top experts with different and different solutions.

Forum: Database - General

Another good habit is referring the good Oracle Related Blogs to know how they resolved the issues, how they implement the things in their organization with their experience.

List of Oracle-related blogs

Do not forget to read and download this great document "Grow That DBA Career"

Note: Above suggestions are only my views and need not to be the same with others. Whoever have the information of real time ideas/tips and wanted share with the blog readers, they are free allowed to comment directly in the comments section of this blog post. I will also keep update this post with more and more real time experience ideas and tips.

Update: Take a look at the Mr.TOM comment in the comment section below, where he has given his own views on real time tips further to this blog post. Thanks to TOM for adding his views to this post.

Happy Reading !!!

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Oracle Metalink Notes for Oracle Apps DBAs.

Dear Friends,

Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.

Note: You have to have an account in Oracle Metalink to access notes.

Installation

Note: 452120.1 - How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 - How to locate the Rapid Wizard Installation log files for Oracle Applications 11.5.8 and higher
Note: 362135.1 - Configuring Oracle Applications Release 11i with Oracle10g Release 2 Real Application Clusters and Automatic Storage Management
Note: 312731.1 - Configuring Oracle Applications Release 11i with 10g RAC and 10g ASM
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 279956.1 - Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig
Note: 294932.1 - Recommendations to Install Oracle Applications 11i
Note: 403339.1 - Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
Note: 455398.1 - Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 11i
Note: 402311.1 - Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows
Note: 405565.1 - Oracle Applications Release 12 Installation Guidelines

AD Utilities

Note: 178722.1 - How to Generate a Specific Form Through AD utility ADADMIN
Note: 109667.1 - What is AD Administration on APPS 11.0.x ?
Note: 112327.1 - How Does ADADMIN Know Which Forms Files To Regenerate?
Note: 136342.1 - How To Apply a Patch in a Multi-Server Environment
Note: 109666.1 - Release 10.7 to 11.0.3 : What is adpatch ?
Note: 152306.1 - How to Restart Failed AutoInstall Job
Note: 356878.1 - How to relink an Applications Installation of Release 11i and Release 12
Note: 218089.1 - Autoconfig FAQ
Note: 125922.1 - How To Find Oracle Application File Versions

Cloning

Note: 419475.1 - Removing Credentials from a Cloned EBS Production Database
Note: 398619.1 - Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
Note: 230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone
Note: 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone
Note: 364565.1 - Troubleshooting RapidClone issues with Oracle Applications 11i
Note: 603104.1 - Troubleshooting RapidClone issues with Oracle Applications R12
Note: 435550.1 - R12 Login issue on target after cloning
Note: 559518.1 - Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Note: 216664.1 - FAQ: Cloning Oracle Applications Release 11i

Patching

Note: 225165.1 - Patching Best Practices and Reducing Downtime
Note: 62418.1 - PATCHING/PATCHSET FREQUENTLY ASKED QUESTIONS
Note: 181665.1 - Release 11i Adpatch Basics
Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?
Note: 231701.1 - How to Find Patching History (10.7, 11.0, 11i)
Note: 60766.1 - 11.0.x : Patch Installation Frequently Asked Questions
Note: 459156.1 - Oracle Applications Patching FAQ for Release 12
Note: 130608.1 - AdPatch Basics
Note::60766.1 - Patch Installation FAQ (Part 1)

Upgrade

Note: 461709.1 - Oracle E-Business Suite Upgrade Guide - Plan
Note: 293166.1 - Previous Versions of e-Business 11i Upgrade Assistant FAQ
Note: 224875.1 - Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note: 224814.1 - Installation, Patching & Upgrade Current Issues
Note: 225088.1 - Installation, Patching & Upgrade Patches Guide
Note: 225813.1 - Installation, Patching & Upgrade Setup and Usage Guide
Note: 224816.1 - Installation, Patching & Upgrade Troubleshooting Guide
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note: 423056.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.2)
Note: 726982.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.3)
Note: 452783.1 - Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note: 406652.1 - Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place
Note: 316365.1 - Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
Note: 418161.1 - Best Practices for Upgrading Oracle E-Business Suite

Printer

Note: 297522.1 - How to investigate printing issues and work towards its resolution ?
Note: 110406.1 - Check Printing Frequently Asked Questions
Note: 264118.1 - Pasta Pasta Printing Setup Test
Note: 200359.1 - Oracle Application Object Library Printer Setup Test
Note: 234606.1 - Oracle Application Object Library Printer Initialization String Setup Test
Note: 1014599.102 - Subject: How to Test Printer Initialization Strings in Unix

Performance

Note: 390137.1 - FAQ for Collections Performance
Note: 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note: 169935.1 - Troubleshooting Oracle Applications Performance Issues
Note: 171647.1 - Tracing Oracle Applications using Event 10046
Note: 153507.1 - Oracle Applications and StatsPack
Note: 356501.1 - How to Setup Pasta Quickly and Effectively
Note: 333504.1 - How To Print Concurrent Requests in PDF Format
Note: 356972.1 - 11i How to troubleshoot issues with printers

Others

Note: 189367.1 - Best Practices for Securing the E-Business Suite
Note: 403537.1 - Best Practices For Securing Oracle E-Business Suite Release 12
Note: 454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note: 394692.1 - Oracle Applications Documentation Resources, Release 12
Note: 370274.1 - New Features in Oracle Application 11i
Note: 130183.1 - How to Get Log Files from Various Programs for Oracle Applications
Note: 285267.1 - Oracle E-Business Suite 11i and Database FAQ
Note: 453137.1 - Oracle Workflow Best Practices Release 12 and Release 11i
Note: 398942.1 - FNDCPASS Utility New Feature ALLORACLE
Note: 187735.1 - Workflow FAQ - All Versions

Oracle Applications 11i/12 Online Documentation

http://www.oracle.com/technology/documentation/applications.html

Note: Above are only the few important notes, there are bunch of notes on many other issues and topics in the metalink. I would suggest one to refer the metalink for more note ids.

Leave your comments if you feel these Oracle Metalink Notes help you in solving your Application issues.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

My Blog Feedback Page

Dear Blog Readers,

I wanted to have a feedback page as a post in my blog where my blog viewers can post their valuable comments and suggestion about my blog and articles.

In order to serve you better with required information in my blog, please leave your comments/suggestions/doubts/questions/requirements/ etc.

Link for comments here - Feedback Page !

Please check the comments submitted in the comments item below.

Thanks & Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

How I Changed My Blog with 3 Column Template and Right & Left Sidebars?

I have been looking for the steps/code to prepare my blog with 3 column template, but couldn’t get a proper reference for that. Luckily, today I came across a wonder full blog for “3 Column Templates Step by Step Guides”, which will fit to my blog template.

It’s as easy as simple.

Step -1: Before going to change the original template, I gave a try on the test template by creating a new blogspot for testing perpose ( This Step-1 is optional)

Step -2: Take the backup of template before trying out any customization that requires direct modification of the HTML code of original template.

Step -3: Follow the below link.

3 Column Templates : Rounders : Left and Right Sidebars

For other type of templates, then check this

3 Column Templates: Step by Step Guides

Regards,

Sabdar Syed.

How We Resolved the Account Locked (Timed) issue?

An application user account, in one of our Oracle 10g Databases, is being locked every time. Below are our findings and solution to resolve the issue.

Details:
Oracle Database Version: 10g R2 (10.2.0.1)
Application User: APPUSR
Error: ORA-28000: the account is locked

Login as SYSDBA

SQL> conn /as sysdba

Check the APPSUSR account status.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT.

Check the resource limits of DEFAULT profile.

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER UNLIMITED
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS UNLIMITED
12 PASSWORD_LIFE_TIME UNLIMITED
13 PASSWORD_REUSE_TIME UNLIMITED
14 PASSWORD_REUSE_MAX UNLIMITED
15 PASSWORD_VERIFY_FUNCTION NULL
16 PASSWORD_LOCK_TIME UNLIMITED
17 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.

Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;

User altered.

Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR OPEN APPUSR_DEFAULT

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

My Mentor is an Oracle Certified Master in Oracle 10g now!

Dear All,

I’m pleased to say that my elder brother, Mr. Syed Jaffar Hussain, is an Oracle Certified Master in Oracle 10g now. He is truly deserved for it. In addition to this, he is already an Oracle Certified Professional in multiple tracks such as Oracle 10g/9i/8i, RAC DBA Expert, and also an Oracle ACE Award winner.

He is my mentor and motivator. So, I will have to gear up for Oracle Certified Master Program, and get trained under his guidance for the same.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Metalink is now My Oracle Support!

Yesterday, when I logged in to Oracle Metalink, I saw a new look of Metalink, and also Oracle Metalink Support has now been renamed to



As per My Oracle Support:


“Introducing My Oracle Support, the next generation MetaLink experience. My Oracle Support offers you secure, real-time access to Oracle experts on the complete Oracle software stack. It also provides groundbreaking personalized & proactive support capabilities that help reduce unplanned down time and improve system stability. Leverage the Internet for immediate access to 24/7 support and get the critical and timely information you need for running your usiness.”
A new look of My Oracle Support







Important Blog on new look of My Oracle Support.

Introducing... My Oracle Support!

Classic MetaLink vs. New MetaLink — What’s the Difference?

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

How to make a Link to be opened in a New Browser Window?

It's as easy as follows:

To get a link or URL, you specified or referred in your blogs, to be opened in a separate New Browser Window, what you need to do is add the target="_blank" attribute to your link tag i.e. in in the HTML code.

For Example:

  <a href="http://sabdarsyed.blogspot.com/" target="_blank">  This is My BlogSpot!</a>

This will give you the following link:

This is My BlogSpot!

This is useful when you do not want to open a link, you specified or referred in your blog, to open in the same window, rather it should open in a new browser window, by keeping original post open.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

How We Used the Oracle CSSCAN Utility and CSALTER Script to Migrate the Database Character Set?

As per the requirement from the Application Vendor, we had to change the database character set on one of the existing test databases.

CSSCAN Utility and CSALTER Script:

Database character set migration has two stages: data scanning (csscan) and data conversion (csalter.plb).

The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:

Each and every character in the current character set is available in the new character set.

Each and every character in the current character set has the same code point value in the new character set.

Here are the simple steps to migrate the default Database Character Set:

Database Version: Oracle 10g Database R2 (10.2.0.3)
Source Character Set: AL32UTF8
Target Character Set: WE8ISO8859P1

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Take a full database backup:

Sometimes, incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set. The database can be reconstructed later using the backup taken before this action, if something goes wrong.

Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.

Start up the database:

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the Database Character Set Scanner (CSSCAN) utility.

This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.

$ csscan \"sys/password@mydb as sysdba\" full=y
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
…………………………………………………………………………….
…………………………………………………………………………….
…………………………………………………………………………….

Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
$

For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.

$ csscan help=y

Run the CSALTER script.

Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database in Restricted Mode.

SQL> startup restrict;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the csalter.plb script

SQL> @?/rdbms/admin/csalter.plb

0 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validility...
begin converting system objects
12 rows in table SYS.WRI$_ADV_RATIONALE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table SYS.RULE$ are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
10576 rows in table SYS.WRH$_SQL_PLAN are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
19 rows in table SYS.WRI$_ADV_ACTIONS are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
578 rows in table SYS.WRI$_ADV_OBJECTS are converted
789 rows in table SYS.WRH$_SQLTEXT are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL>

Make sure the changes:

SQL> SELECT * FROM nls_database_parameters;

References:

Character Set Migration
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm

Character Set Scanner Utilities
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

Oracle Metalink Notes on Database Character Set Migration

Note: 225912.1 - Subject: Changing the Database Character Set - a short Overview
Note: 119164.1 - Subject: Changing Database Character Set - Valid Superset Definitions
Note: 66320.1 -Subject: Changing the Database Character Set or the Database National Character Set
Note: 123670.1 - Subject: Use Scanner Utility before Altering the Database Character Set
Note: 225938.1 - Subject: Database Character Set Health check.

Conclusion: Here I have only explained the steps I followed to convert the database character set, which is specific to our single instance database. It means that this article doesn’t consist of any other information about Database Character Set, as these details are available and discussed thoroughly in the Oracle Documentations and Oracle Metalink. If the database is a multi instance database i.e. RAC, and has other options enabled in place. I would strongly recommend to follows the Oracle Documentations and Oracle Metalink Notes. Moreover, this scenario, explained here, is specific to our requirement, and need not be the same with others. When you are not sure or in doubt, then do *NOT* do this for other character set combinations without logging a tar with Oracle Support for verification.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

From where can I download the Oracle OCP logo?

Various types of OCP Logos are used in many of Oracle related Blogs with different styles and colors; it means that, without knowing the registered OCP Logo by Oracle, simply customized OCP logos, with their own styles and colors, are being used.

The people, those who are using different logos apart from registered and suggested, are searching and downloading from the Google Images for readymade logo and using them.

Click here for Google Images for Oracle OCP Logo

If anybody wants to make use of OCP logo on their blogs or resumes, I would suggest them to download and use the registered OCP logo, which is suggested by Oracle.
Here is how one can download the Oracle OCP Logo.

* Everyone will receive the Oracle Certification Success/Welcome kit upon competition of all the Oracle Certified Professional Exams (OCP).

* In the Welcome/Success Kit, you will find a letter of congratulation by Oracle on completing and becoming an Oracle Certified Professional, and an Oracle Certificate on your name, and the OCP Membership card.

* You will be given the special credentials (User ID and Password) to access the Oracle Certified Professional Members Website.

* The URL for OCP Members Website will be in the letter of congratulation or in the Membership Card.

* You may need to download the OCP logo using your credentials given by accessing the OCP Members website.

* You will find more useful information from the member’s website, its self explanatory; you can explore and navigate the options by yourself.

Note:

* If you forgot your username and password, then you can contact ocpexam_ww@oracle.com an provide your prometric ID, email address and credential and they will provide you with the username and password to receive the OCA logo.
* Don’t share the credentials to access the OCP Members website to Non-OCP Candidates.

Here is a nice document on OCP Guidelines and following are the contents of it.

* Education Certification Logos
* Education Certification Logos Logo Elements and Proportions
* Education Certification Logos Clear Space
* Education Certification Logos Minimum Size
* Education Certification Logos Color Usage
* Education Certification Logos Background Control

http://www.oracle.com/education/downloads/ocplogoguidelines.pdf

Oracle Certified Professional Members Website.

Oracle 8i/9i OCP:
http://www.oracle.com/education/ocp

Oracle 10g OCP:
http://www.oracle.com/education/10ggocp

Oracle 11i Apps OCP:
http://www.oracle.com/education/ebusocp

Or For all:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=137

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Setting an Oracle Environment variable – ORACLE_HOME

Hello,

When I check my blog hit counter statistics, my blog is being hit and searched by the blog users with the word “Oracle Home”, but, looks like they are not finding the required information on “Oracle Home” Environment variable. So, I wanted to explain about ORACLE_HOME in my simple terms which is helpful for novice.

What is ORACLE_HOME used for?

* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.

What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.

Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.

How to check if ORACLE_HOME is set already?

On Unix/Linux Systems:

Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.

The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login­ – C Shell

Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile

To check specific environment variable set:

$ echo $ORACLE_HOME

To check all the environment variables set:

$ env

On Windows Systems:

To check specific environment variable set:

C:\> set ORACLE_HOME

OR

C:\echo %ORACLE_HOME%

To check all the environment variables set:

C:\> set

Or

C:\> env

Other way, to check the ORACLE_HOME, is as follows.

Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE

i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:

To find the ORACLE_HOME path in Oracle Database

How to set the ORACLE_HOME environment variable?

On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile

ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

Source the user profile as follows:

Bash shell:

$ . ./.bash_profile

Bourne shell or Korn shell:

$ . ./.profile

C shell:

% source ./.login

If no profile file is set with environment variables, then physically also be set as follows:

Bourne, Bash, or Korn shell:

$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME

C Shell:

% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0

On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

Another way to physically set the variables as follow at the DOS prompt:

C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%

Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

Using ORA_ROWSCN and SCN_TO_TIMESTAMP to find the last updated date or timestamp of a record.

Today, when I have gone through the Oracle Documentations, I came across two new things available in Oracle Database 10g – ORA_ROWSCN and SCN_TO_TIMESTAMP. Using this ORA_ROWSCN column and SCN_TO_TIMESTAMP function, the last date or timestamp can be found when a table or record updated.

ORA_ROWSCN

ORA_ROWSCN is a pseudocolumn of any table which has the most recent change information to a given row.

Here is an example to get the ora_rowscn value when a row updated.

SQL> connect scott/tiger@mydb
Connected.

SQL> SELECT ename FROM emp WHERE empno=7839;

ENAME
----------
KING

SQL> UPDATE emp SET ename='SABDAR' WHERE empno=7839;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT ora_rowscn,ename FROM emp WHERE empno=7839;

ORA_ROWSCN ENAME
---------- ----------
353845494 SABDAR

Here we got the scn value (353845494) of the empno record number 7839 when updated. Now we have to use the SCN_TO_TIMESTAMP function to convert an SCN, like ORA_ROWSCN 353845494 to the corresponding TIMESTAMP value.

SCN_TO_TIMESTAMP

SCN_TO_TIMESTAMP is a new function, in Oracle 10g, which is used to convert the SCN value generated, using ORA_ROWSCN coumn, into timestamp. SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN.

Here we pass the scn value generated in the above query.

SQL> SELECT scn_to_timestamp(353845494) FROM emp WHERE empno=7839;

SCN_TO_TIMESTAMP(353845494)
-----------------------------------
02-SEP-08 03.20.20.000000000 PM

SCN_TO_TIMESTAMP function can also be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.

SQL> SELECT scn_to_timestamp(ORA_ROWSCN) FROM emp WHERE empno=7839;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
------------------------------------
02-SEP-08 03.20.20.000000000 PM

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

The books, guides, and materials used for my OCA/OCP/OCE Exam Preparation.

Dear All,

As I’m an Oracle Certified Professional on multiple tracks i.e. Oracle Database 10g/9i/8i, Oracle Database 10g RAC Expert Certified, and Oracle 11i E-Business Suite (Application) DBA. So, I have been asked many times in the OTN Forums and by the emails from my blog viewers that "How do you prepare for the certification exams?" And "what advice/tips/suggestions do you have for the certification candidates?"

Even if I have answered to those questions many times, this time, I wanted to have my advice/tips/suggestions posted in my blog for the certification candidates.

01) In addition to any available book in the market for OCA/OCP preparation, I would strongly advice and suggest using Oracle Student Guides/Oracle ILT Materials for your OCA/OCP preparation.

02) Most of the theoretical and logical questions in the real exam are asked from these guides/materials concepts or chapters.

03) These student guides are more simplified and very easy to understand, and have very précised and handy information.

04) The information or concepts of Oracle Student Guides/Oracle ILT Materials have been abstracted, prepared, and developed using the bunch of Oracle Documentations.

05) For writing Oracle Certification Exams, it's very hard to prepare the whole repository of Oracle Documentations. That's why; Oracle University prepared the précised and concise Student Guides/ITL Materials.

06) After finishing thoroughly reading these guides, as many times as you can, till you get the concepts from this materials, the next step is preparing the practice exams, before you take up the real exam.

07) In my opinion and my experience in writing OCP Exams, the practice exams of Self Test Software and Transcender are highly recommended for preparing the OCA/OCP/OCE Exams.

08) This will give you the similar look and feel, and the pattern of the real exam, and helps you manage in real exam.

09) All most of all the practice questions from these test engines (Self Test Software and Transcender) are developed and derived from Oracle Student Guides/Oracle ILT Materials Concepts.

10) Never mug up the practice exam questions and answers, instead try understanding the question and the answer, if needed for any syntax or command sort of questions, do the paper work i.e write the question, syntax or command, and answers in the paper to remember and understand.

11) Concisely what I would suggest is, read the Oracle Student Guides/ILT Materials meticulously as much as you can and do more practice the questions of the test engines (Self Test Software and Transcender) for your Oracle Certification Exam Preparation.

What are the Student Guides/ILT (Instructor Led Training) Materials?
Click here to know about the Student Guides/ILT (Instructor Led Training) Materials

What are the Self Test Software and Transcended Test engines?
Self Test Software and Transcended

For any suggestions, tips, experience of Others on OCA/OCP/OCE, please take a look in the below blog URL.
ORACLE CERTIFIED PROFESSIONALS

And also search in OTN Forums for more questions and answers on Certification doubts/questions.
Oracle Certification Forum

One stop for all your Oracle Certification Program Details.
Oracle Certification Program

Oracle Certifiction Program Candidate Guide

OCP 10g Candidate Guide

Oracle Magazine Inside OCP Columns

List of Topics on OCP Exams

Oracle Certification Preparation (OCP)/Hidden Treasures

OCP Hidden Treasures!!!

How important is to an Oracle Certification?

Happy reading !! and best of luck for your Certification Exams !!

Any comments or suggestions are always welcomed.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

How to perform a full database export using original export/import utility?

Hello,

In my blog, there is a post already available on how to perform a full database export using the new and enhanced feature of Oracle Database 10g Export Utility, Data Pump – expdp/impdp. However, I’m still being requested, by my blog viewers and OTN forum users, to place a procedure on how to perform a full database export using traditional export utility – exp/imp. However, this is not the complete informative post for export/import utility; so, please refer the Oracle Documents links specified at the end of this post.

Before starting off with the steps, to perform the export at the database level, let’s have clear understanding on the following questions.

Can I perform or consider the export of my database as a backup approach?

Absolutely *NO*, because, the import can be done only up to the time the export was originally taken, i.e. the export dump cant be used to import until the point in time (up-to-date) and will not be imported the updates after the export done. This is not acceptable for Production Databases. So, never treat that, taking the export of the database is part of backup strategy of your Production database. In fact, a couple of other approaches are available to perform the backup of your database using the following ways – Cold Backup (Shutdown/Copy/Startup), Hot Backup (ALETER TABLESPACE ….BEGIN/END BACKUP), and RMAN backup methods.

Situations: When and why to perform a full database export.

* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.

Simple Steps: how to perform a full database export using export utility.

* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1

Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

* Start the export with following command and options.

exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log

Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.

Help on Export and Import:

Windows:
C:\> exp help=y
C:\> imp help=y

Linux/Unix.
$ exp help=y
$ imp help=y

References:

Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm

Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787

Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Do not invoke SQL*Plus with a password On UNIX and Linux platforms.

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat.

For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server.

$ sqlplus apps/apps@proddb

Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below.

$ ps -efgrep sqlplus
oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb
oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus

So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials.

Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line.

$ sqlplus apps@proddb
Enter password: ****

Or, even not to expose the username and connecting string.

$ sqlplus
Enter user-name: apps@proddb
Enter password: ****

Or

$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****

And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.

On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.

For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.

Your comments are welcomed.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

Cloning and Refreshing an Oracle Database

Dear Friends and Blog Readers,

I have been asked many times about the Cloning and Refresh process of Oracle Database by emails of blog readers and the users of the Oracle Technology Network (OTN) Forums.

Even though the information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms.

Terms used in this post:

Source System - the system to be cloned - Production
Target System - the newly created (or cloned) system – Non Production
Production Database – PROD
Test Database – TEST
Development Database - DEV

What is a Database Clone?

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will be come severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

How to clone an Oracle Database and different ways of cloning.

There are many possible methods available for cloning a database, but each of them has pros and cons, and significance. Following are the methods.

Using Cold (Offline) Backup:

This is an easy and simple method to perform a clone of a database. This method requires your production database (PROD) needs to be shutdown gracefully, and take the backup of the database related files i.e. Data files, Control files, Redo Log files, using Operating System commands i.e. cp or copy. This is not possible where your PROD database is running 24/7 and should be available continuously for users.

For syntax and the series of steps to perform the clone using cold backup, refer the following URLs from the reference.

References:
http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://www.jlcomp.demon.co.uk/faq/clone_db.html

Using Hot (Online) Backup:

In this method, backup of the database will be done online i.e. without shutting down the database.

For this, your Production Database is must be in Archive log mode. For syntax and the series of steps to perform the clone using hot backup, refer the following URLs from the reference.

Reference:
http://www.quest-pipelines.com/newsletter/cloning.htm
http://www.oralnx.com/index.php/2007/03/22/cloning-an-oracle-database/
http://www.shutdownabort.com/quickguides/clone_hot.php

Using RMAN Commands:

Cloning can also be performed using RMAN Backups and RMAN commands and it’s also an easy method to perform so. The RMAN DUPLICATE command is used to perform the clone. Until Oracle 9i, to clone the database, it is required to be the Source and Target systems should have the same OS i.e. it is not possible to clone across the platform. But as workaround, using export/import can be cloning the database across the platforms. But starting from Oracle 10g the RMAN capabilities have improved immensely. Cross platform cloning/duplicating a database can be done using RMAN CONVERT commands.

For syntax and the series of steps to perform the clone using RMAN Commands, refer the following URLs from the reference.

References:

Creating and Updating Duplicate Databases with RMAN

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564

Cross-Platform Transportable Database: RMAN CONVERT DATABASE

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/dbxptrn002.htm#CHDCFFDI

Creating a Duplicate Database on a Local or Remote Host

http://sabdarsyed.blogspot.com/2007/06/clone-of-database-on-same-host-ie.html

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Pre & Post Cloning Steps/Changes:

* Do *NOT* set the clone database name as good as production database Name.

* It’s *NOT* mandatory to have the initialization parameter values of cloned instance similar to Production Instance.

* It is *NOT* mandatory to have the cloned instance in Archive log mode. Because unnecessarily archive log files are generated, which consume the hard disk space? If at all, the cloned instance crashed and need to be recovered, it can easily be again cloned from the production.

* After the clone, change the system users passwords i.e. SYS & SYSTEM, and for any critical users passwords.

* Disable the jobs which are not required to be run in the cloned instance.

* Change any application users tables from the cloned database which are still referring the Production Database i.e. Server IP, Port Details, Printer Details etc,

Other Useful Links:

OTN Forums on Cloning:
http://forums.oracle.com/forums/search.jspa?threadID=&q=clone+a+database&objID=f61&dateRange=all&userID=&numResults=30&rankBy=10001

Ask Tom Forums:

DB cloning -- what is it and why
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:575623107841

Creating test environment from production box
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:526422273445

Metalink Notes:

Note:245262.1 - Subject: Create a Duplicate Database with NOCATALOG on Same Node
Note:458450.1 - Subject: Steps to Manually Clone a Database
Note:388431.1 - Subject: Creating a Duplicate Database on a New Host.

Conclusion: These are only my views and outlines about cloning and need *NOT* to be the same with others or Oracle. One individual can still follow their cloning procedure documents which are there in place with them. I strongly suggest one to do the changes first in the test/development before doing it on Production. And also, use Oracle Documentations and Metalink Support for any kind of help.

Hope that this post helps you in understanding the Cloning and Refreshing activity of a database.

**Please leave your comments/suggestions about this post**.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/