OMG What Do I Do? - "The database is slow!" - Part III

So, the last time I wrote an epic adventure about my databasing, it was like, three months ago. I've learned a LOT in the last three months.  More vi practicing, how to refresh a database, how to do datapump imports and exports, moving lobs... in fact I am going to move a lob today! Four of them in fact!  I'll post about that later.  What I really wanted to discuss quickly was how the smallest of changes can really F*&^ up how a database coughs up information to users.

So here's the situation. Last week I got an email, then a CALL, from one of my key clients.  "My database is running slow!!"  Oh god.  Again.  But this time, I know a little bit more about databases and slowness, and of all things, I can open Toad AND I have Embarcadero. Two tools!  That makes me like... twice as powerful, right? In fact, I also have something called Precise InDepth!! I am loaded with tools to look at things. So I have all these wonderful tools and I spend literally two days comparing guiful windows stats and numbers.  I just want to say that I was also in the middle of  a 14 day nonstop project so MAYBE I wasn't as thoughtful as I could have been.

I get in late one morning and the senior DBA who sits next to me, who is TOTALLY sweet, mentions to me, "Oh, I opened up topas on your server this morning.  You may want to look at this one process, Hedgehog, that seems to be chewing up some cpu.." then he swivels back around to play some solitaire.

All my tools and he opens up a command line window and runs one unix command and finds my issue.  Yep, turns out that Security is monitoring my database and forgot to turn full debugging off and JUST when my client was running some larger loads, it just HAPPENED to be the straw on the camels back.  Which is why the slowness issue only popped up occasionally... thresholds and all that.

Which is why it takes years to be a senior DBA.

But now I know... before you start using all your fancy tools.  Look at topas.

Like they say, "In the beginning.... was the command line..."

REFRESHING A DATABASE

No, this is not about how cool and refreshing a database can be, although I certainly do think they can be.

This is like one of the most important things a database administrator can do, and just like everything else we do in our jobs there are like 10 DIFFERENT WAYS TO DO IT!

First let's start with the right terminology.  I think refresh is the common vernacular, but really what most people call refresh isn't a refresh at all.  When you replace a development or a test database with a complete database from Production, it's not refreshing. It's called cloning or duplication.  After you have said clone or said duplicate, and then you refill it's yummy gooey insides with fresh yummy gooey data from Production, this is called loading data.  There really is no such technical term as refreshing. You could search all over Oracle and not find something called refresh database, at least not in the way we use it.  I wonder where it even came from...

So, first way to refresh and the easiest, I swear, is doing a duplicate from a cold backup.

OMG, you say, I can't bring my production database down for ANY amount of time, love, nor money! Whatever shall I do!!

There is a duplicate from a hot backup.  For a long time I called it a warm backup, but really, there is no such thing.  I'll do step by steps for that soon. Not now, but soon.

Oh NO, you say, I can't do THAT, my database is like 250gb!! I don't have enough room to store all that crap while I Frankenstein it into a new database incarnation!

Well, speaking of incarnations, there is something called RMAN duplication.... and you can restore from an RMAN backup.  I personally have done two Point-In-Time restores and am still amazed at the magic that goes on inside it while it's cooking away.  I'll do step by steps for that soon. Not now, but soon.

 But today, I will talk about doing a clone from a cold backup. The easiest, right?

A little background.  When I first started looking around for instructions on how to do a clone from a cold backup, I found like pages here and pages there that sort of hinted around at how to do it but no step by step instructions with explanations. Why is THAT?? I don't KNOW, but it drove me crazy.  Why don't senior magic database wizards want to share this stuff? Now, I will tell you that what I am about to write does NOT cover the stuff that can go wrong. That is why senior wizards make the big bucks, because they can figure out at the drop of their hat what's going wrong if your database doesn't come up. They've seen these arcane messages at least one point in their life and they know how to fix it. But this is like a simple plain vanilla cone. And like all things, read all the way through and perform at your own risk. I disclaim any and all production issues from anyone doing this where it kills your production database and you are in someone's cubicle explaining why you were refreshing a a database with instructions you got from some blog called Adventures in DBAsitting. Although I think my instructs are pretty good, I did this with Oracle 10.2 and AIX unix. I have no idea what you have.

Let us start.

The readers' digest condensed version, and the one I heard from senior dbas at work is this:

Shut down the old database.
Copy the database files over to the new database.
Make a new control file.
Start the new database up again.

