Oracle DBA_PROFILE

Profile is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.
Profiles are created by DBA and assigned to the users by DBA only. 
Only one profile can be assigned to one user account. 
Profile is nothing but security properties. 
If didn't assign any profile to user default profile will be there. 

To create a profile, you must have the CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.

Create a profile that defines the limits using the CREATE PROFILE statement

Assign the profile to the user using the CREATE USER or ALTER USER statement


Profile has two types of parameters

A. Resource Parameters:

• SESSIONS_PER_USER:Specify the number of concurrent sessions  to which you want to limit the user.
• CPU_PER_SESSION:Specify the CPU time limit for a session ,expressed in hundredth of seconds.
• CPU_PER_CALL:Specify the CPU time limit for a call(a parse,execute or fetch),expressed in hundredth of seconds.
• CONNECT_TIME:Specify the total elapsed time limit for a session ,expressed in minutes. 
• LOGICAL_READS_PER_SESSION:Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
• LOGICAL_READS_PER_CALL:Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
• PRIVATE_SGA:Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA).
• COMPOSITE_LIMIT:Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. 
• IDLE_TIME:Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

B. Password Parameters               

• FAILED_LOGIN_ATTEMPTS:Specify the number of failed attempts to log in to the user account before the account is locked.
• PASSWORD_LIFE_TIME:Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.
• PASSWORD_REUSE_TIME & PASSWORD_REUSE_MAX:These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.

If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.

For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.

If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.

If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.

If you set both of these parameters to UNLIMITED, then the database ignores both of them.

• PASSWORD_VERIFY_FUNCTION:The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement. Oracle Database provides a default script, but you can create your own routine or use third-party software instead.

For function, specify the name of the password complexity verification routine.

Specify NULL to indicate that no password verification is performed.

• PASSWORD_LOCK_TIME:Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
• PASSWORD_GRACE_TIME:Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

Profile Creation Syntax:

CREATE PROFILE <PROFILE-NAME> LIMIT <RESOURCE PARAMETER OR PASSWORD PARAMETER> VALUE<INTEGER OR DEFAULT OR UNLIMITED>;

EX:

SQL>CREATE PROFILE NEW_PROFILE  LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL                3000 
   CONNECT_TIME                45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL      1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 
   IDLE_TIME                  UNLIMITED

   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
   PASSWORD_LOCK_TIME 1
   PASSWORD_GRACE_TIME 10;


Run below sql statement to Check DEFAULT PROFILE LIMITS:

SQL>select * from dba_profile where profile='DEFAULT';

RESOURCE_NAME                         LIMIT
----------------------------          -----------------------
COMPOSITE_LIMIT                       UNLIMITED
SESSIONS_PER_USER                     UNLIMITED
CPU_PER_SESSION                       UNLIMITED
CPU_PER_CALL                          UNLIMITED
LOGICAL_READS_PER_SESSION             UNLIMITED
LOGICAL_READS_PER_CALL                UNLIMITED
IDLE_TIME                             UNLIMITED
CONNECT_TIME                          UNLIMITED
PRIVATE_SGA                           UNLIMITED
FAILED_LOGIN_ATTEMPTS                 10
PASSWORD_LIFE_TIME                    180
PASSWORD_REUSE_TIME                   UNLIMITED
PASSWORD_REUSE_MAX                    UNLIMITED
PASSWORD_VERIFY_FUNCTION              ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME                    1
PASSWORD_GRACE_TIME                   7

16 rows selected.

Run below sql statement to Check How many Profiles Exists in DB

SQL> select  profile from dba_profiles;

PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT

Run below sql statement to Check How many Profiles assigned to Users in DB


SQL> select distinct profile from dba_users;

PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT

SQL> desc dba_profiles;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 PROFILE                                   NOT NULL VARCHAR2(128)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(128)
 COMMON                                             VARCHAR2(3)

SQL> column limit format a10;
SQL> column resorce_name format a30;

Run below sql  to Check newly create PROFILE LIMITS:

SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';

