Featured Post: Roles and Responsibilities of a DBA

Great video on soft skills for upcoming DBAs. Enjoy!

By Kevin Kline via blogs.sqlsentry.com

Featured Post: 3 Essential SQL Queries in 1.5 Minutes

How often are you stuck waiting for someone else to pull data for you? There's nothing worse than missing a deadline because someone else didn't do a 30 second query. Never again - here are the basic queries that will get you numbers instantly for pivoting, graphing, and other applications of your analysis skills, as applied to an imaginary table of sales data:
 
Get It All

select * from sales;

 
In SQL, the simplest queries are often the most powerful. This grabs every row and every column. If the table is under 50,000 rows, you should have no problem opening it in Excel. If it's bigger, the program may slow down, depending on your RAM and what other applications are running.
 
Get Columns and Rows of Interest

select date, revenue, salesperson from sales where region = 'South';

 
If your organization has too much data to pull down all at once, it's easy enough to work around. Often, you'll be looking for specific slices of the data. It may be by date, salesperson, or even a combination of the two. Again, this simple query returns *almost* all the data, giving you freedom to explore in Excel.
 
Instant Pivot Table

select salesperson,sum(revenue) total_revenue from sales group by      salesperson;

 
This query is a little more complex, but may instantly provide one of your first insights. It's doing exactly the same thing as a pivot table in Excel, summing the revenue for each salesperson. Once you become comfortable with this query, you're well on your way to more advanced functionalities within SQL!
 
That's it! I hope that reading this quick introduction pays off in spades.

By Matthew Ritter via datasciencecentral.com

Job Posting - Database Administrator

Job Title

Database Administrator

Positions Available

2

Company Profile

Education

Bachelor's degree in Computer Science or other related fields required.

An advanced degree preferred.

Relevant Certification in Database Administration preferred.

Job Requirements

Hands on Oracle database administrator with 3 years of experience installing, configuring, managing and troubleshooting oracle 9i, 10g, and 11g database software.

Requires extensive experience with Oracle RAC and multi-application environments. Requires solid understanding of server capacity planning, performance analysis and tuning, and user management. Must work closely with the server and storage engineers to design and implement high-performance and highly available environments.

Must work closely with applications in order to understand their specific requirements and guide them through the application lifecycle.

Applicant must have Architect skills – (i.e., define architecture: concepts, design, build, detailed documentation before, during and after task effort).

Applicant must be able to understand the various technology areas affected and work with those technical SMEs in building robust, stable systems

Applicant must have extensive knowledge of and experience with the span of Oracle products (i.e., Oracle Data Base, OID, Grid Control, Spatial, Partitioning, Application Server, and Data Guard). 

Applicant provides basic technical writing to describe daily operations (template provided) and security controls.

Applicant will be working in a Team environment and must have excellent communication skills, both written and verbal.

Require experience working with servers (Linux/Unix and Windows 2008).

Expert knowledge in shell scripting required.

Ability and willingness to learn new systems required.

Required Documents

Resume 

Cover Letter 

Work Location

Lagos, Nigeria

Work Schedule

Full-Time 

Posting Date

22-10-2015 

Interested applicants should submit resume to kenny_csc@yahoo.com

LiveSQL - Write SQL Online

My fellow DBAs, though this will appeal mostly our little brothers - the Database Developers... I think it's still worth sharing because we've all at some point imagined being able to write SQL scripts without having a database on your PC or Server... well Oracle has given us LiveSQL via livesql.oracle.com

The site went live one week ago on 14th October. LiveSQL is a free online tool for you to learn and code SQL on an Oracle Database. If you would like to just code away right away just click "Start Coding Now" below. You will need to login or create a free Oracle account, accept the terms and you are ready to go. 

Features

• SQL worksheet with access to an Oracle database schema
• Ability to save and share SQL script
• Schema browser to view and extend database objects
• Interactive educational tutorials
• Customized data access examples for PL/SQL, Java, PHP, C

In the screenshot below, I logged in, clicked on Start Coding Now and wrote my first piece of SQL on LiveSQL

In addition to being able to write and test scripts on the go with LiveSQL, you also have access to various tutorials to learn SQL and other features within the Oracle Database, upload SQL scripts from your computer and share your SQL sessions with other Oracle SQL enthusiasts. 

Visit livesql.oracle.com today to script, test, learn and share SQL.


Hope this helps!

Kehinde.

Shutting down the database

Shutting down a database is a routine task for DBAs. There are several situations that might require the DBA to shutting down a database instance:

