Tuesday, October 29, 2013

Get create user DDL with grants, privileges and roles

rem script: user_cr_ddl.sql
rem  Purpose: generate create user script with privs
rem
rem  Usage: user_cr_ddl
rem
rem  Note:
rem   If ORA-31608 encountered, it means the user does not
rem   have grants in that category. Edit the spooled script
rem   as ncessary
rem
rem
SET LINESIZE 200
SET PAGESIZE 0 FEEDBACK off VERIFY off
-- SET TRIMSPOOL on
SET LONG 1000000
-- COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
COLUMN ddl FORMAT A4000
define username=&&1
spool &username._cr_ddl.sql
SELECT DBMS_METADATA.GET_DDL('USER', upper('&username') )  DDL FROM dual;
prompt -- Role
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper('&username'))  DDL from dual
where exists ( select 1 from dba_role_privs  where grantee=upper('&username') );
prompt -- Sys priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper('&username'))  DDL FROM  dual
where exists ( select 1 from dba_sys_privs  where grantee=upper('&username') );
prompt -- Object priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper('&username'))  DDL
FROM dual where exists ( select 1 from dba_tab_privs  where grantee=upper('&username') );
prompt -- tablespace quota
SELECT  DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&username')) DDL from dual
where exists ( select 1 from dba_ts_quotas  where username=upper('&username') );
spool off
exit

Monday, October 28, 2013

TAR commands in unix

#######Creating a tar archive:

tar -cvf /u04/28102013/oracle1020.tar 10.2.0

where /u04/28102013/oracle1020.tar is the destination path and filename where 10.2.0 is the source directory name.

####Extracting all files from an archive:

tar -xf oracle1020.tar

#####Listing the contents of an archive:

tar -tf oracle1020.tar

######Compress:

gzip oracle1020.tar

######Decompress:

gunzip oracle1020.tar.gz

======================================================

In detail

1. Creating an archive using tar command

Creating an uncompressed tar archive using option cvf

This is the basic command to create a tar archive.
$ tar cvf archive_name.tar dirname/
In the above command:
  • c – create a new archive
  • v – verbosely list files which are processed.
  • f – following is the archive file name

Creating a tar gzipped archive using option cvzf

The above tar cvf option, does not provide any compression. To use a gzip compression on the tar archive, use the z option as shown below.
$ tar cvzf archive_name.tar.gz dirname/
  • z – filter the archive through gzip
Note: .tgz is same as .tar.gz
Note: I like to keep the ‘cvf’ (or tvf, or xvf) option unchanged for all archive creation (or view, or extract) and add additional option at the end, which is easier to remember. i.e cvf for archive creation, cvfz for compressed gzip archive creation, cvfj for compressed bzip2 archive creation etc., For this method to work properly, don’t give – in front of the options.

Creating a bzipped tar archive using option cvjf

Create a bzip2 tar archive as shown below:
$ tar cvfj archive_name.tar.bz2 dirname/
  • j – filter the archive through bzip2
gzip vs bzip2: bzip2 takes more time to compress and decompress than gzip. bzip2 archival size is less than gzip.
Note: .tbz and .tb2 is same as .tar.bz2

2. Extracting (untar) an archive using tar command

Extract a *.tar file using option xvf

Extract a tar file using option x as shown below:
$ tar xvf archive_name.tar
  • x – extract files from archive

Extract a gzipped tar archive ( *.tar.gz ) using option xvzf

Use the option z for uncompressing a gzip tar archive.
$ tar xvfz archive_name.tar.gz

Extracting a bzipped tar archive ( *.tar.bz2 ) using option xvjf

Use the option j for uncompressing a bzip2 tar archive.
$ tar xvfj archive_name.tar.bz2
Note: In all the above commands v is optional, which lists the file being processed.

3. Listing an archive using tar command

View the tar archive file content without extracting using option tvf

You can view the *.tar file content before extracting as shown below.
$ tar tvf archive_name.tar

View the *.tar.gz file content without extracting using option tvzf

You can view the *.tar.gz file content before extracting as shown below.
$ tar tvfz archive_name.tar.gz

View the *.tar.bz2 file content without extracting using option tvjf

You can view the *.tar.bz2 file content before extracting as shown below.
$ tar tvfj archive_name.tar.bz2

4. Listing out the tar file content with less command

When the number of files in an archive is more, you may pipe the output of tar to less. But, you can also use less command directly to view the tar archive output, as explained in one of our previous article Open & View 10 Different File Types with Linux Less Command — The Ultimate Power of Less.

5. Extract a single file from tar, tar.gz, tar.bz2 file

To extract a specific file from a tar archive, specify the file name at the end of the tar xvf command as shown below. The following command extracts only a specific file from a large tar file.
$ tar xvf archive_file.tar /path/to/file
Use the relevant option z or j according to the compression method gzip or bzip2 respectively as shown below.
$ tar xvfz archive_file.tar.gz /path/to/file

$ tar xvfj archive_file.tar.bz2 /path/to/file

6. Extract a single directory from tar, tar.gz, tar.bz2 file