Oh my GOD, it is so much more complicated than that.  Okay, so here we go....

First, grab a Diet Mountain Dew. Refreshing and filled with caffeine. Yum!

PREP STEPS:

Verify server name, database name, and database version.
Check the source and target database versions, if same, proceed with refresh.

<kim comment - honestly, if you don't know how to check the versions just STOP right here. You are going to seriously damage something. JUST DROP THE MOUSE AND WALK AWAY>

Check mount point of source and target databases

df –k | grep <dbname> | sort

Make sure the space allocated in the target are equal to those from source for datafiles

<kim comment - on these next steps, ask your clients if they want to keep any of this stuff. I was talking to another app dba today and he said, look, if they ask for a refresh, then my job is give the database, they can put all their fancy procedures and functions.  But I'm nice that way. I think it depends on your relationship with your clients...>

Backup TARGET schema passwords, users, roles,

Backup TARGET  functions, procedures, db_links, packages

Verify any tables that need to be datapumped and saved.

ON THE SOURCE DATABASE:

alter system switch logfile;
alter system checkpoint;

<kim comment – this step saves time when bringing target database up.>

Shutdown listeners on source and target before databases are shutdown
BUT NOTIFY YOUR CLIENTS FIRST


TARGET DATABASE: sign on as SYSDBA and shut the database down


sqlplus / as sysdba


shutdown immediate;

SOURCE DATABASE: sign on as SYSDBA

sqlplus / as sysdba

and issue:

alter database backup controlfile to trace;

This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file (udump location).
  

SOURCE DATABASE: sign on as SYSDBA and shutdown the SOURCE database
<kim comment - actually, you are already signed on as sysdba, so you can really just shut it down...>

sqlplus / as sysdba
shutdown immediate;

TARGET DATABASE:
Delete all files *.dbf, *.log, *.ctl

Copy all data files from the oradata directory on the target server from the old server. Edit the controlfile to reflect the new data file names on the new server.

Identify and copy database files:
select  name, bytes

