Oracle DBA and Apps DBA blog

My Interview Published in the Peer-To-Peer Column of Oracle Magazine

2010-02-22T11:55:00.000-08:00

Hello,

This is to share with you all that Oracle Published my interview in the
peer-topeer column of Oracle Magazine (March - April 2010 Edition).

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

How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

2009-08-08T14:07:00.000-07:00

I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml

When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to ASM disk files, for this RCONFIG tool internally invokes RMAN utility to backup the target database to the ASM disk groups, eventually the database is converted to RAC using RCONFIG.

The conversion took almost 9 hours to complete the process, because during the conversion RMAN used only one channel per data file to backup to ASM disks. There was no chance of improving the RMAN copy process by allocating more channels in the input XML file, and also Oracle doesn’t recommend doing other changes in the input XML file.

One thing was observed during the RMAN copy that RMAN is using target database control file instead of recovery catalog, and also using the RMAN default preconfigured settings for that database.

To know the RMAN default preconfigured settings for the database:

$ export ORACLE_SID=MYPROD
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 5 10:21:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: MYPROD (DBID=1131234567)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

Here we see that the PARALLELISM is 1 (default), that’s why the RMAN using only one channel during backing up the non-ASM datafiles to ASM Disk Groups, and were taking 9 hours to complete the backup.

We have changed the PRALLELISM count to 6 (it depends upon number of CPUs you have in the server).

Solution:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

After changing the PARALLELISM count to 6, the RMAN has allocated 6 channels and the conversion process has improved greatly and reduced the downtime drastically to 4 Hours 30 minutes.

Following is the extract of rconfig.log file, this file is located under:

$ORACLE_HOME/db_1/cfgtoolslogs/rconfig
............................................................................
............................................................................
............................................................................
[17:17:16:43] Log RMAN Output=RMAN> backup as copy database to destination '+DATA_DG';
[17:17:16:53] Log RMAN Output=Starting backup at 04-AUG-09
[17:17:16:258] Log RMAN Output=using target database control file instead of recovery catalog
[17:17:16:694] Log RMAN Output=allocated channel: ORA_DISK_1
[17:17:16:698] Log RMAN Output=channel ORA_DISK_1: sid=866 devtype=DISK
[17:17:17:9] Log RMAN Output=allocated channel: ORA_DISK_2
[17:17:17:13] Log RMAN Output=channel ORA_DISK_2: sid=865 devtype=DISK
[17:17:17:324] Log RMAN Output=allocated channel: ORA_DISK_3
[17:17:17:327] Log RMAN Output=channel ORA_DISK_3: sid=864 devtype=DISK
[17:17:17:637] Log RMAN Output=allocated channel: ORA_DISK_4
[17:17:17:641] Log RMAN Output=channel ORA_DISK_4: sid=863 devtype=DISK
[17:17:17:967] Log RMAN Output=allocated channel: ORA_DISK_5
[17:17:17:971] Log RMAN Output=channel ORA_DISK_5: sid=862 devtype=DISK
[17:17:18:288] Log RMAN Output=allocated channel: ORA_DISK_6
[17:17:18:293] Log RMAN Output=channel ORA_DISK_6: sid=861 devtype=DISK
[17:17:20:416] Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[17:17:20:427] Log RMAN Output=input datafile fno=00053 name=/oradata/MYPROD/users_01.dbf
[17:17:20:532] Log RMAN Output=channel ORA_DISK_2: starting datafile copy
[17:17:20:544] Log RMAN Output=input datafile fno=00021 name=/oradata/MYPROD/ users_02.dbf
[17:17:20:680] Log RMAN Output=channel ORA_DISK_3: starting datafile copy
[17:17:20:694] Log RMAN Output=input datafile fno=00022 name=/oradata/MYPROD/ users_03.dbf
[17:17:20:786] Log RMAN Output=channel ORA_DISK_4: starting datafile copy
[17:17:20:800] Log RMAN Output=input datafile fno=00023 name=/oradata/MYPROD/ users_04.dbf
[17:17:20:855] Log RMAN Output=channel ORA_DISK_5: starting datafile copy
[17:17:20:868] Log RMAN Output=input datafile fno=00024 name=/oradata/MYPROD/ users_05.dbf
[17:17:20:920] Log RMAN Output=channel ORA_DISK_6: starting datafile copy
[17:17:20:930] Log RMAN Output=input datafile fno=00011 name=/oradata/MYPROD/ users_06.dbf
............................................................................
............................................................................
............................................................................
[21:29:5:518] Log RMAN Output=Finished backup at 04-AUG-09
............................................................................
............................................................................
............................................................................

[21:39:10:723] [NetConfig.startListenerResources:5] started Listeners associated with database MYPROD
[21:39:10:723] [Step.execute:255] STEP Result=Operation Succeeded
[21:39:10:724] [Step.execute:284] Returning result:Operation Succeeded
[21:39:10:724] [RConfigEngine.execute:68] bAsyncJob=false
[21:39:10:725] [RConfigEngine.execute:77] Result= < version="1.1">

<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="0" >
          Operation Succeeded
        </Result>
      </Response>
      <ReturnValue type="object">
<Oracle_Home>
         /oracle/ora102/db_1
       </Oracle_Home>
        <SIDList>
          <SID>MYPROD1<\SID>
          <SID>MYPROD2<\SID>
        <\SIDList>     </ReturnValue>
    </Convert>
  </ConvertToRAC></RConfig>

Note: For the sake of look and feel format, the above output has been trimmed neatly. You can also observer that 6 channels were being allocated, timings of backup start and end, and the success code end of the rconfig.log file.

References:

To know more about RCONFIG tool and other Metalink references on it, please take a look at the below blog post written by Mr. Syed Jaffar Hussain.

http://jaffardba.blogspot.com/2008/09/my-experience-of-converting-cross.html

Oracle 10g R2 Documentation information on RCONFIG:

http://download.oracle.com/docs/cd/B19306_01/install.102/b14205/cvrt2rac.htm#BABBAAEH

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

Sand Storm in Saudi Arabia...Today.

2009-03-10T05:57:00.000-07:00

Hello,

Today, there was a huge sand storm in Saudi Arabia. This is the first time I have seen such real and live sand storm covering the Riyadh City in Saudi Arabia.

Here are few pics shared in this blog.

Regards,

Sabdar Syed.

ORA-27123: unable to attach to shared memory segment

2009-01-28T04:27:00.000-08:00

Hello,

Of late, in one of our test unix (Sun Solaris) boxes, a database user is trying to connect to the database, but getting an error “ORA-27123: unable to attach to shared memory segment” with permission denied issue.

Initially we thought that there might be an issue with SGA memory area or may be an issue with the shared memory segments and semaphores for the instance allocated. But later we found that the permission on ORACLE_HOME directory got changed accidentally with the full permission.

Here are our findings:

$ sqlplus testusr/password@testdb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 27 20:53:44 2009

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
SVR4 Error: 13: Permission denied

As per Oracle Error Messages:

Error: ORA-27123: unable to attach to shared memory segment
Cause: shmat() call failed
Action: check permissions on segment, contact Oracle support

Check the oracle executable file permission.

$ cd $ORACLE_HOME/bin
$ ls -l oracle
-rwxrwxr-x 1 oracle dba 119582976 Feb 3 2008 oracle

Here the oracle file permission has -rwxrwxr-x i.e. 775, but this file must have the permission -rwsr-s- -x i.e. 6751

Change the permissions for oracle file.

$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle
-rwsr-s--x 1 oracle dba 119582976 Feb 3 2008 oracle

After changing the permissions on oracle executable file, all the users are now able to connect to the database without any errors.

Note: For further information refer the Oracle Metalink Note ID: 1011995.6 Subject: COMMON ORACLE PERMISSION PROBLEMS ON UNIX.

Regards,
Sabdar Syed

How important is having Unix/Linux OS Knowledge for Oracle DBAs?

2009-01-04T05:37:00.000-08:00

Hello,

I wanna wish you all a very Happy New Year 2009 before being discussed on “How Important Having UNIX OS Knowledge for Oracle DBA?”

The main reason for discussing this topic is to encourage the novice or junior DBAs to have command on Unix/Linux Operating System as part of Database Administration. Because, when they were asked how comfortable they are working as DBA on Unix/Linux environment, then their responses are as following.

“They are pretty much comfortable working and administrating the databases on Windows Operating System as they were trained on administrating the database on Windows OS during their DBA Training”

“They are not enough confident in using the Unix/Linux OS commands to administer the databases”

“We didn’t get the chance yet to maintain the database on UNIX environment including Solaris, HP-Unix, IBM AIX, etc.”

“Working on windows environment is easier than working on Unix/Linux environment as it’s only required, on Windows OS, to select the files physically/copy/cut & paste from one location to another, or delete the files, and stopping the services by simply clicking, where as in Unix/Linux, one should use commands manually for the same”

…. and so on.

If anyone is with the above impression about administrating databases on Unix/Linux OS, then here are my suggestions as follows.

First of all, it’s not compulsory for DBAs to have operating system administration skills. But, working knowledge, on both the operating systems including Windows and Unix/Linux, is always value added to Oracle DBAs
The majority of the production database administration on Unix/Linux based operating systems is enormous in the globe, i.e. very large and mission critical databases are being maintained on Unix/LinuxOS.
Obviously, all big companies require the database administrator who’s having good UNIX scripting knowledge and working knowledge of UNIX based environments. It means that the Oracle DBAs, having good working knowledge of UNIX, have demand in the IT market.
Even though the DBAs intervention is manual in terms of creating directories, files, setting up environment variables, and starting/stopping the services in Unix environment, it’s very easy as good as maintaining in Windows environment.
Administration and OS commands are more or less similar on any flavor of Unix/Linux Operating system i.e. Solaris, HP-Unix, IBM AIX, Red Hat, Fedora, Ubuntu, etc., So, start practicing Oracle database administration on Unix/Linux based systems.
Probably, it may be difficult to have Unix OS installed in the personal computer for practical purpose. So, download the free/trail version of Linux (Oracle Unbreakable Linux) from Oracle, install and configure in the PC, and start practicing on Linux. To practice on different Unix/Linux OS flavors, get your pc installed with the VM Ware, where you can set up multiple and different flavors of Unix/Linux OS.
Note: Of course, this post is not for showing the difference between Windows and Unix/Linux Operating Systems as every OS has its own significance, but to encourage the Oracle DBAs to learn the commands of Unix/Linux required administering the databases.

Quick Links:

Download: Enterprise Linux and Oracle VM

Information: Oracle and Linux

Installation Guides: Oracle 10g Instalaltion on Unix/Linux OS

Books: Oracle9i UNIX Administration Handbook

Linux Recipes for Oracle DBAs

Training: Oracle Database 10g: Managing Oracle on Linux for DBAs

Exam: Oracle Database 10g: Managing Oracle on Linux Certified Expert Oracle

** Your comemnts and inputs are welcomed to this post. **

Regards,

Sabdar Syed

I’m an Oracle ACE Award Winner now !

2008-12-18T01:35:00.000-08:00

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!!

2008-12-16T23:38:00.000-08:00

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?

2008-12-13T01:39:00.000-08:00

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

