How many DBA teams should there be in a company? I mean whether the DBA teams should be split to perform specific activities such as support or development ... Does it make sense?

Tom,

Wouldn't you say that the responsibilities of the DBA are different in development and production?

In production, they are responsible for maintaining a secure and stable environment, whilst in development their main role is to provide a service to developers?

Mark

Hi Tom

Thanks for comments from above...

I also think that Dba's should know a lot about Development..or else there would have very little for them to do...unless you are a part-time DBA ..or may be A System Admin + DBA..

Regards

Tom,
What is your opinion in case dev and prod groups are not within the same group? If these groups are split and every group do what they want, won't that create mess?

Tom,

So when my DBA denies a developer access to the V$ views, as he wouldn't give such access in production, I don't have a leg to stand on. I'm not being flippant - this has happened to me!

Mark

Tom,
And what if "prod" dba group forbids the access to the "dev" dba group to the production systems, in the "mess" case when these groups are separated?
Isn't that ridiculous?

Why would prod and dev dba's be in different groups? Shouldn't they be a part of one team?

I have worked in very controlled environments, with very well separated (1)development, (2)UAT and (3)production systems.

Especially in a financial institution, there are lots of PROCEDURES telling you about methodologies, workflows, etc.

For a system to be in production, it would have to be in development first, then it would have to be approved in UAT (User Acceptance Testing) so it could finally end up in production.

All the interfaces between these stages were well documented and should follow strict rules.

In this typical scenario, there could be a "multipurpose" DBA along this workflow -- eg in small environments -- or else a couple of them in each stage.

Besides, all the database work should be accordingly documented and the programs should be catalogued: no one messing with code in production, no way. So one line of code could be changed in production, someone would do it in dev first, then go to UAT and finally to prod.

When code is altered, there is a department called "change management" to take care of cataloguing the new code. The same goes to database objects which have to be under the control of a "data administrator" (not database administrator, the DBA).

Hope this helps !

Tom,

I totally agree that the DBA procedures should be the same within development and production. My point was really about their attitude.

Oh, and congratulations to Andre of Brazil for their World Cup victory over England (and apologies to all Americans who do not understand the beautiful game).

Mark

Hi Tom,
Do you mean by this that all these guys should be in groups within one and only one dba group?
What if for example manager of prod group wants his group to do some development and dev group is not consulted?

</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/nav/getting_started.htm <code>

It is hard to think of anything less likely to produce a serious thought, than a bunch of developers figuring out if DBAs have enough to do; the "grass is greener on the other side of the fence" sure gets a lot of play. As a DBA, let me say that Tom's book, "Expert one-on-one Oracle", is for developers not DBAs, but most developers don't know 1/10 of what is in it. Tom has also pointed out that everybody should read the concepts manuals, and almost nobody has. So before assigning work to DBAs, how about learning what is in Tom's books and the concept manuals? If you did that, you wouldn't need the DBA to explain it to you. As Tom has pointed out, Oracle is not a black box and to create scalable applications the developers need understand how Oracle works. The suggestion that they learn it from DBAs is without merit. Most of what developers do, they do without showing it to DBAs or discussing it with them, and they are not required to take any suggestions from DBAs. Agree that we can fire you and I will agree that we need to raise our standards.

Now I know that Tom encourages the idea that DBAs should know this and that, and I agree with some of his ideas, but it is the developers who write the code, SQL, PL/SQL and the idea that DBAs should (or ever will) know more than a developer is silly. I have worked with, at a major corporation, contractors expert in Visual Basic, and remember many ugly things they did, including asking "What is this SQL stuff?", so a DBA that knows that answer is a help, but is hardly what is needed. And remember, Tom is not perfect, if he were he would not have said "Fire the DBA" as many times as he has, without saying "Fire the developer" at least once.

Developers can mess up and it is a bug. When DBAs mess up there is often a loss of data (yes, I lost a byte one time) and you can get fired (no) for what would be head scratcher for a developer. You are also blamed if the database is slow, or if it runs out of space, and often denied the hardware to adequately test backups, or create boxes to fail over to, and I have had situations where software went from a developer to production with no testing at all. Also, DBAs and developers frequently roll up to different managers, and developers often ask for things that don't make sense and are not approved by any manager, and if they are wrong, they are more likely to keep their job than the DBA that did what they asked. There is a lot more pressure on DBAs as a single mistake can be awful.