To extract a single directory (along with it’s subdirectory and files) from a tar archive, specify the directory name at the end of the tar xvf command as shown below. The following extracts only a specific directory from a large tar file.
$ tar xvf archive_file.tar /path/to/dir/
To extract multiple directories from a tar archive, specify those individual directory names at the end of the tar xvf command as shown below.
$ tar xvf archive_file.tar /path/to/dir1/ /path/to/dir2/
Use the relevant option z or j according to the compression method gzip or bzip2 respectively as shown below.
$ tar xvfz archive_file.tar.gz /path/to/dir/

$ tar xvfj archive_file.tar.bz2 /path/to/dir/

7. Extract group of files from tar, tar.gz, tar.bz2 archives using regular expression

You can specify a regex, to extract files matching a specified pattern. For example, following tar command extracts all the files with pl extension.
$ tar xvf archive_file.tar --wildcards '*.pl'
Options explanation:
  • –wildcards *.pl – files with pl extension

8. Adding a file or directory to an existing archive using option -r

You can add additional files to an existing tar archive as shown below. For example, to append a file to *.tar file do the following:
$ tar rvf archive_name.tar newfile
This newfile will be added to the existing archive_name.tar. Adding a directory to the tar is also similar,
$ tar rvf archive_name.tar newdir/
Note: You cannot add file or directory to a compressed archive. If you try to do so, you will get “tar: Cannot update compressed archives” error as shown below.
$ tar rvfz archive_name.tgz newfile
tar: Cannot update compressed archives
Try `tar --help' or `tar --usage' for more information.

9. Verify files available in tar using option -W

As part of creating a tar file, you can verify the archive file that got created using the option W as shown below.
$ tar cvfW file_name.tar dir/
If you are planning to remove a directory/file from an archive file or from the file system, you might want to verify the archive file before doing it as shown below.
$ tar tvfW file_name.tar
Verify 1/file1
1/file1: Mod time differs
1/file1: Size differs
Verify 1/file2
Verify 1/file3
If an output line starts with Verify, and there is no differs line then the file/directory is Ok. If not, you should investigate the issue.
Note: for a compressed archive file ( *.tar.gz, *.tar.bz2 ) you cannot do the verification.
Finding the difference between an archive and file system can be done even for a compressed archive. It also shows the same output as above excluding the lines with Verify.
Finding the difference between gzip archive file and file system
$ tar dfz file_name.tgz
Finding the difference between bzip2 archive file and file system
$ tar dfj file_name.tar.bz2

10. Estimate the tar archive size

The following command, estimates the tar file size ( in KB ) before you create the tar file.
$ tar -cf - /directory/to/archive/ | wc -c
20480
The following command, estimates the compressed tar file size ( in KB ) before you create the tar.gz, tar.bz2 files.
$ tar -czf - /directory/to/archive/ | wc -c
508

$ tar -cjf - /directory/to/archive/ | wc -c
428

Wednesday, October 23, 2013

Memory related UNIX commands

Memory related UNIX commands

a.Solaris:
$ /usr/sbin/prtconf |grep -i "Memory size"
$ swap -s
$ df -k
$ /usr/local/bin/top
$ vmstat 5 100
$ sar -u 2 100
$ iostat -D 2 100
$ mpstat 5 100
a.     HP-UX 11.0:
$ grep Physical /var/adm/syslog/syslog.log
$ df -k
$ sar -w 2 100 
$ sar -u 2 100
$ /bin/top
$ vmstat -n 5 100
$ iostat 2 100
$ top
b.     AIX:

$ /usr/sbin/lsattr -E -l sys0 -a realmem
$ /usr/sbin/lsps -s
$ vmstat 5 100
$ iostat 2 100
$ /usr/local/bin/top # May not be installed by default in the server

c.     Linux [RedHat 7.1 and RedHat AS 2.1]:
$ dmesg | grep Memory
$ vmstat 5 100
$ /usr/bin/top

2.     PHYSICAL MEMORY AND SWAP SPACE

OS Specific Commands:
AIX:
/usr/sbin/lsattr -E -l sys0 -a realmem
/usr/sbin/lsps -s

HP-UX:
grep Physical /var/adm/syslog/syslog.log
/usr/sbin/swapinfo -t

Linux:
cat /proc/meminfo | grep MemTotal
/sbin/swapon -s

Solaris:
/usr/sbin/prtconf | grep "Memory size"
/usr/sbin/swap -s

Tru64: 
vmstat -P| grep -i "Total Physical Memory ="
/sbin/swapon -s

Comand to check memory for HP-UNIX

Comand to check memory for HP-UNIX

/usr/sbin/swapinfo -t

V$lock - query to find blockers/Lockconflicts - Oracle

########  if dba_blockers is not working ###########

##### Using V$lock ########################

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2


####### mix of v$session #############

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;



###############

SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid

Monday, October 21, 2013

MySQL: Top MySQL DBA interview questions (Part 2)

Top MySQL DBA interview questions (Part 2)

