In Oracle Database 19c, you can enable audit logging to track database activity using the built-in auditing features. Here's how to enable audit logging:
1. Connect as a privileged user: Log in to the Oracle Database as a user with administrative privileges, such as SYSDBA or SYSOPER.
2. Enable the auditing feature: Run the following SQL command to enable standard auditing for the entire database:
AUDIT ALL;
3. Specify audit settings: Determine which database operations you want to audit and specify the audit settings accordingly. For example, to audit all SQL statements executed by users, you can use:
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE;
4. Enable auditing for specific objects: If you want to audit specific tables or objects, you can use the AUDIT command with object-level granularity. For example:
AUDIT SELECT ON employees;
5. Verify audit settings: You can verify the audit settings by querying the DBA_PRIV_AUDIT_OPTS and DBA_STMT_AUDIT_OPTS views:
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
SELECT * FROM DBA_STMT_AUDIT_OPTS;
6. Configure audit trail: Choose the appropriate audit trail destination where audit records will be stored. You can configure audit trails to write to the database, the operating system, or both. For example, to write audit records to the database and the operating system, you can use:
ALTER SYSTEM SET audit_trail=db, extended SCOPE=SPFILE;
7. Restart the database: Restart the Oracle Database instance to apply the changes to the audit trail configuration.
8. Test the auditing: Perform database operations that fall within the scope of the audit settings to generate audit records. You can then query the audit trail to review the recorded activities.
By following these steps, you can enable audit logging in Oracle Database 19c to track and monitor database activity effectively.
