Database Administrator is TOP 7 out of 100 BEST Jobs in America

CNN.com partnered with PayScale.com and come out with a list of the TOP 100 best jobs in America. The criteria for selection included great pay and growth prospects.

Guess what, Database Administrators are again in the TOP TEN. It is ranked 7th best out of the TOP 100 in USA.

Apart from Database Administrators, the only other IT job in the TOP TEN is Software Architect.

For a full list, click here.

Oracle DBA Training

Interested in Oracle DBA Training and Oracle Apps DBA (Oracle Applications DBA) Training ?. If anyone is interested in getting trained in these skills, please register for courses offered by DBA University.

All the courses are covered with the latest topics, practical assignments as well as access to a world class LAB environment along with job oriented training.

Both on-site and online training are available.

Database Administrator stands 5th in the 'MOST DIFFICULT TO FILL' IT Job

I wrote in March 2008 (just before the onset of the worst recession in USA) that statistics showed Oracle Database is the skill with the biggest demand in the IT Job Market. Click here to read that post.

With the job market in USA still not recovered fully, the latest July 2010 dice.com monthly job report says that Database Administrators stand 5th in the 'MOST DIFFICULT to FILL' IT Job. This shows that IT organizations continue to have a tough time to find quality Database Administrators even in the ongoing recession in USA.

The same report states that DBAs make $10,000 more than the average IT job paycheck in the United States. Same applies for pay raises. DBAs get double the average yearly pay raises in the USA. Click here to download the latest IT job market report.

CNN.com partnered with the leading job portal www.CareerBuilder.com and ranked DBAs among the TOP TEN most IN-DEMAND professions for the 2009-2016 period with a projected growth rate of 29% per year. Click here for more details.

Question on restoring SPFILE after a complete database server crash

Introduction

I am posting a database recovery related question asked by one my blog's visitors' last week for restoring an spfile. A few additional steps are necessary if the database is using ASM technology.

[06:19] sunil: i had a catalog db and i m taking the bkp of target db in to tape drive, all of a sudden my target server crashed(means i lost everything includibg spfile,now i want to built a new server and want to restore the bkp from tape drive,how it'll happen.

[06:19] sunil: how i can restore my spfile?

Sunil,

It is not very tough to restore the spfile. Please try the below commands to restore your spfile. For simplicity, I am making the following assumptions.

1) You have used RMAN and a seperate CATALOG database for your backup strategy.
2) You still know the DBID of your "crashed" database. If you don't have your backup logfile, you can still find the DBID by querying the RMAN Catalog. Let it be 123456789
3) You have made the tapes containing your backup accessible to the new server.

A)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
startup force nomount;
exit


B)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
run
{
allocate channel t1 type 'SBT_TAPE';
restore spfile;
}
exit

c)

sqlplus '/ as sysdba'
create pfile from spfile;
shutdown immediate;

D)

Restore CONTROLFILE and update PFILE with new controlfile location

E)
The following additional steps must be performed if you have ASM for your database.
Let +ASMDATA be the diskgroup name for storing the spfile.

sqlplus '/ as sysdba'
create spfile='+ASMDATA' from pfile;
exit

cd $ORACLE_HOME/dbs
mv pfile pfile.old

Create a new pfile with the below contents after verifying the exact name of the restored spfile from v$asm_alias view
SPFILE='+ASMDATA/DBNAME/parameterfile/spfile_name_in_asm_alias_view'

rm the spfile on disk in $ORACLE_HOME/dbs directory.

The below command will startup the database using the above pfile which points to the actual SPFILE

sqlplus '/ as sysdba'
startup mount;

ASM for Oracle 11g with R12 E-Business Suite

Introduction

One of my visitors' posed a question in my weekly online chat in this site.

>> Tom asked

Hi Srinivas, we are planning to utilizing ASM 11gR2 DB for R12 ebusiness, is there any document that you can point me to for best practice for setting the ASM disks? Currently have only 2 disks; 1 is DATA and the other is RECOVERY. Data disk is for transaction and RECOVERY is for redo, archive log, etc. Can you point me to the right direction. thanks, Tom.

I am making a blogpost for this question because it is ASM is such an important consideration for today's enterprise databases, both for manageability and high availability of data.

Tom,

You have taken a good decision to implement ASM for your database. We had very good success with ASM and its also fun to work with ASM. Makes your life easy.

You should put even redo also as part of the DATA diskgroup.

1) DATA diskgroup = Datafiles + online redologs + controlfiles
2) RECOVERY diskgroup = just the archived redologs.

Are you using ASM on Linux platform ? ASM is optimized for Linux platform. Oracle supplies an utility called ASMLib for the Linux platform, an utility that further automates ASM management and administration.


Also when you create the diskgroup, I prefer you to use "EXTERNAL REDUNDANCY". Your organization must already be using SAN Fabric for your database. For example, if you use EMC Symmetrix as the SAN solution, you must utilize its inbuilt redundancy feature. So you let EMC handle redundancy instead of Oracle.

RMAN Active Duplicate Advantages

Introduction

We are planning to start upgrading some of our enterprise Oracle databases from 10g to 11g and I am evaluating the new features of Oracle 11g that our organization can be benefited from. A 11g new feature that has caught my attention is the 'ACTIVE Database Duplicate' option in Oracle RMAN. This feature allows a database to be duplicated directly from its live source database instead of using its backup.

Advantages

Instant Database Clones possible (On Demand Cloning)

If one requires a database clone, all one has to do is to run the active database duplicate command after specifying the source and destination databases. I call this Cloning onDemand ! This helps organizations create test database environments in a rapid and simpler fashion.

