Pro Oracle Database 11g Administration (103 page)

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

your database within the last four days, this view will only have information in it from the time you last started your database.

Another way to get advice on the undo tablespace sizing is to use the Oracle Undo Advisor, which you can invoke by querying the PL/SQL DBMS_UNDO_ADV package from a SELECT statement. The following query displays the current undo size and the recommended size for an undo retention setting of 900

seconds:

select

sum(bytes)/1024/1024 cur_mb_size

,dbms_undo_adv.required_undo_size(900) req_mb_size

from dba_data_files

where tablespace_name =

(select

value

from v$parameter

where name = 'undo tablespace');

Here is some sample output:

CUR_MB_SIZE REQ_MB_SIZE

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

36864 20897

The output shows that the undo tablespace currently has 36.8 gigabytes allocated to it. In the prior query, you used 900 seconds as the amount of time to retain information in the undo tablespace. To retain undo information for 900 seconds, the Oracle Undo Advisor estimates that the undo tablespace should be 20.8 gigabytes. For this example, the undo tablespace is sized adequately. If it were not sized adequately, you would either have to add space to an existing datafile or add a datafile to the undo tablespace.

Here’s a slightly more complex example of using the Oracle Undo Advisor to find the required size of the undo tablespace. This example uses PL/SQL to display information about potential issues and recommendations to fix the problem:

SET SERVEROUT ON SIZE 1000000

DECLARE

pro VARCHAR2(200);

rec VARCHAR2(200);

rtn VARCHAR2(200);

ret NUMBER;

utb NUMBER;

retval NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE(DBMS_UNDO_ADV.UNDO_ADVISOR(1));

DBMS_OUTPUT.PUT_LINE('Required Undo Size (megabytes): ' || DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE

(900));

retval := DBMS_UNDO_ADV.UNDO_HEALTH(pro, rec, rtn, ret, utb);

DBMS_OUTPUT.PUT_LINE('Problem: ' || pro);

DBMS_OUTPUT.PUT_LINE('Advice: ' || rec);

DBMS_OUTPUT.PUT_LINE('Rational: ' || rtn);

DBMS_OUTPUT.PUT_LINE('Retention: ' || TO_CHAR(ret));

DBMS_OUTPUT.PUT_LINE('UTBSize: ' || TO_CHAR(utb));

END;

/

If no issues are found, a 0 will be returned for the retention size. Here is some sample output: 621

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

Finding 1:The undo tablespace is OK.

Required Undo Size (megabytes): 20897

Problem: No problem found

Advice:

Rational:

Retention: 0

UTBSize: 0

Viewing SQL that is Consuming Undo Space

Sometimes a piece of code does not commit properly, which results in large amounts of space being allocated in the undo tablespace and never being released. Sooner or later you'll get the ORA-30036 error indicating that the tablespace can't extend. Usually the first time a space related error is thrown, I simply increase the size of one of the datafiles associated with the undo tablespace.

However, if a SQL statement continues to run and fills up the newly added space, then the issue is probably with a poorly written application. For example, a developer might not have appropriate commit statements in the code.

In these situations it's helpful to identify which users are consuming space in the undo tablespace.

Run this query to report on basic information regarding space allocated on a per user basis: select

s.sid

,s.serial#

,s.osuser

,s.logon_time

,s.status

,s.machine

,t.used_ublk

,t.used_ublk*16384/1024/1024 undo_usage_mb

from v$session s

,v$transaction t

where t.addr = s.taddr;

If you want to view the SQL statement associated with the user consuming undo space, then join to V$SQL as shown:

select

s.sid

,s.serial#

,s.osuser

,s.logon_time

,s.status

,s.machine

,t.used_ublk

,t.used_ublk*16384/1024/1024 undo_usage_mb

,q.sql_text

from v$session s

,v$transaction t

,v$sql q

where t.addr = s.taddr

and s.sql_id = q.sql_id;

If you need more information, such as the name and status of the rollback segment, run a query that joins to the V$ROLLNAME and V$ROLLSTAT views, like so:

622

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

select

s.sid

,s.serial#

,s.username

,s.program

,r.name undo_name

,rs.status

,rs.rssize/1024/1024 redo_size_mb

,rs.extents

from v$session s

,v$transaction t

,v$rollname r

,v$rollstat rs

where s.taddr = t.addr

and t.xidusn = r.usn

and r.usn = rs.usn;

The prior queries allow you to pinpoint which users are responsible for space allocated within the undo tablespace. This can be especially useful when there is code that is not committing at appropriate times and is excessively consuming undo space.

Handling Temporary Tablespace Issues

Issues with temporary tablespaces are somewhat easy to spot. For example, when the temporary tablespace runs out of space, the following error will be thrown:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

When you see this error, you need to determine if there's enough space in the temporary tablespace or if it's a rare runaway SQL query that has temporarily consumed an inordinate amount of temp space.

Both of these issues are discussed in the following sections.

Determining if Temporary Tablespace is Sized Correctly

The temporary tablespace is used as a sorting area on disk when a process has consumed the available memory and needs more space. Operations that require a sorting area include:

• Index creation

• SQL sorting operations

• Temporary tables and temporary indexes

• Temporary LOBs

• Temporary B-trees

There is no exact formula for determining if your temporary tablespace is sized correctly. It depends on the number and types of queries, index build operations, parallel operations, and size of your memory sort space (program global area). You’ll have to monitor your temporary tablespace while there is a load on your database to determine its usage patterns. If you are using Oracle Database 11
g
or higher, run the following query to show both the allocated and free space within the temporary tablespace:

623

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

select

tablespace_name