RESOURCE_NAME                        LIMIT
------------------------------       ----------
COMPOSITE_LIMIT                      DEFAULT
SESSIONS_PER_USER                    2
CPU_PER_SESSION                      DEFAULT
CPU_PER_CALL                         DEFAULT
LOGICAL_READS_PER_SESSION            DEFAULT
LOGICAL_READS_PER_CALL               DEFAULT
IDLE_TIME                            5
CONNECT_TIME                         DEFAULT
PRIVATE_SGA                          DEFAULT
FAILED_LOGIN_ATTEMPTS                3
PASSWORD_LIFE_TIME                   30
PASSWORD_REUSE_TIME                  2
PASSWORD_REUSE_MAX                   3
PASSWORD_VERIFY_FUNCTION             DEFAULT
PASSWORD_LOCK_TIME                   DEFAULT
PASSWORD_GRACE_TIME                  5

16 rows selected.

Changing Limit for a Resource:

SQL> alter profile trainee limit password_life_time 40;
Profile altered.

SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';

RESOURCE_NAME                        LIMIT
------------------------------       ----------
COMPOSITE_LIMIT                      DEFAULT
SESSIONS_PER_USER                    2
CPU_PER_SESSION                      DEFAULT
CPU_PER_CALL                         DEFAULT
LOGICAL_READS_PER_SESSION            DEFAULT
LOGICAL_READS_PER_CALL               DEFAULT
IDLE_TIME                            5
CONNECT_TIME                         DEFAULT
PRIVATE_SGA                          DEFAULT
FAILED_LOGIN_ATTEMPTS                3
PASSWORD_LIFE_TIME                   40
PASSWORD_REUSE_TIME                  2
PASSWORD_REUSE_MAX                   3
PASSWORD_VERIFY_FUNCTION             DEFAULT
PASSWORD_LOCK_TIME                   DEFAULT
PASSWORD_GRACE_TIME                  5

16 rows selected.

Assigning Profile to User:

SQL> select profile from dba_users where username='SREENU';

PROFILE
--------------------------------------------------------------------------------
DEFAULT

SQL> alter user SREENU profile trainee;

User altered.

SQL> select profile from dba_users where username='SREENU';

PROFILE
--------------------------------------------------------------------------------
TRAINEE

Profiles and Password Verify Function: 
Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.
In Oracle Database 11g/12c the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g / ora12c_verify_function. At the end, the script has the following lines:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;


The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g/12c version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.
[oradb1@test ~]$ cd $ORACLE_HOME/rdbms/admin
[oradb1@test admin]$ pwd
/u03/oradb1/VIS/12.1.0/rdbms/admin

[oradb1@test admin]$ ls utlpwd*
utlpwdmg.sql                                         (Password management Utility)

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                         LIMIT
----------------------------          -----------------------
COMPOSITE_LIMIT                       UNLIMITED
SESSIONS_PER_USER                     UNLIMITED
CPU_PER_SESSION                       UNLIMITED
CPU_PER_CALL                          UNLIMITED
LOGICAL_READS_PER_SESSION             UNLIMITED
LOGICAL_READS_PER_CALL                UNLIMITED
IDLE_TIME                             UNLIMITED
CONNECT_TIME                          UNLIMITED
PRIVATE_SGA                           UNLIMITED
FAILED_LOGIN_ATTEMPTS                 10
PASSWORD_LIFE_TIME                    180
PASSWORD_REUSE_TIME                   UNLIMITED
PASSWORD_REUSE_MAX                    UNLIMITED
PASSWORD_VERIFY_FUNCTION              NULL
PASSWORD_LOCK_TIME                    1
PASSWORD_GRACE_TIME                   7

16 rows selected.