Continuing from our Top MySQL DBA interview questions (Part 1) here are five more questions that test a MySQL DBA’s knowledge, with two that will help suss out some personality traits.
  1. Disk I/O
  2. Disk performance should be an ever present concern to a DBA.  So although they don’t need to be a storage specialist, they should have a working knowledge.  Ask them about RAID versions, mirroring versus striping, and so forth.  Mirroring combines two disks as a unit.  Every write is duplicated on both disks.  If you lose one disk, you have an immediate copy.  Like a tandem truck that has spare tires running in parallel.  Lose one, and you don’t have to pull over immediately to replace it.  Striping spreads I/O over multiple disks so you on the one hand increase throughput linearly as you add disks.  That’s because you have more disks working for you.  At the same time you increase risk with each new disk you add, because the failure rate is then the sum total of all those disks.
    For relational databases the best RAID level is 10, which is striping over mirrored sets.  You use more disks, but disks are cheap compared to the hassle of any outage.
    If you’re deploying on Amazon, your candidate should be familiar with the Elastic Block Storage offering also known as EBS.  This is virtualized storage, so it introduces a whole world of operational flexibility.  No longer do you have to jump through hoops to attach, add or reconfigure storage on your servers.  It can all be done through command line API calls.  That said EBS suffers from variability problems as with any other shared resource.  Although Amazon guarantees your average throughput, the I/O you get at a given time can swing wildly from low to high.  Consider Linux software RAID across multiple EBS volumes to mitigate against this.
  3. How would you setup master/slave & master/master replication?
  4. A basic replication setup involves creating a full dump of the primary database, while it’s tables are locked.  The DBA should capture the master status, logfile & position at that time.  She should then copy the dump file to the secondary machine & import the full dump.  Finally the CHANGE MASTER TO statement should be run to point this database instance to it’s master.  Lastly START SLAVE should be issued.  If all goes well SHOW SLAVE STATUS should show YES for both of these status variables:
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Master-master replication is similar, except one additional step.  After the above steps have run, you know that your application is not pointing at the slave database.  If you’re not sure, verify that fact first.  Now determine the logfile name & position on the slave with SHOW MASTER STATUS.  Return to the primary box, and run the CHANGE MASTER TO command to make it slave from the secondary box.  You’ve essentially asked MySQL to create a circular loop of replication.
    How does MySQL avoid getting into an infinite loop in this scenario?  The server_id variable must be set, and be unique for all MySQL instances in your replication topology.
    For extra credit, ask the candidate about replication integrity checking.  As important as this piece is to a solid reliable replication setup, many folks in the MySQL world are not aware of the necessity.  Though replication can be setup, and running properly, that does not mean it will keep your data clean and perfect.  Due to the nature of statement based replication, and non-deterministic functions and/or non-transactional tables, statements can make their way into the binary logs, without completing.  What this means is they may then complete on the slave, resulting in a different row set on the same table in master & slave instance.
    Percona’s pt-table-checksum is the preventative tool to use.  It can build checksums of all your tables, and then propagate those checksums through replication to the slave.  An additional check can then be run on the slave side to confirm consistency, or show which rows & data are different.
  5. How are Users & Grants different in MySQL than other DBs?
  6. Creating a grant in MySQL can effectively create the user as well.  MySQL users are implemented in a very rudimentary fashion.  The biggest misunderstanding in this area surrounds the idea of a user.  In most databases a username is unique by itself.  In MySQL it is the *combination* of user & hostname that must be unique.  So for example if I create user sean@localhost, sean@server2 and sean@server3, they are actually three distinct users, which can have distinct passwords, and privileges.  It can be very confusing that sean logging in from the local command line has different privileges or password than sean logging in from server2 and server3.  So that’s an important point.
  7. How might you hack a MySQL server?
  8. This is a good opportunity for the candidate to show some creativity with respect to operations and Linux servers.  There are all sorts of ways into a database server:
    a.bad, weak or unset passwords
    b.files with incorrect permissions – modifying or deleting filesystem files can take a database down or corrupt data
    c.intercepting packets – could reveal unencrypted data inside the database
    d.unpatched software – bugs often reveal vulnerabilities that allow unauthorized entry
    e.moving, disabling or interrupting the backup scripts – a possible timebomb until you need to restore
    f.DNS spoofing, could allow login as a different user
    g.generous permissions – may allow an unprivileged user access to protected data
    There are endless possibilities here.  Listening for creative thinking here, reveals how much that person will think thoroughly and effectively about protecting your systems from those same threats.
  9. Brain teasers, riddles and coding problems
  10. Google for a long time was a fan of these types of tests at interviews, but I’m not at all.  For one thing you filter for good test takers, and for another the candidate has no resources – either books or the internet at their disposal.
    Why not instead ask them to tell a story.  Storytelling conveys a lot of things.  It conveys a bit of teaching ability, which extends far beyond internalizing some multiple choice questions.  It tells you more about their personality, which as I’ve said is very important.  It shows how they solve problems, as they’ll take you through their process.  And gives them an opportunity to tell you a real world triumph they presided over.

Personality Questions

