Learning About Performance I've found myself at a certain disadvantage lately, specifically in regards to understanding tuning. I don't like being at a disadvantage...I'm competitive that way.

I understand, from a very high level of what needs to be done, but I could not prove my theories. Well, I could, sort of, but it wasn't necessarily a reasoned or logical approach. I just tried all possible permutations. There's a certain time constraint with that method and when you're talking about huge volumes of data (i.e. datawarehouses), time is short.

I've decided it's time to change that, so I'll begin to peck away ever so slowly.

In Usage Tracking, there is a table called S_ETL_TIME_DAY. It's a sister table to S_ETL_DAY which is your everyday time dimension. S_ETL_TIME_DAY breaks down a single day into hours and minutes which means there are 1440 records (24*60*60).

Somewhere I saw the following SQL:

SELECT DISTINCT hours
FROM s_etl_time_day

which just returns the hours in the day (24). I've pondered on whether DISTINCT is a bug, but it seems fairly innocuous here.

What about this though?

SELECT rownum - 1 hours
FROM dual
  CONNECT BY LEVEL <= 24;

Which one is faster?

Let's try an explain plan

Query 1

S_NQ_SCHED@TESTING>EXPLAIN PLAN FOR
  2  SELECT rownum - 1 hours
  3  FROM dual
  4    CONNECT BY LEVEL <= 24;

Explained.

Elapsed: 00:00:00.05
S_NQ_SCHED@TESTING>@EXPLAIN

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1731520519

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=24)

15 rows selected.

Query 2

S_NQ_SCHED@TESTING>EXPLAIN PLAN FOR
  2  SELECT DISTINCT hours
  3  FROM s_etl_time_day;

Explained.

Elapsed: 00:00:00.04
S_NQ_SCHED@TESTING>@explain

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 878743397

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    24 |    72 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE       |                |    24 |    72 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| S_ETL_TIME_DAY |  1440 |  4320 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

9 rows selected.

Sadly, about the only thing I can usually see in an explain plan is whether an index is being picked up or if there is a nested loop. I'm not going to worry about all of it now...this is just a start. More to get me in the habit.

Next I set up tracing since "reading" the file is so much easier now.

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HOURS';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Here's the output:

Query 1	Query 2

What does it all mean? I am sure you know or understand better than I. For me, I just need to create the habit. I'll read (more) about the specifics of the explain plan and the output from tkprof. If you want to explain, feel free. If you want to do it in your own post, link it up or you can write it here. I'd be happy to host it.

Labels: dba, performance, tuning

  ¶ 3/03/2010 12:37:00 AM   8 Comments Links to this post

  DBA_TABLES vs DBA_OBJECTS

CJUSTICE@TESTING>SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Last night I was installing the Unit Testing repository for SQL Developer for a fun little post. After installing the repository, I just did a brief sanity check.

CJUSTICE@TESTING>SELECT owner, COUNT(*) c
  2  FROM dba_objects
  3  GROUP BY owner
  4  ORDER BY 1;
  
  OWNER                                   C
------------------------------ ----------
...snip
SI_INFORMTN_SCHEMA                      8
SYS                                 22970
SYSMAN                               1341
SYSTEM                                454
S_NQ_SCHED                              3
TSMSYS                                  3
WMSYS                                 242
XDB                                   682

27 rows selected.

Strange.

I created a user, UNIT_TESTING, to house the data and fed it to SQL Developer. Did I create the user?

CJUSTICE@TESTING>SELECT COUNT(*) 
FROM dba_users 
WHERE username = 'UNIT_TESTING';

  COUNT(*)
----------
         1

Yeah.

I check DBA_OBJECTS using UNIT_TESTING as the predicate:

CJUSTICE@TESTING>SELECT * FROM dba_objects WHERE owner = 'UNIT_TESTING';

no rows selected

Really?

CJUSTICE@TESTING>SELECT table_name
  2  FROM dba_tables
  3  WHERE owner = 'UNIT_TESTING';

TABLE_NAME
------------------------------
UT_LIB_TEARDOWNS
UT_LOOKUP_CATEGORIES
UT_LOOKUP_DATATYPES
UT_LOOKUP_VALUES
UT_METADATA
UT_TEST
UT_TEST_ARGUMENTS
UT_TEST_IMPL
UT_VALIDATIONS
UT_TEST_IMPL_ARGUMENTS
UT_LIB_STARTUPS
UT_LIB_VALIDATIONS
UT_LIB_DYN_QUERIES
UT_SUITE
UT_SUITE_TEST
UT_TEST_IMPL_VAL_RESULTS
UT_TEST_IMPL_ARG_RESULTS
UT_TEST_IMPL_RESULTS
UT_TEST_COVERAGE_STATS
UT_TEST_RESULTS
UT_SUITE_RESULTS
UT_SUITE_TEST_RESULTS

22 rows selected.

WTF?

Does this mean that my data dictionary is corrupted? This is a sandbox so it is very well possible...just never seen this kind of thing before.

Labels: dba, sql developer, wtf

  ¶ 3/02/2010 10:38:00 AM   0 Comments Links to this post

  Database Security? I'm not sure what are the question marks are lately...I can't think of better titles so perhaps it's me guessing at the title.

I'm sadly dependent on SQL*Plus...and when I mean "sadly" I mean I go to great lengths to get the Windows version which was obsolesced (sp?) in 11gR1. I could never get it quite right, the output I mean, but it was just so easy. Type in your SELECT statement, hit Enter and go. Easy. Yes, yes, I know, you can use the shell version too...I just liked the Windows version despite all it's quirks.

Anyway, as part of my login.sql script, I have the following 2 queries:

prompt ***roles granted***
SELECT role FROM session_roles;
prompt
prompt ***system privileges granted***
SELECT privilege 
FROM user_sys_privs
UNION
SELECT privilege
FROM role_sys_privs
WHERE role IN ( SELECT role FROM session_roles );

