Square DBA – Oracle DBA Talk

Finding large files in Linux

It’s easy to track down your Oracle datafiles.

select BYTES, NAME from v$datafile;

Will tell the size of each datafile and it’s location on the server. If your server is like mine you will large non-Oracle files on the server as well. From time to time I need to find these large files to see if they can either be compressed, backed up and removed, or deleted all together. Here is a handy little statement I use on my Redhat servers.

find {/path/to/directory/} -type f -size +{size-in-kb}k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

so when I am looking for 200+ MB files on a specific mount I use

find /old_array -type f -size +200000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }' 

or if I want to find files over 500MB in the current directory I will use

find . -type f -size +500000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }' 

ORA-01502: index or partition of such index is in unusable state

Do you ever get and ORA-01502? This error code says your index is in an unusable state. It’s easy to find these indexes.

select index_name, status, owner from
dba_indexes where STATUS = 'UNUSABLE';

If you want to build the rebuild SQL that is easy too.

select 'alter index '||owner||'.'||index_name||' rebuild;' from
dba_indexes where STATUS = 'UNUSABLE'; 

Finding currently running SQL

Have you needed to find out what SQL was running in the database? Much of my time is spent on out data warehouse where long expensive queries may be running. When someone calls to ask why things are running slow one area to look is what SQL are they running. The database may not be running slow, but their SQL is.

When Oracle executes a query, it places it in memory. This allows Oracle to reuse the same SQL if needed by the executing session at a later date or by another user that may need the same SQL statement. Oracle assigns a unique SQL_HASH_VALUE and SQL_ADDRESS to each SQL statement. By Oracle doing this, it provides us a method to determine who is executing what SQL based on the join columns from the V$SESSION of SQL_ADDRESS & SQL_HASH_VALUE to the V$SQLAREA view and columns ADDRESS and HASH_VALUE.

Here is a small script to determine what SQL is currently executing.

elect sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null 

Often you may have an active session and actually show a valid SQL statement through the V$SESSION and V$SQLAREA views that seems to be taking very long. Users may be complaining that their query is “stuck” or not responsive. You as a DBA can validate that the SQL they are executing is actually doing something in the database and not “stuck” be simply querying the V$SESS_IO view to determine if the query is in fact “stuck” or is actually doing work within the database. Granted, this does not mean there isn’t a tuning opportunity but you can at least show the SQL is working.

Here you can see the I/O being done by an active SQL statement.

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null 

If by chance the query shown earlier in the V$SQLAREA view did not show your full SQL text because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. It is a piece by piece of 64 characters by line, that needs to be ordered by the column PIECE.

Here is the SQL to show the full SQL executing for active sessions.

select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece 

If you really just want to see what sessions are active this SQL will help.

elect sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
       username,
       type,
       status,
       process,
       sql_address,
       sql_hash_value
  from v$session
where username is not null 

Killing Oracle Sessions

Do you need to kill an Oracle session? There are several way to do this. In SQLPlus simply run:

ALTER SYSTEM KILL SESSION 'sid,serial#';

You can also run:

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

You can also kill sessions from the UNIX level with this command:

kill -9 spid