In my experience, some of the most important traits of a new hire center around personality traits, and how they might mix with your existing team.  Being punctual for an interview, for instance sets a precedent for many things.  But that door swings both ways, so if you want to hire these types of folks, don’t keep them waiting either!
Pay attention to whether or not the candidate takes some lead in the conversation at all.  This can indicate the person is a self starter.  Obviously a great candidate will also listen carefully and patiently to what you have to say, but may then take the ball and run with it somewhat.
Listen for signals that the person is active in the field, posting on forums, and attending conferences, meetups and forums on technology topics.  You might also ask them if they blog, and what topics interest them.

MySQL: Top MySQL DBA interview questions

Top MySQL DBA interview questions (Part 1)

MySQL DBAs are in greater demand now than they’ve ever been. While some firms are losing the fight for talent, promising startups with a progressive bent are getting first dibs with the best applicants. Whatever the case, interviewing for a MySQL DBA is a skill in itself so I thought I’d share a guide of top MySQL DBA interview questions to help with your screening process.
It’s long and detailed with some background to give context so I will be publishing this in two parts.

The history of the DBA as a career

In the Oracle world of enterprise applications, the DBA has long been a strong career path. Companies building their sales staff required Peoplesoft or SAP, and those deploying the financial applications or e-business suite needed operations teams to manage those systems. At the heart of that operations team were database administrators or DBAs, a catchall title that included the responsibility of guarding your businesses crown jewels. Security of those data assets, backups, management and performance were all entrusted to the DBA.
In the world of web applications, things have evolved a bit differently. Many a startup are driven only by developers. In those smaller shops, Operations tasks are designated to one developer who takes on the additional responsibility of managing systems. In that scenario, Operations or DBA duties becomes a sort of secondary role to the primary one of building the application. Even in cases where the startup creates a specific operations role with one person managing systems administration, chances are they don’t also have DBA experience. Instead, these startups are more likely to manage the database as a typical Linux application.

When I grow up I (don’t) want to be a MySQL DBA

Where do they come from, and why don’t a lot of computer science folks gravitate towards operations, and DBA? This may be in part due to and the romance of certain job roles which we discussed in a past article, The Mythical MySQL DBA. This pattern appeared a lot in the Oracle world as well. Many folks who were career DBAs actually moved to that role from the business side. In fact you’d find that many didn’t have a computer science or engineering background in the first place. In my experience I saw many Linux and Unix administrators with a stronger foundation who would fit into the DBA role but were simply not interested in it. The same can be said of the MySQL side of the house. Computer science grads don’t get out of school aiming for a career in ops or as a DBA because it has never been regarded as the pinnacle. It’s typically the PROGRAMMERS who become the rockstars in a cool startup.
But as the Internet grows into a richer and more complex medium, things are changing. People talk about scalability, high availability, zero downtime and performance tuning. When brief outages cost millions in losses expectations are very high and that requires skilled, experienced DBAs.
We’ve made a list of comprised of skill questions, general questions and ‘good-to-know’ questions. Have fun grilling your candidate with them, although bear in mind that with interviews it’s not about knowing it all, rather how the person demonstrates critical thinking skills.