Each time I login or connect to a different database, this script runs. The results of those 2 queries, the first shows the roles I have been granted and the second query returns the system privileges I (or one of my granted roles) have. I'm sure I got this from Tom Kyte...I've been using this for years and I can't remember when or where I began to use it.

Having changed jobs quite a few times over the past few years, I've gotten to see many different environments. These scripts give me a quick glance at what I am able to do in a particular database, which is very helpful.

Where am I going? Not really sure.

I know that system level privileges are usually a no-no. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, those are the basic items that you need to build objects in your own schema. CREATE ANY TABLE, CREATE ANY VIEW and CREATE ANY PROCEDURE will allow you create said objects in any schema (except SYS...I think, I don't have my power cable thus I can't start up my vm which contains my database).

Having read AskTom for so many years, I know or understand that system level privileges are bad, very bad.

At one former job, in the production database, not only did I have CREATE ANY TRIGGER, I had EXECUTE ANY PROCEDURE. I won't go into why or how those are very, very bad, you can research those yourself. Or you can just buy David Litchfield's The Oracle Hacker's Handbook: Hacking and Defending Oracle.

Pointing those types of things out though can be tricky...to put it mildly. Lots of politics involved. Trust me on this.

Hopefully you can glean a point or at least some information from this post. My mind is not all there due to the massive brain infection I have.

Labels: dba, oracle, security

  ¶ 2/15/2010 10:16:00 PM   2 Comments Links to this post

  Database Table Size I've always wondered how big a table is...up until recently I depended on the DBAs to retrieve such information for me.

Thanks to my good, and very helpful, friend, Mr. Thomas Roach, I no longer have to wait or bother the DBAs.

%_SEGMENTS contains a column called BYTES. Use this column to determine the size of your table, with just a little math.

SELECT segment_name, SUM( bytes ) / 1024 / 1024 mb
FROM user_segments
GROUP BY segment_name
ORDER BY 1
/

SEGMENT_NAME                           MB
------------------------------ ----------
BMP_DIVNBR_CUST                    13.375
BMP_DIVNBR_JOINFACT               37.0625
BMP_DIVNBR_PROD                    13.375
BMP_DIVNBR_SALES                  78.6875
CUST                                  940
DIV                                  .125
IDX_CUSTSKDIVNBR_SALES          2676.6875
IDX_PRODSKDIVNBR_SALES          2701.6875
JOIN_FACT                        4298.125
PIM                                   312
PK_DIVSK                             .125
PK_PIMSK                                8
PK_TMSK                               .25
PROD                            2274.4375
SALES                          116122.375
TIME                                 .875
UQ_CUSTSKDIVNBR_CUST               40.125
UQ_PRODSKDIVNBR_PROD                  144

There's a plethora of these scripts out in the wild...but I was originally inspired by helping Mr. Neil Kodner out back in November (which I refer to as the "missing" month). Read his take on it here.

Labels: dba, howto, sql

  ¶ 2/01/2010 11:47:00 AM   6 Comments Links to this post

  UNDO Brain Damage I "met" Mrs. Pedersen on the oracle-l mailing list. I can't remember what the topic was, but she supplied this detailed message full of stats and opinion. I immediately went out and looked for her blog, but couldn't find it. She didn't have one.

So I dropped her a line and offered her some space here.

I won't take (all) the credit, but she has since started her own blog, DBA Kevlar.

Automatic Undo can be one of those areas that make you go "hmmmm..." I am fully aware that the documentation that came with Oracle 9i new features for DBA's when Automatic Undo was introduced clearly claimed, "You won't receive another ORA-01555 again!" yet Oracle hasn't quite lived up to that promise in either 9i or 10g and I haven't had the chance to prove otherwise in 11g.

I had the opportunity to revisit my old friend, "ORA-01555" when one of our environments began to page a fellow DBA with the issue the other day. I became intrigued, as I often am by any database oddity and when I queried the amount of undo the process was consuming, it didn't add up with the amount of undo that was being consumed. In our environment, it is not uncommon for 800GB or more of undo to be utilized, but it became quickly apparent something was amiss when looking at the query below vs. the amount of undo allocation of 1.2TB that had been recently allocated by the DBA on call:

select distinct
  vs.sid, vs.osuser,
  vs.process,
  vs.sql_id,
  round((vu.undoblks*32768) / (1024*1024*1024)) GB,
  vu.tuned_undoretention
from v$undostat vu, v$session vs
where vu.maxqueryid in vs.sql_id
group by
  vs.sid,
  vs.osuser,
  vs.process,
  vs.sql_id,
  vu.undoblks,
  vu.tuned_undoretention
order by vs.sid

TOTAL GB used from the query above: 45GB

The first aspect I chose to take into consideration is the "tuned undo retention", as many folks forget that the Oracle parameter UNDO_RETENTION can be over-ridden by the SMON if a process requires blocks from the undo to stay unexpired over the set retention period. I have found in my experience, that if there is a rollback issue with an existing process, inspecting the TUNED_UNDO_RETENTION is an important area to start as a DBA.

After verifying that there were over 7000 different maxquerid's in v$undostat, but that only 20% of them were returning when we were looking at active sessions, I queried to find out what queries have undo blocks set with expirations greater than the set time, (currently 43200 seconds) that were also not in active sessions.

select distinct 
  vu.maxqueryid, 
  vu.maxquerylen,
  vu.tuned_undoretention 
from 
  v$undostat vu, 
  v$session vs
where vu.tuned_undoretention > 43200
  and vu.maxqueryid not in  vs.sql_id
group by 
  vu.maxqueryid,
  vu.maxquerylen, 
  vu.tuned_undoretention;

One SQL_ID came up as the offender with the majority of undo blocks retained over the retention period and not in active sessions: 1zuumhmqc93zj

The maxqueryid we are looking for isn't active, but if we searched historical SQL, then we found our statement:

select sql_text
from DBA_HIST_SQLTEXT
where sql_id = '1zuumhmqc93aj';

select col1 
from prod.Tbl1 
where cnt = 1

Why would a select statement create undo blocks and how could it be using undo if it isn't a current running process and yet showing active undo blocks???