,tablespace_size/1024/1024 mb_size

,allocated_space/1024/1024 mb_alloc

,free_space/1024/1024 mb_free

from dba_temp_free_space;

Here is some sample output:

TABLESPACE_NAME MB_SIZE MB_ALLOC MB_FREE

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

TEMP 200 200 170

If the FREE_SPACE (MB_FREE) value drops to near zero, there are SQL operations in your database consuming most of the available space. The FREE_SPACE (MB_FREE) column is the total free space available, including space currently allocated and available for reuse.

If you are using an Oracle Database 10
g
database, run this query to view space being used in your temporary tablespace:

select

tablespace_name

,sum(bytes_used)/1024/1024 mb_used

from v$temp_extent_pool

group by tablespace_name;

Here is some sample output:

TABLESPACE_NAME MB_USED

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

TEMP 120

If the used amount is getting near your current allocated amount, you may need to allocate more space to the temporary tablespace datafiles. Run the following query to view the temporary datafile names and allocated sizes:

SQL> select name, bytes/1024/1024 mb_alloc from v$tempfile;

Here is some typical output:

NAME MB_ALLOC

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

/ora02/DWREP/temp01.dbf 12000

/ora03/DWREP/temp03.dbf 10240

/ora01/DWREP/temp02.dbf 2048

When first creating a database, if I have no idea as to "correct" size of the temporary tablespace, I'll usually size this tablespace at something like 2GB. If I'm building a data warehouse type database, I might size the temporary tablespace at something like 20GB. You'll have to monitor your temporary tablespace with the appropriate SQL and adjust the size as necessary.

Viewing SQL that is Consuming Temporary Space

When Oracle throws the ORA-01652 “unable to extend temp” error, it’s one indicator that your temporary tablespace is too small. However, Oracle may throw that error if it runs out of space because of a one-time event, like a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query: 624

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

SELECT

s.sid

,s.serial#

,s.username

,p.spid

,s.module

,p.program

,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used

,su.tablespace

FROM v$sort_usage su

,v$session s

,dba_tablespaces tbsp

,v$process p

WHERE su.session_addr = s.saddr

AND su.tablespace = tbsp.tablespace_name

AND s.paddr = p.addr

GROUP BY

s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,

p.program, tbsp.block_size, su.tablespace

ORDER BY s.sid;

If you determine that you need to add space, you can either resize an existing datafile or add a new datafile. To resize a temporary tablespace datafile, use the ALTER DATABASE TEMPFILE...RESIZE

statement. The following resizes a temporary datafile to 12GB:

SQL> alter database tempfile '/ora03/DWREP/temp03.dbf' resize 12g; You can add a datafile to a temporary tablespace as follows:

SQL> alter tablespace temp add tempfile '/ora04/DWREP/temp04.dbf' size 2g; Auditing

Auditing
usually means creating a record whenever a certain event happens. Activities that are typically audited include:

• When a table is inserted into, selected from, updated, or deleted from.

• User logon/logoff times.

• What SQL was used to update a table.

• When was an index last used.

Auditing is especially helpful when troubleshooting security issues with the database. Auditing gives you information to diagnose what objects are accessed by which users and when. This gives you a mechanism to detect and report on unauthorized actions or security breaches. For this reason, many databases are required to enable some degree of auditing to comply with regulatory agencies or internal company security requirements.

Auditing can also help with diagnosing some performance issues. For example, knowing what SQL

ran and when or how many users are connected to the database at a specific time will help with diagnosing and resolving some issues.

625

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

There are several different ways to enable auditing:

•

•

•

•

•

I'm not going to cover every aspect of auditing in the prior list (that would be a large amount of documentation). Rather, I'll show the basic techniques that DBAs use to audit databases. If you need more details, see the Oracle
Database Security Guide
(available on Oracle's OTN website).

Enabling Oracle Standard Auditing

Oracle Standard Auditing allows you to audit nearly any type of SQL activity in the database. You can audit any type of insert, update, delete, or select on a tables. You can also audit any system privilege activity such as CREATE TABLE, DROP INDEX, and so on.

You can enable auditing BY ACCESS or BY SESSION. Prior to Oracle Database 11
g
release 2, the BY

SESSION would record just one record per session for an auditing action. With the current version of Oracle, the BY SESSION will record multiple records for the same auditing action.

You can also enable auditing by WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL. This allows you to audit specifically for the success or failure of a particular statement.

Oracle's Standard Auditing feature is enabled through setting the AUDIT_TRAIL initialization parameter. I usually set the AUDIT_TRAIL parameter to DB, which specifies that Oracle will write audit records to an internal database table named AUD$. For example, when using an spfile, here's how to set the AUDIT_TRAIL parameter:

SQL> alter system set audit_trail=db scope=spfile;

If you are using an init.ora file, open it with a text editor and set the AUDIT_TRAIL value to DB. See Table 22–5 for a description of valid values for the AUDIT_TRAIL parameter.

■
Tip
I typically set the AUDIT_TRAIL parameter to DB even if I'm not doing any auditing. This way when I want to enable auditing for a specific action, I can do so without having to stop and re-start the database.

626

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

Table 22–5.
Valid AUDIT_TRAILSettings

Setting Meaning

DB

Enables auditing and sets the SYS.AUD$ table as the audit repository.

DB_EXTENDED

Enables auditing and sets the SYS.AUD$ table as the audit repository and includes the SQLTEXT and SQLBIND columns. This is useful for viewing the actual SQL statement that was issued. Be careful when using this option as it will consume much more space. In prior releases of Oracle, the parameter was specified as 'DB, EXTENDED'.