Skills Questions

  1. Why are SQL queries so fundamental to database performance?
  2. This is the one question which a DBA should have an answer to. If they can’t answer this question, they’re unlikely to be a good candidate.
    After a MySQL server is setup and running, with many of the switches and dials set to use memory, and play well with other services on the Linux server, queries remain an everyday challenge. Why is this?
    SQL queries are like little programs in and of themselves. They ask the database server to collect selections of records, cross tabulate them with other collections of records, then sort them, and slice and dice them. All of this requires MySQL to build temporary tables, perform resource intensive sorts and then organize the output in nice bite size chunks.
    Unfortunately there are many ways to get the syntax and the results right, yet not do so efficiently. This might sound like a moot point, but with modern websites you may have 5000 concurrent users on your site, each hitting pages that have multiple queries inside them.
    What makes this an ongoing challenge is that websites are typically a moving target, with business requirements pushing new code changes all the time. New code means new queries, which pose ongoing risks to application stability.
  3. Indexes – too many, too few; what’s the difference?
  4. Indexes are very important to the smooth functioning of a relational database. Imagine your telephone book of yore. I can look up all the people with last name of “Hull” in Manhattan because I have the proper index. But most yellow pages don’t include an index for *first* names even though they might occaisionally come in handy, for example with the names “Star” or “Persephone”.
    You can imagine that, if you had a phone book which you maintain and update, everytime you add or remove a name you also have to update the index. That’s right, and the same goes for your relational database.
    So therein lies the trade off, and it’s an important one. When you are *modifying* your data, adding, updating or removing records, you must do work to keep the index up to date. More indexes mean more work. However when you’re looking up data or *querying* in SQL speak, more indexes mean more ways of looking up data fast. One more trade off is that indexes take up more pages in your phonebook, and so too they take up more space on disk.
  5. Backup & Recovery – explain various types & scenarios for restore
  6. Backups come in a few different flavors that the DBA should be familiar with.
    Cold backups involve shutdown down the database server (mysqld) and then backing up all the data files by making a copy of them to another directory. To be really thorough, the entire datadir including binlogs, log files, /etc/my.cnf config file should also be backed up. The cold backup is a database in itself, and can be copied to an alternate server and mounted as-is.
    Logical backups involve using the mysqldump tool. This locks tables while it runs to maintain consistency of changing data, and can cause downtime. The resulting dump file contains CREATE DATABASE, CREATE TABLE & CREATE INDEX statements to rebuild the database. Note the file itself is not a database, but rather a set of instructions which can tell a MySQL server *HOW* to reconstruct the database. Important distinction here.
    Hot backups are a great addition to the mix as they allow the physical database data files to be backed up *WHILE* the server is up and running. In MySQL this can be achieved with the xtrabackup tool, available from Percona. Despite the name, it works very well with MyISAM and InnoDB tables too, so don’t worry if you’re not using xtradb tables.
    There are a few different restore scenarios, and the candidate should be able to describe how these various backups can be restored, and what the steps to do so would be. In addition they should understand what point-in-time recovery is, and how to perform that as well. After restoring one of the above three backup types, the DBA would use the mysqlbinlog utility to apply any subsequent transactions from the binary logs. So if the backup was made at 2am last night, and you restore that backup, the mysqlbinlog tool would be used to dig up transactions since 2am, and apply them to that restored database.
  7. Troubleshooting Performance
  8. Since this is an ongoing challenge with relational databases, a good grasp of it is crucial. One way to challenge the candidate would be to describe a recent performance problem you experienced with your infrastructure, and ask them how they would go about resolving it.
    If they struggle with the particulars of what you ran into, ask them to describe a big performance challenge they solved, what the cause was, and how they performed analysis.
    Typically, first steps involve mitigating the immediate problem by finding out what changed in the environment either operationally or code changes. If there is a bug that was hit, or other strange performance anomaly, the first stop is usually looking at log files. MySQL server error logs, and the slow query log are key files. From there, analyzing those files during the timeframe where problems occurred should yield some clues.
    You might also hope to hear some comment about metrics collection in this discussion. Tools such as cacti, munin, opennms, or ganglia are invaluable tools for drilling down on a past event or outage, and sifting through server stats to find trouble.
  9. Joins – describe a few kinds and how the server performs them
  10. A basic understanding of INNER JOIN and OUTER JOIN would be a great start. A simple example might be employees and departments. If you have four employees and two departments, an INNER JOIN of these tables together will give you the departments employees belong to. Add another employee without assigning her to a department, and the inner join won’t display her. Further adding a new department which doesn’t yet contain employees won’t display either. However performing an OUTER JOIN will give you those matches with null in the department field, and null in the employee field respectively.
    Thought of with another example, take a credit card company. One tables contains cardholders identity, their number, address, and other personal information. A second table contains their account activity. When they first join, they don’t have any monthly statements, so an INNER JOIN of cardholders with statements will yield no rows. However an OUTER JOIN on those two tables will yield a record, with a null for the statements columns.

Top Ten DBA Interview Questions

Top Ten DBA Interview Questions

I'm often asked what interview questions should be asked of a new DBA you are considering to hire. Mistakes in a DBA hire can cost an organization a lot of money and hurt morale. There are a lot of factors that determine how well a DBA will fit into an environment. I thought I would share some of the things I consider when I interview a new DBA. These questions can be asked to any type of DBA (MySQL, Oracle, SQL Server, DB2, ..).

Questions you have to answer for yourself before interviewing a new DBA include:
  • What skill sets are you looking for in a DBA? What skills are need to have and which are nice to have?
  • How much time do they have to get up to speed?
  • How important is it you hire the right person? (Or what investment are you willing to make to hire the right person).
When interviewing a potential new DBA it is important to understand:
  • What is their technical depth and experience.
  • What type of environments have they worked in.
  • How well do they interact with customers.
  • How well will they fit in to the team and the company environment.
  • What techniques and methodologies do they use to manage databases.
  • How will they impact the current database management practices.
The type of DBA you are trying to hire greatly impacts how you interview the candidates. I don't care to ask a lot of syntax questions or a lot of technical minutia. I want to understand:
  • The depth and breadth of their knowledge.
  • Their methodology and approach to managing databases.
  • How they solve problems.
  • Their commitment and work ethic.
I could write a white paper on explaining the rationale behind each of these questions, but I'll cut to the quick and give a ten step process to interview a new DBA candidate. These questions assume you are looking for a production DBA. If your organization is new and you do not have the expertise to hire the right candidate, you need to hire a consultant that can help your organization through this process.
  1. What are four errors found in an alert (error) log that can ruin a DBA's appetite and how does the candidate avoid them?
  2. What are four performance bottlenecks that can occur in a database server and how are they detected?
  3. Ask them to explain their philosophy for database management and how they implement best practices and guidelines. This should include questions related to the configuration you are running (replication, cluster, OLTP, data warehouse, etc.).
  4. What area of database internals do they understand the best that helps them troubleshoot problems. Ask them to explain this area so you can get an understanding of their depth of knowledge in this area.
  5. Get ten common types of trouble tickets DBAs have solved in your organization and ask them how they would solve them.
  6. Get five difficult problems your DBAs have had to solve and ask them how they would solve them.
  7. Ask them to explain five things they have learned recently that makes them a better DBA and where they learned them.
  8. Set up a simple database environment. Ask them to use two different methods to back up the database. Break the database. Have them recover the database using the backups they created.
  9. Ask them to give a detailed explanation of setting up an environment for disaster recovery or an upgrade for an important database.
  10. Have them write or build a few things in the sample database to demonstrate their knowledge and experience. This should include typical tasks they will be expected to perform to manage the environment.