If the query is not active - what could it be?

Answer: PREVIOUS

select vs.sid
from v$undostat vu, v$session vs
where  vu.max_queryid=’1zuumhmqc93aj’
  and vu.maxqueryid = vs.prev_sql_id;

SID
---
817

We now have the SID currently running that is the cause, why worry about the SQL anymore? It's not the current SQL, but a previous sql_id in an active undo process in v$undostat, so at this point, we need to focus on the SID.

So what transpired here?

Upon inspecting SID 817, it was found to be a background session for Oracle two weeks back where someone had issued a kill session command on a poorly coded process! Oracle decided that the select, which was a full scan on a 59GB table, used a cursor into a for loop, was going to take just over two weeks to rollback the killed session! The process to rollback was so sublime, that it hadn't come up as a red flag on any monitoring script or tool, including OEM!

Since no actual data block changes had been performed, it was perfectly safe to stop the rollback and execute a session kill. To free the undo vs. allowing the kill command to continue, we selected the OS Session from V$SESSION for SID 817, proceeded to kill the process at the OS level and issued a reset on the undo tablespace to clear the remaining undo blocks that were active and/or unexpired:

begin
  reset_undo_stat();
end;

By resetting the undo, we were able to free up the undo that had been tuned to be retained and were able to disregard the rollback on a select statement that had been killed.

Once we reset the undo, we promptly informed the developer that he/she was never to attempt to use a 59GB non-partitioned table without indexes for a cursor ever, ever again!! :)

Moral of this story
If you have significant undo retained by non-active, non-transactional processes, you should inspect them carefully to verify that they are valid utilization of your undo tablespace. If not, you could receive ORA-01555, snapshot too old errors on transactions that would normally complete successfully, causing incomplete transactions, more rollback occurrence and users asking you why their processes didn't finish.

BIO
Kellyn Pedersen is a Performance Tuning and Operations Database Administration Specialist with 10 years of DBA experience in Oracle, SQL Server and MySQL. She is a Database Administrator and Developer at I-Behavior in Louisville, CO, is busy writing articles, sharing tips on her own blog, DBA Kevlar, and will be presenting at RMOUG 2010 in Denver, CO.

She lives in Westminster, CO with her husband and children - who are wonderfully willing to put up with her strange fascination with Oracle's Cost Based Optimizer.

[ kellyn dot pedersen at gmail | Twitter | LinkedIn ]

Labels: dba, kpedersen

  ¶ 11/29/2009 08:41:00 PM   0 Comments Links to this post

  Playing With PARALLEL I'm trying to learn about PARALLEL and how it affects SELECT statements in a DW/DSS (non OLTP) environment. I can't really say I know what I'm doing or what I'm looking at, but I'm trying.

I had a DBA strongly discourage me from using PARALLELism on reporting tables (i.e. facts or dimensions). I think he even tried to explain it to me...but it was even more over my head at that time. I think it had something to do with all the processes kicked off then multiplied by the number of users running queries on those tables. "It will bring the database to its knees" he use to say. I never fought much on that because 1, he explained it to me 2, I trusted him and 3, he showed me once all the processes spawned during a typical data load operation. (I thought that was really cool by the way, all those neat little processes doing little chunks of work).

He did however let me (us) use them on staging tables. This is typically where our transformations took place. INSERTs and/or partition swapping.

Anyway, I was reading the 10gR2 docs, the Data Warehousing Guide to be exact on Using Parallel Execution. Lots of neat pictures...which is good for me.



Then I decided to see if I could see what the run times on a SELECT COUNT(*) would be given different levels of parallelism. If you want to know if a table or index has PARALLEL enabled, you need to look at the DEGREE column in %_TABLES and %_INDEXES. I spent about 20 minutes looking for PARALLEL in those tables before I remembered that.

CREATE TABLE big_table 
AS
SELECT *
FROM dba_objects;

I know have 74,431 records to play with. It's not a lot, I know, I just want to see what happens. I then created an anonymous block to:
1. loop through and change the PARALLELism starting with 1 up to 8
2. get the start time from DBMS_UTILITY.GET_TIME
3. loop doing 20 SELECT COUNT(*)s on table table
4. get the end time
5. get the total time
6. print it out
7. put it in a spreadsheet to compare the results side by side

DECLARE
  l_start NUMBER;
  l_end NUMBER;
  l_total NUMBER;
  l_dummy INTEGER;
BEGIN
  FOR j IN 1..8 LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE big_table PARALLEL ' || j;
    dbms_output.put_line( 'PARALLEL (DEGREE):  ' || j );
    FOR i IN 1..20 LOOP
      l_start := dbms_utility.get_time;

      SELECT COUNT(*)
      INTO l_dummy
      FROM big_table;

      l_end := dbms_utility.get_time;

      l_total := l_end - l_start;

      dbms_output.put_line( 'Run ' || LPAD( i, 2, '0' ) || ':  ' || LPAD( l_total, 3, '0' ) );
    END LOOP;
  END LOOP;
END;
/

And here are my results:



Sorry that it's an image...I can't seem to get decently formatted text into Blogger lately.

For this silly, unscientific test, PARALLEL set to 1 (i.e. NOPARALLEL) was the fastest all the way down. I don't know what it means or if it's even valid, but hey, I'm trying. I figured that if I posted something way above my head all you nice folks out there would happily correct me if I mistated something. I am avoiding any statements, if you couldn't tell.

Labels: dba, parallel, performance

  ¶ 11/24/2009 12:56:00 AM   2 Comments Links to this post

  APEX: Create Workspace Home --> Administrative Services --> Manage Workspaces --> Create Workspace

Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.

I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.



Of course I had to see if for myself:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf

5 rows selected.

That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:



I give it a name, APEX_TEST:



On Step 2, I set "Re-user existing schema?" to "No"



Step 3:



Step 4, confirming details:



Confirmation page:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf
         6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf

6 rows selected.

As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.

This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.

Labels: apex, dba

  ¶ 11/21/2009 07:00:00 PM   3 Comments Links to this post

  Free Oracle Developer/dba There is a serious lack of work in the Tampa market and desperate times call for desperate measures.