2008-12-11T21:22:00.000-08:00

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

2008-12-03T00:40:00.000-08:00

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$)?

2008-11-24T23:04:00.000-08:00

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.

2008-11-09T01:36:00.000-08:00

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!

2008-11-08T04:28:00.000-08:00

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.

2008-10-12T06:50:00.000-07:00

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

2008-10-03T16:37:00.000-07:00

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?

2008-09-27T06:34:00.001-07:00

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?

2008-09-26T05:01:00.000-07:00

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!

2008-09-24T03:01:00.000-07:00

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!

2008-09-22T03:21:00.000-07:00

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?

2008-09-22T01:03:00.000-07:00

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?

2008-09-15T02:52:00.000-07:00

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?

2008-09-10T05:45:00.000-07:00

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

2008-09-06T06:26:00.000-07:00

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.

2008-09-02T06:18:00.000-07:00

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.

2008-08-27T15:34:00.000-07:00

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?

2008-08-27T05:06:00.000-07:00

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.

2008-08-24T07:32:00.000-07:00

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

2008-08-23T06:42:00.000-07:00

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/

Q&A Session with me in Oracle Certification Professionals' blog

2008-08-21T08:56:00.000-07:00

Dear All,

The Editor of 'Oracle Certification Professionals' blog - http://ebizocp.blogspot.com/ , had a Questions & Answers Session with me and published in his blogspot.

The series of Questions asked as below.

Q#1: Hi Sabdar, on behalf of the OCP blog readers we are delighted to have you today as our featured Oracle Expert. Please tell us something about you, your background and professional experience.

Q#2: New Oracle technologies are being introduced every year. Which technologies excite you most? What is your advice to Oracle professionals for adopting them?

Q#3: You are an Oracle Certified Professional on multiple tracks. How do you prepare for the certification exams?

Q#4: You are a very active member on the Oracle Technology Network Certification forum. Do you think certification plays an important role in the Oracle community? What advice/tips/suggestions do you have for the certification candidates?

Q#5: Your blog is one of the most popular Oracle blogs. Please tell our blog readers what topics do you discuss on your blog?

Q#6: Can you please let us know one habit that has contributed most to your professional success?

Take a look at the below URL where I had already answered to the Questions asked above.

Meet The Experts: Featuring OCP Sabdar Syed

Leave your comments please.

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

A good blog for Oracle OCA/OCP/OCE Certifications

2008-08-18T01:04:00.000-07:00