So my idea is, read Tom's book (I am doing that also, but you need it more) and read the concept's manuals (both volumes), and learn big parts of both, and then you will not need to blame the DBA for not telling you about bind variables, and will be glad to have a DBA that does backups, can restore in case of failure and keeps your brothers from trashing a production box, which, despite allegations to the contrary, is quite a lot to do. And for my part, I promise to quit saying things under my breath after you announce that your instance "has been trashed by forces unknown" and you want it back to six days ago. Which I can do.

And remember, we never see anyone working as hard as we do, because we (really) only see all of what we do, not what anyone else does, so don't be quick to put stuff on someone else's plate (of course we could agree that if I forget to do something because I was talking to you, you take the blame ... huummm ... I like that idea. Any mistake I make can lead to a firing).

If you want to rely on something, let it be that most people want to be part of something that works, and instead of figuring out in advance how people should work together, treat people as individuals that will work with you if you give them the resources to do so.

Cheers,

Harrison

(Read the books, and I really will stop mumbling under my breath;-)

As Tom has stated, the them (Developers) and us (DBA) mentallity is not really going to ensure a fast, reliable, available system. Rather than have development teams and dba teams in each dev/production area, would it not make more sense to have a unified team of developers and DBA's. If a developer really understood the finer points of managing a DB with respect to e.g. buffers, disk I/O, tuning, recovery, security....the list goes on, then I would be out of a job. The converse is true, if I did a large volume of development...why employ developers? It makes sense to utilise each others specialised knowledge. As far as firing is concerned, if you can't do the job you were employed to do.....
If a DBA runs out of space, loses data etc when this was avoidable, you have to ask the question...would I not be better serving in McDonalds?

Just be proactive, if you sit and wait for developers to ask for help, you'll end up being the mushroom and looking after potentially badly developed apps.
I suppose that makes my job more interesting, fixing messes... :)

I agree that the DBA(s) should be as close to each other and developers. Its about relationship management, make the developers your friends!

Very tough saying fire people, isn't training a better option?? Or hiring a contractor to help short term?
I personally would ask up front of an employer what the grounds for dismissal were. Otherwise you could cop the blame on a bad management decision eg deadline, lack of infrastructure, lack of guts etc :)

Have Fun

Tom
As far as DBA working with developers goes, I have one question:
a) Who does the job of query tuning? DBA or developer?

I am in an environment where most developers have worked with SQL and PL/SQL for the first time. They have never read an Oracle manual, never heard about analytic functions, dynamic SQL, bind variables, bulk binds and never run an explain plan. Their main task is to somehow make the application work. As a result of this, when I review the code, I find majority of code which can be replaced by more efficient constructs, but that would mean a complete code rewrite and testing. Sometimes, when I see something totally untolerable, I advise them about better ways of doing the same thing, but such advise is generally ignored.

Is fixing such bad code a DBA responsibility? The project management seems to think that DBA should fix all the poorly written code even if he has to rewrite it. Is that a fair expectation?
Thanks

Can you put data administrator in the mix and
compare and contrast their roles and responsibilities
with DBAs

Hi

My role as production DBA is basically, getting mails with SQL scripts to run in pre-production and production. Dont have time for more! Doing more than 50 per day due to high number of databases (over 500)

Production DBA is a not very recommended job really

...sounds more like a database operater (DBO). Stand up and be a DBA: write some scripts that process your mail and extract those SQL statements, and automatically submit to as many databases as you wish. Almost all can be automated, on unix platforms of course.

Of course I have thought of making everything automatic, it is simply impossible, when the SQL scripts arrives before run them we must check them, you dont know what kind of script they send you! Plus, we have like 10 software providers so we first try to educate them but obviously if you work with 10 consultancies who then sub-contract people from more consultancies you can probably spend all your life educating them! Basically there is a standard procedure but in practice there are always things which does not fit in these standards