Now, I've always wanted to do this, but was never in a position to do so financially...I'm still not, but something is way better than nothing.

I'm going to offer my services for free.

Not forever mind you, somewhere between 2 and 4 weeks. It is negotiable.

If it works out, i.e. you like what I can bring to your organization, I prove that I pick new systems up quickly and I fit in well with your team; and I like working in your organization, then you pay me for that time worked and I sign up full-time either as a permanent employee or some sort of contract.

If it doesn't work out, we part ways and no one is worse for wear. You get free work from me and I get to experience a new environment (i.e. meet new people, expand my network, etc).

You can view my resume here (permanent link up top coming soon).

Some basic highlights:
PL/SQL: Expert (I don't like that term)
DBA: Junior to Mid-level (or DBA in lowercase, "dba")
APEX: Worked with up to version 3.2, admittedly rusty, but passionate about the product.
Design: Love modeling data. Model just about everything in my head (in normal life). Use Visio extensively for visual representations. Experience with SQL Developer Data Modeler, ERwin, etc.
Documentation: Give me a wiki or something similar and I'll document just about everything that I do.

That's it. Contact info is on my resume or up top on the "email" icon.

It's now posted on craigslist as well.

Labels: database, dba, development, jobs, plsql, work

  ¶ 10/27/2009 12:25:00 PM   3 Comments Links to this post

  "Shrink" UNDO Tablespace Having completely screwed up my sandbox database, I decided to do a little house cleaning. Yes, I know, I would probably break something. But I have backups now.

Specifically, I wanted to reclaim some space. More specifically, I didn't like the UNDO data file being 6GB. Completely unnecessary.



So I found this thread on how to "shrink" it.

1. Create a new temporary UNDO tablespace.
2. Point your database to the new UNDO tablespace.
3. Drop the original UNDO tablespace.
Optional
4. Create new UNDO tablespace matching the original.
5. Point your database to the old new UNDO tablespace.
6. Drop the new old UNDO tablespace.
7. Remove the old files from the filesystem

--STEP 1
CREATE UNDO TABLESPACE undotbs2 DATAFILE 'UNDOTBS_02.DBF'
  SIZE 1G
  AUTOEXTEND ON;

--STEP 2
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

--STEP 3
DROP TABLESPACE undotbs INCLUDING CONTENTS;

--STEP 4
CREATE TABLESPACE undotbs DATAFILE 'UNDOTBS_01.DBF'
  SIZE 1G
  AUTOEXTEND ON;

--STEP 5
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs;

--STEP 6
DROP TABLESPACE undotbs2 INCLUDING CONTENTS;

And then you can remove the old datafiles.

Just a reminder, this is a sandbox used for testing, not a production database.

Labels: dba, testing

  ¶ 9/25/2009 03:54:00 PM   3 Comments Links to this post

  Learning By...Breaking? I caught Chen Gwen mocking me the other day on Twitter.



That was in response to Learning By Breaking.

Come on. Give me a break.

Now if I were a big DBA (not a little dba), I would not be so haphazard in my approach. This is a sandbox after all.

Naturally, I was at it again today.

I performed my very first recovery today. That is if you consider

ALTER DATABASE RECOVER;

a recovery. To make it worse, I guessed at the syntax.

Just a reminder as to what I am doing. I am testing my migration script (a couple of hundred DDL/DML and code changes) in an object only copy of production in my own private idaho...sandbox. I run the script, find the errors (usually order of execution related), fix the script(s), flashback the database and then rerun. To make my life someone easier, I created a script that performs the necessary commands to flashback the database.

ALTER DATABASE CLOSE;

FLASHBACK DATABASE TO RESTORE POINT PRE_DEPLOY;

SHUTDOWN;

STARTUP;

ALTER DATABASE OPEN RESETLOGS;

Then I just run @flashback after disconnecting from my other session.

Somewhere along the way I believe I have managed to destroy my database. How did I do that? Well, I don't know. I do know I have accidentally dropped the restore point (2 times) prior to flashing back. When that happens, I clean up the database with one of 2 methods:
1. Use my handy-dandy clean up script to remove all the objects
2. DROP USER test CASCADE;

That's followed by a full import (via datapump) of the 2 necessary schemas.

So tonight I went to flashback and...well, something went really wrong. Something about I needed to recover the system datafile (I didn't save that one unfortunately).

ALTER DATABASE RECOVER?

Nope.

ERROR at line 1:
ORA-00279: change 9235581 generated at 09/22/2009 21:24:09 needed for thread 1
ORA-00289: suggestion : FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.ARC
ORA-00280: change 9235581 for thread 1 is in sequence #23

Sweet...Oracle's giving me a suggestion. What does that mean? (Me guessing again):

ALTER DATABASE RECOVER ARCHIVELOG 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.arc
*
ERROR at line 1:
ORA-00905: missing keyword

ALTER DATABASE RECOVER LOGFILE 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.ARC
'
*
ERROR at line 1:
ORA-00308: cannot open archived log 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U
_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

That second one looked closer right? Meanwhile, I began a backup of the archived redo logs.

BACKUP ARCHIVELOGS ALL;

I wasn't really sure what that was going to do, but I needed some space.

Meanwhile, back in SQL*Plus I keep trying different commands.

SQL>ALTER DATABASE RECOVER TABLESPACE SYSTEM;
ALTER DATABASE RECOVER TABLESPACE SYSTEM
*
ERROR at line 1:
ORA-00275: media recovery has already been started

SQL>ALTER DATABASE RECOVER TABLESPACE USERS;
ALTER DATABASE RECOVER TABLESPACE USERS
*
ERROR at line 1:
ORA-00275: media recovery has already been started

SQL>ALTER DATABASE RECOVER;
ALTER DATABASE RECOVER
*
ERROR at line 1:
ORA-00275: media recovery has already been started

SQL>ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

I'm pretty sure I picked those up in a Google search (in other words, I didn't bother to click through). Out of frustration, I killed the instance (net stop oracleservicetesting) and tried to bring it back up.

STARTUP MOUNT;

SQL>RECOVER DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTING\SYSTEM01.DBF';
ORA-00283: recovery session canceled due to errors
ORA-38798: Cannot perform partial database recovery
ORA-38797: Full database recovery required after a database has been flashed back

For some unknown reason, I went back into RMAN (the session I didn't save to cut and paste) and issued

RECOVER DATABASE;

It's doing something...

Did I get it?

Lo and behold it worked. I was able to get back into my precious sandbox.

What's the moral of the story? I have no idea. Like I said last time, I learned something...I just don't know what it is yet.

Wait, I did learn one thing...Oracle is a pretty incredible piece of software if the likes of me can go in, muck it up, and it still comes back to life. That my friends, is pretty impressive.

Labels: dba, rman

  ¶ 9/22/2009 11:39:00 PM   3 Comments Links to this post

  Learning By Breaking I've spent the last couple of days building and rebuilding my sandbox database.

I can't get it just right.

Last night while running my scripts, none of the foreign key clauses would work. Primary key doesn't exist? WTF?

Do a quick check on the count of indexes...it's about 10% of what it should have been.

Oh yeah, when using REMAP_TABLESPACE, it helps to include the index tablespaces as well. $#*@~!

So I queried all the index tablespaces and put them into my parameter file for impdp.

Only to have my database freeze about 70% of the time. Being just a dba and not a DBA, I figured I'd just add a datafile. OK, so the first time I created it is was 1GB. I didn't bother to resize it.

Then it hung up again. (Of course I'm not bothering to find the root cause of the issue, I'm just brute forcing it). Somewhere in the index creation (reading from the console output anyway).

Killed it again.

Cleaned out the schema again.

Rinse and repeat a couple more times.

Then I decided to drop a datafile, the small one I created up above. Yes, I could have resized it...but I'm just a rookie. Used the OFFLINE DROP, shutdown the database and deleted the datafile.

Reran the impdp.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'USERS02.DBF'

Oops.

OK, let's drop the tablespace and try again.

DROP TABLESPACE users INCLUDING CONTENTS;

ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

OK, let's create a new tablespace

CREATE TABLESPACE USERS2 DATAFILE 'USERS2_01.DBF' SIZE 10G AUTOEXTEND ON;

Now I'll make that one the default

ALTER DATABASE DEFAULT TABLESPACE USERS2;

Now I'll drop the USERS tablespace

DROP TABLESPACE users INCLUDING CONTENTS;

ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

Killing me...

So I dropped all the users (just a reminder, this is just a sandbox) and tried it again.

SYS@TESTING>DROP TABLESPACE users INCLUDING CONTENTS;

Tablespace dropped.

Finally!

For whatever reason, I'm a glutton for punishment. I don't like the look of "USERS2" so I want to go back to "USERS." Let's go through this again.

ALTER DATABASE DEFAULT TABLESPACE USERS;

Database altered.

DROP TABLESPACE users2 INCLUDING CONTENTS;

Tablespace dropped.

Add a second datafile just for fun:

ALTER TABLESPACE USERS ADD DATAFILE 'USERS_02.DBF' SIZE 10G AUTOEXTEND ON;

Done.

Obviously no one should let me do this sort of thing anywhere near a production database. I know just enough to be really, really dangerous. It is find having to go through this (painful) exercise once in awhile in a closed environment. Helps to understand a little bit about how the database works. I'm not sure what exactly I did learn, other than I am a moron...but it was...fun.

Labels: dba, funny

  ¶ 9/21/2009 06:37:00 PM   0 Comments Links to this post

  DBA: Poor Man's Data Vault Nothing against women, so don't start with me. By "Man" I mean (hu)Man. Though there are plenty out there that would argue against any DBA, in any way, resembling a human.

Back in March I started this project on Google Code. Here I am 4 months later and I've finally gotten around to it.

What is it?
Version 1.0, or better, Version 0.1, will simply require you to set a context prior to deployment. All that means is this:

CJUSTICE@TESTING>BEGIN
  pmdv_work_pkg.create_work
    ( p_name => 'TICKET #44', 
      p_description => 'CLEANING UP CJUSTICE SCHEMA' );
END;
/

What's the big deal with that?
Well, it depends. The idea was originally sparked by watching my DBAs deploy my projects at a previous job. After deployment, they would run this script, enter the ticket number, start and stop time and it would spool off this report. I never looked at the report, but guessed that it had something to do with Security or Auditing. So I made the grand leap to assume it was all the objects that were affected by the deployment. It was all an assumption though as I was, on occasion, asked to provide details on objects that I deployed by the Security team.

What does it do?
It's pretty simple actually. One trigger, ON DATABASE BEFORE DDL is set up. That calls a package with the relevant exceptions, and determines whether the context has been set (if the other exceptions are not met). If all the exceptions are met and the context is set, you can deploy your changes (DDL). If not, you get a pretty error message like this:

CJUSTICE@TESTING>@packages/pmdv_work_pkg.pkb
CREATE OR REPLACE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: PMDV:  DEPLOYMENTS NOT ALLOWED!
ORA-06512: at line 14

When you first create your work (order), the start date and end date are set (end_date defaults to 30 minutes, but time can be added if necessary). Also, all INVALID objects are captured prior to deployment. Once you are finished, you issue the following commands:

CJUSTICE@TESTING>EXEC pmdv_work_pkg.end_work;

COMMIT;

When you issue the END_WORK command, the INVALID objects are captured again (DURING), the work record updated, then any changed objects during the deployment window are captured (from DBA_ALL_OBJECTS.LAST_DDL_TIME).

For those of you worried about putting such a thing in production, I am also providing a trigger (AFTER DDL) and a table to capture the DDL in your environment. That way you can note the exceptions and add them to the code before ever throwing such a thing in production. Not that I think many of you would do that...especially without more rigorous testing at the very least.

Testing is my next step. I know, it's backwards. I just want this out the door. I want someone to look at it and say, "Hey Chet, that's a piece of crap." or "You're nuts if you think I would ever put this on a production system!"

I hear you. Testing will be done. Test cases will also be provided so that you can verify the results as well. Hopefully, if enough of you nit-picking, anal-retentive DBAs take a look at it and critique it (good and bad), I can make something of it. Something that would be useful to everyone. So go crazy on it please.

Here's the package spec for PMDV_WORK_PKG:

FUNCTION CAN_DEPLOY_DDL RETURNS BOOLEAN
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ORA_SYSEVENT   VARCHAR2  IN
 P_ORA_LOGIN_USER  VARCHAR2  IN
 P_ORA_INSTANCE_NUM  NUMBER   IN
 P_ORA_DATABASE_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_TYPE  VARCHAR2  IN
 P_ORA_DICT_OBJ_OWNER  VARCHAR2  IN

CAN_DEPLOY_DDL is used by the trigger, PMDV_BEFORE_DDL to determine whether or not the DDL is allowed. Currently, I have 3 exceptions:
1.  If the ORA_LOGIN_USER = SYS. DDL OK.
2.  If the ORA_LOGIN_USER IS NULL. I noticed this behavior on a 10g instance. DDL OK.
3.  By default Oracle does not allow objects to be created in the SYS schema. Unless your system is compromised in some way, it is reasonably safe to assume that if the ORA_DICT_OBJ_OWNER = SYS, DDL is OK.
4.  PMDV_CONTEXT is set. DDL is OK.

If you see anything in that list that jumps out at you, please let me know. I absolutely do not want to introduce something into the wild that could be harmful.

FUNCTION CREATE_WORK RETURNS NUMBER
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_NAME    VARCHAR2  IN
 P_DESCRIPTION   VARCHAR2  IN
 P_ESTIMATED_TIME  DATE   IN     DEFAULT

This gets it all started. It accepts 2 strings, they can be anything, there is no validation check on them. P_ESTIMATE_TIME defaults to 30 minutes. You can either create the work (order) with the default, it give yourself an hour, or however much time you need really.

PROCEDURE END_WORK

This ends your session, captures INVALID and CHANGED objects and clears the context.

PROCEDURE EXTEND_DEPLOYMENT_WINDOW
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TIME_TO_ADD   NUMBER   IN

Kind of self explanatory. Run this in the same (SQL*PLus) session to extend your window.

PROCEDURE JOB_LISTENER

When you issue the CREATE_WORK statement, the PMDV_LISTENER job is enabled and runs once a minute to check if your session has expired. If you finish in 5 minutes, you don't necessarily have to issue the END_WORK call. The job will expire the work (order) after the allotted time.

PROCEDURE RECONNECT
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_CLIENT_IDENTIFIER  VARCHAR2  IN

RECONNECT allows you to open up another session and re-enable the context (i.e. allow you to deploy). You can find this information in PMDV_WORK.CLIENT_IDENTIFIER column.

Since I'm using DBMS_SESSION.UNIQUE_SESSION_ID, you can only have 1 deployment per SQL*Plus session. All you need to do to get around that is DISCONNECT and login again.

For the love of Pete, give me some feedback. Is this something you could ever use? Am I silly (well, you don't have to answer that specifically) for thinking this up? Any and all comments will be most appreciated. I've spent a significant amount of time over the last couple of weeks getting this together...hopefully to some end.

Where do I find this?
The code can be found here. I'll be wrapping it up all nice and pretty over the next couple of days. I'll create a download package as well. The wiki page can be found here.

Oh yeah, did I mention, I built an Apex front end for it?

Here's what that looks like.

The Work (Order) Screen:


And the Work (Order) Details Screen:

Labels: dba, pmdv, security

  ¶ 7/14/2009 12:28:00 AM   0 Comments Links to this post

  Oracle: Getting Started A friend of mine recently expressed interest in learning Oracle.

Here's what I sent him:

Start here:

Oracle Concepts Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm

You don’t have to read everything, but it’s probably the best place to start. I’d be more than happy to answer some of the more “inane” questions (How do I do this? How do I connect? Etc).

Oracle Documentation (List of Books) - http://www.oracle.com/pls/db111/portal.all_books

Don’t be overwhelmed by that. It’s ridiculous how much stuff there is out there. I list some books out by discipline:

DBA
--  2 Day DBA - http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/toc.htm
----  Easiest place to start (after the Concepts Guide)
--  Oracle Database Administrator’s Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/toc.htm
----  Definitely getting into the nitty gritty stuff here.
Developer
--  PL/SQL Language Reference - http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm
----  That should help you get started with PL/SQL if you so choose

For either role, the bible is probably the SQL Language Reference manual: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm

Usually what I do, is just download the software and try to get it running. It’s super easy on Windows, a bit more difficult on *nix environments (I just recently installed Oracle on Oracle Enterprise Linux, my first go at a non-Windows environment).

The documentation above is for 11g, the latest version. You can get the software here: http://www.oracle.com/technology/software/products/database/index.html I’d suggest installing EE, might as well go full boat. Plus, it doesn’t really make a difference resource wise. If you have 1.5 to 2GB RAM, you should have plenty. You can run an instance at about 512MB.

It requires an account (free) and they do not spam.

Also, try Oracle Database XE, a free (as in beer) standalone edition built off of the 10.2 kernel. Application Express is used as the front end (a pretty sweet GUI tool).

http://www.oracle.com/technology/software/products/database/xe/index.html

Hope that doesn’t scare you off too much. ;)

Any other pointers? I know there are tons of books so link them up if you feel they are worthy.

Labels: database, dba, oracle

  ¶ 7/08/2009 12:56:00 AM   8 Comments Links to this post

  Virtual Box, OEL, 11g, ApEx Finally.

After many months and many attempts, I have managed to install a database on a non-windows platform. I think I deserve a little credit. ;)

In January, I began installing Ubuntu Intrepid Ibex on all of my home computers. What this meant for me is that I was often without one because I screwed something up. I even got so far as to install Oracle XE on Ubuntu which took 2 blog posts.

Just before COLLABORATE 09, I installed Jaunty Jackalope on my laptop. Then I got the crazy idea to install Oracle on Ubuntu. I ended up having to wipe the hard drive and start fresh. I have no doubt that it (Oracle) can be installed on Ubuntu, it just takes someone much more experienced than I.

Armed with a fresh install, I was going the virtual route. Inspired, I believe, by this comment (Aman Sharma).
1.  Install Sun's Oracle's VirtualBox
2.  Download the latest version of Oracle Enterprise Linux (v5)

I think that's the point I realized my first installation of Jaunty was hosed, and by hosed I mean unrecoverable by me. I couldn't see the 2nd partition, the one with all the room.

3.  Create OEL virtual machine
4.  Follow this guide to get it ready to install the Oracle database (seriously? Why does OEL not come ready for the database server?)


4a.    There's no mention of opening up the firewall in that article. It's probably obvious to most...
5.  Connect to Oracle from the host machine
6.  ApEx? Which version am I running? 3.0? That won't do. Download ApEx 3.2
7.  Install ApEx
8.  Configure Embedded PL/SQL Gateway for port 80
8a.    Ports below 1024 are reserved for privileged processes...Configure Embedded PL/SQL Gateway for port 8080
9.  Connect from host machine.


9a.    Login to ApEx.


10.  WIN!

Labels: apex, dba, oel, virtualbox

  ¶ 6/23/2009 10:37:00 PM   6 Comments Links to this post

  Who's Your DBA? I'm a "kind of" DBA, but not the real deal (yet). I do things from the application perspective which all you real DBAs laugh at (stop it).

I like competition, it's good for the soul. I've begun wondering who the best DBA in the world would be. As far as I know, there is no such competition.

A good place to start though would be the Oracle Certified Masters. Based on the curriculum to achieve said credential, I'd say these people would rank among the best right?

What about those that choose not to take it, but are more than worthy? Would it be those that blog? How could I ever know? (No Howard, it doesn't really matter, it's just curiousity).

Anyway, my DBA got mentioned by one of the better known DBAs out there, Tanel Poder. I'd say that's pretty cool.

Miladin Modrakovic is his name, Oraclue is his game. (Corny, I know).

Joking aside, I like (love?) working with this guy. If there is something I don't understand I can pass it along without fear of him not knowing. That hasn't always been my experience. My first "DBA" gig I was the only one there...I knew everything, or had to. It's such a relief to know that I don't have to know everything.

So if you like that real DBA stuff, internals, diagnostics, etc., check out his site. It's well worth the read.

Labels: database, dba, oracle

  ¶ 3/26/2009 12:07:00 AM   6 Comments Links to this post

  Jobs: Senior Oracle DBA (Datawarehousing) Anyone looking to move down to sunny Florida?

Email (chet dot justice at the gmail) or leave a comment and I'll pass along the contact information to you.

Job Responsibility:
...seeking a senior level Oracle Datawarehousing DBA to support Oracle 10g in a datawarehouse environment. You will be one of two DBA's supporting databases in excess of 6 Terabytes. This is a production/ application environment and requires 24/7 support. The DBA will be responsible for all Oracle database administration and programming efforts on the Data Warehouse database system. This includes installing Oracle 10g, Oracle RAC, partitioning, ETL and purge efforts. Should be proficient writing stored procedures/triggers and understand how to tune those stored procedures to be able to run without impacting the production system.

Job Requirement:
- Most important requirement is that you have experience as a Senior Oracle DBA in a UNIX Data warehouse environment and proficient in an Oracle 10g environment.
- Must have the skills to support multiple Unix systems
- Strong Unix shell scripting experience
- Oracle software installation and patch management.
- Must have experience with RAC, Oracle encryption, PL/SQL programming, RMAN and backup and recovery.
- Must have experience with Database cloning, partitioning, Oracle auditing, materialized views and VLDB performance tuning.

Labels: dba, jobs

  ¶ 3/10/2009 04:40:00 PM   0 Comments Links to this post

  Alerts - Scheduler Jobs Did Not Run The last couple of days our warehouse environment hasn't been updating properly. It's basically a collection of Materialized Views based on the OLTP system. It appears as though many (all?) of our scheduler jobs are not running. DBA believes it has something to do with the Flash Recovery Area running out of space. I don't know and I don't have the time to find a root cause. I do know that there were no errors and the jobs were not in a failed status.

Rather than have the business inform us their reports our not right I was tasked with creating an alert if the jobs did not run. Usually we set up alerts for things that break or actually completed. I think this is the first time I've had to build something to the opposite.

Here's what I came up with:

CREATE OR REPLACE
PROCEDURE jobs_did_not_run
AS
  b VARCHAR2(3) := CHR(10);
  TYPE r_table IS RECORD
  (
    job_name VARCHAR2(30),
    next_run_date DATE,
    job_action VARCHAR2(4000)
  );
  TYPE t_table IS TABLE OF R_TABLE;
  l_table T_TABLE := T_TABLE();
  l_subject VARCHAR2(50) := 'Alert - Jobs have not run';
  l_message VARCHAR2(32767);
BEGIN
  SELECT job_name, next_run_date, job_action
  BULK COLLECT INTO l_table
  FROM dba_scheduler_jobs
  WHERE state = 'SCHEDULED'
    AND enabled = 'TRUE'
    AND next_run_date < SYSDATE;
 
  l_message := 'The following jobs have not run today:' || b;
  FOR i IN 1..l_table.COUNT LOOP
    l_message := l_message || 'Job:           ' || 
                   l_table(i).job_name || b;
    l_message := l_message || 'Next Run Date: ' || 
                   TO_CHAR( l_table(i).next_run_date, 'MM/DD/YYYY HH24:MI:SS' ) || b;
    l_message := l_message || 'Action:        ' || 
                   l_table(i).job_action || b;
  END LOOP;

  utl_mail.send
    ( sender => '',
      recipients => '',
      subject => l_subject,
      message => l_message );

END jobs_did_not_run;
/
show errors

You can find it here in my Google Code home for DBA Utilities. I do believe that at some future point this will be incorporated into a package, but for now it is a standalone procedure.

You'll receive a nice little email noting the JOB_NAME, scheduled NEXT_RUN_DATE and the JOB_ACTION (the anonymous block).

Labels: code, dba, open source

  ¶ 3/06/2009 11:57:00 PM   0 Comments Links to this post

  Audit Failed Logon Attempts I've been looking at how to send out an alert to the DBA group on failed login attempts.

In searching through the Database and Client events, the closest thing I could find initially was AFTER LOGON. That didn't make sense because of that little word AFTER. I then realized today that I could use SERVERERROR.

The SERVERERROR event gives you access to the following Attribute Functions:
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info

I created the trigger using ORA_LOGIN_USER but when I received the email, it would be blank.

That makes sense, they're not logged in.

What could I do?

Google it.

Well, nothing really there. Some people discussing the same problem, the inability to retrieve the username attempting to login.

Then I went to Metalink and found Doc ID: 352389.1 (not sure how to link up now with the "new" site). The note talks about enabling auditing (done) and an init.ora parameter, but the username was still illusive.

I don't know the internals, but Oracle needs to know the username or how else would it know is logging in?

Anyway, I settled on the following for the time being.

CREATE OR REPLACE
TRIGGER failed_logon_notifications
  AFTER SERVERERROR ON DATABASE
DECLARE
  b VARCHAR2(3) := UTL_TCP.CRLF;
  l_username VARCHAR2(30);
  l_failed_attempts INTEGER;
  l_subject VARCHAR2(40) := 'Alert - Failed Login';
  l_message VARCHAR2(500);
BEGIN
  IF ora_is_servererror( 01017 ) THEN
    l_message := 'Date/Time:  ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
    l_message := l_message || 'OS User:  ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
    l_message := l_message || 'Host:  ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
    l_message := l_message || 'Terminal:  ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
    l_message := l_message || 'IP Address:  ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
    l_message := l_message || 'Protocol:  ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
    l_message := l_message || 'Database Instance:  ' || ora_instance_num || b;
    l_message := l_message || 'Database Name:      ' || ora_database_name || b;

    BEGIN
      utl_mail.send
        ( sender => ora_database_name || '@revolutionmoney.com',
          recipients => 'dbas@email.com',
          subject => l_subject,
          message => l_message );
    EXCEPTION
      WHEN others THEN
        RAISE;
    END;
  END IF;
END failed_logon_notifications;
/

Now I didn't really want this to fire on every single event, but I had problems with the WHEN clause.

So if you know how to 1, capture the username or 2, apply the WHEN clause to only fire on ORA-01017, please comment.

Labels: database, dba, oracle, security

  ¶ 2/09/2009 09:35:00 PM   3 Comments Links to this post

  Job: Oracle DBA in Tampa Here's another one. If interested, leave a comment (I won't publish) or email me, chet dot justice at the google mail domain (does this spelling out really work?).

