As usual, for all government projects September is a pretty busy month (end of fiscal year), but nice thing about September - it always ends (plus/minus a week) with Oracle OpenWorld.
Even a lot of people complain that this conference became more and more marketing-oriented, for me it is a place to meet a lot of my old friends by Oracle community, get the "temperature" of the industry, talk to new people (there is always a ton of new faces from all over the world!). I even try not to submit abstracts for the next cycle of conferences (Collab, KScope) until I return from the OpenWorld - some of topics could just come up as a part of late-night discussion somewhere in the bar after N-th glass of beer. The key point - such topics will be 100% hit! Because if you remember something at that point - it usually means that it is really high in your priority queue :-) Anyways, for the next couple days I will try to post about my experiences here (sorry, not in the minute-by-minute structure!).
Day 1. Sunday, Oct 2.
Sunday is always a conference-in-the-conference, because this is the day when different user groups have a chance to organize its own symposiums. This year I participated in ODTUG Web Architecture Forum, playing a role of the person from the "other side of the wall" - my story was told from the point of view of a good DBA/Senior Database Developer that actively participates in the development of Web applications.
Of course, it was all about "thick database" approach, my favorite concept of pushing as much business logic into the database - and of course, a lot of people in the audience were not 100% comfortable with such extremes. But at least it made them thinking - which is always a good thing for you as a speaker. After my talk I've had about an hour-long conversation with a couple of developers from my native Ukraine - and I was really glad that their way of system development is reasonably close to my understanding, which means that my ideas are valid "in general", not only for the North America. :-) I would hate to sound "provincial" :-)
Another big part of the Forum was a talk of my guru Paul Dorsey about ways of building new Web-based system. The talk was very entertaining - but the summary was very grim... There is no coherency in the industry whatsoever! Take 10 system - get 12 architectures (not even 10!, sometimes architectures start to mutate inside of the life-cycle!). H-m-m... I more and more like my "thick database" approach!
But the evening ended up on the good note - a lot of thanks to Lilian Buziak for setting up an Oracle Ace Dinner (for me it was the very first one, since I got my Ace status only this summer). That was a perfect spot to talk with the "old-timers" (including my esteemed co-authors of "Expert PL/SQL Practices"). And food was great too :-)
Monday, October 3, 2011
Tuesday, August 9, 2011
I am a real DBA now!
Once I've been told is that to be a real DBA you need to do at least one restore a production system from RMAN backup...I think, I came close to that :-) - yesterday night I was able to create a duplicate instance from our production Level-0 (around 200 GB of data restored from tape over the weekend). Although, it was a very long night!
For all my sins (and because our real DBA is on vacations) this task landed on my laps last week - and since then I started to truly appreciate the work of honest-to-god administrators, non semi-architects/semi-developers like me.
OK, I will stop ranting... A couple of useful remarks:
- Arup/Sam/Darl, I owe you at least one round of good drinks! Your book "RMAN Recipes for Oracle Database 11G" helped me a lot!
- As usual, around midnight I've hit an brick wall. RMAN was complaining about a space "somewhere", I've spent another hour trying to figure what it was about. And eventually I discovered the solution in the bug-list (#8631293):
Description
* RMAN Duplicate command signals RMAN-06561 (available space must be larger than NNN kb) when the recovery area parameter is set.
Workaround
* The workaround is not to set db_recovery_file_dest and db_recovery_file_dest_size parameters in the init.ora
For all my sins (and because our real DBA is on vacations) this task landed on my laps last week - and since then I started to truly appreciate the work of honest-to-god administrators, non semi-architects/semi-developers like me.
OK, I will stop ranting... A couple of useful remarks:
- Arup/Sam/Darl, I owe you at least one round of good drinks! Your book "RMAN Recipes for Oracle Database 11G" helped me a lot!
- As usual, around midnight I've hit an brick wall. RMAN was complaining about a space "somewhere", I've spent another hour trying to figure what it was about. And eventually I discovered the solution in the bug-list (#8631293):
Description
* RMAN Duplicate command signals RMAN-06561 (available space must be larger than NNN kb) when the recovery area parameter is set.
Workaround
* The workaround is not to set db_recovery_file_dest and db_recovery_file_dest_size parameters in the init.ora
Monday, August 1, 2011
Expert PL/SQL Practices on sale!
My (to be fair - our) new book is waiting for everybody! Sales started today - here is a link from Amazon:
"Expert PL/SQL Practices"
It was a very interesting project to participate - and huge thanks to Jonathan Gennick from Apress who was brave enough to take the challenge of herding together 15 different authors. To our own surprise the book came together even faster than anybody anticipated - by at least a month. Looks like PL/SQL authors are more disciplined comparing to everybody else :-)
Good reading to everyone!
"Expert PL/SQL Practices"
It was a very interesting project to participate - and huge thanks to Jonathan Gennick from Apress who was brave enough to take the challenge of herding together 15 different authors. To our own surprise the book came together even faster than anybody anticipated - by at least a month. Looks like PL/SQL authors are more disciplined comparing to everybody else :-)
Good reading to everyone!
Tuesday, July 12, 2011
I am an Oracle ACE now!
Some good news: I've been confirmed as Oracle ACE! Here is my profile published on the Oracle's main web-site: click to check yourself...
That was a long way to go, my friends... It started in the IT department of the factory where my dad was working - for the first time I've heard about a miraculous platform that would contain all the information about the whole factory. Later "the way" continued in the university. I was shocked by the depth of what it really meant to built contemporary IT systems, but with the help of my professors I learned how to swim in that sea of data. By the way, I still own my first Oracle-related book - it was Russian Edition of Paul Dorsey's Oracle Designer Handbook. Who would ever guess that couple year later Paul would become my (a) boss (b) mentor (c) friend for life! It is now more than 10 years of me working in Dulcian.... Time is flying!
Anyways, thanks everybody for shaping me as a professional - my teachers, my colleges, my listener, my friends! I hope, in this new status I could contribute even more to the Oracle community - one day at a time, one line of code at a time. Will keep you posted!
Tuesday, June 21, 2011
CLOB-related DataPump issue
Yesterday while building a demo-instance for the upcoming KScope'11 I've hit a strange Oracle bug. Here is the story:
- a single schema is transferred from 10.2.0.5 to 11.2.0.1 (both Windows 32 bit) via datapump
- importing of the data fails with fatal error (in the middle of data import)
- I took export log, found exact table it fails to import (by looking at the last successful load), and tried to import just one table. It also failed with the same fatal error:
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 10:38:53
- in the alert.log I found a bit more details
ORA-07445: exception encountered: core dump [kcblnb_dscn()+38] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0xDD94FE] [UNABLE_TO_READ] []
Metalink pretty quickly gave me an answer: Doc ID 1313537.1:
- Using datapump to import a table that has mixed CACHE/NOCHACHE LOB columns results in an ORA-7445.
- Eventually there will be patch (11.2.0.3), but for now the workaround is to use regular EXP/IMP
I tested the workaround option - it does its job. So, beware!
P.s. Since I am talking for years about manual adjusting CLOB storage parameters, I decided that it is my duty to create this post! As usual, advanced features are causing side-effects...
P.p.s. Forgot to mention - just FYI about old EXP/IMP module: you cannot export data from 10g database using 11g client utilities. The export must be done using native 10g utilities - otherwise a pretty strange error is thrown:
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
- a single schema is transferred from 10.2.0.5 to 11.2.0.1 (both Windows 32 bit) via datapump
- importing of the data fails with fatal error (in the middle of data import)
- I took export log, found exact table it fails to import (by looking at the last successful load), and tried to import just one table. It also failed with the same fatal error:
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 10:38:53
- in the alert.log I found a bit more details
ORA-07445: exception encountered: core dump [kcblnb_dscn()+38] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0xDD94FE] [UNABLE_TO_READ] []
Metalink pretty quickly gave me an answer: Doc ID 1313537.1:
- Using datapump to import a table that has mixed CACHE/NOCHACHE LOB columns results in an ORA-7445.
- Eventually there will be patch (11.2.0.3), but for now the workaround is to use regular EXP/IMP
I tested the workaround option - it does its job. So, beware!
P.s. Since I am talking for years about manual adjusting CLOB storage parameters, I decided that it is my duty to create this post! As usual, advanced features are causing side-effects...
P.p.s. Forgot to mention - just FYI about old EXP/IMP module: you cannot export data from 10g database using 11g client utilities. The export must be done using native 10g utilities - otherwise a pretty strange error is thrown:
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
Wednesday, June 15, 2011
LONG to CLOB
Yes, I know that LONG datatype was considered "dead" already for Oracle 8i. But still I have cases when people are asking about the conversion to CLOB. Finally, I decided to build a "memo" on this topic.
Small remark: in this blog post I explicitly ignore 11g SecureFile LOB implementation, since the chances of people still using LONG datatypes to step up to the most recent feature are not really high.
--------------------
From the database point of view it is very simple to change LONG to CLOB datatype, since ALTER TABLE..MODIFY supports such change explicitly. But since CLOB columns have many more parameters to be configured, the process of conversion provides a good opportunity to make appropriate setups.
The most critical
*Tablespace - CLOBs could use a different tablespace comparing to the table itself, which could be useful for the purposes of data storage management.
* Retention - configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the tablespace (PCTVERSION, which is used by default for columns converted from LONG). The RETENTION parameter is designed for be used with Undo features of the database, such as Flashback Versions Query, and recommended for all DB configurations using Automatic Undo Management.
* Caching – defines whether DML operations against LOB columns would use BUFFER CACHE or Direct IO operations. There are 3 possible settings of this parameter – CACHE / CACHE READS / NOCACHE (default). Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.
Overall, the migration will take a form of running the following statements to each LONG column (as you can see, column names is not changed – only its datatype and extra storage options)
alter table <TableName> modify <ColumnName> CLOB
lob(<ColumnName>) store as
(cache
tablespace <TablespaceName>
retention
)
From the application point of view the following issues/remarks are detected:
* There are no known areas where LONG datatype is doing anything better (performance/functionality) than CLOB
* There is no easy technique to modify CLOB column back to LONG without using some kind of staging solution.
* PL/SQL supports explicit casting of CLOB columns to LONG variables as long as the length of retrieved data meets LONG restrictions. The next example should read/change/write:
declare
v_long long;
begin
select clob_column
into v_long
from t_table;
v_long:= v_long||'ABCDEFG';
update t_table
set clob_column = v_long
where pk_id = 1;
end;
It is recommended to do a full code review and remove variables of type LONG, especially in performance-critical modules (because DBMS_LOB APIs are much more efficient than, for example, concatenation)
Small remark: in this blog post I explicitly ignore 11g SecureFile LOB implementation, since the chances of people still using LONG datatypes to step up to the most recent feature are not really high.
--------------------
From the database point of view it is very simple to change LONG to CLOB datatype, since ALTER TABLE..MODIFY supports such change explicitly. But since CLOB columns have many more parameters to be configured, the process of conversion provides a good opportunity to make appropriate setups.
The most critical
*Tablespace - CLOBs could use a different tablespace comparing to the table itself, which could be useful for the purposes of data storage management.
* Retention - configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the tablespace (PCTVERSION, which is used by default for columns converted from LONG). The RETENTION parameter is designed for be used with Undo features of the database, such as Flashback Versions Query, and recommended for all DB configurations using Automatic Undo Management.
* Caching – defines whether DML operations against LOB columns would use BUFFER CACHE or Direct IO operations. There are 3 possible settings of this parameter – CACHE / CACHE READS / NOCACHE (default). Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.
Overall, the migration will take a form of running the following statements to each LONG column (as you can see, column names is not changed – only its datatype and extra storage options)
alter table <TableName> modify <ColumnName> CLOB
lob(<ColumnName>) store as
(cache
tablespace <TablespaceName>
retention
)
From the application point of view the following issues/remarks are detected:
* There are no known areas where LONG datatype is doing anything better (performance/functionality) than CLOB
* There is no easy technique to modify CLOB column back to LONG without using some kind of staging solution.
* PL/SQL supports explicit casting of CLOB columns to LONG variables as long as the length of retrieved data meets LONG restrictions. The next example should read/change/write:
declare
v_long long;
begin
select clob_column
into v_long
from t_table;
v_long:= v_long||'ABCDEFG';
update t_table
set clob_column = v_long
where pk_id = 1;
end;
It is recommended to do a full code review and remove variables of type LONG, especially in performance-critical modules (because DBMS_LOB APIs are much more efficient than, for example, concatenation)
--------------------
Hope, it helps!
Monday, June 6, 2011
Oracle OpenWorld 2011 => Oracle Mix
As many of you have noticed, at the end of the last week Oracle sent around accept/reject letters for OpenWorld 2011. Mixed feelings...From one side, I already got in with one of "performance" talks that we often do together with Dr. Paul Dorsey (he covers "why"/I cover "how"). From the other side, I didn't get through the abstract I really hoped.
But this year there is an alternative,“Oracle Mix”, where people could re-submit their abstracts to the “popular vote”. What surprises me is that majority of MIX topics are either about development or about core DBA’s issues (manageability/ scalability/ availability). Middleware/SOA/other products are well-behind. Two possible conclusions – either DBA/Dev specialists are more active in sharing of their ideas or the balance of presentations in OpenWorld is slightly off. Opinions?
Anyways, if anybody would like to vote for my additional abstract via Oracle Mix, here is a link.
It is the same talk I will be doing at KScope’11, “Managing unstructured Data: Lobs, BasicFiles, SecureFiles” (Tue June 28, 3 p.m.). Please, increase my chances of increasing the real development content at OpenWorld 2011.
P.s. A bit more of advertising - Yalim's architectural talk on how to appropriately use PL/SQL in XXI century is strongly suggested to everybody:
Subscribe to:
Posts (Atom)