I found a very good blog (http://ebizocp.blogspot.com/) for them those who preparing for Oracle OCA/OCP/OCE, and seeking advice/suggestion/help in how to prepare for Oracle Certification. Mr. Mohan Dutt (OCP Advisor) is the owner of this blog. He is doing a fantastic job by collecting the wonderful and very useful information related to Oracle Certifications, and the people opinion, experience on the certifications.

I’m gonna add this blogspot in my frequently visit blog list.

Keep up the good work Mohan !

Regards,
Sabdar Syed.

http://sabdarsyed.blogspot.com/

How to prepare for 1Z0-048 - Oracle 10g RAC Certified Expert Exam.

2008-08-06T07:14:00.000-07:00

Dear Friends,

I would like to share my experience with the 1Z0-048 Exam – Oracle Database 10g: RAC Certified Expert, which I have recently taken.

I have passed the 1Z0-048 Exam “Oracle Database 10g: Real Applications Clusters Administrator Certified Expert”

About the 1Z0-048 Exam:

This Exam (1Z0-048) is one of the Certified Expert Programs. The Oracle offers the following list of Certified Expert Exams for Oracle 10g Database.

* Oracle 10g: Managing Oracle on Linux Certified Expert
* Oracle Database 10g: Administering Real Application Clusters Certified Expert
* Oracle Database: SQL Certified Expert

What is Oracle Certified Expert Program?

Click Here to know Oracle Certified Expert Program

Here I’m going to talk about the 1Z0-048 Exam - Oracle Database 10g: Administering Real Application Clusters Certified Expert.

Who is eligible for writing the 1Z0-048 Exam?

Those who are already certified in Oracle Database 10g/11g DBA
Or
Those who are *NOT* certified already,
* They may first need to under go one training i.e. Complete one of the approved instructor-led online or in-class courses for either the Oracle University or from the Oracle Authorized Centers,
* Write the exam 1Z0-048 - Oracle Database 10g R2: RAC for Administrators.
* Submit the Hands On Course Requirement Form

For more information on this, please take a look at the below URL
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=186

To know more on the following

* Recommended Training and Preparation
* Additional Information & Resources
* Exam Topics

Take a look at the below URL.
1Z0-048 Exam Details

The Books and Documents I referred.

I have gone through all the ILT (Instructor Led Training) materials of the course “Oracle Database 10g: RAC for Administrators Release 2” which my friend has done this course from Oracle University. These guides have helped me greatly in passing this exam, as all of the questions came from these materials topics. I would strongly recommend and suggest referring the Oracle Instructor Led Training Books/Student Guides/Course Materials on Oracle 10g RAC and also Oracle Documentations.

What are Student Guides/ILT (Instructor Led Training) Materials?
Student Guides/ILT (Instructor Led Training) Materials

Oracle Documentations on Oracle 10g RAC

* 2 Day + Real Application Clusters Guide

* Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

List of useful links for Oracle RAC for reference.

Click here for more Useful Links On Oracle RAC

Questions came from the following topics.

• Settings the Load balancing and failover clauses in the TNS entry, and remote logging parameters.
• Configuring ASM Redundancy Levels i.e. High, Normal and External.
• Adding and enable redo log groups on new node of the cluster. (remember the order of steps to perform)
• Undo Tablespaces in RAC.
• In what situations, we migrated from single
• Checking OCR Resource status and checking the relevant logs.
• Storage types which supports FRA (Flash Recovery Area).
• Post steps after the cluster database creation.
• Backing up the OCR and Voting Disks and recovering them (remember the order of steps to perform)
• ASM Instance Parameters.
• Benefits and implication of RAC.
• Utilities to verify Cluster and OCR Disks.
• Different Oracle Homes locations i.e. ORACLE_HOME,ORA_CRS_HOME,ORACLE_BASE
• Tools you use for patching on RAC environments.
• Using crsctl, srvctl, crs_stat with different attributes.
• Moving from non-rac database to RAC instances. (remember the order of steps to perform)
• Debugging CRS, modifying services
• Views to find the interconnect latencies.
• Difference processes of RAC.
• Removing and Adding the node in the cluster (remember the order of steps to perform)
• Location of server callout and notification scripts.
• Enable debugging for CRS, patching and rolling patch on RAC.

Many other Questions from the following things

OCR, Voting Disks,
RAC and Application VIP,
Services, DBCA, ASM
crsctl,srvctl,crs_stat,ocrcheck,

Others discussion on the 1Z0-048 Exam in OTN Forums.

Click here on Discussion of 1Z0-048 Exam

To know more on all the available Oracle Certifications and navigate yourself on the following options.

* Getting Started
* Certification Paths
* Exams
* Already Certified
* Support

Oracle Certification Program

OTN Forums on Certifications

Oracle Certification Forum

Update: Read my other post on "The books, guides, and materials used for my OCA/OCP/OCE Exam Preparation."

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

Leave the comments if this post provides the relevant information and helps you.

Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com/

I have passed the 1Z0-048 Exam “Oracle Database 10g: Real Applications Clusters Administrator Certified Expert”

2008-08-06T01:59:00.000-07:00

Dear Friends,

I’m glad to say all of my blog viewers that yesterday evening I cleared the Oracle RAC Certified Expert Exam (1Z0-048 - Oracle Database 10g: Real Applications Clusters Administrator Certified Expert).

I would like to share my experience, preparation, references, other information on that exam in my next blog post, till then stay tuned here.

Update: Read my other posts on

How to prepare for 1Z0-048 - Oracle 10g RAC Certified Expert Exam.

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

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

Grow That DBA Career

2008-07-15T07:33:00.000-07:00

Hi Friends,

Today I have come across a good document called "Grow That DBA Career" written and published by Brian Peasland. This document has good details about the following Questions.

Should I Become a DBA?
How do I get that first DBA job?
I just got my first job! now what?
How do I go from a beginner DBA to an intermediate DBA?
How do I go from an intermediate to a senior DBA?

If any one is interested to download this document, the use the below link.

http://www.peasland.net/Grow%20That%20DBA%20Career.zip

Leave me your comments if this document clears your DBA Career Doubts.

Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

A New Saudi Arabian Oracle User Group launched

2008-05-10T23:03:00.000-07:00

Hello,

Here is Good News for Saudi Arabian Oracle Users that new Oracle Group website for Saudi Arabian Users has been launched. Those who (Saudi Arabian’s) have not been registered yet or become member of it; it’s a good chance for them to visit their website and register yourself.

http://www.saoug.org.sa/

More details on

http://www.ameinfo.com/149782.html

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

How to check the Physical RAM and Swap Space in Unix/Linux Systems.

2008-04-22T08:47:00.000-07:00

Following are the most requred OS commands while installing and configuring Oracle Software on Unix/Linux Operating Systems.

Operating System: AIX 5L Based Systems (64-Bit)
Physical RAM: # /usr/sbin/lsattr -E -l sys0 -a realmem
Swap Space: # /usr/sbin/lsps -a

Operating System: HP Tru64 UNIX
PPhysical RAM: # /bin/vmstat -P grep "Total Physical Memory"
Swap Space: # /sbin/swapon -s

Operating System: HP-UX Itanium
Physical RAM: # /usr/contrib/bin/machinfo grep -i Memory
Swap Space: # /usr/sbin/swapinfo -a

Operating System: HP-UX PA-RISC (64-Bit)
Physical RAM: # grep "Physical:" /var/adm/syslog/syslog.log
Swap Space: # /usr/sbin/swapinfo -a

Operating System: IBM zSeries Based Linux, LinuxItanium/POWER/x86/x86-64
Physical RAM: # grep MemTotal /proc/meminfo
Swap Space: # grep SwapTotal /proc/meminfo

Operating System: Solaris SPARC 64-Bit/x86/x86-64
Physical RAM: # /usr/sbin/prtconf grep "Memory size"
Swap Space: # /usr/sbin/swap -s

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

Download Oracle 11g New Features PDF.

2008-03-23T11:10:00.000-07:00

Dear Blog viewers,

Today, when I'm giving answers to in OTN forums, I came across a link for Oracle 11g New Features Document, prepared by Ahmed Baraka.

Below is the link for downloading Oracle 11g New Features pdf.

Oracle 11g New Features for Administrators !

Few more documents available in his website.

Ahmed's Other Documents

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

My New Wordpress Blog !

2008-03-22T14:03:00.000-07:00

Dear Friends,

I have started another new blog in wordpress. This blog is exclusively for RAC Basic Administration.

Take a look at my new blog: http://syedracdba.wordpress.com/

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

Preparing for RAC & Oracle Database 11g Upgrade Exams.!

2008-02-22T08:03:00.000-08:00

Friends,

I'm now preparing for Oracle RAC 10g (1Z0-048) and Oracle Database 11g (1Z0-050) Exams. For this I'm preparing Oracle Documentations.

If anybody wants to share their valuable experience/suggestions/materials/student guides (ILT) on these exams, kindly send me.

Thanks in advance to all !.

Regards,
Sabdar Syed.

New Job and New Assignments !

2008-02-22T07:56:00.000-08:00

Hi Friends,

It’s been quite a long time that I’m updating my blog.

Well, I have left the university and joined a new organization; it’s a first bank in Kingdom of Saudi Arabia. In the bank, we have more challenging jobs, assignments, and cutting edge technologies.

I will continue updating my blog from now onwards.

Regards,
Sabdar Syed.

Oracle EBS R12 Applications DBA Beta Exam Released !!

2007-12-19T00:42:00.000-08:00

Dear All,

I got an email from Oracle about Certification on Oracle EBS R12 Applications DBA Beta Exam. I wanna share the info about that exam.

Why take a Beta Exam?

1. Significantly reduced cost! $50 USD (savings of $145 USD)
2. If you pass the beta exam, your record will indicate a passing score and you will receive credit toward certification for this exam. Therefore, you will not need to retake the final released version of the exam.
3. The beta exam is a great way to identify your strengths and weaknesses in the exam content being tested.

Oracle University is pleased to announce the availability of the following new Oracle E-Business Suite (R12) certification exam in beta version:

Oracle EBS R12: Install, Patch and Maintain Applications [1Z0-238]

This new exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification.

By participating in this beta exam, you have the opportunity to provide the Oracle Certification program with feedback about exam content which is integral to the process of exam development. We depend on the contributions of experienced professionals and developers as we continually improve exam content and maintain the value of Oracle certifications.

There are a limited number of seats available for the beta exam. The beta version of the exam is only available for a limited period as well. Take advantage of this opportunity and book your seat today.

Note: You will be given 3-3.5 hours to complete the beta exam. Please plan accordingly.

Testing is held at Prometric testing centers worldwide, although this exam may not be available in all countries. You must register at least 24 hours prior to taking the exam. Contact your exam provider for seat availability. (Please note: all testing centers will have the capability to offer this exam in its live version.)

Please read the following registration information carefully as schedules vary depending on which testing center you choose:

Registration begins:
December 13, 2007

Beta exam period runs:
January 03 – April 04, 2008

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

Regards,
Sabdar Syed,

http://sabdarsyed.blogspot.com

It's gonna be a year by today !!!

2007-12-12T10:34:00.000-08:00

Hi All,

It's been a year that I have started updating and maintaining my blog.

I will more update my blog continuously with Oracle Stuff.

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

Wikipedia on OCP, Oracle DBA and Oracle Apps DBA

2007-11-25T02:23:00.000-08:00

Hi Friends,

I do get mails from newbie asking that what is OCP, Oracle DBA, and Oracle Apps DBA, and their responsibilities?. The Wikipedia has good information on these questions. Please follow the links given below.

Database administrator
http://en.wikipedia.org/wiki/Database_administrator

Oracle Apps DBA
http://en.wikipedia.org/wiki/Oracle_Apps_DBA

Oracle Certification Program
http://en.wikipedia.org/wiki/Oracle_Certification_Program

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

A new book on Oracle Applications DBA

2007-11-25T02:08:00.000-08:00

Hello Folks,

A new book on Oracle Applications DBA

Title: Oracle Applications DBA
Author: Joyjeet Banerjee
Publisher: Tata McGraw-Hill
ISBN: 9780070621121

Tata Mc Graw hills have just now launched a book on Oracle Apps DBA. This book has already been released in India and expected to release worldwide in a couple of months. This book covers both 11i and R12 and maintains a well balance between them. This book is embellished with over 350 screenshots and illustration making the manage of complex Oracle Applications system in a easy way.

This book has got the information on the below Contents.

Chapter Details

The book contains the following chapters
1. Oracle E-Business Suite: An Overview
2. Oracle Application Architecture
3. Oracle Application File System
4. Installing Oracle Applications
5. Checking and Configuring Oracle Applications
6. Managing Oracle Applications
7. Concurrent Processing and Concurrent Manager
8. Patching Oracle Applications
9. AD Utilities
10. Cloning
11. Oracle Application Manager
12. Upgrading Oracle Applications
13. Printer Setup
14. Managing Application Security

Referernces:

http://www.grenleaf.com/idtitle.aspx?titleid=709
http://en.wikipedia.org/wiki/Oracle_Apps_DBA

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

Oracle Database 11g R1 Software available for all OS's

2007-11-12T00:50:00.000-08:00

Hello All,

Oracle Database 11g Release 1 (11.1.0.6.0)
Standard Edition, Standard Edition One, and Enterprise Edition is now available to download and install on all Operating Systems i.e. Windows, Linux, Solaris, AIX, and HP – UX.

Please go through the following link to download the software.

http://www.oracle.com/technology/software/products/database/index.html

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

Register free for the Oracle Database 11g Launch Event in the Middle East, Riyadh, Saudi Arabia.

2007-11-09T21:45:00.000-08:00

Hello Friends,

I have got an email from Oracle that they are gonna conduct a free even on Oracle Database 11g Launch in the Middle East – Riyadh, Saudi Arabia on 19-November-2007.

Fore more details on Venue, Agenda, date and time, and Registration (Free). Please take a look at the below URL.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=69391&src=5659263&src=5659263&Act=10

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

Back to work from the vacation

2007-11-05T23:04:00.000-08:00

Hello All,

I’m now back to Riyadh, Saudi Arabia after I enjoyed my vacations. From now on wards, I will be continuously updating my blog with new posts.

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

Going on vacation and Happy Ramadan Kareem

2007-10-01T04:29:00.000-07:00

Dear Blog Readers,

This is to inform my blog viewers that I’m going to India on my annual vacation for a month (from October to November). So, I would be missing my blog, Oracle Discussion Forums, and Other Oracle Forums.

Well, this time we have planned to celebrate the festival RAMADAN - EID with all our family members, even my elder brother (Syed Jaffar Hussain) has just returned from the two countries Sweden and Denmark after giving his successful one day seminars on Oracle RMAN Concepts. So, he is also gonna come to India on his vacations with his family.

We wish you all “Happy Ramadan Kareem” !!!

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

Oracle Database 11g New Features for Administrators (1Z1 - 050 Beta Exam)

2007-09-30T02:57:00.000-07:00

Dear friends,

The Beta Exam on Oracle Database 11g New Features for Administrators is now available.

Exam Number: 1Z1-050 ( Beta Exam )
Exam Registration: Pre-registration begins September 29, 2007.Beta Exam available November 1 – December 15, 2007
Exam Price: Beta Exam $50 US
Duration: 190 minutes
Number of Questions: 180-220
Passing Score: TBD
Candidates will receive a beta exam score report approximately 10 weeks after the close of the Beta Exam.

For more information on training, preparation, exam topics and other information, please take a look at the below URL.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_050

Preparation Books/Documents:

http://www.amazon.com/OCP-Oracle-Database-11g-Administrators/dp/0071496823

http://www.amazon.com/Oracle-Database-11g-New-Features/dp/0071496610/ref=pd_sxp_grid_pt_0_0/102-4078405-8004159

*** When you take this exam, please share your experience with this exam in this blog as comments ***

Thanks & Regards,
Sabdar Syed,

http://sabdarsyed.blogspot.com

How to find the ORACLE_HOME path in Oracle Database?

2007-09-10T02:40:00.000-07:00

In 9i:

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';

In 10g:

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Linux/Unix:

echo $ORACLE_HOME

Windows:

start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE

Type the below command at prompt if the environment variable is defined for ORACLE_HOME

c:\> set oracle_home (ENTER)

Update: Take a look at my other post on "Setting an Oracle Environment variable – ORACLE_HOME"

My blog has been added in Oracle Blogs list !!

2007-09-01T22:51:00.000-07:00

Dear Friend,

This is to inform you that my blog http://sabdarsyed.blogspot.com has been reviewed by Oracle and added in Oracle’s official web site http://blogs.oracle.com/ where all most all top blogs, on Oracle Technology, have been registered.

Happy Reading !!

With Best Regards,

Sabdar Syed,

Received My Oracle 11i Apps DBA Certification yesterday !!

2007-08-28T22:52:00.000-07:00

Dear Friends,

This is to tell you that, I have received my “Oracle E-Business Suite 11i Certification” right after one month as I took the “1Z0-233 - Oracle 11i Install, Patch and Maintain Applications” last month. It’s titled in that as “Oracle E-Business Suite 11i Applications Database Administration Certified Professional”

Refer my blog for the exam details i.e. “1Z0-233 - Oracle 11i Install, Patch and Maintain Applications”

http://sabdarsyed.blogspot.com/2007/07/i-have-passed-1z0-233-exam-oracle-11i.html

---
Best Regards,
Sabdar Syed,

http://sabdarsyed.blogspot.com

Installation of Oracle 11g on Linux (Red Hat Enterprise Linux – 4)

2007-08-27T00:55:00.000-07:00

Dear Friends,

As we all aware that Oracle 11g is now available for installing on Linux. Yesterday, I installed & configured the Oracle 11gR1 (11.1.0) and created the database on Linux (Red Hat Enterprise Linux – 4.0) successfully. I would like to share the steps with my blog viewers what I have followed to install it.

Operating System Details:
Linux – (Red Had Enterprise Linux – 4.0)

Database Version Details:
Oracle 11g Release 1 (11.1.0)

Download Software: (linux_11gR1_database.zip)
http://www.oracle.com/technology/software/products/database/index.html

Pre-Installation Tasks:

* Prepare the stage area for Oracle Software on the Serve
----- Choose the mount point where software needs to be staged.
/u01

----- Create the stage directories and change the ownership and permissions.
Su – root
Password: xxxxx ( Enter your root password Here)
# mkdir –p /u01/11gStage
# chown –R oracle:dba /u01/11gStage
# chmod –R 755 /u01/11gStage

----- Copy the software in the stage area
ftp the downloaded Oracle 11g software in this stage area and change the ownership and permissions.
$ cd /u01/11gStage
$ chown oracle:dba linux_11gR1_database.zip
$ chmod 755 linux_11gR1_database.zip

* Performing the pre-installation tasks
----- Check the size of physical RAM
$ grep MemTotal /proc/meminfo
Note: During the installation it will say failed when it checks the available physical RAM is less than 900 MB , no problem even if you have less than 900MB (if it’s for testing purpose), you can continue with the installation.

----- Check the size of SWAP space
$ grep SwapTotal /proc/meminfo

----- Check the free disk space in “/tmp” directory
$ df -k /tmp

----- Check the free space available on the system
$ df –k

* Checking the Software Requirements
----- The version of Linux is installed
$ uname –a

----- Check the following required packages are installed.
binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

----- To check RPMS are installed or not.
Login as ROOT user
# rpm –q libaio-devel-0.3.105-2
# rpm –q unixODBC-devel-2.2.11
……………………………
……………………………
Like this check for all the above RPMS.

----- Install the missing packages or rpms.
First download the missing RPMS from the relavent Linux websites, or if you have the CDs with you, used during the Linux Installation, you can use them to find the missing rpms from that.

----- To install or upgrade RPMS are installed or not.
Login as the ROOT user
# rpm –ivh unixODBC-devel-2.2.11.3-1.i386.rpm
Or
# rpm –Uvh unixODBC-devel-2.2.11.3-1.i386.rpm

* Creating Required Operating System Groups and Users
----- Check and create DBA group
Su – root
Password: xxxxx ( Enter your root password Here)
# cat /etc/group|grep dba
# /usr/sbin/groupadd dba

----- Check and create ORACLE user and assign dba group.
# cat /etc/passwd|grep oracle
# /usr/sbin/useradd –d “/home/oracle” –m –g dba –c “Oracle 11g Owner” oracle

----- Set the password of the ORACLE user
# passwd oracle

* Create Required Directories
----- The Oracle Base Directory and Oracle Home Directories
Su – root
Password: xxxxxx (Enter the password for root here)
# mkdir –p /u01/app/oracle/product/11.0.1
# mkdir –p /u02/oradata

----- Chang the owner and group of the directories
# chown –R oracle:dba /u01/app/oracle/product/11.0.1
# chown –R oracle:dba /u02/oradata

----- Change the permissions of the directories
# chmod –R 755 /u01/app/oracle/product/11.0.1
# chmod –R 755 /u02/oradata

* Configuring Kernel Parameters
----- Check the current parameters and values in /etc/sysctl.conf
# cat /etc/sysctl.conf
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/sysctl.conf /etc/sysctl.conf.old

----- Check and add the below parameters in /etc/sysctl.conf
fs.file-max = 76800
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

----- To change the current values of the kernel parameters
# /sbin/sysctl -p

----- Make sure that the parameters and values in /etc/system file are set properly
# cat /etc/sysctl.conf

----- Check and set the following lines in the /etc/security/limits.conf file.
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/security/limits.conf /etc/security/limits.conf.old

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

----- Add or edit the following line in the /etc/pam.d/login file, if it does not already exist
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/pam.d/login /etc/pam.d/login .old

session required /lib/security/pam_limits.so
session required pam_limits.so

* Configuring the Oracle User’s Environment
----- Check and change the contents of .profile or .lgoin files.
$ vi .bash_profile

----- Remove the oracle environment variables set already.

----- Check the DISPLAY variable is set, if not, then set the variable.
$ echo $DISPLAY
$ DISPLAY=:0.0
$ export DISPLAY
Su – root
Password: xxxx ( Enter the root password here)
# xhost +

----- Make sure that xclock command is running
$ xlcok

Installation of Oracle Software:

* Login as oracle on the server
Su – oracle
Password: xxxxx (Oracle user password)

* Go the directory where Oracle software is staged.
$ cd /u01/11gStage

* Unzip the software file
$ unzip linux_11gR1_database.zip

* Run/Launch the runinstaller
$ cd /u02/11gStage/database
$./.runInstaller

* Answer to the subsequent wizards or screens options with the proper information

Note: Fore more instructions one creating the database using DBCA tool or Manual method, please refer my blog link for the same.

http://sabdarsyed.blogspot.com/2007/08/dear-friends-following-are-general.html

Conclusion: I have followed the above series of steps for installing on one of Linux boxes. I would request and suggest them to go through the referenced links below, who are willing to do this with other options during the installation and as per their requirement.

References:

Oracle Database Installation Guide 11g Release 1 (11.1) for Linux

http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/pre_install.htm#CHDHFGBJ

Installing Oracle Database 11g on Linux

http://www.oracle.com/technology/obe/11gr1_db/install/dbinst/dbinst.htm

Oracle 11g Documentations

http://www.oracle.com/pls/db111/homepage

Others:

http://www.datasoftech.com/library.html

****** Your suggestions and comments are welcomed. ******

-----

With Best Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

General Outlines for Installing & Configuring an Oracle Software on Unix/Linux OS

2007-08-14T22:16:00.000-07:00

Dear Friends,

Following are the general outlines (not the commands and statements) which I followed whenever there is a requirement to Install and Configure an Oracle Server on Unix/Linux Operating Systems.

I would like to share them in my blog as a reference for the same.

Pre-Installation Tasks:

* Prepare the stage area for Oracle Software on the Serve
----- Choose the mount point where software needs to be staged.
----- Create the stage directories
----- Copy the software in the stage area
* Performing the pre-installation tasks
----- Check the size of physical RAM
----- Check the size of SWAP space
----- Check the free disk space in “/tmp” directory
----- Check the free space available on the system
* Checking the Software Requirements
----- The version of Unix/Linux is installed
----- The require packages are installed.
----- Install the missing packages or rpms.
* Creating Required Operating System Groups and Users
----- Check and Create OINSTALL group
----- Check and create DBA group
----- Check and create ORACLE user
----- Assign oracle user to the OINSTALL and DBA groups
----- Set the password of the ORACLE user
* Configuring Kernel Parameters
----- Check the current parameters and values in /etc/system or /etc/sysctl.conf file
----- Change the parameters according to the instruction document.
----- Reboot the machine for the effect.
----- Make sure that the parameters and values in /etc/system file are set properly
* Create Required Directories
----- The Oracle Base Directory and Oracle Home Directories
----- Chang the owner and group of the directories
----- Change the permissions of the directories
* Configuring the Oracle User’s Environment
----- Check and change the contents of .profile or .lgoin files.
----- Remove the oracle environment variables set already.
----- Check the DISPLAY variable is set, if not, then set the variable.
----- Make sure that xclock command is running

Installation of Oracle Software:

* Login as oracle on the server
* Go the directory where Oracle software is staged.
* Unzip the software file
* Extract the files
* Run/Launch the runinstaller
* Answer to the subsequent wizards or screens options with the proper information

Post-Installation tasks:

* Login as oracle user on the server
* Set the following variables in the .profile or .login files
---- ORACLE_BASE
---- ORACLE_SID
---- ORACLE_HOME
---- PATH
---- TNS_ADMIN
---- LD_LIBRARY_PATH
* Check the Oracle availability
* Check the listener availability
* Connect to sqlplus and check the users connections
* Tnsping the services
* Check the OEM access
* Any other post-installation required as per the project requirement.

Methods for creating a Database:

The database can be created in many possible ways

* Choose the basic installation with create starter database option when the Oracle Software is being installed
In this method, an Oracle Software will be installed, a starter database with default configuration will be created, and TNS Listner and TNS names will be set up for you.

* Choose the advanced installation option with install software only option.
Only the oracle software will be installed, and no database will be created, and no TNS listenr and tnsnames will be configured for you.

* You will need to create the database in two ways.
Using DBCA by choosing the options and parameters values needed for your requirement.

Other method for creating the database is manual database creation with CREATE DATABASE command.

Configure the listener with Net Configuration Assistant.

Steps for Manual database creation:

* Decide the ORACLE_SID value (i..e PROD, TEST, DEV, etc.)
* Create the password file using ORAPWD command
* Create the service using ORADIM command (Only on Windows)
* Prepare the initialization parameter file with required configuration settings.
* Create the appropriate pre-directories for dump destinations i..e USER_DUMP_DEST, BACKGROUND_DUMP_DEST, CORE_DUMP_DEST etc.
* Prepare the user’s environment profile file with Oracle variables i.e. ORACLE_SID, ORACLE_HOME, TNS_ADMIN, PATH, LD_LIBRARY_PATH etc.
* Login to sqlplus with SQLPLUS \nolog
* Connect as sysdba
* Startup the instance in no mount state
* Run the CREATE DATABASE command or launch the sql script which you have already prepared for database creation.
* Monitor for any errors during the database creation.
* At the end of successful database creation run the following two important scripts which will populate all Performance & Dynamic system views, and System PL/SQL objects.
* ?/rdbms/admin/catalog.sql
* ?/rdbms/admin/catproc.sql
* Optional (?/sqlplus/admin/pupbld.sql)
* Create required tablespaces manually.

Conclusion: The outlines in this article are genric for any Unix/Linux flavor, but I would suggest one to follow the complete document for installation on their specific OS, you can download this from the Oracle official website.

Any comments on this article are welcomed.

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

Oracle Database 11g is now ready for download !!!

2007-08-10T22:39:00.000-07:00

Dear friends,

May be this is a delayed information to you that the Oracle Database 11g is now ready for download. But I wanna share this information with my blog viewers

To download 11g Software:

http://www.oracle.com/technology/software/products/database/index.html

To download 11g Documentations:

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

Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

I have passed the 1Z0-233 exam "Oracle 11i Install, Patch and Maintain Applications"

2007-07-29T22:49:00.000-07:00

Dear Friends,

I have taken an Oracle 11i Applications exam (1Z0-233 - Oracle 11i Install, Patch and Maintain Applications) yesterday, and passed that exam.

I would like to share my preparation for and experience with that exam.

Pre-requisite for 1Z0-233 exam is that one should have earned Oracle9i and 10g Database Administrator Certified Professionals can achieve an upgrade to the Oracle 11i Applications Database Administrator Certified Professional credential through passing the exam Oracle 11i Install, Patch and Maintain Applications - 1Z0-233.

Please visit the following URL for more information.
http://www.oracle.com/global/us/education//certification/appsdba.html

Please note if you are pursuing the Oracle 11i Applications DBA OCP certification based on your prior Oracle 9i DBA or Oracle DBA 10g OCP certification, it is not necessary to submit a Hands On Course Requirement Form or Waiver.

Note: Please register for all your exams with the same Prometric ID. Using multiple ID's can cause delay in your certification and the database will recognize you as two different candidates.

Preparation: I have gone through all the ILT (Instructor Led Training) materials of the course “11i Install, Patch, and Maintain Oracle Applications” which my friend has done this course from Oracle University. These guides have helped me greatly in passing this exam, as all of the questions came from these materials topics.

Questions came from the following topics.

APPS Schema
Shared APPL_TOP
AutoConfig
Validation and Compilation of APPS Schema
Desktop Tier
Worker status flow, Worker log file locations.
Rapid Clone/Install
Server process scripts of Database and Applications.
Patching i.e AutoPatch (Got many questions from this topic)
Applications Top Level directories – APPL_TOP, ORA_TOP,COMMN_TOP,DATA_TOP
Applications Context File
Single Node and Multi Node installation
AdAdmin – Recreate grants and synonyms for APPS schema.
Oracle APPS tablespace model
Syntaxes of adpatch, admrgpch etc
Apps Tier components
Others i.e. Error messages, log files of the utilities, definitions etc.

Note: I would suggest them those who are willing to appear for this exam that read and go through the Instructor Guides on this exam course provided my Oracle Corp/University/Authorized Center. These materials would stand handy for passing this exam.

Update: Read my other post on "The books, guides, and materials used for my OCA/OCP/OCE Exam Preparation."

http://sabdarsyed.blogspot.com/2008/08/books-guides-and-materials-used-for-my.html

Happy reading!!!

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

Oracle Applications Schema Password Change Utility - (FNDCPASS)

2007-07-22T00:25:00.000-07:00

Changing passwords periodically helps ensure database security. Oracle Applications provides a command line utility, FNDCPASS, to set Oracle Applications schema passwords. In addition to changing the schema password in the database, this utility changes the password registered in Oracle Applications tables (FND Tables). This utility can also change Applications End User passwords.

FNDCPASS changes

Oracle Applications Database System Users (APPS, APPLSYS)
Oracle Applications Product Schema Passwords (GL, AR, AP, etc,)
Oracle Applications End User Account Passwords (SYSADMIN, OPERATIONS etc)

Note: the utility, FNDCPASS, cannot be used for changing the database SYSTEM and SYS users. Only users that are registered in FND meta data tables need to be changed using FNDCPASS. Normally, the APPS database user password and APPLSYS password need to be the same. When changing the APPLSYS password using FNDCPASS, the APPS password is also changed.

Syntax of FNDCPASS command:

FNDCPASS logon 0 Y system/password mode username new_password

Where logon is username/password[@connect]
System/password is password of the system account of that database
Mode is SYSTEM/USER/ORACLE
Username is the username where you want to change its password
new_password is the new password in unencrypted format

Example:

$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

$ FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

$ FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

Note: The FNDCPASS has a new mode, "ALLORACLE", in which all Oracle Application schema passwords can be changed in one call. Apply the patch (Patch No# 4745998) to have this option, if not available currently with your Apps.

Syntax:

FNDCPASS 0 Y ALLORACLE

Example:

$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME

To change APPS/APPLSYS password, we need to give mode as SYSTEM
To change product schema passwords, i.e., GL, AP, AR, etc., we need to give mode as ORACLE
To change end user passwords, i.e., SYSADMIN, OPERATIONS etc., we need give mode as USER

Note: Till 11.5.9 there is bug in FNDCPASS, which allows FNDCPASS to change APPS&APPLSYS passwords. Doing so will corrupt the data in FND meta data tables and cause to the application unusable. Because of that it is recommend taking backup of the tables FND_USER and FND_ORACLE_USERID before changing the passwords.

After changing the APPS/APPLSYS or APPLSYSPUB user, following extra manual steps needs to be done.

If you changed the APPS (and APPLSYS) password, update the password in these files:

iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
ORACLE_HOME/reports60/server/CGIcmd.dat

If you changed the APPLSYSPUB password, update the password in these files:

FND_TOP/resource/appsweb.cfg
OA_HTML/bin/appsweb.cfg
FND_TOP/secure/HOSTNAME_DBNAME.dbc

Note: I would you suggest you to first try changing the passwords using FNDCPASS on your test Apps Instances, once you are done with this without any errors or issues then you can move this to production, and also request you to search in metalink for more information about FNDCPASS utility and it's usage.

Happy Reading !

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

Oracle Database 11g Whitepapers and Data Sheets are available on OTN.

2007-07-17T02:46:00.000-07:00

Dear friends,

There are many whitepapers and data sheets on Oracle Database 11g.

Oracle has published many whitepapers and data sheets on Oracle Database 11g. These are available in OTN, below is an URL for the same.

http://www.oracle.com/technology/products/database/oracle11g/index.html

Best Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com/

Installing & Configuring Oracle ASM on Linux – Red Hat Enterprise Linux 4.0

2007-07-02T03:53:00.000-07:00

Dear Friends,

We have set up the ASM (Automatic Storage Management), a new feature of Oracle 10g, on Linux server. In this article, I would like to share the installation and configurations steps what I have followed.

System Details:

Operating System: Linux – Red Hat Enterprise Linux 4.0
Kernel Version: 2.6.9-42.ELsmp
Architecture: 32 bit x86
Database: Oracle 10g
Version: Release 2 (10.2.0.1)

Following are the steps I have followed.

1) Download the ASMLib software packages from the Oracle Technology Network, below is an URL for the same.

http://www.oracle.com/technology/software/tech/linux/asmlib/rhel4.html

2) You may see 4-6 packages for your Linux platform; the oracleasmlib package provides the actual ASM library. The oracleasm-support package provides the utilities used to get the ASM driver up and running. Both of these packages need to be installed.

oracleasm-support-2.0.3-1.i386.rpm
oracleasmlib-2.0.2-1.i386.rpm
oracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpm

Note: Download as per your OS Kernel and Architecture
# uname -r
2.6.9-42.ELsmp
# uname -i
i386

3) Upload the above 3 packages using ftp in Linux server, and log on to the server as root , and install them as below.

$ su – root
Password:

Go to the rpms directory where rpms are downloaded.

# rpm –Uvh oracleasm-support-2.0.3-1.i386.rpm \
oracleasmlib-2.0.2-1.i386.rpm \
oracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpm

Preparing...########################################## [100%]
1:oracleasm-support################################### [ 33%]
2:oracleasm-2.6.9-42.ELsm############################# [ 67%]
3:oracleasmlib######################################## [100%]

ASMLib software packages are now installed successfully.

4) Run the following command to configure ASMLib, outputs of the commands are the self explanatory.

To Configure

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting without typing an answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]

To Start and Stop ASM

# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: [ OK ]
Unmounting ASMlib driver filesystem: [ OK ]
Unloading module "oracleasm": [ OK ]

# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]

5) Making Disks Available to ASMLib

To create a Disk Group

# /etc/init.d/oracleasm createdisk VOL1 /dev/sda2
Marking disk "/dev/sda2" as an ASM disk: [ OK ]

