OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Auditing Oracle 11g (Oracle 11g Enterprise Edn 11.2.0.3.0 - 64bit, Windows Server 2008 R2 Enterprise Edn)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Auditing Oracle 11g [message #661691]

Tue, 28 March 2017 05:14

ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia

Member

Hi,

Can we disable Audit_trail parameter from pfile in our database, as our SYSTEM tablespace size is increasing day by day.
Now our main concern is,

1. Can we resize SYSTEM tablespace? or
2. Is it possible to add a new datafile to the SYSTEM tablespace? Currently it has a single datafile.
3. Is it recommended to turn off the audit trail by using AUDIT_TRAIL=NONE in pfile

Your valuable advise and guidance in this regard is highly appreciated.

-Aijaz

Report message to a moderator

Re: Auditing Oracle 11g [message #661692 is a reply to message #661691]

Tue, 28 March 2017 05:46

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Can we disable Audit_trail parameter from pfile in our database

Yes.

Quote:

1. Can we resize SYSTEM tablespace? or

Yes.

Quote:

2. Is it possible to add a new datafile to the SYSTEM tablespace?

Yes.

Quote:

3. Is it recommended to turn off the audit trail by using AUDIT_TRAIL=NONE in pfile

No and yes.

Report message to a moderator

Re: Auditing Oracle 11g [message #661695 is a reply to message #661691]

Tue, 28 March 2017 07:00

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Rather than disable AUDIT, you should MOVE the audit trail tables OUT of the system TS and into their own dedicated TS. And set up a schedule of deleting old audit records on a regular basis. This should have been done a long time ago. See the documentation on dbms_audit_mgmt.

You can resize the SYSTEM TS, but it will require down time.

And you mention "Is it recommended to turn off the audit trail by using AUDIT_TRAIL=NONE in pfile".
Are you still using a pfile and not an spfile?

Report message to a moderator

Re: Auditing Oracle 11g [message #661696 is a reply to message #661692]

Tue, 28 March 2017 07:11

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You should not have the SYS.AUD$ table in the SYSTEM tablespace. Personally I have done the following. This will only work in version 11 and above and you will (of course) use your own path for the new tablespace

Code: [Select all] [Show/ hide]

CREATE TABLESPACE AUDIT_TS DATAFILE 
  'C:\APP\ORACLE12C\ORADATA\WHB\PLB_ORCL\AUDIT01.DBF' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
DEFAULT 
  NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Then move the aud$ table with the following command

Code: [Select all] [Show/ hide]

exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');

Thats it, your audit table is now in a different tablespace which you can shrink or anything else you want to do.

Report message to a moderator

Re: Auditing Oracle 11g [message #661697 is a reply to message #661695]

Tue, 28 March 2017 09:28

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

EdStevens wrote on Tue, 28 March 2017 13:00

You can resize the SYSTEM TS, but it will require down time.

That's not the case for extending or adding a data file.

Report message to a moderator

Re: Auditing Oracle 11g [message #661698 is a reply to message #661697]

Tue, 28 March 2017 09:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If you use the SYSTEM tablespace for auditing you will cause fragmentation and your database will run slower. the SYSTEM tablespace if for the use of the database and nothing else. The auditing is a special case and ORACLE should automatically move it to another tablespace if it is turned on but they don't.

[Updated on: Tue, 28 March 2017 09:31]

Report message to a moderator

Previous Topic:	Wrong dates in some records in Timstamp with local zone in 10g
Next Topic:	How would you describe the ORACLE_HOME environment variable?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 19:04:10 CDT 2025