DB Audit on OS LEVEL

In our database there is turned on auditing on some operations and audit records go to OS.

AUDIT_TRAIL =     { none | os | db | db,extended | xml | xml,extended }


DB              Auditing is enabled. Audit records will be written to the
                SYS.AUD$ table.
OS              Auditing is enabled. Audit records will be written to an
                audit trail in the operating system.
db,extended     As db, but the SQL_BIND and SQL_TEXT columns are also populated.
NONE            Auditing is disabled (default).
xml-            Auditing is enabled, with all audit records stored
                as XML format OS files.
xml,extended    As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
TRUE            This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the DB value.
FALSE           This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the NONE value.



SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/app/oracle/admin/oravr/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      LOCAL7.INFO
audit_trail                          string      OS
SQL>




1. Connect to a database instance as sysdba user

SQL> connect / as sysdba

2. Set audit trail to OS

SQL> alter system set audit_trail=OS;

3. Enable auditing for system users

SQL> alter system set audit_sys_operations=TRUE;

4. Set rsyslog facility and severity(needs database restart)

SQL> alter system set audit_syslog_level=local7.info scope=spfile sid='*';

5.  Restart database

SQL> shutdown immediate;

SQL> startup;


6. Edit rsyslog.conf file



#Saving oracle database audit records
local7.info          /opt/syslog/oracle_audit.log

--Optional

#Send oracle database audit trail to remote rsyslog server
local5.info          @192.168.2.54


7. Restart rsyslog service

# service rsyslog restart

Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

8. It is better to limit the size for audit log, or it may fill the space:

# vi /etc/logrotate.d/oracle.audit

#Created by MariK

/opt/syslog/oracle_audit.log {
 rotate 3
 compress
 missingok
 notifempty
 size 40G
 postrotate
 service rsyslog restart
 endscript
}

To check the syntax run :

# logrotate /etc/logrotate.d/oracle.audit

Example:

oravr1.oravr.in:/home/oracle> tailf /opt/syslog/oracle_audit.log

Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "287" SESSIONID:[9] "162326064" ENTRYID:[4] "8009" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[8] "PROFILE$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "288" SESSIONID:[9] "162326064" ENTRYID:[4] "8010" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[9] "DBA_USERS" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "283" SESSIONID:[9] "162326064" ENTRYID:[4] "8011" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "DUAL" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "302" SESSIONID:[9] "162326064" ENTRYID:[4] "8012" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "OBJ$" OS$USERID:[6] "oracle" DBID:[10] "4040881803" PRIV$USED:[3] "237"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8013" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "283" SESSIONID:[9] "162326064" ENTRYID:[4] "8014" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "OBJ$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8015" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "300" SESSIONID:[9] "162326064" ENTRYID:[4] "8016" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[20] "_CURRENT_EDITION_OBJ" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8017" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "290" SESSIONID:[9] "162326064" ENTRYID:[4] "8018" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[10] "_BASE_USER" OS$USERID:[6] "oracle" DBID:[10] "4040881803"

HANDLECOLLISIONS - NOHANDLECOLLISIONS

         

HANDLECOLLISIONS was added to the replicat process in order to allow for collisions caused by uncertainty in the initial load to be handled for a short period of time. It is designed to handle changes to rows that were made during the time that the initial load was happening and is not designed to handle collisions that occur because of bi-directional replication or logic or architectural problems.

Never use HANDLECOLLISIONS unless it is absolutely necessary. Even then, use it for the shortest time possible.

If there is an update to a column that is used as a key in GoldenGate the following will occur:

If the row that has the old key is not found in the target database, the update is converted to an insert.

If a row is found with the new key, the row with the old key is deleted and the update overlays the row with the new key.

Both of these cases require that all of the columns are logged. This is done by making sure trandata is complete and by using the extract parameter NOCOMPRESSUPDATES.

If a duplicate record is found, the value in the trail file is used and the record that previously exists is discarded.

CONDITION ACTION

INSERT Collision INSERT Collision INSERT IN SOURCE WHOSE KEY CLUMN EXIST ON TARGET Converted to UPDATES UPDATE Collision UPDATE Collision Updated in source but row not present in target MISSING ROW FROM TARGET IS CONVERTED TO INSERT DELETE Collision DELETE Collision Deleted in source but row not present in target Ignored Discard file : SOURCE TARGET ERROR MESSAGE Duplicate inserts Send to discard file when it comes across – REPERROR (0001 Discard) Unique constraint violation. Updated in source but row not present at target Send to discard file when it comes across – REPERROR (1403 Discard) No data found Deleted in source but row not present at target Send to discard file when it comes across – EPERROR (1403 Discard) No data found

