Archive for August, 2008
Preparing for Openworld
Posted by Jay Caviness in general on August 30th, 2008
Well just a few weeks to Openworld, I am excited and a little nervous to be presenting this year. Those that know me well know that I can talk all day long about Oracle, but when it comes to Powerpoint, well, perhaps the following Dilbert cartoon says it all.
TOD – The Ties that Bind (Variables)
Posted by Jay Caviness in Tip 'o the day, general on August 25th, 2008
Tuning SQL is not exactly my favorite thing, and I have seen times when a SQL query runs fine with hard coded values, but poorly with bind variables in the application. There is a simple way to test bind variables in a SQL statement if needed.
Declare the variable, the bind it to a value using PL/SQL and insert into your script:
variable a1 varchar2(10); SQL> variable a1 varchar2(10); SQL> variable b1 varchar2(10); SQL> variable c1 number; SQL> SQL> exec :a1 := 'Jay' PL/SQL procedure successfully completed. SQL> exec :b1 := 'DBA' PL/SQL procedure successfully completed. SQL> exec :c1 := 21 PL/SQL procedure successfully completed. SQL> select last_name, DOB, favorite_marshmellow 2 from emp 3 where 4 first_name = :a1 and 5 job_title = :b1 and 6 age > :c1; /
ORA-600 [12235]
Posted by Jay Caviness in general on August 15th, 2008
This is an innocuous error that will occur and be dumped into the alert log of whichever SID is currently set. What it means is that someone typed “oracle” at the unix prompt. Since there is an oracle executable in the path ($ORACLE_HOME/bin) it attempts to execute, is caught by the running database and turned into an error:
Errors in file /oracle/product/admin/comm/log/ora_10718_comm.trc: ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
I think the error (warning, really) could be a bit more descriptive.
So, if you want to scare the pants off the new junior DBA on their first time on call, just type in “oracle” a bunch of times, and wait for their pager to ring!
TOD – Recycling the Recycle Bin
Posted by Jay Caviness in general on August 11th, 2008
The recycle bin was quietly introduced in Oracle 10g as a method to have flashback drop functionality. It comes in handy, but generally not in production databases, most of the use is in development. By default the recycle bin is operating, this can be a problem when you go to list, export or drop all the tables in a schema because there may be a whole bunch with BIN$blahblahblah in there. There are the objects that have been added to the recycle bin. The object dropped still takes up its original space, it is recreated it takes new extents. If this happens over and over again, you could potentially run out of space and not know why unless you check the contents of the recycle bin with:
SQL> select * from user_recyclebin;
If you wish to purge all of the objects you can use the command
SQL> purge recyclebin;
This command will purge all the objects out of the current user’s recycle bin. If you want to purge all objects out of the recycle bin, login as a dba user and execute:
SQL> purge dba_recyclebin;
To shut off the recycle bin depends on the version:
10gR1 - SQL> alter system set "_recyclebin"=false scope=both; 10gR2 & 11g - SQL> alter system set recyclebin = off;
For more information see Metalink note 264253.1.
A Little Friday Oracle Humor
Posted by Jay Caviness in general on August 8th, 2008
Q – What is the mating call of a DBA?
A – “Disk….Disk….Disk”
“The degree of normality in a database is inversely proportional to that of its DBA”.
21st law of databases
- anything that can go wr
ORA-600 [segmentation fault] [0] [0] [0] [0]
“Real DBAs don’t comment their scripts, it was hard to write it should be hard to understand!”
Happy Friday!
Screening and Interview Questions for DBAs
Posted by Jay Caviness in general on August 7th, 2008
I mentioned in a previous post (Are You Certifiable?) that when I interview DBA’s I want to see if they know why things work, not just how they work. I want to see an underlying systemic understanding of not only the database but how other parts of a system affect and are affected by the database. However, to get to that point there is typically a pre-screening phone interview to weed out applicants, and I do need to know core knowledge. Depending on the level of DBA I am looking for come a few questions, I will ask some of the lower level questions to senior level people because I have seen many-a-DBA lock up on remembering the parts of the SGA.
- What is the SGA? What are its components?
- Name several methods to back up an Oracle database. What are their advantages/disadvantages?
- What are the four states a database instance can be in?
- What are undo segments for?
- What is the temporary tablespace?
- What is the cost based optimizer? What are statistics, histograms, etc?
- What happens during a crash recovery?
- How do you recover from a lost: datafile, tablespace, undo tablespace, control file?
- Is a full table scan ever better than an index scan? If so, how and why?
- What is an ORA-1555, how can it be avoided?
- What is your philosophy on datafile placement on SAN, NAS or local storage?
- When is it appropriate to set tracing on SQL*Net?
- When is RAC/Dataguard/streams appropriate to use for high availability?
- List methods to prevent, detect and repair corruption in a database
- Finally, my database is slow, ask me five questions to determine a cause.
This is a starting point. You may have noticed that the questions get more open ended as the level increases. Generally the more complex a problem the more solutions can present themselves, and I would want to hear several ideas. Later I will focus this list for RAC/ASM specifically. Feel free to post your own screening/interview questions, I good base of questions will help both interviews and candidates. Cheers!
TOD – What a character(set)!
Posted by Jay Caviness in Tip 'o the day, general on August 5th, 2008
Is it UTF8, us7ascii, western European? I have always wished that I should just do a “show parameter” to get the character set, but alas, ’tis not so. Here is a Q&D script to find what characterset you are using:
col parameter for a30
col value for a20
select * from nls_database_parameters
where parameter in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET')
order by parameter;
PARAMETER VALUE
------------------------------ --------------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16What’s up doc? Databases up at a glance
Posted by Jay Caviness in general on August 1st, 2008
If you are like me, you are more often logged into a host working on a database than on an OEM or Grid Control page. One of the most useful scripts I have is called sids.sh which at a glance shows ASM, instances and listeners up on the current host. I like to add the script to the end of .profile, .bashrc, etc., to run when I first log in to a host.
################# ASM Instances #####***##############
### +ASM1 came up Jun20 ###
### There are 1 instances up ###
######################################################
################# Oracle Instances ###################
### dgc101t1 came up Jun20 ###
### dgr101t1 came up Jun20 ###
### ct10d2 came up Jun20 ###
### dgc1011 came up Jun20 ###
### fdb32r2 came up Jun20 ###
### dgr1011 came up Jun23 ###
### dgr1012 came up Jul07 ###
### dgc1012 came up Jul07 ###
### mmlive came up Jul31 ###
### mmtest came up Jul31 ###
### mmhist came up Jul31 ###
### mmtrain came up Jul31 ###
### There are 12 instances up ###
######################################################
#################### LISTENERS #######################
### LISTENER_ALERO started Jun20 ###
### There are 1 listeners up ###
######################################################If you would like a copy of this script click sids.sh to bring up the source.