We have seen the database is slow and we dont know what. When we looked the audit and FRA table are in system table space and it is not recommended. Move table Aud$ from System to a new table space.
a-Run the below create tablespace
CREATE BIGFILE TABLESPACE AUDIT_DATA
DATAFILE '+DATA' SIZE 128G AUTOEXTEND ON NEXT 2G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO;
b-Now move the tables to newly created table space from SYSTEM.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_DATA');
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_DATA');
END;
/
c-Verify the move was successful to AUDIT_DATA.
SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
You should see improvement in performance.
a-Run the below create tablespace
CREATE BIGFILE TABLESPACE AUDIT_DATA
DATAFILE '+DATA' SIZE 128G AUTOEXTEND ON NEXT 2G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO;
b-Now move the tables to newly created table space from SYSTEM.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_DATA');
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_DATA');
END;
/
c-Verify the move was successful to AUDIT_DATA.
SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
You should see improvement in performance.
No comments:
Post a Comment