Showing posts with label auditing. Show all posts
Showing posts with label auditing. Show all posts

Friday, June 10, 2016

Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)


 Inserting Data with DML Error Logging:



When you load a table using an INSERT statement with sub query, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.


DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------


  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES INTO TEST TABLE
--------------------------------------------------------


Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------


EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats:

  • Oracle Database logs the following errors during DML operations:
  • Column values that are too large
  • Constraint violations (NOT NULL, unique, referential, and check constraints)
  • Errors raised during trigger execution
  • Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
  • Partition mapping errors
Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------


INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9; 

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:
 
If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------


update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------


select * from ERR_ATEST1;

Friday, February 19, 2016

Audit data pump actions in 12c

   ******************************************************************************
Audit Data pump actions
******************************************************************************

[oracle@Linux03 Desktop]$ sqlplus /"As sysdba"

SQL> alter session set container=pdborcl1;

Session altered.

SQL> create directory dpump as '/u01/app/oracle/dpump';

Directory created.

SQL> grant read,write on directory dpump to pdborcl1_usr2;

Grant succeeded.

******************************************************************************

SQL> SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dp_text_parameters1 FORMAT A30
COLUMN dp_boolean_parameters1 FORMAT A30

SELECT event_timestamp,
       dp_text_parameters1,
       dp_boolean_parameters1
FROM   unified_audit_trail
WHERE  audit_type = 'Datapump';

no rows selected

SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;

Audit policy created.

SQL> AUDIT POLICY audit_dp_all_policy BY pdborcl1_usr2;

Audit succeeded.

******************************************************************************

[oracle@Linux03 oracle]$ expdp pdborcl1_usr2/XXXXXXX@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables15 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 15:24:13 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
11-MAR-16 15:24:15.351: Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables15 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status
11-MAR-16 15:24:15.810: Estimate in progress using BLOCKS method...
11-MAR-16 15:24:16.683: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
11-MAR-16 15:24:16.778: Total estimation using BLOCKS method: 128 KB
11-MAR-16 15:24:21.341: Processing object type TABLE_EXPORT/TABLE/TABLE
11-MAR-16 15:24:22.237: Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
11-MAR-16 15:24:23.233: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
11-MAR-16 15:24:24.615: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
11-MAR-16 15:24:24.656: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
11-MAR-16 15:24:25.230: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11-MAR-16 15:24:25.245: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-MAR-16 15:24:29.893: . . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
11-MAR-16 15:24:29.907: . . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
11-MAR-16 15:24:30.137: Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
11-MAR-16 15:24:30.142:

******************************************************************************
11-MAR-16 15:24:30.142: Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
11-MAR-16 15:24:30.147:   /u01/app/oracle/dpump/pdborc1_usr2_tables15.dmp
11-MAR-16 15:24:30.164: Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 15:24:30 2016 elapsed 0 00:00:15

******************************************************************************

SQL> SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dp_text_parameters1 FORMAT A30
COLUMN dp_boolean_parameters1 FORMAT A30

SELECT event_timestamp,
       dp_text_parameters1,
       dp_boolean_parameters1
FROM   unified_audit_trail
WHERE  audit_type = 'Datapump';

EVENT_TIMESTAMP       DP_TEXT_PARAMETERS1      DP_BOOLEAN_PARAMETERS1
------------------------------ ------------------------------ ------------------------------
11-MAR-16 08.24.15.775940 AM   MASTER TABLE:  "PDBORCL1_USR2" MASTER_ONLY: FALSE, DATA_ONLY:
      ."SYS_EXPORT_TABLE_01" , JOB_T  FALSE, METADATA_ONLY: FALSE,
      YPE: EXPORT, METADATA_JOB_MODE DUMPFILE_PRESENT: TRUE, JOB_RE
      : TABLE_EXPORT, JOB VERSION: 1 STARTED: FALSE
      2.1.0.2.0, ACCESS METHOD: AUTO
      MATIC, DATA OPTIONS: 0, DUMPER
DIRECTORY: NULL  REMOTE LINK:
NULL, TABLE EXISTS: NULL, PAR
      TITION OPTIONS: NONE