This is dangerous and should not normally be done. Pick the wrong OS process and you could crash your instance. There may also be time you need to bulk kill Oracle sessions. Simply selecting again v$session will get you the data needed.

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||'''
IMMEDIATE;' from v$session where username = 'DWUSER' and
STATUS = 'ACTIVE';

Then just run the output in SQLPlus. Again be sure you are only killing the sessions you want. Killing the wrong session could terminate your database instance.

How To Repair UNDO log corruption

Today I had a corrupted segment in my UNDO tablespace. While I have had datafile corruption before I have not seen UNDO segment corruption. This fix could have been a more drastic, but as it was it ended up being pretty easy.

I first found out there was a problem when one of my databases emailed me that “ORA-01034: ORACLE not available , ORA-27101: shared memory realm does not exist”. Well that is never good. I logged into the server and sure enough the database was down. I logged into SQLPlus and started the database backup. Everything start fine so I went to look for the cause. The first place I went look is the alert log. As I expected I saw errors like this one.

ORA-00600: internal error code, arguments: [kdourp_inorder2], [44], [0], [48], [44], [], [], []

No DBA ever likes to see ORA-00600. It’s too generic tell exactly what is going on and generally means something simple broke or something is terribly wrong. Now that the database was backup I started to tail the alert log and noticed more ORA 600 errors, then followed by ORA-00474: SMON process terminated with error and PMON: terminating instance due to error 474. Ouch database crashed again. It was time to really dig into the issue.

Continuing to review the alert log I went back to the earliest point where the ORA-00600 errors started and found these errors scattered in the alert log.

ORA-00600: internal error code, arguments: [kdourp_inorder2],
[44], [0], [48], [44], [], [], []
ORA-08007: Further changes to this block by this transaction
not allowed
Doing block recovery for file 21 block 456408
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial

It looked like I had corruption somewhere. So using this SQL I was able to determine where my issue was:

select
   segment_name,
   status
from
   dba_rollback_segs
where
   tablespace_name='undotbs_corrupt'
and
   status = ‘NEEDS RECOVERY’;

My UNDO table space has the corruption. Because I was able to open my database things were looking pretty good. Is I had pending transaction in my UNDO table space things would have been a bit more tricky. My action pla was to get a second UNDO table space up as quickly as possible and take offline the current UNDO tablespace. This will stop the database from crashing.

I created the tablespace with this SQL

create undo tablespace undotbs2 datafile
‘/u02/oracle/oradata/test/undotbs2.dbf’ size 500m;

Alter the database to use the new UNDO tablespace.

alter system set undo_tablespace=undotbs2 scope=both;

Then take the original UNDO tablespace offline

alter tablespace undotbs1 offline; 

Next drop the corrupted segment.

drop rollback segment "_SYSSMU22$";

Then finally drop the old UNDO tablespace

alter tablespace undotbs1 offline;

Update data in one table with data from another table

Have you ever need to update data in one table with data from another table? I do all the time. One way I perform the update is with functions that I built. I pass a column into the function and it returns a result that use in my update statement. This works well if I am not updating a large numbers rows. If I am updating a large number of rows this can be pretty slow. So instead I run a statement like this:

UPDATE o1dw.stg_acme a
SET (switch_id) = (SELECT switch_id from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC')
WHERE EXISTS (SELECT 1 from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC') 

This method is much more efficient. This update takes about 12 minutes on 3.5 million rows.

Upgrade Oracle to 11.1.0.7

My only 11G instance was running 11.1.0.6 and needed to be upgrade it to 11.1.0.7 to fix a couple of bugs. Apparently this patchkit also includes some new functionality. Before starting the upgrade I needed to do a bit of housekeeping. This instance is relatively small so I performed a full database backup as well as a full database export. I also created a pfile for good measure.

create pfile from spfile ;

I like to have a list of invalid objects to I can compare the before and after.

select object_name, owner, object_type
from all_objects where status like ‘INVALID’; 

Next I checked the prerequisites for this upgrade. The time_zone check is a very important check that needs to be made.

select version from v$timezone_file;

I am running version 4 so I am okay to upgrade. Check out MetaLink Note 568125.1 if you are running anything other than version 4. Next I ran the script utlu111i.sql in the $OH/rdbms/admin directory. It is designed to let you know if there are issues to address prior to the upgrade. Next I shutdown my Oracle instance and performed the upgrade through the GUI installer. All went well. Then it was time to upgrade my instance.

sqlplus /nolog
startup upgrade
 spool upgrade.log
@?/rdbms/admin/catupgrd.sql
spool off

This process took about 90 minutes to run so plan enough time for your upgrade. The script was run without errors. The final step of the script shutdown the database. I started the database normally, then shut it down normally, then started it up again normally. I generally take these extra steps just to ensure everything looks okay. To check the post-upgrade status this script is helpful

@?/rdbms/admin/utlu111s.sql

Next I needed to fix some invalid object which I did with this script.

@?/rdbms/admin/utlrp.sql

The final step to the upgrade process is to take a full database backup. Now I am fully upgraded to the latest version of Oracle. To check my version I ran.

select * from v$version

And then

select comp_name,status,version from dba_registry;

ORA-12518 TNS listener could not hand off client connection

Have you ever seen the error “ORA-12518 TNS listener could not hand off client connection”? I did yesterday and it was a pretty simple fix.

It turns out the processes for a particular instance was set too low. You can see how many processes are running via this command:

select count(*) from v$process;

If you want to see what your threshold is set to run:

show parameter processes;

To change the number of processe run this command:

ALTER SYSTEM SET processes=250 SCOPE=spfile;

Unfortunately this requires you to restart the database for the new value takes affect. To see the new value in the spfile you can run this command:

SELECT value FROM v$spparameter WHERE name='processes';

Disable Oracle’s password expiry

In 11G Oracle started setting the password expiry by default. This was not the case for earlier versions of Oracle. I understand that additional security benefit, but this is annoying for application logins. I have disabled this for the default profile. Here is how.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ORA-06564 When Creating a Materialized View

Today I needed to create a materialized view and received an error. My syntax was correct. Here is the create statement.

CREATE MATERIALIZED VIEW SCOTT.STUPID_TABLE
BUILD IMMEDIATE
REFRESH FORCE
START WITH SYSDATE
NEXT trunc(sysdate+1) + 22/24
AS
select * from BILL.STUPID_TABLE@remote1;

and I received this error.

ERROR at line 1:
ORA-06564: object EXTDIR does not exist
ORA-02063: preceding line from remote1

This error was generated because I did not have read/write privileges on the directory EXTDIR. Yes STUPID_TABLE is an external table. To correct the problem simply grant the correct permissions.

GRANT READ ON DIRECTORY EXTDIR to MYJOBUSER;
GRANT WRITE ON DIRECTORY EXTDIR to MYJOBUSER;