from  (select name, bytes

      from  v$datafile

      union all

      select name, bytes

      from v$tempfile

      union all

      select lf.member "name", l.bytes

      from  v$logfile lf

      ,     v$log l

      where lf.group# = l.group#) used

      ,(select sum(bytes) as poo

      from dba_free_space) free

DO not copy control files across. 

Verify size of datafiles copied (in source and target oradata directories)

ls -ltr /SID_oradata/oradata/* |awk '{s+=$5}; END {print s/1024/1024/1024}'


After datafiles are copied, make sure there are no control files in the oradata directory.
GET THAT? NO CONTROL FILES *.ctl on the new target system!

SOURCE DATABASE: Startup the source database again and notify clients that your source is back up.

TARGET DATABASE: Copy and Edit the Control file

Copy whichever new controlfile.sql you want to use into the target /sid_oradata/oradata directory fresh from trace. It should be in the udump directory along with all your other .trc files.

The file needs major editing before it can be used. Use vi! It's your friend!

Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's about halfway down the file).

Remove any lines that start with --

Remove any lines that start with a #

Remove any blank lines in the 'CREATE CONTROLFILE' section.

Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

Remove the line ‘ALTER DATABASE OPEN’

Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not. Change the line accordingly.  

If the file paths are being changed, alter the file to reflect the changes.

Example: copying oldval to newval:

:%s/oldvalue/newvalue/g

It will look something like this AFTER you do edits:

• STARTUP NOMOUNT
• CREATE CONTROLFILE SET DATABASE "NEWDB" RESETLOGS ARCHIVELOG
•     MAXLOGFILES 50
•     MAXLOGMEMBERS 5
•     MAXDATAFILES 100
•     MAXINSTANCES 1
•     MAXLOGHISTORY 453
• LOGFILE
•   GROUP 1 '/u02/oradata/newval/redo01.log'  SIZE 100M,
•   GROUP 2 '/u02/oradata/newval/redo02.log'  SIZE 100M,
•   GROUP 3 '/u02/oradata/newval/redo03.log'  SIZE 100M
• DATAFILE
•   '/u02/oradata/newval/system01.dbf',
•   '/u02/oradata/newval/undotbs01.dbf',
•   '/u02/oradata/newval/cwmlite01.dbf',
•   '/u02/oradata/newval/drsys01.dbf',
•   '/u02/oradata/newval/example01.dbf',
•   '/u02/oradata/newval/indx01.dbf',
•   '/u02/oradata/newval/odm01.dbf',
•   '/u02/oradata/newval/tools01.dbf',
•   '/u02/oradata/newval/users01.dbf',
•   '/u02/oradata/newval/db01.dbf',
•   '/u02/oradata/newval/BIGONE01.dbf',
•   '/u02/oradata/newval/STATS01.dbf',
•   '/u02/oradata/newval/somerandomname01.dbf'
• CHARACTER SET WE8ISO8859P1
• ;
  <kim comment - the sql actually runs up until the ; above this sentence - you will still need to change the tempfiles before you really open the database, just fyi> copy and paste the alter tablespace temp stuff that is in YOUR controlfile that you created after you initially open the database.>
  
  
• ALTER DATABASE OPEN RESETLOGS;
•  
• ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/newval/temp01.dbf'
•      SIZE 104857600  REUSE AUTOEXTEND OFF;

 When editing, make sure to replace empty lines and comments.

TARGET DATABASE: Start the new database with the newly edited create_controlfile.sql

sqlplus / as sysdba
@create_controlfile.sql
<kim comment – note the startup nomount in the control file>

FIX TEMPFILES:
Rerun control script where it creates tempfiles at the bottom as mentioned previously two sentences up.

TARGET DATABASE:
shutdown immediate;
startup mount
alter database open resetlogs;

IF YOU GET AN ERROR TRY

shutdown abort;
startup nomount;
recover database using backup controlfile;
cancel (when error comes back)
alter database open resetlogs;



Then for god's sake change the database ID!

COMMAND LINE

nid TARGET=/


Do you want to change the database ID - YES YOU DO!

VALIDATION



sqlplus / as sysdba


Tell the database it's pretty. Databases like that.

select status from v$instance;
select distinct status from v$datafile;

check alert log

archive log list

Validate tempfiles
Check the current status of updated temp files

SELECT v.file#, t.file_name, v.status
FROM dba_temp_files t, v$tempfile v
WHERE t.file_id = v.file#;

If bad then

alter database tempfile '/bkrd_oradata/oradata/temp03.dbf' drop;

ALTER TABLESPACE TEMP ADD TEMPFILE
      '/bkrd_oradata/oradata/temp03.dbf' SIZE 8388616192 
   REUSE AUTOEXTEND OFF;

or

create temp tablespace from script generated earlier.


Update the global name to target database

select * from global_name;
update global_name set global_name=’<targetdb>’;

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
select name from v$tempfile;
select name from v$datafile where name like ‘%MISS%’;
select file_name from dba_data_files where file_name like ‘%MISS%’;
select * from v$recover_file;

Validate database directory paths - these are the directory paths in your database itself. If the paths have different file path structures they do not magically change. You have to alter them. FYI.

start the listener

stop and start the database.

RMAN IT AND BACK UP
I hope that if you are using RMAN that you have an AMAZING senior DBA who writes AMAZING scripts so all you have to do is type something like:

rman_it.ksh <dbid>

Then ice up the cake with your saved users and things and you are good to go!

PANIC TIME or OMG HE'S LEAVING IN 7 DAYS!!

Okay, so here's the dealio.  I am most likely, barring an unlikely event, to be taking over a Senior Application DBA's role soon.  This is not to say that I am going to be a Senior Application DBA, this particular crowning achievement will be mine not for many many years.  Maybe 3 or 4.  But for now, I have seven, count them, SEVEN days to glean all the information he has gathered in his brain for the last 30 years. 15 of those years in this particular set of applications.  Sometimes the thought overwhelms me, and I think of the ancient Japanese ritual of sepukku if I accidentally kill a database.

So I have seven days.

What do we do? Imports/Exports.  Tuning.  Partition maintenance.  Architecture advice. Moving databases.  Working with upgrades.  Running scripts in Production.  Reports.

And this is just the stuff I know about.

I'm sure it will be JUST FINE.  In the meantime, I'm getting over a horrible cold and swigging down Sarah Silverman orange Cold/Flu relief cough syrup.

blah....

How Do I Do That? - Installing a pks, pkb file

"S" before "B" when using the "P".

Just when I was feeling a little more secure in tidbits of knowledge, I got a request from a client today.

From: Client
To: Kim

Please run a simple SQL statement in Production.

"Okay", I thought, "Here goes!".  It was easy! I ran it! No fear!  Then I got another request two minutes later.

From: Client
To: Kim

 Please run these two attachments.

[attachments: ThingOne.pkb; ThingOne.pks]

OH MY GAWD!

What were THEY?  What was I supposed to DO with them?  Was I supposed to RUN them? Was I supposed to import them into a schema?  I have never seen things with these extensions before!  Yah, again, people -  wonder at my ignorance.  It's pretty vast.

Okay, Google is my friend.  You know what?  pks and pkb files are all over the place in the Google, but they don't tell you what to do with them. They just tell you bugs and issues. Thank GOD I have Sr. DBA R.

And Instant Messenger

I open up Instant Messenger and start typing...

Me:  Um, have you ever seen a pks or pkb file?

Sr. DBA R: Yes.

Me:  Um, do you... run them?

(pause of Instant Messenger silence)

Sr. DBA R.: Yes.
(kim comment - he messenges like this. Very brief. To the point. He probably knows I'm trying to compose a message that sounds somewhat knowledgeable and this entertains him.)

Me: Um... how?

Sr. DBA R.: Open them in editor in Toad and then run them.

This is where I am busily scrounging in my mind to figure out how to do this, I mean, yes, technically, I understand what he means, but I am still sort of new to the little green frog.  I've been using this tool called Embarcadero and it seems easier, but none of the cool DBAs use, and I want to be cool.  Desperately, I want to be cool.  I want to use the Toad.

Me:  So... how do I do that in Toad? (oh, please come over please please please)

(Two moments of Instant Messenger silence)

Sr. DBA R shows up at my cubicle.  He was probably thinking oh my god, she's in Production maybe it's worth the 1 minute I spend with her in her cube to head off the 3 hours of RMAN recovery in case she wipes out the database.  Or maybe not.  I project a lot of my insecurities.

He very kindly shows me how to open editor, open my files and VOILA! There it is!  He cautions me to run pkS, then pkB.  And I run it! And it's against the wrong schema.  Whereupon he kindly shows me select name from v$database, which I have now consigned to making sure I always run before I do anything else ever in my life, ever.

And then I run them again.  They run great!  And ask, "Do I do commit?" and he wanders off saying, "Nope, it's ddl".  (which I KNEW didn't require commit!! Dammit!! But I forgot.)

And now I know what to do with pkS and pkB files.

OMG What Do I Do? - "The database is slow!" - Part II

Okay, when we last left our heroine, she was bravely dodging the arrows of slow databasity.  Well, here's the thing.  When someone says their database is slow, what does this mean?  I don't mean what's causing it, I mean what is the client really trying to say?  I have complained of what I have called incorrectly a "slow database".  My last job, and in some ways my part-time current job, is Senior Administrator of our enterprise scheduling system.  This system is Applications Manager by UC4, (formerly AppWorx) and I have gone through a couple different application upgrades and two database upgrades.  And I have complained about a slow "database".  My personal experience when *I* was a client went as follows:

Me:  "Oh my god! My database!  It's just... creeping!" (note: complete tone of desperation in my voice. also note: this scheduling system, of which I am/was the Senior Administrator, runs ALL of our billing batch jobs, runs ALL of our payroll, including the top management, runs ALL of our financials, and well, pretty much runs everything in our company.  Normally, I block this all out of my day to day thoughts which helps me cope with all the awesome and scary responsibility.)

Sr. DBA DE:  "Hmm.  When did this start? (As he types on something completely unrelated in the background.)

Me: "Um, when? Um, it just FLIPPED, it was running okay and now everything is going really really slow, like 10 times as slow as it normally does!!"

Sr. DBA DE: "Is the database slow or is your application slow?"

(kim comment - See, this is what DBAs do.  Blame the application.  Typical comment:  It's not the database.  It's the application.  The database is perfectly healthy! All the data is there, the listener is up and running, the archive logs are shuffling off as planned, there is no heavy I/O or cpu, and nothing is crashed. So therefore the database is FINE, except of course the frickin' phone is ringing off the hook because everything that actually touches the database, YES, application wise, is going 100x slower than normal.)

Me:  Trying not to have the panic show in my voice, I say: "Okay, you're right, it's the application, the database is fine.  Can't you put some indexes in or something? Or run statistics or something?"

Although this sounded perfectly reasonable to me, my Sr. DBA snorts.

Me: "Okay, here's the situation, I can make it run at normal speed.  I can stop the application stack, truncate one table, delete all the data out of another table, start it again and then it runs at normal speed.  UNTIL the truncated table starts filling up the table again.  So it's recreatable!"

Sr. DBA DE:  Swivels around his chair, ears all perked up and suddenly shows interest.  "Really?  Well, then we can probably do something that could fix this... have you ever heard of OEM?"

Me: "No. What does it do?"

Sr. DBA DE: "Magical stuff.  Basically, if we can find that sql statement that is causing us trouble, we can lock it into the good twin behavior when it is playing nice and keep it running fast.  Maybe.  No promises.  But maybe, if you can recreate it.  But really, no promises."

(kim comment - there is an art to giving a client hope but not actually promising anything. A skill I need to work on.)

Me: "Okay, let's do it!  I can totally recreate it!"

I use words like totally a lot because I grew up on the East Coast on the beach.  It's sort of mid 80's Valley Talk.  Some people find it endearing.

So then I found out about OEM and magical things like SQL Advisor, which really is magical.  Of course, it took some time to get access hooked up and in the meantime I did a lot of truncating and bouncing.  But I watched my now Hero Sr DBA DE trap the offending sql statement, tune it by locking it in as its good twin so NO MATTER WHAT, it ALWAYS runs this way!  And just like that my application loved my database again, and they danced together as fast as they were supposed to.

Now wouldn't it be great if I could do that with this database that my clients were meeping about?  Well, a couple of things.  We don't have everything in OEM yet, and of course this one particular database is not in OEM.  So that tool is ruled right out.  It will all be manual and eyeballing.  But here are some magical questions that I followed up with.  I think, that in any troubleshooting scenarios there are a couple of basic fundamentals that will work when trying to figure out what's going on.

• Journalistic Questions.
• Sherlock Holmes Most Famous Quotation.

Remember journalism in middle school?  There were basic questions that were taught in that class that have helped me troubleshoot practically everything.

Who?
Who is this affecting? Is it affecting everyone? Are you sure? Have you tried it with different permissions?

What?
What is it affecting? Is it doing the same thing on every query? What is it doing exactly? I need times, dates, milliseconds. What is your definition of "it's taking forever"? I mean, forever is a long time.

Where?
Is it doing this everywhere or only on one desktop or one floor?

When?
When did it start happening exactly? Does it happen all the time? Is it recreatable?

Why?
You know, the client is the closest thing to their application other than the database.  Ask them what their opinion is as to why it's happening.  They have feelings towards their app.  They may have seen it before. Clients have actually pretty good intuition.  And besides which, it makes them feel valued.

How?
How did you discover it? How does it make you feel?  Okay the last question is kinda touchy feely, but I think empathy is something all database administrators should cultivate. I mean, you can go to the best doctor in the world, but there is something lacking if, when you are really sick, he doesn't hold your hand and murmurs, "There, there...we will try and make it better..."

And then Sherlock Holmes Most Famous Quotation.

When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

So using these basic tools, I found out the following:

The whole application is fine, except for two update statements that occur every day at about 1:30 p.m.  These are the only things that are slow. I also learned that this didn't start happening last week, this started happening a couple of weeks ago. Interesting!  You know why?  We have something called Veritas Clustering for high availability databases (databases that have to be up all the time).  To analogize, it's something like having a duplex where there is apartment A on the left side and apartment B on the right side. Usually, each database has their own duplex, own fridge, own bathroom and their individual space and privacy.  Which is important to databases. They like being by themselves as much as possible.  But sometimes, something happens to one side of the duplex that forces that database to temporarily move over to the same side that the other database is living in.  Not ideal, but good in a pinch.  But just like any crunched living situation, it can get a little crowded.

Guess what happened a couple of weeks right when the database started getting slow?  Yup, one database moved in with another and never moved back.  Could this be the problem?  Maybe. No-one likes sharing the bathroom.  Even if it doesn't fix the slowness issue, we can't really start analyzing the situation in great depth until we get the databases back to their respective homes.  So that's happening on Monday.  So everybody, cross your fingers for me!!

In the meantime give good thoughts to all the DBAs in Egypt, Tunisia, and Libya.  They exist and are going through a lot right now, good and bad.  My thoughts and well wishes to them all.

OMG What Do I Do? - "The database is slow!"

Okay, so it's finally happened.  I was sitting at my desk, peacefully catching up on all my work, finding out what's happening in the world, thinking to myself, "Hey, this isn't so bad. Sr. DBA D just left for New Orleans, Sr. DBA H is in New Zealand on vacation, it's actually not bad.  Just basic work, writing meeting minutes, reading about database philosophy, checking out reports..."  So there I was, peaceful and happy, noodling around trying to figure out why the good Pink Floyd wasn't available on vinyl, when suddenly it happened.  A client just popped up at my desk with a panicked look on her face.  "The database, it's slow! It's twice as slow as it was last week! Getting worse!"

Oh crap.  This is where I get the horrible sinking feeling in my stomach.  Database tuning. Like, if I didn't have a really bad weakness of not knowing anything, I'd have to say that database tuning is probably my absolute worst weakness.  I mean, you have to actually know something about databases in order to tune them.  Now I know why some DBAs keep vodka in their desk.

I immediately look over at her, and try and compose myself:

Me:  "Hi! I am sorry to hear that. Slowness, huh? Has this happened before? Well, what did Sr. DBA  H do in the past?"

Client: "I dunno!  Sr. DBA H just did something. Maybe he analyzed tables? Yah, I think he did that. Can you do that? Can you analyze tables?"

Me: "Of course! I can analyze tables.  I can run dbms_stats."  (this is like actually the one thing I can do)

So then I swivel around and log into the machine I need to look at the database on.  My little frog icon comes up, ribbits and then asks me for a license. WHAT?  Doesn't this pc have Toad as a universal user? Apparently not.  So then I go hit up Embarcadero. Another license prompt.  I turn around and look at my client who is twisting her fingers and shooting daggers at me.  OMG.  I can't even open a tool to look at the databases.

I weakly smile: "Hang on.."

Client: "Can we go talk to other DBAs?"

Me: "Um, I think that would be a good idea."

So then my client and I go tromp over to the nearest DBA desk, Sr DBA DH, not to be confused with Sr. DBA D or Sr. DBA H.

Me:  "I can't get into Toad, Embarcadero or anything else, can you pleeeeeze look at ABCD database? It's slow."  (as soon as I say this, I see Sr. DBA DH go into DBA mode.

Sr. DBA DH:  "Well, slowness can be caused by a lot of different things.  It may not necessarily be fixed by running statistics.  In fact, it could be... (whereupon he whips out his fingers and starts counting out all the things that it could be...)"

Client: momentarily transfixed then immediately asks about stats.  She is really convinced that stats is the fix. Maybe it is.  I have no idea.  This is when I get the understanding there is no quick fix for database slowness.  Sr. DBA DH starts running stats after expounding for a long time that this probably isn't going to fix anything. I am learning that we should probably do what the client wants us to do, if it doesn't hurt the database, because maybe it shows we are listening to them.  Maybe it's PR. I don't know.  All I know is, I have a client that has a slow database. And she wants me to fix it.  And I have no idea what the hell to do....

(to be continued....)

How Do I Do That? - Changing Tables from Read Only or vice versa

Every day is a fun learning experience.  What did I learn today?  How to change tables from read only to read writable.  In my current incarnation (haha) I support a database that has had its life divided up into easily understandable periods of time called YEARS.  So basically, my crosstraining partner has gone through and partitioned off tablespaces into these portions called YEARS and most of these tablespaces are in a read only mode.  I think to make the clients unable to write into them.  And possibly easy to maintain.  Now, I had no idea about this until a client sent an enigmatic email:

From: Client
To: Any DBA that can read this note

Plz request that we can write records.  We can't now.
Love,


Client

See, sometimes you get these notes.  Forget about the fact that there are like 300 instances in different iterations of development, stage and production.  To a client only one (maybe two!) databases exist.  They are like teenagers.  There is only one true love.  And we are supposed to read their minds.  Okay.  And actually, sometimes, after years and years at this job, some DBAs can. Which is very Jedi like.  I am light years away from being at that mental state.

So lucky for me at this particular time, even though I am the caretaker of these particular beasts, I wasn't at my desk.  And we have a lot of very helpful DBAs lounging about, so very helpful Sr. DBA E took this email and asked a few more questions.

From: Sr. DBA E
To: Client 


What database? What tables? Who are you? What is that you need, exactly? 


Sincerely,


Sr. DBA E

Funnily enough, I am still learning to ask these questions without floundering around and looking questionable. Anywhoo, after all the information was gathered and apparently the work done, I came back to my desk and saw this:

From: Sr. DBA E
To: Client 

Done!

Sincerely,

Sr. DBA E

What?  What was done?  I asked Sr DBA E and he said, while waving his hand, "C'mon over to my desk and I'll show you.."  Whereupon I learned how to make tablespaces writable.  It's very easy.  Open Embarcadero or Toad and navigate over to tablespaces area.  Right click on the tablespace that says READONLY and change it in the drop down menu to READ WRITE. That's it. It's that easy.  Or you could be all commandline in SQLplus and do a real ALTER TABLESPACE <tablespace_name> READ WRITE;


This is a really simple explanation and nifty trick.

Sometimes things really are that easy.  Tomorrow I'll try and describe my feelings of awe and admiration over Sr DBA D who amazed me with his bravery and ability to kill  a process without thinking twice about it, and apparently magically fixed an application.  By the way, he'd never even seen this process before.  That's how professionals do.

DBA Tidbits - How to run a SQL statement

So last week, I had a developer ask me to run a sql statement in Production to create a package/stored procedure.  Well, the first time it didn't work.

Me:  Hi there Developer Person!  Um, sorry, I am getting errors when I try and run your sql.  Are you SURE I should be running this against (schema/database)?

Developer: {Sounds of rustling and typing.} OH WAIT. No, that shouldn't be run against (old schema/old database) that should be run against  (schema/database)!  Sorreeee!!

Me: OkayDokee! Thanks!  (Whereupon I run the sql again and this time get another error... object doesn't exist!... what?)

I go and look at the code.  The Developer Person has prefaced all the tables and objects with the schema name until he got to the bottom.  Where he missed one object.  Since I am logged in as me, and not as the schema owner, this piece remains an unknown.  Hmm. 

Questions:
Do I change the code?  (ANSWER: NO NO NO NO)

Do I email the developer back and ask him to change the code? (ANSWER: YES!)

Do I ask my Sr. DBAs lurking around in cubicles about this? (ANSWER: YES YES YES)

What is the appropriate way to run code?

Log in as the schema owner; run the code.  Best practice is have the schema prefaced before the objects! Why? Well, if you are logged in as anyone other than the schema owner, then you might end up creating objects in your own dba schema.  And then get busy and forget to drop them and then have all kinds of stuff with your name all over everywhere.  That would be bad housekeeping.

However, having said that don't forget about SOX.  (Sarbanes-Oxley) In some shops if a SOX application exists, then one SHOULD run as the dba and not the schema owner.  Because in a lot of reports, that schema owner might be excluded from an audit.  And then the code is excluded from an audit. That would be bad.

Second tidbit of the day.  Before you run any sql, save it into a text file somewhere you can run it. Maybe, if you are highly organized, in a nice folder somewhere labeled in an organizational way.  Then, run the following:

SQL> spool on
SQL> spool C:\MyOutput.txt
SQL> C:\HighlyOrganizedFolderName\mysqlfileIwanttorun.sql
SQL>spool off

This assumes that you are running sqlplus off of your desktop.  If you are logged into a unix box and run a sql statement from there, you are going to get a nice textfile that is named C:HighlyOrganizedFolderNamemysqlfileIwanttorun.sql
It's not going to end up on your C: Drive. Why not? Because you are logged in to a unix host!

RTFM - Schemas and Users

Scene:  Me in my cube, arms crossed over chest, client in corner looking at all my O'Reilly DBA Books, Sr. DBA in front of me...

Me: My client didn't want any of the stuff of the schema in there, so I saved the ddl of the schema owner and did a drop user {name here} cascade; but now I can't see the schema...

Sr. DBA: Yes, that's because you dropped everything. Now you have to run the ddl that you saved for all the objects.

Me: Um.... I saved the ddl for the schema owner...and then ran that...

Sr. DBA: YOU MEAN YOU DIDN'T SAVE ANYTHING ELSE ?

Me: Er... I reran the ddl for the schema owner... doesn't that make the ... um... exoskeleton of the schema come back? Like, all the stuff but... empty?

Sr. DBA: [silence]

Me: Uh, I guess not.

Sr. DBA: [thought balloon = omg, how did she get here]

LUCKY FOR ME, the client really didn't want any of the objects. And now I have a better understanding of schemas and users. And clients. And Sr DBAs.


I feel better after reading this.