To Delete a Disk Group

# /etc/init.d/oracleasm deletedisk VOL1
Deleting Oracle ASM disk "VOL1" [ OK ]

To query the disks

# /etc/init.d/oracleasm querydisk /dev/sda2
Disk "/dev/sda2" is marked an ASM disk with the label "VOL1"

# /etc/init.d/oracleasm querydisk /dev/sda1
Disk "/dev/sda1" is not marked an ASM disk

To list and query the existing disks

# /etc/init.d/oracleasm listdisks
VOL1

# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device [8, 2]

We are now done with installation and configuration of ASMLib on Linux platform.

Conclusion: This article is specific to one of our Linux boxes, I would request you to refer other documents in Oracle or Google for respective Operating Systems i.e. Windows and Unix.

Reference:

http://www.oracle.com/technology/tech/linux/asmlib/install.html

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

Oracle Database 10g Release 2 (10.2.0.3) for Microsoft Windows Vista

2007-06-26T22:35:00.000-07:00

Dear Folks,

This is to share the information with you that Oracle has released the Oracle 10g R2 (10.2.03) for Windows Vista Operating System.

One can download this from the Oracle official website. Below is an URL for the same.

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10203vista.html

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

Oracle Database 11g is going to be launched on Wednesday, July 11, 2007.

2007-06-23T00:11:00.000-07:00

Dear Friends,

Oracle has announced that “Oracle Database 11g is going to be launched on Wednesday, July 11, 2007”.

To read more about this event, please take a look at an URL given below.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665

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

Clone of a database on the same Host i.e. Server (Linux RHEL -4)

2007-06-19T23:59:00.000-07:00

Dear friends,

As we all know, Cloning of a Database can be done using different backup methods i.e. using cold backup/hot backup (ALTER TABLESPACE <tablespace_name> BEGING BACKUP), and RMAN Backup with DUPLICATE TARGET DATABASE.

Recently I have performed the cloning for one of our University databases using the RMAN backup and DUPLICATE TARGET DATABASE commands, on the same host i.e. same server.

Important Note: It is recommended NOT to clone the production database on the same server or host. But as a practice you can do it for any non-prod instances.

Below are the software details:

DB Name: PROD
DB Version: Oracle 10g Release 2 (10.2.0.1)
OS Version: Red Hat Enterprise Linux (RHEL) – 4

The steps which I have followed for cloning:

Perform the database backup using RMAN.

Login as ORACLE on Linux

$ su – oracle

Password: xxxxxx (Issue the password here)

Check and set the ORACLE_HOME, ORACLE_SID, and other environment variables.

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

$ export ORACLE_SID=prod

$ echo $ORACLE_HOME

Note: Oracle user has a profile (.bash_profile) in which all required environment variables are set, whenever the oracle user logs in to the server the environment variables are set automatically.

Below are the .bash_profile contents.

$ vi. .bash_profile

# User specific environment and startup programs
PATH=$HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin
export PATH
ORACLE_HOME=/u01/app/oracle/product/10.2.0
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
ORACLE_SID=prod
export ORACLE_HOME
export TNS_ADMIN
export LD_LIBRARY_PATH
export ORACLE_SID

:q!

Check if the target database is in Archive log mode or not.

$ sqlplus / as sysdba

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61
SQL>

Here in our case the database is already configured for Archive log mode, and the archive log destination is used by Flash Recovery Area.

If your DB is in no Archive log mode then change the mode to Archive log.

Set FRA (Flash Recovery Area) in your init/spfile.ora

DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G

Startup up the database in mount state

$ sqlplus / as sysdba

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

Connect to RMAN and target instance to perform RMAN backup

$ export ORACLE_SID=prod
$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-: BACKUP DATABASE PLUS ARCHIVELOG:-

This command will take the backup of all database files and archived log files, and place them in flash recovery area (/u03/FRA/PROD/backupsets/__

/, all the backup files contain the extension with .bkp

To check and list the backup taken previously using RMAN’s LIST commands.

RMAN> LIST BACKUP;

Prepare the initialization parameter file (init.ora) for clone database.

Copy the target database init.ora file and rename it corresponding to you clone instance.

$ cd $ORACLE_HOME/dbs

Note: If you do not find the init.ora file for your target instance under the directory $ORACLE_HOME/dbs, may be you are using spfile or the init.ora file might have kept somewhere in another location;

If the spfile is being used, then create the pfile from spfile

SQL> CREATE PFILE FROM SPFILE;

$ cp –p initPROD.ora initTEST.ora

Edit the initialization parameter file (initTEST.ora) and update the following parameters accordingly to your clone instance.

USER_DUMP_DEST=
BACKGROUND_DUMP_DEST=
AUDIT_FILE_DEST=
CORE_DUMP_DEST=
USER_DUMP_DEST=
CONTROL_FILES=
DB_NAME=
INSTANCE_NAME=
SERVICE_NAMES=
DB_FILE_CONVERT_NAME=
LOG_FILE_CONVERT_NAME=

Note: In general, cloned instances are not kept in archive log mode, so you can remove or comment both parameters related to FRA, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Create the password file for clone (TEST) instance.

$ export ORACLE_SID=test

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwtest password=sys

Note: Make sure that the Production (Target) password and Test (Clone) password for orapwd file must be same.

Prepare the listener and tnanemes.ora entries for clone (TEST) instance.

$ cd $ORACLE_HOME/network/admin

Open listener.ora file and add the TEST instance entry as below.

(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)

Open tnsnames.ora file and add the TEST instance entry as below.

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unicorn.kware) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test)
)
)

Check the Prod and Test service can be pinged.

$ tnsping prod

$ tnsping test

Start the clone instance (TEST) in no mount state.

$ export ORACLE_SID=test
$ sqlplus / as sysdba

SQL> startup nomount;

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------------------
test

Connect to RMAN and target & auxiliary instances.

Open a new connection.

$ export ORACLE_SID=prod
$ rman target / auxiliary sys/sys@test

RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
4> DUPLICATE TARGET DATABASE TO 'test';
5> }

Above command output will be a self explanatory, please go through the output generated.

RMAN> exit;

You are now done with clone process.

Checks to be performed after the clone process is finished