[oradb1@test admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 12:42:33 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @?/rdbms/admin/utlpwdmg.sql    (To enable pw_verify_function)

Function created.        (Here ?=$ORACLE_HOME)
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Profile altered.


SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                         LIMIT
----------------------------          -----------------------
COMPOSITE_LIMIT                       UNLIMITED
SESSIONS_PER_USER                     UNLIMITED
CPU_PER_SESSION                       UNLIMITED
CPU_PER_CALL                          UNLIMITED
LOGICAL_READS_PER_SESSION             UNLIMITED
LOGICAL_READS_PER_CALL                UNLIMITED
IDLE_TIME                             UNLIMITED
CONNECT_TIME                          UNLIMITED
PRIVATE_SGA                           UNLIMITED
FAILED_LOGIN_ATTEMPTS                 10
PASSWORD_LIFE_TIME                    180
PASSWORD_REUSE_TIME                   UNLIMITED
PASSWORD_REUSE_MAX                    UNLIMITED
PASSWORD_VERIFY_FUNCTION              ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME                    1
PASSWORD_GRACE_TIME                   7

16 rows selected.

Example for Password Verify Function:

sys SQL> alter user SREENU password expire;
User altered.

user 
[oradb1@test ~]$ sqlplus SREENU/sreenu123;

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:22:35 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
Password unchanged

Enter user-name: sreenu/sreenu123
ERROR:
ORA-28001: the password has expired

Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28007: the password cannot be reused
Password unchanged

Errors:
ORA-20025: Password must contain at least 1 digit(s)
ORA-20022: Password must contain at least 1 letter(s)
ORA-20002: Password contains the username

To Disable Password_verify_function:

SQL> alter profile default limit password_verify_function null;
Profile altered.

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME                     LIMIT
------------------------------    ----------
COMPOSITE_LIMIT                   UNLIMITED
SESSIONS_PER_USER                 UNLIMITED
CPU_PER_SESSION                   UNLIMITED
CPU_PER_CALL                      UNLIMITED
LOGICAL_READS_PER_SESSION         UNLIMITED
LOGICAL_READS_PER_CALL            UNLIMITED
IDLE_TIME                         UNLIMITED
CONNECT_TIME                      UNLIMITED
PRIVATE_SGA                       UNLIMITED
FAILED_LOGIN_ATTEMPTS             10
PASSWORD_LIFE_TIME                180
PASSWORD_REUSE_TIME               UNLIMITED
PASSWORD_REUSE_MAX                UNLIMITED
PASSWORD_VERIFY_FUNCTION          NULL
PASSWORD_LOCK_TIME                1
PASSWORD_GRACE_TIME               7

16 rows selected.

Now we can check 
[oradb1@test ~]$ sqlplus sreenu/sreenu123

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:31:27 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for sreenu
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "SREENU"
SQL> password
Changing password for SREENU
Old password:
New password:
Retype new password:
Password changed

Purge AUD$ Table Using DBMS_AUDIT_MGMT

Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier.

Follow below steps for puring aud$ table.

1. Make sure AUD$ table is not in SYSTEM table tablespace.

  If AUD$ table is present in SYSTEM tablespace , then move it to a a new dedicated tablespace using below script.

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER        SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS          AUD$         TABLE              SYSTEM                                   176


SQL>
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TS_AUDIT');
END;
/

SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL>
 select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER        SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS          AUD$         TABLE              TS_AUDIT                                   176


2. Check the audit mgmt configuration parameters:

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000

SQL>
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE            TS_AUDIT             STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               UNIFIED AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL

14 rows selected.

3. Initialize the clean job:

Note – If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.

SQL>
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/  

PL/SQL procedure successfully completed.

4. Validate whether initialization is success or not:
It should return YES



SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/ 

YES

PL/SQL procedure successfully completed.

5. Set LAST_ARCHIVE_TIMESTAMP:

             This value allows the audit record to be purged before a particular date. Suppose LAST_ARCHIVE_TIME is set to SYSTIMESTAMP -30, then it will keep only recent 30 days record and purge the all audit records dated before 30 days.

SQL>
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-30);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.


SQL>  COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                          DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
STANDARD AUDIT TRAIL            0 28-MAY-16 11.29.39.000000 AM +00:00        552547857 183E654F83D9063AE0540010E0202A24



6. If you want to run the purge instantly( one time):

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

7. Schedule a job to purge AUD$ regularly.

Below job will run daily and delete all the aud$ records before systimestamp- 30 .

SQL[SYS@TCRMDB03]SQL>>]BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'PURGE_DB_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'set_last_archive_aud_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

8. Updating audit properties.

Note:-   If the AUD$ table, lot of rows ( around 2 to 3 crores), then setting the DB AUDIT CLEAN BATCH SIZE to a bit higher values (around 2 to 3 lakh) will increase the performance.  Keeping a low value will increase the time taken for purge and a very high value will led to higher undo generation.

SQL>
  SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL

SQL> SQL>
SQL> begin
 dbms_audit_mgmt.set_audit_trail_property (
 audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
 audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,
 audit_trail_property_value => 300000);
 end;
 /  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE      300000               STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL

Oracle Unified Auditing

Unified auditing enables you to capture audit records from a variety of sources.


Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).


The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.