If a missing record is found during an update or delete operation that does not affect the GoldenGate key value, it is simply discarded.

Enabling HANDLECOLLISIONS---->

Globally: Enable global HANDLECOLLISIONS for ALL MAP statements

/*

HANDLECOLLISIONS

MAP soe.emp, TARGET soe.emp;

MAP soe.device, TARGET soe.device;

MAP soe.deviceinfo, TARGET soe.deviceinfo;

MAP soe.orders, TARGET soe.orders;

*/

Group : Enable HANDLECOLLISIONS for some MAP statements

/*

HANDLECOLLISIONS

MAP soe.emp, TARGET soe.emp;

MAP soe.device, TARGET soe.device;

NOHANDLECOLLISIONS -- (from here NOHANDLECOLLISIONS )

MAP soe.deviceinfo, TARGET soe.deviceinfo;

MAP soe.orders, TARGET soe.orders;

*/

/*

Tables: Enable global HANDLECOLLISIONS but disable for specific tables

HANDLECOLLISIONS

MAP soe.emp, TARGET soe.emp;

MAP soe.device, TARGET soe.device;

MAP soe.deviceinfo, TARGET soe.deviceinfo, NOHANDLECOLLISIONS; -- This is imp

MAP soe.orders, TARGET soe.orders, NOHANDLECOLLISIONS; -- This is imp

*/

Detail record count : stats replicate --- redirected

Don't forget to remove the HANDLECOLLISIONS parameter after the Replicat has moved old CSN where it was abending previously.

Also make sure to restart the Replicat after the removing this parameter.

Create N number of Logical partition in Linux

Hi All,
 With help of below script we can create multiple logical(extended) portions
 
 uses : Mk_partion.sh Disk name
    Ex. Mk_partion.sh /dev/sdb
 Param  :
    1."PARTITION_SIZE"  -- Logical Portion size
    2."PARTITION_SIZE_P" --Primary Portion size

click Here to Download : Mk_partion.sh      
   
#----------------------------Start Mk_partion.sh-------------------------------------