Execute the below steps in Test (Clone) instance, and cross check the result with the Production (Target) instance.

$ export ORACLE_SID=test

$ sqlplus / as sysdba

SQL> SELECT OPEN_MODE,NAME FROM V$DATABASE;

SQL> ARCHIVE LOG LIST;

SQL> SELECT NAME FROM V$TABLESPACE;

SQL> SELECT USERNAME FROM DBA_USERS;

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';

If you find invalid objects then you can compile them using $ORACLE_HOME/rdbms/admin/utlrp.sql

Reference:

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

Conclusion:

I have followed the above procedure/steps as per our requirement in the University. I would request you to follow the complete method and other options from referenced link above.

With Best Regards,

Sabdar Syed,
Oracle DBA/ Apps DBA,
Oracle Certified Professional (OCP) 8i, 9i & 10g

My Blog: http://sabdarsyed.blogspot.com

My Metalink Notes!!

2007-06-11T23:17:00.000-07:00

Dear Friends,

I have submitted two articles in Oracle Metalink. They have published my articles in Metalink, below are the details.

Oracle Metalink URL:

https://metalink.oracle.com/

Note ID: 427974.1
Subject : "How We Identified and Corrected a Cloning Mistake"

Note ID: 429674.1
Subject : "How We Used the Oracle DBNEWID Utility to Change the Database Name"

I got a call from the Oracle US Metalink that my articles got selected in the first 20 articles and given gift check for the articles submitted.

Happy Reading !!

Regards,
Sabdar Syed.

How to install Oracle Database 10g Release 2 on Linux x86 (RHEL4 & SLES9)

2007-06-09T03:29:00.000-07:00

Dear Friends,

I have come across an URL in Oracle Web Site for "Installing Oracle Database 10g Release 2 on Linux x86". Below is an URL.

http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

This links talks about the below contents:

Overview
Part I: Installing Linux
RHEL4
SLES9
Part II: Configuring Linux for Oracle
Verify System Requirements
Create Directories
Create the Oracle Groups and User Account
Configure Linux Kernel Parameters
Set Shell Limits for the oracle User
Part III: Installing Oracle
Install the Software
Part IV: Configuring Storage
Filesystems
Automatic Storage Management
Conclusion
Appendix

Happy reading !!

With Best Regards,
Sabdar Syed.

All about the STATSPACK (Statistics Packages) in Oracle 8i, 9i & 10g

2007-06-01T23:49:00.000-07:00

Dear Friends,

By default Oracle provides the STATSPACK information in the form of spdoc.txt under

Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt

Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt

This document talks about the following contents.

TABLE OF CONTENTS
-------------------------------

0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications

Happy reading!!!!

With Best Regards,
Sabdar Syed.

Free Oracle Applications Vision Demo Instances

2007-05-29T05:01:00.000-07:00

Dear Friends,

I have found two Free Oracle Applications Vision Demo Instances so far.

http://www.appshosting.com/login/

http://www.solutionbeacon.com/tools_r11ivision.htm

All you need to do is, register an account in these above sites and start using Oracle Apps Demo instances.

Happy reading!!!

Regards,
Sabdar Syed.

Oracle 9i is gonna be de-supported by July-2007!!!

2007-05-01T23:20:00.000-07:00

Dear Friends,

Oracle Support has announced that the Oracle version 9i is going to be de-supported from their support. Please take a look at an Oracle Metalink Note ID: 161818.1. In this note, you may also find the support summary information of even Oracle 7, 8, 8i, 9i and 10g.

Oracle has also declared that the first year of extended support for 9.2 would be free!!! Please take a look at the following URL for more information.

http://www.oracle.com/features/hp/database-9i-support.html

Regards,
Sabdar Syed.

My manager's new blog in wordpress.com

2007-04-30T00:08:00.000-07:00

Dear Friends,

I would like to share one thing that my boss, Mr. Massoud Al - Shareef (MD of KnowledgeWare Technologies, Saudi Arabia), maintains his own blog http://massoudalshareef.wordpress.com/ , Where he talks about libraries management system ( UNICORN, sirsidynix ), and other related topics on the same.

Well, this blog is designed in Arabic so far. Sorry for english readers !

Happy reading !!!

Best Regards,
Sabdar Syed.

Completed Oracle 10g OCP Certification Today !!!

2007-04-24T08:31:00.000-07:00

Dear Friends,

Today, I have completed "1Z0-040 - Oracle Database 10g: New Features for Administrators" exam, and become an Oracle 10g Certified DBA.

As I have already certified in 9i OCP, it took me about 2 years to upgrade to 10g OCP due to work. So, please do not procrastinate to write 10g OCP, if you are already an OCP 9i.

Best Regards,
Sabdar Syed.

My first technical article in Oracle Metalink !!

2007-04-20T07:23:00.000-07:00

Dear Friends,

My first technical article has been published in Oracle Metalink. Below are details.

Web URL : www.metalink.oracle.com
Metalink Note ID: 427974.1
Subject: "How We Identified and Corrected a Cloning Mistake"

Happy reading !!

Best Regards,
Sabdar Syed

Installing Oracle 10g Software and Building the Database on UNIX and Linux Environment.

2007-04-16T02:51:00.000-07:00

Dear folks,

There is a good link in Oracle for installing Oracle 10g Software, this contains, the basic and advanced installation steps, creating a database using DBCA, and all screenshots of each step.

http://www.oracle.com/technology/obe/10gr2_2day_dba/install/install.htm

Best Regards,
Sabdar Syed.

ORA-12170: TNS: Connect timeout occurred !!

2007-04-16T01:28:00.000-07:00

We have installed Oracle 10g on Windows-XP machine. But when a client (another pc – windows 2003) is trying to connect to the database, then the following error is being generated “ORA-12170: TNS: Connect timeout occurred”

We ensure that the tnsnames.ora file has the correct information i.e. hostname, port, and sid. When we tnsping the sid, it gives “TNS-12535: TNS: operation timed out” error.

So, we had to resolve this problem by turning OFF the Firewall on Server Side (Windows –XP). After that we could successfully connect to the database, and able to tnsping sid as well.

Conclusion: If you are facing these problems due to other reasons, I would request you to take a look at oracle metalink notes for the same.

Best Regards,
Sabdar Syed.

How to Inovke SQLPLUS in scripts in Oracle 10g !!!

2007-04-15T00:40:00.000-07:00

In Oracle 9i we use "sqlplus -s @start_mydb.sql" to start the database. But, where as in Oracle 10g this command simply hangs and will not do anything.

To invoke SQLPLUS in shell/bat scripts in Oracle 10g, We need to give below command.

sqlplus /NOLOG @start_mydb.sql

Regards,
Sabdar Syed.

ORA-16005: database requires recovery

2007-04-04T03:00:00.000-07:00

Due to some reason, I had to bring one of our test databases in read only mode, but the follwoing error generated while doing the same. "ORA-16005: database requires recovery".

I'm sure that we have't done any changes with the database recently. Well, the problem was resolved by shutting down the database in normal mode. Please take a look at the below steps for the same.

SQL> startup force open read only;

ORA-16005: database requires recovery

SQL> shutdown immediate;

SQL> startup restrict;

SQL> shutdown;

SQL> startup mount;

SQL> alter database open read only;

SQL> select open_mode from v$database;
OPEN_MODE

----------

READ ONLY

Conclusion: If you have got the error "ORA-16005: database requires recovery" due to other reasons, I request you to take a look at the metalink note ID: 316154.1

Regards,

Sabdar Syed.

Installing Oracle Applications 11i 11.5.10.2 on SUSE Linux Enterprise Server 9 SP1

2007-04-04T01:46:00.000-07:00

Dear Friends,

Today I have come accross a good link for "Installing Oracle Apps 11i on SLES 9". Below is an URL.

http://www.novell.com/coolsolutions/feature/16009.html

I hope that this link may help you in installing Oracle Application on Linux.

Happy reading !!!!

Regards,
Sabdar Syed

Creating a Recovery Catalog – RMAN in Oracle 10g

2007-04-01T03:37:00.000-07:00

As we all know, RMAN maintains metadata about the target database and its backup and
recovery operations in the RMAN repository. The RMAN repository data is always in the control file of the target database. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. By default this parameter set to 7 days.

Another copy of the RMAN repository data can also be saved in the recovery catalog.

Using a recovery catalog preserves RMAN repository information if the control file is lost, making it much easier to restore and recover following the loss of the control file. (A backup control file may not contain complete information about recent available backups.) The recovery catalog can also store a much more extensive history of your backups than the control file, due to limits on the number of control file records.

In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. Centralized storage of scripts in recovery catalog can be more convenient than working with command files.

Create a new database for RMAN – Recovery catalog database

Note: You can create a small database with minimal sizes of tablespaces and others, and you can name the database as CATDB for naming convention and to avoid the confusion between your production and rman databases.

Create a new tablespace in the new database (CATDB)

$ sqlplus /nolog

CONNECT SYS/passwd@catdb AS SYSDBA;

CREATE TABLESPACE rman
DATAFILE '/u02/app/oradata/rman/rman01.dbf' size 100m;

Create the Recovery Catalog Owner in the new database (CATDB)

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Grant the necessary privileges to the schema owner

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog

Creating the Recovery Catalog

Connect to the database which will contain the catalog as the catalog owner. For example:

$ rman catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:22:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>

Run the CREATE CATALOG command to create the catalog

RMAN> CREATE CATALOG;

recovery catalog created

Registering a Database in the Recovery Catalog

Connect to the target database and recovery catalog database.

$ ORACLE_SID=prod; export ORACLE_SID

$ rman target / catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:25:30 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3677528376)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Make sure that the registration was successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 500 SYSTEM YES /u02/app/oradata/prod/system01.dbf
2 200 UNDOTBS1 YES /u02/app/oradata/prod/undotbs01.dbf
3 325 SYSAUX NO /u02/app/oradata/prod/sysaux01.dbf
4 100 EXAMPLE NO /u02/app/oradata/prod/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 200 TEMP 200 /u02/app/oradata/prod/temp01.dbf

Reference: Please go through the Oracle Documents for RMAN concepts.
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm

Regards,
Sabdar Ssyed.

Changing an Oracle Database Name in Oracle 10g

2007-03-31T00:15:00.000-07:00

As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.

Below is the snapshot the task.

Make sure that you have a whole database backup.

Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.

Ensure to perform clean shut down of a database and startup in mount stage (but not open).

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES

DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.

DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to database UNIC (DBID=3680129277)

Connected to server version 10.2.0

Control Files in atabase:

/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl

Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name

Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.

Connect to SQL* Plus and shutdown the database

SQL> SHUDOWN IMMEDIATE;

Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;

SQL> SHUDOWN IMMEDIATE;

Create a new password file

On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database

SQL> STARTUP;

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV

Backup the whole database again

Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php

Regards,
Sabdar Syed.

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Linux/Fedora core 6

2007-03-24T02:33:00.000-07:00

We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Fedora Core 6. I would like to share the steps followed for this task.

Note: This is only for Oracle Database Server Upgrade.
If you have Oracle E-Business Suite, Physical/Logical Standby database, RAC, and Other separate options (i.e. RMAN) enabled in place, please read the README.html document included in the Patchset (5337014).