• To carry out a maintenance on the database or host server.
• To perform recovery.
• A last resort when database performance is poor and you have no idea what the root cause is.
• To take a cold backup the database

Shutting down

This goes through the same process for startup but this time in reverse order.

Close Database>Unmount Database>Terminate Instance

These concepts have been explained in the post starting up oracle database

Types of Shutdown

Normal> This an impractical method of shutdown except for the test database installed on your laptop. This is because the database waits for all active users to disconnect from the database, then the database is shutdown in normal manner. One advantage of this shutdown method is that no instance recovery will be done at next startup. This is done by running the command below;

SQL>shutdown

OR

SQL>shutdown normal

Immediate> This is the most common method and somewhat the safest method of shutting down the database. It is performed by running

SQL>shutdown immediate

In this method, all new connections to the database are prevented. Then all uncommitted transactions are rolled back then the database is closed. This what most DBAs call a clean shutdown. This method can be slow at times when there are multiple users connected to the database and there are long uncommitted transactions.

Speeding up Shutdown Immediate

These techniques can be used individually or run as a chain of steps before performing shutdown immediate.

Switch logfiles (applicable for databases running in archivelog mode only)

SQL>alter system switch logfile;

Perform a checkpoint to clear dirty buffers from the buffer cache. This also creates an SCN which could be useful for recovery

SQL>alter system checkpoint;

For UNIX systems you can kill all user sessions from the operating system. Though this might be a little unconventional and somewhat risky, it makes the process faster. Get process id of all database sessions using the script below;

ps –ef | grep LOCAL

In cases where there are multiple instances on the same server (this could also be an ASM instance), eliminate other processes that are not owned by the instance to be shutdown using the command below. Refer to screen shot for better understanding

ps –ef | grep ora<INSTANCENAME>

Killing Sessions from the OS

Then select each process and terminate using;

kill -9 processID

Note the process with elaborate connection description, always ensure they are not killed. You might have multiple occurrences of these for a production database.

After this is completed you can issue shutdown immediate from SQLPLUS, and in situations where you need to bring down the database as soon as possible you can issue the shutdown immediate before proceeding to kill the OS processes.

Abort> This is not considered to be a clean shutdown because the database is brought down instantaneously without doing any form of transaction rollback or commit. Prior to Oracle 11g this method was not recommended… but in Oracle 11g, Oracle guarantees a database will always come back up from a shutdown abort except there are other underlying issues with the server or database initialization parameters. 

SQL>shutdown abort

When you need to bounce a large database (i.e. shutdown and startup the database immediately); you can issue shutdown abort, startup the database, shutdown immediate, then startup. Also note that the database performs instance recovery after a shutdown abort.  Screenshot below displays a database bounce;

Pitfalls

Shutting down the wrong database instance>there might be several instances running on a single machine, so you need to be sure you are connected to the correct instance by running define on SQLPLUS or do

SQL>select name from v$database;

Shutting down without understanding of the underlying issues>in cases where an issue is reported, you should check the alert log for errors first before going ahead with the shutdown. You can also check the status of the database by running

SQL>select open_mode from v$database;

Server restart>ensure that the database is shutdown cleanly before a server restart is performed

Violating company policy>make sure you know what regulations apply for shutting down databases in your organization to avoid sanctions

Hope this helps!

Kehinde.

Killing Sessions in Oracle Database

There's no running away from this... you have to kill sessions at some point while managing Oracle databases. A database session is essentially a user process spawned as a result of a user connections to the database i.e. a session is  created when a user connects to the database. When a session is terminated/killed, active transactions from the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