DESCRIPTION:

The Database Analyst provides database administration support for production, development, and test environments to include day-to-day monitoring and maintenance, problem investigation and resolution, backup creation and maintenance, SQL and database optimization and tuning as well as researching, analyzing, and recommending patches, upgrades, and new technologies surrounding all aspects of data management using relational databases.

RESPONSIBILITIES:

* Provide support for all Oracle database environments to include problem identification, reporting, tracking, analysis, and resolution.

* Research and analyze all assigned anomalies - record, track and log all findings and recommendations.

* Participate in production on-call rotation (one week at a time of 7x24 on-call support for production environments).

* Monitor database performance and space utilization – investigate and resolve reported issues.

* Establish, maintain, and monitor database backups and DRP sites.

* Establish, maintain, and monitor database security, roles, user-role assignments, and individual logins per company security policies and procedures.

* Establish and maintain data replication as required for each application.

* Assist project teams in setting up and testing development, test, and production environments.

* Research, analyze, and recommend patches, tools, and/or database upgrades to resolve issues and add features/functionality.

* Architect, design, and implement replication and/or data synchronization.

* Assist project teams in designing efficient data access methods and procedures, including data structures (physical data modeling), SQL techniques, and use of database tools/utilities.

* Provide technical leadership to the DBA team by assisting junior team members and sharing knowledge and research of tools/techniques, and Oracle features.