EMC TimeFinder BCV sync/split is not necessary

Consider an organization relying on BCV sync/split technology to perform database clones. In this case one must take the help of a system administrator to perform the BCV sync/split operation. A database is put in hotbackup mode and the BCV split process is performed. Once the operation is completed, the database is taken out of backup mode. This is all additional work and the Active Database Duplication eliminates all these steps as it performs a direct copy.

Staging ASM Diskgroup is not required

Consider the case when ASM (Automatic Storage Management) is used as the underlying disk management technology for an Oracle database. An ASM-2-ASM database clone always requires a consistent BCV sync/split operation and this during process the ASM disk headers are copied to the destination host. This requires staging (intermediate) ASM Diskgroups on the destination host with the same name as that of the source database's ASM diskgroups. After the consistent BCV sync/split is completed, data is again copied from the staging ASM diskgroups to the clone database's diskgroups(DEV/TEST diskgroup names). Active Database Duplication eliminates the need for staging ASM diskgroups, thus saving a lot of diskspace (equal to the total size of the database being cloned)

No additional space required

In the traditional backup-based database duplication, one has to make the backups accessible to the destination host before the duplication is started. For tape backups, this equates to moving the tape to a drive attached to the destination host or using a network-accessible tape server. For disk backups, this equates to copying the backup to the destination host (additional space). Active Database Duplication does not require backups and hence the additional disk space requirement is no longer needed on the destination host.

Restrictions

Incomplete recovery NOT possible (no SET UNTIL)

Active Database Duplication cannot be used to duplicate a database until a point in time. One has to rely on the backup-based database duplication method if one wants to create a duplicate database until a specific point in time ( a specific time, log sequence or SCN). Furthermore, active duplicate copies data only until the last archived log file in the source database. The contents of the online redologs will not be copied to the duplicate database.

FRM-40734:Internal Error:PL/SQL error occurred

Introduction 

 

It was one of those days when things get delayed and you go past the deadline. Last week I faced a similar situation when an Oracle R12.1 (E-Business Suite 12.1) clone was not completed successfully.  

 

Problem 

 

All the cloning tasks were completed, however, during testing of the cloned instance, I discovered that Oracle Forms was having a problem.

1. Launch any form ( For e.g Login as SYSADMIN=> System Administrator => Concurrent => Requests )
2. The forms window opens, but it goes into an hour glass mode and after a few minutes it displays the following error message at the bottom of the form

FRM-40734:Internal Error:PL/SQL error occurred  

 

Troubleshooting 

 

I searched for the occurrence of the above error in all the logfiles in the $INST_TOP/logs/ora/10.1.3/opmn directory including the forms_default_group_1 subdirectory. But everything seemed normal in these logfiles with no reference to the above error code and message. I also performed a routine check of the $INST_TOP/logs/ora/10.1.3/j2ee/oacore directory, but no clue at all. This seemed to be one of those errors when the logfiles do not give much information for troubleshooting purposes. 

 

Solution 

 

Because of the several steps involved in the cloning process, I was under the impression that I could have done a mistake or missed a step or two. Hence I executed all the cloning steps again. But the issue refused to go away. I used the adadmin utility to run the 'Compile APPS schema', 'Generate form files', 'Compile menu information' and the 'Compile flexfields' programs. But the issue still refused to go away. Finally, we ran the 'Generate product JAR files' adadmin program which helped fix the problem.  

 

Conclusion 

 

So you now know the trick when you face the same symptoms that I faced above !!

Codeline B Patch readme containing Codeline A prereqs and Codeline C postreqs

Introduction 

 

Oracle has introduced the concept of "Codelevels" and "Codelines" in the new E-Business Suite Release 12. The first base release in R12 is 12.0 (also called Point Release). All the patches introduced on top of this point release are said to be on Codeline A. 12.0 is also called the Codelevel A. The next Codelevel in R12 is Codelevel 12.1 and its Codeline is B. Similary 12.2 and Codeline C. Compare this with the E-Business Suite 11i version when Codelevels were instead called as maintenance packs (11.5.8, 11.5.9, 11.5.10 etc.) Furthermore, Oracle says in one of its support notes, that it is "not possible to apply a Codeline A patch on an E-Business Suite system that is already on Codeline B (12.1 point release)". So that should mean that Codeline A patches are not compatible to apply on a Codeline B system.  

 

Problem 

 

Our E-Business Suite system is on 12.1 point release. The other day I was analyzing a patch xxyyzz.R12.AR.B to be applied for fixing an "Accounts Recievables" issue and I noticed a few discrepancies in the patch's README file. 

 

 Issue #1 (Prereqs) 

 

The README mentioned several prerequisite patches to be applied to be applied prior to applying the actual patch. One of the prereqs mentioned as a Codeline A patch aabbcc.R12.AR.A. I checked with the Oracle Support team and they mentioned that its a "typo" in the README and it was immediately taken care of. 

 

 Issue #2 (Postreqs) 

 

The README mentioned several postrequisite patches as well and one of them was a Codeline C patch ! ppqqrr.R12.AR.C. This time the answer from Oracle Support was not an expected one. As per Oracle Support, some of the products on 12.1 release have been using the C codelevel due to an intermediate release of some vertical products falling in the same product line. So in 12.1, one can see some products with the C label too.  

 

Conclusion 

 

Please seek a clarification from Oracle Support (in the form of a Service Request) if you find a patch that contains prereqs and postreqs from different Codelines. Most of the times it could be a typo in the patch README and sometimes it may be a valid case !!!