Terminating the wrong session can be destructive if the wrong session is killed, you could end up killing a wrong session that was not intended to be killed or removing a background process which could lead to instance crash/abort (I've made this error in the past - It was not a funny incident).

Several reasons that can warrant this drastic action (as the name suggests) are highlighted below.

1.You might want to perform an administrative operation and need to terminate all non-administrative user sessions

2. Wait events can be resolved by killing the blocking session which holding up the resource(s)

3. User sessions can be killed to enable batch processes run faster

4. You can use the technique to remove long running queries either to rewrite the query or run at another time when the system is less busy

Identifying sessions to be killed

You can identify session to be killed by querying V$SESSION and V$PROCESS. Database sessions can be marked for kill by selecting based on USERNAME, MACHINE, PROGRAM, OS_USER or STATUS. Sessions STATUS can be any of the following states;

• ACTIVE - Session currently executing SQL
• INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
• KILLED - Session marked to be killed
• CACHED - Session temporarily cached for use by Oracle*XA
• SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.

To get more familiar with V$SESSION and V$PROCESS run a describe on each table.

Identifying sessions for kill in a RAC database has a little twist to it. The GV$SESSION and GV$PROCESS dynamic performace views can be queried from any instance to get database wide session data. Whiile instance specific session data can be gotten by querying our good old V$SESSION & V$PROCESS from each instance.

Killing the sessions

To kill sessions at the database level, Oracle provides the alter system kill statement. Captions below demonstrate how to kill a user session;

1. Connect to the database with any named user

SQL> conn kehinde

Enter password:

Connected.

SQL> select sysdate from dual;

SYSDATE

-----------

16-OCT-2015

SQL>

2. In another session login as sysdba to kill the user session created above

SQL> select sid,serial# from v$session where username='KEHINDE';

     SID   SERIAL#

-------- ---------

    2273       145

           

SQL> alter system kill session '2273,145';

System altered.

SQL>

3. Go back to user session and try to run any script

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-00028: your session has been killed

                                                                  

For RAC databases, ensure that the kill script for instance a run instance a session as you might end up terminating a session you did not intend to terminate if the script is run on another instance.

To kill multiple sessions, say all inactive sessions on the server you can use script below to genrate the kill script and run as a batch;

Script

SELECT NVL(s.username, '(oracle)') AS username,

       s.osuser,

       'alter system kill session '''||s.sid||','||s.serial#||'''',

       'kill -9 '||p.spid,

       s.sid,

       s.serial#,

       p.spid,

       s.lockwait,

       s.status,

       s.module,

       s.machine,

       s.program,

       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM   v$session s, v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY s.osuser;

For unix systems you can also kill sessions from the operating system level by using the command kill -9 spid, this is also selected as part of the script above. Alternatively you can run ps -ef | grep LOCAL from the OS to identify process ids to be killed. Note that you need to be very careful while selecting processes to be killed with this command. I will show the pitfalls and how to avoid them in the next post which will be on "Startup & Shutdown - Bouncing the Database". 

Hope this helps!

Kehinde.

Who is a Database Administrator

This should actually be my first post... the reason I started the blog is to create a repository for solutions to issues I have encountered and resolved and also deem appropriate (legally/morally) for the public domain,  hence the lack of an introductory post. So, this post will serve as an advise/reference for new IT professionals that are trying to make a choice about which field to specialize in and the ones that the Database Administration function has fallen upon.

Typical DBA's desk.

Without any further ado let me introduce you to what we Database Administrators do and why big organisations need us regardless of their line of business.

In simple terms, a Database Administrator is responsible for performance, integrity, availability and security of business data of organisations. Database Administrator roles vary depending on the type of database, the processes they administer and the capabilities of the database management system (DBMS) in use. Every organisation has a corporate/core database which all or most of their applications interface with. Various industries have different names for this central database which acts as the heart of their business systems. In addition to this central database, ancillary applications usually have their own self contained databases which it's application specific data are stored.

Typical day-to-day duties of Database Administrators popularly called DBAs include;
 - Implementing, supporting and managing the corporate database.
 - Ensuring optimum performance of the database.
 - Designing and configuring database objects such as tables, views, indexes functions and procedures.
 - Ensuring data integrity, availability and security.
 - Deployment and monitoring database servers.
 - Designing and implementing backup and data archiving solutions.
 - Planing and implementing solutions for disaster recovery.
 - Analyse and report on corporate data to help shape business decisions.
 - Produce entity relationship & data flow diagrams and database normalization schemata.


Although there are no hard and fast rules on the educational roadmap to becoming a DBA, typically a DBA should have a bachelor's degree in Computer Science or  other related disciplines. Most employers favour those with professional certifications of some sorts from leading DBMS like Oracle Database, Microsoft SQL Server, IBM DB2, Sybase or MySQL. For senior DBAs it is desirable to have an MBA in Management Information Systems, MSc in Database Management/Computer Systems.

As a DBA, you have to continually improve on your skills and expand your areas of expertise because it is practically impossible to learn everything in this space. Let me put my premise in perspective - Oracle 11g Database has over 358 system parameters with each one having 4 properties that could have at least 2 possible values. I'm not going to leave you to do the math because you might not get my point. This implies that there are over (358^4)^2 different configuration options for an Oracle 11g database. I don't have a calculator capable of computing the actual value, but I know it is a very large number - ultimately no DBA knows it all.

Hope this helps!

Kehinde.