When the database is writable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the POSIX file system in the $ORACLE_BASE/audit/$ORACLE_SID directory.


Enabling Unified Auditing

The Unified Auditing option is not enabled after you intall Oracle Database. You can find if your database has been migrated to unified auditing by querying the V$OPTION dynamic view. Query the VALUE column of the V$OPTION dynamic view as follows with SQL*Plus:



SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER         VALUE

----------------  ----------
Unified Auditing  FALSE

If the output for the VALUE column is TRUE, then pure unified auditing is already enabled in your database. If unified auditing has not been enabled, then the output is FALSE.

To enable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.

After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to enable Unified Auditing:

/CALL-PROCEDURE INSTALL.P.UNIAUD-ON

Restart the databases and listeners in your DBA user IDs. After restarting, all the databases run with Unified Auditing.

Steps:

1. Stop all Oracle processes: databases and listener.

Logon to oracle database server and logon to sqlplus with sysdba.

 [oracle@Oradb1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:35:07 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@Oradb1 lib]$ps -ef | grep pmon

Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop

[oracle@Oradb1 lib]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:38:36

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))
The command completed successfully
[oracle@Oradb1 lib]$

2. Relink Oracle with the uniaud_on option.

[oracle@Oradb1 lib]$cd $ORACLE_HOME/rdbms/lib

[oracle@Oradb1]make -f ins_rdbms.mk uniaud_on ioracle


3. Restart all Oracle processes:  listener, databases.For the purpose of the demonstration, only the orcl database instance needs to be started and the listener.

Startup the Oracle listener with command: lsnrctl start

[oracle@Oradb1 lib]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:41:06
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/Oradb1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                15-DEC-2015 10:41:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF

Listener Parameter File   /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File         /opt/app/oracle/diag/tnslsnr/Oradb1/listener/alert/log.xml

Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))
Services Summary…
Service “cdb1” has 1 instance(s).

  Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@Oradb1 lib]$

Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:41:32 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup open
ORACLE instance started.
Total System Global Area  549453824 bytes
Fixed Size                  2926616 bytes
Variable Size             268437480 bytes
Database Buffers          272629760 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.
SQL>

4. Verify that unified auditing is now enabled.
SQL> sqlplus / as sysdba
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER         VALUE
----------------  ----------
Unified Auditing  TRUE

Disabling Unified Auditing

To disable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.


After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to disable Unified Auditing:

/CALL-PROCEDURE INSTALL.P.UNIAUD-OFF

1. Restart the databases and listeners in your DBA user IDs. After restarting, all databases run without the Unified Auditing option.

Logon to oracle database server and logon to sqlplus with sysdba.

[oracle@Oradb1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:35:07 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

SQL>

Disable any enabled unified audit policies, preventing database from going into mixed mode auditing. You can use the statement:  select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;

SQL> select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;
‘NOAUDITPOLICY’||POLICY_NAME||’;’
——————————————————————————–
noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

SQL> noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.
SQL>
SQL> noaudit policy ORA_LOGON_FAILURES;
Noaudit succeeded.

SQL>
Verify that no unified audit policies are now enabled by querying the audit_unified_enabled_policy table.

SQL> select count(*) from audit_unified_enabled_policies;

COUNT(*)
———-
  0

2. Shutdown the database with immediate option.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3. Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop


SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

[oracle@Oradb1 lib]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:38:36
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))
The command completed successfully
[oracle@Oradb1 lib]$

4. From the command prompt go to directory $ORACLE_HOME/rdbms/lib and use make to execute ins_rdbms.mk uniaud_off ioracle command
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle
5. Startup the Oracle listener with command: lsnrctl start

[oracle@Oradb1 lib]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:41:06
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/Oradb1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))
STATUS of the LISTENER
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                15-DEC-2015 10:41:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/Oradb1/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))
Services Summary…
Service “cdb1” has 1 instance(s).
Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@Oradb1 lib]$
6. Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:41:32 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup open
ORACLE instance started.
Total System Global Area  549453824 bytes
Fixed Size                  2926616 bytes
Variable Size             268437480 bytes
Database Buffers          272629760 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

SQL>
7. Verify that the unified Auditing is off by following statement: select value from v$option where parameter=’Unified Auditing’;
SQL> select value from v$option where parameter=’Unified Auditing’;
VALUE
——————
FALSE