Home » RDBMS Server » Server Administration » Auditing Oracle 11g (Oracle 11g Enterprise Edn 11.2.0.3.0 - 64bit, Windows Server 2008 R2 Enterprise Edn)
Auditing Oracle 11g [message #661691] Tue, 28 March 2017 05:14 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

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

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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 16:49:51 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.