If you work in a big environment you would see running scripts automatically is totally out of question, if we do that we probably spend half of time restoring backups :)

Things to check are such as

owner of objects (sometimes they specify the development user!)
storage parameters
tablespace specifications
check if there is commit in DML scripts, not allowed
check synonym creations
check if they grant privileges to roles and not users

when a guy first arrived here asked us why we dont automize the process, we told him, sure try it. After two months he gave up :)

The definition of Data Administrator in DW is from a book by Sid Adelman et al.

"
The role responsible for the enterprise's data resource and for the administration
control and coordination of all data-related analysis activities. The DA has the
responsibility for planning and defining the conceptual framework for the
overall data environment. The functions of the DA typically include requirements
definition, logical data modeling, data definitions, logical to physical
mapping, maintenance of inventory of the current system, data analysis, and
the meta data responsibility.
"

This site may be little off for these type of questions, however, I like to
know if possible, how a DA and DBA compare and contrast in their roles and
responsibilities

Hi Tom

It seems to be unbelievable as first time I came across this site. You are great in way you answer the queries. I am a programmer new in Oracle and working on ORACLE 7 server release 7.1.4.1.0 and Pl/SQL Release 2.1.4.0.0. on SCO UNIX Open Server. I am working in an Automobile dealership as programmer. I have designed Forms and Reports(.rpts) for the company.
Now I have been given the charge of whole organization. You can say that I have been the system manager there. The system runs for Inventory Control, Accounting and Vehicle Job Processing System etc. There is 1 server and 8 dumb terminals. Now problem with me is that I don&#8217;t know what type of responsibilities are there, which should be handled by DBA. Actually I don&#8217;t know the abc of DBA. Will you please guide me ( if possible for you,I know you have a lot of queries to answer) where can I get the material regarding Data Base Administration so that atleast I can handle the day to day activities.

Hope you will understand the problem with me and help.

Regards,

Hi Tom
I daily backup the entire data base to a .dmp file to another machine. In last 2 years i did not face any failure but once i had deleted an important file that i recorvered from .dmp file. About hardware we have Compaq Proliant PIII server.

Hi, i followed the thread and want to throw in some personal opinions. Regarding the administration of the DEV database we follow these rules:

- the whole database is regularly rebuild from scratch by a script machine

- Testdata is provided by scripts (reproduceability)

- all scripts above are taken from the version control system

- DEV databases are *never ever* backed up (if it gets screwed up, I rebuild a clean one). If buggy code logically corrupts my data, these database looses any value, so why back it up?

- each developer is advised to work as if the DEV database is completely rebuild tomorrow morning, so don't rely on anything in that database

- the holy grail is the version control system, nothing else

- the DEV-DBA is the maintainer of all database scripts. All DDL has to be discussed with him/her. Developers aren't allowed to change the data model. On the other hand the DBA commits himself to propagate all DDL ASAP to the DEV databases.

Marc

SQL> select count(*) from dba_responsabilities;

  COUNT(*)
----------
8374319983

SQL> select count(*) from user_responsabilities;

  COUNT(*)
----------
         0 

Hi Tom,
I really don't have words to appreciate the kind of knowledge you hold of Oracle, it’s a little personal but I would like to know what will you call yourself a developer having sound knowledge of database things or a DBA having sound knowledge of development related activities, please don’t take it offending. I have been working as a developer in Oracle with just PL/SQL from long, left other languages and tools back behind and as time went by learning more about databases I have a desire to move towards database administration but not just backup and recovery, more of the way you deal with the things development plus anything of databases we ask you and you know it, I too want to make a mark in Oracle world, I really want to be an expert what would you suggest, how should somebody go on this, I do understand practically working on things is very important but in real life one doesn’t get chance to explore everything but I want your comments on this please.

Thanking you in anticipation and waiting for your reply.

Hello TOM. This is an open ended question for you and for all the readers. My manager asked our DBA team, what are the extra or proactive things that we could be doing to make us look in the eyes of the upper management and within the company. Currently our databases are very stable and we don't have the "black eye", but he wants to make us even stronger. Things that come to mind, are testing recovery of every database at least once a quarter, playing DBA-wars to improve our skills. Does anybody have any other suggestions? Thanks.

