Exporting AUDIT tables Using Oracle Data Pump

By -

 In Oracle 11g, it is not possible to use Data Pump to export the AUD$ table directly:

[oracle@myhost ~]$ expdp directory=mrm dumpfile=aud.dmp tables=aud$

Export: Release 11.2.0.4.0 – Production on Sat Dec 8 17:30:47 2018

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

Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** AS SYSDBA directory=mrm dumpfile=aud.dmp tables=aud$

Estimate in progress using BLOCKS method…

Total estimation using BLOCKS method: 0 KB

ORA-39166: Object SYS.AUD$ was not found.

ORA-31655: no data or metadata objects selected for job

Job “SYS”.”SYS_EXPORT_TABLE_01″ completed with 2 error(s) at Sat Dec 8 17:30:59 2018 elapsed 0 00:00:04

However, this can be done using the traditional exp/imp utilities:

[oracle@myhost ~]$ exp file=’/home/oracle/aud.dmp’ tables=aud$

Export: Release 11.2.0.4.0 – Production on Sat Dec 8 17:59:20 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

About to export specified tables via Conventional Path …

. . exporting table                           AUD$          10 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

With the introduction of unified auditing in Oracle 12c, it is now possible to export audit-related tables using the INCLUDE=AUDIT_TRAILS parameter:

[oracle@myhost ~]$ expdp directory=mrm dumpfile=aud.dmp full=y INCLUDE=AUDIT_TRAILS

Export: Release 18.0.0.0.0 – Production on Sat Azar 17 18:15:39 1397

Version 18.3.0.0.0

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

Starting “SYS”.”SYS_EXPORT_FULL_01″:  sys/******** AS SYSDBA directory=mrm dumpfile=aud.dmp full=y INCLUDE=AUDIT_TRAILS

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported “SYS”.”KU$_USER_MAPPING_VIEW”               6.218 KB      49 rows

. . exported “AUDSYS”.”AUD$UNIFIED”:”SYS_P754″           145.0 KB     297 rows

. . exported “AUDSYS”.”AUD$UNIFIED”:”SYS_P181″           64.41 KB      37 rows

. . exported “SYS”.”AUD$”                                25.84 KB      12 rows

. . exported “SYS”.”DAM_CONFIG_PARAM$”                   6.531 KB      14 rows

. . exported “AUDSYS”.”AUD$UNIFIED”:”AUD_UNIFIED_P0″         0 KB       0 rows

. . exported “SYS”.”DAM_CLEANUP_EVENTS$”                     0 KB       0 rows

. . exported “SYS”.”DAM_CLEANUP_JOBS$”                       0 KB       0 rows

. . exported “SYS”.”AUDTAB$TBS$FOR_EXPORT”               5.953 KB       2 rows

. . exported “SYS”.”FGA_LOG$FOR_EXPORT”                      0 KB       0 rows

Master table “SYS”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded

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

Dump file set for SYS.SYS_EXPORT_FULL_01 is:

  /home/oracle/aud.dmp

Job “SYS”.”SYS_EXPORT_FULL_01″ successfully completed at Sat Dec 8 18:17:26 2018 elapsed 0 00:01:41

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more