I like a three or four interview process. Each interview should only leave the candidates that meet the profile of the type of person you want to hire.
  • First interview I like to get a baseline if they match the resume they sent and do they meet the profile of what you are looking for.
  • Second interview I like to make very technical to see if they meet the technical profile of what you are looking for. Make sure you make this sufficiently in-depth for the position you are hiring. There should be no doubt at the end of this interview as to whether they have the technical skills or not for the position you have open.
  • Third interview I like different members of the team determine how well they will fit into your environment and then perform one more level of technical evaluation to make sure you have the right candidate.
Have fun and good luck. What are some of your favorite questions?

MySQL: Learning the MySQL Architecture


Learning the MySQL Architecture
MySQL is an database server and anyone managing the MySQL database server has to learn the architecture, and feature functionality like any other database server. The biggest mistake to make with a new MySQL environment is thinking it is a toy and trivial to work with. A MySQL server needs to be properly configured, tuned and managed like any other database server.

The key to learning any database server is to learn its feature/functionality and the architecture.

MySQL: Managing the MySQL Instance

Managing the MySQL Instance

An Oracle DBA needs to learn how to manage a MySQL Instance. The MySQL instance has different memory buffers and caches like any database server. Part of MySQL's uniqueness is that it has memory areas to be configured that are associated with different types of storage engines.

MySQL :Managing MySQL Storage

Managing MySQL Storage

Storage needs to be managed for any MySQL instance. A MySQL database server does not have the same flexibility as an Oracle database server in terms of how to lay out the data storage. It is important to learn the data storage defaults and options.


MySQL : MySQL Optimal Configuraton Architecture

MySQL Optimal Configuraton Architecture

Oracle DBAs understand the importance of defining guidelines and standards and using them in the configuration and management of database servers. Years ago the Optimal Configuration Architecture (OFA) developed a base set of guidelines and naming conventions for Oracle DBAs.

I developed the MySQL Optimal Configuration Architecture (MOCA) modeled after OFA to give new MySQL DBAs a set of guidelines and standards to consider when configurating MySQL database servers.

MySQL: Starting up and Shutting Down a MySQL Server


Starting up and Shutting Down a MySQL Server

There are different ways for starting up and shutting down a MySQL server. A MySQL server when it starts up it starts up all the way. When a MySQL server shuts down it shuts down all the way. MySQL cannot startup and and shutdown at different levels the way an Oracle database server can.

MySQL: Differences between Oracle and MySQL

Differences between Oracle and MySQL

Some key differences for DBAs between Oracle and MySQL database servers include:
  • Different tools used to manage and monitor database servers.
  • Oracle architecture is process based, MySQL architecture is thread based.
  • Different tools used for backup and recovery.
  • Database specific SQL syntax.
  • Database specific SQL functions.
  • Different syntax for stored routines. MySQL has no packages.
  • MySQL routines are not compiled and run in each session thread and not in global memory.
  • MySQL only supports row-level triggers.
  • Different startup and shutdown processes.
  • Oracle RAC is a shared disk solution while MySQL Cluster is a shared nothing solution.
  • The default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases.
  • With Oracle, the CREATE DATABASE command is used to create the physical storage for the database server.
  • With MySQL, the mysql_install_db script is used to create the physical storage for the database server.
  • The term "database" in Oracle means all the physical files associated with an instance.
  • The term "database" in MySQL means a schema. The term database and schema can be used interchangeably in MySQL.
  • In Oracle a database user owns all the schema objects.
  • In MySQL the database schema owns all the schema objects.
  • Oracle supports role based security. With MySQL, scripts and stored routines are used to programatically organize security permissions.
  • Oracle has a lot more feature functionality that makes it very popular.
  • MySQL has a lot less functionality than Oracle that makes it very popular.
  • Oracle has tons of options for creating a table. Different characteristics and behavior are defined with these options.
  • The key with MySQL tables are defining a storage engine with a table. The storage engine defines characteristics such as row level locking versus table level locking, referential integrity, support for different types of indexes and features. Different optimizations, storage, tuning and backup and recovery are required for each storage engine. Storage engines are a KEY feature in MySQL.
  • MySQL supports different ISOLATION levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ AND SERIALIZABLE.
Oracle DBAs are not just managing Oracle databases any longer. They are supporting, SQL Server, MySQL, Business applications, application servers in a lot of Oracle environments. The more software you can support in an organization the more marketable you are. Its important that Oracle DBAs be able to support the corporate environments that are running database software from multiple vendors and the software surrounding databases.

There are reasons that MySQL customers like the light weight, easy to use MySQL database server. If an Oracle DBA compares MySQL to Oracle feature by feature they are going to be very disappointed. An Oracle DBA needs to look at the feature/functionality that makes MySQL so popular. The feature/functionality is not tons of features, its that it is light weight, fast and easy to manage.