Details:

Operating Version Details: Linux localhost.localdomain 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux
Oracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
Oracle Instance Name: testdb
Patchset (to be applied on) : p5337014_10203_LINUX.zip (Downloaded from http://www.metalink.oracle.com/)

Requirements for installing the Patchset # 5337014

Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).

System Requirements
* Operating System must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Note: Installation/Upgrade of Oracle 10g on Fedora Core 6 Is not supported by Oracle, but as a workaround, we had to change the the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
* Oracle Database 10g (10.2.0.1) or later

Preinstallation Tasks

To download and extract the patch set installation software:
* Download the p5337014_10203_LINUX.zip patch set installation archive to a directory "/home/oracle/patches" that is not the Oracle home directory or under the Oracle home directory.
* To unzip and extract the installation files:
$ unzip p5337014_10203_LINUX.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables
Bourne, Bash, or Korn shell:·
$ ORACLE_HOME=/u01/app/oracle
$ ORACLE_SID=testdb·
$ export ORACLE_HOME ORACLE_SID
C shell:·
% setenv ORACLE_HOME /u01/app/oracle
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.

Stopping All Processes for a Single Instance Installation
### To stop Listener Service
$lsnrctl
LSNRCTL> stop
### To stop Enterprise Manager Service
$ emctl stop dbconsole
### To stop iSQLPlus Service
$ isqlplusctl stop
### To Shut Down Oracle Databases
$ sqlplus /nolog
$ connect /as sysdba
SQL> SHUTDOWN IMMEDIATE;

Back Up the System
Oracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.

Installation Tasks

Installing the Oracle Database 10g Patch Set Interactively
** Log in as the oracle user.
su - oracle
password:
** If you are not installing the software on the local system then, run the following command on remote machine:
Bourne, Bash, or Korn shell:
$ export DISPLAY=localhost:0.0
C shell:
% setenv DISPLAY local_host:0.0
Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer (your workstation or PC):
$ xhost + [fully_qualified_remote_host_name]
** To start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software
$ cd /home/oracle/patches/Disk1
$ ./runInstaller
** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.
** On the Summary screen, click Install.
This screen lists all of the patches available for installation.
** When prompted, run the $ORACLE_HOME/root.sh script as the root user.
** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Postinstallation Tasks

Upgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.

Manually Upgrading a Release 10.2 Database

* Required Preupgrade Checks
** Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) "Size in MB"
2 FROM dba_free_space
3 WHERE tablespace_name='SYSTEM';

Size in MB
----------
97.125

We have enough free space in SYSTEM tablespace.
If not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafile
ALTER TABLESPACE SYSTEM
ADD ' /u02/app/oradata/unic/system02.dbf'
SIZE 100M;
## To resize a existing datafile.
ALTER DATABASE DATAFILE ' /u02/app/oradata/unic/system01.dbf'
RESIZE 500M;

* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
##If the system uses an initialization parameter file
If necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

Shut down the database:
SQL> SHUTDOWN

Upgrading a Release 10.2 Database

##Login as Oracle User
Su – oracle
Password:
##Start the Listener
$lsnrctl
LSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
$sqlplus /nolog
SQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF;

Review the patch.log file for errors and inspect the list of components that is displayed at the

end of catupgrd.sql script.

## Restart the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

SQL> @?/rdbms/admin/utlrp.sql

Start All Oracle Related Services

To Check Current Oracle Version

SQL> select * from v$version;
BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for Linux: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production

Common Errors:

** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:

OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 2
10.2.0.3

The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.

** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:
ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.

$sqlplus /nolog
SQL> connect /as sysdba
SQL> STARTUP UPGRADE;

Reference:

Follow the README.html document of patch set # 5337014

Regards,

Sabdar Syed.

WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

2007-03-19T05:32:00.000-07:00

Dear Friends,

In one of our Databases, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file. As per the Oracle Support Documentation, These are warning messages generated in Oracle 10g (Release 2).

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero. Note: In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
------------------------------------ -------------------- --------------------_kgl_large_heap_warning_threshold 2097152 2097152

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using "old-style" init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------_kgl_large_heap_warning_threshold integer 8388608

Regards,
Sabdar Syed.

Database Cloning Mistake !!!!!

2007-03-13T23:44:00.000-07:00

Dear Friends,

The following errors were generated continuously in the test database alert log file.

1). ORA-25153: Temporary Tablespace is Empty

2). GATHER_STATS_JOB encountered errors. Check the trace file.

3). ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist.

But when we query to see the information about temporary tablespace and tempfiles in the database, the following findings were observed.

## To check TEMP tablespace and tempfile details.

SQL> SELECT file_name, bytes FROM dba_temp_files;

no rows selected

SQL> SELECT * FROM v$tempfile;

no rows selected

SQL> SELECT * FROM v$tablespace WHERE name='TEMP';

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
3 TEMP NO NO YES

SQL> SELECT tablespace_name, contents FROM dba_tablespaces WHERE tablespace_name='TEMP';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY

SQL> SELECT DISTINCT temporary_tablespace FROM dba_users;

TEMPORARY_TABLESPACE
------------------------------
TEMP

## Gather Schema Statistics.

In Oralce 10g, when the data of a table is grown to certain threshold value i.e. 10% of data, Automatically Gather Schema Statistics is performed on those tables.And also it needs enough free space in the temporary tablespace for sorting. Due to this reason above messages generated in the alert log file.

Cause: The TEST Database has recently been cloned from PRODuction Database. So, we realized that the step, adding a tempfile to the temp tablespace, was missed mistakenly. In fact the tempfile was copied and it is physically existing in the server, but not added to the TEMP tablespace as part of the cloning.

Solution:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/oradata/test/temp01.dbf' REUSE;

After adding a tempfile to the TEMP tablespace, no errors generated regarding Gather Schema Statistics and temp tablespace afterward !!!.

Note: if you are receiving an error "ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist." due to other reasons. Please take a look at the Oracle Metalink Note ID: 333966.1

This article, written by me, is also available in Oracle Metalink. Note ID: 427974.1

Best Regards,
Sabdar Syed.

Installation of Oracle 10g Release 2 (10.2.0.1.0) on Fedora Core Linux 6

2007-02-27T05:10:00.000-08:00

Dear friends,

After fixing some general errors during the process of installation, I could successfully install and configure Oracle 10gR2 (10.2.0.1) on Fedora Core Linux 6. I would like to share the procedure (step-by-step) of Installation what I followed. Note: In fact Oracle 10gR2 is not supported on Fedora Core 6

Operating System Details:
Linux testserver 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux

Database Verison Details:
Oracle 10g Release 2 (10.2.0.1.0)

Download Software: (10201_database_linux32.zip) http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html

Pre-Installation tasks:

Creating DBA group and ORACLE user account in Linux

Login to the System as ROOT and execute the below commands.

$ su – root
Password:

# groupadd dba
# useradd –d "/home/oracle" –m –g dba oracle
# passwd oracle

Configuring Kernel Parameters

Check and edit the /etc/sysctl.conf file with the following lines.

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Note: Make sure to reboot the system or run the command " /sbin/sysctl –p" to change the kernel parameters.

Add the following lines to the /etc/security/limits.conf file:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

Make sure that " SELINUX=disabled" in the the /etc/selinux/config file

Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4

Creating an Oracle Base Directory

# mkdir –p /u01/app/oracle
# mkdir –p /u01/app/oradata
# mkdir –p /u02/app/oradata

# chown –R oracle:dba /u01/app/oracle
# chown –R oracle:dba /u0 1/app/oradata /u02/app/oradata
# chmod –R 775 /u01/app/oracle
# chmod –R 775 /u0 1/app/oradata /u02/app/oradata

Setting Oracle Environment

Login as Oracle Unix user and change the .bash_profile with the following variables.

Su – oracle
Password:

ORACLE_HOME=/u01/app/oracle
ORACLE_SID=mydb
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=$ORACLE_HOME/network
PATH=$PATH:$ORACLE_HOME/bin

Export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH TNS_ADMIN

Save the bash profile after changing: wq!

Insallation

Login as oracle and unzip the Oracle 10g software

Su – oracle
Password:

$ unzip 10201_database_linux32.zip

Check all pre-req packages are installed, if not install them

To check the packages information: # rpm –q
To install the packages: # rpm –q
Require packages for installation:

rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif openmotif21 setarch pdksh libaio libaio-devel

Important: For successful installation, the rpms "libXp-1.0.0-8.i386.rpm and libaio-0.3.106-3.2.i386.rpm" must have installed. Download these from : http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS

Go to database directory and launch the installer.

$./runInstaller

Provide the required information asked by wizards during installation.

Post – Installation tasks:

Edit the /etc/redhat-release file restoring the original release information:
Fedora Core release 6 (Zod)

I didn't let DBCA to create the default database during the installation; I selected Install Software Only option. After the successful installation of Oracle 10g Software, I created the database manually with my requiremtns. The following are the particulars.

Login as oracel

Make sure that all the required environment variables are Set i.e. ORACLE_HOME and ORACLE_SID etc..

Prepare the initialization parameter with required values

Connect to SQL*Plus as sysdba

$ sqlplus /nolog
SQL> connect /as sysdba

Create the database with the following script.

SQL> startup nomount pfile= /initmydb.ora;

SQL> @$HOME/create_db_manually.sql

create_db_manually.sql file contains

CREATE DATABASE unic
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u01/app/oradata/unic/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oradata/unic/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oradata/unic/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u02/app/oradata/unic/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/app/oradata/unic/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
datafile '/u02/app/oradata/unic/users01.dbf'
SIZE 20M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u02/app/oradata/unic/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u02/app/oradata/unic/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 2000m;

Run Scripts to Build Data Dictionary Views

CONNECT SYS/password AS SYSDBA
SQL>@$ORACLE_HOME \rdbms\admin\catalog.sql
SQL>@$ORACLE_HOME \rdbms\admin\catproc.sql

Errors generated during the installation

>>Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2Failed

Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4

>> Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directoryat java.lang.ClassLoader$NativeLibrary.load(Native Method)at java.lang.ClassLoader.loadLibrary0(Unknown Source)
……
……

Download and install the libXp-1.0.0-8.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm

#rpm –ivh libXp-1.0.0-8.i386.rpm

>> error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Download and install the libaio-0.3.106-3.2.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm

# rpm -ivh libaio-0.3.106-3.2.i386.rpm

References:

http://download-uk.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm

http://www.puschitz.com/InstallingOracle10g.shtml

http://ivan.kartik.sk/oracle/install_ora10gR2_fedora.html

Regards,
Sabdar Syed.

Upgrade Oracle Database from 9.2.0.1 to 9.2.0.7 on Soalris 10 (Sun Sparc)

2007-02-20T04:10:00.000-08:00

As per the requirement I have upgrded one of our test database from the Version Oracle 9.2.0.1 to 9.2.0.7 on Solaris 10 (Sun Sparc)

Details:

Operating Version Details : SunOS libtest 5.10 Generic_118833-23 sun4u sparc SUNW,Sun-Fire
Oracle Version Details (Current) : Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
Oracle Instance Name : testdb
Patchset (to be applied on) : p4163445_9207_solaris64.zip (Downloaded from www.metalink.oracle.com)