#-- +----------------------------------------------------------------------------+
#-- |                               By OraVR                                     |
#-- |                            info@gmail.com                                 |
#-- |                              www.oravr.com                                 |
#-- |----------------------------------------------------------------------------|
#-- |                                                                            |
#-- |----------------------------------------------------------------------------|
#-- | DATABASE : Oracle                                                          |
#-- | FILE     : Mk_partion.sh                                                   |
#-- | CLASS    : Storage                                                         |
#-- | PURPOSE  : Create Multiple logical disk                                    |
#-- | NOTE     : As with any code, ensure to test this script in a development   |
#-- |            environment before attempting to run it in production.          |
#-- +----------------------------------------------------------------------------+
#!/bin/bash
if [ $# -eq 0 ]
then
  echo "input the device"
  exit
fi
NUM_PARTITIONS=50
PARTITION_SIZE="+4096M"   
PARTITION_SIZE_P="+100M"
SED_STRING="o"
TAIL="p
w
q
"
NEW_LINE="
"
LETTER_n="n"
EXTENDED_PART_NUM=4
TGTDEV=$1
SED_STRING="$SED_STRING$NEW_LINE"
for i in $(seq $NUM_PARTITIONS)
do
  if [ $i -lt $EXTENDED_PART_NUM ]
  then
    SED_STRING="$SED_STRING$LETTER_n$NEW_LINE$NEW_LINE$NEW_LINE$NEW_LINE$PARTITION_SIZE_P$NEW_LINE"
  fi
  if [ $i -eq $EXTENDED_PART_NUM ]
  then
    SED_STRING="$SED_STRING$LETTER_n$NEW_LINE$NEW_LINE$NEW_LINE$NEW_LINE"
    SED_STRING="$SED_STRING$LETTER_n$NEW_LINE$NEW_LINE$PARTITION_SIZE$NEW_LINE"
  fi
  if [ $i -gt $EXTENDED_PART_NUM ]
  then
    SED_STRING="$SED_STRING$LETTER_n$NEW_LINE$NEW_LINE$PARTITION_SIZE$NEW_LINE"
  fi
done
SED_STRING="$SED_STRING$TAIL"
sed -e 's/\s*\([\+0-9a-zA-Z]*\).*/\1/' << EOF | fdisk ${TGTDEV}
  $SED_STRING
EOF
########## Remove PARTITION if anything goes wrong #########################################
######dd if=/dev/zero of=/dev/sda bs=512 count=1 conv=notrunc######

#---------------------------- End  Mk_partion.sh-------------------------------------












 

 

 

Enable DDL Logging

Enable DDL Logging

DDL logging is really very good feature to monitor changes done in DB design.
One can monitor or audit all DB changes by viewing log file.
One can set enable_ddl_logging=true to enable DDL logging.
Oracle will start recording all DDL statements in log.xml file. This file has the same format and basic behavior as the alert log, but it only contains the DDL statements.
The DDL log file is created in $ADR_HOME/log/ddl directory; it contains DDL statements that are extracted from alertl log file.
Log file Path
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml
NOTE:- Oracle license "Oracle Change Management Pack" is require to use this feature.
Parameter enable_ddl_logging is licensed as part of the Change Management Pack.
Following DDL statements are written to the log:

•    ALTER/CREATE/DROP/TRUNCATE CLUSTER
•    ALTER/CREATE/DROP FUNCTION
•    ALTER/CREATE/DROP INDEX
•    ALTER/CREATE/DROP OUTLINE
•    ALTER/CREATE/DROP PACKAGE
•    ALTER/CREATE/DROP PACKAGE BODY
•    ALTER/CREATE/DROP PROCEDURE
•    ALTER/CREATE/DROP PROFILE
•    ALTER/CREATE/DROP SEQUENCE
•    CREATE/DROP SYNONYM
•    ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
•    ALTER/CREATE/DROP TRIGGER
•    ALTER/CREATE/DROP TYPE
•    ALTER/CREATE/DROP TYPE BODY
•    DROP USER
•    ALTER/CREATE/DROP VIEW

Following is the example of DDL logging,


VRTEST$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 11 04:41:39 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

VB_SQL>
VB_SQL> show parameter enable_ddl_logging
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     FALSE
VB_SQL> alter system set enable_ddl_logging=true;
System altered.
VB_SQL>
VB_SQL> show parameter enable_ddl_logging;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     TRUE
VB_SQL> conn VB/VB
Connected.
VB_SQL>
VB_SQL> create table vb_ddltest (id number, name varchar2(50));
Table created.
VB_SQL>
VB_SQL> alter table vb_ddltest add ( address varchar2(100));
Table altered.
VB_SQL>
VB_SQL> insert into vb_ddltest values (1,'CBT','XYZ');
1 row created.
VB_SQL>
VB_SQL> commit;
Commit complete.
VB_SQL> drop table vb_ddltest;
Table dropped.
VB_SQL>
Check the log file for all DDL commands that was run by user.

VRTEST$ pwd
"/opt/oracle/app/orcl/diag/rdbms/VRTEST/VRTEST/log/ddl"

VRTEST$ cat log.xml
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'
 version='1'>
 create table vb_ddltest (id number, name varchar2(50))

 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 alter table vb_ddltest add ( address varchar2(100))

 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 drop table vb_ddltest

VRTEST$
If you want to see clear and readable DDL log file, Just invoke ADRCI utility and issue show log command as shown below,
VRTEST$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Tue Feb 11 04:44:53 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/opt/oracle/app/orcl"

ADR base = "/mnt/Oracle"
adrci> show log -l ddl
ADR Home = /opt/oracle/app/orcl/diag/rdbms/VRTEST/VRTEST:
*************************************************************************
Output the results to file: /tmp/utsout_19321_13991_1.ado
adrci>

When you issue the command "show log", this will open the log.xml file in edtor (i.e. vi in linux/unix) and show the contents, in following format :


2020-02-11 12:23:22.395000 +05:30
create table vb_ddltest (id number, name varchar2(50))
2020-02-11 12:24:05.301000 +05:30
alter table vb_ddltest add ( address varchar2(100))
2020-02-11 12:25:14.003000 +05:30
drop table vb_ddltest
2020-02-11 12:30:33.272000 +05:30
truncate table wri$_adv_addm_pdbs
Also you can see the debug log using adrci show log command i.e. show log -l debug.