How to Enable Audit for Grantor User in Oracle Database

Scenario: Here we want to enable audit for those Database users who are giving Database Privileges to other Database users. 

Step 1: Create two users (said and mihir) and grant DBA privileges to one user

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>create user said identified by said ;

SQL> grant dba to said;

SQL> create user mihir identified by mihir;

Step 2: Now connect to Database with DBA privilege user and grant some privileges to other user

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:39:28 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: said/said

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter audit

NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest       string  /u01/app/oracle/admin/orcl/adu
 mp
audit_sys_operations      boolean  TRUE
audit_syslog_level      string
audit_trail      string  DB, EXTENDED

SQL> grant create table to mihir; 

Grant succeeded.

SQL> grant create session to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
0


Step 3: Now grant privilege to user SAID for auditing 

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> AUDIT system grant by "SAID";

Audit succeeded.


SQL> show parameter audit

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest      string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations      boolean TRUE
audit_syslog_level      string
audit_trail      string DB, EXTENDED


Step 4: Now provide privilege to user mihir, connect database as said

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn said/said
Connected.
SQL>
SQL> show user
USER is "SAID"

SQL> grant create session to mihir;

Grant succeeded.

SQL> grant create table to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
2

SQL> column username format a10
SQL> column action_name format a20
SQL> column grantee format a10
SQL> column sql_text format a40
SQL> set lin 100
SQL> select username,action_name,grantee, sql_text from dba_audit_trail;

USERNAME   ACTION_NAME GRANTEE    SQL_TEXT
---------- -------------------- ---------- ----------------------------------------
SAID    SYSTEM GRANT MIHIR    grant create table to mihir
SAID    SYSTEM GRANT MIHIR    grant create session to mihir

SQL>

SQL> show parameter audit

NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest       string  /u01/app/oracle/admin/orcl/adu
 mp
audit_sys_operations      boolean  TRUE
audit_syslog_level      string
audit_trail      string  DB, EXTENDED

Thats it....!!!!!!!!!

Delete large no of file in Linux -bash: /bin/find: Argument list too long

Error:::

[root@said audit]# ls -l | wc -l
173460

[root@said audit]# find /var/lib/oracle/grid/rdbms/audit/*.aud -mtime +2 -exec rm {} \;
-bash: /bin/find: Argument list too long

Solution: Delete large no of file generated in linux server using the following command

[root@said audit]# find /var/lib/oracle/grid/rdbms/audit -type f -name "*.aud" | xargs rm -f

[root@said audit]# clear

[root@said audit]# ls -l | wc -l

50

Background Media Recovery terminated with ORA-1274 ORA-01186 ORA-01157 ORA-01111 after adding a Datafile in Primary

Error

Errors in file /u01/oracle/diag/rdbms/orcldg/ORCL/trace/ORCL_dbw0_8408.trc:
ORA-01186: file 8 failed verification tests
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008'
File 8 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/oracle/diag/rdbms/orcldg/T


Reason 

This Error occurs if we add a Datafile OR Tablespace in PRIMARY Database and that could not be translated to the Standby Database due to these Reasons:

    - Standby_file_management is set to MANUAL
    - Primary & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in Primary and Standby
    - Insufficient Space or wrong Permissions on the Standby Database to create the Datafile
    - If standby_file_management is set to Auto ,but directory path of Primary and standby are different , db_file_name_convert is not set ,but db_create_file_dest has been set to wrong value on standby.


The Redo Log generated from Primary will have Information about the Tablespace / Datafile added however it could not be created successfully in Physical Standby Database due to the standby_file_management = MANUAL

or is not able to find the specified Folder due to a missing / incorrect Filename Conversion.
The File Entry is added to Standby Controlfile as "UNNAMED0000n" in /dbs or /database
folder depends on the Operating System and eventually the MRP terminates.

Solution:

Step 1: Check that the standby_file_management = 'MANUAL.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>

Step 2: Identify the File which is "unnamedn"


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADATA/orcldg/datafile/system.310.932810169
+ORADATA/orcldg/datafile/sysaux.309.932810385
+ORADATA/orcldg/datafile/undotbs1.308.932810409
+ORADATA/orcldg/datafile/users.305.932810455
+ORADATA/orcldg/datafile/undotbs2.307.932810425
+ORADATA/orcldg/datafile/abc.311.932809925
+ORADATA/orcldg/datafile/abcd.306.932810439
/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

8 rows selected.


Step 3: Rename/create the Datafile to the correct Filename

SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'D:\oradata\mystd\sales01.dbf';


If the standby is in ASM + OMF then use the below command,

SQL> alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as <'+ASMDISKGROUPNAME'> size ;

alter database create datafile '/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008' as '+ORADATA' size 25G;


Step 4: Verify the Filename is correct

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADATA/orcldg/datafile/system.310.932810169
+ORADATA/orcldg/datafile/sysaux.309.932810385
+ORADATA/orcldg/datafile/undotbs1.308.932810409
+ORADATA/orcldg/datafile/users.305.932810455
+ORADATA/orcldg/datafile/undotbs2.307.932810425
+ORADATA/orcldg/datafile/abc.311.932809925
+ORADATA/orcldg/datafile/abcd.306.932810439
+ORADATA/orcldg/datafile/audit_tblspc.329.948974823

8 rows selected.

SQL>

Step 5: Change the STANDBY_FILE_MANAGMENT  MANUAL to AUTO


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.

Step 6: Start the MRP (this is using Real Time Apply)


SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Step 7: Verify the MRP is running as expected


SQL> select process, status , sequence# from v$managed_standby;

PROCESS   STATUS SEQUENCE#
--------- ------------ ----------
ARCH  CONNECTED 0
ARCH  CONNECTED 0
ARCH  CONNECTED 0
ARCH  CONNECTED 0
RFS  IDLE 0
RFS  IDLE 0
RFS  IDLE    27033
RFS  IDLE    38836
RFS  IDLE 0
RFS  IDLE 0
MRP0  APPLYING_LOG    27026

11 rows selected.

SQL>