The closest analogy I can make to an experienced Oracle DBAs, is that a MySQL database server is similar in management to what an Oracle version 7 database was like. MySQL uses cache hit ratios, has minimal wait events and DBAs still need to write scripts to surround the MySQL database.

MySQL - An Introduction for Oracle DBAs

MySQL: An Introduction for Oracle DBAs

You are an Oracle Database Administrator. You enjoy looking after Oracle databases and you are really good at it. In the corner of the machine room, on the edge of your peripheral vision, is a server running a database called MySQL.
You have been told that this holds the data for your company’s website and your corporate blog, all running cool, free, open-source software. A trendy, young contractor installed this stuff a while back. He told your boss that, as MySQL is open-source, light and free, it does not require the overhead of database administration, unlike big, complex Oracle. You were both impressed by and sceptical about this claim.
The contractor has long gone. The company website and the corporate blog are a big success. On the edge of your peripheral vision, that server keeps worrying you.
One day your manager calls you into his office, “Can you just have a quick look at that MySQL database server, the one with the website and the blog on it? You know, make sure it’s secure and stuff. Shouldn’t be too hard for an Oracle DBA like yourself.”
You know absolutely nothing about MySQL. Where do you start?

How do I connect to MySQL?

First logon to the server hosting MySQL, typically via ssh (let’s assume someone has at least given you access to the server, otherwise we might as well give up now). The server in this story is running linux; however most of the commands we will cover are operating system independent. Type the following:
mysql -u root
If you do not get an error message and this command seems to work, then you can already tell things are not as they should be. This means the MySQL root user (which is nothing to do with the operating system root user; it is the equivalent of Oracle’s sysdba) is not password-protected. This is the default situation and I’ll show you how to fix it in a minute.
However if there is a password for the MySQL root user and, assuming you have been told it, type the command below, followed by the password when prompted:
mysql -u root -p
You are now logged in to the mysql client (the equivalent of sqlplus) and will see a prompt, typically 'mysql>'.

What’s in this database?

Within the mysql client, invoked above, type:
show databases;
This will list the databases contained within this MySQL server. Beware databases are not equivalent to databases in Oracle; they are equivalent to Oracle schemas. One MySQL server may contain many databases. In the mysql client you can use the word schema interchangeably with the word database.
In the list of databases you will see some or all of the following standard databases: information_schema, mysql, performance_schema and test. Anything else will be a database supporting one of your own applications.
To enter one of these databases:
use database_name;
To see which tables are in this database:
show tables;
and to see more detail:
show table status;
The second column in the output of the above command is “Engine”. An unusual feature of MySQL (for those familiar with Oracle) is that it has many ways of storing data, called storage engines. The two most common storage engines are MyISAM and InnoDB. MyISAM (the default prior to MySQL 5.5) is a very basic storage engine (e.g. it has table-level locking and reads are blocked by updates). InnoDB (the default from MySQL 5.5 onwards) is more similar to Oracle, providing advanced features such as transactions, multi-version concurrency, referential integrity and row-level locking.
You can use SQL against the tables in a database as you would expect, optionally qualifying a table with its database name, i.e.
select * from table_name;
or:
select * from database_name.table_name;
Bear in mind that each command must end with a ‘;’ or ‘\G’; the latter displaying the output vertically, which is handy for tables with many columns. You will also notice, by pressing the up arrow, that the mysql client has a command history, like the bash shell.
To see the indexes on a table:
show index from table_name;
To see the full definition of a table:
show create table table_name;

Where is my data stored?

Exit out of the mysql client, back to the server’s operating system, and have a look around your MySQL base data directory, typically /var/lib/mysql:
ls -l /var/lib/mysql
You will see a directory for each database. A database’s directory contains many files of the format *.frm, which describe the structure of each table within the database. For MyISAM tables, the data (*.MYD) and indexes (*.MYI) are stored within this directory also.
InnoDB tables are stored in InnoDB tablespaces, each of which consists of one or more files, which are very similar to Oracle tablespaces. By default all InnoDB data and indexes for all databases on a MySQL server are held in one tablespace, consisting of one file: /var/lib/mysql/ibdata1. InnoDB has log files, which are the equivalent of Oracle redo logs, allowing automatic crash recovery. By default there are two log files: /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Undo data is held within the tablespace file.

Is there a Data Dictionary?

Since MySQL 5.0 the information_schema database has been available, containing data dictionary information. To see all the tables (actually views) contained in the data dictionary, within the mysql client:
use information_schema;
show tables;
And then query any of these tables, for example:
select * from information_schema.tables where table_schema='database_name';
select * from information_schema.views where table_schema='database_name';
Information_schema is defined in the SQL 2003 standard and is implemented by other major databases, e.g. SQL Server, PostgreSQL.

Who is connected to my MySQL server?

To see sessions currently connected to MySQL:
show processlist;
This will tell you:
  • which users are connected;
  • from which host;
  • to which database;
  • which commands they are currently executing.

Which users are defined in my MySQL server and what can they do?