Requirements for installing the patchset # 4163445

Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.4 (This includes in the patchset).

System Requirements
* Operating System is Solaris 10 (But it can be Solaris 8 or 9)
* Oracle9i release 2 (9.2.0.1.0) or later

Preinstallation Tasks

Upgrade and SYS Schema

During an upgrade from release 9.2.0.1 or later, the catpatch.sql script can take a long time if there are statistics for the SYS schema. Delete the statistics on all of the objects in the SYS schema, and then recollect the statistics after normal database open, if necessary.

To drop and re-create the statistics.
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

Download and Extract the Installation Software

To download and extract the patch set installation software:
Download and extract the p4163445_9207_solaris64.zip patch set installation archive to a directory " /oracle9i/9207_patch"

To unzip and extract the installation files:
$ unzip p4163445_9207_solaris64.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables

To set the ORACLE_HOME and ORACLE_SID environment variables:
Bourne, Bash, or Korn shell:

$ ORACLE_HOME=/oracle9i
$ ORACLE_SID=testdb
$ export ORACLE_HOME ORACLE_SID

C shell:

% setenv ORACLE_HOME /oracle9i
% setenv ORACLE_SID testdb

The oracle_home is the Oracle home directory where the Oracle9i installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.

Shut Down Oracle Databases

Shut down any existing Oracle database instances with normal or immediate priority.
SQL> SHUTDOWN IMMEDIATE;

Stop All Processes

Stop all listener and other processes running in the Oracle home directory where you want to install the patch set.
LSNRCTL> stop

Back Up the System

Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.
We took the backup of Oracle home and all the database files.

Installation Tasks

Installing the Patch Set Interactively

To install the patch set interactively:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* If you are not installing the software on the local system, enter the following command to direct X applications to display on the local system:
Bourne, Bash, or Korn shell:
$ DISPLAY=libtest:0.0 ; export DISPLAY
C shell:
% setenv DISPLAY libtest:0.0
Here, local_host is the host name or IP address of the system that you want to use to display the Installer (your workstation or PC).
* Enter following commands to start the Installer where patchset_directory is the directory where you unzipped the patch set software:
$ cd /oracle9i/9207_patch/Disk1
$ ./runInstaller
* On the Welcome screen, click Next.
* On the Specify File Locations screen, click Browse next to the Path field in the Source section.
* Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. i,e
/oracle9i/9207_patch/Disk1/stage/products.xml
* In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
Name: Home1
Path: /oracle9i.
* On the Summary screen, click Install.
* This screen lists all of the patches available for installation.
* When prompted, run the $ORACLE_HOME/root.sh script as the root user.
* On the End of Installation screen, click Exit, then click Yes to exit from the Installer.

Postinstallation Tasks

Required Postinstallation Tasks

Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
SQL> alter database datafile '/u02/oradata/testdb/SYSTEM01.DBF' resize 500m;
Database altered.

Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

* Start the database:
SQL> STARTUP;

* If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;

* This command displays the name and location of the server parameter file or the initialization parameter file.
Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE

* If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

* If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

* If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

* Shut down the database:
SQL> SHUTDOWN

Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
* Log in as the Oracle software owner (typically oracle).
su - oracle

* Start the Oracle Net listener as follows:
$ lsnrctl start

* For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA

* Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

* Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.

* This list provides the version and status of each SERVER component in the database.

* If necessary, rerun the catpatch.sql script after correcting any problems.

* Restart the database:
SQL> SHUTDOWN
SQL> STARTUP

* Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql

Conclusion: It is importnat note that We have followed the above process as per our requirement and perticular limites to our test instance. But this is not the complete upgrade procedure, please refer the read me document of README for 4163445 " Oracle9i Patch Set Notes Release 2 (9.2.0.7) Patch Set 6 for Solaris Operating System (SPARC 64-bit) " .

SET SERVEROUTPUT ON SIZE UNLIMITED

2007-02-12T04:12:00.000-08:00

One of our PL/SQL Programs, which is supposed to generate about 100000 records at every run with required format output But after running this PL/SQL Program, the error "ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes" is being generated even after we set "SET SERVEROUTPUT ON SIZE 100000".

We are luckily on 10gR2. So, it supports UNLIMITED size i.e "SET SERVEROUTPUT ON SIZE UNLIMITED". We set this in the PL/SQL Program, after that no buffer over flow error generated.

Regards,
Sabdar Syed.

Oracle Applications R 12 Release date announced !!

2007-01-28T05:02:00.000-08:00

Dear All,

Much awaited release of Next Oracle Applications (after 11i) /E-Business Suite has been announced by Oracle. As per Schedule Oracle is releasing Apps R 12 on Jan 31, 2007 / February 1, 2007.

The key Technology changes

On Middle Tier

o Application Server version 1.0.2.2.2 will change to 10g Application Server i.e. 10.1.3.X
o mod_jserv will be replaced by mod_oc4j
o Apache which is part of Application Server mentioned above will be changed from version 1.3.19 to 1.3.34
o Biggest change will be in long running Forms & Reports Version 6i (8.0.6) to Forms & Reports Version 10g i.e. 10.1.2.X
o Java/ JDK version 1.3.X or 1.4.X will be replaced by JDK 1.5.X

On File System

o There will still be three ORACLE_HOME, one for Web Server another for Forms & Reports & third one for Database but with New versions i.e. 10g AS for Web Server, 10g AS for forms & reports, 10g R2 for database.

* There will be new TOP called as Instance TOP

o In Oracle Apps R12, You can see new TOP called INSTANCE_TOP in Application Tier. This TOP will now hold any configuration files (specific to that server), Certificates (If SSL is enabled SSL Certificates & any other certificates specific to that server) and related log files or PID files (for Apache or any other process) and INSTANCE_TOP will be specific to Server.

Regards,
Sabdar Syed.

Oracle E-Business Suite 11i Apps Certified Professional Administrator

2007-01-22T23:56:00.000-08:00

1) Exam Number: 1Z0-235 [Oracle 11i Applications DBA Fundamentals I]
URL: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_235

2) Exam Number: 1Z0-236 [Oracle 11i Applications DBA Fundamentals II]
URL: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_236

3) Exam Number: 1Z0-233 [11i Install Patch and Maintain Oracle Applications ]
URL: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_233

4) Requirements for Oracle E-Business 11i Certified Professional
URL: http://www.oracle.com/global/us/education/certification/ebus11i_coursereq.html

Note: Oracle9i and 10g DBA OCPs can earn the Oracle 11i Applications DBA OCP credential by passing a single exam i.e. Oracle 11i Install Patch and Maintain Oracle Applications - 1z0-233.

http://www.oracle.com/global/us/education//certification/appsdba.html

Best Regards,

Sabdar Syed.

To take the full database export (Oracle 10g):

2006-12-16T04:23:00.000-08:00

Create a Export Directory:
##########################

On Solaris:
-----------
SQL> create or replace directory sys_dmp as '/u02/expdp';
Directory created.

On Windows:
-----------
SQL> create or replace directory sys_dmp as 'D:\expdp';
Directory created.

Create a separate export user:
##############################

SQL> Connect /as sysdba

SQL> CREATE USER expdpadmin IDENTIFIED BY expdp default tablespace users;
User created.

Grant Export and Import Privileges.
###################################

SQL> GRANT CONNECT,RESOURCE TO expdpadmin;
Grant succeeded.

SQL> GRANT exp_full_database to expdpadmin;
Grant succeeded.

SQL> alter user expdpadmin quota unlimited on USERS;
User altered.

SQL> GRANT READ, WRITE ON DIRECTORY SYS_DMP to expdpadmin;
Grant succeeded.

To check on which directories you have privilege to read & write:
#################################################################

SQL> SELECT privilege, directory_name
2 FROM user_tab_privs t, all_directories d
3 WHERE t.table_name(+)=d.directory_name
4 ORDER BY 2,1;

Exporting Full Database:
########################

expdp expdpadmin/XXXXXX full=y directory=sys_dmp dumpfile=full_db_expdp.dmp logfile=full_db_expdp.log

Update: Take a look at my other post on "How to perform a full database export using original export/import utility?"

http://sabdarsyed.blogspot.com/2008/08/how-to-perform-full-database-export.html

Regards,
Sabdar Syed.

Group and User Creation in Solairs.

2006-12-13T00:24:00.000-08:00

This post is for creating an Unix Group and a User.

Solaris 5.10
--------------------------------------
Creating a Group:
--------------------------------------

For instance: Group - dba

Login as ROOT User

login: root
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
#

To Check whther the group is already created.

# grep dba /etc/group
or
# cat /etc/groupgrep dba

If dba group info is not displayed, then

To create a Group:

# groupadd dba

Syntax: groupadd -g

# cat /etc/groupgrep dba

dba:103:

Note: The gid, which needs to be changed, should be not being the gid of other group.
Example:
# usermod -g 333 dba
UX: groupmod: ERROR: 103 is already in use. Choose another.

To remove a Group:

# groupdel dba

# cat /etc/groupgrep dba

Commands:
---------

Create Group - groupadd
Modify Group - groupmod
Remove Group - groupdel

For more details, check the man pages i.e. man groupadd, man groupmod, and man groupdel

--------------------------------------
Creating a User Account:
--------------------------------------

For instance: User - Oracle

Login as ROOT User

login: root
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
#

To Check whther the oracle user is already created.

# grep oracle /etc/passwd
Or
# id –a oracle

If Oracle group info is not displayed, then

To create a User:

# useradd –d /export/home/oracle –m -g dba -s /bin/sh oracle

Note: In the above command
Option -Discription -Value
------------- -------------------- --------------------
-d -Home for the Oracle user -/export/home/oracle
-m -For making directory
-g -Primary Group dba
-s -Default Shell for the User -/bin/sh
Login -Name -Oracle

# grep oracle /etc/passwd
oracle:x:100:100::/export/home0/oracle:/bin/sh

To modify a User:

For example: To Comment the user and assing secondray group to the user

# usermod -c "Owner of the Oracle Software 10g" -G oinstall oracle

Note: In the above command
Option -Discription -Value
------------- -------------------- --------------------
-c -Comment -"Owner of the Oracle Software"
-G -econdary -Group oinstall

# grep oracle /etc/passwd
oracle:x:100:100:Owner of the Oracle Software 10g:/export/home0/oracle:/bin/sh

To set a password:

# passwd -r files oracle
New Password: XXXXXXXX
Re-enter new Password: XXXXXXXX
passwd: password successfully changed for oracle

Note: Where XXXXXXXX is the new password for oracle User.

To remove a user:

# userdel -r oracle

Note: The "-r" option is used to remove the user's home directory from the system.
And the Oracle user account should not be removed until and unles required, and
this should not be done on Production Servers.

# grep oracle /etc/passwd

Commands:
---------
Create a user - useradd
Modify a user - usermod
Rmove a user - userdel

seta password to the user- passwd

For more details, please check the man pages in UNIX i.e. man useradd, man usermod, and man userdel

Regards,
Sabdar Syed.

My First Post

2006-12-12T03:38:00.000-08:00

Hello Everybody,

This is my first post in this blog ! ! !.

Regards,
Sabdar Syed.