does upper mgmt know some of your names? Nope. There is more than 180 people in the DataManagement team alone.

would they know you in the hallway? Nope.

are you considered "support staff" or "part of the team"
They say we're all "part of the team", but really we are support staff. Our resposibilites are really shared by many teams...DBA,developers, business consultats etc...It's really hard for us to know what the business is doing, how they're doing it, because we don't see on that side of the fence.


I firmly believe that if all a DBA is doing is manging a database (or 200 of
them), they need to broaden out....But how do you do it in the big company, where everybody has a role to play.

do you train the developers in best practices? I'm pushing for that, because in my previous job I had a lot of input on the developers. Now we really don't know many times what they're doing.

do you make suggestions on ways the business could run better? It's hard without knowing the business.
are you proactive or reactive? On the databases we stay proactive. But from the business side, we are reactive.

Hi
Some Developers are there. If you change their sql, pl/sql scripts they will go mad.
But if you are a DBA, you must change the script if required for performance point of view. To do this, it is better if you know about the application. Always make your point clear to the management before doing anything. It will save you from unwanted management (not developers) misunderstanding. Educate the developer if possible. My point of view is restrict all privileges in development at the time of new application schema preparation. Provide full privileges (not DBA level) to Individual Developers database account in Development to learn (my dev db size is 10gb, but some developer enables the database trace and trace file became 127gb) and restrict developers database accounts owning any db objects otherwise they will own application objects in development and at the time of moving to production your policy may not allow this. If your company policy is not allowing full privilege for developers in dev db then go for personal oracle for each developer.

Hi Tom,

I wanted to ask you a few things about Oracle standards for DBAs. Do you think there should set processes for all DBAs? Do you enforce Oracle standards (I don't even know what they are, but we have a guy who's very adamant about them...) Or do you feel a DBA should do whatever he needs to do to get his job done even if it's very different from what Joe does? Have you ever managed a group of DBAs before, and if yes I would like to know what kinds procedures you like them to follow. We have different people doing different things, and some believe this to be a problem because things aren't standardized. I am to inexperienced at the moment to contribute much so I'm doing a little research. Thank you.

I attended few interviews in the last month, and asked by similiar questions:

Q: What do you think the most important responsibilities of a DBA?

My answer: Availability, acceptable performance, and good and solid backup and recovery procedures

Q: What questions should be asked if a developer want you to add a new column to a table?

My answer: data type, size, any constraints defined, index defined

Q: Assume a new project will be developed soon, what areas should DBA need to plan for this project?

My answer: capacity planning in the database and server, memory usage in the server, CPU usage in the server, number of users in the project, backup/recovery policy, any special requirement (such that can be implemented by Oracle features such as replication, standby database etc)

Q: Which command you can type to resume the database's service after the archive log destination is cleared?

My answer: Not need to type any command, when the archive log destination is cleared, the database can resume the service (but the interviewer insisted that a command is needed, is that correct?)

Tom, could you give me some other areas that I didn't mentioned for the above questions?

Thanks,
David

Hi Tom,

A small question : Which dictionary is used to retrieve the information of a private procedure/function ( not defined in the package header but defined in the package body ) ? This is urgent.

Thanks
Deba

Hi Tom,

Thanks for the reply. But how TOAD shows this private procedure separately ? Is there any OCI for this ?

Deba

Tom,

We as dbas are constantly trying to show/prove to managers and customers (application teams in this context) how valuable we are. Basically, people think any monkey can be a DBA, and we are trying to come up with a list of services.

In my opinion, it is not coming up with the list that is a problem, it is trying to convey the importance and complexity behind the tasks.

Do you have any experience with this type of battle? Do you have advice or documents you've used in the past? Bear in mind we are shooting for all databases not just Oracle. Thank you.

Need anyone, you won't get any IM speak in my resume ;)

My department is considered overhead, so it's tough to assign a cost to downtime since most of ours systems are internal and technically don't make money.

I wish we could all take off for the week, they'd get the picture real fast.

So in your experience when you go to customer sites were you left with the impression there was a great appreciation for dbas and the database? Maybe our organization is just missing the boat.