| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Moving SYS.AUD$ unsupported?
This is a multi-part message in MIME format.
------=_NextPart_000_006B_01C07F43.A0B92B00 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
There is an article on Metalink (Note:72460.1), where they say that moving it is not supported, but than give the procedure how to do it.
Btw, any idea why they do not make a SYS synonym instead of the view?
Djordje
> I'm getting ready to set up some auditing and in the past I've routinely
> moved the SYS.AUD$ out of the system tablespace thinking it was the only
> data dictionary table move supported by Oracle. But I just stumbled on the
> following in the 8.1.6 Admin manual:
>
> "Note: Moving the SYS.AUD$ table out of the SYSTEM tablespace
> is not supported because the Oracle code makes implicit
> assumptions about the data dictionary tables such as SYS.AUD$,
> which could cause problems with upgrades and backup/recovery
> scenarios."
>
> Is this a documentation change reflecting a new Oracle policy? Does anyone
> know why Oracle made this change? What would be the ramifications of
moving
> the table?
>
> Steve Orr
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steve Orr
> INET: sorr_at_arzoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
------=_NextPart_000_006B_01C07F43.A0B92B00 Content-Type: text/plain;
name="MovingAUD.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="MovingAUD.txt"
Doc ID: Note:72460.1
Subject: Moving AUD$ to another Content Type: TEXT/PLAIN
tablespace and adding Creation Date: 24-JUN-1999
triggers to AUD$ Last Revision 30-DEC-1999
Type: BULLETIN Date:
Status: PUBLISHED Language: USAENG
Purpose
=3D=3D=3D=3D=3D=3D=3D
This article contains a script to move the "SYS.AUD$" table from the =
user SYS
and tablespace SYSTEM to another user and tablespace. It also gives an =
example
of a trigger that could be added to the resultant table for recording
additional audit information.
Scope & Application
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
For DBA's requiring to move SYS.AUD$ from the SYSTEM tablespace or =
recording
additional audit information.
Overview
=3D=3D=3D=3D=3D=3D=3D=3D
This script has been tested with Oracle8i, Release 8.1.5 and should work =
with
all Oracle8 releases. It is NOT supported because the following are NOT
supported :
The reason the above are not supported is because the Oracle Code makes
implicit assumptions about the data dictionary tables such as SYS.AUD$, =
which
could cause problems with upgrades and backup/recovery scenarios.
Moving AUD$
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Oracle stores audit trail records in a single table, "SYS.AUD$", being a =
base
data dictionary table, it is not supported to change it. However, it is
supported to modify the default storage parameters (except INITIAL) in
"SYS.AUD$".
By changing the "SYS.AUD$" storage parameters, the table can grow as =
large as
you require, but only inside the SYSTEM tablespace. The Oracle8i =
Administrator's
Guide covers ways of managing this growth within the SYSTEM tablespace, =
but
without the additional flexibility provided by moving "SYS.AUD$" to =
another
tablespace.
The following script will allow you to move the "SYS.AUD$" table out of =
the
SYSTEM tablespace and from the SYS to SYSTEM user (although it could be =
any
other user).
audit_trail=3Dnone
2. Run the following in Server Manager :
connect sys/<password>
create table system.aud$
tablespace <tablespace name>
as select * from aud$;
create index system.i_aud1 on system.aud$(sessionid, ses$tid);
rename aud$ to aud$_temp;
create view aud$ as select * from system.aud$;
connect system/<password>
grant all on aud$ to sys with grant option; grant delete on aud$ to delete_catalog_role;
3. Re-Start the database with :
audit_trail=3DDB
4. Recreate the data dictionary views for auditing :
connect sys/<password>
@%ORACLE_HOME%/rdbms/admin/cataudit.sql
For customers who only need to move the SYS.AUD$ table to a new =
tablespace
this is also documented in PR:1019377.6.
Overview of adding triggers to AUD$
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Default Oracle auditing purely stores a record of the type of action =
performed
by a user; it is often useful to record additional information about the =
action
performed by the user.
The following is a example of a trigger that could be added after the =
above
script has been run :
create table system.accounting
(USERNAME VARCHAR2(30),
LOGIN_TIME DATE,
LOGOFF_TIME DATE,
SCHEMANAME VARCHAR2(30),
OSUSER VARCHAR2(15),
PROCESS VARCHAR2(9),
MACHINE VARCHAR2(64),
TERMINAL VARCHAR2(8),
PROGRAM VARCHAR2(64),
TYPE VARCHAR2(10),
LOGICAL_READ NUMBER,
PHYSICAL_READ NUMBER,
SID NUMBER,
SESSION_SID NUMBER,
CPU_TIME NUMBER)
create or replace trigger acc_trigger
after update on system.aud$ for each row
when (new.action# =3D 101 or new.action# =3D 102)
declare
v_conc_id number;
begin
v_conc_id :=3D 99;
insert into system.accounting
select :new.userid,
:new.timestamp#,
:new.logoff$time,
ss.schemaname,
ss.osuser,
ss.process,
ss.machine,
ss.terminal,
ss.program,
ss.type,
:new.logoff$lread,
:new.logoff$pread,
st.sid,
ss.audsid,
st.value
from V$STATNAME S, V$SESSTAT ST, V$SESSION SS
where S.STATISTIC# =3D ST.STATISTiC# AND ST.SID =3D SS.SID
and :new.sessionid =3D SS.AUDSID
and S.NAME=3D'CPU used by this session';
end;
An example of querying the accounting table is :
select osuser,username,
to_char(LOGIN_TIME,'dd.mm.yy:hh:mi') login,
to_char(LOGOFF_TIME,'dd.mm.yy:hh:mi') logout,
MACHINE,
CPU_TIME
_________________________________________________________________________=
_____
Oracle Support =
Services
 |
 |