* Serve as team liaison to project managers for database administration and system administration services.

REQUIREMENTS:

* 7 years Oracle database administration experience to include versions 9I and 10G.

* 7 years SQL experience with expert-level skills in the SQL language.

* 3 years near-real time replication experience - any tool (streams, golden gate, etc).

* 1 year RAC or ASM experience.

* RMAN/Legato backup experience.

* 4 year BS or BA degree required in a computer related field or 6 years of directly related work experience in a large-scale IT environment.

PREFERRED EXPERIENCE:

* PL/SQL development experience.

* Oracle advanced replication, streams, or Golden Gate replication.

* Oracle Application Server (not Oracle Applications 11i).

* Experience in Oracle RAC (10G).

* Experience in Oracle Advanced Security & Data Encryption.

* Transparent application failover.

Labels: dba, jobs, oracle

  ¶ 9/26/2008 11:34:00 AM   0 Comments Links to this post

  Love Your DBA I consider myself a Developer/DBA.

That said, you've probably either read about or experienced the typical riff between the developer and the DBA.

At my current employer, I am finally surrounded by true Production DBAs. Initially, I found it difficult to work them. When I would ask "Why can't I do that?" I would rarely get a response.

Over time though, things have changed...for the better.

I believe it's called trust.

Trust that I am trying to do the right thing.
Trust that I want to learn.
Trust that I will listen to their suggestions.
Trust that I won't hack their DEV/QA instances using CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE.
Trust that I want to build a scalable and robust application.

So love your DBA. Give them time to get to know you. Give them time to learn your style, your methodology. Maybe someday they'll love you back and your job will get infinitely easier.

Labels: database, dba, development, work

  ¶ 1/31/2008 12:15:00 AM   7 Comments Links to this post