Tag Archives: system

error, Oracle

Resolving Oracle Error ORA-01653 by adding additional storage

Leave a comment

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM

1. Logon to the Oracle database as a sysdba user.

2. Validate that the system tablespace is out of space.

set pagesize 500
select a.tablespace_name,
(select sum(b.bytes)/1024/1024/1024 from dba_data_files b where a.tablespace_name=b.tablespace_name) as GB_TOT,
(select sum(c.bytes)/1024/1024/1024 from dba_free_space c where a.tablespace_name=c.tablespace_name) as GB_AVAIL,
100*(select sum(c.bytes) from dba_free_space c where a.tablespace_name=c.tablespace_name)/(select sum(b.bytes) from dba_data_files b where a.tablespace_name=b.tablespace_name) as PERC_FREE
from dba_tablespaces a where tablespace_name=’SYSTEM’;

TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 10.27125 .01766357 .024375

SQL>

3. The query above showes that the system tablespace is out of space. The quickest fix is to add additional space to the SYSTEM tablespace, additionally we
can address the amount of data stored in the AUD$ tables referenced in the above error message. This alternative is address in another article.

4. To add additional space to the system tablespace, find the current location of SYSTEM data files on your system with the following statement.

SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name=’SYSTEM’;

FILE_NAME BYTES/1024/1024
——————————— —————

/u01/oradata/UCOP/SYSTEM01.DBF 10000

SQL>

5. Verify that you have enough space available on your drives and add an additional datafile;

alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;

SQL> alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;

Tablespace altered.

SQL>

6. Validate that you know have available free space in the SYSTEM tablespace and re-execute the statement which caused your initial error.

set pagesize 500
select a.tablespace_name,
(select sum(b.bytes)/1024/1024/1024 from dba_data_files b where a.tablespace_name=b.tablespace_name) as GB_TOT,
(select sum(c.bytes)/1024/1024/1024 from dba_free_space c where a.tablespace_name=c.tablespace_name) as GB_AVAIL,
100*(select sum(c.bytes) from dba_free_space c where a.tablespace_name=c.tablespace_name)/(select sum(b.bytes) from dba_data_files b where a.tablespace_name=b.tablespace_name) as PERC_FREE
from dba_tablespaces a where tablespace_name=’SYSTEM’;

TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 19.53125 9.1766357 49.184375

SQL>

7. This completes resolving Error ORA-01653

Larry Catt

11g, audit, Oracle, Oracle 10g, oracle 9i

Removal of Oracle auditing on specific privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which privilege and user in the database they suspect is causing a problem, thus they only wish to monitor that privilege. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific privilege on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

2. Logon to your Oracle database server as the Oracle software owner.

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Mar 6 10:22:512010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. In this procedure we will audit the create table privilege by the user LJCATT, execute the following command to begin the auditing process for our specified object: audit create table by {schema.object_name};

SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the privilege ‘create table ‘ by LJCATT is setup for auditing.

SQL> select user_name, privilege from DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE TABLE

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all privilege auditing. To only remove auditing on LJCATT privileges replace the line

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from sys.dba_obj_audit_opts where USER_NAME=’LJCATT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL
Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed, re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_PRIV_AUDIT_OPTS;

SQL> select * from DBA_PRIV_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a privilege in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Determining top system wait events

Leave a comment

Oracle monitors the wait events which occur in a database in two ways: Session level and System level. Session level wait events records events which affect a single user activities within the database. System level wait events records all events which affect the entire database system and all user activities within it. When considering which to use during active performance issues, it is normally best to concentrate on session level which would show which user is actually causing the problem. However, situations such as loss of a hard disk would appear across all session and thus it would be best to monitor from a system level. In this article we will examine how to find the top system wait events occurring. This procedure will work regardless of OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 01 22:14:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. For this procedure we will be using the SYS view of V_$SYSTEM_EVENT, to see what is causing possible database slowdown. Below is a description of the view:


SQL> desc V_$SYSTEM_EVENT
Name Null? Type
—————————————– ——– —————————-
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

4. First, let us look at retrieving the TOTAL_WAITS for a particular event, execute the following SQL: select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc


SQL> select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc
2 ;

EVENT TOTAL_WAITS
——————————- ———–
rdbms ipc message 59943
PX Idle Wait 19430
db file sequential read 12983
control file sequential read 12177
SQL*Net more data from dblink 9250

5. Second, you see the TOTAL_TIMEOUTS (halt of work) due to wait events.


select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1 order
by TOTAL_TIMEOUTS desc;

SQL> select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1
order by TOTAL_TIMEOUTS desc;

EVENT TOTAL_TIMEOUTS
———————– ————–
rdbms ipc message 59949
PX Idle Wait 19701
pmon timer 6339
PX qref latch 1954
jobq slave wait 1408

6. Third, we see the total TIME_WAITED for a type of event, recorded in milisecond.


select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by
TIME_WAITED desc;

EVENT TIME_WAITED
——————— ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

7. Finally, we get the average time in miliseconds of a wait event.


select event, AVERAGE_WAIT from V_$SYSTEM_EVENT where average_wait>.1
AVERAGE_WAIT order by desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

EVENT TIME_WAITED
————————- ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

The above information can be used to fine tune the Oracle RDBMS and improve the performance of your system.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, audit, Oracle, Oracle 10g, oracle 9i

Auditing the use of an Oracle privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In this article we will show how to audit use of a system privilege by a particular user in the Oracle RDBMS. This procedure will work on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Nov 14 13:28:31 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ——————————
Audit_trail string DB

SQL>

4. If the parameter is not set to DB and you are using a SPFILE, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database, add the string AUDIT_TRIAL=DB and start the database.

NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

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

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. In this procedure we will audit the creation of tables by the user LJCATT. To perform this auditing, we will use the command: audit create table by {user_name};

SQL> connect / as sysdba
Connected.
SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

7. Perform a select on the view DBA_PRIV_AUDIT_OPTS, to verify that auditing is enabled.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-

LJCATT CREATE TABLE

SQL>

8. Logon as the user you are auditing and create a table.

SQL> connect ljcatt
Enter password:
Connected.
SQL> create table test_audit(col_a varchar2(30));

Table created.

SQL>

9. Logon with sysdba privileges and verify that the create table action was recorded in you DBA_AUDIT_TRAIL view. As seen below the audit record for the create table privilege was recorded.

SQL> connect / as sysdba
Connected.
SQL>
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAM
———- ———-
LJCATT CREATE TABLE

This completes auditing of a system privilege by a user account within the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com