This information is held in another standard MySQL database, called mysql. To see the users defined:
select * from mysql.user;
This shows all the users defined in the MySQL server, together with their global privileges.
By default root users do not have a password (as we saw in the first part of this blog post) and there is also an anonymous user with no password. To make your MySQL server more secure (create a password for root, remove the anonymous user and clear up a few other default security holes) it is recommended that you run the script below (on the operating system, not within the mysql client):
mysql_secure_installation
An important difference between MySQL and Oracle is that, in MySQL, it is the combination of user name and host that uniquely identify a user. So the user billy@server1 is a different user to billy@server2; they can have different passwords and different privileges. Also MySQL only allows a user to connect from a host defined for it in the mysql.user table. This means, for example, assuming the two users above are the only users defined with the user name billy, if someone attempts to connect to this MySQL server from server3 as the user billy, then MySQL will deny access.
To create a user:
create user webapp@server1 identified by 'apple';
Typically, as with Oracle databases these days, database users are pooled connections from an application (e.g. a content management or web server) and are given all privileges on the database which holds their data:
grant all on database_name.* to webapp@server1;
Ideally privileges can and should be granted at a lower granularity.
To see the database-level privileges for all users:
select * from mysql.db;
It is possible to create the user and grant privileges in one statement:
grant all on database_name.* to webapp@server1 identified by 'apple';
It is also possible to put a wildcard (%) into the host to allow a given user to connect from anywhere (quotes are needed around the wildcard):
create user billy@'%' identified by 'banana';

How do I start and stop MySQL?

On linux and unix MySQL is typically controlled through init.d scripts, as you might expect:
/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart
If MySQL fails to start, check the error log, usually /var/lib/mysql/server_name.err
If you examine the running MySQL server processes (ps -ef|grep mysqld) you will see two: mysqld and mysqld_safe. The former is the MySQL server process and the latter is a process that monitors the MySQL server process and will restart it if it has failed.

Where is the MySQL Server Configuration data?

The MySQL configuration file (the equivalent of an Oracle pfile or spfile) is usually /etc/my.cnf (or my.ini on Windows). The file is divided into different sections, each of which provides configuration data for different programs. The section labelled [mysqld] controls the behaviour of the mysqld server. The section labelled [mysql] controls the behaviour of the mysql client.
Often very little is needed in this file as the default values are fine for typical installations.

How do I Backup MySQL?

The easiest way to backup MySQL is to use the program mysqldump. This creates a logical database backup, which is a text file containing the SQL commands necessary to recreate the database objects and populate the data. You need to specify the user name, the database to be backed up and a file in which to store the backup:
mysqldump -u root -p database_name > backup_file_name
By default mysqldump will make all tables read-only, preventing updates during this logical backup. If all your tables are stored in the InnoDB storage engine then it is better to apply the –single-transaction option to mysqldump, which will create a consistent backup while leaving tables open for update.
There are many other more powerful and flexible ways to backup MySQL which are beyond the scope of this introduction (e.g. file system snapshots, MySQL Enterprise Backup, Percona XtraBackup).

How do I restore MySQL?

It is very simple to restore a backup taken with mysqldump. As the file created by mysqldump contains plain SQL, simply invoke the mysql client, providing the mysqldump backup file as input:
mysql -u root -p database_name < backup_file_name
Bear in mind that, as this is a logical backup, restore will involve applying many SQL statements and may therefore take a long time for large databases.

How do I Install MySQL?

The easiest way to install MySQL is from your operating system provider’s standard repositories. To install the server on Oracle Enterprise Linux, Red Hat or Centos:
yum install mysql-server
and then the client:
yum install mysql
However your distribution is unlikely to be providing the latest MySQL version. To obtain the latest version go tohttp://dev.mysql.com/downloads and obtain the software for your operating system (e.g. Linux rpm package files or Windows Installer installation files). For example to install version 5.5.19 of the server and client on 32-bit Oracle Enterprise Linux, Red Hat or Centos :
rpm -ivh MySQL-server-5.5.19-1.rhel5.i386.rpm
rpm -ivh MySQL-client-5.5.19-1.rhel5.i386.rpm
You can also obtain versions for other operating systems here, e.g. Windows Installer installation files.
A good way to get started is to install one of the sample databases provided by MySQL. These can be found, confusingly, on the MySQL documentation page: http://dev.mysql.com/doc/index-other.html. I have found the Sakila database, which models a DVD rental shop, very useful for getting to know MySQL.

Is MySQL free?

The community edition of MySQL can be downloaded (http://dev.mysql.com/downloads) and used for free, even for public-facing live systems.
If you want support and extra tools (e.g. the MySQL Enterprise Backup hot backup tool) then you can purchase this. Details are athttp://www.mysql.com/products.
Additionally if you embed MySQL in a product which you distribute you will need to purchase licences. Details are athttp://www.mysql.com/oem.

Summary

As an Oracle DBA I have been very impressed with MySQL’s features, reliability and ease-of-use. I hope I have wetted your appetite and given you the confidence to jump in there and have a look at those MySQL servers that are sitting in the corner of your server rooms, or to download and install the free version and have a go with it.
And now that MySQL is owned by Oracle you are not even being unfaithful.