Oracle DBA’s dos and don’ts 1

[Andrzejek]

I am a programmer who uses Oracle as my DB. I have a great relations with my Data Base Administrators, but I would like to know what are the 'do's and 'do not's as far as DBA responsibilities.

I know some of the 'do's (I hope I am correct here): DB back-ups, upgrades, copying data from production to test/development environment, etc.

And some of the 'do not's (I guess those will be mine – as a programmer - responsibilities): create new / modify existing tables in test environment, setting PK, FK, data constrains, etc.

In order to better do my job, I would like to find out what exactly is DBA’s job so I can communicate better, do my job better, take care of my duties instead of dumping them on DBA (it happens, and they are too nice to tell me: 'It's your job'), etc.

And if there are some places on the Web that state what is what and who does it, or 'even though you can - you never, ever do that because...' (rebuild production table containing SDO_GEOMETRY field with a lot of records, i.e. re-order fields, just a guess here), that would be great, too.

Oracle DBAs, would you share the secrets of your job?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[johnherman]

Each company, and sometimes groups within companies, have their own delineatio of responsiblities. Here are some of the "grey areas":

Performance tuning of SQL
Creation of indexes
DDL and/or DDL approval
Writing and maintenance of stored procedures and triggers

I'm sure others will add to this, but as I stated, each company (and sometimes smaller units within companies) sets their boundaries.



==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SantaMufasa]

JohnHerman came up with a good starter list. In addition, you "DBA Don't" list seems to be my DBA territory, as well.

In addition to the above, my day is consumed by any of the following:

[ul][li]Create and provisioning (i.e., grant privileges to) users approved for database access.[/li]
[li]Create proxy access from one Oracle user to another.[/li]
[li]Create database links from one database user on one instance to a user on another instance.[/li]
[li]Respond to automated database alerts warning of risky conditions (e.g., tablespace full conditions, et cetera).[/li]
[li]Adding files (or increasing max sizes of files) to tablespaces to allocate more space.[/li]
[li]Implement security settings to prevent unauthorized access to the database.[/li]
[li]Modify database-instance parameters to improve performance.[/li]
[li]Bounce databases to have parameters take effect.[/li]
[li]Install Oracle databases on PCs or servers, and create databases.[/li]
[li]Apply patches from Oracle.[/li]
[li]Analyze database performance, and make changes to improve performance.[/li]
[li]Lock Oracle user accounts for former employees.[/li]
[li]Unlock Oracle user accounts that are locked from too many invalid-password attempts.[/li]
[li]Create and assign profiles to cause user compliance with business policies.[/li]
[li]Reverse engineer data models from existing databases.[/li]
[li]Peer review database designs and compliance with standards prior to implementation.[/li]
[li]Document naming conventions for tables, columns, indexes, sequences, et cetera.[/li]
[li]Produce and execute SQL scripts to monitor compliance with organization standards.[/li]
[li]Conduct training on various SQL and database-development topics for developers.[/li]
[li]Investigate and resolve database connectivity issues.[/li]
[li]Review AUD$ contents to identify unauthorized database access.[/li]
[li]Modify the size and number of REDOLOGFILEs to improve database performance.[/li]
[/ul]

This is a non-exhaustive list off the top of my head (and heaven knows that I don't have much on the top of my head).

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

Hi Dave,

How about backups?: "A DBA is as good as his backups's"




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

...DB back-ups...

Since Andrzejek already listed "DB backups" on his "DBA Do" list, I didn't bother repeating tasks he already had listed there.

But your well-taken comment does remind me of yet another important task that DBAs often do:

[ul][li]Oracle database Duplicates (often from Prod to Stage, User-Acceptance, Functional Test, Development, et cetera).[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Andrzejek]

Santa,
Do you agree with all my points (guesses) listed in my OP?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SantaMufasa]

Do you agree with all my points (guesses) listed in my OP?

As I implied in my earlier post, this DBA does all of the tasks that you listed, even the "DBA Don'ts".

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Andrzejek]

Out of curiosity…

Would you recommend any classes, books, web links, other sources of information, to somebody who may be interested in becoming an Oracle DBA (after working with Oracle for almost 20 years as an application developer)? Or is it mostly ‘learn the hard way on the job’ approach?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[johnherman]

Oracle DBA qualification is probably not something to learn on-the-job. Although, as you can see from this thread, the breadth and depth of DBA activities varies across positions, departments, companies, and time. As an Oracle developer, you've got solid SQL skills. Presumably including DDL in addition to the DML. It's important to understand the Oracle architecture, even at a basic level. While I was never officially an Oracle DBA, I was thrust into that role during some prototype and proof of concept projects way back in the 1990's. (I was also the Informix and SQL Server DBA as we were testing those three databases). Anyway, I find the Oracle DBA Handbook to be worthwhile.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright