30 July 2011

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..."

29 July 2011

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!