Enable Scheduler Job Execution - Oracle 11g: Cont'd

After enabling job execution on the database you might want to check the status of individual scheduler jobs. 

This can be done by querying DBA_SCHEDULER_JOBS.

SQL> select job_name, state from dba_scheduler_job where job_name = 'test_job';

JOB_NAME                    STATE

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

TEST_JOB                    DISABLED

As seen above, test_job will not run as scheduled because it us disabled. To enable test_job run;

SQL> BEGIN 

    DBMS_SCHEDULER.ENABLE('schema1.test_job'); 

END;

/

You can also can check the progress of currently running jobs by querying ALL_SCHEDULER_RUNNING_JOBS or DBA_SCHEDULER_JOBS.

Hope this helps!

Kehinde.

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

The Alert Log

The alert log is a very important file within the Oracle Database. Though Oracle recommends the use of Enterprise Manager for monitoring, most DBAs (including the author) still prefer using the Alert Log. Alert log is a text/XML file that keeps record of messages and errors from the database in chronological order. The file also contains information abut trace and dump files created on the server.

Alert log entries are created when;

• Parameter changes are made on the database. 
• Adding/removing datafiles from tablespaces.
• Instance startup/shutdown.
• Messages to the operator console.
• Errors causing trace files.
• Errors when a materialized view is refreshed.
• Archive log switches
• ORA-00600 (internal) errors.
• ORA-01578 errors (block corruption)
• ORA-00060 errors (deadlocks)

How to find the Alert Log
The location of the alert log is specified by the background_dump_dest initialization parameter. You can get this from sqlplus using the 'show parameter' command.

SQL> set lines 400

SQL>

SQL> show parameter dump_dest

NAME                   TYPE     VALUE

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

background_dump_dest   string   /oracle/app/oracle/diag/rdbms/kPROD/trace

                               

core_dump_dest         string   /oracle/app/oracle/diag/rdbms/kPROD/trace

                               

user_dump_dest         string   /oracle/app/oracle/diag/rdbms/kPROD/trace

SQL>

SQL> !  

testsvr:/home/oracle>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert*.log

-rw-r-----   1 oracle     dba         178214 Oct 27 01:07 alert_kPROD.log

testsvr:/home/oracle>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>tail -5l alert_kPROD.log

Archived Log entry 2 added for thread 1 sequence 268 ID 0x1c3da76c dest 1:

Tue Oct 27 01:07:50 2015

Starting background process SMCO

Tue Oct 27 01:07:51 2015

SMCO started with pid=32, OS id=2248

testsvr:/home/oracle>

Viewing the Alert Log
On windows you can easily view the alert log file using any text editor. You can also install Tail for Windows, a desktop application that updates the file as it is being updated by the database engine.

On UNIX systems use either of the commands below;

tail alert.log

view alert.log

Check the links below for more ways to use the tail and view UNIX commands http://www.tutorialspoint.com/unix_commands/tail.htm
http://www.tutorialspoint.com/unix/unix-vi-editor.htm


Truncating Alert log
This can be done in two ways;

1. The alert log can be renamed like shown below. After this the system will automatically create a new alert log and start writing to it. 

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert_kPROD.log

-rw-r-----   1 oracle     dba        164763517 Oct 26 06:59 alert_kPROD.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>mv alert_kPROD.log alert_kPROD_bkp.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert_kPROD*

-rw-r-----   1 oracle     dba        164763517 Oct 26 06:59 alert_kPROD_bkp.log

-rw-r-----   1 oracle     dba              204 Oct 26 06:59 alert_kPROD_bkp.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

2. If you don't need to keep historaical records of the alert log you can simply empty the file by using the command below;

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert_kPROD.log

-rw-r-----   1 oracle     dba        164763517 Oct 26 06:59 alert_kPROD.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>cat /dev/null > alert_kPROD.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert_kPROD.log

-rw-r-----   1 oracle     dba              0 Oct 26 17:42 alert_kPROD.log

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>

Hope this helps!

Kehinde.

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

Killing Sessions in Oracle Database Cont'd

In situations where a session is waiting on a resource or performing operations such as roll back the session will not be killed until such operations are completed. When this happens, the status of the session will be changed to "MARKED FOR KILL" and will be killed when the operation is completed. This can be a little frustrating for the user if the session in question was created from a client application like SQL Navigator because the cusor/control is not returned to the user until the session is eventually terminated.

Use the immediate option of the alter system kill session command to remove the session from the system at once; 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Another alternative method is to disconnect the session from the database by killing the dedicated or shared server process from the server. This method also has the immediate option, see sample script below

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

Hope this helps!

Kehinde.

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.

Starting up the Oracle Database

We left off at ps -ef | grep LOCAL in the last post. This command is used during shutdown to kill OS processes in view of making the process faster. But as the topic suggests we are only going to explain startup here because I won't be able to discuss each topic at length if I put both of them in a single post... so you can guess what the next post will be about. Starting up the database might seem like a trivial topic for non-production DBAs which is quite understandable because the actual command is just startup [forcce/mount/nomount] but believe me there's more to it.

Before deep diving into today's topic, I'm gonna chip in a little fact about most DBAs or IT Professionals... most of the terms we use are not the actual/real technical term. A good example is our topic for today - you don't actually shutdown or startup a database... it's the instance that is started/shutdown. If we look at it critically, Oracle defines "the database" as a set of files on disk - by design these can't be started or shutdown. It also call's it's RDBMS Software "Database Nx" - where N is version number and x represents the flagship feature of that version e.g. Database 12c, Database 11g and so on... this can be a little confusing but what's paramount is to be able to make the distinction between instance, database files, Oracle - the software and Oracle Corporation - the company when situation warrants it.

The Instance is a set of memory structures and background processes that manages database files. When an instance is started, Oracle allocates a memory area called the System Global Area (SGA) and starts some background processes. 

The Database is a set of files on disk which data is stored and retrieved. Files that make up the database in Oracle are the data files, redo logfiles and control files. These files are occasionally loaded into memory for various forms of processing.

Starting the Instance
The mode of instance startup determines the state of the database. When the instance is started the database becomes available for valid users to connect. The database can be started up in either of the three modes listed below;

Nomount

SQL>startup nomount;

Mount

SQL>startup nomount;

Open

SQL>startup; | startup mount; / alter database open;

You can also startup the database using specific parameters by creating a parameter file and using same to startup the database. By default a parameter file is read from the location %ORACLE_HOME%\database (Windows) or $ORACLE_HOME/dbs (UNIX). In the default location for each platform Oracle locates the initialization parameter file by examining file names in the following order:

1. spfile$ORACLE_SID.ora
2. spfile.ora
3. init$ORACLE_SID.ora

To explicitly specify a non default initialization parameter file use the command;

SQL> startup [nomount|mount] pfile= '/any_location/pfile.ora';

Stages of database startup
When a startup command is issued, the database will read the initialization parameter file(init.ora). Oracle uses this to determine the applicable configurations, Oracle then acquires memory area for its SGA and some background processes are started. When these have been done successfully the database is said to be in NOMOUNT state at this point. Oracle then moves to read the controlfile (a binary file that keeps information about the physical structure of the database) and accesses all the database files. At this point the database is MOUNTED. After mount state the database moves to OPEN state after reading the data files and other files associated with the database. Some processes like the ARC, RECO are also started here.

It is very important for DBAs to have an in depth understanding of the various startup modes because several situations like Database Recovery, Upgrades or changing updating static parameter might require you to put the database in any of the startup modes. Visit http://docs.oracle.com for more on starting the database instance.


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.